[NPOI] 讀取Excel File

於程式中存取Excel File作大量數據的import / export 是正常事. 然而之前總須要於電腦中安裝Microsoft Office 才可以透過CreateObject() 進行. 在它將了Document standard 轉成XML後, 坊間已經有library 可以存取檔案內容, 不用再令伺服器如此擁腫.

NPOI 是一套以POI 為基礎的Library, 它可以存取Word / Excel / PowerPoint / Outlook / Visio 及Publisher 檔案. 因為工作需要, 所以只研究了Excel 方面. 在示範中會讀取Excel 檔案並將內容顯示在DataGrid 內.

MainWindow.xaml

<Window x:Class="MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:PoC.NPOI.Excel.VB"
        mc:Ignorable="d"
        Title="MainWindow" Height="350" Width="525">
    <Window.Resources>
        <Style TargetType="{x:Type Control}">
            <Setter Property="Margin" Value="10" />
        </Style>
        <Style TargetType="Button">
            <Setter Property="Width" Value="120" />
            <Setter Property="Margin" Value="10" />
        </Style>
    </Window.Resources>
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto" />
            <RowDefinition Height="*" />
        </Grid.RowDefinitions>
        <StackPanel Grid.Row="0">
            <Grid>
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="Auto" />
                    <ColumnDefinition Width="*" />
                </Grid.ColumnDefinitions>
                <StackPanel Grid.Column="0">
                    <Button Width="100" Name="btnImport" Click="btnImport_Click">Import</Button>
                </StackPanel>
                <StackPanel Grid.Column="1" Orientation="Horizontal">
                    <TextBlock Text="Path: "/>
                    <TextBlock Name="txtPath" />
                </StackPanel>
            </Grid>
        </StackPanel>
        <DataGrid Grid.Row="1" Name="dgFileContent" HorizontalAlignment="Stretch" ItemsSource="{Binding }" />
    </Grid>
</Window>

MainWindow.xaml.vb

Imports System.Data
Imports System.IO
Imports Microsoft.Win32
Imports NPOI.HSSF.UserModel
Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel

Class MainWindow

    Public dataTable As DataTable

    Private Sub btnImport_Click(sender As Object, e As RoutedEventArgs)
        ' Create Dialog and get input file.
        Dim openFileDialog As OpenFileDialog = New OpenFileDialog() With
        {
            .Filter = "Excel 97-2000 Files (*.xls; *.xlsx)|*.xls; *.xlsx|Comma spearated Files (*.csv)|*.csv",
            .Multiselect = False
        }
        Dim openResult As Boolean?
        openResult = openFileDialog.ShowDialog(Me)

        ' Get file content and update datagrid.
        If (openResult.HasValue) And (openResult) Then
            ' Update text block.
            Me.txtPath.Text = openFileDialog.FileName

            Using fileStream As FileStream = openFileDialog.OpenFile()
                ' Open file and get first sheet.
                Dim excelWorkBook As IWorkbook
                Select Case Path.GetExtension(openFileDialog.FileName).ToUpper()
                    Case ".XLSX"
                        excelWorkBook = New XSSFWorkbook(fileStream)
                    Case Else
                        excelWorkBook = New HSSFWorkbook(fileStream)
                End Select

                Dim excelSheet As ISheet = excelWorkBook.GetSheetAt(0)

                If (excelSheet IsNot Nothing) Then
                    dataTable = New DataTable()
                    ' Create column for datatable.
                    Dim refRow As IRow = excelSheet.GetRow(0)
                    For c = refRow.FirstCellNum To refRow.LastCellNum - 1
                        Select Case refRow.GetCell(c).CellType
                            Case CellType.Numeric
                                dataTable.Columns.Add(New DataColumn("Header" & c, GetType(Decimal)))
                            Case CellType.String
                                dataTable.Columns.Add(New DataColumn("Header" & c, GetType(String)))
                            Case Else
                                dataTable.Columns.Add(New DataColumn("Header" & c, GetType(String)))
                        End Select
                    Next

                    ' Get each row and column data and add to DataGrid.
                    For r = 0 To excelSheet.LastRowNum - 1
                        Dim excelRow As IRow = excelSheet.GetRow(r)
                        Dim dataRow As DataRow = dataTable.NewRow()

                        For c = 0 To excelRow.LastCellNum - 1
                            dataRow(c) = excelRow.GetCell(c)
                        Next
                        dataTable.Rows.Add(dataRow)
                    Next
                    dgFileContent.DataContext = dataTable.DefaultView
                End If
            End Using
        End If
    End Sub
End Class