/// <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));
}
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>
/// 讀取 function 標籤設定
/// </summary>
/// <param name="document"> XmlDocument </param>
/// <returns></returns>
protected Dictionary <string, FunctionInfo> readFunctionInfo(XmlDocument document)
{
// =========================================================
// 讀取 function 設定
// =========================================================
XmlNodeList functionNodeList = document.SelectNodes("//" + Constant.ELEMENT_FUNCTION);
// =========================================================
// 解析 NODE 設定
// =========================================================
var functionInfoMap = new Dictionary <string, FunctionInfo>();
if (functionNodeList == null)
{
return(functionInfoMap);
}
foreach (XmlNode funcNode in functionNodeList)
{
var functionInfo = new FunctionInfo();
if (funcNode.Attributes == null)
{
continue;
}
functionInfo.FuncId = ExcelStringUtil.GetNodeAttr(funcNode, Constant.ATTRIBUTE_FUNCID);
functionInfo.ClassName = ExcelStringUtil.GetNodeAttr(funcNode, Constant.ATTRIBUTE_CLASSNAME);
functionInfo.Method = ExcelStringUtil.GetNodeAttr(funcNode, Constant.ATTRIBUTE_METHOD);
functionInfoMap.Add(functionInfo.FuncId, functionInfo);
}
return(functionInfoMap);
}
/// <summary>
/// 讀取 format 標籤設定 (正規表示式檢核設定)
/// </summary>
/// <param name="document"> XmlDocument </param>
/// <returns></returns>
protected Dictionary <string, FormatInfo> readFormatInfo(XmlDocument document)
{
// =========================================================
// 讀取 function 設定
// =========================================================
XmlNodeList formatNodeList = document.SelectNodes("//" + Constant.ELEMENT_FORMAT);
// =========================================================
// 解析 NODE 設定
// =========================================================
var formatInfoMap = new Dictionary <string, FormatInfo>();
if (formatNodeList == null)
{
return(formatInfoMap);
}
foreach (XmlNode funcNode in formatNodeList)
{
var formatInfo = new FormatInfo();
if (funcNode.Attributes == null)
{
continue;
}
formatInfo.FormatId = ExcelStringUtil.GetNodeAttr(funcNode, Constant.ATTRIBUTE_FORMATID);
formatInfo.Regex = ExcelStringUtil.GetNodeAttr(funcNode, Constant.ATTRIBUTE_REGEX);
formatInfo.RegexErrorMsg = ExcelStringUtil.GetNodeAttr(funcNode, Constant.ATTRIBUTE_REGEX_ERROR_MSG);
formatInfoMap.Add(formatInfo.FormatId, formatInfo);
}
return(formatInfoMap);
}
// =====================================================
// 公用程式
// =====================================================
/// <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>
/// 解析 sheet 層的設定
/// </summary>
/// <param name="partNode">子項目名稱</param>
private List <TrInfo> readContextInfo(XmlNode partNode)
{
List <TrInfo> trInfoList = new List <TrInfo>();
// 未設定時返回
if (partNode == null)
{
return(trInfoList);
}
// 取得 tr node list
XmlNodeList trNodeList = partNode.SelectNodes(Constant.ELEMENT_TR);
// 未設定時返回
if (trNodeList == null || trNodeList.Count == 0)
{
return(trInfoList);
}
foreach (XmlNode trNode in trNodeList)
{
TrInfo trInfo = new TrInfo();
// 讀取 style 屬性設定
trInfo.readStyleAttr(trNode);
// 取得 TD 設定 list 設定
XmlNodeList tdNodeList = trNode.SelectNodes(Constant.ELEMENT_TD);
// 檢核
if (tdNodeList == null || tdNodeList.Count == 0)
{
throw new ExcelOperateException("<tr> 標籤下, 不可無 <td> 設定!");
}
// 取得TD 設定
List <TdInfo> tdInfoList = new List <TdInfo>();
foreach (XmlNode tdNode in tdNodeList)
{
TdInfo tdInfo = new TdInfo();
// 讀取rowspan 屬性 (TdInfo 獨有)
if (tdNode.Attributes != null)
{
tdInfo.Rowspan = Convert.ToInt32(ExcelStringUtil.GetNodeAttr(tdNode, Constant.ATTRIBUTE_ROWSPAN, "0"));
}
// 讀取資料元素設定
tdInfo.readDataColumnAttr(tdNode);
// 讀取 style 屬性設定
tdInfo.readStyleAttr(tdNode);
tdInfoList.Add(tdInfo);
}
trInfo.TdInfoList = tdInfoList;
trInfoList.Add(trInfo);
}
return(trInfoList);
}
/// <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);
}
}
private List <ColumnInfo> readDetailInfo(XmlNode partNode)
{
List <ColumnInfo> columnInfoList = new List <ColumnInfo>();
// 取得 column node list
XmlNodeList columnList = partNode.SelectNodes(Constant.ELEMENT_COLUMN);
foreach (XmlNode columnNode in columnList)
{
ColumnInfo columnInfo = new ColumnInfo();
// 讀取資料元素設定
columnInfo.readDataColumnAttr(columnNode);
// 讀取 style 屬性設定
columnInfo.readStyleAttr(columnNode);
// 取得子元素list
XmlNodeList columnDeatilNodList = columnNode.SelectNodes("(" + Constant.ELEMENT_ARRAY + "|" + Constant.ELEMENT_SINGLE + ")");
// column(array|single)[遞迴]
List <ColumnDetailInfo> columnDetailInfoList = new List <ColumnDetailInfo>();
if (columnDeatilNodList != null)
{
foreach (XmlNode columnDetailNode in columnDeatilNodList)
{
ColumnDetailInfo columnDetailInfo = new ColumnDetailInfo();
// type
columnDetailInfo.Type = columnDetailNode.Name;
// dataId
columnDetailInfo.DataId = ExcelStringUtil.GetNodeAttr(columnDetailNode, Constant.ATTRIBUTE_DATAID);
// column
var xmlNodeList = columnDetailNode.SelectNodes(Constant.ELEMENT_COLUMN);
if (xmlNodeList != null && xmlNodeList.Count > 0)
{
columnDetailInfo.ColumnInfoList = this.readDetailInfo(columnDetailNode);
}
// add to list
columnDetailInfoList.Add(columnDetailInfo);
}
}
columnInfo.ColumnDetailInfoList = columnDetailInfoList;
columnInfoList.Add(columnInfo);
}
return(columnInfoList);
}
// =====================================================
// 元素屬性
// =====================================================
// =====================================================
// 公用程式
// =====================================================
/// <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>
/// 讀取 Node 中,與 style 類型元素 相關的屬性
/// </summary>
/// <param name="node"></param>
public void readStyleAttr(XmlNode node)
{
if (node == null)
{
return;
}
if (node.Attributes == null)
{
return;
}
// 字型
Font = getFont(ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_FONT));
// 字體大小
Size = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_SIZE);
// 粗體
Bold = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_BOLD);
// 斜體
Italic = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_ITALIC);
// 底線
Underline = getUnderlineStyle(ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_UNDERLINE));
// 列高
Height = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_HEIGHT);
// 欄寬
Width = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_WIDTH);
// 文字顏色
Color = getColour(ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_COLOR));
// 水平位置
Align = getAlign(ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_ALIGN));
// 垂直位置
Valign = getValign(ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_VALIGN));
// 自動換行
Wrap = ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_WRAP);
// 背景顏色
Background = getColour(ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_BACKGROUND));
// 邊線位置
BorderSide = getBorderSide(ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_BORDERSIDE));
// 邊線樣式
BorderStyle = getBorderLineStyle(ExcelStringUtil.GetNodeAttr(node, Constant.ATTRIBUTE_BORDERSTYLE));
}
/// <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);
}
}
/// <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 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>
/// 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);
}
private ExportConfigInfo readExcelInfo(XmlDocument document, string id)
{
// =========================================================
// 讀取設定資訊
// =========================================================
XmlNode excelNode =
document.SelectSingleNode(
"//" + Constant.ELEMENT_EXCEL +
"[@" + Constant.ATTRIBUTE_ID + "=\"" + id + "\"]");
if (excelNode == null)
{
throw new ExcelOperateException("設定資訊:[" + id + "] 不存在!");
}
// =========================================================
// 讀取 excelInfo
// =========================================================
var exportConfigInfo = new ExportConfigInfo
{
Id = ExcelStringUtil.GetNodeAttr(excelNode, Constant.ATTRIBUTE_ID),
FileName = ExcelStringUtil.GetNodeAttr(excelNode, Constant.ATTRIBUTE_FILENAME),
PaperSize =
this.getPaperSize(
ExcelStringUtil.GetNodeAttr(excelNode, Constant.ATTRIBUTE_PAPERSIZE))
};
// =========================================================
// 讀取 範圍 style 設定
// =========================================================
// 讀取 style node
XmlNode styleNode = excelNode.SelectSingleNode(Constant.ELEMENT_STYLE);
// 讀取屬性設定
var styleInfo = new StyleInfo();
styleInfo.readStyleAttr(styleNode);
// 將未設定的屬性，設為系統預設值
styleInfo.setEmptyAttrToSystemDefault();
// 放入 excelInfo
exportConfigInfo.StyleInfo = styleInfo;
// =========================================================
// 讀取 sheet 設定
// =========================================================」
XmlNodeList sheetNodeList = excelNode.SelectNodes(Constant.ELEMENT_SHEET);
// 記錄已使用的 dataId
var dataIdSet = new Dictionary <string, Boolean>();
// 逐筆讀取
var sheetList = new List <SheetlInfo>();
if (sheetNodeList != null)
{
foreach (XmlNode sheetNode in sheetNodeList)
{
// sheet 基本資訊
var sheetlInfo = new SheetlInfo
{
Id = ExcelStringUtil.GetNodeAttr(sheetNode, Constant.ATTRIBUTE_ID),
SheetName = ExcelStringUtil.GetNodeAttr(sheetNode, Constant.ATTRIBUTE_SHEETNAME)
};
// sheet 以下的 part 設定
var partInfoMap = new Dictionary <string, object>();
// 取得Node list
XmlNodeList nodeList =
sheetNode.SelectNodes("(" + Constant.ELEMENT_CONTEXT + "|" + Constant.ELEMENT_DETAIL + ")");
// 解析 node 設定
for (int i = 0; i < nodeList.Count; i++)
{
// 取得 node
XmlNode partInfoNode = nodeList[i];
// 取得 dataId
if (partInfoNode.Attributes != null)
{
string dataId = ExcelStringUtil.GetNodeAttr(partInfoNode, Constant.ATTRIBUTE_DATAID);
// 檢核 dataId 不可重複
if (dataIdSet.ContainsKey(dataId))
{
throw new ExcelOperateException(
" <sheet> 標籤下的 (context|detail) 標籤, dataId 不可重複! " +
"[" + dataId + "] (取用 ExportDataSet 中資料時會造成異常)");
}
dataIdSet.Add(dataId, true);
// 依據標籤類型，進行解析
if (Constant.ELEMENT_CONTEXT.Equals(partInfoNode.Name))
{
var contextInfo = new ContextInfo();
contextInfo.DataId = dataId;
contextInfo.TrInfoList = this.readContextInfo(partInfoNode);
partInfoMap.Add(partInfoNode.Name + "_" + i, contextInfo);
}
else if (Constant.ELEMENT_DETAIL.Equals(partInfoNode.Name))
{
var xmlNodeList = partInfoNode.SelectNodes(Constant.ELEMENT_COLUMN);
if (xmlNodeList != null && xmlNodeList.Count < 1)
{
throw new ExcelOperateException("<detail> 標籤下, 不可無 <column> 設定!");
}
var detailInfo = new DetailInfo
{
DataId = dataId,
ColumnInfoList = this.readDetailInfo(partInfoNode)
};
partInfoMap.Add(partInfoNode.Name + "_" + i, detailInfo);
}
}
}
//
sheetlInfo.PartInfoMap = partInfoMap;
// 放入List
sheetList.Add(sheetlInfo);
}
}
// 放入 excelInfo
exportConfigInfo.SheetList = sheetList;
return(exportConfigInfo);
}
/// <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>
///
/// </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();
}
}
}
