Slicer is removed in Excel 2010/2013 after save

Aspose.Cells Product Family
1

Hi team,

We have an Excel template with some slicer in it. For example, sheet ‘Total Forecast’. After executing save method, the slicer is removed from the Excel, could you please take a look?
Attached the original template and output template.

Code used as below:
String file_path = “C:\test slicer.xlsx”;
Workbook wb = new Workbook(file_path);
WorksheetCollection sheets = wb.getWorksheets();
wb.save(wb.getFileName()+"_out.xlsx");

Thanks.
2

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue after executing the following sample code with the latest version: Aspose.Cells
for Java v8.4.1.3. The output excel file is either corrupt or when it is opened in Excel 2013, slicer is removed from it.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41319 - Slicer is removed in Excel 2010/2013 after save

Java

String filePath = “D:\Downloads\test+slicer.xlsx”;


Workbook workbook = new Workbook(filePath);

workbook.save(filePath + “.out.xlsx”);

3

Hi,

Thanks for using Aspose.Cells for Java.

Please download and try this fix: Aspose.Cells for Java v8.4.1.4 and let us know your feedback.

4

Hi Team,

range.copy method will throw exception on this new 8.4.1.4 version.

Can you please take a quick look?

Thanks.
5
Hi,

Sissi:
range.copy method will throw exception on this new 8.4.1.4 version.

Can you please take a quick look?


Could you provide us sample code (runnable) with template file(s) to reproduce the issue on our end, we will check it soon.

Thank you.
6

It is not related to template. It has compile error.


We have code as below which can compile and work fine on 8.3.2.6 and previous version, but when we replace aspose lib to 8.4.1.4, it will throw compile error on the copy row.
PasteOptions pst = new PasteOptions();
pst.setPasteType(PasteType.FORMULAS_AND_NUMBER_FORMATS);
pasteTo.copy(copyFrom, pst);
7

Hi,

Thanks for your posting and using Aspose.Cells.

We were unable to observe any compile time or runtime error. We have tested this issue with the following code and it ran fine and generated the output excel file which I have attached for your reference.

Please let us know some runnable sample code to replicate this issue so that we could investigate this issue further.

Please check the red highlighted part of the code which is related to your code.

Java
String filePath = “F:\Shak-Data-RW\Downloads\Aspose+Example.xlsx”;

//Create workbook
Workbook workbook = new Workbook(filePath);

//Access worksheet
Worksheet worksheet = workbook.getWorksheets().get(“Expected Report Output”);

//This is the range we want to replicate
Range templateRng = worksheet.getCells().createRange(“D4:F13”);

//Start cell to copy ranges to
String startCell = “J4”;

for (int i = 0; i < 5; i++)
{
//Calculate end cell
int row, col;
int[] indices = CellsHelper.cellNameToIndex(startCell);
row = indices[0];
col = indices[1];
String endCell = CellsHelper.cellIndexToName(row + 9, col + 2);

//Copy source range to destination range
Range destRange = worksheet.getCells().createRange(startCell + “:” + endCell);

PasteOptions opts = new PasteOptions();
opts.setPasteType(PasteType.FORMULAS_AND_NUMBER_FORMATS);

destRange.copy(templateRng, opts);

//now change start cell
startCell = CellsHelper.cellIndexToName(row, col + 3);
}

//Save the workbook
workbook.save(filePath + “.out1.xlsx”);

8
Not sure how you compile your main above, if only run it from eclipse, it may run successful. But if you compile it, it should have the error.
Please see the compile error we encoutered, the error only happened on 8.4.1.4. If we change to use 8.3.2.6 and without any other code change, recompile the code, it can be successful.
[javac] AsposeUtil.java:830: unreported exception ja
va.lang.Exception; must be caught or declared to be thrown
[javac] pasteTo.copy(copyFrom, pst);
[javac] ^
[javac] Note: Some input files use unchecked or unsafe operations.
[javac] Note: Recompile with -Xlint:unchecked for details.
[javac] 1 error

Our method code as below,
public static void pasteFormat(Range copyFrom, Range pasteTo,boolean isCopyValue,boolean isSetColumnWidth) {
if (copyFrom != null && pasteTo != null) {
StyleFlag sf = new StyleFlag();
sf.setBorders(true);
sf.setFont(true);
sf.setCellShading(true);
sf.setHorizontalAlignment(true);
sf.setVerticalAlignment(true);
sf.setTextDirection(true);
sf.setWrapText(true);
sf.setRotation(true);
sf.setNumberFormat(false);
pasteTo.applyStyle(copyFrom.get(0, 0).getStyle(), sf);
if (isSetColumnWidth) {
pasteTo.setColumnWidth(copyFrom.getColumnWidth());
}
if(isCopyValue){
PasteOptions pst = new PasteOptions();
pst.setPasteType(PasteType.FORMULAS_AND_NUMBER_FORMATS);
pasteTo.copy(copyFrom, pst);
}
}
}

9

BTW, we use Java 1.6.0_33

10

Hi,

Thanks for your feedback and using Aspose.Cells.

It seems, you are able to run your code fine in Eclipse but you are getting problem in command line.

Please provide us your .java file and also let us know the command line to compile the code. We will try it at our end and log it in our database so that it could be fixed at the earliest.

Please also change your code like this and see if it resolves this issue. Changes are highlighted in red color

public static void pasteFormat(Range copyFrom, Range pasteTo,boolean isCopyValue,boolean isSetColumnWidth) throws Exception {
if (copyFrom != null && pasteTo != null) {
StyleFlag sf = new StyleFlag();
sf.setBorders(true);
sf.setFont(true);
sf.setCellShading(true);
sf.setHorizontalAlignment(true);
sf.setVerticalAlignment(true);
sf.setTextDirection(true);
sf.setWrapText(true);
sf.setRotation(true);
sf.setNumberFormat(false);
pasteTo.applyStyle(copyFrom.get(0, 0).getStyle(), sf);
if (isSetColumnWidth) {
pasteTo.setColumnWidth(copyFrom.getColumnWidth());
}
if(isCopyValue){
PasteOptions pst = new PasteOptions();
pst.setPasteType(PasteType.FORMULAS_AND_NUMBER_FORMATS);
pasteTo.copy(copyFrom, pst);
}
}
}

                                </font><br>
11

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

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

Thanks team!

I add try catch block on my code and it compile successful.
The root cause is it added throws Exception on range.copy method in 8.4.1.4.
13

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved by adding the mentioned code change. Let us know if you encounter any other issue, we will be glad to look into it and help you further.