/// <summary>
/// import by stream
/// </summary>
/// <param name="stream"></param>
/// <param name="importDto"></param>
/// <param name="fileName"></param>
/// <param name="uiDtFormat"></param>
/// <returns></returns>
public async Task <ResultImportDto> ImportByStreamAsync(Stream stream, ExcelImportDto <T> importDto, string dirUpload, string fileName, string uiDtFormat)
{
stream.Position = 0;
var docx = _Excel.StreamToDocx(stream);
var result = await ImportByDocxAsync(docx, importDto, dirUpload, fileName, uiDtFormat);
//release docx
docx = null;
return(result);
}
/*
* //excel license file path
* //protected string asposeLicPath;
* protected string saveExcelPath; //with path
* protected string tplPath;
* protected string sheetName;
* protected string uploadFileName;
* protected string sysFileName;
*/
#region 抽象方法宣告, 在子代實作
//檢查table row(excel轉dataTable)
//abstract public T CheckTableRow(DataRow dr);
//執行匯入
//abstract public void RunImport();
#endregion
/*
* /// <summary>
* /// constructor, 如果傳入參數有誤則送出 Exception
* /// </summary>
* /// <param name="asposeLicPath">server path</param>
* /// <param name="excelPath">excel file full path</param>
* /// <param name="checkTableRow"></param>
* /// <param name="sheetName">excel sheet name</param>
* public ImportExcelService(string asposeLicPath, string excelPath, string tplPath, string sheetName = "Sheet1")
* {
* try
* {
* //set instance variables
* this.asposeLicPath = asposeLicPath;
* this.excelPath = excelPath;
* this.tplPath = tplPath;
* this.sheetName = sheetName;
*
* //建立excel connection
* var excelBook = Utils.OpenExcel(asposeLicPath, excelPath);
*
* //set dataTable _dt
* var errorMsg = "";
* this.dt = Utils.ReadWorksheet(excelBook, sheetName, 1, 0, out errorMsg, true);
* }
* catch (Exception ex)
* {
* throw new Exception("ImportExcelService.cs constructor failed: " + ex.Message);
* }
* }
*/
/// <summary>
///
/// </summary>
/// <param name="asposeLicPath"></param>
/// <param name="uploadFileName"></param>
/// <param name="saveExcelPath"></param>
/// <param name="tplPath"></param>
/// <param name="sheetName"></param>
/// <returns>system error, not excel row error</returns>
/*
* public string ImportByDocx_new(string asposeLicPath, string uploadFileName, string saveExcelPath, string tplPath, string sheetName = "Sheet1")
* {
* //initial
* var error = Init(asposeLicPath, uploadFileName, saveExcelPath, tplPath, sheetName);
* if (!string.IsNullOrEmpty(error))
* return error;
*
* //檢查excel rows
* CheckRows();
*
* //匯入
* RunImport();
* return "";
* }
*/
public string ImportByStream(string frontDtFormat, Stream stream, ExcelImportDto <T> import)
{
stream.Position = 0;
var docx = _Excel.StreamToDocx(stream);
//var docx = SpreadsheetDocument.Open(stream, false);
var error = ImportByDocx(frontDtFormat, docx, import);
//release docx
docx = null;
return(error);
}
/*
* //string filePath, string insertSql, int[] excelCols, int excelStartRow, bool[] isDates = null, int sheetNo = 0, Db db = null)
* public static bool ImportByFile(HttpPostedFileBase file, string insertSql, int excelStartRow, int[] excelCols, bool[] isDates = null, int sheetNo = 0, Db db = null)
* {
* //check
* if (file == null || file.ContentLength == 0)
* return false;
*
* return _Excel.ImportByStream(file.InputStream, insertSql, excelStartRow, excelCols, isDates, sheetNo, db);
* }
*/
public static string ImportByFile <T>(IFormFile file,
ExcelImportDto <T> import) where T : class, new()
{
//check
if (file == null || file.Length == 0)
{
return("error");
}
//TODO pending
//return new ExcelImportService<T>().ImportByStream(file.InputStream, import);
return(null);
}
/// <summary>
/// import by file
/// </summary>
/// <param name="file"></param>
/// <param name="importType"></param>
/// <param name="importDto"></param>
/// <param name="uiDtFormat">skip if excel no datetime cell</param>
public static async Task <ResultImportDto> ImportByFileAsync <T>(IFormFile file, string dirUpload,
ExcelImportDto <T> importDto, string uiDtFormat = "") where T : class, new()
{
//check
if (file == null || file.Length == 0)
{
return new ResultImportDto()
{
ErrorMsg = "Upload file is empty.",
}
}
;
return(await new ExcelImportService <T>().ImportByStreamAsync(file.OpenReadStream(), importDto, dirUpload, file.FileName, uiDtFormat));
}
/// <summary>
/// import by excel docx
/// excel row/cell (base 0)
/// </summary>
/// <param name="docx"></param>
/// <param name="importDto"></param>
/// <param name="fileName">imported excel file name</param>
/// <param name="uiDtFormat"></param>
/// <returns>error msg if any</returns>
public async Task <ResultImportDto> ImportByDocxAsync(SpreadsheetDocument docx, ExcelImportDto <T> importDto, string dirUpload, string fileName, string uiDtFormat)
{
#region 1.set variables
#region set docx, excelRows, ssTable
//var errorMsg = "";
var wbPart = docx.WorkbookPart;
var wsPart = (WorksheetPart)wbPart.GetPartById(
wbPart.Workbook.Descendants <Sheet>().ElementAt(importDto.SheetNo).Id);
var excelRows = wsPart.Worksheet.Descendants <Row>(); //include empty rows
var ssTable = wbPart.GetPartsOfType <SharedStringTablePart>().First().SharedStringTable;
#endregion
#region set importDto.ExcelFids, excelFidLen
int idx;
var colMap = new JObject(); //col x-way name(ex:A) -> col index
var cells = excelRows.ElementAt(importDto.FidRowNo - 1).Elements <Cell>();
var excelFids = new List <string>();
//if (importDto.ExcelFids == null || importDto.ExcelFids.Count == 0)
//{
//如果沒有傳入excel欄位名稱, 則使用第一行excel做為欄位名稱
idx = 0;
foreach (var cell in cells)
{
excelFids.Add(GetCellValue(ssTable, cell));
colMap[CellXname(cell.CellReference)] = idx;
idx++;
}
/*
* }
* else
* {
* //有傳入excel欄位名稱
* //check
* var cellLen = cells.Count();
* if (cellLen != importDto.ExcelFids.Count)
* {
* errorMsg = "importDto.ExcelFids length should be " + cellLen;
* goto lab_error;
* }
*
* //set colMap
* for (var i=0; i< cellLen; i++)
* {
* var colName = CellXname(cells.ElementAt(i).CellReference);
* colMap[colName] = i;
* }
* }
*/
//initial excelIsDates & set excelFidLen
var excelIsDates = new List <bool>(); //是否為是日期欄位
var excelFidLen = excelFids.Count;
for (var i = 0; i < excelFidLen; i++)
{
excelIsDates.Add(false); //initial
}
#endregion
#region set excelIsDates, modelFids, modelDateFids/Fno/Len, modelNotDateFids/Fno/Len
int fno;
var modelFids = new List <string>(); //全部欄位
var model = new T();
foreach (var prop in model.GetType().GetProperties())
{
//如果對應的excel欄位不存在, 則不記錄此欄位(skip)
//var type = prop.GetValue(model, null).GetType();
var fid = prop.Name;
fno = excelFids.FindIndex(a => a == fid);
if (fno < 0)
{
continue;
}
modelFids.Add(fid);
if (prop.PropertyType == typeof(DateTime?))
{
excelIsDates[fno] = true;
}
}
//var modelDateFidLen = modelDateFids.Count;
//var modelNotDateFidLen = modelNotDateFids.Count;
#endregion
#region set fileRows by excel file
var fileRows = new List <T>(); //excel rows with data(not empty row)
var excelRowLen = excelRows.LongCount();
for (var i = importDto.FidRowNo; i < excelRowLen; i++)
{
var excelRow = excelRows.ElementAt(i);
var fileRow = new T();
/*
* //set datetime column
* //var rowHasCol = false;
* for(var j=0; j<modelDateFidLen; j++)
* {
* //var cell = cells.ElementAt(modelDateFnos[j]);
* var cell = excelRow.Descendants<Cell>().ElementAt(j);
* if (cell.DataType != null)
* {
* //rowHasCol = true;
* value = (cell.DataType == CellValues.SharedString) ? ssTable.ChildElements[int.Parse(cell.CellValue.Text)].InnerText :
* cell.CellValue.Text;
* _Model.SetValue(modelRow, modelDateFids[j], DateTime.FromOADate(double.Parse(value)).ToString(rb.uiDtFormat));
* }
* }
*/
//write not date column
//for (var j = 0; j < modelNotDateFidLen; j++)
//var j = 0;
foreach (Cell cell in excelRow)
{
/*
* if (i == 2 && j == 1)
* {
* var aa = "aa";
* }
*/
//var cell = cells.ElementAt(modelNotDateFnos[j]);
//var cell = excelRow.Descendants<Cell>().ElementAt(modelNotDateFnos[j]);
//colName = ;
fno = (int)colMap[CellXname(cell.CellReference)];
var value = (cell.DataType == CellValues.SharedString)
? ssTable.ChildElements[int.Parse(cell.CellValue.Text)].InnerText
: cell.CellValue.Text;
_Model.SetValue(fileRow, excelFids[fno], excelIsDates[fno]
? DateTime.FromOADate(double.Parse(value)).ToString(uiDtFormat)
: value
);
}
fileRows.Add(fileRow);
}
#endregion
#endregion
#region 2.validate fileRows loop
idx = 0;
//var error = "";
foreach (var fileRow in fileRows)
{
//validate
var context = new ValidationContext(fileRow, null, null);
var results = new List <ValidationResult>();
if (Validator.TryValidateObject(fileRow, context, results, true))
{
//user validate rule
//if (importDto.FnCheckImportRow != null)
// error = importDto.FnCheckImportRow(fileRow);
//if (_Str.IsEmpty(error))
_okRowNos.Add(idx);
//else
// AddError(idx, error);
}
else
{
AddErrorByResults(idx, results);
}
idx++;
}
#endregion
#region 3.save database for ok rows(call FnSaveImportRows())
if (_okRowNos.Count > 0)
{
//set okRows
var okRows = new List <T>();
foreach (var okRowNo in _okRowNos)
{
okRows.Add(fileRows[okRowNo]);
}
//call FnSaveImportRows
idx = 0;
var saveResults = importDto.FnSaveImportRows(okRows);
if (saveResults != null)
{
foreach (var result in saveResults)
{
if (!_Str.IsEmpty(result))
{
AddError(_okRowNos[idx], result);
}
idx++;
}
}
}
#endregion
#region 4.save ok excel file
if (_Str.IsEmpty(importDto.LogRowId))
{
importDto.LogRowId = _Str.NewId();
}
var fileStem = _Str.AddDirSep(dirUpload) + importDto.LogRowId;
docx.SaveAs(fileStem + ".xlsx");
#endregion
#region 5.save fail excel file (tail _fail.xlsx)
var failCount = _failRows.Count;
if (failCount > 0)
{
//set excelFnos: excel column map model column
var excelFnos = new List <int>();
for (var i = 0; i < excelFidLen; i++)
{
fno = modelFids.FindIndex(a => a == excelFids[i]);
excelFnos.Add(fno); //<0 means no mapping
}
//get docx
var failFilePath = fileStem + "_fail.xlsx";
File.Copy(importDto.TplPath, failFilePath, true);
var docx2 = SpreadsheetDocument.Open(failFilePath, true);
var wbPart2 = docx2.WorkbookPart;
var wsPart2 = (WorksheetPart)wbPart2.GetPartById(
wbPart2.Workbook.Descendants <Sheet>().ElementAt(0).Id);
var sheetData2 = wsPart2.Worksheet.GetFirstChild <SheetData>();
var startRow = importDto.FidRowNo; //insert position
for (var i = 0; i < failCount; i++)
{
//add row, fill value & copy row style
var modelRow = fileRows[_failRows[i].Sn];
var newRow = new Row(); //new excel row
for (var colNo = 0; colNo < excelFidLen; colNo++)
{
fno = excelFnos[colNo];
var value2 = _Model.GetValue(modelRow, excelFids[colNo]);
newRow.Append(new Cell()
{
CellValue = new CellValue(fno < 0 || value2 == null ? "" : value2.ToString()),
DataType = CellValues.String,
});
}
//write cell for error msg
newRow.Append(new Cell()
{
CellValue = new CellValue(_failRows[i].Str),
DataType = CellValues.String,
});
sheetData2.InsertAt(newRow, startRow + i);
}
docx2.Save();
docx2.Dispose();
}
#endregion
#region 6.insert ImportLog table
var totalCount = fileRows.Count;
var okCount = totalCount - failCount;
var sql = $@"
insert into dbo.XpImportLog(Id, Type, FileName,
OkCount, FailCount, TotalCount,
CreatorName, Created)
values('{importDto.LogRowId}', '{importDto.ImportType}', '{fileName}',
{okCount}, {failCount}, {totalCount},
'{importDto.CreatorName}', '{_Date.NowDbStr()}')
";
await _Db.ExecSqlAsync(sql);
#endregion
//7.return import result
return(new ResultImportDto()
{
OkCount = okCount,
FailCount = failCount,
TotalCount = totalCount,
});
}
/// <summary>
/// import by excel docx
/// excel row/cell 為base 0
/// </summary>
/// <param name="docx"></param>
/// <param name="import">匯入參數</param>
/// <returns>error msg if any</returns>
public string ImportByDocx(string frontDtFormat, SpreadsheetDocument docx, ExcelImportDto <T> import)
{
//檢查傳入參數
#region set docx, excelRows, ssTable
var wbPart = docx.WorkbookPart;
var wsPart = (WorksheetPart)wbPart.GetPartById(
wbPart.Workbook.Descendants <Sheet>().ElementAt(import.SheetNo).Id);
var excelRows = wsPart.Worksheet.Descendants <Row>();
var ssTable = wbPart.GetPartsOfType <SharedStringTablePart>().First().SharedStringTable;
#endregion
#region set import.ExcelFids, excelFidLen
int idx;
//string colName;
var colMap = new JObject(); //欄位字元(ex:A) -> 欄位陣列元素idx
var cells = excelRows.ElementAt(0).Elements <Cell>();
if (import.ExcelFids == null || import.ExcelFids.Count == 0)
{
//如果沒有傳入excel欄位名稱, 則使用第一行excel做為欄位名稱
idx = 0;
foreach (var cell in cells)
{
import.ExcelFids.Add(GetCellValue(ssTable, cell));
colMap[CellNameRemoveNum(cell.CellReference)] = idx;
idx++;
}
}
else
{
//有傳入excel欄位名稱
//check
var cellLen = cells.Count();
if (cellLen != import.ExcelFids.Count)
{
return("import.ExcelFids length should be " + cellLen);
}
//set colMap
for (var i = 0; i < cellLen; i++)
{
var colName = CellNameRemoveNum(cells.ElementAt(i).CellReference);
colMap[colName] = i;
}
}
//initial excelIsDates & set excelFidLen
var excelIsDates = new List <bool>(); //是否為是日期欄位
var excelFidLen = import.ExcelFids.Count;
for (var i = 0; i < excelFidLen; i++)
{
excelIsDates.Add(false); //initial
}
#endregion
#region set excelIsDates, modelFids, modelDateFids/Fno/Len, modelNotDateFids/Fno/Len
int fno;
var modelFids = new List <string>(); //全部欄位
var model = new T();
foreach (var prop in model.GetType().GetProperties())
{
//如果對應的excel欄位不存在, 則不記錄此欄位(skip)
//var type = prop.GetValue(model, null).GetType();
var fid = prop.Name;
fno = import.ExcelFids.FindIndex(a => a == fid);
if (fno < 0)
{
continue;
}
modelFids.Add(fid);
if (prop.PropertyType == typeof(DateTime?))
{
excelIsDates[fno] = true;
}
}
//var modelDateFidLen = modelDateFids.Count;
//var modelNotDateFidLen = modelNotDateFids.Count;
#endregion
#region set modelRows
//var rb = _Locale.RB;
var modelRows = new List <T>();
//Cell cell;
//string value;
var excelRowLen = excelRows.LongCount();
for (var i = import.ExcelStartRow - 1; i < excelRowLen; i++)
{
//var cells = excelRows.ElementAt(i).Elements<Cell>();
var excelRow = excelRows.ElementAt(i);
//var cells = excelRows.ElementAt(i).Descendants<Cell>();
//var cells = row.Elements<Cell>();
var modelRow = new T();
/*
* //寫入日期欄位
* //var rowHasCol = false;
* for(var j=0; j<modelDateFidLen; j++)
* {
* //var cell = cells.ElementAt(modelDateFnos[j]);
* var cell = excelRow.Descendants<Cell>().ElementAt(j);
* if (cell.DataType != null)
* {
* //rowHasCol = true;
* value = (cell.DataType == CellValues.SharedString) ? ssTable.ChildElements[int.Parse(cell.CellValue.Text)].InnerText :
* cell.CellValue.Text;
* _Model.SetValue(modelRow, modelDateFids[j], DateTime.FromOADate(double.Parse(value)).ToString(rb.FrontDtFormat));
* }
* }
*/
//寫入非日期欄位
//for (var j = 0; j < modelNotDateFidLen; j++)
//var j = 0;
foreach (Cell cell in excelRow)
{
/*
* if (i == 2 && j == 1)
* {
* var aa = "aa";
* }
*/
//var cell = cells.ElementAt(modelNotDateFnos[j]);
//var cell = excelRow.Descendants<Cell>().ElementAt(modelNotDateFnos[j]);
//colName = ;
fno = (int)colMap[CellNameRemoveNum(cell.CellReference)];
var value = (cell.DataType == CellValues.SharedString)
? ssTable.ChildElements[int.Parse(cell.CellValue.Text)].InnerText
: cell.CellValue.Text;
_Model.SetValue(modelRow, import.ExcelFids[fno], excelIsDates[fno]
? DateTime.FromOADate(double.Parse(value)).ToString(frontDtFormat)
: value
);
}
modelRows.Add(modelRow);
}
#endregion
#region validate modelRows loop
idx = 0;
var error = "";
foreach (var modelRow in modelRows)
{
//validate
var context = new ValidationContext(modelRow, null, null);
var results = new List <ValidationResult>();
if (Validator.TryValidateObject(modelRow, context, results, true))
{
//user validate rule
if (import.FnCheckImportRow != null)
{
error = import.FnCheckImportRow(modelRow);
}
if (string.IsNullOrEmpty(error))
{
_okRowNos.Add(idx);
}
else
{
AddError(idx, error);
}
}
else
{
AddErrorByResults(idx, results);
}
idx++;
}
#endregion
#region save database if need
if (_okRowNos.Count > 0)
{
//set okRows
var okRows = new List <T>();
foreach (var okRowNo in _okRowNos)
{
okRows.Add(modelRows[okRowNo]);
}
idx = 0;
var saveResults = import.FnSaveImportRows(okRows);
if (saveResults != null)
{
foreach (var result in saveResults)
{
if (!string.IsNullOrEmpty(result))
{
AddError(_okRowNos[idx], result);
}
idx++;
}
}
}
#endregion
//save excel file
var fileStem = _Str.AddAntiSlash(import.SaveDir) + import.LogRowId;
docx.SaveAs(fileStem + "_source.xlsx");
#region save error excel file
var errorLen = _errorRows.Count;
if (errorLen > 0)
{
//產生 excel 欄位與 model 欄位的對應 excelFnos
var excelFnos = new List <int>();
for (var i = 0; i < excelFidLen; i++)
{
fno = modelFids.FindIndex(a => a == import.ExcelFids[i]);
excelFnos.Add(fno); //小於0表示無對應欄位
}
//get docx
var errorFilePath = fileStem + "_error.xlsx";
File.Copy(import.TplFilePath, errorFilePath, true);
var docx2 = SpreadsheetDocument.Open(errorFilePath, true);
var wbPart2 = docx2.WorkbookPart;
var wsPart2 = (WorksheetPart)wbPart2.GetPartById(
wbPart2.Workbook.Descendants <Sheet>().ElementAt(0).Id);
var sheetData2 = wsPart2.Worksheet.GetFirstChild <SheetData>();
var startRow = import.ExcelStartRow - 1; //insert position
for (var i = 0; i < errorLen; i++)
{
//add row, fill value & copy row style
var modelRow = modelRows[_errorRows[i].Sn];
var newRow = new Row(); //new excel row
for (var colNo = 0; colNo < excelFidLen; colNo++)
{
fno = excelFnos[colNo];
var value2 = _Model.GetValue(modelRow, import.ExcelFids[colNo]);
newRow.Append(new Cell()
{
CellValue = new CellValue(fno < 0 || value2 == null ? "" : value2.ToString()),
DataType = CellValues.String,
});
}
//write cell for error msg
newRow.Append(new Cell()
{
CellValue = new CellValue(_errorRows[i].Str),
DataType = CellValues.String,
});
sheetData2.InsertAt(newRow, startRow + i);
}
docx2.Save();
docx2.Dispose();
}
#endregion
#region add import log
var sql = string.Format(@"
insert into dbo._ImportLog(Id, UploadFileName,
OkCount, FailCount, TotalCount,
CreatorName, Created)
values('{0}', '{1}',
{2}, {3}, {4},
'{5}', '{6}')
", import.LogRowId, import.UploadFileName,
modelRows.Count - _errorRows.Count, _errorRows.Count, modelRows.Count,
import.CreatorName, _Date.NowDbStr());
_Db.Update(sql);
#endregion
return(string.Empty);
}