public static void WriteDataInExcelFile(ResultTable results, Stream stream)
{
if (results == null)
{
throw new ApplicationException(ExcelMessage.ThereAreNoResultsToWrite.NiceToString());
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true))
{
document.PackageProperties.Creator = "";
document.PackageProperties.LastModifiedBy = "";
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = document.GetWorksheetPartByName(ExcelMessage.Data.NiceToString());
CellBuilder cb = PlainExcelGenerator.CellBuilder;
SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().SingleEx();
List <ColumnData> columnEquivalences = GetColumnsEquivalences(document, sheetData, results);
UInt32Value headerStyleIndex = worksheetPart.Worksheet.FindCell("A1").StyleIndex;
//Clear sheetData from the template sample data
sheetData.InnerXml = "";
sheetData.Append(new Sequence <Row>()
{
(from columnData in columnEquivalences
select cb.Cell(columnData.Column.Column.DisplayName, headerStyleIndex)).ToRow(),
from r in results.Rows
select(from columnData in columnEquivalences
select cb.Cell(r[columnData.Column], cb.GetTemplateCell(columnData.Column.Column.Type), columnData.StyleIndex)).ToRow()
}.Cast <OpenXmlElement>());
var pivotTableParts = workbookPart.PivotTableCacheDefinitionParts
.Where(ptpart => ptpart.PivotCacheDefinition.Descendants <WorksheetSource>()
.Any(wss => wss.Sheet.Value == ExcelMessage.Data.NiceToString()));
foreach (PivotTableCacheDefinitionPart ptpart in pivotTableParts)
{
PivotCacheDefinition pcd = ptpart.PivotCacheDefinition;
WorksheetSource wss = pcd.Descendants <WorksheetSource>().FirstEx();
wss.Reference.Value = "A1:" + GetExcelColumn(columnEquivalences.Count(ce => !ce.IsNew) - 1) + (results.Rows.Count() + 1).ToString();
pcd.RefreshOnLoad = true;
pcd.SaveData = false;
pcd.Save();
}
workbookPart.Workbook.Save();
document.Close();
}
}
