Export DataTable to Excel

Question

0.00/5 (No votes)

See more:

Hi all i have a problem when i export a datatale to excel. When i open the file Excel says, "The file you are trying to open is in a different format than specified by the file extension. Verify the file is not corrupted and is from a trusted source before opening the file."...if i click yes the file does open properly but is there a way to stop this message from appearing?...the message only appears when i try and open the file with excel 2010 and the message doesn't appear when i open with 2003...I got the code from an article i read. Here is the code.

C#

private void ExporttoExcel(DataTable table,GridView GridView_Result)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=TestingReports.xls");

            HttpContext.Current.Response.Charset = "utf-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
            //sets font
            HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
            HttpContext.Current.Response.Write("<BR><BR><BR>");
            //sets the table border, cell spacing, border color, font of the text, background, foreground, font height
            HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
              "borderColor='#000000' cellSpacing='0' cellPadding='0' " +
              "style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
            //am getting my grid's column headers
            int columnscount = table.Columns.Count;

            for (int j = 0; j < columnscount; j++)
            {      //write in new column
                HttpContext.Current.Response.Write("<Td>");
                //Get column headers  and make it as bold in excel columns
                HttpContext.Current.Response.Write("");
                HttpContext.Current.Response.Write(table.Columns[j].ToString());
                HttpContext.Current.Response.Write("");
                HttpContext.Current.Response.Write("</Td>");
            }
            HttpContext.Current.Response.Write("</TR>");
            foreach (DataRow row in table.Rows)
            {//write in new row
                HttpContext.Current.Response.Write("<TR>");
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    HttpContext.Current.Response.Write("<Td>");
                    HttpContext.Current.Response.Write(row[i].ToString());
                    HttpContext.Current.Response.Write("</Td>");
                }

                HttpContext.Current.Response.Write("</TR>");
            }
            HttpContext.Current.Response.Write("</Table>");
            HttpContext.Current.Response.Write("</font>");
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
        }

Posted 23-Apr-13 21:55pm

Ruwaldo

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Michael Ochmann · Answer 1 · 2013-04-23T22:06:00

Solution 1

Change the file extension to *.xlsx

Posted 23-Apr-13 22:06pm

Michael Ochmann

Comments

Ruwaldo 24-Apr-13 4:08am

Thanks i tried that already and it doesn't open the spreadsheet at all. It says the 'file format or file extension isn't valid'.

cdpace · Answer 2 · 2013-04-23T22:09:00

Hi Ruwaldo,

Its because the file extension is .xls instead of .xlsx in this part of your code

C#

HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=TestingReports.xls");

I tried to create a new excel sheet on my desktop and renamed the file extension to .xls and when I opened the excel sheet it gave me the same warning message.

Hope this helps some how.

cdpace · Answer 3 · 2013-04-23T22:11:00

Solution 3

Hi Ruwaldo,

Its because the file extension is .xls instead of .xlsx in this part of your code

C#

HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=TestingReports.xls");

I tried to create a new excel sheet on my desktop and renamed the file extension to .xls and when I opened the excel sheet it gave me the same warning message.

Hope this helps.

Posted 23-Apr-13 22:11pm

cdpace

Comments

Ruwaldo 24-Apr-13 4:12am

I tried changing that already but it doesn't open the spreadsheet at all when i change the file extension.

cdpace 24-Apr-13 4:33am

Hay m8,

the problem might be from the char-set encoding of utf-8

HttpContext.Current.Response.Charset = "utf-8";

try to change it to a different encoding.

Ruwaldo 24-Apr-13 4:56am

UTF-8 is the usual character set for excel 2010, i changed it to UTF-16 but still get the message...I changed the "(windows-1250)" to "(windows-1252)" and still no change...

Export DataTable to Excel

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Export DataTable to Excel

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us