How do I remove double quotes from Excel cell when data is exported to Excel in VB code?
-
I need help to fix an issue where when data from web application is exported to Excel by clicking a button export to excel, if the data in a cell contains double quotes, that data should be displayed without the double quotes visible. Previously I made a change to the application code in VB so that the output exports text fields with formulas (="") to force Excel to treat those values as a string. This has been working except some instances where the output actually displays the formula characters (="") within the cell as text, rather than as hidden formulas. It appears when a cell contains text with an actual double quotes that is when after export to Excel is done, those quotes appear in Excel. I need help to figure out if there is a way to suppress those. For example. A cell with the following data Allows the user the abilities to Add, View, Modify and Delete Notes on the Notes Tab of the Case Record. "View" allows the user to view the Notes Tab of the Case Record. When this is exported to Excel the data is displayed as follows ="Allows the user the abilities to Add, View, Modify and Delete Notes on the Notes Tab of the Case Record. "View" allows the user to view the Notes Tab of the Case Record. I do not want to quotes to appear in Excel. On the other hand, a cell with the following data Maintain Victim Classification Types. when this is exported to Excel there are no visible quotes. It displays as Maintain Victim Classification Types. Here is my VB code that needed changing
Dim row As DataRow
'Loop through the datatable's rows
For Each row In dt.Rows
'Newline between the previous row and the next row
sw.Write(vbNewLine)Dim column As New DataColumn() 'Loop through each column and write the cell the the stringwriter For Each column In dt.Columns 'If the cell isn't empty write it, else write an empty cell If Not row(column.ColumnName) Is Nothing Then 'WK Old code sw.Write(row(column).ToString().Trim() + vbTab) 'WK new 10/03/2019 'To fix Marsha issue where preceding zero is truncated when export to Excel is processed. 'This line of code enables the SQL query to return the numbers as unchanged strings, not numbers sw.Write("=""" & row(column).ToString().Trim() & """" -
I need help to fix an issue where when data from web application is exported to Excel by clicking a button export to excel, if the data in a cell contains double quotes, that data should be displayed without the double quotes visible. Previously I made a change to the application code in VB so that the output exports text fields with formulas (="") to force Excel to treat those values as a string. This has been working except some instances where the output actually displays the formula characters (="") within the cell as text, rather than as hidden formulas. It appears when a cell contains text with an actual double quotes that is when after export to Excel is done, those quotes appear in Excel. I need help to figure out if there is a way to suppress those. For example. A cell with the following data Allows the user the abilities to Add, View, Modify and Delete Notes on the Notes Tab of the Case Record. "View" allows the user to view the Notes Tab of the Case Record. When this is exported to Excel the data is displayed as follows ="Allows the user the abilities to Add, View, Modify and Delete Notes on the Notes Tab of the Case Record. "View" allows the user to view the Notes Tab of the Case Record. I do not want to quotes to appear in Excel. On the other hand, a cell with the following data Maintain Victim Classification Types. when this is exported to Excel there are no visible quotes. It displays as Maintain Victim Classification Types. Here is my VB code that needed changing
Dim row As DataRow
'Loop through the datatable's rows
For Each row In dt.Rows
'Newline between the previous row and the next row
sw.Write(vbNewLine)Dim column As New DataColumn() 'Loop through each column and write the cell the the stringwriter For Each column In dt.Columns 'If the cell isn't empty write it, else write an empty cell If Not row(column.ColumnName) Is Nothing Then 'WK Old code sw.Write(row(column).ToString().Trim() + vbTab) 'WK new 10/03/2019 'To fix Marsha issue where preceding zero is truncated when export to Excel is processed. 'This line of code enables the SQL query to return the numbers as unchanged strings, not numbers sw.Write("=""" & row(column).ToString().Trim() & """"NB: You've posted this in the C# forum. It should really be in the VB.NET forum, since you're using VB.NET code. You're exporting to a "tab-separated variables" file. You will have very limited control over formatting for this type of file. It would probably be better to export to a "real" Excel file instead. For example, using EPPlus[^]:
Using package As New ExcelPackage()
Dim sheet As ExcelWorksheet = package.Workbook.Worksheets.Add("Sheet1")
sheet.Cells("A1").LoadFromDataTable(dt, True, TableStyles.Medium9)Response.Clear() Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" package.SaveAs(Response.OutputStream)End Using
EPPlus will give you a lot more control over the resulting file: Getting Started · JanKallman/EPPlus Wiki · GitHub[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I need help to fix an issue where when data from web application is exported to Excel by clicking a button export to excel, if the data in a cell contains double quotes, that data should be displayed without the double quotes visible. Previously I made a change to the application code in VB so that the output exports text fields with formulas (="") to force Excel to treat those values as a string. This has been working except some instances where the output actually displays the formula characters (="") within the cell as text, rather than as hidden formulas. It appears when a cell contains text with an actual double quotes that is when after export to Excel is done, those quotes appear in Excel. I need help to figure out if there is a way to suppress those. For example. A cell with the following data Allows the user the abilities to Add, View, Modify and Delete Notes on the Notes Tab of the Case Record. "View" allows the user to view the Notes Tab of the Case Record. When this is exported to Excel the data is displayed as follows ="Allows the user the abilities to Add, View, Modify and Delete Notes on the Notes Tab of the Case Record. "View" allows the user to view the Notes Tab of the Case Record. I do not want to quotes to appear in Excel. On the other hand, a cell with the following data Maintain Victim Classification Types. when this is exported to Excel there are no visible quotes. It displays as Maintain Victim Classification Types. Here is my VB code that needed changing
Dim row As DataRow
'Loop through the datatable's rows
For Each row In dt.Rows
'Newline between the previous row and the next row
sw.Write(vbNewLine)Dim column As New DataColumn() 'Loop through each column and write the cell the the stringwriter For Each column In dt.Columns 'If the cell isn't empty write it, else write an empty cell If Not row(column.ColumnName) Is Nothing Then 'WK Old code sw.Write(row(column).ToString().Trim() + vbTab) 'WK new 10/03/2019 'To fix Marsha issue where preceding zero is truncated when export to Excel is processed. 'This line of code enables the SQL query to return the numbers as unchanged strings, not numbers sw.Write("=""" & row(column).ToString().Trim() & """"I think what you are asking is how to have double quotes inside a string that is being converted into a quoted string. If so, not only do you need to add double quotes at both ends (which you already do), but you ned to double the embedded double quotes, viz:
sw.Write("=""" & Replace(row(column).ToString().Trim(), """", """""") & """" & vbTab)
(It looks yucky because the double quotes have to also be doubled in the 'from' and 'to' arguments in the
Replace()function)
