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++;
}
}
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++;
}
}
}