internal C.XValues ToXValues()
{
var xv = new C.XValues();
if (UseMultiLevelStringReference)
{
xv.MultiLevelStringReference = MultiLevelStringReference.ToMultiLevelStringReference();
}
if (UseNumberReference)
{
xv.NumberReference = NumberReference.ToNumberReference();
}
if (UseNumberLiteral)
{
xv.NumberLiteral = NumberLiteral.ToNumberLiteral();
}
if (UseStringReference)
{
xv.StringReference = StringReference.ToStringReference();
}
if (UseStringLiteral)
{
xv.StringLiteral = StringLiteral.ToStringLiteral();
}
return(xv);
}
/// <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)
{
LineChartSeries linechart_series = chart_part.ChartSpace.Descendants <LineChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).First();
DocumentFormat.OpenXml.Drawing.Charts.Values v = linechart_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 NumberReference Build()
{
var numberReference = new NumberReference();
var formulaString = string.Concat(this.Options.Letter, "$", this.Options.RowStart, ":", this.Options.Letter, this.Options.RowEnd);
numberReference.Formula = new Formula(formulaString);
numberReference.NumberingCache = new NumberingCache();
numberReference.NumberingCache.FormatCode = new FormatCode(this.Options.FormatCode);
numberReference.NumberingCache.PointCount = new PointCount {
Val = (uint)this.Options.Values.Count
};
for (var index = 0; index < this.Options.Values.Count; index++)
{
var numericPoint = new NumericPoint();
numericPoint.Index = (uint)index;
numericPoint.NumericValue = new NumericValue(this.Options.Values[index]);
numberReference.NumberingCache.Append(numericPoint);
}
return(numberReference);
}
internal C.CategoryAxisData ToCategoryAxisData()
{
var cad = new C.CategoryAxisData();
if (UseMultiLevelStringReference)
{
cad.MultiLevelStringReference = MultiLevelStringReference.ToMultiLevelStringReference();
}
if (UseNumberReference)
{
cad.NumberReference = NumberReference.ToNumberReference();
}
if (UseNumberLiteral)
{
cad.NumberLiteral = NumberLiteral.ToNumberLiteral();
}
if (UseStringReference)
{
cad.StringReference = StringReference.ToStringReference();
}
if (UseStringLiteral)
{
cad.StringLiteral = StringLiteral.ToStringLiteral();
}
return(cad);
}
/// <summary>
/// Creates the number reference.
/// </summary>
/// <param name="values">The values.</param>
/// <param name="formulaText">The formula text.</param>
/// <returns></returns>
protected static NumberReference CreateNumberReference(NumberDataSourceType values, string formulaText)
{
values.RemoveAllChildren <NumberReference>();
NumberReference numberReference = values.AppendChild <NumberReference>(new NumberReference());
numberReference.Formula = new Formula();
numberReference.Formula.Text = formulaText;
return(numberReference);
}
internal SLNumberDataSourceType Clone()
{
var ndst = new SLNumberDataSourceType();
ndst.bUseNumberReference = bUseNumberReference;
ndst.NumberReference = NumberReference.Clone();
ndst.bUseNumberLiteral = bUseNumberLiteral;
ndst.NumberLiteral = NumberLiteral.Clone();
return(ndst);
}
/// <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);
}
}
internal C.Plus ToPlus()
{
var plus = new C.Plus();
if (UseNumberReference)
{
plus.NumberReference = NumberReference.ToNumberReference();
}
if (UseNumberLiteral)
{
plus.NumberLiteral = NumberLiteral.ToNumberLiteral();
}
return(plus);
}
internal C.Minus ToMinus()
{
var minus = new C.Minus();
if (UseNumberReference)
{
minus.NumberReference = NumberReference.ToNumberReference();
}
if (UseNumberLiteral)
{
minus.NumberLiteral = NumberLiteral.ToNumberLiteral();
}
return(minus);
}
internal C.BubbleSize ToBubbleSize()
{
var bs = new C.BubbleSize();
if (UseNumberReference)
{
bs.NumberReference = NumberReference.ToNumberReference();
}
if (UseNumberLiteral)
{
bs.NumberLiteral = NumberLiteral.ToNumberLiteral();
}
return(bs);
}
internal C.YValues ToYValues()
{
var yv = new C.YValues();
if (UseNumberReference)
{
yv.NumberReference = NumberReference.ToNumberReference();
}
if (UseNumberLiteral)
{
yv.NumberLiteral = NumberLiteral.ToNumberLiteral();
}
return(yv);
}
internal C.Values ToValues()
{
var v = new C.Values();
if (UseNumberReference)
{
v.NumberReference = NumberReference.ToNumberReference();
}
if (UseNumberLiteral)
{
v.NumberLiteral = NumberLiteral.ToNumberLiteral();
}
return(v);
}
/// <summary>
/// Updates the chart.
/// </summary>
/// <param name="chart">The chart.</param>
/// <param name="sheetName">Name of the sheet.</param>
protected override void UpdateChart(OpenXmlCompositeElement chart, string sheetName)
{
if (chart != null)
{
chart.RemoveAllChildren <LineChartSeries>();
// Index 0 is for category axis data
for (int index = 1; index < chartData.columnNameToSeries.Count(); index++)
{
string columnName = GetExcelColumnName(index);
LineChartSeries lineChartSeries = chart.AppendChild <LineChartSeries>(new LineChartSeries());
UpdateSeriesText(sheetName, lineChartSeries, columnName, 1, chartData.columnNameToSeries.Skip(index).FirstOrDefault().Key);
// Update Category Axis data
CategoryAxisData catAxisData = new CategoryAxisData();
catAxisData.RemoveAllChildren <StringReference>();
catAxisData.RemoveAllChildren <NumberReference>();
StringReference catStringReference = GetStringReference(sheetName + "!$A$2:$A$" + (chartData.Count + 1).ToString(), chartData.Count);
// Series 0 is for category axis data
foreach (string cat in chartData.columnNameToSeries.First().Value)
{
AddStringPoint(catStringReference.StringCache, catStringReference.StringCache.Descendants <StringPoint>().Count(), cat);
}
catAxisData.Append(catStringReference);
// Update Values
NumberingCache numberingCache;
PointCount pointCount;
Values values = lineChartSeries.AppendChild <Values>(new Values());
NumberReference reference = CreateNumberReference(values, sheetName + "!$" + columnName + "$2:$" + columnName + "$" + (chartData.Count + 1).ToString());
SetNumberingCache(reference, out numberingCache, out pointCount);
int rowIndex = 0;
foreach (var point in chartData.columnNameToSeries.Skip(index).FirstOrDefault().Value)
{
AddNumericPoint(numberingCache, pointCount, rowIndex, point.ToString());
rowIndex += 1;
}
}
}
}
internal SLAxisDataSourceType Clone()
{
var adst = new SLAxisDataSourceType();
adst.bUseMultiLevelStringReference = bUseMultiLevelStringReference;
adst.bUseNumberLiteral = bUseNumberLiteral;
adst.bUseNumberReference = bUseNumberReference;
adst.bUseStringLiteral = bUseStringLiteral;
adst.bUseStringReference = bUseStringReference;
adst.MultiLevelStringReference = MultiLevelStringReference.Clone();
adst.NumberLiteral = NumberLiteral.Clone();
adst.NumberReference = NumberReference.Clone();
adst.StringLiteral = StringLiteral.Clone();
adst.StringReference = StringReference.Clone();
return(adst);
}
/// <summary>
/// Updates the chart.
/// </summary>
/// <param name="chart">The chart.</param>
/// <param name="sheetName">Name of the sheet.</param>
protected override void UpdateChart(OpenXmlCompositeElement chart, string sheetName)
{
if (chart != null)
{
chart.RemoveAllChildren <ScatterChartSeries>();
ScatterChartSeries scatterChartSeries = chart.AppendChild <ScatterChartSeries>(new ScatterChartSeries());
Outline outline = new Outline()
{
Width = 28575
};
NoFill noFill = new NoFill();
outline.Append(noFill);
scatterChartSeries.ChartShapeProperties = new ChartShapeProperties(outline);
UpdateSeriesText(sheetName, scatterChartSeries, "B", 1, chartData.yColumnName);
XValues xValues = scatterChartSeries.AppendChild <XValues>(new XValues());
YValues yValues = scatterChartSeries.AppendChild <YValues>(new YValues());
NumberReference referenceX = CreateNumberReference(xValues, sheetName + "!$A$2:$A$" + (chartData.Count + 1).ToString());
NumberReference referenceY = CreateNumberReference(yValues, sheetName + "!$B$2:$B$" + (chartData.Count + 1).ToString());
NumberingCache ncX;
PointCount ptXCount;
NumberingCache ncY;
PointCount ptYCount;
SetNumberingCache(referenceX, out ncX, out ptXCount);
SetNumberingCache(referenceY, out ncY, out ptYCount);
int rowIndex = 0;
foreach (var xToY in chartData.xValToYValMap)
{
AddNumericPoint(ncX, ptXCount, rowIndex, xToY.Key.ToString());
AddNumericPoint(ncY, ptYCount, rowIndex, xToY.Value.ToString());
rowIndex += 1;
}
}
}
private void FillNumberReference(NumberReference valuesNumberReference, ChartSeriesElement newSeriesItem, Column dataColumn)
{
int dataCount = dataColumn.Data.Count;
UInt32Value pointCount = new UInt32Value((uint)dataCount);
valuesNumberReference.Formula.Text = newSeriesItem.ValuesAddress;
valuesNumberReference.NumberingCache.RemoveAllChildren <NumericPoint>();
valuesNumberReference.NumberingCache.PointCount.Val = pointCount;
for (int rowNo = 0; rowNo < dataColumn.Data.Count; rowNo++)
{
if (dataColumn.Data[rowNo] != null)
{
var point = new NumericPoint()
{
Index = new UInt32Value((uint)rowNo)
};
point.NumericValue = new NumericValue(dataColumn.Data[rowNo] == null ? "0" : string.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}", dataColumn.Data[rowNo]));
valuesNumberReference.NumberingCache.Append(point);
}
}
}
/// <summary>
/// Sets the numbering cache.
/// </summary>
/// <param name="numberReference">The number reference.</param>
/// <param name="numberingCache">The numbering cache.</param>
/// <param name="pointCount">The point count.</param>
protected static void SetNumberingCache(NumberReference numberReference, out NumberingCache numberingCache, out PointCount pointCount)
{
numberingCache = numberReference.Descendants <NumberingCache>().FirstOrDefault();
if (numberingCache == null)
{
numberingCache = new NumberingCache();
numberReference.AppendChild <NumberingCache>(numberingCache);
}
pointCount = numberingCache.Descendants <PointCount>().FirstOrDefault();
if (pointCount == null)
{
pointCount = new PointCount();
numberingCache.AppendChild <PointCount>(pointCount);
}
pointCount.Val = new UInt32Value((uint)0);
numberingCache.FormatCode = new FormatCode("General");
}
/// <summary>
/// 指定した数値numをセットする
/// </summary>
/// <param name="num">数値</param>
/// <returns>解放後、参照カウンタを１下げた値を返す。0の場合は、完全に解放されたことを示す。</returns>
public ulong Acquire(ref ulong num)
{
NumberReference Tmp = new NumberReference();
num = default(ulong);
//-------------------------------------------
// 存在する場合はカウントを＋１する。
//-------------------------------------------
if (num != default(ulong))
{
if (m_useData.ContainsKey(num))
{
++m_useData[num].refCnt;
return(m_useData[num].refCnt);
}
}
//-------------------------------------------
// フリーリストが空なら、新しいものを追加し、
// そうでなければ、使用済みの値を使う。
//-------------------------------------------
if (m_freeSlots.Count == 0)
{
// コンテナが空
num = ++m_numCount;
Tmp.id = m_numCount;
Tmp.refCnt = 1;
m_useData.Add(Tmp.id, Tmp);
}
else
{
Tmp = m_freeSlots.Dequeue();
num = Tmp.id;
Tmp.refCnt = 1;
m_useData.Add(Tmp.id, Tmp);
}
return(Tmp.refCnt);
}
/// <summary>
/// 指定した数値numを解放する
/// </summary>
/// <param name="num">数値</param>
/// <returns>解放後、参照カウンタを１下げた値を返す。0の場合は、完全に解放されたことを示す。</returns>
public ulong Release(ulong num)
{
NumberReference Tmp = new NumberReference();
// どれか？
// それが有効なことを確認
if (!m_useData.ContainsKey(num))
{
Debug.Assert(false);
return(0);
}
if (--m_useData[num].refCnt <= 0)
{
// 削除 & 未使用のフリーリストに追加
m_freeSlots.Enqueue(m_useData[num]);
m_useData.Remove(num);
return(0);
}
return(m_useData[num].refCnt);
}
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();
}
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();
}
}
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());
}
;
}
protected void modificaChartData(string FormatoValori, string titoloSerie, out SeriesText seriesText1, out CategoryAxisData categoryAxisData1, out Values values1)
{
seriesText1 = new SeriesText();
StringReference stringReference1 = new StringReference();
Formula formula1 = new Formula();
formula1.Text = "Foglio1!$B$1";
StringCache stringCache1 = new StringCache();
PointCount pointCount1 = new PointCount()
{
Val = (UInt32Value)1U
};
StringPoint stringPoint1 = new StringPoint()
{
Index = (UInt32Value)0U
};
NumericValue numericValue1 = new NumericValue();
numericValue1.Text = titoloSerie;
stringPoint1.Append(numericValue1);
stringCache1.Append(pointCount1);
stringCache1.Append(stringPoint1);
stringReference1.Append(formula1);
stringReference1.Append(stringCache1);
seriesText1.Append(stringReference1);
DataPoint dataPoint1 = new DataPoint();
Index index2 = new Index()
{
Val = (UInt32Value)2U
};
dataPoint1.Append(index2);
//################################i testi ####################################
categoryAxisData1 = new CategoryAxisData();
StringReference stringReference2 = new StringReference();
Formula formula2 = new Formula();
formula2.Text = string.Format("Foglio1!$A$2:$A${0}", valori.Count + 2);
StringCache stringCache2 = new StringCache();
UInt32Value nValori = Convert.ToUInt32(valori.Count);
PointCount pointCount2 = new PointCount()
{
Val = nValori
};
StringPoint[] stringPoints = new StringPoint[nValori];
UInt32Value n = 0;
foreach (KeyValuePair <string, double> item in valori)
{
stringPoints[n] = new StringPoint()
{
Index = (UInt32Value)n
};
NumericValue numericValue2 = new NumericValue();
numericValue2.Text = item.Key;
stringPoints[n].Append(numericValue2);
n += 1;
}
stringCache2.Append(pointCount2);
for (int i = 0; i < n; i++)
{
stringCache2.Append(stringPoints[i]);
}
stringReference2.Append(formula2);
stringReference2.Append(stringCache2);
categoryAxisData1.Append(stringReference2);
//################################i valori####################################
values1 = new Values();
NumberReference numberReference1 = new NumberReference();
Formula formula3 = new Formula();
formula3.Text = string.Format("Foglio1!$B$2:$B${0}", valori.Count + 2);
NumberingCache numberingCache1 = new NumberingCache();
FormatCode formatCode1 = new FormatCode();
formatCode1.Text = FormatoValori; //<-----------------------------------------------------------
PointCount pointCount3 = new PointCount()
{
Val = nValori
};
NumericPoint[] numericPoints = new NumericPoint[nValori];
n = 0;
foreach (KeyValuePair <string, double> item in valori)
{
numericPoints[n] = new NumericPoint()
{
Index = (UInt32Value)n
};
NumericValue numericValue = new NumericValue();
numericValue.Text = item.Value.ToString();
numericValue.Text = numericValue.Text.Replace(",", "."); // devo forzare il formato americano
numericPoints[n].Append(numericValue);
n += 1;
}
numberingCache1.Append(formatCode1);
numberingCache1.Append(pointCount3);
for (int i = 0; i < n; i++)
{
numberingCache1.Append(numericPoints[i]);
}
numberReference1.Append(formula3);
numberReference1.Append(numberingCache1);
values1.Append(numberReference1);
}
