(SOLVED) StringBuilder error

[samflex]

Hi all, We are currently having problem with data exported to excel. When we review the exported file, some show values with exponential format. To try to work around that, I have the code below with stringbuilder to fix the exponential format issue.

Public Sub GetExcel(ByVal dt As DataTable)
    Dim fileName As String = "file" & DateTime.Now.ToString("MMddyyyy") & ".xls"
    Response.AddHeader("content-disposition", "attachment;filename=" & fileName)
    Response.ContentType = "application/vnd.ms-excel"
    Dim stringWriter As StringWriter = New StringWriter()
    Dim htmlWrite As HtmlTextWriter = New HtmlTextWriter(stringWriter)
    Dim dtExportExcel As DataGrid = New DataGrid()
    dtExportExcel.DataSource = dt
    dtExportExcel.DataBind()
    dtExportExcel.RenderControl(htmlWrite)
    Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()
    sb.Append("  table { mso-number-format:'0'; }  ")
    sb.Append(stringWriter & "")
    Response.Write(sb.ToString())
    Response.\[End\]()
End Sub

When I run the code, I get an error on this line:

sb.Append(stringWriter & "")

The error says, Operator '&' is s not defined for types 'StringWriter' and 'String' Any ideas what this means? Thanks in advance

[Lost User]

It means you cannot add a simple string to a StringWriter object, as it makes no sense. Furthermore you cannot append a StringWriter object to a StringBuilder. You can only append strings (or the string values of objects) to a StringBuilder. See StringWriter Constructor (System.IO) | Microsoft Learn[^] for the correct way to combine the two.

[samflex]

Ok, thank you very much for your response. So, this would have been the correct way?

sb.Append("")

[Lost User]

Yes, You can only add strings, or "things" that can be expressed as strings.

[samflex]

Thank you. I saw a similar code but written in C# that has exact same code and users say it worked for them. Thank you for your help. I will try this and hopefully, it works.

[Lost User]

You are writing in C#, or were you not aware?

[Lost User]

Sorry about the other message. But looking at your code again I do not see why the StringWriter, or the HtmlTextWriter, are there, as apart from the following two lines:

Dim stringWriter As StringWriter = New StringWriter()
Dim htmlWrite As HtmlTextWriter = New HtmlTextWriter(stringWriter)

You never refer to either object (other than in the line with the error).

[samflex]

Right, you are correct. I have actually removed them from there but added them some place else like:

dtExportExcel.RenderControl(htmlWrite)
Response.Write(stringWriter.ToString())

[Richard Deeming]

As a simple fix:

Dim sb As New System.Text.StringBuilder()
sb.Append(" table { mso-number-format:'0'; } ")

Dim stringWriter As New StringWriter(sb)
Dim htmlWrite As New HtmlTextWriter(stringWriter)
Dim dtExportExcel As New DataGrid()
dtExportExcel.DataSource = dt
dtExportExcel.DataBind()
dtExportExcel.RenderControl(htmlWrite)

sb.Append("")
Response.Write(sb.ToString())
Response.[End]()

However, note that you are not "exporting an Excel file"; you are rendering HTML content, but lying to the browser and claiming that it's an Excel file. Excel will display a warning message, and then do its best to import that HTML into a new spreadsheet, but you'll get extremely limited control over the results. If you actually want to export an Excel file, with precise control over the formatting and layout, then use a library designed to do that - for example, ClosedXML[^] or the Open XML SDK[^].

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[samflex]

WOW, knowledge is power. Thank you very much sir and great to hear from you again :) With your advise, I was able to successfully use ClosedXML and it is working great. I still have this solution you provided here as a back up. Thank you again.