Aspose.Cells for Java - Issues in conversion from one format to other

Hi,
I’m evaluating the product Aspose.Cells for Java.
I’m trying to perform the following file conversions:
1. Excel 97-2003 to Excel 2007(Both XLSX & XLSM format).
2. Excel 2007(Both XLSX & XLSM format) to Excel 97-2003.
3. Excel 2007(XLSX) to Excel 2007(XLSM).
4. Excel 2007(XLSM) to Excel 2007(XLSX).

For conversion 1, 3 & 4, the workbook is saved successfully, but when I open those workbooks, I get the following warning:
“Excel found unreadable content in <workbook_name>.Do you want to recover the contents of this workbook? If you trust the source of this workbook,click Yes.”

On clicking Yes, the following message is shown:
“Removed Records: Named range from /xl/workbook.xml part (Workbook).”

Also, the macro in the workbooks are not saved after conversion.

For conversion 2, I get an ArrayIndexOutOfBoundsException. The stacktrace is:
Exception in thread “main” java.lang.ArrayIndexOutOfBoundsException: 6
at com.aspose.cells.gJ.c(Unknown Source)
at com.aspose.cells.gJ.g(Unknown Source)
at com.aspose.cells.fH.a(Unknown Source)
at com.aspose.cells.fH.a(Unknown Source)
at com.aspose.cells.Shape.a(Unknown Source)
at com.aspose.cells.gc.b(Unknown Source)
at com.aspose.cells.gc.a(Unknown Source)
at com.aspose.cells.dv.b(Unknown Source)
at com.aspose.cells.dv.y(Unknown Source)
at com.aspose.cells.dv.a(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
at ExcelAspose.save(ExcelAspose.java:75)
at ExcelAspose.xlsmToXls(ExcelAspose.java:49)
at ExcelAspose.main(ExcelAspose.java:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)


Attached is the templates that are being used for conversion and the templates obtained after conversion.

The source code is:

public void xlsToXlsm() throws Exception{
save(getXLSWorkbook(), “Template XLS to XLSM.xlsm”, FileFormatType.EXCEL2007XLSM);
}

public void xlsxToXlsm() throws Exception{
save(getXLSXWorkbook(), “Template XLSX to XLSM.xlsm”, FileFormatType.EXCEL2007XLSM);
}

public void xlsToXlsx() throws Exception{
save(getXLSWorkbook(), “Template XLS to XLSX.xlsx”, FileFormatType.EXCEL2007);
}

public void xlsmToXlsx() throws Exception{
save(getXLSMWorkbook(), “Template XLSM to XLSX.xlsx”, FileFormatType.EXCEL2007);
}

public void xlsmToXls() throws Exception{
save(getXLSMWorkbook(), “Template XLSM to XLS.xls”, FileFormatType.EXCEL97TO2003);
}

public void xlsxToXls() throws Exception{
save(getXLSXWorkbook(), “Template XLSX to XLS.xls”, FileFormatType.EXCEL97TO2003);
}

public Workbook getXLSWorkbook() throws Exception{
Workbook xls = new Workbook();
xls.open(inputDirectory + “Mod Template XLS.xls”, FileFormatType.EXCEL97TO2003);
return xls;
}

public Workbook getXLSMWorkbook() throws Exception{
Workbook xlsm = new Workbook();
xlsm.open(inputDirectory + “Mod Template XLSM.xlsm”, FileFormatType.EXCEL2007XLSM);
return xlsm;
}

public Workbook getXLSXWorkbook() throws Exception{
Workbook xlsx = new Workbook();
xlsx.open(inputDirectory + “Mod Template XLSX.xlsx”, FileFormatType.EXCEL2007);
return xlsx;
}

public void save(Workbook workbook, String name, int type) throws Exception{
workbook.save(outputDirectory + name, type);
}


Can you please provide a solution for the above mentioned problems?

Apart from the file conversions, I’ll be performing evaluation to see if following features are supported:

1. Pasting charts as graphics.<o:p></o:p>

2. Evaluating formulas and pasting their results as values. After which the formula should be deleted.

3. Allow setting passwords to open and modify workbooks.

4. Hiding, Deleting and Advanced Hiding a sheet.

5. Deleting macro code from a xls, xlsx or xlsm files.

It would be very helpful if you could provide some reference to whether the above things be achieved.


Thanks,
Kulbhushan Singhal.



Hi,

Please try our latest fix Aspose.Cells for Java v3.2.1.4 (attached), your issues i.e. 1, 3 and 4 are resolved in it as I tested using your template files.

For 2) I can find the issue, I have logged your issue into our issue tracking system with an id: CELLSJAVA-18896. We will fix this issue soon.


