Populate Excel Template using Named Range

Sep 30, 2011 at 11:25 AM

Hello,

First of all , kudos to the wonderful library, great efforts :)

I am working on an Office automation project which requires us to populate an existing Excel Template (XLSM) file using asp.net , generate graphs and Pull these graphs into a powerpoint presentation.

We had earlier thought (and designed) working on this using interop DLL components, however though it gives a convenient development experience, we had to chuck the code since Server permissions for DCOM would (will) be an issue.

We also explored Sharepoint Excel Services, but even that gives the error: 

The workbook cannot be opened because it contains the following features that are not supported by Excel in the browser:

• Data validation
• Sheet protection

Issues with OpenXML (EPPlus):

1. We are trying to use named range to populate fields in excel, but the code fails on certain named range saying "The given key was not present in the dictionary.". When I look at the pck.Workbook.Names (where Pck is my Package), it has just 1857 values for definedNames, whereas the workbook.xml has approx 2012 Nodes.

Please suggest what could be done in this regards.

2. I downloaded the latest Source Code, added project reference to my existing application (trying to debug through the named range), but this throws exception:

PackageRelationship with specified ID does not exist for the source part.    at System.IO.Packaging.PackagePart.GetRelationship(String id)   at OfficeOpenXml.ExcelWorksheet.LoadHyperLinks(XmlTextReader xr) in C:\ROI EPP\ExcelPackage\ExcelWorksheet.cs:line 819   at OfficeOpenXml.ExcelWorksheet.CreateXml() in C:\ROI EPP\ExcelPackage\ExcelWorksheet.cs:line 579   at OfficeOpenXml.ExcelWorksheet..ctor(XmlNamespaceManager ns, ExcelPackage excelPackage, String relID, Uri uriWorksheet, String sheetName, Int32 sheetID, Int32 positionID, eWorkSheetHidden hide) in C:\ROI EPP\ExcelPackage\ExcelWorksheet.cs:line 184   at OfficeOpenXml.ExcelWorksheets..ctor(ExcelPackage xlPackage) in C:\ROI EPP\ExcelPackage\ExcelWorksheets.cs:line 107   at OfficeOpenXml.ExcelWorkbook.get_Worksheets() in C:\ROI EPP\ExcelPackage\ExcelWorkbook.cs:line 246   at OfficeOpenXml.ExcelWorkbook.GetDefinedNames() in C:\ROI EPP\ExcelPackage\ExcelWorkbook.cs:line 227   at OfficeOpenXml.ExcelPackage.get_Workbook() in C:\ROI EPP\ExcelPackage\ExcelPackage.cs:line 493   at TCOHelper.XMLToExcel(String UID, String SelectedSheet, String SelectedModule) in c:\ROI EPP\App_Code\TCOHelper_EPP.cs:line 333

AND

The 'worksheet' start tag on line 2 position 2 does not match the end tag of 'hyperlinks'. Line 2, position 1326.    at System.Xml.XmlTextReaderImpl.Throw(Exception e)   at System.Xml.XmlTextReaderImpl.Throw(String res, String[] args)   at System.Xml.XmlTextReaderImpl.ThrowTagMismatch(NodeData startTag)   at System.Xml.XmlTextReaderImpl.ParseEndElement()   at System.Xml.XmlTextReaderImpl.ParseElementContent()   at System.Xml.XmlTextReaderImpl.Read()   at System.Xml.XmlLoader.LoadNode(Boolean skipOverWhitespace)   at System.Xml.XmlLoader.LoadDocSequence(XmlDocument parentDoc)   at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)   at System.Xml.XmlDocument.Load(XmlReader reader)   at System.Xml.XmlDocument.LoadXml(String xml)   at OfficeOpenXml.ExcelWorksheet.CreateXml() in C:\ROI EPP\ExcelPackage\ExcelWorksheet.cs:line 590   at OfficeOpenXml.ExcelWorksheet..ctor(XmlNamespaceManager ns, ExcelPackage excelPackage, String relID, Uri uriWorksheet, String sheetName, Int32 sheetID, Int32 positionID, eWorkSheetHidden hide) in C:\ROI EPP\ExcelPackage\ExcelWorksheet.cs:line 184   at OfficeOpenXml.ExcelWorksheets..ctor(ExcelPackage xlPackage) in C:\ROI EPP\ExcelPackage\ExcelWorksheets.cs:line 107   at OfficeOpenXml.ExcelWorkbook.get_Worksheets() in C:\ROI EPP\ExcelPackage\ExcelWorkbook.cs:line 246   at OfficeOpenXml.ExcelWorkbook.GetDefinedNames() in C:\ROI EPP\ExcelPackage\ExcelWorkbook.cs:line 227   at OfficeOpenXml.ExcelPackage.get_Workbook() in C:\ROI EPP\ExcelPackage\ExcelPackage.cs:line 493   at TCOHelper.XMLToExcel(String UID, String SelectedSheet, String SelectedModule) in c:\ROI EPP\App_Code\TCOHelper_EPP.cs:line 333

3. I need a way via which I can export My Excel Chart to PowerPoint (can't use Copy Paste on Server programatically).

 

 

Any guidance and help would be greatly appreciated.

Coordinator
Oct 4, 2011 at 9:32 PM

I cant say what the problem is unless I have your template. You mail the file to epplusdocs@gmail.com or create an issue and upload it.