/// <summary> /// Generate Excel report from array of DataTables by using a provided Excel template file. /// </summary> /// <param name="dataTables">Array of DataTable containing data for report</param> /// <param name="templateFile">Filename of Excel file to use as template. Sheets correspond (in order) to DataTables</param> /// <param name="templateCellsRow">Array of Excel row numbers where template cells are defined for each column</param> /// <returns></returns> public static MemoryStream GenerateReport(DataTable[] dataTables, string templateFile, int[] templateCellsRow) { MemoryStream ms = new MemoryStream(); ms = Utility.LoadFile(templateFile); using (SpreadsheetDocument ssDoc = SpreadsheetDocument.Open(ms, true)) { WorkbookPart workbookPart = ssDoc.WorkbookPart; List<Sheet> templateSheets = (from s in workbookPart.Workbook.Descendants<Sheet>() select s).ToList(); for (int i = 0; i < dataTables.Length; i++) { //TODO: this method really needs to be simplified!!! Sheet worksheet = workbookPart.Workbook.Descendants<Sheet>().ToArray()[i]; WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(worksheet.Id); Row tRow = wsPart.Worksheet.Descendants<Row>(). Where(r => r.RowIndex == templateCellsRow[i]).FirstOrDefault(); int cnt = tRow.Count(); //First row of templateFile is used to get column count "cnt" List<Cell> cellLookup = new List<Cell>(); for (int j = 0; j < cnt; j++) { Cell c = (Cell)tRow.ElementAt(j); cellLookup.Add((Cell)c.CloneNode(true)); } SheetData sheetData = wsPart.Worksheet.GetFirstChild<SheetData>(); int currRowIndex = templateCellsRow[i]; for (int j = 0; j < dataTables[i].Rows.Count; j++) { Row newRow = new Row(); newRow.RowIndex = (UInt32)(currRowIndex); //DataTable columns must match templateFile columns, if there is an out of index error the datatable columns were likely updated causing a mismatch for (int k = 0; k < dataTables[i].Columns.Count; k++) { int styleIndex = -1; if (((Cell)cellLookup[k]).StyleIndex != null) { styleIndex = (int)(((Cell)cellLookup[k]).StyleIndex.Value); } Cell newCell = ExcelReportGenerator.CreateCellBasedOnType(dataTables[i].Rows[j][k], ExcelColumnReferenceLookup[k] + currRowIndex, styleIndex, ssDoc); newRow.AppendChild(newCell); } sheetData.AppendChild(newRow); currRowIndex++; } } } return ms; }
private static void OutputRawData(DataTable dataTable, SpreadsheetDocument ssDoc, string sheetName) { SheetData sheetData = ExcelReportGenerator.GetSheetData(ssDoc, sheetName); int currRowIndex = 1; Row headerRow = new Row(); headerRow.RowIndex = (UInt32)currRowIndex; for (int colIndex = 0; colIndex < dataTable.Columns.Count; colIndex++) { Cell newCell = ExcelReportGenerator.CreateInlineTextCell( dataTable.Columns[colIndex].ColumnName, ExcelColumnReferenceLookup[colIndex] + currRowIndex); headerRow.AppendChild(newCell); } currRowIndex++; sheetData.AppendChild(headerRow); for (int row = 0; row < dataTable.Rows.Count; row++) { Row newRow = new Row(); newRow.RowIndex = (UInt32)currRowIndex; for (int colIndex = 0; colIndex < dataTable.Columns.Count; colIndex++) { CellType ct = ExcelReportGenerator.DetermineCellType(dataTable.Rows[row][colIndex]); int styleIndex = (int)(ExcelReportGenerator.DefaultTemplateCells[(int)ct].StyleIndex.Value); object currObj = dataTable.Rows[row][colIndex]; if (dataTable.Rows[row][colIndex] is DBNull) { currObj = "NULL"; } Cell newCell = ExcelReportGenerator.CreateCellBasedOnType(currObj, ExcelColumnReferenceLookup[colIndex] + currRowIndex, styleIndex, ssDoc); newRow.AppendChild(newCell); } sheetData.AppendChild(newRow); currRowIndex++; } }
private static void OutputTemplatedData(DataTable dataTable, SpreadsheetDocument ssDoc, int templateCellsRow, string sheetName) { WorkbookPart workbookPart = ssDoc.WorkbookPart; Sheet worksheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(); WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(worksheet.Id); Row tRow = wsPart.Worksheet.Descendants<Row>().FirstOrDefault(); int cnt = tRow.Count(); List<Cell> cellLookup = new List<Cell>(); for (int i = 0; i < cnt; i++) { Cell c = (Cell)tRow.ElementAt(i); cellLookup.Add((Cell)c.CloneNode(true)); } SheetData sheetData = wsPart.Worksheet.GetFirstChild<SheetData>(); //int currRowIndex = templateCellsRow + 1; int currRowIndex = templateCellsRow; for (int i = 0; i < dataTable.Rows.Count; i++) { Row newRow = new Row(); newRow.RowIndex = (UInt32)(currRowIndex); for (int j = 0; j < dataTable.Columns.Count; j++) { int styleIndex = -1; if (((Cell)cellLookup[j]).StyleIndex != null) { styleIndex = (int)(((Cell)cellLookup[j]).StyleIndex.Value); } Cell newCell = ExcelReportGenerator.CreateCellBasedOnType(dataTable.Rows[i][j], ExcelColumnReferenceLookup[j] + currRowIndex, styleIndex, ssDoc); newRow.AppendChild(newCell); } sheetData.AppendChild(newRow); currRowIndex++; } var calculationProperties = ssDoc.WorkbookPart.Workbook.CalculationProperties; calculationProperties.ForceFullCalculation = true; calculationProperties.FullCalculationOnLoad = true; calculationProperties.CalculationOnSave = true; var chart = ssDoc.WorkbookPart.ChartsheetParts; foreach (var c in chart) { c.Chartsheet.Reload(); c.Chartsheet.Save(); } // WorkbookPart workbookPart = ssDoc.WorkbookPart; //WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); //string sheetName1 = workbookPart.Workbook.Descendants<Sheet>().ElementAt(1).Name; //string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => sheetName1.Equals(s.Name)).Id; //workbookPart.Workbook.WorkbookProperties.RefreshAllConnections = true; //WorksheetPart worksheetPart1 = (WorksheetPart)workbookPart.GetPartById(relId); //worksheetPart1.Worksheet.Reload(); ////Thread.Sleep(5000); //worksheetPart1.Worksheet.Reload(); }