用asp.net调用excel并输出的一点经验

前一段时间用asp.net调用excel做报表,有一些小心得。

1.创建:我先自己创建了一份模版,然后用excel读取模版,再把数据写进去。这样做的好处,就是可以把数据层和表现层分开,以后需要改变颜色,字体等等的时候,只要format这个模版就行了。

读取模版文件(templateName为文件名):

        Dim objExcel As Excel.Application
        
Dim objSheet As Excel.Worksheet

        
Try
            objExcel 
= New Excel.Application()
            
'If encounter same name file,then overwrite
            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的。


    
Private Sub FormatRange(ByRef range As Excel.Range, _
    
ByVal formatIndex As Integer = 0)
        
With range
            
Select Case formatIndex
                
Case 1
                    
'lines: top and bottom, left align
                    With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .Color 
= 0
                        .Weight 
= 2
                        .LineStyle 
= Excel.XlLineStyle.xlContinuous
                    
End With
                    
With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .Color 
= 0
                        .Weight 
= 2
                        .LineStyle 
= Excel.XlLineStyle.xlContinuous
                    
End With
                    
If .Rows.Count > 1 Then
                        
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                            .Color 
= 0
                            .Weight 
= 2
                            .LineStyle 
= Excel.XlLineStyle.xlContinuous
                        
End With
                    
End If
                
Case 2
                    
'bold, underline, center
                    .Font.Bold = True
                    .Font.Underline 
= True
                    .HorizontalAlignment 
= Excel.XlHAlign.xlHAlignCenter
                
Case 3
                    
'bold, left align, nowrap
                    .WrapText = False
                    .Font.Bold 
= True
                    .HorizontalAlignment 
= Excel.XlHAlign.xlHAlignLeft
                
Case 4
                    
'bold, underline, left align
                    .Font.Bold = True
                    .Font.Underline 
= True
                    .HorizontalAlignment 
= Excel.XlHAlign.xlHAlignLeft
                
Case 5
                    
'bold, center
                    .Font.Bold = True
                    .HorizontalAlignment 
= Excel.XlHAlign.xlHAlignCenter
                
Case 6
                    
'normal
                Case 7
                    
'bold, right, wrap text
                    .WrapText = True
                    .Font.Bold 
= True
                    .HorizontalAlignment 
= Excel.XlHAlign.xlHAlignRight
            
End Select
        
End With
    
End Sub

使用的时候:
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")

            
Dim exl As Byte() = ReadFile(strFileName)   '自己写的binary读取文件的函数
            Response.OutputStream.Write(exl, 0, exl.Length)
            Response.OutputStream.Flush()

            Response.
End()

 

这个文件当然是要删除的,我在page_unload里面删除他:


    
Private Sub Page_Unload(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Unload
        
Dim strFileName As String = Session("strfilename")
        
If Not strFileName = Nothing AndAlso strFileName.Length > 0 Then
            
Try
                System.IO.File.Delete(strFileName)
            
Catch ex As Exception
            
End Try
        
End If
    
End Sub

 

posted on 2004-07-07 15:58  myrat  阅读(13469)  评论(14编辑  收藏  举报

导航