Cell.HtmlString is erroring out while setting the value- for a value

amjad.sahi · September 3, 2012, 5:36am

Hi,

If you are using simple text, you should use Cell.PutValue() and not HtmlString property.

Sample code:

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

string cellValue = "Barclays Capital Inc. Barclays US Floating Rate Note < 5 Years Index are trademarks of Barclays Bank PLC and have been licensed for use for certain purposes by BlackRock Institutional Trust Company, N.A. (\"BTC\"). iShares® is a registered trademark of BTC.";

Aspose.Cells.Cell cell = worksheet.Cells["A1"];

cell.PutValue(cellValue);

workbook.Save("e:\\test2\\output.xls");

Corporateitcompliance · September 10, 2012, 7:04am

Hi Amjad Sahi,

Thanks for your reply.

Unfortunately, we are not using simple text values. We have values with local formatting as well. So please provide the solution to work with both “simple text values” and “values with local formatting”.

This Solution is required as soon as possible, as we need to have a fix for a production issue.

Thanks,

Lakshmi Narasaiah C

amjad.sahi · September 10, 2012, 7:22am

Hi,

I am afraid, Aspose.Cells for .NET might not support all the HTML tags, so I recommended you to kindly use PutValue() method and later you may use Aspose.Cells’ Style relevant APIs to format the text for your needs. Also, Aspose.Cells support MS Excel oriented HTML file.

Anyways, please try using the latest version/fix: Aspose.Cells for .NET v7.3.1.2

If you still find any issue, kindly give us complete sample runnable code same as mine, we may log your issue and may look into it.

Thank you.

Corporateitcompliance · September 17, 2012, 4:25am

Hi Amjad Sahi,

Thanks for your reply.

I tried with latest Aspose.Cells.dll, but issue still exists. Please have the sample code below for less-than “<” issue.

Sample Code:

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

string cellValue1 = ““Barclays Capital Inc.” and “Barclays US Floating Rate Note < 5 Years Index” are trademarks of Barclays Bank PLC and have been licensed for use for certain purposes by BlackRock Institutional Trust Company, N.A. (“BTC”). iShares® is a registered trademark of BTC.”;

string cellValue2 = “one is < two”;

Aspose.Cells.Cell cellA1 = worksheet.Cells[“A1”];

Aspose.Cells.Cell cellA2 = worksheet.Cells[“A2”];

cellA1.HtmlString = System.Web.HttpUtility.HtmlDecode( cellValue1 );

cellA2.HtmlString = System.Web.HttpUtility.HtmlDecode( cellValue2 );

workbook.Save("C:\\temp\\result.xls");

Note:

1. for cellValue1: it throws below error:

[ArgumentException: Item has already been added. Key in dictionary: 'FOR'  Key being added: 'FOR']
   System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add) +9355775
   System.Collections.Hashtable.Add(Object key, Object value) +11
   ..(StringBuilder , Hashtable ) +462
   ..(WorksheetCollection , Font , String , ArrayList ) +1249
   ..(Cell , String ) +100
   Aspose.Cells.Cell.set_HtmlString(String value) +5

2. for cellValue2: it does not give error but It trims the value from "<" (less-than):

Sample output: "one is "

Please have these issues get resolved as soon as possible AS we need to support the production for the same issues.

Thanks,

Lakshmi Narasaiah C

amjad.sahi · September 17, 2012, 4:51am

Hi,

Thanks for providing details and sample codes:

1) I can find the exception as you mentioned.

Sample code:

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

string cellValue1 = ““Barclays Capital Inc.” and “Barclays US Floating Rate Note < 5 Years Index” are trademarks of Barclays Bank PLC and have been licensed for use for certain purposes by BlackRock Institutional Trust Company, N.A. (“BTC”). iShares® is a registered trademark of BTC.”;

Aspose.Cells.Cell cellA1 = worksheet.Cells[“A1”];

cellA1.HtmlString = System.Web.HttpUtility.HtmlDecode( cellValue1 );

workbook.Save(“C:\temp\result1.xls”);

It throws below error:

"[ArgumentException: Item has already been added. Key in dictionary: ‘FOR’ Key being added: ‘FOR’] "

I have logged a ticket with an id: CELLSNET-41060 for this issue, we will look into it soon.

2) I can find the second issue as well.

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

string cellValue2 = “one is < two”;

Aspose.Cells.Cell cellA2 = worksheet.Cells[“A2”];

cellA2.HtmlString = System.Web.HttpUtility.HtmlDecode( cellValue2 );

workbook.Save(“C:\temp\result2.xls”);

output: "one is "

