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