/// <summary>
/// 將 DataTable 資料轉換至 Excel
/// </summary>
/// <param name="thisTable">欲轉換之DataTable</param>
/// <param name="path">檔案放置位置</param>
/// <param name="sheetName">寫入之sheet名稱</param>
/// <returns>失敗時回傳錯誤訊息</returns>
public static string DataTableToExcel(DataTable dt, string path, Excel_DownloadName type, List <FormateTitle> titles = null)
{
string result = string.Empty;
try
{
string version = "2003"; //default 2003
IWorkbook wb = null;
ISheet ws;
string configVersion = ConfigurationManager.AppSettings["ExcelVersion"];
if (!configVersion.IsNullOrWhiteSpace())
{
version = configVersion;
}
//建立Excel 2003檔案
if ("2003".Equals(version))
{
wb = new HSSFWorkbook();
}
if ("2007".Equals(version))
{
wb = new XSSFWorkbook();
}
ws = wb.CreateSheet(type.GetDescription());
ExcelSetValue(ws, dt, type, titles);
FileStream file = new FileStream(path, FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
}
catch (Exception ex)
{
result = ex.Message;
}
finally
{
//關閉文件
//oXL.Quit();
}
return(result);
}
/// <summary>
/// 將 DataTable 資料轉換至 Excel
/// </summary>
/// <param name="thisTable">欲轉換之DataTable</param>
/// <param name="path">檔案放置位置</param>
/// <param name="sheetName">寫入之sheet名稱</param>
/// <returns>失敗時回傳錯誤訊息</returns>
public static string DataTableToExcel(DataTable dt, string path, Excel_DownloadName type, List <FormateTitle> titles = null)
{
string result = string.Empty;
try
{
string version = "2003"; //default 2003
IWorkbook wb = null;
ISheet ws;
string configVersion = ConfigurationManager.AppSettings["ExcelVersion"];
if (!configVersion.IsNullOrWhiteSpace())
{
version = configVersion;
}
//建立Excel 2003檔案
if ("2003".Equals(version))
{
wb = new HSSFWorkbook();
}
if ("2007".Equals(version))
{
wb = new XSSFWorkbook();
}
ws = wb.CreateSheet(type.GetDescription());
ExcelSetValue(ws, dt, type, titles);
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// ws.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// if (sheetName.IndexOf("A7") > -1) //A7 系列
// {
// if (0.Equals(j)) //第一行固定為 string
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
// }
// else //後面皆為 double
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(Convert.ToDouble(dt.Rows[i][j]));
// }
// }
// else
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
// }
// }
//}
FileStream file = new FileStream(path, FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
}
catch (Exception ex)
{
result = ex.Message;
}
finally
{
//關閉文件
//oXL.Quit();
}
return(result);
}
