Cell.HTMLString Property error out when setting a value

Aspose.Cells Product Family
1

Hi,

While setting the value for a cell using cell.HtmlString, I am getting below error:

Item has already been added. Key in dictionary: ‘OF’ Key being added: 'OF

I am using below code to set the value -

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

string cellValue = “Other: <15% of body: $100; ≥15% of body: $500. This benefit is payable only once per covered person, per accident.”;

Aspose.Cells.Cell cell = worksheet.Cells[“A1”];
cell.HtmlString = cellValue;

workbook.Save(“c:\output.xls”);

If I remove “<” from the value or give space between < and 15 it works fine, otherwise it shows error. Is there any way to make it work with the given value. The value might have some inline formatting in it so can’t use PutValue.

Thanks,
Shefali

2

@shefi29

I tried your code with the most recent version and it is not giving any error. Please try it and let us know your feedback.

3

Hi,

Thanks for you reply. I am currently using Version - 8.5.1. Please let me know, how can I solve the issue in this version.

Thanks,
Shefali

4

Hi,

I tried the updated version, but the below code is not showing correct output - .

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

string cellValue = “Other: < 15% of body: $100; ≥15% of body: $500. This benefit is payable only once per covered person, per accident.”;

Aspose.Cells.Cell cell = worksheet.Cells[“A1”];
cell.HtmlString = cellValue;

workbook.Save(“c:\output.xls”);

The text “Other” from the above value should come as bold but it is showing the text as it is without any formatting.

5

@shefi29

Please create your Excel file with desired formatting manually using Microsoft Excel and provide it to us. Please check the sample Excel file created by me manually. You need to provide a similar Excel file. We will then look into this issue further with your file and provide you a correct code.

sampleExcelFile.zip (6.2 KB)

6

Hi,

Thanks for you reply. Currently my organization is using Version - 8.5.1. I would like to first confirm if there is a way to solve the issue in this version. Please provide a solution for this version.

Thanks,

Shefali

7

@shefi29

Please provide your Excel file, we will then give you sample code. Most probably, it will run successfully with 8.5.1 too because it is just one property Cell.HtmlString and it exists in both versions so there should not be any issue.

In short, we need your expected Excel file (which you will create manually using Microsoft Excel) to proceed further.

8

Hi,

My company’s policy do not allow me to upload file to external sites. Is there any email on which I can forward the file. In the file I just need to print below text in the given format. Please provide me code for version 8.5.1 to print it in excel -

Other: <15% of body: $100; ≥15% of body: $500. This benefit is payable only once per covered person, per accident.

Thanks,
Shefali

9

@shefi29

Please see the following sample code, its output Excel file as well as the screenshot. It should fix your issue.

Download Link:
Output Excel File from HTML.zip (5.8 KB)

C#

Workbook wb = new Workbook();

Worksheet ws = wb.Worksheets[0];

Cell cell = ws.Cells["A1"];

cell.HtmlString = "<Font Style=\"FONT-WEIGHT: bold;FONT-FAMILY: Arial;FONT-SIZE: 10.5pt;COLOR: #222222;\">Other</Font><Font Style=\"FONT-FAMILY: Arial;FONT-SIZE: 10.5pt;COLOR: #222222;\">: &lt;15% of body: $100; ≥15% of body: $500. This benefit is payable only once per covered person, </Font><Font Style=\"FONT-WEIGHT: bold;FONT-STYLE: italic;FONT-FAMILY: Arial;FONT-SIZE: 10.5pt;COLOR: #222222;\">per accident</Font><Font Style=\"FONT-FAMILY: Arial;FONT-SIZE: 10.5pt;COLOR: #222222;\">.</Font>";

wb.Save("output.xlsx");

Screenshot:

10

Hi,

Thanks for reply. I can see you have replaced the < sign with “&lt” manually. Actually the input will be provided by user, so I can not do it manually. Using String.Replace() or HttpUtility.Encode() will replace all < sign with &lt. So that’s not a feasible solution.

Please provide a solution that will work with all inputs containing < sign followed by any character.

Thanks,
Shefali

11

@shefi29

I think that is not possible. It is the HTML question not Aspose.Cells question. If you think, it is Aspose.Cells question, then provide me the good HTML that works in Web Browser and that does not replace < with &lt.

12

Hi,

I am OK to replace < with &lt. I just want to know if I should replace all < with &lt in my string to make it work globally with all inputs provided by user?

Thanks,
Shefali

13

@shefi29

< is a special character in HTML. Because, all tags of HTML are composed of it. e.g.

<h1>
<p>
<table>

When < comes in HTML tags, you must not change them to &lt or &gt

But when they come inside the body of the html tag, you must change (encode) them.

For example consider this sample code and its output Excel file.
output excel file.zip (5.6 KB)

C#

Workbook wb = new Workbook();

Worksheet ws = wb.Worksheets[0];

Cell cell = ws.Cells["A1"];

cell.HtmlString = "<p>this is &lt; and &gt; chars.</p>";

wb.Save("output.xlsx");
14

Hi,

Thanks for reply. One thing I dont’ understand is why do I need to change < to &lt and don’t need to change > to &gt when using Cell.HtmlString. Also, in case of < when there is a space between < and the character it works fine. Ex - “This is < with space”. But when there is no space, it error out. Ex - “This is <without space”.

Additionally, my user input contains basic html tags like - “< b >,< I >,< U >”. Currently, they are parsed by HTMLString and input is formatted in excel appropriately. As per your solution, I would need to first identify all HTML tags in user input and not replace with &lt or &gt. That would require ample code. Isn’t there any alternative for this.

Thanks,
Shefali

Thanks,
Shefali

15

The above code is not working in version 8.5.1. It shows same error that I was previously getting without replacing < with &lt.

Thanks,
Shefali

16

@shefi29

Please download the following

and try it at your end. It is working good and not throwing any exception. It generates the

  • output-8.5.1.0.xlsx

in Debug directory.

17

Hi,

Thanks for reply. Again access to dropbox is prohibitted in my company. Unable to download the project. Please paste the code here or send the project to my mail if possible.

Thanks,
Shefali

18

@shefi29

I have sent you a message. It has my email address. Email me or provide me your email by replying the message.

19

@shefi29

I have uploaded the sample project in parts. Please download 1.zip and 2.zip and place them in some empty folder. Then click Extract Here using the WinRAR tool as shown in the following screenshot.

Parts Download Links:
1.zip (2 MB)
2.zip (1.1 MB)

Screenshot:

20

Thanks Shakeel. I am able to download the project now. I will check and let you know if it works for me.