Unsupported HTML elements causing issue in excel export

Aspose.Cells Product Family
52

@Bhumika.Shah,

You may simply use ImportTableOptions.IsHtmlString to set to “false” when importing data from datatable to Excel spreadsheet using Cells.ImportData() method/overload. This way, your HTML will be imported as plain string/text. But if you need to import HTML string excluding relevant tags, e.g., an HTML string like:
<i>Aniseed</i> Syrup
to be rendered as:
Aniseed Syrup
this is not possible. You have to extract the text separated from tags by yourselves. Either you have to do it in datatable or after you have imported data to worksheet cells via Aspose.Cells APIs.

53

@amjad.sahi

Ok. I get that.

  1. Can you please also update on when you are planning to release the version. So, we can plan accordingly. We have multiple clients waiting for the bug fix.

  2. Can you please check with dev team and confirm that this time they are not planning to replace var with any color which is same for foreground and background. Removal seems to be only option.

54

@Bhumika.Shah,

We have scheduled to release Aspose.Cells for .NET v24.4 next week. However, it is also possible that the new version will be published before the end of this week. We cannot provide a specific date as releases are published once they are ready.

Your understanding is correct.

1 Like
55

@amjad.sahi

Thanks. During certain input, exporting to csv resulted to strange output as below for some user and not every. Can you please check.

Input
<font color=““rgba(0, 0, 0, 0)””><span style=““background-color: var(–color-field-bg); color: var(–color-field-text); font-size: 0px;””>Test</span>

Output
<span class=““ui-provider a b c d e f g h i j k l m n o p q r s t u v w x y z ab ac ae af ag ah ai aj ak”” dir=““ltr””><font color=““rgba(0, 0, 0, 0)””><span style=““background-color: var(–color-field-bg); color: var(–color-field-text); font-size: 0px;””>Test</span>

56

@Bhumika.Shah,

Are you using Cell.HtmlString attribute to set the HTML or are you using the Cells.ImportData() method to import from some data source (e.g., DataTable, etc.)? We would appreciate it if you could provide/paste your complete (runnable) code that could be executed standalone to reproduce the issue on our end. We will check it soon.

PS: When writing a simulation application, if you are importing from a DataTable, please use .NET APIs (e.g., System.Data) to create a DataTable with field(s) containing HTML tags in the code.

57

@Bhumika.Shah
By using the following sample code for testing on the latest version v24.3, we were able to reproduce the exception issue. FormatException occurred when setting Cell.HtmlString.

The sample code as follows:

Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
Cell b6 = cells["B6"];
b6.HtmlString = "<font color=\"rgba(0, 0, 0, 0)\"><span style=\"background-color: var(–color-field-bg); color: var(–color-field-text); font-size: 0px;\">Test</span>";

workbook.Save(filePath + "out.csv");

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-55436

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

1 Like
58

Thank you. Also just wanted to check if you see an option of plain text in export of any use in future? For now, csv is not readable with both html tags and plain text present together. Although there is also a chance that tags are not started or ended correctly which needs to be taken care of. For ex. <span Test</span

59

@Bhumika.Shah
Thank you for your feedback. The current issue is due to an error in the parsing of rgba color. After the FormatException is fixed, we will further test it. Once there are updates, we will notify you promptly.

1 Like
60

I understand this fix is regarding additional class of “a b c d e f …” for csv export.

61

@Bhumika.Shah,

No, we logged a ticket (“CELLSNET-55436”) as we found an exception (“System.FormatException : Input string was not in a correct format”) when setting your HTML string using Cell.HtmlString attribute (see the post for your reference).

For your issue (“additional class of “a b c d e f … for csv export”), we again request you to provide your complete (runnable) code or a standalone sample application to reproduce the issue on our end, so we could resolve it as well.

62

I am using asheet.Cells.ImportData(Datatable). There is no other significant code used here. Please note that it was found during export of some users.

Also, I just wanted to confirm that setting .IsHTMLString to True or False does not make any difference in csv export.

63

@Bhumika.Shah,

I tested using the following sample code. I created a dynamic DataTable in code. I added a column having your mentioned HTML tags in it. When I try to import data using Cells.ImportData, I still get the same exception(“System.FormatException : Input string was not in a correct format”) as we did get using Cell.HtmlString attribute.
e.g.
Sample code:

