/// <summary> /// 測試 /// </summary> /// <param name="args"></param> public static void Main(string[] args) { //const string configFile = "C:/workspace/RbtProject/srcExcelOpt/import_sample.xml"; const string configFile = "e:/TESN/ExcelConfig/Import/Import_UnitRawData.xml"; const string configID = "UnitRawData"; const string importFile = "q:/統一編號/utn3386k_xlsx/utn3386K_00.xlsx"; ImportConfigInfo importConfigInfo = (new ImportConfigReader()).read(configFile, configID); IList <Dictionary <string, object> > result; using (StreamReader sr = new StreamReader(importFile)) { result = new ExcelImporter().readXlsx(sr.BaseStream, importConfigInfo); } //Console.Write(result); }
/// <summary> /// 依據ID讀取設定檔案 /// </summary> /// <param name="configFilePath"> 設定檔案完整路徑 </param> /// <param name="configID"> 設定資訊 ID /// @return </param> public ImportConfigInfo read(string configFilePath, string configID) { // ========================================================= // 讀取設定檔案 // ========================================================= XmlDocument document = this.readConfigFile(configFilePath); // ========================================================= // 讀取 ImportConfigInfo // ========================================================= ImportConfigInfo importConfigInfo = this.readExcelInfo(document, configID); // ========================================================= // 讀取 FormatInfo // ========================================================= importConfigInfo.FunctionInfoMap = this.readFunctionInfo(document); // ========================================================= // 讀取 FormatInfo // ========================================================= importConfigInfo.FormatInfoMap = this.readFormatInfo(document); return(importConfigInfo); }
/// <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> /// 讀取 excel 檔案 /// </summary> /// <param name="inStream">Excel 檔案 input Stream (以 StreamReader 讀取檔案)</param> /// <param name="importConfigInfo">設定檔資料</param> /// <param name="isXlsx">是否為 xlsx </param> /// <returns></returns> public IList <Dictionary <string, object> > readXlsx( Stream inStream, ImportConfigInfo importConfigInfo, bool isCloseStream = true) { return(read(inStream, importConfigInfo, true, isCloseStream)); }
/// <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); }