/// <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>
/// 下載 Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="type"></param>
/// <param name="path"></param>
/// <param name="data"></param>
/// <returns></returns>
public MSGReturnModel DownLoadExcel <T>(Excel_DownloadName type, string path, List <T> data)
{
MSGReturnModel result = new MSGReturnModel();
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type.download_Fail
.GetDescription(null, Message_Type.not_Find_Any.GetDescription());
if (type == Excel_DownloadName.A95_1)
{
result.DESCRIPTION = FileRelated.DataTableToExcel(data.Cast <A95_1ViewModel>().ToList().ToDataTable(), path, Excel_DownloadName.A95_1, new A95_1ViewModel().GetFormateTitles());
result.RETURN_FLAG = string.IsNullOrWhiteSpace(result.DESCRIPTION);
}
if (type == Excel_DownloadName.A96)
{
result.DESCRIPTION = FileRelated.DataTableToExcel(data.Cast <A96ViewModel>().ToList().ToDataTable(), path, Excel_DownloadName.A96);
result.RETURN_FLAG = string.IsNullOrWhiteSpace(result.DESCRIPTION);
}
return(result);
}
private static void ExcelSetValue(ISheet ws, DataTable dt, Excel_DownloadName type, List <FormateTitle> titles = null)
{
ws.CreateRow(0);//第一行為欄位名稱
for (int i = 0; i < dt.Columns.Count; i++)
{
ws.GetRow(0).CreateCell(i).SetCellValue((dt.Columns[i].ColumnName).formateTitle(titles));
}
if (type == Excel_DownloadName.A59)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
if (type == Excel_DownloadName.A72 || type == Excel_DownloadName.A73)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
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]));
}
}
}
}
}
private static void ExcelSetValue(ISheet ws, DataTable dt, Excel_DownloadName type, List <FormateTitle> titles = null)
{
ws.CreateRow(0);//第一行為欄位名稱
//if (type == Excel_DownloadName.A95)
//{
// for (int i = 0; i < dt.Columns.Count; i++)
// {
// ws.GetRow(0).CreateCell(i).SetCellValue((dt.Columns[i].ColumnName).formateTitle(titles));
// }
//}
if (type == Excel_DownloadName.C07AdvancedSum)
{
ws.GetRow(0).CreateCell(0).SetCellValue(
(dt.Columns[0].ColumnName).formateTitle(titles) + "："
+ dt.Rows[0]["報導日"].ToString()
+ " "
+ (dt.Columns[1].ColumnName).formateTitle(titles) + "："
+ dt.Rows[0]["版本"].ToString()
+ " "
+ "產品："
+ dt.Rows[0]["產品群代碼"].ToString() + " " + dt.Rows[0]["產品群名稱"].ToString()
);
ws.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));
ws.CreateRow(1);
ws.GetRow(1).CreateCell(0).SetCellValue("預期信用損失統計_台幣(報導日匯率)");
ws.AddMergedRegion(new CellRangeAddress(1, 1, 0, 3));
ws.CreateRow(2);
for (int i = 0; i < dt.Columns.Count - 4; i++)
{
ws.GetRow(2).CreateCell(i).SetCellValue((dt.Columns[i + 4].ColumnName).formateTitle(titles));
}
}
else
{
for (int i = 0; i < dt.Columns.Count; i++)
{
ws.GetRow(0).CreateCell(i).SetCellValue((dt.Columns[i].ColumnName).formateTitle(titles));
}
}
if (type == Excel_DownloadName.A59)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
else if (type == Excel_DownloadName.A72 || type == Excel_DownloadName.A73)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
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 if (type == Excel_DownloadName.A95)
//{
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// ws.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count - 2; j++) //Bond_Type,Assessment_Sub_Kind 由程式產生
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
// }
// }
//}
else if (type == Excel_DownloadName.C07Mortgage || type == Excel_DownloadName.C07Bond)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
else if (type == Excel_DownloadName.D62 || type == Excel_DownloadName.D64)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
else if (type == Excel_DownloadName.C07AdvancedSum)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.CreateRow(i + 3);
for (int j = 0; j < dt.Columns.Count - 4; j++)
{
ws.GetRow(i + 3).CreateCell(j).SetCellValue(dt.Rows[i][j + 4].ToString());
}
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
for (int i = 0; i < dt.Columns.Count; i++)
{
ws.AutoSizeColumn(i);
}
}
/// <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);
}
