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(); } }