2

I have a web page that exports data to an excel file. The only problem I am having is when I try and open the excel file I get a message saying "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.". How can I get rid of this message. To do the export I am using a function that I found in another article. Here is the code...

private void ExporttoExcel(DataTable table)
{
    //Response.Clear();
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ClearContent();
    HttpContext.Current.Response.ClearHeaders();
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.ContentType = "application/excel";
    HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=TestingReports");
    HttpContext.Current.Response.Charset = "UTF-8";
    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1252");
    // 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 bgColor='#ffffff' 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;

    // Write in new column
    for (int j = 0; j < columnscount; j++)
    {
        HttpContext.Current.Response.Write("<Td style='font-size:15.0pt; text-align:center; width:80.0pt; border-width:1.0pt; border-color:#000000; border-style:solid; height:22.0pt;'>");
        // Get column headers  and make it as bold in excel columns
        HttpContext.Current.Response.Write("<B>");
        HttpContext.Current.Response.Write(table.Columns[j].ToString());
        HttpContext.Current.Response.Write("</B>");
        HttpContext.Current.Response.Write("</Td>");
    }
    HttpContext.Current.Response.Write("</TR>");

    // Write in new row
    foreach (DataRow row in table.Rows)
    {
        HttpContext.Current.Response.Write("<TR>");
        for (int i = 0; i < table.Columns.Count; i++)
        {
            HttpContext.Current.Response.Write("<Td style='width:80.0pt; text-align:center; border-width:0.5pt; border-color:#000000; border-style:solid; height:22.0pt;'>");
            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();

    //Response.Write("TestingReports.xls");
    //Response.Flush();
    //Response.End();
}

Any help will be much appreciated. Thank you in advance.

Improve this question
1

4 Answers 4

Reset to default
2

Below method is used to export in excel from your c# code.

// fileName = your file name like test.xls
// dt = your data table
// caption = it is caption which display on top of excel file.


 public static void Export(string fileName, DataTable dt, string Caption)
        {


            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1254");
            HttpContext.Current.Response.Charset = "windows-1254"; //ISO-8859-13 ISO-8859-9  windows-1254

            HttpContext.Current.Response.AddHeader(
                "content-disposition", string.Format("attachment; filename={0}", fileName));
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            string header = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\n<head>\n<title></title>\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=windows-1254\" />\n<style>\n</style>\n</head>\n<body>\n";

            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    //  Create a form to contain the grid

                    Table table = new Table();
                    table.GridLines = GridLines.Horizontal;
                    //table.CellSpacing = 17;                                      

                    if (Caption.Trim() != "")
                        table.Caption = "<span style='background-color: #FFFFFF; color: #666666; font-size: 14pt; font-weight: bold; padding: 5px 0px; height: 30px;'>" + Caption + "</span>";

                    TableRow row = null;
                    row = new TableRow();
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        TableHeaderCell headerCell = new TableHeaderCell();
                        headerCell.Text = dt.Columns[i].ColumnName;
                        PrepareControlForExport(headerCell);
                        row.Cells.Add(headerCell);
                    }
                    table.Rows.Add(row);
                    foreach (DataRow rows in dt.Rows)
                    {
                        row = new TableRow();
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            TableCell RowCell = new TableCell();
                            RowCell.Text = rows[i].ToString();
                            PrepareControlForExport(RowCell);
                            row.Cells.Add(RowCell);
                        }
                        table.Rows.Add(row);
                    }

                    //  render the table into the htmlwriter
                    table.RenderControl(htw);

                    //  render the htmlwriter into the response
                    HttpContext.Current.Response.ContentType = "text/csv";
                    HttpContext.Current.Response.Write(header + sw.ToString());
                    HttpContext.Current.Response.End();

                 }
            }
        }

        private static void PrepareControlForExport(Control control)
        {
            for (int i = 0; i < control.Controls.Count; i++)
            {
                Control current = control.Controls[i];
                if (current is LinkButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                }
                else if (current is ImageButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                }
                else if (current is HyperLink)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                }
                else if (current is DropDownList)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                }
                else if (current is CheckBox)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                }
                else if (current is Label)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as Label).Text));
                }

                if (current.HasControls())
                {
                    ReportExport.PrepareControlForExport(current);
                }
            }
        }
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Change your content type to application/ms-excel and include extension in the file name. i.e. 

Response.ContentType = "application/ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=MyFileName.xls");

I have written an article regarding this with 3 different ways earlier. You can use one of those methods.

The HTML table way:

    public void ExportToExcel(DataTable table)
    {
        HttpContext context = HttpContext.Current;
        context.Response.Clear();

        //Begin Table
        context.Response.Write("<table><tr>");

        //Write Header
        foreach (DataColumn column in table.Columns)
        {
            context.Response.Write("<th>" + column.ColumnName + "</th>");
        }
        context.Response.Write("</tr>");

        //Write Data
        foreach (DataRow row in table.Rows)
        {
            context.Response.Write("<tr>");
            for (int i = 0; i < table.Columns.Count; i++)
            {
                context.Response.Write("<td>" + row[i].ToString().Replace(",", string.Empty) + "</td>");
            }
            context.Response.Write("</tr>");
        }

        //End Table
        context.Response.Write("</table>");

        context.Response.ContentType = "application/ms-excel";
        context.Response.AppendHeader("Content-Disposition", "attachment;filename=MyFileName.xls");
        context.Response.End();
    }
Improve this answer

Comments

0

Web sites that use the "application/x-msexcel" or "application/vnd.ms-excel" MIME type to open web page content inside of Microsoft Excel may encounter the following warning prompt when the file attempts to open in Excel 2007:

"The file you are trying to open, '[filename]', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" (Yes | No | Help)

If the user clicks Yes, the file will open as expected. If the user clicks No, the file may open anyway, or may prompt a second time, and then not open if the user selects No again.

More Information on http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/03/11/excel-2007-extension-warning.aspx

Improve this answer

Comments

0

While what your doing would probably work it would also take a VERY long time to read in a medium to large file. Check this excel reader out. Since you have the Dataset all you will have to do is loop through creating a filestream and you will have your excel document

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.