I am exporting SSRS reports into to individual excel files and I am merging them into one file using EPPlus. Merge happens successfully but I get following error when I try to open the file in Excel. I dropped report data to produce empty report but it still gives me this error. I could merge some other SSRS report files without any issue though. I wonder if there is any option or setting in EPPlus I should turn on/off.
Repaired Records: String properties from /xl/sharedStrings.xml part (Strings)
I am providing both XML files (from /xl/sharedStrings.xml after unzipping xlsx) here with before and after Excel fixed the worksheet. My apology but I am not formatting XML on purpose to retain all tags in original form.
Before Excel Repair (As is from EPPlus merged file):
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?><sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="6" uniqueCount="6"><si><r xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><rPr><b></b><i val="0"></i><strike val="0"></strike><u val="none"></u><sz val="18"></sz><color rgb="FF000000"></color><rFont val="Tahoma"></rFont></rPr><t xml:space="preserve">Data Exceptions - Warnings</t></r><r xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><rPr><b val="0"></b><i val="0"></i><strike val="0"></strike><u val="none"></u><sz val="18"></sz><color rgb="FF000000"></color><rFont val="Verdana"></rFont></rPr><t xml:space="preserve"></t></r><r xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><rPr><b></b><i val="0"></i><strike val="0"></strike><u val="none"></u><sz val="18"></sz><color rgb="FF000000"></color><rFont val="Tahoma"></rFont></rPr><t xml:space="preserve">Summary</t></r></si><si><r xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><rPr><b val="0"></b><i val="0"></i><strike val="0"></strike><u val="none"></u><sz val="10"></sz><color rgb="FF000000"></color><rFont val="Calibri"></rFont></rPr><t xml:space="preserve"> Execution Time: </t></r><r xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><rPr><b></b><i val="0"></i><strike val="0"></strike><u val="none"></u><sz val="10"></sz><color rgb="FF000000"></color><rFont val="Calibri"></rFont></rPr><t xml:space="preserve">11/14/2019 12:03:07 PM</t></r></si><si><t>Loan Number</t></si><si><t>Asset Type</t></si><si><t>Data Exception Name</t></si><si><t># Of Loans</t></si></sst>After Excel Repair (After excel repaired the original file and saved it in Excel):
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="6" uniqueCount="6"><si><t>Data Exceptions - WarningsSummary</t></si><si><r><rPr><sz val="10"/><color rgb="FF000000"/><rFont val="Calibri"/></rPr><t xml:space="preserve"> Execution Time: </t></r><r><rPr><b/><sz val="10"/><color rgb="FF000000"/><rFont val="Calibri"/></rPr><t>11/14/2019 12:03:07 PM</t></r></si><si><t>Loan Number</t></si><si><t>Asset Type</t></si><si><t>Data Exception Name</t></si><si><t># Of Loans</t></si></sst>Here is the code for merging Excel files in case I am missing anything here. By the way, I don't have this issue with same files merged using Office InterOp. But, I wanted to avoid using InterOp which has issues of its own. Any help/tips would be great. Thanks
private string MergeUsingEPPlus(List<String> ExcelFiles, string resultFile)
{
using (ExcelPackage masterPackage = new ExcelPackage(new FileInfo(resultFile)))
{
foreach (var file in ExcelFiles)
{
using (ExcelPackage pckg = new ExcelPackage(new FileInfo(file)))
{
foreach (var sheet in pckg.Workbook.Worksheets)
{
//check name of worksheet, in case that worksheet with same name already exist exception will be thrown by EPPlus
string workSheetName = sheet.Name;
foreach (var masterSheet in masterPackage.Workbook.Worksheets)
{
if (sheet.Name == masterSheet.Name)
{
workSheetName = string.Format("{0}_{1}", workSheetName, DateTime.Now.ToString("yyyyMMddhhssmmm"));
}
}
//add new sheet
masterPackage.Workbook.Worksheets.Add(workSheetName, sheet);
}
}
}
//masterPackage.Save();
masterPackage.SaveAs(new FileInfo(resultFile));
}
}