Put value more that 32K in cell using Java and ASPSOE Cell 8.2.5

Aspose.Cells Product Family
1

Hi Aspose Team,

Once again need help from your side :)

I am using Java 1.6 and Aspose cell 8.2.5.jar. Whiel my cell have data more that 32k, its throwing exception

com.aspose.cells.CellsException: You want to put a string longer than 32K to Cell B5. MS Excel only allows to put a string shorter than 32K to a Cell

So I got one solution on your forum as like need to set CheckExcelRestriction to false.

LoadOptions options = new LoadOptions();

options.setCheckExcelRestriction(false);

Workbook excel = new Workbook("D:\\ASPOSE\\A.xlsx", options);

Cells cells = excel.getWorksheets().get(0).getCells();

//Put a string into a cell

Cell cell = cells.get(0, 0);

StringBuilder sb = new StringBuilder(33000);

for (int i = 0; i < 33000; i++)

sb.append("x");

String longVal = sb.toString();

cell.putValue(longVal);

// cell.putValue("Hello");

System.out.println(cell.getStringValue());

excel.save("D:\\ASPOSE\\A.xlsx");

If I run above code , I am not getting any exception but if I tried to open excel sheet getting popup like "Excel found unreadable content in A.xlsx. Do you want to recover the content of this workbook?" If I click Yes, then cell having that data but data length is 32768 not 33000.

Kindly find the attached A.xlsx file.

Please guide to get proper data without any excel opening issue.

Thanks in advance
2

My output file some time xlsx, xls, csv, pdf format. Not specific to only one format. I just want to set value more than 32K.

Please guide me.

3

Hi,


Thanks for providing us template file and sample code.

After an initial test, I observed the issue as you mentioned by using your sample code with a simple template file. I found when I opened the output file into MS Excel, it prompts the “Excel found unreadable content…” error.
e.g
Sample code:

LoadOptions options = new LoadOptions();

options.setCheckExcelRestriction(false);

Workbook excel = new Workbook(“Book1.xlsx”, options);

Cells cells = excel.getWorksheets().get(0).getCells();

//Put a string into a cell

Cell cell = cells.get(0, 0);

StringBuilder sb = new StringBuilder(33000);

for (int i = 0; i < 33000; i++)

sb.append(“x”);

String longVal = sb.toString();

cell.putValue(longVal);

// cell.putValue(“Hello”);

System.out.println(cell.getStringValue());

excel.getSettings().setCheckExcelRestriction(false);

excel.save(“A.xlsx”);


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

Once we have any update on it, we will let you know here.

Thank you.
4

Hi Amjad,

Thanks for your reply.

I got same issue in .NET: We have provided a new option:

Workbook.Settings.CheckExcelRestriction

When you set this property as false, we will not give exception for long string value that is longer than 32K and you can save it completely to CSV file later. However, if you save the workbook as Excel files, such as XLS, XLSX with such kind of invalid cell value, unexpected problem may occur for the created Excel file.

But in my case, I am opening existing file and populating data and saving it as XLSX, XLS, PDF etc. format.

So please make sure that fix will work for diff file types.

Thanks once again,

5

Hi,


I suspect it is not an issue with the product but expected behavior. I used my above code segment after adding a line of code at the end to save as the file to CSV file format.
e.g
Sample code:

…
excel.save(“out2.csv”, SaveFormat.CSV);

when I open the CSV file, it opens fine into MS Excel without any error.

Well, as MS Excel does not allow a string more than 32k to be inserted into a cell for its native XLS/ XLSX, you may confirm this in Ms Excel by manually trying to accomplish the task, so if we do input a string more than 32K into a cell, MS Excel will invalidate it due to the corrupted or invalid data into a cell, you cannot save such a file in MS Excel.

Anyways, our concerned developer will further evaluate your issue as we have already logged a ticket with an id “CELLSJAVA-40986” into our database.

Once we have any update on it, we will let you know here.

Thank you.

6

Hi Amjad,

I just open MS excel 20101 and cell 0,0 have data more than 32767. It's not corrupted.

Thanks,

7

Hi,


Thanks for the sample file and details.

You are right. There seems to me an issue with the product as when I used my code segment to open your template file and re-saved it (after making the Excel restriction (for the 32K string) to false), I still got the error when I opened the output file into MS Excel. We will look into it soon.

Thank you.
8

Hi Amjad,

I am trying to say, if I manually open one excel file and try to save string which have length more than 32676, then it splitting data into two part, A1 cell have data from 0 to 32675 and B1 cell have data 32676 to end of string. I can save this file as book1 and sent you in last post as book1.xlsx. It was not corrupted. If we re-open it, it get opened without any warning.

Now if I do it programatically by loading workbook as setting that CheckExcelRestriction flag to false and then assign string which have length more than 32676 to cell A1 then its taking only string upto 32676 and remaining string get trim from right. But if I tried to open this excel, it is corrupted.

Here with ASPOSE, excel get corrupted is only issue. That string get trim is ok for me.

So is it expected behavior or ASPOSE team will fix this issue.

Please let me know otherwise I need to trim my string before put into as cell value.

Thanks,

Gauri

9

Hi,


Well, as I said, using our latest version/fix v8.2.0.3, there seems to me an issue with the product as when I used my code segment (above) with your template file, I still got the error when I opened the output file into MS Excel (2007/ 2010). We need to evaluate your issue thoroughly and will figure it out soon.

Once we have any update on it, we will let you know here.

Thank you.
10

Hey Amjad, Thanks

11
Hi,

Thank you for your patience with us.

Please try the latest version of Aspose.Cells for Java 8.2.1.2 as we have fixed the issue logged earlier as CELLSJAVA-40986 with this release. Please feel free to write back in case you face any difficulty.
12

The issues you have found earlier (filed as CELLSJAVA-40986) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
13

Hi Team,


We are using Aspose : 8.7.0.4, but still facing the same issue.

Can you please let us know what could be the issue?
14

Hi Chandra,


Thank you for contacting Aspose support.

I have evaluated the presented scenario while using the following piece of code against the latest version of Aspose.Cells for Java 16.11.4. I am afraid, I am not able to observe any problem with the resultant spreadsheet. I believe the said issue has already been resolved therefore you should first give the latest version a try on your end. In case the problem persists, please provide us an executable sample application along with the input spreadsheet (if any). Moreover, we strongly suggest you to create a new thread with your samples so that all notifications regarding that thread are sent to you.

Java

LoadOptions options = new LoadOptions();
options.setCheckExcelRestriction(false);
Workbook excel = new Workbook(dir + “book1.xlsx”, options);
Cells cells = excel.getWorksheets().get(0).getCells();
//Put a string into a cell
Cell cell = cells.get(0, 0);
StringBuilder sb = new StringBuilder(33000);
for (int i = 0; i < 33000; i++)
sb.append(“x”);
String longVal = sb.toString();
cell.putValue(longVal);
System.out.println(cell.getStringValue());
excel.save(dir + “output.xlsx”);