前兩天遇到一個需求, 輸出的 Excel 要加上表頭, 如下圖
於是乎把 UW.ExcelPOI.DTToExcelAndWriteToClient 做了一些擴充, (其實應該說是幫 DTToWorkSheet 做了擴充), 過程如下.
1. 需求: 一個可以快速填入欄位的 Sub (method or function)
A. 每一個 Cell 可以設定內容(文字), 字型大小, 跨欄數, 對齊方式. (其它的未來再來擴充, 例如顔色).
B. 每一個 Row 由 Cell 組成, 由左到右.
C. 一次可以填多個 Row
2. 實作:
A. 先定義 Cell
Public Class Cell
Public Content As String
Public Colspan As Int32 = 1
Public Alignment As NPOI.SS.UserModel.HorizontalAlignment
Public FontHeightInPoints As Int32 = 0
Sub New(Content As String, Optional Colspan As Int32 = 1,
Optional Alignment As NPOI.SS.UserModel.HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General,
Optional FontHeightInPoints As Int32 = 0)
Me.Content = Content
Me.Colspan = Colspan
Me.Alignment = Alignment
Me.FontHeightInPoints = FontHeightInPoints
End Sub
End Class
B. Row 的格式: 我想最直的覺的就是 List(of Cell) 了吧.
C. 多個 Row 的表示法: List(Of List(Of Cell))
D. 來把 Cell 填入 WorkSheet 吧,
Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)
共有三個參數: WS 和 ltRows 應該不用解釋了. 最後一個 StartRow 用來指定插入資料的開始 Row.
E. 完整程式碼: (程式碼不看沒關係, 但要跳到 F. 重點講解哦)
Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)
Dim WR As HSSFRow
If ltRows IsNot Nothing Then
For Each ltRow As List(Of Cell) In ltRows
WR = WS.CreateRow(StartRow)
Dim C As Int32 = 0
For Each cell As Cell In ltRow
Dim ic As NPOI.SS.UserModel.ICell = WR.CreateCell(C)
ic.SetCellValue(cell.Content)
Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()
cs.Alignment = cell.Alignment
If cell.FontHeightInPoints > 0 Then
Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()
oFont.FontHeightInPoints = cell.FontHeightInPoints
cs.SetFont(oFont)
End If
ic.CellStyle = cs
If cell.Colspan > 1 Then
WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))
C += cell.Colspan - 1
End If
C += 1
Next
StartRow += 1
Next
End If
End Sub
F. 重點講解:
這個 function 在實作時有兩個卡點:
1. 如何合併欄:
WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))
2. 如何設定字型大小和對齊方式:
Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()
cs.Alignment = cell.Alignment
If cell.FontHeightInPoints > 0 Then
Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()
oFont.FontHeightInPoints = cell.FontHeightInPoints
cs.SetFont(oFont)
End If
ic.CellStyle = cs
這裡有件有有趣的事, 我一開始是這樣寫的.
ic.CellStyle.Alignment = cell.Alignment
結果是整個 WorkSheet 的對齊方式都被改了. 我猜當 WorkSheet 初建立時, CellStyle 都是用同一個. 所以改任一個 cell 的 CellStyle 會同時改到所有 cell 的.
G. 使用方式:
Dim ltHeader As New List(Of List(Of UW.ExcelPOI.Cell))
Dim ltLine As New List(Of UW.ExcelPOI.Cell)
ltLine.Add(New UW.ExcelPOI.Cell(DB.SysConfig.SYSTEM_NAME & "應收明細表", 16,
NPOI.SS.UserModel.HorizontalAlignment.Center, 28))
ltHeader.Add(ltLine)
'第二行
ltLine = New List(Of UW.ExcelPOI.Cell)
ltLine.Add(New UW.ExcelPOI.Cell("期間: " & Me.txtbl_date_s.Text & " ~ " & Me.txtbl_date_e.Text, 10,
NPOI.SS.UserModel.HorizontalAlignment.Left, 20))
ltLine.Add(New UW.ExcelPOI.Cell("製表日期: " & Now.ToString("yyyy-MM-dd"), 6,
NPOI.SS.UserModel.HorizontalAlignment.Right, 20))
ltHeader.Add(ltLine)
UW.ExcelPOI.DTToExcelAndWriteToClient(newdt, ltHeader:=ltHeader)