Export to Excel issue

Question

4.00/5 (1 vote)

See more:

I am having trouble with exporting a certain spreadsheet column into excel, and having excel see the values as a date. I have seen many explanations and fixes for this symptom, but nothing seems to work for me. It is a grid view of a database query that contains in one column (NPK), with values such as 8-16-8. When this gets to Excel, it is displayed as 8/16/2008, even though in my vb code I am indicating the data to be text. Gridview and code-behide follows:

Part

VB

<%@ Page Title="" Language="VB" MasterPageFile="~/MasterContent/MasterPage.master" EnableEventValidation="false" AutoEventWireup="false" CodeFile="fertall.aspx.vb" Inherits="Secure_fertall" %>

<asp:Content ID="Content1" ContentPlaceHolderID="pageContent" Runat="Server">
    <link href="../CSS/pages.css" rel="stylesheet" type="text/css" />
    <h1>List all registered fertilizer dealers</h1>
    <asp:Label ID="lblExcel" runat="server" Style="position: relative" Text="Export List to Excel"></asp:Label>
      
<asp:ImageButton ID="ImageButton1" runat="server" ImageUrl="~/images/Excel-32-d.gif"
                            Style="position: relative; left: -17px; top: -2px;" OnClick="btnExportExcel_Click" />
    <br />
    <br />
    <asp:GridView ID="GridView1" runat="server" AllowPaging="False" 
        AllowSorting="False" AutoGenerateColumns="False" DataKeyNames="lngLicNum" 
        DataSourceID="SQLDataSource1">
        <Columns>
            <asp:BoundField DataField="lngLicNum" HeaderText="License Number" ReadOnly="True" 
                SortExpression="lngLicNum" />
            <asp:BoundField DataField="txtCoName" HeaderText="Company" 
                SortExpression="txtCoName" />
            <asp:BoundField DataField="txtMailAddress" HeaderText="Address" 
                SortExpression="txtMailAddress" />
            <asp:BoundField DataField="txtMailCity" HeaderText="City" 
                SortExpression="txtMailCity" />
            <asp:BoundField DataField="txtMailState" HeaderText="State" 
                SortExpression="txtMailState" />
            <asp:BoundField DataField="txtMailZip" HeaderText="Zip" 
                SortExpression="txtMailZip" />
            <asp:BoundField DataField="dtExpiration" HeaderText="Expiration Date" 
                SortExpression="dtExpiration" />
            <asp:BoundField DataField="txtBrand" HeaderText="Brand Name"
            SortExpression="txtBrand" />
            <asp:BoundField DataField="NPK" HeaderText="NPK Values"
            SortExpression="NPK" />
            <asp:BoundField DataField="ProdExp" HeaderText="Product Expiration"
            SortExpression="ProdExp" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:FertilizerConnectionString %>" 
    SelectCommand="SELECT * FROM [FertProd] ORDER BY [txtCoName]"></asp:SqlDataSource>
    </asp:Content>

Code-behind---

Imports System.IO

Partial Class secure_fertall
    Inherits System.Web.UI.Page

    Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim style As String = "<style> td { mso-number-format:\@; } </style> "
        Response.AddHeader("content-disposition", "attachment;filename=fertilizer.xls")
        Response.Charset = String.Empty
        Response.ContentType = "application/vnd.xls"
        Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
        Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
        GridView1.RenderControl(hw)
        Response.Write(style)
        Response.Write(sw.ToString())
        Response.End()
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

    End Sub

I should also mention that this gridview aspx page is based from a master page, if it makes a difference. I have tried many variations of the above code, but nothing seems to get the NPK column to render to excel as a text field.

Posted 8-Mar-13 6:45am

Member 9397200

Updated 8-Mar-13 6:46am

Richard C Bishop

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

ZurdoDev · Answer 1 · 2013-03-08T08:17:00

Here is code I have used before that works. The issue is you need to get that style that you are applying wrapped around the cells. In your case you are just dumping it into the Response but not actually applying it to any cells. This should work:

C#

private void ExportGrid(string fileName)
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
DataTable dt = (DataTable)DataGrid1.DataSource; 

	// here, we loop through each row and then each cell and check the data type.  We then add the appropriate class              	 	

	foreach (DataGridItem itm in DataGrid1.Items)              	
	{                              		
		for (int i = 0; i < itm.Cells.Count; i++)                 		
		{                      			
			TableCell tc = itm.Cells[i];                      			

			switch dt.Columns[i].DataType.ToString().ToLower())                     			

			{                          				

				case "system.string":                              					

					tc.Attributes.Add("class", "text");                              					

					break;       				

				case "system.datetime":                              					

					tc.Attributes.Add("class", "dt");                             					

					break;                           				

				case "system.decimal":                              					

					tc.Attributes.Add("class", "num");                              					

					break;                          				

				default:                              					

					break;                     			

			}                 		

		}            	

	}             

	DataGrid1.RenderControl(htw);              	

	StringBuilder sb = new StringBuilder();              	

	sb.Append(@"<style> ");              	

	sb.Append(@" .text { mso-number-format:\@; } ");              	

	sb.Append(@" .num { mso-number-format:0\.00; } ");              	

	sb.Append(@" .dt { mso-number-format: ’Short Date’; } ");              	

	sb.Append(@"</style>");                

	Response.Write(sb.ToString());  // write the styles to the Response           	

	Response.Write(sw.ToString());             	

	Response.Flush();              	

	Response.End();         

}

Sorry, the formatting didn't paste in too well.