Hi ..
I am using datagrid to render my data in excel format.

following is the code
gridExcel.DataSource = viewExcel;
gridExcel.DataBind();
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
System.IO.StringWriter stringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter textWriter = new System.Web.UI.HtmlTextWriter(stringWriter);
gridExcel.RenderControl(textWriter);
Response.Write(stringWriter.ToString());
Response.End();

This code is rendering it as excel but the problem is ..in my query there is field called serial number, this is a varchar field, but contains numeric data ie 000123456..etc..when rendering it is considering it as a numeric field and truncating the trailing zeros..
Can anybody tell me what should I do to solve this problem ?

Thanks,
Gibi

No guarantee's but you might try prefixing text numbers with a single appostrophy ('). I know that if you are actually in Excel and you type "'0012345" (with that leading ' you get a string. I don't know if this would also work when you generate the data from HTML, but it's worth a shot.

If you don't want to change your source data (which you probably won't) use the datagrid's ItemDataBound event to modify each datagriditem's column value.

Peter

The single quote does not work.

After much plowing around, there is a work around for this.

You need to set a style and style attribute for the cells to be formatted in the grid.

The only way I could seem to pass the style through to the Excel sheet was to force it with a string append.

Here is my code to format all of the cells to a "String" format in Excel.

            response.Clear();
            response.Charset = "";
            response.ContentType = "application/vnd.ms-excel";
            StringWriter stringWrite = new StringWriter();
            HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
            DataGrid dg = new DataGrid();
            dg.DataSource = ds.Tables[0];
            dg.DataBind();
            for (int rowPos = 0; rowPos < dg.Items.Count; ++rowPos)
            {
                for (int colPos = 0; colPos < dg.Items[rowPos].Cells.Count; ++colPos)
                {
                    dg.Items[rowPos].Cells[colPos].Attributes.Add("class","NumberString");
                }
            }
            dg.RenderControl(htmlWrite);
            string styleInfo = @"<style>.NumberString {mso-number-format:\@;}</style>";
            response.Write(styleInfo + stringWrite.ToString());
            response.End();

PG

I am working with JSPs and tried this to display the trailing zeros.

For example:

<td STYLE="vnd.ms-excel.numberformat:@"> 000001111 </td>

Check out http://www.greggriffiths.org/webdev/both/excel/ while aimed at Classic ASP, the code and basics presented should be of assistance.

Just wanted to say thanks. This came in handy for a similiar problem I was having. Just curious, do you know of a good resource for the "keys" parameters for the Attribute.Add function?

Thanks again,

Hi, I have a related problem with exporting to Excel using a datagrid. I'm currently supporting pagination and sorting on my datagrid using caching on the underlying dataset. When I export to Excel, I first disable paging and sorting, and for the most part the resulting Excel export works fine. The only minor glitch is that if the user has previously sorted the datagrid, my column header in Excel is replaced by the up or down arrow I've added to indicate sort direction. Note that this isn't a problem on the datagrid itself, only on the resulting Excel output.

Here's the code that adds the sort arrow:

For i = 0 To dgTestExportDataGrid.Columns.Count - 1
      Dim columnSortExpression As String = dgTestExportDataGrid.Columns(i).SortExpression
      If simpleSortExpression = columnSortExpression And columnSortExpression <> "" Then
        Dim cell As TableCell = e.Item.Cells(i)
        Dim lblSorted As New Label
        lblSorted.ID = "lblArrow"
        lblSorted.Font.Name = "webdings"
        lblSorted.Font.Size = FontUnit.XSmall
        If isDescending = True Then
          lblSorted.Text = " 6"
        Else
          lblSorted.Text = " 5"
        End If
        cell.Controls.Add(lblSorted)
      End If
    Next

Also note that my actual Excel export code is very similar to those posted earlier in the thread (using an HTMLTextWriter, etc.) Any help greatly appreciated.

Thanks,
Jeremy

If you have control over Excel and can run VBA then you can set the number format of the columns, if however you are just posting the file vai the browser using the Content Type then you will need to prefix the fields with a single apostrophe '

Good postings, very useful