於程式中存取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
Leave a Reply