1.创建:我先自己创建了一份模版,然后用excel读取模版,再把数据写进去。这样做的好处,就是可以把数据层和表现层分开,以后需要改变颜色,字体等等的时候,只要format这个模版就行了。
读取模版文件(templateName为文件名):
Dim
objExcel
As
Excel.Application
Dim
objSheet
As
Excel.Worksheet
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
Try
objExcel
=
New
Excel.Application()
'
Ifencountersamenamefile,thenoverwrite
objExcel.Application.DisplayAlerts
=
False
objExcel.Visible
=
False
objExcel.SheetsInNewWorkbook
=
1
objExcel.Workbooks.Add(Server.MapPath(templateName))
End
Try
objSheet
=
objExcel.Workbooks(
1
).Worksheets(
1
)
2.数据:然后就往里面写数据啦。如果是单个的数据当然好写,像这样:
objSheet.Cells(3, 5) = startDate.ToString("dd-MMM-yy")
但如果是一个dataset呢?试验多次后发现,最方便快捷的方法是把dataset转成array在写进去,因为可以直接将一个range赋值为一个array。而且这种方法效率也应该是最高的:
Dim dataArray As Object(,) = DataSetToArray(dsTrainerPerformance, _
"SNO,FullName,Organization,Department,Feedback")
objSheet.Range("A8").Resize(dataCount, 13).Value = dataArray
这里的DataSetToArray是自己写的一个function,把dataset转换成一个2d array。
3.格式:上面不是说了在模版里面format嘛,为什么又要再讲?别忘了,再模版里面东西是死的,第几行就是第几行,可是你写了一堆数据进去后,行数就全变了阿。这时候有两种办法:1,写code来format。我是对每种格式都定义了一下,比如表头是一种格式,数据栏是一种格式,统计栏是一种格式。然后定义每种格式,比如边框,有边框/无边框,对齐,下划线,字体,折叠,number format等等。
这个function是用来format一个制定的range的。
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
Private
SubFormatRange()
SubFormatRange(ByRefrangeAsExcel.Range,_
ByValformatIndexAsInteger=0)
Withrange
SelectCaseformatIndex
Case1
'lines:topandbottom,leftalign
With.Borders(Excel.XlBordersIndex.xlEdgeTop)
.Color=0
.Weight=2
.LineStyle=Excel.XlLineStyle.xlContinuous
EndWith
With.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.Color=0
.Weight=2
.LineStyle=Excel.XlLineStyle.xlContinuous
EndWith
If.Rows.Count>1Then
With.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.Color=0
.Weight=2
.LineStyle=Excel.XlLineStyle.xlContinuous
EndWith
EndIf
Case2
'bold,underline,center
.Font.Bold=True
.Font.Underline=True
.HorizontalAlignment=Excel.XlHAlign.xlHAlignCenter
Case3
'bold,leftalign,nowrap
.WrapText=False
.Font.Bold=True
.HorizontalAlignment=Excel.XlHAlign.xlHAlignLeft
Case4
'bold,underline,leftalign
.Font.Bold=True
.Font.Underline=True
.HorizontalAlignment=Excel.XlHAlign.xlHAlignLeft
Case5
'bold,center
.Font.Bold=True
.HorizontalAlignment=Excel.XlHAlign.xlHAlignCenter
Case6
'normal
Case7
'bold,right,wraptext
.WrapText=True
.Font.Bold=True
.HorizontalAlignment=Excel.XlHAlign.xlHAlignRight
EndSelect
EndWith
EndSub
使用的时候:
FormatRange(oSheet.Range(range), formatIndex)
这里才列了7种格式,当时我越写越多,每种不同的格式都要定义一下,很快写了30多种格式,实在不想写了,才想了下面这种方法。
其实也很简单,把每种格式都做在template里面,比如为表头定义一行,数据栏定义一行,然后copy表头那一行,再在里面写字;copy数据栏那一行paste,需要多少行就paste多少行,然后再写数据。
copy预先在template里面定义好的数据栏
For i = 0 To datacount - 1
objSheet.Range("A8:D8").Copy(objSheet.Range("A" & StartRow + 1 + i & ":D" & StartRow + 1 + i))
Next
objSheet.Range("A8:D8")就是事先format好的。以后改变format的时候就容易拉~~
4.清除垃圾:Excel这个进程,如果不杀死的话,就不会自动退出,所以一定要手动杀死:
If
Not
objExcel
Is
Nothing
Then
objExcel.Workbooks.Close()
objExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)
End
If
If
Not
objSheet
Is
Nothing
Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet)
End
If
objSheet
=
Nothing
objExcel
=
Nothing
GC.Collect()
5.输出:我实在找不到办法把这个workbook变成stream输出,只好用笨办法,写到文件里再读出来:谁有更好的办法请告诉我
Dim
strFileName
As
String
=
GetFileName()
'
自己写的得到一个文随机的文件名
objExcel.Workbooks(
1
).SaveCopyAs(strFileName)
'
需要一个可以写的folder
Response.Clear()
Response.Charset
=
"
"
Response.ContentType
=
"
Application/vnd.ms-excel"
Response.AppendHeader(
"
content-disposition
"
,
"
attachment;filename=
"
&
Type
&
"
Report
"
&
"
.xls
"
)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
Dim
exl
As
Byte
()
=
ReadFile(strFileName)
'
自己写的binary读取文件的函数
Response.OutputStream.
Write
(exl,
0
,exl.Length)
Response.OutputStream.Flush()
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
Response.
End
()
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
这个文件当然是要删除的,我在page_unload里面删除他:
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
Private
SubPage_Unload()
SubPage_Unload(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMyBase.Unload
DimstrFileNameAsString=Session("strfilename")
IfNotstrFileName=NothingAndAlsostrFileName.Length>0Then
Try
System.IO.File.Delete(strFileName)
CatchexAsException
EndTry
EndIf
EndSub