之前有推薦過這篇好文章 -- [在 Server 端存取 Excel 檔案的利器:NPOI Library]
小弟改寫成 VB語法,這裡用的是 NPOI v1.21版 for .NET 2.0
比對原文的範例順序來改寫,一共改了四個。
如果您有需要就拿去參考看看。
之前有推薦過這篇好文章
在 Server 端存取 Excel 檔案的利器:NPOI Library
http://msdn.microsoft.com/zh-tw/ee818993.aspx
因為上文採用C#來寫範例。
前兩天在論壇上,看見有人要VB語法的範例
小弟改寫成 VB語法,也分享給大家。
謝謝網路上的朋友 熱心分享
.....................................................................................................................................
我是比對原文的範例順序來改寫,一共改了四個。
如果您有需要就拿去參考看看。
請務必依照上面文章的指引
先把 NPOI Library 1.21 for .NET 2.0安裝起來(在 Visual Studio之中 加入參考)
範例一: 下列的 ASP.NET 程式碼可以生成一個空白的 Excel 檔案,並且添加三個指定名稱的試算表
02 Imports NPOI.HSSF.UserModel
03 Imports NPOI.HPSF
04 Imports NPOI.POIFS.FileSystem
05
06
07 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
08 '== 本範例的資料來源:http://msdn.microsoft.com/zh-tw/ee818993.aspx
09
10 Dim workbook As HSSFWorkbook = New HSSFWorkbook()
11 Dim ms As MemoryStream = New MemoryStream() '==需要 System.IO命名空間
12
13 '== 新增試算表。
14 '== 生成一個空白的 Excel 檔案,並且添加三個指定名稱的試算表 Sheet
15 workbook.CreateSheet("試算表 Sheet A")
16 workbook.CreateSheet("試算表 Sheet B")
17 workbook.CreateSheet("試算表 Sheet C")
18
19 workbook.Write(ms)
20 '== Excel檔名,請寫在最後面 filename的地方
21 Response.AddHeader("Content-Disposition", String.Format("attachment; filename=你的檔名.xls"))
22 Response.BinaryWrite(ms.ToArray())
23
24 '== 釋放資源
25 workbook = Nothing '== C#為 null
26 ms.Close()
27 ms.Dispose()
28 End Sub
範例二: 添加一個指定名稱的試算表,在裡面添加資料
02 Imports NPOI.HSSF.UserModel
03 Imports NPOI.HPSF
04 Imports NPOI.POIFS.FileSystem
05
06
07 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
08 '== 本範例的資料來源:http://msdn.microsoft.com/zh-tw/ee818993.aspx
09
10 Dim workbook As HSSFWorkbook = New HSSFWorkbook()
11 Dim ms As MemoryStream = New MemoryStream() '==需要 System.IO命名空間
12
13 '== 新增試算表 Sheet名稱。
14 Dim u_sheet As HSSFSheet = workbook.CreateSheet("My Sheet")
15
16 '== 插入資料值。
17 u_sheet.CreateRow(0).CreateCell(0).SetCellValue("0000")
18 u_sheet.CreateRow(1).CreateCell(0).SetCellValue("1111")
19 u_sheet.CreateRow(2).CreateCell(0).SetCellValue("2222")
20 u_sheet.CreateRow(3).CreateCell(0).SetCellValue("3333")
21 u_sheet.CreateRow(4).CreateCell(0).SetCellValue("4444")
22 u_sheet.CreateRow(5).CreateCell(0).SetCellValue("5555")
23
24 u_sheet.CreateRow(6).CreateCell(1).SetCellValue("6666") '== CreateCell() 可設定為同一列(Row)的 [第幾個格子]
25
26
27 workbook.Write(ms)
28
29 '== Excel檔名,請寫在最後面 filename的地方
30 Response.AddHeader("Content-Disposition", String.Format("attachment; filename=你的檔名.xls"))
31 Response.BinaryWrite(ms.ToArray())
32
33 '== 釋放資源
34 workbook = Nothing '== C#為 null
35 ms.Close()
36 ms.Dispose()
37 End Sub
範例三: 設定儲存格的背景色
02 Imports NPOI.HSSF.UserModel
03 Imports NPOI.HPSF
04 Imports NPOI.POIFS.FileSystem
05
06
07 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
08 '== 本範例的資料來源:http://msdn.microsoft.com/zh-tw/ee818993.aspx
09
10 Dim workbook As HSSFWorkbook = New HSSFWorkbook()
11 Dim ms As MemoryStream = New MemoryStream() '==需要 System.IO命名空間
12
13 '== 新增試算表 Sheet名稱。
14 Dim u_sheet As HSSFSheet = workbook.CreateSheet("My Sheet")
15
16 '== 建立儲存格樣式(底色)。
17 Dim style1 As HSSFCellStyle = workbook.CreateCellStyle()
18 style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index2 '==藍色底的儲存格
19 style1.FillPattern = HSSFCellStyle.SOLID_FOREGROUND
20
21 Dim style2 As HSSFCellStyle = workbook.CreateCellStyle()
22 style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2 '==黃色底的儲存格
23 style2.FillPattern = HSSFCellStyle.SOLID_FOREGROUND
24
25
26 '== 設定儲存格樣式與資料。
27 Dim cell As HSSFCell = u_sheet.CreateRow(0).CreateCell(0)
28 cell.CellStyle = style1
29 cell.SetCellValue("0000") '== 插入資料值。
30
31 cell = u_sheet.CreateRow(1).CreateCell(0)
32 cell.CellStyle = style2
33 cell.SetCellValue("1111") '== 插入資料值。
34
35 cell = u_sheet.CreateRow(2).CreateCell(0)
36 cell.CellStyle = style1
37 cell.SetCellValue("2222") '== 插入資料值。
38
39 cell = u_sheet.CreateRow(3).CreateCell(0)
40 cell.CellStyle = style2
41 cell.SetCellValue("3333") '== 插入資料值。
42
43 cell = u_sheet.CreateRow(4).CreateCell(0)
44 cell.CellStyle = style1
45 cell.SetCellValue("4444") '== 插入資料值。
46
47
48 workbook.Write(ms)
49 '== Excel檔名,請寫在最後面 filename的地方
50 Response.AddHeader("Content-Disposition", String.Format("attachment; filename=你的檔名.xls"))
51 Response.BinaryWrite(ms.ToArray())
52
53 '== 釋放資源
54 workbook = Nothing '== C#為 null
55 ms.Close()
56 ms.Dispose()
57 End Sub
範例四: 把手邊的 Excel檔案上傳(透過 FileUpload控制項),轉換成 DataTable
並轉換成 GridView呈現在 Web畫面上
這個範例的難度略高了一些,
我加上自己的註解(但,不保證正確),希望對您有幫助
02 Imports System.Data '-- DataTable會用到
03
04 Imports NPOI
05 Imports NPOI.HPSF
06 Imports NPOI.HSSF
07 Imports NPOI.HSSF.UserModel
08 Imports NPOI.POIFS
09 Imports NPOI.POIFS.FileSystem
10 Imports NPOI.Util
11
12
13 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
14 '== 本範例的資料來源:http://msdn.microsoft.com/zh-tw/ee818993.aspx
15 '==先把上傳的 Excel資料檔案,讀取到 DataTable裡面。
16
17 '-- 註解:先設定好檔案上傳的路徑,這是Web Server電腦上的目錄。
18 Dim savePath As String = "C:\temp\uploads\"
19
20 If (FileUpload1.HasFile) Then
21 Dim fileName As String = FileUpload1.FileName
22
23 savePath = savePath & fileName
24 FileUpload1.SaveAs(savePath)
25
26 Label1.Text = "上傳成功,檔名---- " & fileName
27 '--------------------------------------------------
28 '---- 上傳的部分,成功運作!
29 '--------------------------------------------------
30
31
32
33 Dim workbook As HSSFWorkbook = New HSSFWorkbook(FileUpload1.FileContent) '==只能讀取 System.IO.Stream
34
35 '-- FileContent 屬性會取得指向要上載之檔案的 Stream 物件。這個屬性可以用於存取檔案的內容 (做為位元組)。
36 ' 例如,您可以使用 FileContent 屬性傳回的 Stream 物件,將檔案的內容做為位元組進行讀取並將其以位元組陣列儲存。
37 '-- FileContent 屬性,型別:System.IO.Stream
38 '-- http://msdn.microsoft.com/zh-tw/library/system.web.ui.webcontrols.fileupload.filecontent.aspx
39
40 Dim u_sheet As HSSFSheet = workbook.GetSheetAt(0) '-- 0表示:第一個 worksheet工作表
41
42 Dim D_table As DataTable = New DataTable()
43
44 Dim headerRow As HSSFRow = u_sheet.GetRow(0) '-- Excel 表頭列
45
46 For k As Integer = headerRow.FirstCellNum To (headerRow.LastCellNum - 1) '-- 表頭列,共有幾個 "欄位"?(取得最後一欄的數字)
47 '-- 把上傳的 Excel「表頭列」,每一欄位都寫入 DataTable裡面
48 If Not headerRow.GetCell(k) Is Nothing Then
49 Dim D_column As DataColumn = New DataColumn(headerRow.GetCell(k).StringCellValue)
50 D_table.Columns.Add(D_column)
51 End If
52 Next
53
54
55 '-- For迴圈的「啟始值」要加一,表示不包含 Excel表頭列
56 For i As Integer = (u_sheet.FirstRowNum + 1) To u_sheet.LastRowNum '-- 每一列做迴圈
57 Dim row As HSSFRow = u_sheet.GetRow(i) '--不包含 Excel表頭列的 "其他資料列"
58 Dim D_dataRow As DataRow = D_table.NewRow()
59
60 For j As Integer = row.FirstCellNum To (row.LastCellNum - 1) '-- 每一個欄位做迴圈
61 If Not row.GetCell(j) Is Nothing Then
62 D_dataRow(j) = row.GetCell(j).ToString() '--每一個欄位,都加入同一列 DataRow
63 End If
64 Next
65 D_table.Rows.Add(D_dataRow)
66 Next
67
68 '-- 釋放 NPOI的資源
69 workbook = Nothing
70 u_sheet = Nothing
71
72 Dim D_View As DataView = New DataView(D_table)
73
74 GridView1.DataSource = D_View
75 GridView1.DataBind()
76
77
78
79 Else
80 Label1.Text = "???? ...... 請先挑選檔案之後,再來上傳"
81 End If
82
83 End Sub
2010/5/24補充: 如果您用來上傳的 Excel檔案,在欄位(Cell)或是資料列(Row)有空白的話。
如下圖,可能會導致輸出的畫面不正確。 因為上面的範例,是一格一格、一列一列去讀取。
如果遇見空白,可能會認為是結束。
2010/8/26補充:謝謝網友 瑪姬 提供下列建議
想要產出excel時, 當出現windows save as 視窗時, 檔案類型預設為 HTML,
要再加一行下面的字, 才會預設為 xls,
我同事試出來的結果, 他的經驗是2003不用設, 2005一定要, 礙於公司server的問題所以2010還沒試,
我想也許可加在文章上, 可以讓網友參考時少了許多測試時間,
Response.ContentType="application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", String.Format("attachment; filename=你的檔名.xls"))
謝謝每一位分享的朋友,
「分享」讓智慧愈加地發光
............................................................................................................................................................................
(1). 如果您不會使用 FileUpload控制項,可以參考這幾篇文章 --
http://www.dotblogs.com.tw/mis2000lab/archive/2010/01/13/npoi_excel_asp_net.aspx
(2). 以前透過傳統的方法,要輸出或是讀取 Excel
比對起 NPOI,相形之下,傳統方法真是麻煩,可以參考這幾篇文章 --
http://www.dotblogs.com.tw/mis2000lab/Tags/Excel/default.aspx
2011/12/1 補充 : 範例由此下載(請注意 版本差異):
[NPOI 1.2.4版][下載]ASP.NET 輸出 Excel,範例下載。
我將思想傳授他人, 他人之所得,亦無損於我之所有;
猶如一人以我的燭火點燭,光亮與他同在,我卻不因此身處黑暗。----Thomas Jefferson
線上課程教學,遠距教學 (Web Form 約 51hr) https://dotblogs.com.tw/mis2000lab/2016/02/01/aspnet_online_learning_distance_education_VS2015
線上課程教學,遠距教學 (ASP.NET MVC 約 140hr) https://dotblogs.com.tw/mis2000lab/2018/08/14/ASPnet_MVC_Online_Learning_MIS2000Lab
寫信給我,不要私訊 -- mis2000lab (at) yahoo.com.tw 或 school (at) mis2000lab.net
(1) 第一天 ASP.NET MVC5 完整影片(5.5小時 / .NET 4.x版)免費試聽。影片 https://youtu.be/9spaHik87-A
(2) 第一天 ASP.NET Core MVC 完整影片(3小時 / .NET Core 6.0~8.0)免費試聽。影片 https://youtu.be/TSmwpT-Bx4I
[學員感言] mis2000lab課程評價 - ASP.NET MVC , WebForm 。 https://mis2000lab.medium.com/%E5%AD%B8%E5%93%A1%E6%84%9F%E8%A8%80-mis2000lab%E8%AA%B2%E7%A8%8B%E8%A9%95%E5%83%B9-asp-net-mvc-webform-77903ce9680b
ASP.NET遠距教學、線上課程(Web Form + MVC)。 第一天課程, "完整" 試聽。
......... facebook社團 https://www.facebook.com/mis2000lab ......................
......... YouTube (ASP.NET) 線上教學影片 https://www.youtube.com/channel/UC6IPPf6tvsNG8zX3u1LddvA/
Blog文章 "附的範例" 無法下載,請看 https://dotblogs.com.tw/mis2000lab/2016/03/14/2008_2015_mis2000lab_sample_download
請看我們的「售後服務」範圍(嚴格認定)。
......................................................................................................................................................
ASP.NET MVC => .NET Core MVC 線上教學 ...... 第一天課程 完整內容 "免費"讓您評估 / 試聽
[遠距教學、教學影片] ASP.NET (Web Form) 課程 上線了!MIS2000Lab.主講 事先錄好的影片,並非上課側錄! 觀看時,有如「一對一」面對面講課。