I have logged another ticket for this issue with an id: CELLSNET-41061. We will look into this issue as well soon.

Thank you.

shakeel.faiz · September 20, 2012, 4:35am

Hi,

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET v7.3.1.5 and let us know your feedback.

Below is the sample code.

C#

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

string cellValue2 = “one is < two”;

Aspose.Cells.Cell cellA2 = worksheet.Cells[“A2”];

cellA2.HtmlString = System.Web.HttpUtility.HtmlEncode( cellValue2 );

workbook.Save(“C:\temp\result2.xls”);

Corporateitcompliance · September 21, 2012, 6:41am

Hi Shakeel Faiz,

Thanks for your reply. I tried with Aspose.Cells.dll v7.3.1.5 which is attached, but your sample code says HtmlEncode():

cellA2.HtmlString = System.Web.HttpUtility.HtmlEncode( cellValue2 );

But in my case I should use HtmlDecode() only AS my code should handle other values which may have local formatting html tags as well (like , , .)

If I use HtmlEncode(), excel shows these tags as it is, which I don't want.

So please provide the actual fix with System.Web.HttpUtility.HtmlDecode(value);

AND also

I tested CELLSNET-41060 with this latest dll, still it shows same error. Please provide the fix for this as well AS we need to support production issue.

Thanks & Regards

Lakshmi Narasaiah C

amjad.sahi · September 21, 2012, 7:45am

Hi,

1) Well, you have to use HtmlEncode as the fix works using this method.

2) For “CELLSNET-41060”, you have to also use HtmlEncode() method for it as HtmlDecode will give you the error.

Sample code that works fine with the fix we provided to you

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

string cellValue1 = ““Barclays Capital Inc.” and “Barclays US Floating Rate Note < 5 Years Index” are trademarks of Barclays Bank PLC and have been licensed for use for certain purposes by BlackRock Institutional Trust Company, N.A. (“BTC”). iShares® is a registered trademark of BTC.”;

string cellValue2 = “one is < two”;

Aspose.Cells.Cell cellA1 = worksheet.Cells[“A1”];

Aspose.Cells.Cell cellA2 = worksheet.Cells[“A2”];

cellA1.HtmlString = System.Web.HttpUtility.HtmlEncode(cellValue1);

cellA2.HtmlString = System.Web.HttpUtility.HtmlEncode(cellValue2);

workbook.Save(“e:\test2\result1.xls”);

Anyways, we have logged your comments against your existing issue(s). Our concerned developer will look into it if we can support it. If we have some update on it, we will let you know here.

Thank you.

shakeel.faiz · September 23, 2012, 9:35pm

Hi,

Thanks for your posting and using Aspose.Cells.

Please use the following code.

C#

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

string cellValue1 = ““Barclays Capital Inc.” and “Barclays US Floating Rate Note < 5 Years Index” are trademarks of Barclays Bank PLC and have been licensed for use for certain purposes by BlackRock Institutional Trust Company, N.A. (“BTC”). iShares® is a registered trademark of BTC.”;

string cellValue2 = “one is < two”;

Aspose.Cells.Cell cellA1 = worksheet.Cells[“A1”];

Aspose.Cells.Cell cellA2 = worksheet.Cells[“A2”];

cellA1.HtmlString = cellValue1;//cellvalue1 has been encoded.

cellA2.HtmlString = System.Web.HttpUtility.HtmlEncode(cellValue2);

workbook.Save(“d:\filetemp\dest.xls”);

Corporateitcompliance · September 25, 2012, 5:15am

Hi Shakeel Faiz,

Thanks for your code.

But, the above your code won’t works for all my scenarios, below.

Please have below my actual code (foreach()) and provide actual solution as ExpectedResult.xls.

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

string[] cellValues = string.Format("“Barclays Capital Inc.” and “Barclays US Floating Rate Note < 5 Years Index” are trademarks of Barclays Bank PLC and have been licensed for use for certain purposes by BlackRock Institutional Trust Company; N.A. (“BTC”). iShares® is a registered trademark of BTC.,one is < two,Bold,Italic,Underline,H₂O,(a+b)²").Split(’,’);

Aspose.Cells.Cell cellA1 = worksheet.Cells[“A1”];

Aspose.Cells.Cell cellA2 = worksheet.Cells[“A2”];

Aspose.Cells.Cell cellA3 = worksheet.Cells[“A3”];

Aspose.Cells.Cell cellA4 = worksheet.Cells[“A4”];

Aspose.Cells.Cell cellA5 = worksheet.Cells[“A5”];

Aspose.Cells.Cell cellA6 = worksheet.Cells[“A6”];

