When merging two workbooks under certain conditions using Aspose.Cells version 8.2.2.0, a corrupted workbook is produced.
See attached workbooks, and code below :
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Cells.lic");
Aspose.Cells.Workbook wb1 = new Aspose.Cells.Workbook(@"C:\$Wip\MergeWorkbook1.xlsx");
Aspose.Cells.Workbook wb2 = new Aspose.Cells.Workbook(@"C:\$Wip\MergeWorkbook2.xlsx");
wb1.Combine(wb2);
wb1.Save(@"C:\$Wip\MergeOutError.xlsx", (Aspose.Cells.SaveFormat )wb1.FileFormat);
If you recover the merged workbook, you will see that range names are missing and some formula's have been stripped, replaced with a value of zero (0), or sometimes just blank.
It appears the issue is caused when a worksheet is named like R and a number (e.g. R1, R2P, etc).
I think the issue is because a worksheet named like R# has to have apostrophe's around the sheet name in the xml, otherwise Excel confuses the worksheet name as an R1C1 reference (just a guess).
See picture below.
Notice how in the MergeWorkbook1.xlsx's workbook.xml, the worksheet names are surrounded by apostrophe's, whereas the merged workbook MergeOutError.xlsx, workbook.xml does not.
This workbook's associated sheet xml's that reference R# sheets, also do not have apostrophes around the sheet name in references.
If I manually add the apostrophe's to workbook.xml and any sheet#.xml that refer to R# named worksheets, the workbook opens without any errors.
Also, if I change the names of all worksheets like R#, the merged workbook opens fine:
static void RenameR1Sheets()
{
Aspose.Cells.Workbook wb1 = new Aspose.Cells.Workbook(@"C:\$Wip\MergeWorkbook1.xlsx");
Aspose.Cells.Workbook wb2 = new Aspose.Cells.Workbook(@"C:\$Wip\MergeWorkbook2.xlsx");
string pattern = @"^[R|r]\d+";
System.Text.RegularExpressions.Regex re = new System.Text.RegularExpressions.Regex(pattern);
wb1.Worksheets.Cast().Where(q =>re.IsMatch(q.Name)).ToList().ForEach(q => q.Name = "x" + q.Name);
wb1.Combine(wb2);
wb1.Save(@"C:\$Wip\MergeOutRename.xlsx", (Aspose.Cells.SaveFormat)wb1.FileFormat);
}
Please see "Understanding How Excel Interprets Labels"