.net 输出Excel的一点经验总结（转载）

用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() 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() Sub Page_Unload(ByVal sender As System.Object, ByVal 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