public byte[] StreamExcelDocument <T>(List <T> data) where T : class, new()
{
using (var stream = new MemoryStream())
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
{
var ds = new DataSet();
ds.Tables.Add(EPCore.ListToDataTable(data));
document.WriteExcelFile(ds, Stylesheet);
return(stream.ToArray());
}
}
public byte[] StreamExcelDocument <T>(Dictionary <string, List <T> > sheetsData) where T : class, new()
{
using (var stream = new MemoryStream())
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
{
var ds = new DataSet();
foreach (var data in sheetsData)
{
ds.Tables.Add(EPCore.ListToDataTable(data.Value, data.Key));
}
document.WriteExcelFile(ds, Stylesheet);
return(stream.ToArray());
}
}
public static void WriteExcelFile(this SpreadsheetDocument spreadsheet, DataSet ds, Stylesheet stylesheet = null)
{
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
DefinedNames definedNamesCol = new DefinedNames();
spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart <WorkbookStylesPart>("rIdStyles");
workbookStylesPart.Stylesheet = GenerateDefaultStyleSheet(stylesheet);
workbookStylesPart.Stylesheet.Save();
uint worksheetNumber = 1;
Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
foreach (DataTable dt in ds.Tables)
{
string worksheetName = dt.TableName;
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>();
Sheet sheet = new Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName
};
sheets.Append(sheet);
EPCore.WriteDataTableToExcelWorksheet(dt, newWorksheetPart, definedNamesCol);
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Append(definedNamesCol);
spreadsheet.WorkbookPart.Workbook.Save();
}
public ExcelFileModel ReadExcelDocument(Stream fileStream, bool headerInFirstRow = true)
{
if (fileStream == null)
{
return(null);
}
var efm = new ExcelFileModel();
using (var document = SpreadsheetDocument.Open(stream: fileStream, isEditable: false))
{
var wBookPart = document.WorkbookPart;
var sheets = wBookPart.Workbook.GetFirstChild <Sheets>();
foreach (Sheet sheet in sheets)
{
Worksheet wSheet = ((WorksheetPart)wBookPart.GetPartById(sheet.Id)).Worksheet;
SheetData sheetData = (SheetData)wSheet.GetFirstChild <SheetData>();
var sheetModel = new SheetModel()
{
SheetId = Convert.ToInt32(sheet.Id),
SheetName = sheet.Name
};
int rowIdx = 0;
int cellIdx = 1;
var columnNames = new List <(int idx, string name)>();
var rowModels = new List <RowModel>();
foreach (Row row in sheetData)
{
RowModel rowModel = null;
if (headerInFirstRow && rowIdx == 0)
{
foreach (Cell cell in row)
{
if (cell.DataType != null)
{
if (cell.DataType == CellValues.SharedString)
{
int id;
if (int.TryParse(cell.InnerText, out id))
{
var item = wBookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
columnNames.Add((idx: cellIdx, name: item?.Text?.Text ?? item?.InnerText ?? item?.InnerXml));
}
}
}
else
{
columnNames.Add((idx: cellIdx, name: cell.InnerText ?? cellIdx.ToString()));
}
cellIdx += 1;
}
}
else
{
rowModel = new RowModel()
{
RowId = rowIdx
};
foreach (Cell cell in row)
{
if (rowIdx == 0)
{
columnNames.Add((idx: cellIdx, name: cellIdx.ToString()));
}
var res = new CellModel()
{
RowId = rowIdx,
ColumnName = columnNames.FirstOrDefault(t => t.idx == cellIdx).name,
CellId = cellIdx
};
if (cell.CellFormula?.InnerText == null)
{
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
if (int.TryParse(cell.InnerText, out int id))
{
var item = wBookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
res.CellValue = item?.Text?.Text ?? item?.InnerText ?? item?.InnerXml;
}
break;
case CellValues.Number:
if (decimal.TryParse(cell.InnerText, out decimal numberVal))
{
res.CellValue = cell.InnerText;
res.CellValueNumber = numberVal;
}
break;
case CellValues.Date:
if (DateTime.TryParse(cell.InnerText, out DateTime dateVal))
{
res.CellValue = cell.InnerText;
res.CellValueDateTime = dateVal;
}
break;
case CellValues.Boolean:
res.CellValue = cell.InnerText;
switch (cell.InnerText)
{
case "0":
res.CellValueBoolean = false;
break;
default:
res.CellValueBoolean = true;
break;
}
break;
default:
res.CellValue = cell.InnerText;
break;
}
}
else
{
try
{
EPCore.SetFormattedValue(cell, ref res);
}
catch
{
res.CellValue = cell.InnerText;
}
}
}
rowModel.Cells.Add(res);
cellIdx += 1;
}
}
if (rowModel != null)
{
sheetModel.Rows.Add(rowModel);
}
rowIdx += 1;
cellIdx = 1;
}
efm.Sheets.Add(sheetModel);
}
}
return(efm);
}
