NPOI读取excel

来源：互联网发布：100本网络禁书网盘下载编辑：程序博客网时间：2024/06/06 21:37

/// <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))
            {
                FileStream stream = new FileStream(filePath, FileMode.Open);
                HSSFWorkbook workbook = new HSSFWorkbook(stream);
                stream.Close();

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;
                            string val = cell.DateCellValue.ToString("yyyy/MM/dd HH:mm:ss");
                            if (val.EndsWith(" 00:00:00"))
                            {
                                return cell.DateCellValue.ToString("yyyy/MM/dd");
                            }
                            return val;
                        }
                        else if (cellValue.StartsWith("."))
                        {
                            return Convert.ToDecimal(cellValue).ToString();
                        }
                    }
                    catch
                    {
                   }
                }

return cellValue;
}

return "";
}

#endregion
}

0 0