For your queries:

1. Pasting charts as graphics.

Supported, creating / manipulating MS Excel charts are supported, the charts would be rendered in the native Excel format, so if you open the Excel file into MS Excel, you may edit the charts directly in it. See the topics in the sections: Creating charts
<o:p></o:p>

2. Evaluating formulas and pasting their results as values. After which the formula should be deleted.

Supported, you may call Workbook.caculateFormula() method, it will calculate the results of the formulas, then you may simply use cell.setValue(cell.getValue()) method as it will replace the formulas with calculated results/values. See the document:

Formula calculation engine

3. Allow setting passwords to open and modify workbooks.

Supported, see the topic: Encrypting files


4. Hiding, Deleting and Advanced Hiding a sheet.

Supported, see the documents:

Hide or unhide a worksheet


5. Deleting macro code from a xls, xlsx or xlsm files.

Not Supported, I have logged your feature request into our issue tracking system with an id: CELLSJAVA-18897. We will support this feature soon.


Thank you.

Hi,
The issue of the files not opening got resolved. But the macros are still not copied in the XLSM files when converted from 2003 file. Could you please look into this.
Also, one more question, is it possible to run macros?

Thanks,
Kulbhushan Singhal.

Hi,

When using v2.3.1.4 the XLSM 2007 version file contains macros. You can find the file with this post.

Please check this link for details on running macros. Working with controls

Hi,
I’m using the V2.3.1.4 jars that you had provided. The macros are copied for 2007 XLSM file to 2007 XLSM file. But, when I convert a 2003 XLS file to 2007 XLSM file, the macros are not there in the 2007 XLSM files.
This is the code that i’m using:

public void xlsToXlsm() throws Exception{
save(getXLSWorkbook(), “Template XLS to XLSM.xlsm”, FileFormatType.EXCEL2007XLSM);
}

public Workbook getXLSWorkbook() throws Exception{
Workbook xls = new Workbook();
xls.open(inputDirectory + “Template XLS.xls”, FileFormatType.EXCEL97TO2003);
return xls;
}

Hi,

I can see the problem with 2003 XLS file to 2007 XLSM file conversion. The problem is logged in our issue tracking system with an ID CELLSJAVA-18917. Once it is resolved, we will let you know.

Hi,
Regarding running macros, the link you provided is for .net and not java. And I could not find any method there that can help me in running macros code.

Regarding protecting workbooks, everything works fine for Excel 2003, but for Excel 2007 after entering the password to open the file, I get the following error:

“The file is corrupt and cannot be opened.”
The error occurs for both XLSM & XLSX files.

The error occurs even if I provide only a password to open the file.

I have to set the following types of protection passwords:
1. Open Workbook.
2. Modify Workbook.
3. Protect Structure.
4. Protect all worksheets.

P.S.: All the above things work for Excel 2003. The templates are the same that I had uploaded previously.

Thanks,
Kulbhushan Singhal.



Regarding hiding/unhiding of sheets, is this the advanced hide:

