NPOI - 讀寫Excel檔案

NPOI可在沒有安裝Microsoft Office的環境讀寫Excel檔案。

讀取

VB (展開)

Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel

Dim file As New FileStream("excel_file.xlsx", FileMode.Open) '開啟Excel檔案
Dim xlBook As XSSFWorkbook = New XSSFWorkbook(file) '取得活頁簿的instance
Dim xlSheet As XSSFSheet = xlBook.GetSheetAt(0) '取得第一個工作表

For row As Integer = 0 To xlSheet.LastRowNum
    Dim xlRow As XSSFRow = xlSheet.GetRow(row) '取得每一列

    For col As Integer = 0 To xlRow.LastCellNum – 1
        Dim xlCell As XSSFCell = xlRow.GetCell(col) '取得目前列的每個儲存格
        Dim value As String

        '取得儲存格的值
        If xlCell.CellType = CellType.Numeric Then
            If DateUtil.IsCellDateFormatted(xlCell) Then
                value = xlCell.DateCellValue.ToString() '日期格式
            Else
                value = xlCell.NumericCellValue.ToString() '數值格式
            End If

        ElseIf xlCell.CellType = CellType.String Then
            value = xlCell.StringCellValue '字串格式
        End If
    Next
Next

file.Close()
xlBook = Nothing

C# (展開)

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

FileStream file = new FileStream("excel_file.xlsx", FileMode.Open); //開啟Excel檔案
XSSFWorkbook xlBook = new XSSFWorkbook(file); //取得活頁簿的instance
XSSFSheet xlSheet = xlBook.GetSheetAt(0) as XSSFSheet; //取得第一個工作表

for(int row = 0; row <= xlSheet.LastRowNum; row++)
{
    XSSFRow xlRow = xlSheet.GetRow(row) as XSSFRow; //取得每一列

    for(int col = 0; col < xlRow.LastCellNum; col++)
    {
        XSSFCell xlCell = xlRow.GetCell(col) as XSSFCell; //取得目前列的每個儲存格
        string value;

        //取得儲存格的值
        if(xlCell.CellType == CellType.Numeric)
        {
            if(DateUtil.IsCellDateFormatted(xlCell))
                value = xlCell.DateCellValue.ToString(); //日期格式
            else
                value = xlCell.NumericCellValue.ToString(); //數值格式
        }
        else if(xlCell.CellType == CellType.String)
        {
            value = xlCell.StringCellValue; //字串格式
        }
    }
}

file.Close();
xlBook = null;

寫入 (文字、圖片)

▾VB

Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel

Dim xlBook As XSSFWorkbook = New XSSFWorkbook() '建立活頁簿的instance
Dim xlSheet As XSSFSheet = xlBook.CreateSheet("sheet_name") '建立新工作表
Dim drawing as XSSFDrawing = xlSheet.CreateDrawingPatriarch()

'儲存格格式設定
Dim xlStyle As XSSFCellStyle = xlBook.CreateCellStyle() '建立儲存格的格式設定
xlStyle.Alignment = HorizontalAlignment.Center '置中對齊

'寫入10 x 10的表格資料，最後一行為圖片
For row As Integer = 0 To 9
    Dim xlRow As XSSFRow = xlSheet.CreateRow(row) '建立新的一列

    For col As Integer = 0 To 9
        With xlRow.CreateCell(col) '在目前列建立每個儲存格
            If col = 9 Then
                Dim data As Byte() = File.ReadAllBytes(String.Format("image_{0}.png", row + 1)) '讀取圖檔
                Dim picIndex As Integer = xlBook.AddPicture(data, XSSFWorkbook.PICTURE_TYPE_PNG)
                Dim anchor As XSSFClientAnchor = New XSSFClientAnchor(0, 0, 0, 0, col, row, 0, 0) '定位圖片要插入的儲存格位置
                Dim pic As XSSFPicture = drawing.CreatePicture(anchor, picIndex)
                pic.Resize(1) '參數帶1為縮放至儲存格大小，不帶參數會維持原圖片大小

            Else
                .SetCellValue("row " & row & " col " & col) '把值寫入儲存格
            End If

            .CellStyle = xlStyle '設定儲存格格式
        End With
    Next
Next

Dim file As New FileStream("excel_file.xlsx", FileMode.Create) '建立Excel檔案
xlBook.Write(file) '將活頁簿內容寫入Excel檔案
file.Close()
xlBook = Nothing

儲存格格式設定

▾VB

Imports NPOI.HSSF.Util
Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel

Dim xlBook As XSSFWorkbook = New XSSFWorkbook()
Dim xlStyle As XSSFCellStyle = xlBook.CreateCellStyle()
Dim xlFont As XSSFFont = xlBook.CreateFont()
Dim xlFormat As XSSFDataFormat = xlBook.CreateDataFormat()

'字型設定
xlFont.FontName = "Arial" '字型
xlFont.FontHeightInPoints = 12 '字型大小
xlFont.Color = HSSFColor.Blue.Index '字型顏色
xlFont.Boldweight = FontBoldWeight.Bold '粗體
xlStyle.SetFont(xlFont)

'外框
xlStyle.BorderTop = BorderStyle.Thin
xlStyle.BorderBottom = BorderStyle.Thin
xlStyle.BorderLeft = BorderStyle.Thin
xlStyle.BorderRight = BorderStyle.Thin

'置中對齊
xlStyle.Alignment = HorizontalAlignment.Center

'資料格式
xlStyle.DataFormat = xlFormat.GetFormat("yyyy/MM/dd") '日期資料以 年/月/日 寫入
xlStyle.DataFormat = xlFormat.GetFormat("0.00") '數值資料寫入到小數第二位

'背景顏色
xlStyle.FillForegroundColor = HSSFColor.Yellow.Index
xlStyle.FillPattern = FillPattern.SolidForeground

'背景顏色：自訂色彩
Dim colorRgb = New Byte() {255, 255, 0}
xlStyle.SetFillForegroundColor(New XSSFColor(colorRgb))
xlStyle.FillPattern = FillPattern.SolidForeground

 
多種格式時，將重複的部分先設定成一種格式，然後用CloneStyleFrom複製此格式再增加其他設定，這樣可以減少程式行數。

▾VB

Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel

Dim xlBook As XSSFWorkbook = New XSSFWorkbook()
Dim xlStyle As XSSFCellStyle = xlBook.CreateCellStyle()
Dim xlBackStyle As XSSFCellStyle = xlBook.CreateCellStyle()

'格式1：外框
xlStyle.BorderTop = BorderStyle.Thin
xlStyle.BorderBottom = BorderStyle.Thin
xlStyle.BorderLeft = BorderStyle.Thin
xlStyle.BorderRight = BorderStyle.Thin

'格式2：外框 + 置中對齊
xlBackStyle.CloneStyleFrom(xlStyle)
xlBackStyle.Alignment = HorizontalAlignment.Center