how to: vb.net exporting datagrid to excel

[toml]

Hi all

I am exporting the datagrid values to excel. that part is working fine. but when i export the data to excel i am loosing the leading zeros. does any one know how to do this?

Thanks

[trisuglow]

Sounds like your data is being converted to a numeric data type somewhere along the way. If you can keep it as Text then you should keep the leading zeroes.

[toml]

can some one give me an example

[Pirate]

Something like this : When it's string type , it should pertain its zeros like trisuglow said .

Dim I As Integer 
Dim Str As String = I.[B]ToString()[/B]

[Ksyrium]

toml,

I am wanting to export some data out of my datagrid and into excel. Would you mind sharing this with me?

Thanks in advance!!

[CyberHawke]

how are you exporting the data?

Are you using the excel.application object to open a sheet and writing the data cell by cell, or are you opening a datatable using the oledb provider and connecting to your sheet file?

There are a few problems with either and it mostly pertains to how excel interprets the data you are sending to the sheet.

In general excel interprets all numeric data as Numerics, not string, so leading zeros will be truncated unless the column is formatted to display leading zeros.

It would be helpful to see the code you are using to export your data.

[Rockhopper]

If you are looking for a quick and dirty way to export to excel...

You can always export in HTML format... excel picks it up, including formatting, but be warned, the grid background dissapears like when u have a graph in excel (because excel is interpreting HTML).
Then again if you copy the data into a new excel document, the grids work fine.

In the example, dgrTemp is the datagrid to export

 Dim tw As New System.IO.StringWriter
        Dim hw As New System.Web.UI.HtmlTextWriter(tw)
        dgrTemp.RenderControl(hw)
        dgrTemp.Dispose()

        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = " "
        Response.AddHeader("Content-Disposition", "inline;filename=Report.xls;")
        Response.Write("<table border=0>")
        Response.Write("<tr>")
        Response.Write("<td style='font-family:Arial; font-size:12pt; font-weight: lighter'>")
        Response.Write("<b>Report Name</b>")
        Response.Write("<tr></tr>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td>")
        Response.Write("Date: " & Date.Now.ToShortDateString & "  Time: " & Date.Now.ToShortTimeString)
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<td>")
        Response.Write(tw.ToString)
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("</table>")
        Response.End()