Hi,
There is a problem with merging workbooks if a cell within one of the workbooks contains an external reference in data validation formula (it is E12 in the s.xlsx attached). Please look at the example code below - there are two tests: with/without trying removing external references. As a result of both tests the files generated cannot be opened in Excel (“Excel found unreadable content…”).
string sourseFileName = “s.xlsx”;
string mergeFileName = “t.xlsx”;
string resultFileName1 = “result1.xlsx”;
string resultFileName2 = “result2.xlsx”;
AC.Workbook sourceWorkbook;
AC.Workbook mergeWorkbook;
//--------------
//----TEST 1----
//--------------
using (FileStream fstream = new FileStream(sourseFileName, FileMode.Open))
{
sourceWorkbook = new AC.Workbook(fstream);
}
using (FileStream fstream = new FileStream(mergeFileName, FileMode.Open))
{
mergeWorkbook = new AC.Workbook(fstream);
}
mergeWorkbook.Combine(sourceWorkbook);
mergeWorkbook.Save(resultFileName1, AC.SaveFormat.Xlsx);
//--------------
//----TEST 2----
//--------------
bool hasExternalReferences = sourceWorkbook.HasExernalLinks();
//At this point hasExternalReferences is True
sourceWorkbook.RemoveExternalLinks();
hasExternalReferences = sourceWorkbook.HasExernalLinks();
//At this point hasExternalReferences is still True
//However the code below still reprodues the problem
using (FileStream fstream = new FileStream(mergeFileName, FileMode.Open))
{
mergeWorkbook = new AC.Workbook(fstream);
}
mergeWorkbook.Combine(sourceWorkbook);
mergeWorkbook.Save(resultFileName2, AC.SaveFormat.Xlsx);
Thanks,
Leo Veriga