System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("ID");
table.Columns.Add("Name");
table.Columns.Add("TestField");
string[] addrow = {"1", "Name1", "<font color=\"rgba(0, 0, 0, 0)\"><span style=\"background-color: var(–color-field-bg); color: var(–color-field-text); font-size: 0px;\">Test</span>"};
table.Rows.Add(addrow);

var workbook = new Workbook();
var ws = workbook.Worksheets[0];
ws.Cells.ImportData(table, 0, 0, new ImportTableOptions
{
    IsFieldNameShown = true,
    ConvertNumericData = true,
    IsHtmlString = true


}); //exception
workbook.Save("g:\\test2\\out1.csv");

In short, either you are not giving us actual (input) HTML string or you are using some other code segment or HTML string for your case. Since you are not ready to give us sample code to reproduce the issue on our end, so how could we evaluate your issue or figure it out? Please give us exact sample code (if possible). By the way, we are using Aspose.Cells for .NET v24.3. Are you using same version of some older version, which version? Please try using latest version.

Yes, it won’t make any difference. The reason is since CSV is text file format, so all the formatting would be lost when rendering to CSV.

64

@Bhumika.Shah ,

We are pleased to inform you that your issues CELLSNET-55416, CELLSNET-55417 and CELLSNET-55436 have been resolved. The fix will be included in an upcoming release (Aspose.Cells v24.4) that we plan to release in the first half of April 2024. You will be notified when the next version is released.

65

Thank you. Actually, in my previous input < was replaced to < and > to >. So correct input was not provided.

Please replace < and > in below input accordingly and test.

System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add(“ID”);
table.Columns.Add(“Name”);
table.Columns.Add(“TestField”);
string[] addrow = {“1”, “Name1”, " <font color="rgba(0, 0, 0, 0)"><span style="background-color: var(–color-field-bg); color: var(–color-field-text); font-size: 0px;">Test111</span>
"};
table.Rows.Add(addrow);

var workbook = new Workbook();
var ws = workbook.Worksheets[0];
ws.Cells.ImportData(table, 0, 0, new ImportTableOptions
{
IsFieldNameShown = true,
IsHtmlString = true
}); //exception
workbook.Save(“g:\test2\out1.csv”);

66

Please note that below output was found for some users only during export to csv as I noticed.

Output
<span class=““ui-provider a b c d e f g h i j k l m n o p q r s t u v w x y z ab ac ae af ag ah ai aj ak”” dir=““ltr””><font color=““rgba(0, 0, 0, 0)””><span style=““background-color: var(–color-field-bg); color: var(–color-field-text); font-size: 0px;””>Test

67

You can also refer to the image below where new span was added upon automatically.

image.png (25.0 KB)

68

@Bhumika.Shah
Can you share a file which contains your template codes? Then they will not be escaped .

69

Test.zip (637 Bytes)

Please refer to the above csv file. Please note that it was observed by some of users as for now.

70

@Bhumika.Shah,

I tested your scenario/case using the following sample code (“<” was replaced to “&lt;” and “>” to “&gt;”), it works fine.
e.g.
Sample code:

System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("ID");
table.Columns.Add("Name");
table.Columns.Add("TestField");
string[] addrow = {"1", "Name1", " &lt;font color=\"rgba(0, 0, 0, 0)\"&gt;&lt;span style=\"background-color: var(–color-field-bg); color: var(–color-field-text); font-size: 0px;\"&gt;Test111&lt;/span&gt;<br>"};

table.Rows.Add(addrow);

var workbook = new Workbook();
var ws = workbook.Worksheets[0];
ws.Cells.ImportData(table, 0, 0, new ImportTableOptions
{
IsFieldNameShown = true,
IsHtmlString = true
}); 
workbook.Save("g:\\test2\\out1.csv");

Please find attached the output CSV file for your reference.
out1.zip (289 Bytes)
You will notice the CSV file by Aspose.Cells is rendered as expected.

I did not get your mentioned issue, something similar to:
i.e.,

71

Thats true. This was caused to some user and not all so far. It’s different than previously mentioned which were always reproducible.