/// <summary>
/// Modify/Add data into chart XML
/// </summary>
/// <param name="column_index">Corresponds to the column index that needs to be modified in chart spreadsheet (Ex: A, B, C, ...)</param>
/// <param name="row_index">Corresponds to the column index that needs to be modified in excel </param>
/// <param name="new_value">Corresponds to the new value we need to insert to the cell </param>
protected override void ModifyChartXML_Data(string column_index, uint row_index, string new_value)
{
PieChartSeries piechart_Series = chart_part.ChartSpace.Descendants <PieChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).First();
DocumentFormat.OpenXml.Drawing.Charts.Values v = piechart_Series.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault();
NumberReference nr = v.Descendants <NumberReference>().First();
NumberingCache nc = nr.Descendants <NumberingCache>().First();
try
{
NumericPoint np = nc.Descendants <NumericPoint>().ElementAt((int)row_index - 2);
NumericValue nv = np.Descendants <NumericValue>().First();
nv.Text = new_value;
}
catch (Exception)
{
// Create new data and append to previous XML
nc.PointCount.Val = nc.PointCount.Val + 1;
NumericValue nv = new NumericValue(new_value);
NumericPoint np = new NumericPoint(nv);
np.Index = (uint)nc.Descendants <NumericPoint>().ToList().Count;
nc.Append(np);
// Change fomula range
DocumentFormat.OpenXml.Drawing.Charts.Formula f = nr.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Formula>().FirstOrDefault();
f.Text = worksheet_name + "!$" + column_index + "$2:$" + column_index + "$" + GetRowIndexByNum((int)row_index - 2).ToString();
}
}
public IPieChart SetExplosion(double percent)
{
PieChartSeries pieChartSeries = this.doughnutChart.GetFirstChild <PieChartSeries>() ?? this.doughnutChart.AppendChild(new PieChartSeries());
Explosion explosion = pieChartSeries.GetFirstChild <Explosion>() ?? pieChartSeries.AppendChild(new Explosion());
explosion.Val = (ushort)(percent * 100);
return(this);
}
/// <summary>
/// Modify/Add series into chart XML
/// </summary>
/// <param name="column_index">Corresponds to the column index that needs to be modified in chart spreadsheet (Ex: A, B, C, ...)</param>
/// <param name="row_index">Corresponds to the column index that needs to be modified in excel </param>
/// <param name="new_value">Corresponds to the new value we need to insert to the cell </param>
protected override void ModifyChartXML_Series(string column_index, uint row_index, string new_value)
{
PieChartSeries piechart_series = chart_part.ChartSpace.Descendants <PieChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).FirstOrDefault();
if (piechart_series != null) // There exists data on the series --> We only need to modify it
{
SeriesText st = piechart_series.Descendants <SeriesText>().FirstOrDefault();
StringReference sr = st.Descendants <StringReference>().First();
StringCache sc = sr.Descendants <StringCache>().First();
StringPoint sp = sc.Descendants <StringPoint>().First();
NumericValue nv = sp.Descendants <NumericValue>().First();
nv.Text = new_value;
}
else // No such series exists --> Consider create a new series
{
/*
* // Find location in XML to append the PieChartSeries
* Chart chart = chart_part.ChartSpace.Descendants<Chart>().FirstOrDefault();
* PlotArea plot = chart.PlotArea;
*
* // Create new PieChartSeries
* barchart_series = new PieChartSeries();
* uint index = (uint)plot.Descendants<PieChartSeries>().ToList().Count;
*
* barchart_series.Append(new Index() { Val = index });
* barchart_series.Append(new Order() { Val = index });
*
* SeriesText seriesText = new SeriesText();
* seriesText.Append(new NumericValue() { Text = new_value });
*
* barchart_series.Append(seriesText);
*
*
* // Append data
* Bar3DChart bar_3dchart = plot.Descendants<Bar3DChart>().FirstOrDefault();
* if (bar_3dchart != null) // Chart is 3D
* bar_3dchart.Append(barchart_series);
* else // Chart is not 3d
* {
* BarChart barchart = plot.Descendants<BarChart>().FirstOrDefault();
* barchart.Append(barchart_series);
* }
*
* // Append other settings
* PieChartSeries barchart_series_template = chart_part.ChartSpace.Descendants<PieChartSeries>().LastOrDefault();
*
* CategoryAxisData cateAxisData = new CategoryAxisData();
* StringReference string_ref = new StringReference();
* string_ref.Append(new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = barchart_series.Descendants<DocumentFormat.OpenXml.Drawing.Charts.Formula>().FirstOrDefault().Text});
* StringCache string_cache = new StringCache();
* string_cache.Append(new PointCount() { Val = count });
*/
}
}
public void exec()
{
System.IO.File.Copy(template, copie, true);
SpreadsheetDocument myWorkbookCopie = SpreadsheetDocument.Open(copie, true);
SpreadsheetDocument myWorkbookSource = SpreadsheetDocument.Open(source, true);
////Obtention du graph
////WorkbookPart workbookPart = myWorkbookSource.WorkbookPart;
////WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt<WorksheetPart>(2);
//WorksheetPart worksheetPart = XcelWin.GetWorksheetPartByName(myWorkbookSource, "REPORTING");
//DrawingsPart a = worksheetPart.DrawingsPart;
//ChartPart b = a.ChartParts.ElementAt(0);
////Creation drawingpart et chartpart
//WorkbookPart workbookPart2 = myWorkbookCopie.WorkbookPart;
//WorksheetPart worksheetPart2 = workbookPart2.WorksheetParts.ElementAt<WorksheetPart>(0);
//DrawingsPart dp = worksheetPart2.AddNewPart<DrawingsPart>();
//ChartPart x = dp.AddNewPart<ChartPart>();
//x.FeedData(b.GetStream());
WorkbookPart workbookPart2 = myWorkbookCopie.WorkbookPart;
WorksheetPart worksheetPart = XcelWin.getWorksheetPartByName(myWorkbookCopie, "REPORTING");
string[] labels = { "a", "b", "c" };
double[] data = { 2.2, 3.3, 5.5 };
//Permet de mettre un chart avec des données
PieChartSeries pcs = GeneratePieChartSeries(labels, data);
Chart x = getChartByTitle(worksheetPart, "Ressource");
PieChart px = x.Descendants <PieChart>().First();
px.RemoveAllChildren <PieChartSeries>();
px.Append(pcs);
workbookPart2.Workbook.Save();
myWorkbookCopie.Close();
}
/// <summary>
/// Modify/Add categories into chart XML
/// </summary>
/// <param name="column_index">Corresponds to the column index that needs to be modified in chart spreadsheet (Ex: A, B, C, ...)</param>
/// <param name="row_index">Corresponds to the column index that needs to be modified in excel </param>
/// <param name="new_value">Corresponds to the new value we need to insert to the cell </param>
protected override void ModifyChartXML_Categories(string column_index, uint row_index, string new_value)
{
foreach (PieChartSeries piechart_series in chart_part.ChartSpace.Descendants <PieChartSeries>().ToList())
{
CategoryAxisData category_axis_data = piechart_series.Descendants <CategoryAxisData>().FirstOrDefault();
if (category_axis_data == null)
{
// If no StringReference --> Clone one from the 1st (usually we go in this when we create a new PieChartSeries)
PieChartSeries template_barchartseries = chart_part.ChartSpace.Descendants <PieChartSeries>().FirstOrDefault();
CategoryAxisData template_categoryaxisdata = template_barchartseries.Descendants <CategoryAxisData>().FirstOrDefault();
CategoryAxisData new_categoryaxisdata = new CategoryAxisData(template_categoryaxisdata.OuterXml);
piechart_series.Append(new_categoryaxisdata);
}
else
{
StringReference sr = category_axis_data.Descendants <StringReference>().FirstOrDefault();
// If there is a StringReference --> Update its values
StringCache sc = sr.Descendants <StringCache>().First();
try
{
StringPoint sp = sc.Descendants <StringPoint>().ElementAt((int)row_index - 2);
NumericValue nv = sp.Descendants <NumericValue>().First();
nv.Text = new_value;
}
catch (Exception)
{
// Create new data and append to previous XML
sc.PointCount.Val = sc.PointCount.Val + 1;
NumericValue nv = new NumericValue(new_value);
StringPoint sp = new StringPoint(nv);
sp.Index = (uint)sc.Descendants <StringPoint>().ToList().Count;
sc.Append(sp);
// Change fomula range
DocumentFormat.OpenXml.Drawing.Charts.Formula f = sr.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Formula>().FirstOrDefault();
f.Text = worksheet_name + "!$A$2:$A$" + GetRowIndexByNum((int)row_index - 2).ToString();
}
}
}
}
public PieChartSeries GeneratePieChartSeries(string[] labels, double[] data)
{
PieChartSeries pieChartSeries1 = new PieChartSeries();
Index index1 = new Index()
{
Val = (UInt32Value)0U
};
Order order1 = new Order()
{
Val = (UInt32Value)0U
};
SeriesText seriesText1 = new SeriesText();
NumericValue numericValue1 = new NumericValue();
numericValue1.Text = "sreie 1";
seriesText1.Append(numericValue1);
CategoryAxisData categoryAxisData1 = new CategoryAxisData();
StringLiteral stringLiteral1 = new StringLiteral();
PointCount pointCount1 = new PointCount()
{
Val = (uint)labels.Length
};
//StringPoint stringPoint1 = new StringPoint() { Index = (UInt32Value)0U };
//NumericValue numericValue2 = new NumericValue();
//numericValue2.Text = "a";
//stringPoint1.Append(numericValue2);
//StringPoint stringPoint2 = new StringPoint() { Index = (UInt32Value)1U };
//NumericValue numericValue3 = new NumericValue();
//numericValue3.Text = "n";
//stringPoint2.Append(numericValue3);
//StringPoint stringPoint3 = new StringPoint() { Index = (UInt32Value)2U };
//NumericValue numericValue4 = new NumericValue();
//numericValue4.Text = "c";
//stringPoint3.Append(numericValue4);
//StringPoint stringPoint4 = new StringPoint() { Index = (UInt32Value)3U };
//NumericValue numericValue5 = new NumericValue();
//numericValue5.Text = "d";
//stringPoint4.Append(numericValue5);
//Ajout des etiquette de legendes
for (int i = 0; i < labels.Length; i++)
{
StringPoint stringPoint = new StringPoint()
{
Index = (uint)i
};
NumericValue numericValue = new NumericValue();
numericValue.Text = labels[i];
stringPoint.Append(numericValue);
stringLiteral1.Append(stringPoint);
}
stringLiteral1.Append(pointCount1);
//stringLiteral1.Append(stringPoint1);
//stringLiteral1.Append(stringPoint2);
//stringLiteral1.Append(stringPoint3);
//stringLiteral1.Append(stringPoint4);
categoryAxisData1.Append(stringLiteral1);
DocumentFormat.OpenXml.Drawing.Charts.Values values1 = new DocumentFormat.OpenXml.Drawing.Charts.Values();
NumberLiteral numberLiteral1 = new NumberLiteral();
FormatCode formatCode1 = new FormatCode();
formatCode1.Text = "General";
PointCount pointCount2 = new PointCount()
{
Val = (uint)data.Length
};
//NumericPoint numericPoint1 = new NumericPoint() { Index = (UInt32Value)0U };
//NumericValue numericValue6 = new NumericValue();
//numericValue6.Text = "1";
//numericPoint1.Append(numericValue6);
//NumericPoint numericPoint2 = new NumericPoint() { Index = (UInt32Value)1U };
//NumericValue numericValue7 = new NumericValue();
//numericValue7.Text = "2";
//numericPoint2.Append(numericValue7);
//NumericPoint numericPoint3 = new NumericPoint() { Index = (UInt32Value)2U };
//NumericValue numericValue8 = new NumericValue();
//numericValue8.Text = "3";
//numericPoint3.Append(numericValue8);
//NumericPoint numericPoint4 = new NumericPoint() { Index = (UInt32Value)3U };
//NumericValue numericValue9 = new NumericValue();
//numericValue9.Text = "5";
//numericPoint4.Append(numericValue9);
for (int i = 0; i < data.Length; i++)
{
NumericPoint numericPoint = new NumericPoint()
{
Index = (uint)i
};
NumericValue numericValue = new NumericValue();
numericValue.Text = data[i].ToString();
numericPoint.Append(numericValue);
numberLiteral1.Append(numericPoint);
}
numberLiteral1.Append(formatCode1);
numberLiteral1.Append(pointCount2);
//numberLiteral1.Append(numericPoint1);
//numberLiteral1.Append(numericPoint2);
//numberLiteral1.Append(numericPoint3);
//numberLiteral1.Append(numericPoint4);
values1.Append(numberLiteral1);
pieChartSeries1.Append(index1);
pieChartSeries1.Append(order1);
pieChartSeries1.Append(seriesText1);
pieChartSeries1.Append(categoryAxisData1);
pieChartSeries1.Append(values1);
return(pieChartSeries1);
}
