/// <summary>
///
/// </summary>
/// <param name="columnKey"></param>
/// <param name="funcId"></param>
/// <param name="funcParam"></param>
/// <param name="value"></param>
/// <param name="rowDataMap"></param>
/// <param name="conn"></param>
/// <returns></returns>
/// <exception cref="ExcelOperateException"></exception>
protected string functionProcess(
string columnKey, string funcId, string funcParam,
string value, Dictionary<string, object> rowDataMap, DbConnection conn = null)
{
// 未設定 FuncId 時跳過
if (ExcelStringUtil.IsEmpty(funcId))
{
return value;
}
// 讀取設定
FunctionInfo functionInfo = configInfo.FunctionInfoMap[funcId];
// 檢核
if (functionInfo == null)
{
throw new ExcelOperateException("Excel 處理錯誤,function 設定不存在! funcId:[" + funcId + "]");
}
//取得物件
AbstractExcelOperateFunction functionObject = functionInfo.FunctionObject;
try
{
// 取得固定方法 『Process』
MethodInfo functionMethodInfo = functionObject.GetType().GetMethod("Process");
// 準備傳入參數
Object[] args = { functionInfo.Method, columnKey, funcParam, value, rowDataMap, conn };
//執行方法
Object returnValue = functionMethodInfo.Invoke(functionObject, args);
//回傳
return ExcelStringUtil.SafeTrim(returnValue);
}
catch (ExcelOperateException)
{
//處理過程 catch 過,已轉成ExcelOperateException的, 不再攔截，直接拋出
throw;
}
catch (Exception e)
{
throw new ExcelOperateException(
"Excel 處理錯誤, " +
"\r\ncolumnKey:[" + columnKey + "], " +
"\r\nfuncId:[" + funcId + "], " +
"\r\nfuncParam[" + funcParam + "]," +
"\r\nvalue:[" + value + "]" +
"\r\n" + e.StackTrace);
}
}
public int Compare(AbstractImportCommonAttrInfo info1, AbstractImportCommonAttrInfo info2)
{
string index1 = ExcelStringUtil.SafeTrim(info1.Index);
string index2 = ExcelStringUtil.SafeTrim(info2.Index);
if (ExcelStringUtil.IsEmpty(index1) || !ExcelStringUtil.isNumber(index1))
{
return(1);
}
if (ExcelStringUtil.IsEmpty(index2) || !ExcelStringUtil.isNumber(index2))
{
return(-1);
}
if (ExcelStringUtil.isNumber(index1) && ExcelStringUtil.isNumber(index2))
{
return(Convert.ToInt32(index1) - Convert.ToInt32(index2));
}
return(0);
}
/// <summary>
/// 準備要放入 cell 的資料
/// </summary>
/// <param name="key"> </param>
/// <param name="value"> </param>
/// <param name="funcId"> </param>
/// <param name="funcParam"> </param>
/// <param name="dataMap">
/// @return
/// </param>
private string perpareContent(string key, string value, string funcId, string funcParam,
Dictionary <string, object> dataMap)
{
string content = "";
// KEY 有設定時優先處理
if (dataMap != null && ExcelStringUtil.NotEmpty(key) && dataMap.ContainsKey(key))
{
content = ExcelStringUtil.SafeTrim(dataMap[key]);
}
// 取不到值時,
if (ExcelStringUtil.IsEmpty(content))
{
content = value;
}
// 呼叫處理的 function
if (ExcelStringUtil.NotEmpty(funcId))
{
content = functionProcess(key, funcId, funcParam, value, dataMap, Conn);
}
return(content);
}
/// <summary>
/// 設定 Cell 格式 (Detal時, 沒有 tr 和 td, 此時兩個參數傳入同一個物件, 不影響判斷)
/// </summary>
/// <param name="trInfo" />
/// <param name="tdInfo" />
private WritableCellFormat getCellFormat(AbstractStyleInfo trInfo, AbstractStyleInfo tdInfo)
{
//style 設定
StyleInfo styleInfo = ((ExportConfigInfo)configInfo).StyleInfo;
// 字體名稱
WritableFont.FontName font = styleInfo.Font;
if (tdInfo.Font != null)
{
font = tdInfo.Font;
}
else if (trInfo.Font != null)
{
font = trInfo.Font;
}
// 字體大小
int size = 0;
if (ExcelStringUtil.NotEmpty(tdInfo.Size))
{
size = Convert.ToInt32(ExcelStringUtil.SafeTrim(tdInfo.Size, "0"));
}
else if (ExcelStringUtil.NotEmpty(trInfo.Size))
{
size = Convert.ToInt32(ExcelStringUtil.SafeTrim(trInfo.Size, "0"));
}
if (size == 0)
{
size = Convert.ToInt32(styleInfo.Size);
}
// 粗體
bool isBold = ("true".Equals(styleInfo.Bold, StringComparison.CurrentCultureIgnoreCase));
if (ExcelStringUtil.NotEmpty(tdInfo.Bold))
{
isBold = ("true".Equals(tdInfo.Bold, StringComparison.CurrentCultureIgnoreCase));
}
else if (ExcelStringUtil.NotEmpty(trInfo.Bold))
{
isBold = ("true".Equals(trInfo.Bold, StringComparison.CurrentCultureIgnoreCase));
}
// 斜體
bool isItalic = ("true".Equals(styleInfo.Italic, StringComparison.CurrentCultureIgnoreCase));
if (ExcelStringUtil.NotEmpty(tdInfo.Italic))
{
isItalic = ("true".Equals(tdInfo.Italic, StringComparison.CurrentCultureIgnoreCase));
}
else if (ExcelStringUtil.NotEmpty(trInfo.Bold))
{
isItalic = ("true".Equals(trInfo.Italic, StringComparison.CurrentCultureIgnoreCase));
}
// 底線
UnderlineStyle underlineStyle = styleInfo.Underline;
if (tdInfo.Underline != null)
{
underlineStyle = tdInfo.Underline;
}
else if (trInfo.Underline != null)
{
underlineStyle = trInfo.Underline;
}
// 字體顏色
Colour color = styleInfo.Color;
if (tdInfo.Color != null)
{
color = tdInfo.Color;
}
else if (trInfo.Color != null)
{
color = trInfo.Color;
}
// 水平位置
Alignment align = styleInfo.Align;
if (tdInfo.Align != null)
{
align = tdInfo.Align;
}
else if (trInfo.Align != null)
{
align = trInfo.Align;
}
// 垂直位置
VerticalAlignment valign = styleInfo.Valign;
if (tdInfo.Valign != null)
{
valign = tdInfo.Valign;
}
else if (trInfo.Valign != null)
{
valign = trInfo.Valign;
}
// 文字換行
bool isTextWrap = ("true".Equals(styleInfo.Wrap, StringComparison.CurrentCultureIgnoreCase));
if (ExcelStringUtil.NotEmpty(tdInfo.Wrap))
{
isTextWrap = ("true".Equals(tdInfo.Wrap, StringComparison.CurrentCultureIgnoreCase));
}
else if (ExcelStringUtil.NotEmpty(trInfo.Wrap))
{
isTextWrap = ("true".Equals(trInfo.Wrap, StringComparison.CurrentCultureIgnoreCase));
}
// 邊線位置
Border borderSide = styleInfo.BorderSide;
if (tdInfo.BorderSide != null)
{
borderSide = tdInfo.BorderSide;
}
else if (trInfo.BorderSide != null)
{
borderSide = trInfo.BorderSide;
}
// 邊線樣式
BorderLineStyle borderStyle = styleInfo.BorderStyle;
if (tdInfo.BorderStyle != null)
{
borderStyle = tdInfo.BorderStyle;
}
else if (trInfo.Valign != null)
{
borderStyle = trInfo.BorderStyle;
}
// 背景顏色
Colour background = styleInfo.Background;
if (tdInfo.Background != null)
{
background = tdInfo.Background;
}
else if (trInfo.Background != null)
{
background = trInfo.Background;
}
// 產生字型設定
var writableFont = new WritableFont(font, size, (isBold) ? WritableFont.BOLD : WritableFont.NO_BOLD,
isItalic, underlineStyle, color);
// 資料列cell格式
var writableCellFormat = new WritableCellFormat(writableFont);
// 水平置中
writableCellFormat.setAlignment(align);
// 垂直置中
writableCellFormat.setVerticalAlignment(valign);
// 換行
writableCellFormat.setWrap(isTextWrap);
// 背景顏色
writableCellFormat.setBackground(background);
// 邊線
writableCellFormat.setBorder(borderSide, borderStyle);
return(writableCellFormat);
}
// ===========================================================================
// 功能區
// ===========================================================================
/// <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);
}
}
private int WriteContext(
WritableSheet writableSheet,
ContextInfo contextInfo,
int targetRowIndex,
Dictionary <string, object> dataMap,
Dictionary <int, HashSet <string> > usedCells,
Dictionary <string, int> maxWidthMap)
{
// 無資料時跳出
if (contextInfo.TrInfoList == null)
{
return(targetRowIndex);
}
// 逐列處理
for (int row = 0; row < contextInfo.TrInfoList.Count; row++)
{
//取得 TrInfo
TrInfo trInfo = contextInfo.TrInfoList[row];
// col index 指標
int targetColIndex = 0;
for (int col = 0; col < trInfo.TdInfoList.Count; col++)
{
// 取得 TdInfo
TdInfo tdInfo = trInfo.TdInfoList[col];
// 取得欄位設定
WritableCellFormat cellFormat = getCellFormat(trInfo, tdInfo);
// 取得要放入 cell 的值
string content = perpareContent(tdInfo.Key, tdInfo.DefaultValue, tdInfo.FuncId, tdInfo.FuncParam,
dataMap);
// 取得寬度設定
int width = Convert.ToInt32(ExcelStringUtil.SafeTrim(tdInfo.Width, "0"));
// 取得還未使用的 column
targetColIndex = getUnUsedCol(usedCells, targetRowIndex, targetColIndex);
if (tdInfo.Colspan > 1 || tdInfo.Rowspan > 1)
{
// 合併儲存格
merageCell(writableSheet, usedCells, targetColIndex, targetRowIndex, tdInfo.Colspan,
tdInfo.Rowspan, maxWidthMap, width);
// addCell
//addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap, width, tdInfo.Key);
addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat,
maxWidthMap, width);
// 移動 col 指標
if (tdInfo.Colspan > 0)
{
targetColIndex += tdInfo.Colspan;
}
else
{
targetColIndex++;
}
}
else
{
// addCell
//addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap, width, tdInfo.Key);
addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat,
maxWidthMap, width);
// 移動 col 指標
targetColIndex++;
}
}
// 取得列高設定
//height of the row in 1/20ths of a point
int height = Convert.ToInt32(ExcelStringUtil.SafeTrim(trInfo.Height, "0")) * 20;
if (height > 0)
{
writableSheet.setRowView(targetRowIndex, height);
}
targetRowIndex++;
}
return(targetRowIndex);
}
private int writeDetail(WritableSheet writableSheet, IEnumerable <ColumnInfo> columnInfoList,
ColumnDataSet columnDataSet, int targetRowIndex, Dictionary <int, HashSet <string> > usedCells,
Dictionary <string, int> maxWidthMap)
{
int targetColIndex = 0;
int newTargetRowIndex = targetRowIndex;
foreach (ColumnInfo columnInfo in columnInfoList)
{
// 取得子欄位
List <ColumnDetailInfo> columnDetailInfoList = columnInfo.ColumnDetailInfoList;
// 為子欄位陣列時，進行遞迴處理
if (ExcelStringUtil.NotEmpty(columnDetailInfoList))
{
foreach (ColumnDetailInfo columnDetailInfo in columnDetailInfoList)
{
// 設定元素類別
string type = columnDetailInfo.Type;
// dataId
string dataId = columnDetailInfo.DataId;
// 欄位下的欄位
List <ColumnInfo> childColumnInfoList = columnDetailInfo.ColumnInfoList;
// ELEMENT_SINGLE
if (string.Equals(type, Constant.ELEMENT_SINGLE, StringComparison.OrdinalIgnoreCase))
{
// 遞迴處理
newTargetRowIndex = writeDetail(
writableSheet, childColumnInfoList,
columnDataSet.getSingle(dataId),
newTargetRowIndex, usedCells, maxWidthMap);
}
if (string.Equals(type, Constant.ELEMENT_ARRAY, StringComparison.OrdinalIgnoreCase))
{
// 取得 array 元素的資料集
List <ColumnDataSet> arrayDataList = columnDataSet.getArray(dataId);
// 逐筆處理
foreach (ColumnDataSet arrayColumnDataSet in arrayDataList)
{
// 遞迴處理
newTargetRowIndex = writeDetail(writableSheet, childColumnInfoList, arrayColumnDataSet,
newTargetRowIndex, usedCells, maxWidthMap);
}
}
}
continue;
}
// 取得 key
string key = columnInfo.Key;
// 取得欄位設定
WritableCellFormat cellFormat = getCellFormat(columnInfo, columnInfo);
// 取得要放入 cell 的值
string content = perpareContent(key, columnInfo.DefaultValue, columnInfo.FuncId, columnInfo.FuncParam,
columnDataSet.ColumnDataMap);
// 取得寬度設定
int width = Convert.ToInt32(ExcelStringUtil.SafeTrim(columnInfo.Width, "0"));
// 取得還未使用的 column
targetColIndex = getUnUsedCol(usedCells, targetRowIndex, targetColIndex);
// 取得 rowspan (之前已計算好)
if (!columnDataSet.ColumnDataMap.ContainsKey(KEY_COLUMN_COLSPAN_PERFIX + key))
{
throw new Exception("指定的索引鍵不在字典中 [" + key + "]");
}
var rowspan = (int)columnDataSet.ColumnDataMap[KEY_COLUMN_COLSPAN_PERFIX + key];
// colspan
int colspan = columnInfo.Colspan;
if (colspan > 1 || rowspan > 1)
{
// 合併儲存格
merageCell(writableSheet, usedCells, targetColIndex, targetRowIndex, colspan, rowspan, maxWidthMap,
width);
// addCell
//addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap, width, key);
addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap,
width);
// 移動 col 指標
if (colspan > 0)
{
targetColIndex += colspan;
}
else
{
targetColIndex++;
}
}
else
{
// addCell
//addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap, width, key);
addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap,
width);
// 移動 col 指標
targetColIndex++;
}
}
targetRowIndex++;
// newTargetRowIndex++;
return((targetRowIndex > newTargetRowIndex) ? targetRowIndex : newTargetRowIndex);
}
/// <summary>
///
/// </summary>
/// <param name="inStream"></param>
/// <param name="importConfigInfo"></param>
/// <param name="isXlsx"></param>
/// <param name="isCloseStream"></param>
/// <returns></returns>
private IList <Dictionary <string, object> > parseXsl(
Stream inStream, ImportConfigInfo importConfigInfo,
bool isXlsx, bool isCloseStream = true)
{
IWorkbook wb = null;
//以複製 Stream 方式使用, 避免 inStream 需要被重用
using (MemoryStream memstream = new MemoryStream())
{
inStream.CopyTo(memstream);
memstream.Position = 0; // <-- Add this, to make it work
if (isXlsx)
{
//2010以上格式
wb = new XSSFWorkbook(memstream);
}
else
{
wb = new HSSFWorkbook(memstream);
}
}
inStream.Position = 0;
// 錯誤訊息
string blankLineMessage = "";
// 欄位資料List
IList <Dictionary <string, object> > dataRowList = new List <Dictionary <string, object> >();
// ==============================================
// 設定檔資料
// ==============================================
// 起始行數
int startRow = importConfigInfo.StartRow;
// Sheet 名稱
int sheetNum = importConfigInfo.SheetNum;
if (sheetNum < 1)
{
throw new ExcelOperateException(" sheetNum 屬性設定錯誤, 不可小於 1");
}
// 取得欄位讀取設定
IList <ColumnInfo> columnInfoList = importConfigInfo.ColumnInfoList;
try
{
// ==============================================
// 讀取檔案資料
// ==============================================
ISheet sheet = null;
try
{
// 讀取 sheet
sheet = wb.GetSheetAt(sheetNum - 1);
}
catch (Exception e)
{
throw new ExcelOperateException("檔案解析失敗", e);
}
if (sheet == null)
{
throw new ExcelOperateException(" 檔案解析錯誤，第[" + sheetNum + "]個 sheet 不存在");
}
// ==============================================
// 讀取行
// ==============================================
// 取得行數
int lastRowNum = sheet.LastRowNum;
// 檢核無內容
if (lastRowNum - startRow < 0)
{
throw new ExcelOperateException("上傳檔案無資料內容! rows[" + lastRowNum + "], startRow:[" + startRow + "]");
}
// 檢核是否以下都是空行
bool tailBlankLine = false;
for (int rowNum = (startRow - 1); rowNum <= lastRowNum; rowNum++)
{
// 取得 row (列)
var row = sheet.GetRow(rowNum);
if (row == null)
{
continue;
}
// 資料MAP
Dictionary <string, object> dataRowMap = new Dictionary <string, object>();
//
bool isBlankline = true;
for (int colNum = 0; colNum < columnInfoList.Count; colNum++)
{
// 取得欄位參數設定
ColumnInfo columnInfo = columnInfoList[colNum];
string cellContent = "";
//長度足夠時才讀取 cell
if ((colNum + 1) <= row.Cells.Count)
{
var cell = row.Cells[colNum];
//// 取得 cell 內容 (並去空白)
////如果是數字型 就要取 NumericCellValue 這屬性的值
//if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
//{
// cellContent = StringUtil.SafeTrim(cell.NumericCellValue);
//}
////如果是字串型 就要取 StringCellValue 這屬性的值
//else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
//{
// cellContent = StringUtil.SafeTrim(cell.StringCellValue);
//}
cellContent = ExcelStringUtil.SafeTrim(GetFormattedCellValue(cell));
}
// 不為空時異動 flag
if (ExcelStringUtil.NotEmpty(cellContent))
{
isBlankline = false;
}
// 為空時，放入預設值
if (ExcelStringUtil.IsEmpty(cellContent))
{
cellContent = columnInfo.DefaultValue;
}
// 依據Key放入放入 map
dicPut(dataRowMap, columnInfo.Key, cellContent);
}
// 本行無資料時
if (isBlankline)
{
// 尾部空行標記
tailBlankLine = true;
// 空行訊息
blankLineMessage += "第" + (rowNum + 1) + "行為空行，請重新檢查資料";
// 空行略過 加入List
continue;
}
tailBlankLine = false;
// 加入List
dataRowList.Add(dataRowMap);
}
// ==============================================
// 檢核
// ==============================================
// 1.排除以下都是空行
// 2.有設定需檢核空行
// 3.錯誤訊息不為空
if (!tailBlankLine &&
"true".Equals(importConfigInfo.CheckEmptyRow, StringComparison.CurrentCultureIgnoreCase) &&
ExcelStringUtil.NotEmpty(blankLineMessage))
{
throw new ExcelOperateException("資料內容有誤!\n" + blankLineMessage);
}
return(dataRowList);
}
finally
{
if (inStream != null && isCloseStream)
{
inStream.Dispose();
}
}
}
/// <summary>
/// 讀取 excel 檔案
/// </summary>
/// <param name="inStream">Excel 檔案 input Stream (以 StreamReader 讀取檔案)</param>
/// <param name="importConfigInfo">設定檔資料</param>
/// <param name="isXlsx">是否為 xlsx </param>
/// <param name="isCloseStream">是否自動關閉 inStream (多 sheet 用)</param>
/// <returns></returns>
public IList <Dictionary <string, object> > read(
Stream inStream, ImportConfigInfo importConfigInfo,
bool isXlsx = false, bool isCloseStream = true)
{
this.configInfo = importConfigInfo;
// ==============================================
// 讀取檔案內容
// ==============================================
IList <Dictionary <string, object> > dataList = this.parseXsl(inStream, importConfigInfo, isXlsx, isCloseStream);
// ==============================================
// 建立以 key 為引索的 設定 map, 與收集欄位list
// ==============================================
// Map
Dictionary <string, AbstractImportCommonAttrInfo> paramInfoMap =
new Dictionary <string, AbstractImportCommonAttrInfo>();
// List
List <AbstractImportCommonAttrInfo> paramInfoList = new List <AbstractImportCommonAttrInfo>();
foreach (ColumnInfo columnInfo in importConfigInfo.ColumnInfoList)
{
dicPut(paramInfoMap, columnInfo.Key, columnInfo);
paramInfoList.Add(columnInfo);
}
foreach (ParamInfo paramInfo in importConfigInfo.ParamInfoList)
{
dicPut(paramInfoMap, paramInfo.Key, paramInfo);
paramInfoList.Add(paramInfo);
}
// ==============================================
// param (其他額外設定欄位)
// ==============================================
foreach (Dictionary <string, object> rowDataMap in dataList)
{
foreach (ParamInfo paramInfo in importConfigInfo.ParamInfoList)
{
// 取得預設值
string defaultValue = paramInfo.DefaultValue;
// 存入 map
dicPut(rowDataMap, paramInfo.Key, defaultValue);
}
}
// ==============================================
// 欄位值額外處理
// ==============================================
string errorMessage = "";
int rowNum = importConfigInfo.SheetNum;
foreach (Dictionary <string, object> rowDataMap in dataList)
{
foreach (ColumnInfo columnInfo in importConfigInfo.ColumnInfoList)
{
// 取得欄位 key
string key = columnInfo.Key;
// 取得欄位說明
string desc = columnInfo.Desc;
// 取得值
string value = ExcelStringUtil.SafeTrim(rowDataMap[key]);
// =======================================
// 資料檢核
// =======================================
// 該欄位已檢核出錯誤時, 不繼續進行檢核
// 有限定欄位不可為空時,進行資料檢查
if ("true".Equals(columnInfo.CheckNull, StringComparison.CurrentCultureIgnoreCase) && ExcelStringUtil.IsEmpty(value))
{
errorMessage += "<br/>第" + rowNum + "行, 欄位：【" + desc + "." + key + "】資料內容不可為空";
continue;
}
// 有設定 formatId 時,進行資料檢查
if (ExcelStringUtil.NotEmpty(columnInfo.FormatId))
{
errorMessage += this.validateDataByFormatId(columnInfo.FormatId, value, key, desc, rowNum);
continue;
}
// 有設定 regex 時,進行資料檢查
if (ExcelStringUtil.NotEmpty(columnInfo.Regex))
{
errorMessage += this.validateData(value, columnInfo.Regex, key, desc, rowNum, columnInfo.RegexErrorMsg);
continue;
}
}
rowNum++;
}
// 有檢核到錯誤時，拋出
if (ExcelStringUtil.NotEmpty(errorMessage))
{
throw new ExcelOperateException(errorMessage);
}
// ==============================================
// 欄位值額外處理
// ==============================================
foreach (Dictionary <string, object> rowDataMap in dataList)
{
foreach (KeyValuePair <string, AbstractImportCommonAttrInfo> infoEntry in paramInfoMap)
{
// key
string key = infoEntry.Key;
// 資料值
string value = ExcelStringUtil.SafeTrim(rowDataMap[key]);
// 設定檔
AbstractImportCommonAttrInfo info = infoEntry.Value;
// 進行額外處理
value = this.functionProcess(key, info.FuncId, info.FuncParam, value, rowDataMap, this.Conn);
// 放回資料
dicPut(rowDataMap, key, value);
}
}
// ==============================================
// 檢核資料重覆
// ==============================================
// 未設定時跳出
if (ExcelStringUtil.NotEmpty(importConfigInfo.CheckDuplicate))
{
// 檢核欄位陣列
string[] duplicateColumns = importConfigInfo.CheckDuplicate.Split(',');
// 檢核欄位值
Dictionary <string, int> duplicateValueStoreMap = new Dictionary <string, int>();
// 重置 rowNum
rowNum = 1;
foreach (Dictionary <string, object> cellMap in dataList)
{
string keyContent = "";
// 組成 key
foreach (string checkColumnKey in duplicateColumns)
{
keyContent += ExcelStringUtil.SafeTrim(cellMap[checkColumnKey], "NULL") + "_";
}
// 檢核同樣的內容是否已存在
if (duplicateValueStoreMap.ContainsKey(keyContent))
{
int num = duplicateValueStoreMap[keyContent];
string errormessage = "";
foreach (string checkColumnKey in duplicateColumns)
{
errormessage += "【" + paramInfoMap[checkColumnKey].Desc + "." +
paramInfoMap[checkColumnKey].Key + "】:[" + cellMap[checkColumnKey] +
"]<br/>";
}
throw new ExcelOperateException(
"<br/>第[" + num + "]行資料與第[" + (rowNum + importConfigInfo.StartRow) + "]行重複!(鍵值)<br/>" + errormessage);
}
duplicateValueStoreMap[keyContent] = rowNum + importConfigInfo.StartRow;
rowNum++;
}
}
// ==============================================
// 欄位List 排序
// ==============================================
paramInfoList.Sort(new ComparatorAnonymousInnerClassHelper(this));
// ==============================================
// 資料欄位重新排序
// ==============================================
IList <Dictionary <string, object> > sortDataList = new List <Dictionary <string, object> >();
foreach (Dictionary <string, object> rowDataMap in dataList)
{
Dictionary <string, object> sortRowDataMap = new Dictionary <string, object>();
foreach (AbstractImportCommonAttrInfo commonAttrInfo in paramInfoList)
{
string key = commonAttrInfo.Key;
dicPut(sortRowDataMap, key, rowDataMap[key]);
}
sortDataList.Add(sortRowDataMap);
}
dataList = sortDataList;
// ==============================================
// 移除不需要的欄位
// ==============================================
foreach (Dictionary <string, object> rowDataMap in dataList)
{
foreach (ColumnInfo columnInfo in importConfigInfo.ColumnInfoList)
{
// 欄位設定為 pass 時，移除該欄位
if (columnInfo.Pass)
{
rowDataMap.Remove(columnInfo.Key);
}
}
}
return(dataList);
}
