30

這篇主要是寫給自己備忘的，以下為使用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, 檔案名稱為:&quot;日期格式.xlsx&quot;

# 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

Post a comment