static void Main(string[] args)
{
var n = DateTime.Now;
var tempDi = new DirectoryInfo(string.Format("ExampleOutput-{0:00}-{1:00}-{2:00}-{3:00}{4:00}{5:00}", n.Year - 2000, n.Month, n.Day, n.Hour, n.Minute, n.Second));
tempDi.Create();
var sourceDi = new DirectoryInfo("../../");
foreach (var file in sourceDi.GetFiles("*.docx"))
{
File.Copy(file.FullName, Path.Combine(tempDi.FullName, file.Name));
}
foreach (var file in sourceDi.GetFiles("*.pptx"))
{
File.Copy(file.FullName, Path.Combine(tempDi.FullName, file.Name));
}
var fileList = Directory.GetFiles(tempDi.FullName, "*.docx");
foreach (var file in fileList)
{
var fi = new FileInfo(file);
Console.WriteLine(fi.Name);
var newFileName = "Updated-" + fi.Name;
var fi2 = new FileInfo(Path.Combine(tempDi.FullName, newFileName));
File.Copy(fi.FullName, fi2.FullName);
using (var wDoc = WordprocessingDocument.Open(fi2.FullName, true))
{
var chart1Data = new ChartData
{
SeriesNames = new[] {
"Car",
"Truck",
"Van",
"Bike",
"Boat",
},
CategoryDataType = ChartDataType.String,
CategoryNames = new[] {
"Q1",
"Q2",
"Q3",
"Q4",
},
Values = new double[][] {
new double[] {
100, 310, 220, 450,
},
new double[] {
200, 300, 350, 411,
},
new double[] {
80, 120, 140, 600,
},
new double[] {
120, 100, 140, 400,
},
new double[] {
200, 210, 210, 480,
},
},
};
ChartUpdater.UpdateChart(wDoc, "Chart1", chart1Data);
var chart2Data = new ChartData
{
SeriesNames = new[] {
"Series"
},
CategoryDataType = ChartDataType.String,
CategoryNames = new[] {
"Cars",
"Trucks",
"Vans",
"Boats",
},
Values = new double[][] {
new double[] {
320, 112, 64, 80,
},
},
};
ChartUpdater.UpdateChart(wDoc, "Chart2", chart2Data);
var chart3Data = new ChartData
{
SeriesNames = new[] {
"X1",
"X2",
"X3",
"X4",
"X5",
"X6",
},
CategoryDataType = ChartDataType.String,
CategoryNames = new[] {
"Y1",
"Y2",
"Y3",
"Y4",
"Y5",
"Y6",
},
Values = new double[][] {
new double[] { 3.0, 2.1, .7, .7, 2.1, 3.0, },
new double[] { 3.0, 2.1, .8, .8, 2.1, 3.0, },
new double[] { 3.0, 2.4, 1.2, 1.2, 2.4, 3.0, },
new double[] { 3.0, 2.7, 1.7, 1.7, 2.7, 3.0, },
new double[] { 3.0, 2.9, 2.5, 2.5, 2.9, 3.0, },
new double[] { 3.0, 3.0, 3.0, 3.0, 3.0, 3.0, },
},
};
ChartUpdater.UpdateChart(wDoc, "Chart3", chart3Data);
var chart4Data = new ChartData
{
SeriesNames = new[] {
"Car",
"Truck",
"Van",
},
CategoryDataType = ChartDataType.DateTime,
CategoryFormatCode = 14,
CategoryNames = new[] {
ToExcelInteger(new DateTime(2013, 9, 1)),
ToExcelInteger(new DateTime(2013, 9, 2)),
ToExcelInteger(new DateTime(2013, 9, 3)),
ToExcelInteger(new DateTime(2013, 9, 4)),
ToExcelInteger(new DateTime(2013, 9, 5)),
ToExcelInteger(new DateTime(2013, 9, 6)),
ToExcelInteger(new DateTime(2013, 9, 7)),
ToExcelInteger(new DateTime(2013, 9, 8)),
ToExcelInteger(new DateTime(2013, 9, 9)),
ToExcelInteger(new DateTime(2013, 9, 10)),
ToExcelInteger(new DateTime(2013, 9, 11)),
ToExcelInteger(new DateTime(2013, 9, 12)),
ToExcelInteger(new DateTime(2013, 9, 13)),
ToExcelInteger(new DateTime(2013, 9, 14)),
ToExcelInteger(new DateTime(2013, 9, 15)),
ToExcelInteger(new DateTime(2013, 9, 16)),
ToExcelInteger(new DateTime(2013, 9, 17)),
ToExcelInteger(new DateTime(2013, 9, 18)),
ToExcelInteger(new DateTime(2013, 9, 19)),
ToExcelInteger(new DateTime(2013, 9, 20)),
},
Values = new double[][] {
new double[] {
1, 2, 3, 2, 3, 4, 5, 4, 5, 6, 5, 4, 5, 6, 7, 8, 7, 8, 8, 9,
},
new double[] {
2, 3, 3, 4, 4, 5, 6, 7, 8, 7, 8, 9, 9, 9, 7, 8, 9, 9, 10, 11,
},
new double[] {
2, 3, 3, 3, 3, 2, 2, 2, 3, 2, 3, 3, 4, 4, 4, 3, 4, 5, 5, 4,
},
},
};
ChartUpdater.UpdateChart(wDoc, "Chart4", chart4Data);
}
}
fileList = Directory.GetFiles(tempDi.FullName, "*.pptx");
foreach (var file in fileList)
{
var fi = new FileInfo(file);
Console.WriteLine(fi.Name);
var newFileName = "Updated-" + fi.Name;
var fi2 = new FileInfo(Path.Combine(tempDi.FullName, newFileName));
File.Copy(fi.FullName, fi2.FullName);
using (var pDoc = PresentationDocument.Open(fi2.FullName, true))
{
var chart1Data = new ChartData
{
SeriesNames = new[] {
"Car",
"Truck",
"Van",
},
CategoryDataType = ChartDataType.String,
CategoryNames = new[] {
"Q1",
"Q2",
"Q3",
"Q4",
},
Values = new double[][] {
new double[] {
320, 310, 320, 330,
},
new double[] {
201, 224, 230, 221,
},
new double[] {
180, 200, 220, 230,
},
},
};
ChartUpdater.UpdateChart(pDoc, 1, chart1Data);
}
}
}
private static void UpdateEmbeddedWorkbook(ChartPart chartPart, ChartData chartData)
{
XDocument cpXDoc = chartPart.GetXDocument();
XElement root = cpXDoc.Root;
var firstSeries = root.Descendants(C.ser).FirstOrDefault();
if (firstSeries == null)
{
return;
}
var firstFormula = (string)firstSeries.Descendants(C.f).FirstOrDefault();
if (firstFormula == null)
{
return;
}
var sheet = firstFormula.Split('!')[0];
var embeddedSpreadsheetRid = (string)root.Descendants(C.externalData).Attributes(R.id).FirstOrDefault();
if (embeddedSpreadsheetRid == null)
{
return;
}
var embeddedSpreadsheet = chartPart.GetPartById(embeddedSpreadsheetRid);
if (embeddedSpreadsheet != null)
{
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(embeddedSpreadsheet.GetStream(), true))
{
var workbookPart = sDoc.WorkbookPart;
var wbRoot = workbookPart.GetXDocument().Root;
var sheetRid = (string)wbRoot
.Elements(S.sheets)
.Elements(S.sheet)
.Where(s => (string)s.Attribute("name") == sheet)
.Attributes(R.id)
.FirstOrDefault();
if (sheetRid != null)
{
var sheetPart = workbookPart.GetPartById(sheetRid);
var xdSheet = sheetPart.GetXDocument();
var sheetData = xdSheet.Descendants(S.sheetData).FirstOrDefault();
var stylePart = workbookPart.WorkbookStylesPart;
var xdStyle = stylePart.GetXDocument();
int categoryStyleId = 0;
if (chartData.CategoryFormatCode != 0)
{
categoryStyleId = AddDxfToDxfs(xdSheet, xdStyle, chartData.CategoryFormatCode);
}
stylePart.PutXDocument();
var firstRow = new XElement(S.row,
new XAttribute("r", "1"),
new XAttribute("spans", string.Format("1:{0}", chartData.SeriesNames.Length + 1)),
new [] { new XElement(S.c,
new XAttribute("r", "A1"),
new XAttribute("t", "str"),
new XElement(S.v,
new XAttribute(XNamespace.Xml + "space", "preserve"),
" ")) }
.Concat(
chartData.SeriesNames
.Select((sn, i) => new XElement(S.c,
new XAttribute("r", RowColToString(0, i + 1)),
new XAttribute("t", "str"),
new XElement(S.v, sn)))));
var otherRows = chartData
.CategoryNames
.Select((cn, r) =>
{
var row = new XElement(S.row,
new XAttribute("r", r + 2),
new XAttribute("spans", string.Format("1:{0}", chartData.SeriesNames.Length + 1)),
new[] {
new XElement(S.c,
new XAttribute("r", RowColToString(r + 1, 0)),
categoryStyleId != 0 ? new XAttribute("s", categoryStyleId) : null,
chartData.CategoryDataType == ChartDataType.String ? new XAttribute("t", "str") : null,
new XElement(S.v, cn))
}.Concat(
Enumerable.Range(0, chartData.Values.Length)
.Select((c, ci) =>
{
var cell = new XElement(S.c,
new XAttribute("r", RowColToString(r + 1, ci + 1)),
new XElement(S.v, chartData.Values[ci][r]));
return(cell);
})));
return(row);
});
var allRows = new[] {
firstRow
}.Concat(otherRows);
var newSheetData = new XElement(S.sheetData,
allRows);
sheetData.ReplaceWith(newSheetData);
sheetPart.PutXDocument();
var tablePartRid = (string)xdSheet
.Root
.Elements(S.tableParts)
.Elements(S.tablePart)
.Attributes(R.id)
.FirstOrDefault();
if (tablePartRid != null)
{
var partTable = sheetPart.GetPartById(tablePartRid);
var xdTablePart = partTable.GetXDocument();
var xaRef = xdTablePart.Root.Attribute("ref");
xaRef.Value = string.Format("A1:{0}", RowColToString(chartData.CategoryNames.Length - 1, chartData.SeriesNames.Length));
var xeNewTableColumns = new XElement(S.tableColumns,
new XAttribute("count", chartData.SeriesNames.Count() + 1),
new[] {
new XElement(S.tableColumn,
new XAttribute("id", 1),
new XAttribute("name", " "))
}.Concat(
chartData.SeriesNames.Select((cn, ci) =>
new XElement(S.tableColumn,
new XAttribute("id", ci + 2),
new XAttribute("name", cn)))));
var xeExistingTableColumns = xdTablePart.Root.Element(S.tableColumns);
if (xeExistingTableColumns != null)
{
xeExistingTableColumns.ReplaceWith(xeNewTableColumns);
}
partTable.PutXDocument();
}
}
}
}
}
public static bool UpdateChart(WordprocessingDocument wDoc, string contentControlTag, ChartData chartData)
{
var mainDocumentPart = wDoc.MainDocumentPart;
var mdXDoc = mainDocumentPart.GetXDocument();
var cc = mdXDoc.Descendants(W.sdt)
.FirstOrDefault(sdt => (string)sdt.Elements(W.sdtPr).Elements(W.tag).Attributes(W.val).FirstOrDefault() == contentControlTag);
if (cc != null)
{
var chartRid = (string)cc.Descendants(C.chart).Attributes(R.id).FirstOrDefault();
if (chartRid != null)
{
ChartPart chartPart = (ChartPart)mainDocumentPart.GetPartById(chartRid);
UpdateChart(chartPart, chartData);
var newContent = cc.Elements(W.sdtContent).Elements().Select(e => new XElement(e));
cc.ReplaceWith(newContent);
mainDocumentPart.PutXDocument();
return(true);
}
}
return(false);
}
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();
}
