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