We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Problem while merging two excel files - formula not getting copied which is having cross reference data with another sheet

Hi!,

We recently purchased the license for Aspose .Cell for Jav for one of the business user requiremnt. Requirement was to merge sevral excel files(workbooks) into one with copying all the sheet's of different workbook into one with all style's formulas and macros.

I was trying to merge one with another but was unable to copy formulas which have cross reference data..as i'm attaching the excel which is having one scnario.

In this excel file there is a chart which is using cross reference data in another sheet with some formula. I was trying to merge these two sheets to another workbok, It was successful in copying the vales and chart but was not able to copy the formula.

Can you please help us to do that as this is an critical requirement for which they are waiting for long time.

Also I'm pasting the code for your reference.

List wSheetNames = new ArrayList();

Workbook excelWorkbook2 = new Workbook();

excelWorkbook2.open(bookletFileName);

for(int i=0;i<excelWorkbook2.getWorksheets().getNumberOfSheets();i++)

{

wSheetNames.add(excelWorkbook2.getWorksheets().getSheet(i).getName());

}

for(String pathList : filePathList){

try{

String fileName = pathList.toString();

log.info("Stating Excel FILE : "+fileName);

Workbook excelWorkbook1 = new Workbook();

excelWorkbook1.open(fileName);

int oldsheet = excelWorkbook1.getWorksheets().getNumberOfSheets();

for(int i=0;i<excelWorkbook1.getWorksheets().getNumberOfSheets();i++)

{

String SheekName = "";

if(wSheetNames.contains(excelWorkbook1.getWorksheets().getSheet(i).getName()))

{

Format formatter;

formatter = new SimpleDateFormat("ssSSS");

Date date = new Date();

String s_DtTs = formatter.format(date)+"_";

SheekName = s_DtTs + excelWorkbook1.getWorksheets().getSheet(i).getName();

wSheetNames.add(SheekName);

}else

{

wSheetNames.add(excelWorkbook1.getWorksheets().getSheet(i).getName());

SheekName = excelWorkbook1.getWorksheets().getSheet(i).getName();

}

excelWorkbook2.getWorksheets().addSheet(SheekName);

excelWorkbook2.getWorksheets().getSheet(SheekName).copy(excelWorkbook1.getWorksheets().getSheet(i));

}

log.info("End Excel merge for: "+fileName);

}catch (IOException e) {

log.error("ExcelMergeAspose.merge.ERROR:",e);

}

}

excelWorkbook2.getWorksheets().getSheet(0).setVisible(true);

excelWorkbook2.getWorksheets().getSheet(0).setSelected(true);

excelWorkbook2.save(bookletFileName, FileFormatType.DEFAULT);

log.info("END EXCEL MERGE");

return bookletFileName;

Thanks and regards,

Sarvesh Sharma

Hi,

We did find some issues regarding copy operation for the worksheets. We will investigate and figure them out soon.

By the way the file you attached is the generated file or the source file?

For macros, well they would not be copied into the new workbook when copying sheets.

I have logged your issue into our issue tracking system with an issue id: CELLSJAVA-17998.

Thanks Amjad!

This is the source file you can check the formula's in the chart sheet which we needed to be copied.

It is very critical requiremnt and hope for your early reply.

Thanks,

Sarvesh Sharma

Hi Amjad!,

Can i know if it is critical issue for you which would take some time to resolve.

I'll have to get back to the business users thats why just wanted to know if that can be resolved easily or would take time.

I appriciate your early response.

Thanks and Regards,

Sarvesh

Hi Sarvesh,

In order to combine your desired worksheets into the new workbook, I am afraid you will have to create these worksheets in the new workbook at first and then copy the data. There will be a slight change in your originally provided code. The updated code (modified.txt) is attached. Please pay attention to the lines between //ADDED BY ASPOSE:BEGIN and //ADDED BY ASPOSE:END

Also, for your convenience, in the attached version of Aspose.Cells for Java we have provided a new method:
void com.aspose.cells.Workbook.combine(Workbook source)
This method combines another Workbook object. Currently, we only combine the sheet and data into this workbook.
Parameters: source is the Workbook object to be combined into this one

Hi Sabir / Aspose team,
Thanks for your response it resolved my problem.

I appreciate your support.

Thanks
Sarvesh Sharma


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


This message was posted using Notification2Forum from Downloads module by aspose.notifier.