private int EnterWriteDetail(WritableSheet writableSheet, DetailInfo detailInfo, int targetRowIndex, IList <ColumnDataSet> columnDataSetList, Dictionary <int, HashSet <string> > usedCells, Dictionary <string, int> maxWidthMap) { // 取得欄位設定欄位 List <ColumnInfo> columnInfoList = detailInfo.ColumnInfoList; // 無資料跳出 if (ExcelStringUtil.IsEmpty(detailInfo.ColumnInfoList) || ExcelStringUtil.IsEmpty(columnDataSetList) || columnDataSetList.Count == 0) { return(targetRowIndex); } // 計算 rowspan foreach (ColumnDataSet columnDataSet in columnDataSetList) { countRowspan(columnInfoList, columnDataSet); } // excel 欄位輸出 foreach (ColumnDataSet columnDataSet in columnDataSetList) { targetRowIndex = writeDetail( writableSheet, columnInfoList, columnDataSet, targetRowIndex, usedCells, maxWidthMap); } return(targetRowIndex); }
/// <summary> /// 讀取設定檔 /// </summary> /// <param name="configFilePath"></param> /// <param name="configId"></param> /// <returns></returns> private ImportConfigInfo prepareConfigInfo(string configFilePath, string configId) { // ============================================== // 傳入參數檢核 // ============================================== if (ExcelStringUtil.IsEmpty(configFilePath)) { throw new ExcelOperateException("Excel 檔案匯入設定檔未設定 ! [configFilePath]"); } if (ExcelStringUtil.IsEmpty(configId)) { throw new ExcelOperateException("Excel 檔案匯入設定檔未設定 ! [configID]"); } if (!File.Exists(configFilePath.Trim())) { throw new ExcelOperateException("Excel 檔案匯入設定檔不存在 ! [" + configFilePath.Trim() + "]"); } // ============================================== // 讀取設定檔 // ============================================== return(new ImportConfigReader().read(configFilePath, configId)); }
// ===================================================== // 公用程式 // ===================================================== /// <summary> /// 讀取 Node 中,與 Data Column 類型元素 相關的屬性 /// </summary> /// <param name="node">XmlNode</param> public void readDataColumnAttr(XmlNode node) { if (node == null || node.Attributes == null) { return; } //key Key = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_KEY); //funcId FuncId = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_FUNCID); //FuncParam FuncParam = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_FUNC_PARAM); //colspan Colspan = Convert.ToInt32(ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_COLSPAN, "0")); // defaultValue (先讀取 node, node 無值時, 讀取 attr) XmlNode defaultValueNode = node.SelectSingleNode(Constant.ELEMENT_DEFAULT_VALUE); string defaultValue = ""; if (defaultValueNode != null) { defaultValue = defaultValueNode.InnerText; } if (ExcelStringUtil.IsEmpty(defaultValue) && node.Attributes != null) { defaultValue = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_DEFAULT_VALUE); } DefaultValue = defaultValue; }
/// <summary> /// 檢核Key 是否重複 </summary> /// <param name="keySet"> </param> /// <param name="key"> </param> private void checkKey(HashSet <string> keySet, string key) { if (ExcelStringUtil.IsEmpty(key)) { throw new ExcelOperateException("有<column> 的 key 未設定 (為空)"); } if (keySet.Contains(key)) { throw new ExcelOperateException("<column> key:[" + key + "] 重複設定"); } keySet.Add(key); }
/// <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); } }
// ===================================================== // 元素屬性 // ===================================================== // ===================================================== // 公用程式 // ===================================================== /// <summary> /// 讀取 Node 中,共通屬性 </summary> /// <param name="node"> </param> public virtual void readCommonAttr(XmlNode node) { if (node == null) { return; } // key this.Key = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_KEY); // desc this.Desc = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_DESC); // funcId this.FuncId = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_FUNCID); // index this.Index = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_INDEX); // defaultValue (先讀取 node, node 無值時, 讀取 attr) XmlNode defaultValueNode = node.SelectSingleNode(Constant.ELEMENT_DEFAULT_VALUE); string defaultValue = ""; if (defaultValueNode != null) { defaultValue = defaultValueNode.Value; } if (ExcelStringUtil.IsEmpty(defaultValue)) { defaultValue = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_DEFAULT_VALUE); } this.DefaultValue = defaultValue; // funcParam (先讀取 node, node 無值時, 讀取 attr) XmlNode funcParamNode = node.SelectSingleNode(Constant.ELEMENT_FUNC_PARAM); string funcParam = ""; if (funcParamNode != null) { funcParam = funcParamNode.Value; } if (ExcelStringUtil.IsEmpty(funcParam)) { funcParam = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_FUNC_PARAM); } this.FuncParam = funcParam; }
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> /// 產生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); } }
/// <summary> /// 計算 rowspan /// </summary> /// <param name="columnInfoList"></param> /// <param name="columnDataSet"></param> private int countRowspan(List <ColumnInfo> columnInfoList, ColumnDataSet columnDataSet) { int myRowspan = 0; foreach (ColumnInfo columnInfo in columnInfoList) { // 取得子欄位 List <ColumnDetailInfo> columnDetailInfoList = columnInfo.ColumnDetailInfoList; // 無子欄位設定時略過 if (ExcelStringUtil.IsEmpty(columnDetailInfoList)) { continue; } int currRowAdd = 0; 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)) { // 遞迴處理 currRowAdd += countRowspan(childColumnInfoList, columnDataSet.getSingle(dataId)); } if (string.Equals(type, Constant.ELEMENT_ARRAY, StringComparison.OrdinalIgnoreCase)) { // 取得 array 元素的資料集 List <ColumnDataSet> arrayDataList = columnDataSet.getArray(dataId); if (arrayDataList == null || arrayDataList.Count == 0) { arrayDataList = new List <ColumnDataSet>(); arrayDataList.Add(new ColumnDataSet()); } // 逐筆處理 foreach (ColumnDataSet arrayColumnDataSet in arrayDataList) { // 遞迴處理 currRowAdd += countRowspan(childColumnInfoList, arrayColumnDataSet); } } // 取得最大 rowspan if (currRowAdd > myRowspan) { myRowspan = currRowAdd; } } } // 加上基礎的1列 if (myRowspan == 0) { myRowspan = 1; } foreach (ColumnInfo columnInfo in columnInfoList) { // 取得子欄位 List <ColumnDetailInfo> columnDetailInfoList = columnInfo.ColumnDetailInfoList; // 有子欄位設定時略過 if (ExcelStringUtil.NotEmpty(columnDetailInfoList)) { continue; } // 取得 key string key = columnInfo.Key; // 把 rowspan值 以 key 加上固定前綴之後, 放入 資料MAP try { columnDataSet.ColumnDataMap.Add(KEY_COLUMN_COLSPAN_PERFIX + key, myRowspan); } catch (ArgumentException) { //throw new Exception("detail 的 key :[" + key + "] 重複,請檢查設定檔"); } } return(myRowspan); }
/// <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); }
/// <summary> /// ExportConfigInfo /// </summary> /// <param name="document"> </param> /// <param name="id"></param> private ImportConfigInfo readExcelInfo(XmlDocument document, string id) { // ========================================================= // 讀取設定資訊 // ========================================================= XmlNode excelNode = document.SelectSingleNode( "//" + Constant.ELEMENT_EXCEL + "[@" + Constant.ATTRIBUTE_ID + "=\"" + id + "\"]"); //XmlNode excelNode = document.SelectSingleNode("//" + Constant.ELEMENT_EXCEL + "[" + Constant.ATTRIBUTE_ID + "=\"" + configID + "\"]"); if (excelNode == null) { throw new ExcelOperateException("設定資訊:[" + id + "] 不存在!"); } // ========================================================= // 讀取 excel 標籤屬性 // ========================================================= ImportConfigInfo importConfigInfo = new ImportConfigInfo(); //configID importConfigInfo.Desc = ExcelStringUtil.GetNodeAttr(excelNode, Constant.ATTRIBUTE_ID); //sheetNum string sheetNum = ExcelStringUtil.GetNodeAttr(excelNode, Constant.ATTRIBUTE_SHEETNUM, "1"); if (!ExcelStringUtil.isNumber(sheetNum)) { throw new ExcelOperateException("屬性 sheetNum 設定錯誤! sheetNum:[" + sheetNum + "]"); } importConfigInfo.SheetNum = Convert.ToInt32(sheetNum); //startRow string startRow = ExcelStringUtil.GetNodeAttr(excelNode, Constant.ATTRIBUTE_STARTROW); if (!ExcelStringUtil.isNumber(startRow)) { throw new ExcelOperateException("屬性 startRow 設定錯誤! startRow:[" + startRow + "]"); } importConfigInfo.StartRow = Convert.ToInt32(startRow); //CheckEmptyRow importConfigInfo.CheckEmptyRow = ExcelStringUtil.GetNodeAttr(excelNode, Constant.ATTRIBUTE_CHECK_EMPTY_ROW); //check duplicate importConfigInfo.CheckDuplicate = ExcelStringUtil.GetNodeAttr(excelNode, Constant.ATTRIBUTE_CHECK_DUPLICATE); //desc importConfigInfo.Desc = ExcelStringUtil.GetNodeAttr(excelNode, Constant.ATTRIBUTE_DESC); // ========================================================= // 讀取 excel/read 標籤 下的 column // ========================================================= //讀取 node list XmlNodeList columnNodeList = excelNode.SelectNodes(Constant.ELEMENT_READ + "/" + Constant.ELEMENT_COLUMN); //檢核 if (ExcelStringUtil.IsEmpty(columnNodeList)) { throw new ExcelOperateException("未找到任何 <column> (config/excel/read/column)"); } //收集屬性設定list IList <ColumnInfo> columnInfoList = new List <ColumnInfo>(); importConfigInfo.ColumnInfoList = columnInfoList; //紀錄KEY避免重複 var keySet = new HashSet <string>(); //逐筆讀取 if (columnNodeList != null) { foreach (XmlNode columnNode in columnNodeList) { //未設定 key 代表要略過的欄位 //if (ExcelStringUtil.GetNodeAttr(columnNode, Constant.ATTRIBUTE_KEY, "@@xx") == "@@xx") //{ // continue; //}; //初始化物件 ColumnInfo columnInfo = new ColumnInfo(); //讀取共通屬性 columnInfo.readCommonAttr(columnNode); //FormatId columnInfo.FormatId = ExcelStringUtil.GetNodeAttr(columnNode, Constant.ATTRIBUTE_FORMATID); //regexp columnInfo.Regex = ExcelStringUtil.GetNodeAttr(columnNode, Constant.ATTRIBUTE_REGEX); //RegexErrorMsg columnInfo.RegexErrorMsg = ExcelStringUtil.GetNodeAttr(columnNode, Constant.ATTRIBUTE_REGEX_ERROR_MSG); //isNull columnInfo.CheckNull = ExcelStringUtil.GetNodeAttr(columnNode, Constant.ATTRIBUTE_CHECK_NULL); //pass columnInfo.Pass = "******".Equals(ExcelStringUtil.GetNodeAttr(columnNode, Constant.ATTRIBUTE_PASS), StringComparison.CurrentCultureIgnoreCase); //add to list columnInfoList.Add(columnInfo); //檢核Key 是否重複 this.checkKey(keySet, columnInfo.Key); } } // ========================================================= // 讀取 excel/params 標籤 下的 param // ========================================================= //讀取 node list XmlNodeList paramNodeList = excelNode.SelectNodes(Constant.ELEMENT_PARAMS + "/" + Constant.ELEMENT_PARAM); //收集屬性設定list IList <ParamInfo> paramInfoList = new List <ParamInfo>(); importConfigInfo.ParamInfoList = paramInfoList; //逐筆讀取 if (paramNodeList != null) { foreach (XmlNode paramNode in paramNodeList) { //初始化物件 ParamInfo paramInfo = new ParamInfo(); //讀取共通屬性 paramInfo.readCommonAttr(paramNode); //add to list paramInfoList.Add(paramInfo); //檢核Key 是否重複 this.checkKey(keySet, paramInfo.Key); } } return(importConfigInfo); }