0
votes

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();