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 = CreateContentCell(CurrentColumn, StartRow, _DataSet.Columns[c].ToString(), CellValues.InlineString);
//cell.StyleIndex = 0;
row.AppendChild(cell);
}
}
public byte[] GenerateExcel(List <System.Data.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("rownumb"))
{
_DataSet[i].Columns.Remove("rownumb");
}
if (_dccColumnID.Contains("SSN"))
{
_DataSet[i].Columns.Remove("SSN");
}
if (_dccColumnID.Contains("DOC_MAP")) //Catch all to rename this column whenever present.
{
_DataSet[i].Columns["DOC_MAP"].ColumnName = "ASG";
}
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)
{
DataColumnCollection dsColumns = _DataSet.Columns;
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);
DataColumn thisColumn = _DataSet.Columns[c];
CellValues CellDataType = CellValues.InlineString;
string colName = thisColumn.ColumnName.ToUpper();
if (colName == "REQ" ||
colName == "AUTH" ||
colName == "DOC_MAP" ||
colName == "ASG")
{
CellDataType = CellValues.Number;
}
//strip time formatting for DEROS_DATE
if (colName == "DEROS_DATE" ||
colName == "DATE_ARRIVED")
{
string val = _DataSet.Rows[r][c].ToString();
if (!string.IsNullOrEmpty(val))
{
DateTime dt;
if (DateTime.TryParse(val, out dt))
{
val = dt.ToString("yyyy-MM-dd");
}
}
cell = CreateContentCell(CurrentColumn, StartRow, val, CellDataType);
}
else
{
cell = CreateContentCell(CurrentColumn, StartRow, _DataSet.Rows[r][c].ToString(), CellDataType);
}
//cell.StyleIndex = 0;
row.AppendChild(cell);
}
}
}