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