c#NPOI讀取excel 比interop和Microsoft.Jet.OLEDB.4.0 之類 的好的多

標籤：xlsx   今天   程式   odata   http   taf   getc   c#   book   

今天下午開始整理excel這塊, 微軟弄的那些庫簡直是個坑, 什麼com註冊之類的淨是些報錯.

在網上搜資料偶然碰見npoi ,好東西,值得使用

NPOI是指構建在POI 3.x版本之上的一個程式，NPOI可以在沒有安裝Office的情況下對Word或Excel文檔進行讀寫操作。NPOI是一個開源的C#讀寫Excel、WORD等微軟OLE2組件文檔的項目。https://github.com/tonyqus/npoi 我是在項目裡邊nuget 裡直接搜npoi  安裝直接就能用了網上一個比較好的程式

using System;using System.Data;using System.IO;using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;using NPOI.HSSF.UserModel;namespace MakeData{    public class ReadExcelHelper    {        /// <summary>        /// 將excel匯入到datatable        /// </summary>        /// <param name="filePath">excel路徑</param>        /// <param name="isColumnName">第一行是否是列名</param>        /// <returns>返回datatable</returns>        public static DataTable ExcelToDataTable(string filePath, bool isColumnName)        {            DataTable dataTable = null;            FileStream fs = null;            DataColumn column = null;            DataRow dataRow = null;            IWorkbook workbook = null;            ISheet sheet = null;            IRow row = null;            ICell cell = null;            int startRow = 0;            try            {                using (fs = File.OpenRead(filePath))                {                    // 2007版本                    if (filePath.IndexOf(".xlsx") > 0)                        workbook = new XSSFWorkbook(fs);                    // 2003版本                    else if (filePath.IndexOf(".xls") > 0)                        workbook = new HSSFWorkbook(fs);                    if (workbook != null)                    {                        sheet = workbook.GetSheetAt(0);//讀取第一個sheet，當然也可以迴圈讀取每個sheet                        dataTable = new DataTable();                        if (sheet != null)                        {                            int rowCount = sheet.LastRowNum;//總行數                            if (rowCount > 0)                            {                                IRow firstRow = sheet.GetRow(0);//第一行                                int cellCount = firstRow.LastCellNum;//列數                                //構建datatable的列                                if (isColumnName)                                {                                    startRow = 1;//如果第一行是列名，則從第二行開始讀取                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                                    {                                        cell = firstRow.GetCell(i);                                        if (cell != null)                                        {                                            if (cell.StringCellValue != null)                                            {                                                column = new DataColumn(cell.StringCellValue);                                                dataTable.Columns.Add(column);                                            }                                        }                                    }                                }                                else                                {                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                                    {                                        column = new DataColumn("column" + (i + 1));                                        dataTable.Columns.Add(column);                                    }                                }                                //填充行                                for (int i = startRow; i <= rowCount; ++i)                                {                                    row = sheet.GetRow(i);                                    if (row == null) continue;                                    dataRow = dataTable.NewRow();                                    for (int j = row.FirstCellNum; j < cellCount; ++j)                                    {                                        cell = row.GetCell(j);                                        if (cell == null)                                        {                                            dataRow[j] = "";                                        }                                        else                                        {                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)                                            switch (cell.CellType)                                            {                                                case CellType.Blank:                                                    dataRow[j] = "";                                                    break;                                                case CellType.Numeric:                                                    short format = cell.CellStyle.DataFormat;                                                    //對時間格式（2015.12.5、2015/12/5、2015-12-5等）的處理                                                    if (format == 14 || format == 31 || format == 57 || format == 58)                                                        dataRow[j] = cell.DateCellValue;                                                    else                                                        dataRow[j] = cell.NumericCellValue;                                                    break;                                                case CellType.String:                                                    dataRow[j] = cell.StringCellValue;                                                    break;                                            }                                        }                                    }                                    dataTable.Rows.Add(dataRow);                                }                            }                        }                    }                }                return dataTable;            }            catch (Exception ex)            {                string mess = ex.Message;                if (fs != null)                {                    fs.Close();                }                return null;            }        }    }}

 

 

c#NPOI讀取excel 比interop和Microsoft.Jet.OLEDB.4.0 之類 的好的多