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