private static void UpdateSeries(ChartPart chartPart, ChartData chartData)
{
UpdateEmbeddedWorkbook(chartPart, chartData);
XDocument cpXDoc = chartPart.GetXDocument();
XElement root = cpXDoc.Root;
var firstSeries = root.Descendants(C.ser).FirstOrDefault();
var numRef = firstSeries.Elements(C.val).Elements(C.numRef).FirstOrDefault();
string sheetName = null;
var f = (string)firstSeries.Descendants(C.f).FirstOrDefault();
if (f != null)
{
sheetName = f.Split('!')[0];
}
// remove all but first series
XName chartType = firstSeries.Parent.Name;
firstSeries.Parent.Elements(C.ser).Skip(1).Remove();
var newSetOfSeries = chartData.SeriesNames
.Select((string sn, int si) =>
{
XElement cat = null;
var oldCat = firstSeries.Elements(C.cat).FirstOrDefault();
if (oldCat == null)
{
throw new OpenXmlPowerToolsException("Invalid chart markup");
}
var catHasFormula = oldCat.Descendants(C.f).Any();
if (catHasFormula)
{
XElement newFormula = null;
if (sheetName != null)
{
newFormula = new XElement(C.f, string.Format("{0}!$A$2:$A${1}", sheetName, chartData.CategoryNames.Length + 1));
}
if (chartData.CategoryDataType == ChartDataType.String)
{
cat = new XElement(C.cat,
new XElement(C.strRef,
newFormula,
new XElement(C.strCache,
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.CategoryNames[ci]));
return(newPt);
}))));
}
else
{
cat = new XElement(C.cat,
new XElement(C.numRef,
newFormula,
new XElement(C.numCache,
new XElement(C.formatCode, FormatCodes[chartData.CategoryFormatCode]),
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.CategoryNames[ci]));
return(newPt);
}))));
}
}
else
{
if (chartData.CategoryDataType == ChartDataType.String)
{
cat = new XElement(C.cat,
new XElement(C.strLit,
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.CategoryNames[ci]));
return(newPt);
})));
}
else
{
cat = new XElement(C.cat,
new XElement(C.numLit,
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.CategoryNames[ci]));
return(newPt);
})));
}
}
XElement newCval = null;
if (sheetName == null)
{
newCval = new XElement(C.val,
new XElement(C.numLit,
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.Values[si][ci]));
return(newPt);
})));
}
else
{
newCval = new XElement(C.val,
new XElement(C.numRef,
sheetName != null ?
new XElement(C.f, string.Format("{0}!${2}$2:${2}${1}", sheetName, chartData.CategoryNames.Length + 1, SpreadsheetMLUtil.IntToColumnId(si + 1))) : null,
new XElement(C.numCache,
sheetName != null ? numRef.Descendants(C.formatCode) : null,
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.Values[si][ci]));
return(newPt);
}))));
}
var serHasFormula = firstSeries.Descendants(C.f).Any();
XElement tx = null;
if (serHasFormula)
{
XElement newFormula = null;
if (sheetName != null)
{
newFormula = new XElement(C.f, string.Format("{0}!${1}$1", sheetName, SpreadsheetMLUtil.IntToColumnId(si + 1)));
}
tx = new XElement(C.tx,
new XElement(C.strRef,
newFormula,
new XElement(C.strCache,
new XElement(C.ptCount, new XAttribute("val", 1)),
new XElement(C.pt,
new XAttribute("idx", 0),
new XElement(C.v, chartData.SeriesNames[si])))));
}
else
{
tx = new XElement(C.tx,
new XElement(C.v, chartData.SeriesNames[si]));
}
XElement newSer = null;
if (chartType == C.area3DChart || chartType == C.areaChart)
{
newSer = new XElement(C.ser,
// common
new XElement(C.idx, new XAttribute("val", si)),
new XElement(C.order, new XAttribute("val", si)),
tx,
firstSeries.Elements(C.spPr),
// CT_AreaSer
firstSeries.Elements(C.pictureOptions),
firstSeries.Elements(C.dPt),
firstSeries.Elements(C.dLbls),
firstSeries.Elements(C.trendline),
firstSeries.Elements(C.errBars),
cat,
newCval,
firstSeries.Elements(C.extLst));
}
else if (chartType == C.bar3DChart || chartType == C.barChart)
{
newSer = new XElement(C.ser,
// common
new XElement(C.idx, new XAttribute("val", si)),
new XElement(C.order, new XAttribute("val", si)),
tx,
firstSeries.Elements(C.spPr),
// CT_BarSer
firstSeries.Elements(C.invertIfNegative),
firstSeries.Elements(C.pictureOptions),
firstSeries.Elements(C.dPt),
firstSeries.Elements(C.dLbls),
firstSeries.Elements(C.trendline),
firstSeries.Elements(C.errBars),
cat,
newCval,
firstSeries.Elements(C.shape),
firstSeries.Elements(C.extLst));
}
else if (chartType == C.line3DChart || chartType == C.lineChart || chartType == C.stockChart)
{
newSer = new XElement(C.ser,
// common
new XElement(C.idx, new XAttribute("val", si)),
new XElement(C.order, new XAttribute("val", si)),
tx,
firstSeries.Elements(C.spPr),
// CT_LineSer
firstSeries.Elements(C.marker),
firstSeries.Elements(C.dPt),
firstSeries.Elements(C.dLbls),
firstSeries.Elements(C.trendline),
firstSeries.Elements(C.errBars),
cat,
newCval,
firstSeries.Elements(C.smooth),
firstSeries.Elements(C.extLst));
}
else if (chartType == C.doughnutChart || chartType == C.ofPieChart || chartType == C.pie3DChart || chartType == C.pieChart)
{
newSer = new XElement(C.ser,
// common
new XElement(C.idx, new XAttribute("val", si)),
new XElement(C.order, new XAttribute("val", si)),
tx,
firstSeries.Elements(C.spPr),
// CT_PieSer
firstSeries.Elements(C.explosion),
firstSeries.Elements(C.dPt),
firstSeries.Elements(C.dLbls),
cat,
newCval,
firstSeries.Elements(C.extLst));
}
else if (chartType == C.surface3DChart || chartType == C.surfaceChart)
{
newSer = new XElement(C.ser,
// common
new XElement(C.idx, new XAttribute("val", si)),
new XElement(C.order, new XAttribute("val", si)),
tx,
firstSeries.Elements(C.spPr),
// CT_SurfaceSer
cat,
newCval,
firstSeries.Elements(C.extLst));
}
if (newSer == null)
{
throw new OpenXmlPowerToolsException("Unsupported chart type");
}
int accentNumber = (si % 6) + 1;
newSer = (XElement)UpdateAccentTransform(newSer, accentNumber);
return(newSer);
});
firstSeries.ReplaceWith(newSetOfSeries);
chartPart.PutXDocument();
}
/// <summary>
/// Creates a chartsheet part from given data
/// </summary>
/// <param name="chartType">Type of chart to generate</param>
/// <param name="values">Values to represent in the chart</param>
/// <param name="headerReference">Columns to be used as series</param>
/// <param name="categoryReference">Column to be used as category</param>
/// <returns>Chartsheet part with contents related</returns>
public static ChartsheetPart Create(SpreadsheetDocument parentDocument, ChartType chartType, List <string> values, List <string> headerReference, string categoryReference)
{
//Creates base content and associates it to a new chartsheet part
WorkbookPart workbook = parentDocument.WorkbookPart;
ChartsheetPart chartsheetPart = workbook.AddNewPart <ChartsheetPart>();
XDocument chartsheetDocument = CreateEmptyChartsheet();
chartsheetPart.PutXDocument(chartsheetDocument);
//Creates a base drawings part and associates it to the chartsheet part
DrawingsPart drawingsPart = chartsheetPart.AddNewPart <DrawingsPart>();
XDocument drawingDocument = CreateEmptyDrawing();
drawingsPart.PutXDocument(drawingDocument);
//Adds content to chartsheet document to reference drawing document
chartsheetDocument
.Element(ns + "chartsheet")
.Add(
new XElement(ns + "drawing",
new XAttribute(relationshipsns + "id", chartsheetPart.GetIdOfPart(drawingsPart))
)
);
//creates the chart part and associates it to the drawings part
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
XDocument chartDocument = CreateChart(chartType, values, headerReference, categoryReference);
chartPart.PutXDocument(chartDocument);
//Adds content to drawing document to reference chart document
drawingDocument
.Descendants(drawingns + "graphicData")
.First()
.Add(
new XAttribute("uri", chartns),
new XElement(chartns + "chart",
new XAttribute(XNamespace.Xmlns + "c", chartns),
new XAttribute(XNamespace.Xmlns + "r", relationshipsns),
new XAttribute(relationshipsns + "id", drawingsPart.GetIdOfPart(chartPart))
)
);
//Associates the chartsheet part to the workbook part
XDocument document = parentDocument.WorkbookPart.GetXDocument();
int sheetId = document.Root.Element(ns + "sheets").Elements(ns + "sheet").Count() + 1;
int chartsheetCount =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute("name").Value.StartsWith("chart")
)
.Count() + 1;
//Adds content to workbook document to reference chartsheet document
document.Root
.Element(ns + "sheets")
.Add(
new XElement(ns + "sheet",
new XAttribute("name", string.Format("chart{0}", chartsheetCount)),
new XAttribute("sheetId", sheetId),
new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(chartsheetPart))
)
);
chartsheetPart.PutXDocument();
drawingsPart.PutXDocument();
parentDocument.WorkbookPart.PutXDocument();
return(chartsheetPart);
}
