CODEWITHSUNDEEP
c#excel
Anu Hardin
Anu Hardin

Reputation: 75

Headers are missing while exporting XML to Excel in C#

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.

Upvotes: 0

Views: 589

Answers (2)

Anu Hardin
Anu Hardin

Reputation: 75

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

Upvotes: 0

Barry O&#39;Kane
Barry O&#39;Kane

Reputation: 1187

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.

Upvotes: 1

Related Questions