You can also hide the worksheets in a special way. This feature can hide the worksheet so that the only way for you to make it visible again is by giving “true” as parameter value for the setVeryHidden method in the code (it is to be noted here, the users cannot make the object visible in MS Excel directly by using its menu options). Users can also use isVeryHidden method to check whether a worksheet is marked as VeryHidden or not.

Hi,
I’m facing some issue while writing a chart to image. The background of the chart is turned to black and all axis values are lost and can’t be seen.
This issue occurs for both 2003 & 2007(XLSX & XLSM) files. I’m attaching the templates used.
The source code that I’m using is:

Workbook workbook = getWorkbook();
Worksheets worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.getSheet(“Sample Charts”);
Charts charts = worksheet.getCharts();
int size = charts.size();
for (int i = size - 1; i >= 0; i–) {
Chart chart = charts.getChart(i);
ImageOptions options = new ImageOptions();
options.setImageFormat(ImageFormat.JPEG);
String tempFilePath = ExcelAsposeUtil.outputDirectory + “XLS temp” + i + “.jpg”;
chart.toImage(tempFilePath, options);
}
Could you please look into the issue?

Also, can you please provide the status/solution of issues mentioned in the previous posts.

Thanks,
Kulbhushan.

Hi,

I don’t find the issue using your template files and converting them to jpg images using your code. The images are similar to charts in the template files.

I have attached the output images here. Please make sure that you are using latest version/fix 2.3.1.4, if you still find the issue kindly do generate the images with 2.3.1.4 and post them here. If you don’t have this version/fix, kindly do let us know and we will provide you the version.

For Encryption or protection (workbooks/sheets) issue, we could not find any issue at all. We appreciate if you could provide us complete runnable sample code with template files here, we will check your issue soon.

Thank you.

Hi,
Sorry, I had attached the wrong templates for conversion of charts to image. I have attached the correct templates along with the output image produced.

I’m using the version 2.3.1.4 that you had provided earlier.

For protection issue, the templates are the same. I have also attached the templates that are produced.

The source code that I’m using is:

public void setProtectionXLSX() throws Exception {
Workbook protectedWorkbook = getXLSXWorkbook();

protectedWorkbook.protectWorkbook(ProtectionType.STRUCTURE, “c”);
protectedWorkbook.protectFile(ProtectionType.OPEN_FILE, “a”);
protectedWorkbook.protectFile(ProtectionType.MODIFY_FILE, “b”);
Protection protection = new Protection();
protection.setPassword(“d”);
protection.setSelectingLockedCellsAllowed(true);
protection.setSelectingUnlockedCellsAllowed(true);
protection.setEditingObjectsAllowed(true);
protection.setEditingScenariosAllowed(true);
Worksheets worksheets = protectedWorkbook.getWorksheets();
for (int i = 0; i < worksheets.size(); i++) {
worksheets.getSheet(i).protect(protection);
}
save(protectedWorkbook, “Protection\Protected XLSX.xlsx”, FileFormatType.EXCEL2007);
}

public static Workbook getXLSXWorkbook() throws Exception {
Workbook xlsx = new Workbook();
xlsx.open(inputDirectory + “Chart Template XLSX.xlsx”, FileFormatType.EXCEL2007);
return xlsx;
}

public static void save(Workbook workbook, String name, int type) throws Exception {
workbook.save(outputDirectory + name, type);
}
Please let me know if there is something wrong with this code. But this code works fine for Excel 2003.

Thanks,
Kulbhushan.

Hi,

Please try the attached version we have fixed your 2) issue (issue id: 18896 — mentioned in the first post of the thread).

Thank you.

Hi,
The new version fixed the issue of converting from XLSM to XLS, but like issue CELLSJAVA-18917, the macros are not copied from Excel 2007(XLSM) to Excel 2003. It is very important for us that the macros are also copied during conversion.

Apart from that and other issues mentioned in previous posts, I’m still stuck at the problem of how to run macros. The link you had provided was for .NET and it did not have any method that could help me in running macros. I need to run the macros programmatically. Please let me know if there is some way to do that.

