NPOI讀取Excel檔

来源：互联网发布：易游网络验证通杀器编辑：程序博客网时间：2024/05/18 17:42

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace WebDB.Common.Utility
{
    /// <summary>
    /// 轉換輔助型別
    /// </summary>
    public static class ConvertUtility
    {

#region 使用 NPOI 方式匯出Excel

        /// <summary>
        /// 轉換Excel資料至DataTable
        /// </summary>
        /// <param name="filePath">Excel檔案路徑</param>
        /// <returns>轉換Excel資料至DataTable</returns>
        public static DataTable ExcelToDataTableByNPOI(string filePath)
        {
            DataSet dsData = ExcelToDataSetByNPOI(filePath);

            if (dsData.Tables.Count > 0)
            {
                return dsData.Tables[0];
            }

return new DataTable();
}

        /// <summary>
        /// 轉換Excel資料至DataSet
        /// </summary>
        /// <param name="filePath">Excel檔案路徑</param>
        /// <returns>轉換Excel資料至DataSet</returns>
        public static DataSet ExcelToDataSetByNPOI(string filePath)
        {
            DataSet dsData = new DataSet();

            if (File.Exists(filePath))
            {
                using (FileStream stream = new FileStream(filePath, FileMode.Open))
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(stream);

short dateFormat = workbook.CreateDataFormat().GetFormat("yyyy/MM/dd HH:mm:ss");

                    for (int idx = 0, count = workbook.NumberOfSheets; idx < count; idx++)
                    {
                        ISheet sheet = workbook.GetSheetAt(idx);

                        if (sheet != null)
                        {
                            DataTable table = new DataTable();

IRow headerRow = sheet.GetRow(0);

                            if (headerRow != null)
                            {
                                int cellCount = headerRow.LastCellNum;

                                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                                {
                                    DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());

table.Columns.Add(column);
}

                                for (int i = (sheet.FirstRowNum + 1), rowCount = sheet.LastRowNum; i <= rowCount; i++)
                                {
                                    IRow row = sheet.GetRow(i);

DataRow dataRow = table.NewRow();

                                    if (row != null)
                                    {
                                        for (int j = row.FirstCellNum; j < cellCount; j++)
                                        {
                                            ICell cell = row.GetCell(j);

                                            if (cell != null)
                                            {
                                                dataRow[j] = ParseNPOICellValue(cell, dateFormat);
                                            }
                                        }
                                    }
                                    else
                                    {
                                        for (int j = 0; j < cellCount; j++)
                                        {
                                            dataRow[j] = "";
                                        }
                                    }

table.Rows.Add(dataRow);
}

dsData.Tables.Add(table);

                                sheet = null;
                            }
                        }
                    }

                    workbook = null;
                }
            }

return dsData;
}

        /// <summary>
        /// 依欄位資料型態轉換成適當字串
        /// </summary>
        /// <param name="cell">欄位</param>
        /// <param name="dateFormat">日期格式</param>
        /// <returns>欄位資料</returns>
        private static string ParseNPOICellValue(ICell cell, short dateFormat)
        {
            string cellValue = cell.ToString();

            if (!cellValue.IsEmpty())
            {
                if (cell.CellType == CellType.NUMERIC)
                {
                    try
                    {
                        if (HSSFDateUtil.IsCellDateFormatted(cell))
                        {
                            cell.CellStyle.DataFormat = dateFormat;
                            return cell.DateCellValue.ToString("yyyy/MM/dd HH:mm:ss");
                        }
                    }
                    catch
                    {
                        Log.Log.Write(String.Format("解析欄位資料發生錯誤:{0}({1})",
                            cellValue,
                            cell.DateCellValue));
                    }
                }

return cellValue;
}

return "";
}

#endregion
}
}