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 !; if (workbookPart.CalculationChainPart != null) { workbookPart.Workbook.CalculationProperties !.ForceFullCalculation = true; workbookPart.Workbook.CalculationProperties !.FullCalculationOnLoad = true; } WorksheetPart worksheetPart = document.GetWorksheetPartBySheetName(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.GetDefaultStyle(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(); } }