Thanks,
Kulbhushan.

Hi,

<span style=“font-size: 10pt; font-family: “SimSun”,“serif”;”>We
are looking at the feature of keeping macros when converting from one file
format to another. We will give you an eta if we can support it soon. For running
macros, I am afraid, it is not supported at the moment and cannot be supported soon.<o:p></o:p>

Thank you


Hi,

For the issue of chart2image, it is because currently the JDK's Image APIs do not support transparent background for JPG format. I think you can try other image formats such as png which can show the image correctly.

For the issue of saving macros, we will support saving the macros when reading from excel2003 files and saving to excel2007 files in the next week. For reading from excel2007 files and saving it to excel2003 files, it is more complicated and we cannot support it soon or shorter time.

Thank you.

Hi,<?XML:NAMESPACE PREFIX = O />

The issues:

1. Chart2image.
2. Saving macros.

has been fixed. Please find the updated version of the jar file.

Thanks,

Hi,

The issues:

1. Chart2image
2. Saving macros

have been fixed. Please find the updated version of jar file.

Thanks,

Hi,
The chart to image issue got solved after changing the image format and also the issue of saving of macros from 2003 to 2007 XLSM type is solved.

Thanks for the fixes.

-Kulbhushan Singhal.

Hi,
I’m currently using Aspose.CellsV2.3.1.6 and I’m facing the following issues:
1. Very Hidden is not working for Excel 2007(both XLSX & XLSM) format.
2. Prompt for modify password is not being shown for Excel 2007(both XLSX & XLSM) format, even after setting a password for modify file(In previous version of Aspose.Cells, I used to get an error after entering the open password, but now after giving the open password, the workbook opens without asking for modify password). Other protection settings work fine.

The source code that I’m using is:

public void setProtectionXLSX() throws Exception {
Workbook protectedWorkbook = getXLSXWorkbook();

protectedWorkbook.protectWorkbook(ProtectionType.STRUCTURE, “c”);
protectedWorkbook.protectFile(ProtectionType.OPEN_FILE, “a”);
protectedWorkbook.protectFile(ProtectionType.MODIFY_FILE, “b”);
Protection protection = new Protection();
protection.setPassword(“d”);
protection.setSelectingLockedCellsAllowed(true);
protection.setSelectingUnlockedCellsAllowed(true);
protection.setEditingObjectsAllowed(true);
protection.setEditingScenariosAllowed(true);
Worksheets worksheets = protectedWorkbook.getWorksheets();
for (int i = 0; i < worksheets.size(); i++) {
worksheets.getSheet(i).protect(protection);
}
save(protectedWorkbook, “Protected XLSX.xlsx”, FileFormatType.EXCEL2007);
}

public void advancedHideXLSXSheets() throws Exception {
Workbook workbook = ExcelAsposeUtil.getXLSXWorkbook();
workbook.getWorksheets().getSheet(“DataSheet”).setVeryHidden(true);
ExcelAsposeUtil.save(workbook, “Advanced Hide Sheet XLSX.xlsx”, FileFormatType.EXCEL2007);
}

public static Workbook getXLSXWorkbook() throws Exception {
Workbook xlsx = new Workbook();
xlsx.open(inputDirectory + “Template XLSX.xlsx”, FileFormatType.EXCEL2007);
return xlsx;
}

public static void save(Workbook workbook, String name, int type) throws Exception {
workbook.save(outputDirectory + name, type);
}
Attached are the templates that I’m using.
Please look into this issue.
Also, can you provide some ETA about the previous issues like deleting macro code, copying macros from 2007 to 2003 and running macros.

Thanks,
Kulbhushan Singhal.

Hi,
Two questions:
1. Is it possible to check that whether the macro/VBA code is protected?
2. Is it possible to check whether the file contains a particular macro say “ABC”?

-Kulbhushan Singhal