Combining workbooks with macros using Aspose.Cells for Java

Aspose.Cells Product Family
1

Hi,

I have a master workbook with macro code and when I try to combine other workbooks with this workbook, the excel file gets corrupted.

Is there any way to do it using the API? If so, please provide the solution.

Thanks

DJ

2

Please note that I am using Aspose Cells for Java

Thanks

DJ

3

Hi,

Please try our latest version v2.5.2:
http://www.aspose.com/community/files/72/java-components/aspose.cells-for-java/entry289554.aspx

If you still find the issue, kindly paste your sample code and attach your template file(s) here to show the issue, we will check your issue soon.

Thank you.

4

No, the latest version is not working as well. I will create the sample code and upload the same.

Thanks

DJ

5

Please find the sample code and sample files:

1. master_blank.xls with macro code

2. Sample file to merge (2011030803180104019106.xls)

3. Output file (outputWithAspose.xls)

/*
* File : $Source: $
* Version : $Revision: $
* Date : $Date: $
* Modified by : $Author: $
*/

import java.io.File;

import com.aspose.cells.Workbook;

public class TestAspose
{
static String wd = "C:\\Developer\\TEMP\\frame\\";

@SuppressWarnings("unchecked")
public static void main(String[] args) throws Exception
{
File file = new File("C:\\Developer\\TEMP\\frame\\");
File[] inputFiles = file.listFiles();


Workbook outputWorkbook = new Workbook();
outputWorkbook.open(file + "/master_blank.xls");
for (File inputFile : inputFiles)
{

if (!inputFile.getAbsolutePath().equalsIgnoreCase("C:\\Developer\\TEMP\\frame\\master_blank.xls"))
{
Workbook book = new Workbook();

book.loadData(inputFile.getAbsolutePath());
outputWorkbook.combine(book);
}
}

String fileName = wd + "outputWithAspose.xls";
outputWorkbook.save(fileName);

}

}

6

Hi,

Thanks for providing us sample files with code segment.

For your issue of macros, We don’t think it is caused by our component. When we edit the “master_blank.xls” manually in MS Excel to add one new sheet into it, then re-saved file will show the same macro error when opening it again. So I am afraid there might be some issues of the original macro code itself.

Thanks for your understanding!

7

Hi Ajmad,

I believe this is happening because Aspose is trying to open the excel while trying to copy the complete content. Is there any way to avoid it? What is the workbook.open to not execute while this copy is happening?

Please advise.

Thanks

DJ

8

Hi,

We are not sure about your words: “Is there any way to avoid it?”.

Do you mean that you do not want the macros to be included into the resultant file?

If so, you can use Workbook.loadData(“master_blank.xls”) instead of Workbook.open(“master_blank.xls”). Anyways, we don’t think it is our component’s issue for the macro error of the generated file.

Please see attached file(“outputByExcel.xls”) generated by ms excel itself when copying sheets from “2011030803180104019106.xls” to “master_blank.xls”.

When opening this file by excel, you can get the same error for the macro(see the screeshot “errinfo.jpg”).

9

If I have 5 workbooks and 1 workbook is with macros, can I copy the complete content with macros the last ( as in the case below - masterWorkbook). When I use combine, it does not copy the macros.

Workbook book = new Workbook();

for (HSSFWorkbook w : srcWorkBookList)

{

Workbook srcBook = new Workbook();

srcBook.loadData(new ByteArrayInputStream(saveFile(w)));

book.combine(srcBook);

}

Worksheets sheets = book.getWorksheets();

sheets.removeSheet("Sheet1");

Workbook masterWorkbook = new Workbook();

masterWorkbook.open(new ByteArrayInputStream(saveFile(newWorkBook)));

book.combine(masterWorkbook);

Thanks

DJ

10

Also, is there a way to copy macros alone from one workbook to another?

Thanks

DJ

11

Just wanted to let you know that we have purchased the license. Please help.

Thanks

DJ

12

Hi,

Well, I am afraid, Aspose.Cells for Java does not support to copy macros while combining workbooks. I have logged your feature request into our tracking system with an id: CELLSJAVA-25431. We will support it in future versions.

Thank you.