private static async Task <MemoryStream> DataToExcelStreamAsync(WriteRowsDelegate writeRows, IList <String> headers, string sheetName, List <int> columnWidths = null)
{
var xmlStream = ReportingHelper.GetResourceStream("Shesha.Web.DataTable.Excel.template.xlsx", typeof(ExcelUtility).Assembly);
using (var document = SpreadsheetDocument.Open(xmlStream, true))
{
var workbookPart = document.WorkbookPart;
var worksheetPart = workbookPart.WorksheetParts.First();
var originalSheetId = workbookPart.GetIdOfPart(worksheetPart);
var replacementPart = workbookPart.AddNewPart <WorksheetPart>();
var replacementPartId = workbookPart.GetIdOfPart(replacementPart);
// Configure the spreadsheet
SetSheetName(sheetName, document);
SetStyleSheet(document);
// Fit to page
var sp = new SheetProperties(new PageSetupProperties());
var ws = worksheetPart.Worksheet;
ws.SheetProperties = sp;
// Set the FitToPage property to true
ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true);
var pgOr = new PageSetup
{
// Page size A4 landscape
PaperSize = 9,
Orientation = OrientationValues.Landscape,
// Scale to fit to page width
FitToWidth = 1,
FitToHeight = 0
};
ws.AppendChild(pgOr);
var maxWidth = 0;
if (columnWidths != null)
{
var idx = 1;
var columns =
columnWidths
.Select(
w => new Column
{
CustomWidth = true,
Min = Convert.ToUInt32(idx),
Max = Convert.ToUInt32(idx++),
Width = w,
BestFit = false
})
.ToList();
var cols = new DocumentFormat.OpenXml.Spreadsheet.Columns(columns);
worksheetPart.Worksheet.InsertBefore(cols, worksheetPart.Worksheet.GetFirstChild <SheetData>());
}
else
{
maxWidth = headers.Select(h => h.Length).Max();
AddCellWidthStyles(Convert.ToUInt32(1), Convert.ToUInt32(headers.Count), maxWidth, document, worksheetPart);
}
worksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook.Save();
using (var xmlReader = OpenXmlReader.Create(worksheetPart))
{
using (var xmlWriter = OpenXmlWriter.Create(replacementPart))
{
while (xmlReader.Read())
{
if (xmlReader.ElementType == typeof(SheetData))
{
if (xmlReader.IsEndElement)
{
continue;
}
xmlWriter.WriteStartElement(new SheetData());
var headerCell = new Cell(new CellValue());
headerCell.DataType = new EnumValue <CellValues>(CellValues.String);
// write headers
xmlWriter.WriteStartElement(new Row());
SetHeaderStyle(document, headerCell);
foreach (var header in headers)
{
headerCell.CellValue.Text = header;
xmlWriter.WriteElement(headerCell);
}
xmlWriter.WriteEndElement();
await writeRows.Invoke(xmlWriter);
xmlWriter.WriteEndElement();
}
else
{
if (xmlReader.IsStartElement)
{
xmlWriter.WriteStartElement(xmlReader);
}
else if (xmlReader.IsEndElement)
{
xmlWriter.WriteEndElement();
}
}
}
}
}
var sheet = workbookPart.Workbook.Descendants <Sheet>().First(s => s.Id.Value.Equals(originalSheetId));
sheet.Id.Value = replacementPartId;
workbookPart.DeletePart(worksheetPart);
}
return(xmlStream);
}
