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