protected override string ReplaceValuesInChartInSlide(ChartPart chartPart, string categoryTitle, int nSerie)
{
ChartSpace chartSpace = chartPart.ChartSpace;
GraficoBarre myBarre = new GraficoBarre2d();
myBarre.BarChartSpace = chartSpace;
myBarre.getBarre();
if (myBarre.Barre != null)
{
BarChartSeries barChartSeries1 = myBarre.barChartSeries1;
SeriesText seriesText1;
CategoryAxisData categoryAxisData1;
Values values1;
modificaChartData("0,0", "", out seriesText1, out categoryAxisData1, out values1);
barChartSeries1.SeriesText = seriesText1;
barChartSeries1.Append(categoryAxisData1);
barChartSeries1.Append(values1);
return("ok");
}
else
{
return("non trovo il grafico a barre!");
}
}
/// <summary>
/// Updates the colour of a <see cref="BarChartSeries" /> so it matches a <see cref="SolidColorBrush" />
/// </summary>
/// <param name="series">The series.</param>
/// <param name="brush">The brush.</param>
private static void UpdateSeriesColour(this BarChartSeries series, SolidColorBrush brush)
{
var chartShapeProperties = series.Descendants <ChartShapeProperties>().FirstOrDefault();
if (chartShapeProperties == null)
{
chartShapeProperties = new ChartShapeProperties();
// if there's a series text then insert afterwards
// the series title, this is the name of the column header
var seriesText = series.Descendants <SeriesText>().FirstOrDefault();
if (seriesText == null)
{
series.InsertAt <ChartShapeProperties>(chartShapeProperties, 0);
}
else
{
series.InsertAfter <ChartShapeProperties>(chartShapeProperties, seriesText);
}
}
// Determine if the series has a SolidFill
SolidFill seriesSolidFill = chartShapeProperties.Elements <SolidFill>().FirstOrDefault();
if (seriesSolidFill == null)
{
// No fill, so create one so we can set the colour
seriesSolidFill = new SolidFill();
seriesSolidFill.UpdateSolidFill((SolidColorBrush)brush);
chartShapeProperties.InsertAt(seriesSolidFill, 0);
}
else
{
seriesSolidFill.UpdateSolidFill((SolidColorBrush)brush);
}
}
/// <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 virtual void ModifyChartXML_Data(string column_index, uint row_index, string new_value)
{
BarChartSeries barchart_series = chart_part.ChartSpace.Descendants <BarChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).First();
DocumentFormat.OpenXml.Drawing.Charts.Values v = barchart_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();
}
}
private void BindBarChart()
{
zgSales.Visible = false;
bcSales.Visible = true;
if (salesByMonthData == null)
{
salesByMonthData = CommerceReport.GetSalesByYearMonthByModule(moduleGuid);
}
StringBuilder categories = new StringBuilder();
string comma = string.Empty;
List <decimal> revenue = new List <decimal>();
// original data is sorted descending on Y, M resorting here
DataRow[] result = salesByMonthData.Select(string.Empty, "Y ASC, M ASC");
int spaceInterval = 0;
int totalItems = result.Length;
int itemsAdded = 0;
if (totalItems > 12)
{
spaceInterval = 4;
}
int nextItemToShow = 0;
foreach (DataRow row in result)
{
categories.Append(comma);
if (itemsAdded == nextItemToShow)
{
categories.Append(row["Y"].ToString());
categories.Append("-");
categories.Append(row["M"].ToString());
nextItemToShow = itemsAdded + spaceInterval;
}
comma = ",";
revenue.Add(Convert.ToDecimal(row["Sales"]));
itemsAdded += 1;
}
bcSales.ChartTitle = Resource.SalesByMonthChartLabel;
bcSales.CategoriesAxis = categories.ToString();
BarChartSeries series = new BarChartSeries();
//series.Name = Resource.SalesByMonthChartSalesLabel;
series.Data = revenue.ToArray();
bcSales.Series.Add(series);
//bcSales.CategoriesAxis
//bcSales.Series.
}
/// <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 virtual void ModifyChartXML_Series(string column_index, uint row_index, string new_value)
{
BarChartSeries barchart_series = chart_part.ChartSpace.Descendants <BarChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).FirstOrDefault();
if (barchart_series != null) // There exists data on the series --> We only need to modify it
{
SeriesText st = barchart_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 BarChartSeries
* Chart chart = chart_part.ChartSpace.Descendants<Chart>().FirstOrDefault();
* PlotArea plot = chart.PlotArea;
*
* // Create new BarChartSeries
* barchart_series = new BarChartSeries();
* uint index = (uint)plot.Descendants<BarChartSeries>().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
* BarChartSeries barchart_series_template = chart_part.ChartSpace.Descendants<BarChartSeries>().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 });
*/
}
}
/// <summary>
/// Modify the worksheet (embedded into chart) range values
/// </summary>
/// <param name="result">The DataTable instance represent a table</param>
protected void ModifyChartRange(System.Data.DataTable result)
{
ChartSpace chartspace = chart_part.ChartSpace;
for (int i = 1; i < result.Columns.Count; i++)
{
BarChartSeries barchart_series = chart_part.ChartSpace.Descendants <BarChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + GetColumnIndexByNum(i) + "$1", true) > 0).First();
DocumentFormat.OpenXml.Drawing.Charts.Values val = barchart_series.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault();
NumberReference nr = val.Descendants <NumberReference>().First();
DocumentFormat.OpenXml.Drawing.Charts.Formula f = nr.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Formula>().First();
f.Text = worksheet_name + "!$" + GetColumnIndexByNum(i) + "$2:$" + GetColumnIndexByNum(i) + "$" + GetRowIndexByNum(result.Rows.Count - 1);
}
}
private void loadGenderChart()
{
BarChartSeries Male = new BarChartSeries();
Male.BarColor = "#0000FF";
Male.Name = "Male";
BarChartSeries Female = new BarChartSeries();
Female.BarColor = "#FF00FF";
Female.Name = "Female";
string strCountMale = "SELECT COUNT (*) AS 'COUNT' FROM Tenants WHERE Gender = 1";
string strCountFemale = "SELECT COUNT (*) AS 'COUNT' FROM Tenants WHERE Gender = 2";
int MaleCount = int.Parse(DataAccess.ReturnData(strCountMale, conString, "COUNT"));
int FemaleCount = int.Parse(DataAccess.ReturnData(strCountFemale, conString, "COUNT"));
Decimal[] MaleNumbers =
{
Convert.ToDecimal(MaleCount)
};
Decimal[] FemaleNumbers =
{
Convert.ToDecimal(FemaleCount)
};
Male.Data = MaleNumbers;
Female.Data = FemaleNumbers;
BarChartSeries[] GenderCountCollection =
{
Male, Female
};
barchartGender.Series.AddRange(GenderCountCollection);
barchartGender.DataBind();
lblTotalTenants.Text = (FemaleCount + MaleCount).ToString();
}
private void BindCorpClassesChart()
{
BarChartSeries yearSeries;
DEWSReference.DeptEstadoDataSoapClient DEWS = new DeptEstadoDataSoapClient();
foreach (DataRow dRow in DEWS.GetCorpClassesByYearSeries().Rows)
{
yearSeries = new BarChartSeries();
yearSeries.Name = dRow["CorpClass"].ToString();
yearSeries.Data = new decimal[]
{
Convert.ToDecimal(dRow["CorpTotal2008"]), Convert.ToDecimal(dRow["CorpTotal2009"]),
Convert.ToDecimal(dRow["CorpTotal2010"]), Convert.ToDecimal(dRow["CorpTotal2011"]),
Convert.ToDecimal(dRow["CorpTotal2012"])
};
bcCorpClassesByYear.Series.Add(yearSeries);
}
}
//-----------------------------------------------------------------------------------
//Tests sur les charts
public void exec(string formuleVal, string formuleLegende)
{
string label = "Feuil1!D5:J5";
string serie = "Feuil1!C12";
string val = "Feuil1!D12:J12";
//euro mmp mut2m cmav sapem quatrem auxia
System.IO.File.Copy(template, copie, true);
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(copie, true);
ChartPart cc = XcelWin.cloneChart(XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1"), "Tests", 0, 0, 10, 10);
BarChart bc = cc.ChartSpace.Descendants <BarChart>().First();
for (int i = 0; i < 6; i++)
{
BarChartSeries newSerie = (BarChartSeries)bc.Elements <BarChartSeries>().First().CloneNode(true);
string form = val.Replace("12", (6 + i).ToString());
newSerie.SeriesText.StringReference.Formula.Text = serie;
//newSerie.Descendants<CategoryAxisData>().First().Remove();
newSerie.Descendants <CategoryAxisData>().First().NumberReference.Formula.Text = label;
newSerie.Descendants <Charts.Values>().First().NumberReference.Formula.Text = form;
newSerie.Index.Val = (uint)i;
newSerie.Order.Val = (uint)i;
bc.Append(newSerie);
}
bc.Elements <BarChartSeries>().First().Remove();
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.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 (LineChartSeries linechart_series in chart_part.ChartSpace.Descendants <LineChartSeries>().ToList())
{
CategoryAxisData category_axis_data = linechart_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 BarChartSeries)
BarChartSeries template_barchartseries = chart_part.ChartSpace.Descendants <BarChartSeries>().FirstOrDefault();
CategoryAxisData template_categoryaxisdata = template_barchartseries.Descendants <CategoryAxisData>().FirstOrDefault();
CategoryAxisData new_categoryaxisdata = new CategoryAxisData(template_categoryaxisdata.OuterXml);
linechart_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();
}
}
}
}
private void SetWaterfallStructure(BarChartSeries seriesItem, Column dataColumn)
{
var dataPoints = seriesItem.Elements <DataPoint>();
var labels = seriesItem.FirstElement <DataLabels>();
bool isFirstBar = dataColumn.Data[0] != null;
if (!isFirstBar)
{
int valueToHideInd = dataColumn.Data.IndexOf(dataColumn.Data.First(v => v != null));
DataPoint dataPoint = dataPoints.FirstOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value == valueToHideInd);
dataPoint.RemoveAllChildren <ChartShapeProperties>();
ChartShapeProperties chartShapeProperties = new ChartShapeProperties();
chartShapeProperties.Append(new A.NoFill());
dataPoint.Append(chartShapeProperties);
DataLabel label = labels.Elements <DataLabel>().FirstOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value == valueToHideInd);
if (label != null)
{
label.FirstElement <ShowValue>().Val.Value = false;
}
}
labels.FirstElement <ShowValue>().Val.Value = false;
}
void setChartData(ChartPart chartPart1, ChartDataHolder[] ChartDataSlide)
{
ChartSpace chartSpace = chartPart1.ChartSpace;
DocumentFormat.OpenXml.Drawing.Charts.Chart chart1 = chartSpace.GetFirstChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>();
PlotArea plotArea1 = chart1.GetFirstChild <PlotArea>();
BarChart barChart1 = plotArea1.GetFirstChild <BarChart>();
//BarChartSeries barChartSeries1 = barChart1.Elements<BarChartSeries>().ElementAtOrDefault(2);
for (int i = 0; i < barChart1.Elements <BarChartSeries>().Count(); i++)
{
BarChartSeries barChartSeries = barChart1.Elements <BarChartSeries>().ElementAtOrDefault(i);
ChartDataSlide dataModel = ChartDataSlide.ElementAtOrDefault(i).chartData;
SeriesText seriesText = barChartSeries.Elements <SeriesText>().ElementAtOrDefault(0);
if (seriesText != null)
{
var stringReference = seriesText.Descendants <StringReference>().FirstOrDefault();
var stringCache = stringReference.Descendants <StringCache>().FirstOrDefault();
var stringPoint = stringCache.Descendants <StringPoint>().FirstOrDefault();
var barLabel = stringPoint.GetFirstChild <NumericValue>();
barLabel.Text = ChartDataSlide.ElementAtOrDefault(i).seriesText;
}
if (barChartSeries != null)
{
Values values1 = barChartSeries.GetFirstChild <Values>();
NumberReference numberReference1 = values1.GetFirstChild <NumberReference>();
NumberingCache numberingCache1 = numberReference1.GetFirstChild <NumberingCache>();
NumericPoint numericPoint1 = numberingCache1.GetFirstChild <NumericPoint>();
NumericPoint numericPoint2 = numberingCache1.Elements <NumericPoint>().ElementAt(1);
NumericPoint numericPoint3 = numberingCache1.Elements <NumericPoint>().ElementAt(2);
NumericValue numericValue1 = numericPoint1.GetFirstChild <NumericValue>();
//numericValue1.Text = ".50";
if (numericValue1 != null)
{
numericValue1.Text = dataModel.Interaction.ToString();
}
NumericValue numericValue2 = numericPoint2.GetFirstChild <NumericValue>();
//numericValue2.Text = ".10";
if (numericValue2 != null)
{
numericValue2.Text = dataModel.Knowlegde.ToString();
}
NumericValue numericValue3 = numericPoint3.GetFirstChild <NumericValue>();
//numericValue3.Text = ".40";
if (numericValue3 != null)
{
numericValue3.Text = dataModel.Image.ToString();
}
}
}
chartSpace.Save();
}
//Here be dragons
internal override void AddData(Statistics stat)
{
// Add a new drawing to the worksheet.
DrawingsPart drawingsPart = WorksheetPart.AddNewPart <DrawingsPart>();
WorksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{
Id = WorksheetPart.GetIdOfPart(drawingsPart)
});
WorksheetPart.Worksheet.Save();
// Add a new chart and set the chart language to English-US.
var chartPart = CreateChartPart(drawingsPart);
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart());
// Create a new clustered column chart.
PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea());
plotArea.AppendChild <Layout>(new Layout());
BarChart barChart =
plotArea.AppendChild <BarChart>(
new BarChart(
new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
},
new VaryColors()
{
Val = false
}
));
// Iterate through each key in the Dictionary collection and add the key to the chart Series
// and add the corresponding value to the chart Values.
BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index()
{
Val = new UInt32Value((uint)0)
},
new Order()
{
Val = new UInt32Value((uint)0)
},
new SeriesText(new NumericValue()
{
Text = "Timeline"
})));
StringLiteral strLit =
barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData())
.AppendChild <StringLiteral>(new StringLiteral());
strLit.Append(new PointCount()
{
Val = new UInt32Value((uint)stat.Diffs.Count)
});
NumberLiteral numLit = barChartSeries.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Values>(
new DocumentFormat.OpenXml.Drawing.Charts.Values())
.AppendChild <NumberLiteral>(new NumberLiteral());
numLit.Append(new FormatCode("General"));
numLit.Append(new PointCount()
{
Val = new UInt32Value((uint)stat.Diffs.Count)
});
uint i = 0;
foreach (var diff in stat.Diffs)
{
strLit.AppendChild <StringPoint>(new StringPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(diff.TimeStamp.ToString()));
numLit.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(diff.Value.ToString()));
i++;
}
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
AppendCategoryAxis(plotArea, 48650112u, "Time, ms", 48672768U);
AppendValueAxis(plotArea, 48672768u, "Duration, ms", 48650112U);
// Add the chart Legend.
Legend legend =
chart.AppendChild <Legend>(
new Legend(
new LegendPosition()
{
Val = new EnumValue <LegendPositionValues>(LegendPositionValues.Right)
},
new Layout()));
chart.Append(new PlotVisibleOnly()
{
Val = new BooleanValue(true)
});
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
AppendGraphicFrame(drawingsPart, chartPart);
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
}
public IBarChart InitializeFromRange(IRange labelRange, IRange categoryRange)
{
uint orderStart = (uint)this.chartSpace.Charts.TakeWhile(c => c != this).Sum(c => c.SeriesCount);
this.barChart.RemoveAllChildren <BarChartSeries>();
IWorksheet worksheet = labelRange.Worksheet;
if (labelRange.Width == 1 && labelRange.Height > 0)
{
if (categoryRange.Height == 1 && categoryRange.Width > 0)
{
for (uint labelIndex = 0; labelIndex < labelRange.Height; ++labelIndex)
{
BarChartSeries series = this.barChart.AppendChild(new BarChartSeries()
{
Index = new Index()
{
Val = labelIndex
}, Order = new Order()
{
Val = orderStart + labelIndex
}
});
series.AppendChild(
new SeriesText().AppendChildFluent(
new StringReference()
{
Formula = new Formula(labelRange[0, labelIndex].Reference),
StringCache = new StringCache()
.AppendChildFluent(new PointCount()
{
Val = 1
})
.AppendChildFluent(new StringPoint()
{
Index = 0, NumericValue = new NumericValue()
{
Text = labelRange[0, labelIndex].InnerValue
}
})
}
)
);
series.AppendChild(
new CategoryAxisData().AppendChildFluent(
new StringReference()
{
Formula = new Formula(categoryRange.Formula),
StringCache = new StringCache()
.AppendChildFluent(new PointCount()
{
Val = (uint)categoryRange.Width.Value
})
.AppendFluent(Enumerable.Range(0, categoryRange.Width.Value).Select(categoryIndex => new StringPoint()
{
Index = (uint)categoryIndex, NumericValue = new NumericValue()
{
Text = categoryRange[(uint)categoryIndex, 0].InnerValue
}
}))
}
)
);
string valuesFormula = ReferenceEncoder.EncodeRangeReference(
worksheet.Name,
categoryRange.StartColumn, false,
labelRange.StartRow + labelIndex, false,
categoryRange.EndColumn, false,
labelRange.StartRow + labelIndex, false
);
series.AppendChild(
new Values().AppendChildFluent(
new NumberReference()
{
Formula = new Formula(valuesFormula),
NumberingCache = new NumberingCache()
.AppendChildFluent(new PointCount()
{
Val = (uint)categoryRange.Width.Value
})
.AppendFluent(Enumerable.Range(0, categoryRange.Width.Value).Select(categoryIndex => new NumericPoint()
{
Index = (uint)categoryIndex, NumericValue = new NumericValue()
{
Text = worksheet.Cells[categoryRange.StartColumn.Value + (uint)categoryIndex, labelRange.StartRow.Value + labelIndex].InnerValue
}
}))
}
)
);
}
}
else
{
throw new ArgumentException();
}
}
else if (labelRange.Height == 1 && labelRange.Width > 0)
{
if (categoryRange.Width == 1 && categoryRange.Height > 0)
{
for (uint labelIndex = 0; labelIndex < labelRange.Width; ++labelIndex)
{
BarChartSeries series = this.barChart.AppendChild(new BarChartSeries()
{
Index = new Index()
{
Val = labelIndex
}, Order = new Order()
{
Val = orderStart + labelIndex
}
});
series.AppendChild(
new SeriesText().AppendChildFluent(
new StringReference()
{
Formula = new Formula(labelRange[labelIndex, 0].Reference),
StringCache = new StringCache()
.AppendChildFluent(new PointCount()
{
Val = 1
})
.AppendChildFluent(new StringPoint()
{
Index = 0, NumericValue = new NumericValue()
{
Text = labelRange[labelIndex, 0].InnerValue
}
})
}
)
);
series.AppendChild(
new CategoryAxisData().AppendChildFluent(
new StringReference()
{
Formula = new Formula(categoryRange.Formula),
StringCache = new StringCache()
.AppendChildFluent(new PointCount()
{
Val = (uint)categoryRange.Height.Value
})
.AppendFluent(Enumerable.Range(0, categoryRange.Height.Value).Select(categoryIndex => new StringPoint()
{
Index = (uint)categoryIndex, NumericValue = new NumericValue()
{
Text = categoryRange[0, (uint)categoryIndex].InnerValue
}
}))
}
)
);
string valuesFormula = ReferenceEncoder.EncodeRangeReference(
worksheet.Name,
labelRange.StartColumn + labelIndex, false,
categoryRange.StartRow, false,
labelRange.StartColumn + labelIndex, false,
categoryRange.EndRow, false
);
series.AppendChild(
new Values().AppendChildFluent(
new NumberReference()
{
Formula = new Formula(valuesFormula),
NumberingCache = new NumberingCache()
.AppendChildFluent(new PointCount()
{
Val = (uint)categoryRange.Height.Value
})
.AppendFluent(Enumerable.Range(0, categoryRange.Height.Value).Select(categoryIndex => new NumericPoint()
{
Index = (uint)categoryIndex, NumericValue = new NumericValue()
{
Text = worksheet.Cells[labelRange.StartColumn.Value + labelIndex, categoryRange.StartRow.Value + (uint)categoryIndex].InnerValue
}
}))
}
)
);
}
}
else
{
throw new ArgumentException();
}
}
else
{
throw new ArgumentException();
}
return(this);
}
internal override void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location)
{
DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>();
writer.WriteStartElement(new Drawing()
{
Id = part.GetIdOfPart(drawingsPart)
});
writer.WriteEndElement();
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart());
chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted()
{
Val = false
});
// Create a new clustered column chart.
PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea());
Layout layout1 = plotArea.AppendChild <Layout>(new Layout());
BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart());
barChart.Append(new BarDirection()
{
Val = BarDirectionValues.Bar
});
barChart.Append(new BarGrouping()
{
Val = BarGroupingValues.Stacked
});
barChart.Append(new GapWidth()
{
Val = (UInt16Value)75U
});
barChart.Append(new Overlap()
{
Val = 100
});
GanttTypeChart ganttChart = new GanttTypeChart(UserSettings);
var groupedData = GanttData
.GroupBy(x => x.Name);
List <GanttDataPairedSeries> ganttDataWithSeries = new List <GanttDataPairedSeries>();
for (int i = 0; i < groupedData.Max(x => x.Count()); i++)
{
// For each series create a hidden one for spacing.
BarChartSeries barChartSeriesHidden = barChart.AppendChild <BarChartSeries>(new BarChartSeries(
new Index()
{
Val = new UInt32Value((uint)(i * 2))
},
new Order()
{
Val = new UInt32Value((uint)(i * 2))
},
new SeriesText(new NumericValue()
{
Text = "Not Active"
})));
BarChartSeries barChartSeriesValue = barChart.AppendChild <BarChartSeries>(new BarChartSeries(
new Index()
{
Val = new UInt32Value((uint)(i * 2) + 1)
},
new Order()
{
Val = new UInt32Value((uint)(i * 2) + 1)
},
new SeriesText(new NumericValue()
{
Text = "Time Spent"
})));
ganttChart.SetChartShapeProperties(barChartSeriesHidden, visible: false);
ganttChart.SetChartShapeProperties(barChartSeriesValue, colorPoints: (uint)GanttData.Count);
var ganttData = new List <GanttData>();
foreach (var data in groupedData.Where(x => x.Count() >= i + 1))
{
ganttData.Add(data.ElementAt(i));
}
ganttDataWithSeries.Add(new GanttDataPairedSeries()
{
BarChartSeriesHidden = barChartSeriesHidden,
BarChartSeriesValue = barChartSeriesValue,
Values = ganttData
});
}
ganttChart.SetChartAxis(ganttDataWithSeries, groupedData.ToList());
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
// Add the Category Axis (X axis).
ganttChart.SetGanttCategoryAxis(plotArea);
// Add the Value Axis (Y axis).
ganttChart.SetGanttValueAxis(plotArea, GanttData.Min(x => x.Start), GanttData.Max(x => x.End));
chartContainer.Append(new PlotVisibleOnly()
{
Val = new BooleanValue(true)
});
ganttChart.SetChartLocation(drawingsPart, chartPart, location);
}
public void CreateExcelDoc(string fileName)
{
List <Student> students = new List <Student>();
Initizalize(students);
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Students"
};
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Add drawing part to WorksheetPart
DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
sheets.Append(sheet);
workbookPart.Workbook.Save();
// Add a new chart and set the chart language
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.AppendChild(new EditingLanguage()
{
Val = "en-US"
});
Chart chart = chartPart.ChartSpace.AppendChild(new Chart());
chart.AppendChild(new AutoTitleDeleted()
{
Val = true
}); // We don't want to show the chart title
// Create a new Clustered Column Chart
PlotArea plotArea = chart.AppendChild(new PlotArea());
Layout layout = plotArea.AppendChild(new Layout());
BarChart barChart = plotArea.AppendChild(new BarChart(
new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
},
new VaryColors()
{
Val = false
}
));
// Constructing header
Row row = new Row();
int rowIndex = 1;
row.AppendChild(ConstructCell(string.Empty, CellValues.String));
foreach (var month in Months.Short)
{
row.AppendChild(ConstructCell(month, CellValues.String));
}
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
rowIndex++;
// Create chart series
for (int i = 0; i < students.Count; i++)
{
BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(
new Index()
{
Val = (uint)i
},
new Order()
{
Val = (uint)i
},
new SeriesText(new NumericValue()
{
Text = students[i].Name
})
));
// Adding category axis to the chart
CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData());
// Category
// Constructing the chart category
string formulaCat = "Students!$B$1:$G$1";
StringReference stringReference = categoryAxisData.AppendChild(new StringReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula()
{
Text = formulaCat
}
});
StringCache stringCache = stringReference.AppendChild(new StringCache());
stringCache.Append(new PointCount()
{
Val = (uint)Months.Short.Length
});
for (int j = 0; j < Months.Short.Length; j++)
{
stringCache.AppendChild(new NumericPoint()
{
Index = (uint)j
}).Append(new NumericValue(Months.Short[j]));
}
}
var chartSeries = barChart.Elements <BarChartSeries>().GetEnumerator();
for (int i = 0; i < students.Count; i++)
{
row = new Row();
row.AppendChild(ConstructCell(students[i].Name, CellValues.String));
chartSeries.MoveNext();
string formulaVal = string.Format("Students!$B${0}:$G${0}", rowIndex);
DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values());
NumberReference numberReference = values.AppendChild(new NumberReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula()
{
Text = formulaVal
}
});
NumberingCache numberingCache = numberReference.AppendChild(new NumberingCache());
numberingCache.Append(new PointCount()
{
Val = (uint)Months.Short.Length
});
for (uint j = 0; j < students[i].Values.Length; j++)
{
var value = students[i].Values[j];
row.AppendChild(ConstructCell(value.ToString(), CellValues.Number));
numberingCache.AppendChild(new NumericPoint()
{
Index = j
}).Append(new NumericValue(value.ToString()));
}
sheetData.AppendChild(row);
rowIndex++;
}
barChart.AppendChild(new DataLabels(
new ShowLegendKey()
{
Val = false
},
new ShowValue()
{
Val = false
},
new ShowCategoryName()
{
Val = false
},
new ShowSeriesName()
{
Val = false
},
new ShowPercent()
{
Val = false
},
new ShowBubbleSize()
{
Val = false
}
));
barChart.Append(new AxisId()
{
Val = 48650112u
});
barChart.Append(new AxisId()
{
Val = 48672768u
});
// Adding Category Axis
plotArea.AppendChild(
new CategoryAxis(
new AxisId()
{
Val = 48650112u
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = 48672768u
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new AutoLabeled()
{
Val = true
},
new LabelAlignment()
{
Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center)
}
));
// Adding Value Axis
plotArea.AppendChild(
new ValueAxis(
new AxisId()
{
Val = 48672768u
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left)
},
new MajorGridlines(),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "General",
SourceLinked = true
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = 48650112u
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new CrossBetween()
{
Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between)
}
));
chart.Append(
new PlotVisibleOnly()
{
Val = true
},
new DisplayBlanksAs()
{
Val = new EnumValue <DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap)
},
new ShowDataLabelsOverMaximum()
{
Val = false
}
);
chartPart.ChartSpace.Save();
// Positioning the chart on the spreadsheet
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(
new ColumnId("0"),
new ColumnOffset("0"),
new RowId((rowIndex + 2).ToString()),
new RowOffset("0")
));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(
new ColumnId("8"),
new ColumnOffset("0"),
new RowId((rowIndex + 12).ToString()),
new RowOffset("0")
));
// Append GraphicFrame to TwoCellAnchor
GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new GraphicFrame());
graphicFrame.Macro = string.Empty;
graphicFrame.Append(new NonVisualGraphicFrameProperties(
new NonVisualDrawingProperties()
{
Id = 2u,
Name = "Sample Chart"
},
new NonVisualGraphicFrameDrawingProperties()
));
graphicFrame.Append(new Transform(
new DocumentFormat.OpenXml.Drawing.Offset()
{
X = 0L, Y = 0L
},
new DocumentFormat.OpenXml.Drawing.Extents()
{
Cx = 0L, Cy = 0L
}
));
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Graphic(
new DocumentFormat.OpenXml.Drawing.GraphicData(
new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
}
)
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}
));
twoCellAnchor.Append(new ClientData());
drawingsPart.WorksheetDrawing.Save();
worksheetPart.Worksheet.Save();
}
}
/// <summary>
/// Sets the color of the series using a solidcolor brush
/// If a null brush is supplied any color is removed so the color will be automatic
/// </summary>
/// <param name="line">The line.</param>
/// <param name="brush">The brush.</param>
public static void UpdateLineBrush(this OpenXmlCompositeElement line, Brush brush)
{
if (line == null)
{
return;
}
// If we have a BarChart, we really want tp update the SolidFill (not the Outline.SolidFill)
BarChartSeries barChartSeries = line as BarChartSeries;
if (barChartSeries != null)
{
// For BarCharts, we update the SolidFill
barChartSeries.UpdateSeriesColour((SolidColorBrush)brush);
}
else
{
// Update the Outline.SolidFill + set the SolidFill to the same colour
var chartShapeProperties = line.Descendants <ChartShapeProperties>().FirstOrDefault();
if (brush == null && !(brush is SolidColorBrush))
{
if (chartShapeProperties != null)
{
line.RemoveChild <ChartShapeProperties>(chartShapeProperties);
}
return;
}
// the series title, this is the name of the column header
var seriesText = line.Descendants <SeriesText>().FirstOrDefault();
if (chartShapeProperties == null)
{
chartShapeProperties = new ChartShapeProperties();
// if there's a series text then insert afterwards
if (seriesText == null)
{
line.InsertAt <ChartShapeProperties>(chartShapeProperties, 0);
}
else
{
line.InsertAfter <ChartShapeProperties>(chartShapeProperties, seriesText);
}
}
var outline = chartShapeProperties.Descendants <Outline>().FirstOrDefault();
if (outline == null)
{
outline = new Outline();
chartShapeProperties.InsertAt(outline, 0);
}
var outlineSolidFill = outline.Descendants <SolidFill>().FirstOrDefault();
if (outlineSolidFill == null)
{
outlineSolidFill = new SolidFill();
outline.Append(outlineSolidFill);
}
// Update the fill with the supplied brush colour
outlineSolidFill.UpdateSolidFill((SolidColorBrush)brush);
// Clones the OutlineSolidFill as the SolidFill of the series...
var solidFill = chartShapeProperties.GetFirstChild <SolidFill>();
if (solidFill != null)
{
chartShapeProperties.RemoveChild(solidFill);
}
chartShapeProperties.InsertAt(outlineSolidFill.CloneNode(true), 0);
}
}
private void CreateHistogram(PlotArea plotArea, Statistics stat, uint index, uint categoryAxisId, uint valueAxisId)
{
BarChart barChart =
plotArea.AppendChild <BarChart>(
new BarChart(
new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
},
new VaryColors()
{
Val = false
}
));
BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(new Index()
{
Val = new UInt32Value(index)
},
new Order()
{
Val = new UInt32Value(index)
},
new SeriesText(new NumericValue()
{
Text = "Histogram"
})));
StringLiteral strLit =
barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData())
.AppendChild <StringLiteral>(new StringLiteral());
strLit.Append(new PointCount()
{
Val = new UInt32Value((uint)stat.Frequencies.Count)
});
NumberLiteral numLit = barChartSeries.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Values>(
new DocumentFormat.OpenXml.Drawing.Charts.Values())
.AppendChild <NumberLiteral>(new NumberLiteral());
numLit.Append(new FormatCode("General"));
numLit.Append(new PointCount()
{
Val = new UInt32Value((uint)stat.Frequencies.Count)
});
for (uint i = 0; i < stat.Frequencies.Count; i++)
{
strLit.AppendChild <StringPoint>(new StringPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(stat.Frequencies[(int)i].Value.ToString()));
numLit.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(stat.Frequencies[(int)i].Count.ToString()));
}
barChart.Append(new AxisId()
{
Val = new UInt32Value(categoryAxisId)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(valueAxisId)
});
AppendCategoryAxis(plotArea, categoryAxisId, "Time, ms", valueAxisId);
AppendValueAxis(plotArea, valueAxisId, "Number of samples", categoryAxisId);
}
private static void InsertChartInSpreadsheet(string docName, string worksheetName, string title,
Dictionary <string, int> data)
{
// Open the document for editing.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
{
IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>().
Where(s => s.Name == worksheetName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
// Add a new drawing to the worksheet.
DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
// Add a new chart and set the chart language to English-US.
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>(
new DocumentFormat.OpenXml.Drawing.Charts.Chart());
// Create a new clustered column chart.
PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea());
Layout layout = plotArea.AppendChild <Layout>(new Layout());
BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart(new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
}));
uint i = 0;
// Iterate through each key in the Dictionary collection and add the key to the chart Series
// and add the corresponding value to the chart Values.
foreach (string key in data.Keys)
{
BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index()
{
Val =
new UInt32Value(i)
},
new Order()
{
Val = new UInt32Value(i)
},
new SeriesText(new NumericValue()
{
Text = key
})));
StringLiteral strLit = barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData()).AppendChild <StringLiteral>(new StringLiteral());
strLit.Append(new PointCount()
{
Val = new UInt32Value(1U)
});
strLit.AppendChild <StringPoint>(new StringPoint()
{
Index = new UInt32Value(0U)
}).Append(new NumericValue(key));
NumberLiteral numLit = barChartSeries.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Values>(
new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild <NumberLiteral>(new NumberLiteral());
numLit.Append(new FormatCode("General"));
numLit.Append(new PointCount()
{
Val = new UInt32Value(1U)
});
numLit.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(0u)
})
.Append(new NumericValue(data[key].ToString()));
i++;
}
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
//// Add the Category Axis.
CategoryAxis catAx = plotArea.AppendChild <CategoryAxis>(new CategoryAxis(new AxisId()
{
Val = new UInt32Value(48650112u)
}, new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.
OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = new UInt32Value(48672768U)
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new AutoLabeled()
{
Val = new BooleanValue(true)
},
new LabelAlignment()
{
Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center)
},
new LabelOffset()
{
Val = new UInt16Value((ushort)100)
}));
// Add the Value Axis.
ValueAxis valAx = plotArea.AppendChild <ValueAxis>(new ValueAxis(new AxisId()
{
Val = new UInt32Value(48672768u)
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(
DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left)
},
new MajorGridlines(),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = new StringValue("General"),
SourceLinked = new BooleanValue(true)
}, new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>
(TickLabelPositionValues.NextTo)
}, new CrossingAxis()
{
Val = new UInt32Value(48650112U)
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new CrossBetween()
{
Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between)
}));
// Add the chart Legend.
Legend legend = chart.AppendChild <Legend>(new Legend(new LegendPosition()
{
Val = new EnumValue <LegendPositionValues>(LegendPositionValues.Right)
},
new Layout()));
chart.Append(new PlotVisibleOnly()
{
Val = new BooleanValue(true)
});
// Save the chart part.
chartPart.ChartSpace.Save();
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild <TwoCellAnchor>(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("1"),
new ColumnOffset("581025"),
new RowId("1"),
new RowOffset("114300")));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("10"),
new ColumnOffset("276225"),
new RowId("16"),
new RowOffset("0")));
// Append a GraphicFrame to the TwoCellAnchor object.
DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
twoCellAnchor.AppendChild <DocumentFormat.OpenXml.
Drawing.Spreadsheet.GraphicFrame>(new DocumentFormat.OpenXml.Drawing.
Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
{
Id = new UInt32Value(2u), Name = "Chart 1"
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));
graphicFrame.Append(new Transform(new Offset()
{
X = 0L, Y = 0L
},
new Extents()
{
Cx = 0L, Cy = 0L
}));
graphicFrame.Append(new Graphic(new GraphicData(new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}));
twoCellAnchor.Append(new ClientData());
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
}
}
/// <summary>
/// draw the 2D bar chart
/// index start from 1
/// </summary>
/// <param name="startx">index start from 1 for row</param>
/// <param name="starty">index start from 1 for column</param>
/// <param name="columnCount"></param>
/// <param name="rowCount"></param>
public void InsertChartInSpreadsheet(WorksheetPart sheetpart, string sheetName, int startx, int starty, int columnCount, int rowCount, int chart_pointx, int chart_pointy)
{
WorksheetPart worksheetPart = CurrentWorksheetPart;
#region SDK How to example code
// Add a new drawing to the worksheet.
DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
// Add a new chart and set the chart language to English-US.
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>(
new DocumentFormat.OpenXml.Drawing.Charts.Chart());
// Create a new clustered column chart.
PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea());
Layout layout = plotArea.AppendChild <Layout>(new Layout());
BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart(new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
}));
#endregion
string sheetName = GetCurrentSheetName();
string columnName = GetColumnName(starty - 1);
string formulaString = string.Format("{0}!${1}${2}:${3}${4}", sheetName, columnName, startx + 1, columnName, startx + rowCount - 1);
CategoryAxisData cad = new CategoryAxisData();
cad.StringReference = new StringReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString)
};
uint i = 0;
for (int sIndex = 1; sIndex < columnCount; sIndex++)
{
columnName = GetColumnName(starty + sIndex - 1);
formulaString = string.Format("{0}!${1}${2}", sheetName, columnName, startx);
SeriesText st = new SeriesText();
st.StringReference = new StringReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString)
};
formulaString = string.Format("{0}!${1}${2}:${3}${4}", sheetName, columnName, startx + 1, columnName, startx + rowCount - 1);
DocumentFormat.OpenXml.Drawing.Charts.Values v = new DocumentFormat.OpenXml.Drawing.Charts.Values();
v.NumberReference = new NumberReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString)
};
BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index()
{
Val = new UInt32Value(i)
},
new Order()
{
Val = new UInt32Value(i)
}, st, v));
if (sIndex == 1)
{
barChartSeries.AppendChild(cad);
}
i++;
}
#region SDK how to example Code
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
// Add the Category Axis.
CategoryAxis catAx = plotArea.AppendChild <CategoryAxis>(new CategoryAxis(new AxisId()
{
Val = new UInt32Value(48650112u)
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(
DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = new UInt32Value(48672768U)
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new AutoLabeled()
{
Val = new BooleanValue(true)
},
new LabelAlignment()
{
Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center)
},
new LabelOffset()
{
Val = new UInt16Value((ushort)100)
}));
// Add the Value Axis.
ValueAxis valAx = plotArea.AppendChild <ValueAxis>(new ValueAxis(new AxisId()
{
Val = new UInt32Value(48672768u)
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(
DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left)
},
new MajorGridlines(),
new DocumentFormat.OpenXml.Drawing.Charts.NumberFormat()
{
FormatCode = new StringValue("General"), SourceLinked = new BooleanValue(true)
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = new UInt32Value(48650112U)
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new CrossBetween()
{
Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between)
}));
// Add the chart Legend.
Legend legend = chart.AppendChild <Legend>(new Legend(new LegendPosition()
{
Val = new EnumValue <LegendPositionValues>(LegendPositionValues.Right)
},
new Layout()));
chart.Append(new PlotVisibleOnly()
{
Val = new BooleanValue(true)
});
// Save the chart part.
chartPart.ChartSpace.Save();
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild <TwoCellAnchor>(new TwoCellAnchor());
twoCellAnchor.Append(new FromMarker(new ColumnId("9"),
new ColumnOffset("581025"),
new RowId("17"),
new RowOffset("114300")));
twoCellAnchor.Append(new ToMarker(new ColumnId("17"),
new ColumnOffset("276225"),
new RowId("32"),
new RowOffset("0")));
// Append a GraphicFrame to the TwoCellAnchor object.
DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
twoCellAnchor.AppendChild <DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame>(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
{
Id = new UInt32Value(2u), Name = "Chart 1"
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));
graphicFrame.Append(new Transform(new Offset()
{
X = 0L, Y = 0L
},
new Extents()
{
Cx = 0L, Cy = 0L
}));
graphicFrame.Append(new Graphic(new GraphicData(new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}));
twoCellAnchor.Append(new ClientData());
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
#endregion
}
private void FillPoints(string baseFormula, String mode, List <String> data)
{
int idx = data.Count;
ChartPart cp = Document.MainDocumentPart.ChartParts.FirstOrDefault();
Chart chart = cp.ChartSpace.Elements <Chart>().FirstOrDefault();
BarChart barchart = chart.PlotArea.Elements <BarChart>().FirstOrDefault();
BarChartSeries series = barchart.Elements <BarChartSeries>().FirstOrDefault();
CategoryAxisData labels = new CategoryAxisData();
DocumentFormat.OpenXml.Drawing.Charts.Values values = new DocumentFormat.OpenXml.Drawing.Charts.Values();
NumberReference nref = new NumberReference();
string formula = baseFormula + (idx + 1);
DocumentFormat.OpenXml.Drawing.Charts.Formula f = new DocumentFormat.OpenXml.Drawing.Charts.Formula();
f.Text = formula;
Log.Info(formula);
nref.Formula = f;
NumberingCache nc = new NumberingCache();//nref.Descendants<NumberingCache>().First();
nc.PointCount = new PointCount();
nc.PointCount.Val = (uint)idx;
int pointIndex = 0;
foreach (string val in data)
{
NumericPoint point = new NumericPoint();
point.Index = (uint)pointIndex;
NumericValue value = new NumericValue();
if ("LABELS".Equals(mode))
{
value.Text = val;
}
else if ("VALUES".Equals(mode))
{
if (val != "0")
{
float valuePerc = float.Parse(val, CultureInfo.InvariantCulture.NumberFormat) * 100;
value.Text = valuePerc.ToString(CultureInfo.InvariantCulture);
}
else
{
value.Text = "";
}
}
point.AppendChild(value);
nc.AppendChild(point);
pointIndex++;
}
nref.AppendChild(nc);
if ("LABELS".Equals(mode))
{
labels.AppendChild(nref);
series.ReplaceChild <CategoryAxisData>(labels, series.Elements <CategoryAxisData>().FirstOrDefault());
}
else if ("VALUES".Equals(mode))
{
values.AppendChild(nref);
series.ReplaceChild <DocumentFormat.OpenXml.Drawing.Charts.Values>(values, series.Elements <DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault());
}
;
}