1. Home
  2. Forum
  3. WinForms
  4. XlsIO error

XlsIO error

Hi,

I am trying to use syncfusion XlsIO component to build Excel from Html table HTML table is built by code on the fly. Some text has ; (semicolon) in the <font> tag, which is failing while using ImportHtmlTable with following error.

Error: 

'<', hexadecimal value 0x3C, is an invalid attribute character. Line 1, position 122.


Html table used to build Excel: Check the last <td> data, I have put the comment where it fails (inside the <font> tag when ; is where it fails. Interestingly, it only fails when ; is used within <font> tag with a Style attribute, otherwise it works fine.


<html>

<head>

<title>AMC</title>

</head>

<body>

                                <table border="0" cellpadding="0" cellspacing="0" style='border-collapse:collapse;'>

                    <tr>

                    <td>

                    <b>Buyer:</b> Sample Buyer

                    </td>

                    </tr>


                    <tr>

                <td>

<font Style="font-size:8.0pt">716-224 Adding Semicolon in the font tag caused error;  when there is a style attribute within a font tag. If style is removed, it works too</font>

If the semicolon without the font tag, it workes fine; -- this is working good

</td>

</tr>

</table>

</body>

</html>


9 Replies
SU Sunil November 25, 2021 09:05 PM UTC

Hi, I think the issue is there is a missing ; in the style attribute. When I add that it works fine.


Also - can you tell me how to set the Excel Column/Cell data format in html? say, I want to set certain column number type and others general and may be others in currency. Is that possible to set in the HTML styling ?


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team November 26, 2021 02:35 PM UTC

Hi Sunil, 

Greetings from Syncfusion. 

We are checking the query and will get back to you with details on November 29th,2021. 

Regards, 
Keerthi. 


SU Sunil November 27, 2021 08:29 PM UTC

Thanks Keerthi! Also, is there an efficient way to achieve below step?


Dim lastRow As Int32 = worksheet.UsedRange.LastRow

                Dim rng As IRange = worksheet.Range("F7:F" & lastRow.ToString())

                rng.CellStyle.Font.Bold = True


Excel has XlUp and XlDown to find an end cell in a range, is that supported by XLSIO API?


Thanks.

Sunil


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team November 29, 2021 01:07 PM UTC

Hi Sunil, 

We appreciate your patience. 

It is not possible to set the number format while importing HTML table to Excel. But you can set the format in Excel file, after importing. Please go through the below link to know about applying number formats. 


Regarding, applying the cell style to Excel cells, if you are trying to apply cell style to entire used range. Then you can use the below code snippet. 

Code Snippet: 

worksheet.UsedRange.CellStyle.Font.Bold = True 

If you are trying to apply cell style to some custom range (not the entire used range), then we recommend you to use the code which you already have at your end. This would satisfy your requirement. 

Kindly let un know if you need any further assistance. 

Regards, 
Keerthi. 


SU Sunil December 10, 2021 03:34 AM UTC

Thank you Keerthi for the response! When I use a range to update format, it is extremely slow. Any suggestion how to optimize it?


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team December 13, 2021 11:35 AM UTC

Hi Sunil, 

You can use IMigrantRange instead of IRange to optimize the performance. Please look into the following link for more information. 

We have modified the code for your reference and this sample code is given below. 

Dim worksheet As IWorksheet = workbook.Worksheets(0) 
 
Dim usedRange As IRange = worksheet.UsedRange 
Dim rowCount As Integer = usedRange.LastRow 
Dim colCount As Integer = usedRange.LastColumn 
 
Dim migrantRange As IMigrantRange = worksheet.MigrantRange 
 
Dim row As Integer 
Dim column As Integer 
 
For row = 1 To rowCount Step 1 
    For column = 1 To colCount Step 1 
        migrantRange.ResetRowColumn(row, column) 
        migrantRange.CellStyle.Font.Bold = True 
    Next 
Next 

Kindly try this code snippet and let us know if this helps. 

Regards, 
Keerthi. 


SU Sunil replied to Konduru Keerthi Konduru Ravichandra Raju January 6, 2022 09:20 PM UTC

Thanks Keerthi! I will try this and let you know if any issues. I keep running into parsing error with importing data into HTML table format. Now the issue is when a value has '&' symbol in it. This is the error I am getting and the value is is error out: Please advise, if should try alternative solution instead of importing data into HTML and then excel.


HTML Value - 

 <td align="left" class="xl25">Food & Oils Company</td>


Error:

An error occurred while parsing EntityName. Line 1072, position 68.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Xml.XmlException: An error occurred while parsing EntityName. Line 1072, position 68.

Source Error:

Line 294:
Line 295:                ' Import data
Line 296:                worksheet.ImportHtmlTable(MStream, 1, 1)
Line 297:
Line 298:

Source File: C:\Users\sunil\source\repos\FMCWebAppNew\report\rPricing.aspx.vb    Line: 296

Stack Trace:

[XmlException: An error occurred while parsing EntityName. Line 1072, position 68.]
   System.Xml.XmlTextReaderImpl.Throw(Exception e) +73
   System.Xml.XmlTextReaderImpl.Throw(String res, String arg) +121
   System.Xml.XmlTextReaderImpl.ParseEntityName() +77
   System.Xml.XmlTextReaderImpl.ParseEntityReference() +49
   System.Xml.XmlTextReaderImpl.Read() +5389951
   System.Xml.XmlLoader.LoadNode(Boolean skipOverWhitespace) +57
   System.Xml.XmlLoader.LoadDocSequence(XmlDocument parentDoc) +21
   System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace) +133
   System.Xml.XmlDocument.Load(XmlReader reader) +109
   System.Xml.XmlDocument.LoadXml(String xml) +168
   Syncfusion.XlsIO.Implementation.HtmlToExcelConverter.ParseHTMLTable(String htmlText, WorksheetImpl worksheetImpl, Int32 row, Int32 col) +135
   Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportHtmlTable(Stream fileStream, Int32 row, Int32 column) +203
   FMC.rPricing.WriteReport() in C:\Users\sunil\source\repos\FMCWebAppNew\report\rPricing.aspx.vb:296
   FMC.rPricing.btnReportDownload_Click(Object sender, EventArgs e) in C:\Users\sunil\source\repos\FMCWebAppNew\report\rPricing.aspx.vb:183
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9796242
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +211
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1696


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team January 7, 2022 01:18 PM UTC

Hi Sunil, 

We are able to reproduce the reported issue at our end and validating it currently. We will share the validation details on January 11th,2022. 


Query 
Response 
Please advise, if should try alternative solution instead of importing data into HTML and then excel. 
You can directly import data from Data table to Excel instead of importing into HTML. 
Please look into following link to Import data to Excel. 



Regards, 
Ramya. 


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team January 11, 2022 11:41 AM UTC

Hi Sunil, 

We appreciate your patience. 

On validating the issue, an HTML string with either & or &amp; in the value, is rendered as & symbol in the HTML document. But, Syncfusion XlsIO depends on the XMLDocument object to load HTML string in which the “&” symbol is invalid.  So an exception is thrown while using the “&” symbol alone.  

To overcome this, we suggest you to change & as &amp; Please find the modified text below. 

Modified Text: 

<td align="left" class="xl25">Food &amp; Oils Company</td> 

Kindly try the suggestion and let us know if this helps. 

Regards, 
Keerthi. 


Loader.
Up arrow icon