CODEWITHSUNDEEP
c#asp.net-mvcasp.net-mvc-3export-to-excel
Joy
Joy

Reputation: 21

Convert xml to excel

As I am trying to convert xml file to excel.

I am able to do this with .xls file extensions only. When I try to convert xml file to excel with .xlsx extension I am able to create the excel file, but am unable to open it.

When I attempt to open the created excel file I receive the following Error message:

Excel cannot open the file 'Request.xlsx'because the file format or the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

Here is my code:

dt = project2.ConvertXMLToDataSet(xmlString);

if (dt.Rows.Count > 0)
{

        string filename = xmlfileName + ".xlsx";
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        DataGrid dgGrid = new DataGrid();
        dgGrid.DataSource = dt;
        dgGrid.DataBind();
        dgGrid.RenderControl(hw);
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AppendHeader("Content-Disposition",
                              "attachment; filename=" + filename + "");
        Response.Write(tw.ToString());
        Response.End();

}

Upvotes: 1

Views: 7684

Answers (2)

Meikel71
Meikel71

Reputation: 46

I found two older post here on stackoverflow. I hope one of them will help:

Upvotes: 2

trailmax
trailmax

Reputation: 35106

You can't just dump datagrid into response and expect it to be picked up by Excel. You need to convert data in proper format. I find NPOI the most appropriate tool to do that. It is quite a bit of extra work, but you'll be able to do what you need to do and you'll have much more control over the resulting Excel file.

Upvotes: 1

Related Questions