public async Task CreateReport(byte sheets = 1, int records = 1000, byte seconds = 1)
{
object[] h = new object[] { @"Name", @"BirthDate", @"Age", @"Email Address" };
List <object[]> headers = new List <object[]>(1)
{
h
};
object[] i = new object[] { @"Israel Chavez Gamez", DateTime.Now.AddYears(-27), 27, @"*****@*****.**" };
List <object[]> data = Enumerable.Repeat(i, records).ToList();
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
for (byte x = 0; x < sheets; x++)
{
document.AddSheet($@"Sheet{x}");
SheetData sheet = document.GetSheetData($@"Sheet{x}");
await sheet.AddRows(headers);
await sheet.AddRows(data);
sheet.GetRow(0).SetColor(SystemColor.White, SystemColor.Purple);
}
stopwatch.Stop();
document.AutoAdjustWidth();
document.SaveAs($@"{AppDomain.CurrentDomain.BaseDirectory}TestResults\Extensions\Excel\SpreadSheet.xlsx");
Assert.True(stopwatch.Elapsed.TotalSeconds <= seconds);
}
public static string DownloadExcel(string path, string pathToDownload = null)
{
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
{
string preffix = string.Empty;
string name = fs.Name.Split('/').Last().Split('.').First();
string ext = fs.Name.Split('.').Last();
pathToDownload = string.IsNullOrEmpty(pathToDownload) ? "." : pathToDownload;
if (!Directory.Exists(pathToDownload))
{
Directory.CreateDirectory(pathToDownload);
}
if (File.Exists(path))
{
preffix = "_" + DateTime.Now.ToString("ddMMyyyyhhmmssmm");
}
pathToDownload = $"{pathToDownload}/{name}{preffix}.{ext}";
doc.SaveAs(pathToDownload);
return(pathToDownload);
}
}
}
/// <summary>Save spreadsheetbook in the path</summary>
/// <param name="fullNamePath">path and name for create file</param>
public void SaveAs(string fullNamePath)
{
foreach (var sheet in this)
{
sheet.Save();
}
WorkbookPart.Workbook.Save();
SpreadsheetDocument.SaveAs(fullNamePath);
}
public async Task GenerateAccount(Dictionary <string, string> markerValuePairs, string filePath)
{
await Task.Factory.StartNew(() =>
{
using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(templateFile, true))
{
var savedDoc = excelDoc.SaveAs(filePath);
savedDoc.Close();
}
using (SpreadsheetDocument generatedDoc = SpreadsheetDocument.Open(filePath, true))
{
var workTable = generatedDoc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First().SharedStringTable;
var innerXml = ReplaceMarkersByValues(markerValuePairs, workTable.InnerXml);
workTable.InnerXml = innerXml;
workTable.Save();
}
});
}
/// <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);
}
public void Save()
{
var dir = Path.GetDirectoryName(OriginalFile);
Document.SaveAs(dir + "\\tempfile.xlsx");
}
/// <summary>
/// Creates the workbook to which all sheets will be added, using the specified template file
/// </summary>
/// <returns>The newly created <see cref="SpreadsheetDocument"/></returns>
private SpreadsheetDocument CreateTargetWorkbook()
{
Trace.Indent();
try
{
SpreadsheetDocument targetWorkbook = null;
SpreadsheetDocument workbook = null;
try
{
// we will either use a template from a path they specify OR one of the built in templates
if (!string.IsNullOrEmpty(Settings.TemplateFilePath) && File.Exists(Settings.TemplateFilePath))
{
Trace.WriteLine(string.Format("Creating target workbook from template at path ({0})", Settings.TemplateFilePath), TraceCategory);
workbook = SpreadsheetDocument.CreateFromTemplate(Settings.TemplateFilePath);
workbook.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook);
targetWorkbook = (SpreadsheetDocument)workbook.SaveAs(OutputPath);
}
else
{
Trace.WriteLine(string.Format("Creating target workbook from compiled template ({0})", Settings.TemplateName), TraceCategory);
// this is done to juggle limitations in open xml creating a file from a stream where the stream is readonly
var resourceName = string.Format(BuiltInTemplateNameFormat, Settings.TemplateName);
if (Assembly.GetExecutingAssembly().GetManifestResourceNames().Contains(resourceName, StringComparer.OrdinalIgnoreCase))
{
using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName))
{
workbook = SpreadsheetDocument.Open(stream, false);
var temp = workbook.SaveAs(OutputPath);
workbook.Close();
temp.Close(); // this allows us to reopen the file below
}
targetWorkbook = SpreadsheetDocument.Open(OutputPath, true);
targetWorkbook.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook);
targetWorkbook.Save();
}
else
{
var message = string.Format("Could not locate embedded resource template {0}.", resourceName);
Trace.TraceError(message);
throw new Exception(message);
}
}
Trace.WriteLine(string.Format("Created target workbook at {0}", OutputPath), TraceCategory);
}
finally
{
if (workbook != null)
{
workbook.Dispose();
}
}
return(targetWorkbook);
}
catch (Exception err)
{
Trace.TraceError("Error creating output workbook from template");
Trace.TraceError(err.ToString());
throw;
}
finally
{
Trace.Unindent();
}
}
