I am using a data table, and pass it with open-xml to excel. I want to put the Columns names as the first row. I tried to insert after I get the data table, but it doesn't let me in columns that the DataType is not string. I have tried to iterate the data table, put the first row, and than continue on data table, but I get that the file have only the first header. I guess this is the right approach. Can any one see where my mistake is? The code without the if condition works great, I just get a file without headers.
for (int rowNum = 0; rowNum <= dtResults.Rows.Count; rowNum++)
{
if(rowNum == 0) //ADD HEADERS
{
for(int j=0;j< dtResults.Columns.Count;j++)
{
//create a new list of attributes
attributes = new List<OpenXmlAttribute>();
// add the row index attribute to the list
attributes.Add(new OpenXmlAttribute("r", null, rowNum.ToString()));
//write the row start element with the row index attribute
writer.WriteStartElement(new Row(), attributes);
string value = dtResults.Columns[j].ColumnName;
attributes = new List<OpenXmlAttribute>();
// add data type attribute - in this case inline string (you might want to look at the shared strings table)
attributes.Add(new OpenXmlAttribute("t", null, "str"));
//add the cell reference attribute
attributes.Add(new OpenXmlAttribute("r", "", string.Format("{0}{1}", GetColumnName(j+1), 1)));
//write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
//write the cell value
writer.WriteElement(new CellValue(value));
// write the end cell element
writer.WriteEndElement();
}
writer.WriteEndElement();
continue;
}
//create a new list of attributes
attributes = new List<OpenXmlAttribute>();
// add the row index attribute to the list
attributes.Add(new OpenXmlAttribute("r", null, rowNum.ToString()));
//write the row start element with the row index attribute
writer.WriteStartElement(new Row(), attributes);
for (int columnNum = 1; columnNum <= dtResults.Columns.Count; ++columnNum)
{
//reset the list of attributes
string value = dtResults.Rows[rowNum-1][columnNum-1].ToString();
attributes = new List<OpenXmlAttribute>();
// add data type attribute - in this case inline string (you might want to look at the shared strings table)
attributes.Add(new OpenXmlAttribute("t", null,double.TryParse(value,out double dec) ? "num" : "str"));
//add the cell reference attribute
attributes.Add(new OpenXmlAttribute("r", "", string.Format("{0}{1}", GetColumnName(columnNum), rowNum)));
//write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
//write the cell value
writer.WriteElement(new CellValue(value));
// write the end cell element
writer.WriteEndElement();
}
// write the end row element
writer.WriteEndElement();
}
// write the end SheetData element
writer.WriteEndElement();
// write the end Worksheet element
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = "Large Sheet",
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
// End Sheets
writer.WriteEndElement();
// End Workbook
writer.WriteEndElement();
writer.Close();
document.Close();