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"); }