Aspose.Cells.Cell cellA7 = worksheet.Cells[“A7”];

int i = 0;

foreach (Aspose.Cells.Cell cell in worksheet.Cells)

{

if (cell != null)

{

cell.HtmlString = System.Web.HttpUtility.HtmlDecode(cellValues[i]);

}

i++;

}

workbook.Save(“C:\temp\ExpectedResult.xls”);

Please Find 2 attachments.

Thanks,

Lakshmi Narasaiah C

amjad.sahi · September 25, 2012, 5:38am

Hi Lakshmi,

I think you may replace the line in your code, i.e.

cell.HtmlString = System.Web.HttpUtility.HtmlDecode(cellValues[i]);

with:

cell.HtmlString = cellValues[i];

However, I can still see one significant issue though. For the A2 cell, it still displays:

"one is" instead of "one is < two" but again, you may use HtmlEncode() method for this.

Anyways, we need to further look into it and we will do so soon.

Thank you.

shakeel.faiz · September 26, 2012, 12:33am

Hi,

Thanks for your posting and using Aspose.Cells.

If you want input < as the cell value not html tag, please encode it as “<”

If we get ‘<’ when parsing html string and can get ‘>’ after ‘>’, we think it’s not standard html string and can process it fine.

And we did the following tests in IE with a simple html file:

a)If " one is < two " is input, " one is < two " is displayed.

b)If " one is <two " is input, " one is " is displayed.

We found if a space is followed by < , it should be process as html tag. We will fix this issue soon.

But we still hope you can input standard html string value to ignore unknown issues.

Corporateitcompliance · September 26, 2012, 1:16am

Hi,

Looks like, issue got resolved with " Aspose.Cells for .NET v7.3.1.5 and Code Change:

cell.HtmlString = cellValue;"

instead of cell.HtmlString = System.Web.HttpUtility.HtmlDecode(cellValue);

But still, as you said, if value has “<” instead of “<” issue exists. So please look into this as well and update us back.

I will get back to you, if we face any more issues after deploying to actual application.

Thank you very much Amjad Sahi and Shakeel Faiz for your support.

Regards,

Lakshmi Narasaiah C

shakeel.faiz · October 1, 2012, 1:55am

Hi,

You are welcome.

We will keep looking into your mentioned issue. Once we will have some update/advice for you, we will get back to you asap.

Also, if you face any other issue relating to Aspose.Cells, please let us know, we will be glad to assist you.

Corporateitcompliance · February 12, 2013, 12:06am

Hi,

I have downloaded the version specified for Aspose.cell in the thread. But I am getting Index out of range. Did you guys changed anything in the specified version in thread.. ? As previously I downloaded the dll from this thread it was working fine, and Now I am getting "Index out of Range Error " while setting the value for cell.HtmlString..

Thanks.

amjad.sahi · February 12, 2013, 1:08am

Hi,

Could you paste your sample runnable code here and also provide us the template file(s) if you have any to reproduce the issue on our end. We will check your issue soon.

Also, kindly use our latest version/fix: Aspose.Cells for .NET v7.4.0.3

Thank you.

Corporateitcompliance · February 12, 2013, 5:55am

 cell.HtmlString  is "<Font Style="FONT-WEIGHT: bold;FONT-FAMILY: Arial;FONT-SIZE: 10pt;COLOR: #000000;">Less Fee Waivers &amp; Net Expenses Footnote</Font> "<div><br></div><div>cell.StringValue :-  Less Fee Waivers & Net Expenses Footnote.</div><div><br></div><div> cell.HtmlString = cell.StringValue;  this line is throwing the error  Index out of range. </div><div><br></div>

Corporateitcompliance · February 12, 2013, 6:08am

This is happening for me whenever there is “&” symbol in the cell.StringValue

amjad.sahi · February 12, 2013, 6:34am

Hi,

Thanks for providing us details.

Yes, you are right, I can find the error (“IndexOutOfRangeException”) by using the simplest lines of code.

Sample code:

Workbook workbook = new Workbook();
Worksheet worksheet1 = workbook.Worksheets[0];
Cells cells = worksheet1.Cells;
Aspose.Cells.Cell cell = cells[“A1”];
string val = “Less Fee Waivers & Net Expenses Footnote.”;
cell.HtmlString = val; // error

workbook.Save(“e:\test2\outputcellstring.xlsx”);

I have logged a ticket with an id “CELLSNET-41405” for your issue. We will look into your issue soon.

Thank you.

shakeel.faiz · February 18, 2013, 3:20am

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and try this fix: Aspose.Cells for .NET v7.4.0.4 and let us know your feedback.