/// <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); }