Headers are missing while exporting XML to Excel in C#

Question

In my application I have an xml file.i want to export the xml into Excel file in xls format.My problem is is while exporting headers are missing.Please find the code below.

private string ExportTestDatatoExcel(string testDataPath)
    {
        Microsoft.Office.Interop.Excel.Application xlApp;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        DataSet ds = new DataSet();
        XmlReader xmlFile;
        int i = 0;
        int j = 0;

        xlApp = new Microsoft.Office.Interop.Excel.Application();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xmlFile = XmlReader.Create(testDataPath, new XmlReaderSettings());
        ds.ReadXml(xmlFile);

        for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
        {
            for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
            {
                xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
            }
        }
        string fileName = Path.GetFileNameWithoutExtension(testDataPath);
        fileName += ".xls" ;
        xlWorkBook.SaveAs(Path.GetFullPath(fileName), Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, 
            misValue, misValue, misValue, misValue, 
            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlApp);
        releaseObject(xlWorkBook);
        releaseObject(xlWorkSheet);
        return Path.GetFullPath(fileName);
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
        }
        finally
        {
            GC.Collect();
        }
    }

Please help me.

Barry O'Kane · Accepted Answer · 2016-07-05 08:18:58Z

1

Your code is writing out the data as expected.

Just before your for loop you need to add another for loop to add the column headers in the first row of the sheet.

for(int k = 0; k < ds.Tables[0].Columns.Count - 1; k++)
{
    xlWorkSheet.Cells[1, k + 1] = ds.Tables[0].Columns[k].ColumnName;
}

then in our outter for loop you need to initialize i = 1 to account for the header row.

answered Jul 5, 2016 at 8:18

Barry O'Kane

1,1877 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1274820 · Accepted Answer · 2019-12-03 21:03:15Z

0

Thanks Barry for the reference.It should work when the loop is as follows

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
    for (j = 0; j <= ds.Tables[0].Columns.Count-1; j++)
    {
        for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
        {                        
            xlWorkSheet.Cells[1, k + 1] = ds.Tables[0].Columns[k].ColumnName;   
        }
        xlWorkSheet.Cells[i + 2, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
    }
}

edited Dec 3, 2019 at 21:03

user1274820

8,1894 gold badges39 silver badges81 bronze badges

answered Jul 5, 2016 at 12:58

Anu Hardin

758 bronze badges

Collectives™ on Stack Overflow

Headers are missing while exporting XML to Excel in C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related