July 07, 2004
The previous time used ASP.NET to call Excel to do a report, and some are careful.
1. Create: I created a template first, then read the template with Excel, and write the data in. The advantage of this is to separate the data layer and the performance layer, and then change the color, font, etc., as long as the format is scheduled.
Read the template file (TemplateName is the file name):
DIM
Objexcel
AS
Excel.Application
DIM
Objsheet
AS
Excel.Worksheet
Try
Objexcel
=
New
Excel.Application ()
'
Ifencountersamenamefile, ThenoverWrite
Objexcel.Application.displayalerts
=
False
Objexcel.visible
=
False
Objexcel.sheetsinnewwkebook
=
1
Objexcel.Workbooks.add (server.mappath (templater))
End
Try
Objsheet
=
Objexcel.Workbooks
1
). Worksheets
1
)
2. Data: Then write data in it. If it is a single data, it is ok, like this: objsheet.cells (3, 5) = startdate.tostring ("DD-MMM-YY") But if is a DataSet? After the test has repeatedly found, the most convenient and fast way is to transfer DataSet into array, because you can assign a range directly as an Array. And this method is also the highest: Dim DataArray As Object (,) = DatasettoArray (DstrainerPerformance, _ "Sno, Fullname, Organization, Department, Feedback") objsheet.range ("a8"). Resize (Datacount, 13). Resize ) .Value = DataArray here's DataSetToarray is a FUNCTION written by himself, converts DataSet into a 2D Array.
3. Format: The above is not said in the template, why do you have to talk about it again? Don't forget, then the template is dead, the first few is the first few, but after you write a bunch of data, the number of lines changed. There are two ways at this time: 1, write code to format. I have defined each format, such as a format, a format, a format, and a format. Then define each format, such as a border, a border / border, alignment, underscore, font, folding, Number Format, and more.
This Function is used to form a set of Range.
Private
SubformatRange ()
SubFormatRange (ByRefrangeAsExcel.Range, _ByValformatIndexAsInteger = 0) WithrangeSelectCaseformatIndexCase1'lines: topandbottom, leftalignWith.Borders (Excel.XlBordersIndex.xlEdgeTop) .Color = 0.Weight = 2.LineStyle = Excel.XlLineStyle.xlContinuousEndWithWith.Borders (Excel.XlBordersIndex.xlEdgeBottom ) .Color = 0.Weight = 2.LineStyle = Excel.XlLineStyle.xlContinuousEndWithIf.Rows.Count> 1ThenWith.Borders (Excel.XlBordersIndex.xlInsideHorizontal) .Color = 0.Weight = 2.LineStyle = Excel.XlLineStyle.xlContinuousEndWithEndIfCase2'bold , underline, center.Font.Bold = True.Font.Underline = True.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterCase3'bold, leftalign, nowrap.WrapText = False.Font.Bold = True.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeftCase4'bold , underline, leftalign.Font.Bold = True.Font.Underline = True.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeftCase5'bold, center.Font.Bold = True.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterCase6'normalCase7'bold, right, wraptext .Wraptext = true.font.bold = true.horizontalalignment = Excel. Xlhalign.xlhalignRightendSerecTendwithendSub is used: formatRange (osheet.range (range), formatindex)
Here is the 7 formats, the more you write, the more you have to define each different format, and I have written more than 30 formats soon. I really don't want to write, I think about this method.
In fact, it is also very simple, do every format in Template, such as defining a line for the head, defines a row, then the Copy header, then writing; Copy Data Bar Paste, how much is needed Paste how much, then write data.
COPY pre-defined data bar for i = 0 to Datcount - 1 Objsheet.Range ("A8: D8"). Copy ("A8: D8"). Copy ("A8: D8"). Copy ("A8: D8"). COPY 1 I)) NextObjsheet.Range ("A8: D8") is good informat. When you change Format, you will easily pull ~~ 4. Clear the garbage: Excel, if you don't kill, you will not exit it automatically, so you must manually kill:
IF
NOT
Objexcel
IS
Nothing
THEN
Objexcel.Workbooks.close () object () object () system.Runtime.InteropServices.Marshal.ReleaseComobject (Object (Objexcel)
End
IF
IF
NOT
Objsheet
IS
Nothing
THEN
System.Runtime.InteropServices.Marshal.ReleaseComobject (objsheet)
End
IF
Objsheet
=
Nothing
Objexcel
=
Nothing
Gc.collect ()
5. Output: I can't find a way to turn this workbook into stream output. I have to use a stupid method. I will write it in the file: Who has a better way, please tell me
DIM
Strfilename
AS
String
=
GetFileName ()
'
Write a file name to get a text
Objexcel.Workbooks
1
) .Savecopyas (strfilename)
'
Need a writable Folder
Response.Clear () response.charset
=
"
"
Response.ContentType
=
"
Application / VND.MS-Excel "
Response.Appendheader
"
Content-Disposition
"
,
"
Attachment; filename =
"
&
Type
&
"
Report
"
&
"
.xls
"
)
DIM
EXL
AS
Byte
()
=
Readfile (StrfileName)
'
Written Binary read files
Response.OutputStream.
Write
(EXL,
0
, EXL.LENGTH) response.outputstream.flush () Response.
End
()
This file is of course deleted, I delete him in Page_unload:
Private
Subpage_unload ()
SubPage_Unload (ByValsenderAsSystem.Object, ByValeAsSystem.EventArgs) HandlesMyBase.UnloadDimstrFileNameAsString = Session ( "strfilename") IfNotstrFileName = NothingAndAlsostrFileName.Length> 0ThenTrySystem.IO.File.Delete (strFileName) CatchexAsExceptionEndTryEndIfEndSub
Posted @ 2004-07-07 15:58 Myrat reading (615) | Comments (6) | Edit Collection
