VBA Macros do not work with EPPlus 3.1 & Pie charts do not work in 3.0.0.2

Mar 20, 2013 at 5:47 PM
Edited Mar 20, 2013 at 7:48 PM
Hello,

I am using a macro enabled (.XLSM) template to generate excel files using EPPlus. If I use the 3.1 version of this DLL, then I run into an issue. Files are generated fine but the macros are disabled. I have a button on the sheet that triggers the macro. But in generated excel files, the button does not trigger the macro at all. If I run the macro on its own, it runs fine. My code implements the template using the "using" block, which means I should ideally not worry about disposing the excelPackage variable.

I tried downgrading the DLL to v3.0.0.2. Problem I mentioned above is solved now. But there is another problem. For one of my excel based reports, I am detecting a pie chart on a sheet and modifying its attributes using EPPlus.

I am using following code to read pie chart -

var chart = (OfficeOpenXml.Drawing.Chart.ExcelChart)ws.Drawings[rpTemplate.ChartName];

if (chart == null)
{
    chart = (OfficeOpenXml.Drawing.Chart.ExcelPieChart)ws.Drawings[0];
}

For EPPlus v3.0.0.2, this code throws an exception in first line -
var chart = (OfficeOpenXml.Drawing.Chart.ExcelChart)ws.Drawings[rpTemplate.ChartName];

The exception is -
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Following is the StackTrace -
    StackTrace  "   at System.Collections.Generic.List`1.get_Item(Int32 index)\r\n   at OfficeOpenXml.Drawing.Chart.ExcelChartSeries.get_Item(Int32 PositionID)\r\n   at OfficeOpenXml.Drawing.Chart.ExcelPieChart.GetChartType(String name)\r\n   at OfficeOpenXml.Drawing.Chart.ExcelChart..ctor(ExcelDrawings drawings, XmlNode node, Uri uriChart, PackagePart part, XmlDocument chartXml, XmlNode chartNode)\r\n   at OfficeOpenXml.Drawing.Chart.ExcelPieChart..ctor(ExcelDrawings drawings, XmlNode node, Uri uriChart, PackagePart part, XmlDocument chartXml, XmlNode chartNode)\r\n   at OfficeOpenXml.Drawing.Chart.ExcelChart.GetChart(XmlElement chartNode, ExcelDrawings drawings, XmlNode node, Uri uriChart, PackagePart part, XmlDocument chartXml, ExcelChart topChart)\r\n   at OfficeOpenXml.Drawing.Chart.ExcelChart.GetChart(ExcelDrawings drawings, XmlNode node)\r\n   at OfficeOpenXml.Drawing.ExcelDrawing.GetDrawing(ExcelDrawings drawings, XmlNode node)\r\n   at OfficeOpenXml.Drawing.ExcelDrawings.AddDrawings()\r\n   at OfficeOpenXml.Drawing.ExcelDrawings..ctor(ExcelPackage xlPackage, ExcelWorksheet sheet)\r\n   at OfficeOpenXml.ExcelWorksheet.get_Drawings()\r\n   at Queue_Manager.AdHocExcelReport.ExportToSheet(ExcelWorkbook workbook, ExcelWorksheet ws, Int32 iSheetNo, Int32 sessionID, TemplateSheet templateSheet, Int32 templateID, SqlConnection sqlConn, Int32 taskID) in C:\\Users\\mule\\Documents\\M&TDev\\Application1\\QM RS\\QM\\Queue Manager\\AdHocExcelReport.cs:line 714\r\n   at Queue_Manager.AdHocExcelReport.MakeAdHocExcelReport(Int32 templateID, Int32 sessionID, String userName, String userEmail, String server, String strSheetName, String strDownloadTemplateName, String database, Int32 taskID) in C:\\Users\\mule\\Documents\\M&TDev\\Application1\\QM RS\\QM\\Queue Manager\\AdHocExcelReport.cs:line 142\r\n   at Queue_Manager.QueueManager.RunQueue(String strServer, String strDatabase, String strSheetName, String strDownloadTemplateName, Boolean blnDebug) in C:\\Users\\mule\\Documents\\M&TDev\\Application1\\QM RS\\QM\\Queue Manager\\QueueManager.cs:line 339"   string