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