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