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 OutputRawData(object[][] inputData, SpreadsheetDocument ssDoc, string sheetName)
{
SheetData sheetData = ExcelReportGenerator.GetSheetData(ssDoc, sheetName);
int currRowIndex = 1;
for (int i = 0; i < inputData.GetLength(0); i++)
{
Row newRow = new Row();
newRow.RowIndex = (UInt32)(currRowIndex);
for (int j = 0; j < inputData[i].Count(); j++)
{
Cell newCell = ExcelReportGenerator.CreateInlineTextCell(
inputData[i][j].ToString(), ExcelColumnReferenceLookup[j] + currRowIndex);
newRow.AppendChild(newCell);
}
sheetData.AppendChild(newRow);
currRowIndex++;
}
}
static ExcelReportGenerator()
{
ExcelReportGenerator.InitializeDefaultDocument();
}
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();
}
private static void OutputRawData(GridView dataTable, SpreadsheetDocument ssDoc, string sheetName)
{
SheetData sheetData = ExcelReportGenerator.GetSheetData(ssDoc, sheetName);
ssDoc.WorkbookPart.WorkbookStylesPart.Stylesheet = GenerateStyleSheet();
int currRowIndex = 1;
foreach (System.Web.UI.WebControls.GridViewRow gridViewRow in dataTable.Controls[0].Controls)
{
if (gridViewRow.RowType != DataControlRowType.Footer)
{
Row newRow = new Row();
newRow.RowIndex = (UInt32)currRowIndex;
int colIndex = 0;
foreach (System.Web.UI.WebControls.TableCell gTableCell in gridViewRow.Cells)
{
Cell newCell = ExcelReportGenerator.CreateTextCell(gTableCell.Text, ExcelColumnReferenceLookup[colIndex] + currRowIndex);
if (gridViewRow.RowType == DataControlRowType.Header)
{
newCell.StyleIndex = 7;
}
colIndex = colIndex + (gTableCell.ColumnSpan > 0 ? gTableCell.ColumnSpan - 1 : 0);
newRow.AppendChild(newCell);
colIndex++;
}
sheetData.AppendChild(newRow);
currRowIndex++;
}
}
int mergRowIndex = 1;
Worksheet worksheet = GetWorksheet(ssDoc, sheetName);
foreach (System.Web.UI.WebControls.GridViewRow gridViewRow in dataTable.Controls[0].Controls)
{
int colIndex = 0;
foreach (System.Web.UI.WebControls.TableCell gTableCell in gridViewRow.Cells)
{
int colSpn = gTableCell.ColumnSpan > 0 ? gTableCell.ColumnSpan - 1 : 0;
if (colSpn != 0)
{
MergeCells mergeCells;
if (worksheet.Elements<MergeCells>().Count() > 0)
mergeCells = worksheet.Elements<MergeCells>().First();
else
{
mergeCells = new MergeCells();
// Insert a MergeCells object into the specified position.
if (worksheet.Elements<CustomSheetView>().Count() > 0)
worksheet.InsertAfter(mergeCells, worksheet.Elements<CustomSheetView>().First());
else
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
}
// Create the merged cell and append it to the MergeCells collection.
MergeCell mergeCell = new MergeCell()
{
Reference =
new StringValue(ExcelColumnReferenceLookup[colIndex] +
mergRowIndex + ":" +
ExcelColumnReferenceLookup[
colIndex + colSpn] +
mergRowIndex)
};
mergeCells.Append(mergeCell);
worksheet.Save();
}
colIndex = colIndex + colSpn;
colIndex++;
}
}
}
