ConditionalFormatting is not working when converting the file from SXSSFWorkbook to Aspose cells workbook JAVA

Aspose.Cells Product Family
1

ConditionalFormatting is not working when converting the file from SXSSFWorkbook to Aspose cells workbook

I have a SXSSFWorkbook , where some ConditionalFormatting is done. If I write the SXSSFWorkbook to response outputstream , the file is getting downloaded with ConditionalFormatting .

However I am converting the SXSSFWorkbook to Aspose.cell workbook for doing some operation. After the operation , when I write the Aspose workbook to response outputstream for download. After file download, the conditional formatting is missing.

What can I do here so that after aspose.cells workbook is downloaded, the conditional formatting remains intact. and how it is removing the conditional formatting in the first place while conversion from SXSSFWorkbook to Aspose.cells workbook.

Please help suggest the solution in JAVA .

2

@rinkusm,
Would you like to provide your sample file and executable Console project? We will check it soon.

3

Thanks for the reply.

The project I am working on is quite big . It will take a long time for me to filter out the code specific to this and make a test project.

However, you can create a SXSSFWorkbook and add conditional formatting there . and then convert it to aspose cells and downloads, you can reproduce the issue.

4

@rinkusm,
By using SXSSFWorkbook to create file and using Aspose.Cells for open and save testing, we can obtain the correct results. Please refer to the attachment (10.8 KB). The attachment includes sample code and test result files.

1 Like
5

ok I am doing similar things . just that it is little more operations and formatting.

Just one query, what version of aspose cells you are using . Because I am using below.

aspose-cells
aspose-cells
22.11

Might it be the reason why formatting is omitted while converting the SXSSFWorkbook to Aspose.cells Workbook.

6

@rinkusm,
We use the latest version Aspose.Cells for Java 23.8 for testing. If you have any questions, please feel free to contact us.

1 Like
7

Ok thanks, can you please try using 22.11 if you can replicate my issue .

If so, then I will have to upgrade to aspose latest version which might required some extra work.

Sorry for asking and for the inconvenience. But could you please try it out from your side with 22.11

8

@rinkusm,
By using Aspose.Cells for Java 22.11 for testing, we can still obtain the correct results. If you can reproduce the issue using the sample code I provided, please share your detailed information (including but not limited to JDK, etc.).

9

ok . I will take a look and get back to you if required with some information.

10

you are doing this.

OutputStream os = new FileOutputStream(“E:\VSCellsForm\out_test.xlsx”);

but for me, I am getting the file from stream itself .
ByteArrayOutputStream out = new ByteArrayOutputStream();
workbook.write(out);

and then calling the below method

public void updateDocument(ByteArrayOutputStream os, OutputStream finalOs, String extentionStr) {
try(ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
byte[] bytes = os.toByteArray();
InputStream inputStream = new ByteArrayInputStream(bytes);
Workbook workbook = new Workbook(inputStream);
//Doing some opeartion
workbook.save(outputStream, getExtention(extentionStr));
outputStream.writeTo(finalOs);
}
catch (Exception e) {
logger.error("Error ", e);
}
}

We are getting byteArrayOutputstream from workbook . Then by using byteArray creating the aspose workbook .

Can you try the same , in that case formatting goes missing.

11

@rinkusm
Please save SXSSFWorkbook to the disk and share it here, we will check it soon.

12

@simon.zhao, @John.He

The issue is its accepting the basic conditional formatting which you have done, but not accepting the formatting (formula) which I use.

I have done a POC with your code with 23.8 version and is attaching the files, code and excel and screenshots of conditional formatting on Excel.

Please have a look and suggest a solution so that Aspose cells workbook will not omit the formula while writing the workbook.attach_results.zip (76.5 KB)

13

@rinkusm
The SXSSFWorkbook does not comply with the specifications of XML, and the greater than sign is not escaped.
<formula>=IF(AND($A1="TEST", $A2="BEST"), B1&lt;>B2, FALSE)</formula>
We will try to parse this non-standard XML
And If you resave the out_test.xlsx with MS Excel, you will find the formula will be changed as
<formula>IF(AND($A1="TEST", $A2="BEST"), B1&lt;&gt;B2, FALSE)</formula>

And you also can use Aspose.Cells to create conditional formattings as :

14

I am getting an SXSSFWorkbook which is having this formula. As per them, this workbook works fine because while writing SXSSFWorkbook to outputstream , the downloaded file is having the changes as per the formula.

So it seems like there is nothing wrong with the formula. But I am converting and saving this to Aspose.cells where this formula is missing.

This excel is contains lot of information. SO I am not able to provide that to you.
However the sample which I have sent to you should help as it is the same logic for conditional formatting.

Please suggest the way around.

15

@rinkusm,
We currently have no alternative solution for formula. The happy thing is that this issue has been solved at present. The fix will be included in our upcoming release (Aspose.Cells v23.9) that we plan to release in the first half of September 2023.You will be notified when the next version is released.

1 Like
16

ok , good to know. and Thanks for your inputs.

Is there any way I can achieve this from my side.

It is pointless for me to add the conditional formatting again from Aspose.cells side as the formatting is coming from upstream SXSSFWorkbook. So that may fix the issue but it doesn’t look like a solution.

17

@rinkusm,
There should be no alternative solution. You are also calling the API, and internal data cannot be changed. This is caused by the internal data storage of the formula, unlocking and changing the internal data of each file may cause the file to crash.

18

The issues you have found earlier (filed as CELLSJAVA-45584) have been fixed in Aspose.Cells for Java 23.9.