private bool VerifyColumnHeaders(DataSet importDataset, List <string> badSheets)
{
bool hasValidCoumnSet = true;
int count = importDataset.Tables.Count;
for (int sheetIndex = 0; sheetIndex < count; sheetIndex++)
{
var excelSheet = importDataset.Tables[sheetIndex];
//Console.WriteLine($"sheets {excelSheet.TableName}");
// validate column Headers
int columnCount = excelSheet.Columns.Count;
var sheetColumns = excelSheet.Columns;
List <string> badColumns = new List <string>();
for (int col = 0; col < columnCount; col++)
{
if (!FileHeaderDefinitions.ColumnDefinitions().Contains(sheetColumns[col].ColumnName.Replace(" ", string.Empty)))
{
badColumns.Add(sheetColumns[col].ColumnName);
hasValidCoumnSet = false;
}
}
if (badColumns.Any())
{
badSheets.Add($"Sheet : {excelSheet.TableName}, Columns : { string.Join(" ,", badColumns) } ");
}
var rowCount = excelSheet.Rows.Count;
Console.WriteLine($"number of rows : {rowCount}");
}
return(hasValidCoumnSet);
}
public void writeDataSetToSheet(string fileName, DataSet dataSet)
{
using (SpreadsheetDocument xl = SpreadsheetDocument.Open(fileName, true))
{
List <OpenXmlAttribute> oxa;
OpenXmlWriter oxw;
WorkbookPart workbookPart = xl.WorkbookPart;
WorksheetPart wsp = workbookPart.AddNewPart <WorksheetPart>();
oxw = OpenXmlWriter.Create(wsp);
oxw.WriteStartElement(new Worksheet());
oxw.WriteStartElement(new SheetData());
//write headers
oxa = new List <OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("r", null, 1.ToString()));
oxw.WriteStartElement(new Row(), oxa);
foreach (var item in FileHeaderDefinitions.ColumnDefinitions())
{
WriteRowCells(oxw, item);
}
oxw.WriteEndElement();
var dataTable = dataSet.Tables[0];
var count = dataTable.Rows.Count;
for (int i = 0; i < count; ++i)
{
oxa = new List <OpenXmlAttribute>();
var index = i + 2;
// this is the row index
oxa.Add(new OpenXmlAttribute("r", null, index.ToString()));
oxw.WriteStartElement(new Row(), oxa);
//schoolcode
WriteRowCells(oxw, dataTable.Rows[i].Field <String>(FileHeaderDefinitions.SchoolCode));
//schoolName
WriteRowCells(oxw, dataTable.Rows[i].Field <String>(FileHeaderDefinitions.SchoolName));
//FirstName
WriteRowCells(oxw, dataTable.Rows[i].Field <String>(FileHeaderDefinitions.FirstName));
//MiddleName
WriteRowCells(oxw, dataTable.Rows[i].Field <String>(FileHeaderDefinitions.MiddleNameDesc));
//LastName
WriteRowCells(oxw, dataTable.Rows[i].Field <String>(FileHeaderDefinitions.LastName));
//DOB
WriteRowCells(oxw, dataTable.Rows[i].Field <DateTime>(FileHeaderDefinitions.DOB).Date.ToString());
//for (int j = 1; j <= 100; ++j)
//{
// oxa = new List<OpenXmlAttribute>();
// // this is the data type ("t"), with CellValues.String ("str")
// oxa.Add(new OpenXmlAttribute("t", null, "str"));
// // it's suggested you also have the cell reference, but
// // you'll have to calculate the correct cell reference yourself.
// // Here's an example:
// //oxa.Add(new OpenXmlAttribute("r", null, "A1"));
// oxw.WriteStartElement(new Cell(), oxa);
// oxw.WriteElement(new CellValue(string.Format("R{0}C{1}", i, j)));
// // this is for Cell
// oxw.WriteEndElement();
//}
// this is for Row
oxw.WriteEndElement();
}
// this is for SheetData
oxw.WriteEndElement();
// this is for Worksheet
oxw.WriteEndElement();
oxw.Close();
oxw = OpenXmlWriter.Create(xl.WorkbookPart);
oxw.WriteStartElement(new Workbook());
oxw.WriteStartElement(new Sheets());
// you can use object initialisers like this only when the properties
// are actual properties. SDK classes sometimes have property-like properties
// but are actually classes. For example, the Cell class has the CellValue
// "property" but is actually a child class internally.
// If the properties correspond to actual XML attributes, then you're fine.
oxw.WriteElement(new Sheet()
{
Name = "Sheet1",
SheetId = 1,
Id = xl.WorkbookPart.GetIdOfPart(wsp)
});
// this is for Sheets
oxw.WriteEndElement();
// this is for Workbook
oxw.WriteEndElement();
oxw.Close();
xl.Close();
}
Console.WriteLine($" Student Data Done Writing");
}
