Problems exporting to Word/Excel using ASP.Net 1

[JulesBos]

Hi all,

I am having problems with exporting a datagrid to Word and Excel. Firstly, I need to do the export as the data needs to be reformatted by users and printed (unless someone can give me an easier way of printing a datagrid, as I haven't found one yet).

I don't mind whether it goes to Word or Excel but I'm having problems with both:

If I export to Excel some of the fields are misrepresented, i.e. a textual field that has a numeric value in it sometimes gets represented in its scientific format when it gets to Excel which I don't want.

If I export to Word, some of the characters get exported incorrectly (probably because I'm from the UK not the US and our keyboard is differently formatted), e.g. the "-" character gets exported as 2 different symbols.

If I export to Word I also get the additional problem that the table is huge (approx 2500 records) and the document crashes, I'm unable to edit it, although if I create the table directly in Word manually this doesn't happen.

Here's the code I'm using:

Response.AddHeader("content-disposition", "attachment;filename=Capability List for Printing.doc")

'Set MIME type to word
Response.ContentType = "application/vnd.ms-word"

'Remove the charset from the Content-Type header.
Response.Charset = ""

' Prepare to export the DataGrid
Dim strw As New System.IO.StringWriter
Dim htmlw As New System.Web.UI.HtmlTextWriter(strw)
' Use the Label control to add the title
strw.GetStringBuilder.Append("<B>")
strw.GetStringBuilder.Append("<U>")
strw.GetStringBuilder.Append("<Font Face='Verdana' Size ='2'>")
strw.WriteLine("Doc No: " + Me.DocNo.Text)
strw.GetStringBuilder.Append("</U>")
strw.GetStringBuilder.Append("<br>")
strw.WriteLine("Revision number: " + Me.RevNumberLabel.Text)
strw.GetStringBuilder.Append("<br>")
strw.WriteLine("Revision date: " + Me.RevDateLabel.Text)
strw.GetStringBuilder.Append("<br>")
strw.WriteLine("Change request number: " + Me.CRLabel.Text)
strw.GetStringBuilder.Append("<br>")
strw.GetStringBuilder.Append("<br>")
strw.GetStringBuilder.Append("</B>")
strw.GetStringBuilder.Append("</Font>")
strw.GetStringBuilder.Append("<table>")
strw.GetStringBuilder.Append("<tr>")
strw.GetStringBuilder.Append("<td colspan=7 align=left")
strw.GetStringBuilder.Append("</td>")
strw.GetStringBuilder.Append("</tr>")

'render the datagrid
PrintDataGrid.RenderControl(htmlw)

' Finish the Excel spreadsheet and send the response
strw.GetStringBuilder.Append("</table>")
Response.Write(strw.ToString())
Response.End()

Any help would be greatly appreciated. I've posted to other sites with no helpful responses so far!

Julia

 

[PGoRule]

There are many links which may help you to export the datagrid contents to Excel/Word/Text...
Export dataview/datagrid as downloadable Excel file
transfering from datagrid to excel spread sheet
Exporting DataGrid to Excel, Word and Text Files

Sharing the best from my side...

--Prashant--

 

[JulesBos]

Thanks for this Prashant. I believe the third link you sent me will solve my problem. Basically it seems I need to use cascading style sheets to give instructions as to the style in which a column should be exported.

This has been a problem for ages for me, so your help is really really appreciated! If I don't post again in the next couple of days, it means it's worked

 

[JulesBos]

Prashant,

Just wanted to let you know that the solution worked like a dream! Excel now accepts data in the format it was intended and doesn't do scientific interpretation or remove leading zeros.

The only problem it doesn't solve is that some characters are still exporting incorrectly - e.g. the character "-" is interpreted as 2 symbols. I'm sure this must be something to do with me having a UK keyboard not a US one, but I don't know how to resolve this. Any ideas?

Thanks
Julia

 

[NoCoolHandle]

Julia, I gave Pogorule a star for you (the best way to say thanks on these forums )..

 

[PGoRule]

That is a known issue with UK Encoding. You need to handle these cases with styles.

You can have a look at this also...
xport to Excel

Sharing the best from my side...

--Prashant--