筆記-Excel轉DataTable的NPOI簡單範例
30 | 92,543 |
這篇主要是寫給自己備忘的,以下為使用NPOI讀取表格Excel檔,自動轉成DataTable的簡單範例: (NPOI是什麼? 可參考舊文)
排版顯示純文字
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
public class NPOIHelper
{
public static DataTable ReadExcelAsTableNPOI(string fileName)
{
using (FileStream fs = new FileStream(fileName, FileMode.Open))
{
HSSFWorkbook wb = new HSSFWorkbook(fs);
Sheet sheet = wb.GetSheetAt(0);
DataTable table = new DataTable();
//由第一列取標題做為欄位名稱
Row headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
//以欄位文字為名新增欄位,此處全視為字串型別以求簡化
table.Columns.Add(
new DataColumn(headerRow.GetCell(i).StringCellValue));
//略過第零列(標題列),一直處理至最後一列
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
Row row = sheet.GetRow(i);
if (row == null) continue;
DataRow dataRow = table.NewRow();
//依先前取得的欄位數逐一設定欄位內容
for (int j = row.FirstCellNum; j < cellCount; j++)
if (row.GetCell(j) != null)
//如要針對不同型別做個別處理,可善用.CellType判斷型別
//再用.StringCellValue, .DateCellValue, .NumericCellValue...取值
//此處只簡單轉成字串
dataRow[j] = row.GetCell(j).ToString();
table.Rows.Add(dataRow);
}
return table;
}
}
}
範例假設Excel第一列視為標題列,讀取各欄位值當成DataTable的Column名稱,建立欄位後,逐一讀取各列的資料新增為Row,其中欄位內容一律視為字串處置。
排版顯示純文字
<%@ Page Language="C#" %>
<script type="text/C#" runat="server">
void Page_Load(object sender, EventArgs e)
{
System.Data.DataTable t = NPOIHelper.ReadExcelAsTableNPOI(
Server.MapPath("~/App_Data/Koobe.xls"));
GridView1.DataSource = t;
GridView1.DataBind();
}
</script>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<body>
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</form>
</body>
</html>
補充一點,因為Excel檔案中儲存的是資料的原值加格式、或是計算公式內容,並非套用格式及運算後的最終結果(這點在之前有說明過),故可能與Excel中看到的有點誤差。如下例中的第一列備註就由2011/5/12變成5/12/11,在應用時需留意。
Comments
# by Weslly
暗黑你好 有個問題想跟你討教一下 你好 我想跟你討論一下 我使用NPOI讀取DataTable並匯出已經成功了 但現在要把這個檔案夾帶為附檔寄出,但會有兩個問題 1.因為我程式attachment為string型態,這個string為範本檔路徑,但是寄出後的內容是空的,可能是因為它匯出並不會去修改到範本檔,而我又直接抓範本檔,所以為空 有辦法抓到匯出後的檔嗎? 因為attachment直接抓本機範本檔一定會抓到空的,而不是已讀取DataTable並匯出的檔 2.我有嘗試將attachment的型態,呼叫mail function所帶入的值也改變為其它型態,但也不行 Thanks
# by Jeffrey
to Weslly, 由你的描述,我猜想你程式的目標是由DataTable物件讀取資料,開啟預先設計好格式的Excel檔(所謂的範本),再一一對指定Cell填入資料,接著作為郵件附檔寄出?那麼我想你缺少了一個將填好內Excel另存成檔案的步驟,我有另一篇範例(http://blog.darkthread.net/post-2010-12-16-npoi-forceformularecalculation.aspx)你可以參考看看。若以上猜測沒有命中你的需求,就要請你再多提供一些細節(例如: 程式碼),方便大家給進一步的建議。
# by Weslly
To Jeffrey: 沒錯,這正是我的需求 而且看了你給我的一篇範例後 發現原來是我沒有另存檔案 = =" 好蠢 真的很感謝你 以後有問題找不到或是卡關太久再跟你討教一下 我有一些參考資料都是從你這取得的 真的獲益良多,感覺你蠻利害的而且經驗豐富 我正在慢慢累積經驗中... Thanks Weslly
# by arthas989
您好,請教一個小細節 最後需要 wb = null; sheet = null; 嗎? 對於這些比較基礎的概念不太了解,再您見諒,謝謝!! 再次感謝您的分享。
# by mis2000lab
現在都是找知名Blog來貼留言(同一篇留言,到處貼),請人幫忙 Debug。為何不是去論壇跟大家討論呢? 在論壇上,可以公佈Code不是更好討論嗎? 越來越不懂這些人是用什麼心態、什麼方法來學習?
# by Jeffrey
to arthas989, .NET使用的是Garbage Collection的記憶體管理機制,變數一超出範圍就會被判為無用,等待回收,跟VB時代不同,並不需要特別設成Nothing/null。但如果物件有涉及Unmanged Resource(非由.NET管轄的資源,例如: 資料庫連線、硬碟上的檔案、網路連線等),最好用using包起來,或使用完畢強制呼叫Dispose()。相關的細節還很多,以上只是帶出一些關鍵字,建議你可以找本C#或VB.NET語言的基本入門書來看,比較容易理解。
# by Weslly
Hi Jeffrey 請問一下 為什麼輸出後 原本儲存格的框線或去掉一半或是背景顏色被更改?? 我使用NPOI 1.2.1 原本要使用1.2.3 來調格式~但輸出會有問題 改用1.2.4 輸入後變檔案錯誤 = = Thanks Weslly
# by cheng
不好意思 請問一下 using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; 為何我這兩個密名空間 都無法使用???
# by Jeffrey
to cheng, 無法使用是指using那一列出現錯誤無法編譯嗎? 專案是否有參照NPOI.dll, NPOI.HPSF.dll等程式庫?
# by JerryH
//試用後發現這裡要做個小強化,不然Excel有一欄空白時會爆炸 if (headerRow.GetCell(i) != null) table.Columns.Add( new DataColumn(headerRow.GetCell(i).StringCellValue)); else//null 則放空白 table.Columns.Add( new DataColumn(""));
# by Hin
你好; 請問一下可以把其中一個cell的value 取出呢? 即是如果我input D3 那如何可以把D3 內旳資料提出? 因為我見上面的code是要把整row提取的 謝謝
# by Jeffrey
to Hin, 推薦你改用ClosedXML,可使用"D3", "A1:A3"等Excel語法選取指定範圍: http://blog.darkthread.net/post-2012-12-28-closedxml.aspx
# by JerryH
Bug通報 簵例這一行要改成i<=,不然EXCEL會漏掉最後一列 //略過第零列(標題列),一直處理至最後一列 for (int i = (sheet.FirstRowNum + 1); i < = sheet.LastRowNum; i++)
# by Jeffrey
to JerryH, 感謝指正,已修改。
# by Ken
您好, 我想請問一下, 使用NPOI將Excel轉為DataTable時, 若欄位的型態為TimeSpan (e.g. 12:11:09), 但NPOI會將其當做DateTime去處理, 將DateTime塞入TimeSpan的欄位會出現Exception, 想請問有沒有人有遇過跟我一樣的問題呢?
# by Belle
想請教,NPOI讀取xls檔案時,有時會發生:oledb外部資料表不是預期的格式的錯誤,必須要另存新檔才行。請問有方式可以解嗎? 謝謝!!
# by Jeffrey
to Belle,沒遇過,而比較離奇的是「外部資料表不是預期的格式的錯誤」(External table is not in the expected format)多半發生在使用OLEDB開啟Excel,沒聽過發生在NPOI開啟時。有辦法提供可以重現問題的程式及檔案組合嗎?
# by Travis
黑暗大您好 小弟最近在研究NPOI讀excel並轉成Data Table 剛好找到您的文章 有用您的程式碼來測試 發現一些小問題 不知道是不是您寫這篇文章的時間點的NPOI版本不一樣 現在的版本似乎要在原文中的這兩行程式碼要做強制轉型的修改 Sheet sheet = wb.GetSheetAt(0); Row headerRow = sheet.GetRow(0); 變成 Sheet sheet = (HSSFSheet)wb.GetSheetAt(0); Row headerRow = (HSSFSheet)sheet.GetRow(0); 才不會出現錯誤訊息。 另外想請問您一個我遇到的錯誤訊息: /**發生 I/O 錯誤,例如無法讀取或寫入檔案時,就會擲回 IOException。**/ 這是MSDN上顯示的, 我的目標excel檔,是每次執行程式的時候,會產生一個新的檔 檔名會是產生的時間,因此每次要轉成data table的檔會因為產出時間不同而不同,在目標路徑上,因此我有加入一個時間變數在using (FileStream fs = new FileStream(fileName, FileMode.Open)) 這邊我改為 string time = DateTime.Now.ToString("yyyyMMddHHmm"); string excelPath = @"D:\Temp\" + time + ".xls";//出貨單檔案位址 FileStream fsnpoi = File.Open(excelPath , FileMode.Open); HSSFWorkbook workbook = new HSSFWorkbook(fsnpoi); 然後就跑出剛剛提到的錯誤訊息,想請問這種情況該如何解決呢 感謝您
# by Jeffrey
to Travis, 程式在ASP.NET裡執行嗎? 會不會是沒有D:\TEMP目錄的寫入權限?另外,我現在已經很少用NPOI了,處理Excel的新歡是ClosedXML http://blog.darkthread.net/post-2012-12-28-closedxml.aspx
# by Travis
後來我發現 我的excel檔 是個包著.cls的.txt.... 所以NPOI應該就是判斷出並不是個純excel 而只是改副檔名,看起來是.xls檔而以XD 感謝您的答覆 小弟又得到一個新的知識 感謝您^^
# by Danny
黑暗大大您好: 想像您請問一下,我用這NPOI再匯入EXCEL資料時,發現如果EXCEL檔案的原始欄位的格式為"日期"或"自訂格式(yyyy/mm/dd)",再取到的內容會變成中文(例如 : 原始資料為2017/9/23,NPOI讀取到的資料卻為23-九月-2017),不知道有沒有辦法可以取到原始沒變更到內容呢?(原始資料為2017/9/23,NPOI讀到的就要為2017/9/23),我需要先判斷原始資料的長度,再做後續的處理,不知道有沒有辦法呢? Bset Wishes
# by Jeffrey
to Danny, 我已棄用NPOI多年,近年多改用ClosedXML: blog.darkthread.net/post-2012-12-28-closedxml.aspx 但建議可從CellType、DateCellValue下手,參考: https://stackoverflow.com/a/40558050/288936
# by Danny
黑暗大大,謝謝您的回覆,但其實我在row.GetCell(j)取到的值就已經是中文字了,您所提議的DateCellValue是在取完資料後的判斷,但我卻是要在row.GetCell(j)取到的內容就想要是原始的資料 ,不知道是否有方法可以解決? 想請問您,如果使用ClosedXML,可以解決這問題嗎? Best Wishes
# by Jeffrey
to Danny, 我有興趣想知道ClosedXML是否能克服這問題,能提供一個簡單可重現問題的Excel樣本嗎? (只需一個日期欄位,並確定用NPOI讀不出來)
# by Danny
黑暗大大 謝謝您 我可以提供 但不知道要則麼寄給您 ?
# by Jeffrey
to Danny, 可透過Dropbox, OneDrive公開分享檔案,或者 ge.tt 這類可抛式服務也是選擇: http://ge.tt/6xU1Dcm2
# by Danny
謝謝黑暗大大, 我放在OneDrive, 這是我的帳號wefu****0@yahoo.com.tw, 檔案名稱為:"日期格式.xlsx"
# by Jeffrey
to Danny, OneDrive要透過專屬連結的方式共享檔案,做法請參考: https://www.facebook.com/darkthread.net 發訊息與我連絡
# by Danny
黑暗大大, 謝謝您, 再給您一次網址, 希望您可以使用 ^O^ https://1drv.ms/f/s!As3xzkqL-C3eab1_9RZka8dg9cE 還有抱歉, 很少用雲端分享, 所以很多都還不懂 ~"~
# by Jeffrey
to Danny,實測結果請見新文 http://blog.darkthread.net/post-2017-09-23-get-date-value-by-closedxml.aspx