使用NPOI做EXCEL檔的資料套表

2010-04-20

13281
0
Excel

使用NPOI做EXCEL檔的資料套表

前言

之前我們的套表是將EXCEL存成HTML格式，然後加上一些要置換的字，如#FLD_LOCATION#，然後去REPLACE它! 然後再將該檔案存成.XLS給Client端。可是當我的範本是要多個Sheet時，有些Client端的電腦會無法下載這些Sheet!

研究

因為目前我要套表的資料只有一筆，而且是有多個Sheet要套，所以就參考在 Server 端存取 Excel 檔案的利器：NPOI Library，使用NPOI然後將每個Sheet中的Cell換成該筆資料即可!
If File.Exists(strFileName) Then
  '開啟Excel 
  Dim fsExcel As New FileStream(strFileName, FileMode.Open, FileAccess.Read)
  Dim workbook As New HSSFWorkbook(fsExcel)
  Dim drData As DataRow = GetDataTable.Rows(0)
  For i As Integer = 0 To workbook.NumberOfSheets - 1
    '預設取10個sheet來處理
    Dim sheet As HSSFSheet
    sheet = workbook.GetSheetAt(i)
    '取得sheet，進行套表
    MergeSheetData(sheet, drData)
  Next
  Dim fsExcelNew As New FileStream("C:\NewExcel.xls", FileMode.Create)

  workbook.Write(fsExcelNew)
  workbook = Nothing
  fsExcelNew.Close()
  fsExcel.Close()
  MsgBox("執行完成!")
End If
''' 將一筆資料合併到Excel的Sheet中
''' </summary>
''' <param name="sheet"></param>
''' <param name="drData"></param>
''' <remarks></remarks>
Private Sub MergeSheetData(ByRef sheet As HSSFSheet, ByVal drData As DataRow)
  For i As Integer = sheet.FirstRowNum To sheet.LastRowNum
    Dim row As HSSFRow = sheet.GetRow(i)
    If IsNothing(row) = False Then
      For j As Integer = row.FirstCellNum To row.LastCellNum
        '依dataRow來Find並Replace #FLD_ COLUMN_NAME #
        For Each dcColumn As DataColumn In drData.Table.Columns
          Dim strSearchName As String = String.Format("#FLD_{0}#", dcColumn.ColumnName)
          If IsNothing(row.GetCell(j)) = False Then
            If row.GetCell(j).ToString.Contains(strSearchName) Then
              row.GetCell(j).SetCellValue(row.GetCell(j).ToString.Replace(strSearchName, drData(dcColumn.ColumnName)))
              Exit For
            End If
          End If
        Next
      Next
    End If
  Next
End Sub
 