private void process_Click(object sender, EventArgs e) { var excelExporter = new ExcelExporter(); FileStream fileStream = null; try { console.Text = @"開始執行"; // console.Text += "\r\n讀取設定檔"; ExportConfigInfo exportConfigInfo = ( new ExportConfigReader()).read(txt_configFile.Text, txt_configID.Text); DataTable newDataTable = (DataTable)BaseUtil_GetObjectFromTemp("c:\\Logs\\6.txt", new DataTable().GetType()); //================================================= //準備匯出資料的容器 //================================================= ExportDataSet exportDataSet = new ExportDataSet(); //================================================= //兜組單筆型態欄位資料 (dataId = header) //================================================= Dictionary <string, object> headerData = new Dictionary <string, object>(); //放入資料容器 (設定檔 context id 為 title) exportDataSet.setContext("title", headerData); //================================================= //兜組多筆型態欄位資料 (dataId = detail) //================================================= exportDataSet.setDetailDataTable("detail", newDataTable); console.Text += "\r\n輸出"; fileStream = new FileStream(txt_exportPath.Text + exportConfigInfo.FileName, FileMode.Create); excelExporter.export(exportConfigInfo, perpareTestData(), fileStream); console.Text += "\r\n執行完成"; } catch (Exception ex) { console.Text += "\r\n發生錯誤"; console.Text += ex.StackTrace; } finally { if (fileStream != null) { fileStream.Dispose(); } } }
// =========================================================================== // 參數預設值區 // =========================================================================== /// <summary> /// 產生測試用的資料 /// @return /// </summary> public virtual ExportDataSet perpareTestData() { // ExportDataSet var exportDataSet = new ExportDataSet(); // Detail DataSet List var detailDataSetList = new List <ColumnDataSet>(); exportDataSet.setDetailDataSet("detail", detailDataSetList); // ============================================== // 外框資料 // ============================================== string[] schoolNames = { "莊敬高職", "亞東", "東南", "城市科大", "致理", "耕莘", "康寧" }; foreach (string schoolName in schoolNames) { var columnData = new Dictionary <string, object>(); columnData["SCHOOL_CHN_NAME"] = schoolName; columnData["EMPLOYMENT_RATE"] = "100.00%"; detailDataSetList.Add(perpareTesDataDetail(columnData)); } return(exportDataSet); }
// =========================================================================== // 功能區 // =========================================================================== /// <summary> /// 產生Excel /// </summary> /// <param name="exportConfigInfo"> </param> /// <param name="exportDataSet"> </param> /// <param name="outString"></param> public virtual void export(ExportConfigInfo exportConfigInfo, ExportDataSet exportDataSet, Stream outString) { configInfo = exportConfigInfo; try { // ========================================================= // 建立 Workbook // ========================================================= WritableWorkbook writableWorkbook = Workbook.createWorkbook(outString); for (int sheetlIndex = 0; sheetlIndex < exportConfigInfo.SheetList.Count; sheetlIndex++) { // ===================================================== // 建立 sheet // ===================================================== // 取得 sheetlInfo 設定 SheetlInfo sheetlInfo = exportConfigInfo.SheetList[sheetlIndex]; // 取得 sheetName string sheetName = (ExcelStringUtil.IsEmpty(sheetlInfo.SheetName)) ? "Sheet" + (sheetlIndex + 1) : sheetlInfo.SheetName; // 建立 sheet WritableSheet writableSheet = writableWorkbook.createSheet(sheetName, sheetlIndex); // 版面設定 // setPageSetup Parameters: // p - the page orientation // ps - the paper size // hm - the header margin, in inches // fm - the footer margin, in inches writableSheet.setPageSetup(PageOrientation.LANDSCAPE, exportConfigInfo.PaperSize, 0, 0); writableSheet.getSettings().setLeftMargin(0); writableSheet.getSettings().setRightMargin(0); // ===================================================== // 處理前準備 // ===================================================== // 列指標 int targetRowIndex = 0; // 紀錄已使用的儲存格 (cell) var usedCells = new Dictionary <int, HashSet <string> >(); // 紀錄欄(column)的最大欄寬 var maxWidthMap = new Dictionary <string, int>(); // ===================================================== // 資訊 // ===================================================== foreach (var entry in sheetlInfo.PartInfoMap) { if (entry.Value == null) { return; } // 內容為 context if (entry.Key.StartsWith(Constant.ELEMENT_CONTEXT)) { //取得 context 設定檔設定資料 var contextInfo = (ContextInfo)entry.Value; //取得 匯出資料 Dictionary <string, object> dataMap = exportDataSet.getContext(contextInfo.DataId); targetRowIndex = WriteContext( writableSheet, contextInfo, targetRowIndex, dataMap, usedCells, maxWidthMap); } //內容為 detail if (entry.Key.StartsWith(Constant.ELEMENT_DETAIL)) { //取得 context 設定檔設定資料 var detailInfo = (DetailInfo)entry.Value; //取得 匯出資料 var columnDataSetList = exportDataSet.getDetail(detailInfo.DataId); targetRowIndex = EnterWriteDetail( writableSheet, detailInfo, targetRowIndex, columnDataSetList, usedCells, maxWidthMap); } } // ===================================================== // 設定欄寬 // ===================================================== // 取得最大欄位 index int maxColIndex = maxWidthMap[KEY_MAX_COL]; for (int colIndex = 0; colIndex <= maxColIndex; colIndex++) { // 取得欄寬 int colWidth = 0; //取得 MAP 中的值 (tr、td 設定) if (maxWidthMap.ContainsKey(colIndex + "")) { colWidth = maxWidthMap[colIndex + ""]; } //若 tr td 未設定時,取 style 設定 if (colWidth == 0) { colWidth = Convert.ToInt32(ExcelStringUtil.SafeTrim(exportConfigInfo.StyleInfo.Width, "0")); } // 以上都未設定時使用預設值 //if (colWidth == 0) //{ // colWidth = Convert.ToInt32(Constant.DEFAULT_WIDTH); //} if (colWidth > 0) { writableSheet.setColumnView(colIndex, colWidth); } } } writableWorkbook.write(); writableWorkbook.close(); } catch (Exception e) { throw new ExcelOperateException("EXCEL 檔案匯出處理錯誤! \r\n" + e.Message + "\r\n" + e.StackTrace); } }