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