【Myrat】用asp.net呼叫excel並輸出的一點經驗

iDotNetSpace發表於2008-06-10

前一段時間用asp.net呼叫excel做報表,有一些小心得。

1.建立:我先自己建立了一份模版,然後用excel讀取模版,再把資料寫進去。這樣做的好處,就是可以把資料層和表現層分開,以後需要改變顏色,字型等等的時候,只要format這個模版就行了。

讀取模版檔案(templateName為檔名):

【Myrat】用asp.net呼叫excel並輸出的一點經驗        Dim objExcel As Excel.Application
【Myrat】用asp.net呼叫excel並輸出的一點經驗        
Dim objSheet As Excel.Worksheet
【Myrat】用asp.net呼叫excel並輸出的一點經驗
【Myrat】用asp.net呼叫excel並輸出的一點經驗        
Try
【Myrat】用asp.net呼叫excel並輸出的一點經驗            objExcel 
= New Excel.Application()
【Myrat】用asp.net呼叫excel並輸出的一點經驗            
'If encounter same name file,then overwrite
【Myrat】用asp.net呼叫excel並輸出的一點經驗
            objExcel.Application.DisplayAlerts = False
【Myrat】用asp.net呼叫excel並輸出的一點經驗            objExcel.Visible 
= False
【Myrat】用asp.net呼叫excel並輸出的一點經驗            objExcel.SheetsInNewWorkbook 
= 1
【Myrat】用asp.net呼叫excel並輸出的一點經驗            objExcel.Workbooks.Add(Server.MapPath(templateName))
【Myrat】用asp.net呼叫excel並輸出的一點經驗        
End Try
【Myrat】用asp.net呼叫excel並輸出的一點經驗        objSheet 
= objExcel.Workbooks(1).Worksheets(1)
【Myrat】用asp.net呼叫excel並輸出的一點經驗

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的。

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

【Myrat】用asp.net呼叫excel並輸出的一點經驗

使用的時候:
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這個程式,如果不殺死的話,就不會自動退出,所以一定要手動殺死:

【Myrat】用asp.net呼叫excel並輸出的一點經驗
【Myrat】用asp.net呼叫excel並輸出的一點經驗            
If Not objExcel Is Nothing Then
【Myrat】用asp.net呼叫excel並輸出的一點經驗                objExcel.Workbooks.Close()
【Myrat】用asp.net呼叫excel並輸出的一點經驗                objExcel.Quit()
【Myrat】用asp.net呼叫excel並輸出的一點經驗                System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)
【Myrat】用asp.net呼叫excel並輸出的一點經驗            
End If
【Myrat】用asp.net呼叫excel並輸出的一點經驗            
If Not objSheet Is Nothing Then
【Myrat】用asp.net呼叫excel並輸出的一點經驗                System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet)
【Myrat】用asp.net呼叫excel並輸出的一點經驗            
End If
【Myrat】用asp.net呼叫excel並輸出的一點經驗            objSheet 
= Nothing
【Myrat】用asp.net呼叫excel並輸出的一點經驗            objExcel 
= Nothing
【Myrat】用asp.net呼叫excel並輸出的一點經驗            GC.Collect()
【Myrat】用asp.net呼叫excel並輸出的一點經驗


5.輸出:我實在找不到辦法把這個workbook變成stream輸出,只好用笨辦法,寫到檔案裡再讀出來:誰有更好的辦法請告訴我

【Myrat】用asp.net呼叫excel並輸出的一點經驗
【Myrat】用asp.net呼叫excel並輸出的一點經驗
Dim strFileName As String = GetFileName()   '自己寫的得到一個文隨機的檔名
【Myrat】用asp.net呼叫excel並輸出的一點經驗
objExcel.Workbooks(1).SaveCopyAs(strFileName)     '需要一個可以寫的folder
【Myrat】用asp.net呼叫excel並輸出的一點經驗

【Myrat】用asp.net呼叫excel並輸出的一點經驗            Response.Clear()
【Myrat】用asp.net呼叫excel並輸出的一點經驗            Response.Charset 
= ""
【Myrat】用asp.net呼叫excel並輸出的一點經驗
            Response.ContentType = "Application/vnd.ms-excel"
【Myrat】用asp.net呼叫excel並輸出的一點經驗
            Response.AppendHeader("content-disposition""attachment; filename=" & Type & "Report" & ".xls")
【Myrat】用asp.net呼叫excel並輸出的一點經驗
【Myrat】用asp.net呼叫excel並輸出的一點經驗            
Dim exl As Byte() = ReadFile(strFileName)   '自己寫的binary讀取檔案的函式
【Myrat】用asp.net呼叫excel並輸出的一點經驗
            Response.OutputStream.Write(exl, 0, exl.Length)
【Myrat】用asp.net呼叫excel並輸出的一點經驗            Response.OutputStream.Flush()
【Myrat】用asp.net呼叫excel並輸出的一點經驗
【Myrat】用asp.net呼叫excel並輸出的一點經驗            Response.
End()
【Myrat】用asp.net呼叫excel並輸出的一點經驗
【Myrat】用asp.net呼叫excel並輸出的一點經驗

 

這個檔案當然是要刪除的,我在page_unload裡面刪除他:

【Myrat】用asp.net呼叫excel並輸出的一點經驗
【Myrat】用asp.net呼叫excel並輸出的一點經驗    
Private Sub Page_Unload(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Unload
【Myrat】用asp.net呼叫excel並輸出的一點經驗        
Dim strFileName As String = Session("strfilename")
【Myrat】用asp.net呼叫excel並輸出的一點經驗        
If Not strFileName = Nothing AndAlso strFileName.Length > 0 Then
【Myrat】用asp.net呼叫excel並輸出的一點經驗            
Try
【Myrat】用asp.net呼叫excel並輸出的一點經驗                System.IO.File.Delete(strFileName)
【Myrat】用asp.net呼叫excel並輸出的一點經驗            
Catch ex As Exception
【Myrat】用asp.net呼叫excel並輸出的一點經驗            
End Try
【Myrat】用asp.net呼叫excel並輸出的一點經驗        
End If
【Myrat】用asp.net呼叫excel並輸出的一點經驗    
End Sub

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12639172/viewspace-343226/,如需轉載,請註明出處,否則將追究法律責任。

相關文章