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