亂馬客

前言

我們有個功能是將資料匯出到Excel，這個功能會設定該Excel各欄位的型態(如日期、字串...)，同事反應匯出的效能不好。於是我就進去看一下程式，結果它是一個Cell一個Cell指定資料，所以當資料很多時，這樣就跑很久。

Google一下，有很多將資料Assign到Excel中的方式，有以下幾個方式:

1.一個Cell一個Cell Assign
2.建立檔案，然後用Insert Into的方式(直接從SQL取出資料)
3.設一個Range然後Assign一個Array給它(資料需要加工過，放到Array之中)

...

因為程式中已有Array資料，所以我就選擇使用第3種方式來處理。依欄位數取得Excel的ColumnLabel可參考「傳入數值，傳出Excel的ColumnLabel的函數」。

速度還不錯，但卻發生了該Cell的顯示與我設定的欄位格式不同。需要在該Cell上Dbclick才會以設定的格式來顯示。

研究

先錄一下看DbClick做了那些事，原來是Assign資料到該Cell(ActiveCell.FormulaR1C1 = "1111111")。如果照巨集那個做法的話，那又要1個1個Cell重新Assign值，不就又回到了原點，變得很慢。

還試過，把Range Copy起來再貼上值，還是沒有用。

最後想說會不會是資料來源的問題呢? 來源是設成String Arrry! 因為資料有字串、日期也有數值，所以就改成Object Array! 試一下，果然是資料來源的問題! 所以資料的型態還是會影響顯示的哦!

實作

以下說明如何設定Excel的欄位資料型態及Assign資料到Range之中。

1.建立測試的資料Array

Dim aryDataSource(2, 1) As Object
aryDataSource(0, 0) = "字串0,0"
aryDataSource(0, 1) = 1111111

aryDataSource(1, 0) = "字串1,0"
aryDataSource(1, 1) = 2222222

aryDataSource(2, 0) = "字串0,0"
aryDataSource(2, 1) = 3333333

2.設定各欄位的資料型態及將資料Array Assign到Range

dim strFileNam as string = "c:\test1.xls"
Dim oBook As Object = oExcel.Workbooks.Add
Try
    Dim oSheet As Object
    oSheet = oBook.Worksheets(1)
    '設定Excel各欄位的型態，目前設定字串及數值 
    '第1欄設成文字
    oSheet.columns(1).NumberFormatLocal = "@"
    '第2欄設成3位一撇
    oSheet.columns(2).NumberFormatLocal = "#,##0"
        '傳入Column數，傳出Excel的ColumnLabel
        '1=>A, 26=>Z, 27=>AA
    Dim strColumnLabel As String = GetExcelColumnLabel(UBound(aryDataSource, 2) + 1)
    '將資料Assign給Range
    oSheet.Range("A1", strColumnLabel & UBound(aryDataSource, 1) + 1) = aryDataSource
    oExcel.DisplayAlerts = False
    '將資料存檔
    oBook.SaveAs(strFileName)
    oBook.Close()
    oExcel.Quit()

    GC.Collect() '強制記憶體回收。
    MsgBox("OK!")
Catch ex As Exception
    oBook.Close()
    oExcel.Quit()
    Throw ex
Finally
    oBook = Nothing
    oExcel = Nothing
End Try

附上測試範例:ExcelRangeAssign.rar

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^