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
}
}