[Tutorial] How to add new features/methods to EPPlus

Feb 28, 2012 at 7:44 AM

Here is a "simple" tutorial how to add new features/methods to EPPlus:
1. Create a blank .xlsx document with Microsoft Office.
2. Add the feature/functionality you would like to have in EPPlus to the document. In my case I wanted to change colors of pie charts so I've created a simple pie chart and changed it's colors (there I noticed that I actually needed to change Data Point Fill property).
3. Save the document. Open it with an archive manager (I used 7-Zip) and extract all contents. 

4. Now comes the tricky part. You have to locate what has changed in .xlsx's xml (what code have you added). You could use a diff tool of your choice (WinMerge?) and make a comparison of your files vs a blank document. I've just navigated to xl -> charts -> chart1.xml . I've additionally used Notepad++ Tidy Xml (plugin) to make the code more readable.  
Now you have to look for xml code that is somehow similar to naming convention used when you created the feature - I was looking for Data Point elements. I've also set the colors to something other than defaults (red and blue) so this would somehow stand out from the code (in my case yellow and green). 

<c:chart>
...
	<c:plotArea>
...
		<c:pie3DChart>
			<c:varyColors val="1"/>
				<c:ser>
					<c:idx val="0"/>
					<c:order val="0"/>
					<c:dPt>
						<c:idx val="0"/>
						<c:bubble3D val="0"/>
						<c:spPr>
							<a:solidFill>
								<a:srgbClr val="92D050"/>
							</a:solidFill>
						</c:spPr>
					</c:dPt>
					<c:dPt>
						<c:idx val="1"/>
						<c:bubble3D val="0"/>
						<c:spPr>
							<a:solidFill>
								<a:srgbClr val="FFFF00"/>
							</a:solidFill>
						</c:spPr>
					</c:dPt>
					<c:val>
						...
					</c:val>
				</c:ser>
				...
			</c:pie3DChart>
	</c:plotArea>
</c:chart>

 

A few minutes later I noticed tag c:dPt ... well, well what do you know, could this be our DataPoint? the other colors values seem to prove my observation. Just to be 100% sure I've change the values of a:srgbClr tag to some other color, used 7zip to pack everything to one .zip file, change the extension to .xlsx and voila! it worked.

5. Now you need to code adding/changing this xml in our document. Take a look at my code on how I added DataPoint colors

	const string dataPointChildrenPath = "c:spPr/a:solidFill/a:srgbClr/@val";
        const string dataPointFullPath = "c:dPt/c:spPr/a:solidFill/a:srgbClr/@val";
        const string dataPointPath = "c:dPt";
        const string dataPointIdxPath = "c:dPt/c:idx/@val";
        const string dataPointBubble3DPath = "c:dPt/c:bubble3D/@val";
        public void SetDataPointColor(int dataPointNr, Color newColor)
        {
            //the list might be longer as there are more pie type charts, haven't tested it on other than Pie3D
            List supportedChartTypes = new List() {eChartType.Pie3D};
            eChartType currentChartType = _chartSeries.Chart.ChartType;

            if (dataPointNr < 0)
            {
                throw new IndexOutOfRangeException("Data point index can't be less than 0.");
            }

            if (!supportedChartTypes.Contains(currentChartType)) 
            { 
                StringBuilder types = new StringBuilder();
                foreach(eChartType e in supportedChartTypes)
                {
                    types.Append(e.ToString());
                    types.Append(" ");
                }
                throw new Exception("Unsupported chart type. Please use one of these chart types: " + types.ToString());
                
            }

            //supported chart type
            switch (currentChartType)
            {
                case eChartType.Pie3D:
                    {
                        //check if index in less than amount of columns (cells?) for this series
                        //if chart has DataPoints in rows A1:A2 then serie.SetDataPointColor(2, Color.Red) throws an exception!
                        ExcelAddress address = new ExcelAddress(XSeries);
                        if (dataPointNr > (address._toCol - address._fromCol))
                        {
                            throw new Exception("Data Point index is higher than  amount of columns (cells?) for this series. Max possible value is: " + (address._toCol - address._fromCol));
                        }
                        else
                        {
                            //color string
                            string color = newColor.ToArgb().ToString("X").Substring(2, 6);
                            //get all Data Point nodes
                            XmlNodeList nodeList = _node.SelectNodes("c:dPt", _ns);
                            //create node because it doesn't exist
                            if (dataPointNr + 1 > nodeList.Count)
                            {
                               XmlElement dpt = _node.OwnerDocument.CreateElement("c", "dPt", ExcelPackage.schemaChart);
                               //index
                               XmlElement temp = _node.OwnerDocument.CreateElement("c", "idx", ExcelPackage.schemaChart);
                               XmlAttribute tempAttr = _node.OwnerDocument.CreateAttribute("val");
                               //index must be incremental if previous Data Points exist
                               tempAttr.Value = nodeList.Count.ToString();
                               temp.Attributes.Append(tempAttr);
                               dpt.AppendChild(temp);
                               //bubble
                               temp = _node.OwnerDocument.CreateElement("c", "bubble3D", ExcelPackage.schemaChart);
                               tempAttr = _node.OwnerDocument.CreateAttribute("val");
                               tempAttr.Value = "0";
                               temp.Attributes.Append(tempAttr);
                               dpt.AppendChild(temp);
                               temp = _node.OwnerDocument.CreateElement("c", "spPr", ExcelPackage.schemaChart);
                               string nameSpaceURI = NameSpaceManager.LookupNamespace("a");
                               XmlElement temp2 = _node.OwnerDocument.CreateElement("a", "solidFill", nameSpaceURI);
                               XmlElement temp3 = _node.OwnerDocument.CreateElement("a", "srgbClr", nameSpaceURI);
                               tempAttr = _node.OwnerDocument.CreateAttribute("val");
                               tempAttr.Value = color;
                               temp3.Attributes.Append(tempAttr);
                               temp2.AppendChild(temp3);
                               temp.AppendChild(temp2);
                               dpt.AppendChild(temp);

                               if (nodeList.Count > 0)
                               {
                                   //if other Data Points exist
                                   _node.InsertAfter(dpt, nodeList[nodeList.Count - 1]);
                               }
                               else
                               {
                                   //if first Data Point
                                   InserAfter(_node, "c:tx", dpt);
                               }
                            }
                            else 
                            {
                                //node already exists, change it
                                SetXmlNodeString(nodeList[dataPointNr], dataPointChildrenPath, color);
                            }
                         }
                         break;
                    }
                default:
                    throw new Exception("Ooops! Update the supported chart type list or the implementation for this chart type, something is missing!");
            }
        }

