private void CreateColumnHeader(System.Data.DataTable _DataSet, SheetData sheetData, _GetExcelInfo _ge, UInt32 StartRow) { Row row = new Row(); row.RowIndex = StartRow; Cell cell; sheetData.AppendChild(row); for (int c = 0; c < _DataSet.Columns.Count; c++) { string CurrentColumn = _ge.GetXCellViaNumber(c + 1); cell = CreateTextCell(CurrentColumn, StartRow, _DataSet.Columns[c].ToString()); //cell.StyleIndex = 0; row.AppendChild(cell); } }
public byte[] GenerateExcel(List <DataTable> _DataSet, string Classification) { var stream = new MemoryStream(); SpreadsheetDocument document = SpreadsheetDocument.Create(stream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook); WorkbookPart wbp = document.AddWorkbookPart(); wbp.Workbook = new Workbook(); Sheets sheets = new Sheets(); FileVersion fv = new FileVersion(); fv.ApplicationName = "Microsoft Office Excel"; UInt32 TotalSheets = 1; WorksheetPart[] wsp = new WorksheetPart[TotalSheets]; //Worksheet[] ws = new Worksheet[TotalSheets]; SheetData[] sd = new SheetData[TotalSheets]; Sheet[] sheet = new Sheet[TotalSheets]; Columns thisColumn = new Columns(); for (int i = 0; i < TotalSheets; i++) { wsp[i] = wbp.AddNewPart <WorksheetPart>(); sd[i] = new SheetData(); wsp[i].Worksheet = new Worksheet(); wsp[i].Worksheet.Append(thisColumn); wsp[i].Worksheet.Append(sd[i]); sheet[i] = new Sheet(); } WorkbookStylesPart wbsp = wbp.AddNewPart <WorkbookStylesPart>(); wbsp.Stylesheet = CreateStylesheet(); wbsp.Stylesheet.Save(); _GetExcelInfo _ge = new _GetExcelInfo(); UInt32 HeaderRow = 1; for (int i = 0; i < TotalSheets; i++) { DataColumnCollection _dccColumnID = _DataSet[i].Columns; if (_dccColumnID.Contains("SSN")) { _DataSet[i].Columns.Remove("SSN"); } CreateColumnHeader(_DataSet[i], sd[i], _ge, HeaderRow); CreateHeaderFooter(Classification, wsp, sd, i); CreateContent(_DataSet[i], sd[i], HeaderRow, _ge); AutoSizeColumns(wsp, sd, thisColumn, i); } for (UInt32 i = 0; i < TotalSheets; i++) { //wsp[i].Worksheet.Append(sd[i]); wsp[i].Worksheet.Save(); sheet[i].SheetId = i + 1; sheet[i].Name = "Sheet " + (i + 1); sheet[i].Id = wbp.GetIdOfPart(wsp[i]); sheets.Append(sheet[i]); } wbp.Workbook.Append(fv); wbp.Workbook.Append(sheets); document.WorkbookPart.Workbook.Save(); document.Close(); return(stream.ToArray()); }
private void CreateContent(System.Data.DataTable _DataSet, SheetData sheetData, UInt32 StartRow, _GetExcelInfo _ge) { Cell cell; for (int r = 0; r < _DataSet.Rows.Count; r++) { Row row = new Row(); row.RowIndex = ++StartRow; // row = new DocumentFormat.OpenXml.Spreadsheet.Row { RowIndex = ++rowIndex }; sheetData.AppendChild(row); DataRow dr = _DataSet.Rows[r]; for (int c = 0; c < _DataSet.Columns.Count; c++) { string CurrentColumn = _ge.GetXCellViaNumber(c + 1); cell = CreateTextCell(CurrentColumn, StartRow, _DataSet.Rows[r][c].ToString()); //cell.StyleIndex = 0; row.AppendChild(cell); } } }