6. You also need to change the value of SchemaNodeOrder - Jan Källman helped me here. Jan could you shares with us where do you find the correct schema??

Mar 1, 2012 at 4:19 AM

Just to understand this completely, it seems like you are just editing XML that already exists (produced by EPPlus).

Does this methodology also apply if you are creating an new excel file using epplus?

 

(In my case, I was trying to figure out how to create a dataTable in excel.  When I open the xml file, I notice I need to just add a simple line to one of the cells:

<f t="dataTable" ref="C3:E5" dt2D="1" dtr="1" r1="A1" r2="B1" />

 

However, when I try to load the xml using worksheet.WorksheetXml, I noticed the xml for the other cells hasn't been created yet.  Can I change the xml ahead of time / is there a proper way to edit the xml directly without having first saved the file?)

Mar 1, 2012 at 8:15 AM
Edited Mar 1, 2012 at 8:21 AM
namit101 wrote:

Just to understand this completely, it seems like you are just editing XML that already exists (produced by EPPlus).

Does this methodology also apply if you are creating an new excel file using epplus?

First I get all DataPoint nodes which are in _node (which is a Series)

//get all Data Point nodes
XmlNodeList nodeList = _node.SelectNodes("c:dPt", _ns);

Then I check if DataPoint used in my method exists

if (dataPointNr + 1 > nodeList.Count)
  • if it doesn't I'm creating it - that's the longest part in my code which includes creating xml and adding it to this node with methods: _node.InsertAfter, InserAfter
  • If it does exist, I'm just changing the xml
//node already exists, change it
SetXmlNodeString(nodeList[dataPointNr], dataPointChildrenPath, color);

When You open .xlsx with MS Excel some additional xml is added to the file (for example formulas are calculated). So in other words You need to add the xml to this node manually if it doesn't exist. Does that answer Your question?

Mar 1, 2012 at 9:10 PM

I've just started using EPPlus, so forgive the ignorance, but where do these nodes coming from?

The only interface into the xml that I've seen is using worksheet.WorksheetXml (which if I recall correctly is read-only and has no cell nodes in it until I save down the file).

Mar 2, 2012 at 7:28 AM

namit

Sure, no hard feelings ;) My tutorial is about changing EPPlus itself, you would need to download the source code first, add your features and then for example link reference to newly created EPPlus dll (and use the new feature). WorksheetXml or ChartXml properties are as You said read-only, you could change this in the source as well. Just go to ExcelChart class, public XmlDocument ChartXml { get; internal set; } <- remove internal, but then You would have to modify XmlDocument object. 

Editor
Mar 6, 2012 at 3:39 PM

Its good to see new people learning about epplus =)

Thanks for the tutorial.

Mar 6, 2012 at 5:54 PM

My pleasure! :)

Coordinator
Mar 15, 2012 at 8:38 PM

Nice work.

The schemas and documentation can be found at..

http://www.ecma-international.org/publications/standards/Ecma-376.htm

Thanks

Jan

Mar 28, 2012 at 10:10 AM

Hi !

Thx for your uge work Jan and for your tuto Bart, I finally succed to change all colors.

The problem was to change the color for the legend too when you've more than 1 serie. I had the theme 1 from Excel each time. I inspected several xlsx and i saw that we have a small part of code for the color legend in the chart1.xml file :

 

        <ser xmlns="http://schemas.openxmlformats.org/drawingml/2006/chart">
          <c:idx val="0" />
          <c:order val="0" />
          <c:tx>
            <c:v>Serie Name</c:v>
          </c:tx>
          <c:spPr>
            <a:solidFill>
              <a:srgbClr val="FF0000" />
            </a:solidFill>
          </c:spPr>
          <c:dPt>
[...My Datapoints...]

 

 

So I rewrote a small part of the Bart's function :

 

 

case eChartType.ColumnClustered :
                {
                    ExcelAddress address = new ExcelAddress(XSeries);
                    if (dataPointNr > (address._toCol - address._fromCol))
                    {
                        throw new Exception("Data Point index is higher than  amount of columns (cells?) for this series. Max possible value is: " + (address._toCol - address._fromCol));
                    }
                    else
                    {
                        //color string
                        string color = newColor.ToArgb().ToString("X").Substring(2, 6);
                        //get all Data Point nodes
                        XmlNodeList nodeList = _node.SelectNodes("c:dPt", _ns);
                        //create node because it doesn't exist
                        if (dataPointNr + 1 > nodeList.Count)
                        {
                            XmlElement dpt = _node.OwnerDocument.CreateElement("c", "dPt", ExcelPackage.schemaChart);
                            //index
                            XmlElement temp = _node.OwnerDocument.CreateElement("c", "idx", ExcelPackage.schemaChart);
                            XmlAttribute tempAttr = _node.OwnerDocument.CreateAttribute("val");
                            //index must be incremental if previous Data Points exist
                            tempAttr.Value = nodeList.Count.ToString();
                            temp.Attributes.Append(tempAttr);
                            dpt.AppendChild(temp);
                            //bubble
                            temp = _node.OwnerDocument.CreateElement("c", "bubble3D", ExcelPackage.schemaChart);
                            tempAttr = _node.OwnerDocument.CreateAttribute("val");
                            tempAttr.Value = "0";
                            temp.Attributes.Append(tempAttr);
                            dpt.AppendChild(temp);
                            temp = _node.OwnerDocument.CreateElement("c", "spPr", ExcelPackage.schemaChart);
                            string nameSpaceURI = NameSpaceManager.LookupNamespace("a");
                            XmlElement temp2 = _node.OwnerDocument.CreateElement("a", "solidFill", nameSpaceURI);
                            XmlElement temp3 = _node.OwnerDocument.CreateElement("a", "srgbClr", nameSpaceURI);
                            tempAttr = _node.OwnerDocument.CreateAttribute("val");
                            tempAttr.Value = color;
                            temp3.Attributes.Append(tempAttr);
                            temp2.AppendChild(temp3);
                            temp.AppendChild(temp2);
                            dpt.AppendChild(temp);
                            
                            if (nodeList.Count > 0)
                            {
                                //if other Data Points exist
                                _node.InsertAfter(dpt, nodeList[nodeList.Count - 1]);
                            }
                            else
                            {
                                XmlElement legendColor = _node.OwnerDocument.CreateElement("c", "spPr", ExcelPackage.schemaChart);
                                string nameSpaceURIColor = NameSpaceManager.LookupNamespace("a");
                                XmlElement temp2Color = _node.OwnerDocument.CreateElement("a", "solidFill", nameSpaceURIColor);
                                XmlElement temp3Color = _node.OwnerDocument.CreateElement("a", "srgbClr", nameSpaceURIColor);
                                XmlAttribute tempAttrColor = _node.OwnerDocument.CreateAttribute("val");
                                string legendColorColor = newColor.ToArgb().ToString("X").Substring(2, 6);
                                tempAttrColor.Value = color;
                                temp3Color.Attributes.Append(tempAttrColor);
                                temp2Color.AppendChild(temp3Color);
                                legendColor.AppendChild(temp2Color);
                                InserAfter(_node, "c:tx", legendColor);
                                //if first Data Point
                                InserAfter(_node, "c:spPr", dpt);
                            }
                        }
                        else
                        {
                            //node already exists, change it
                            SetXmlNodeString(nodeList[dataPointNr], dataPointChildrenPath, color);
                        }
                    }
                    break;
                }

 

 

The difference is in the else when we insert the first dtp.

Mar 28, 2012 at 10:16 AM

oops !

you can delete 

string legendColorColor = newColor.ToArgb().ToString("X").Substring(2, 6);