private static string GetCellRef(uint rowIndex, uint columnIndex, bool absolute)
{
if (absolute)
{
return(string.Format("${0}${1}", CellExtensions.GetColumnLetter(columnIndex), rowIndex));
}
else
{
return(string.Format("{0}{1}", CellExtensions.GetColumnLetter(columnIndex), rowIndex));
}
}
public static void UpdateXYValueChartSeries(this OpenXmlCompositeElement series, string sheetName, uint column, uint rowOffset, uint rowCount)
{
if (series == null)
{
throw new ArgumentNullException("series");
}
if (!(series is ScatterChartSeries))
{
throw new InvalidOperationException("Only valid for series of type ScatterChartSeries");
}
var index = series.Descendants <Index>().FirstOrDefault();
if (index != null)
{
index.Val = (UInt32Value)column - 1;
}
var order = series.Descendants <Order>().FirstOrDefault();
if (order != null)
{
order.Val = (UInt32Value)column - 1;
}
// the series title, this is the name of the column header
var seriesText = series.Descendants <SeriesText>().FirstOrDefault();
if (seriesText != null)
{
seriesText.StringReference = new StringReference();
seriesText.StringReference.Formula = new Formula();
seriesText.StringReference.Formula.Text = string.Format("'{0}'!${1}${2}", sheetName, CellExtensions.GetColumnLetter(column + 1), rowOffset);
}
// set the formula for the category axis, currently always the 1st column of data
var xvalues = series.Descendants <XValues>().FirstOrDefault();
if (xvalues != null)
{
xvalues.StringReference = new StringReference();
xvalues.StringReference.Formula = new Formula();
xvalues.StringReference.Formula.Text = string.Format("'{0}'!$A${1}:$A${2}", sheetName, rowOffset + 1, rowCount + rowOffset);
}
var yvalues = series.Descendants <YValues>().FirstOrDefault();
if (yvalues != null)
{
yvalues.NumberReference = new NumberReference();
yvalues.NumberReference.Formula = new Formula();
yvalues.NumberReference.Formula.Text = string.Format("'{0}'!${1}${2}:${1}${3}", sheetName, CellExtensions.GetColumnLetter(column + 1), rowOffset + 1, rowCount + rowOffset);
}
}
public static void UpdateCategoryValueChartSeries(this OpenXmlCompositeElement series,
bool isRowSeries,
string sheetName,
uint seriesCount,
uint itemPosition,
uint xCount,
uint xOffset,
uint yCount,
uint yOffset)
{
if (series == null)
{
throw new ArgumentNullException("series");
}
if (!(series is BarChartSeries) && !(series is LineChartSeries))
{
throw new InvalidOperationException("Only valid for series of type BarChartSeries & LineChartSeries");
}
// Updates the supplied series Index and Order
var index = series.Descendants <Index>().FirstOrDefault();
if (index != null)
{
index.Val = (UInt32Value)seriesCount;
}
var order = series.Descendants <Order>().FirstOrDefault();
if (order != null)
{
order.Val = (UInt32Value)seriesCount;
}
// Set the SeriesText ('Legend Entries(Series)' in Excel).
// This is set to reference the column header in Excel and determines the Category.
var seriesText = series.Descendants <SeriesText>().FirstOrDefault();
if (seriesText != null)
{
seriesText.StringReference = new StringReference();
seriesText.StringReference.Formula = new Formula();
if (isRowSeries)
{
seriesText.StringReference.Formula.Text = string.Format("'{0}'!$A${1}", sheetName, itemPosition + 1);
}
else
{
// a column is a series
seriesText.StringReference.Formula.Text = string.Format("'{0}'!${1}${2}", sheetName, CellExtensions.GetColumnLetter(itemPosition + 1), yOffset);
}
}
// set the formula for the category axis, currently always the 1st column of data
var categoryAxisData = series.Descendants <CategoryAxisData>().FirstOrDefault();
if (categoryAxisData != null)
{
categoryAxisData.StringReference = new StringReference();
categoryAxisData.StringReference.Formula = new Formula();
if (isRowSeries)
{
// if row is the series, the category are the dynamic columns
// so for example B3:F3
categoryAxisData.StringReference.Formula.Text = string.Format("'{0}'!${1}${2}:${3}${2}", sheetName, CellExtensions.GetColumnLetter(xOffset + 1), yOffset, CellExtensions.GetColumnLetter(xCount + 1));
}
else
{
// if row isnt series, then its a category, the default behaviour
// so assuming 1st column is the category then formula is A4:A10 with 4 being the starting row in the sheet
categoryAxisData.StringReference.Formula.Text = string.Format("'{0}'!$A${1}:$A${2}", sheetName, yOffset + 1, yCount + yOffset);
}
}
var values = series.Descendants <Values>().FirstOrDefault();
if (values != null)
{
values.NumberReference = new NumberReference();
values.NumberReference.Formula = new Formula();
if (isRowSeries)
{
values.NumberReference.Formula.Text = string.Format("'{0}'!${1}${2}:${3}${2}", sheetName, CellExtensions.GetColumnLetter(xOffset + 1), itemPosition + 1, CellExtensions.GetColumnLetter(xCount + xOffset));
}
else
{
values.NumberReference.Formula.Text = string.Format("'{0}'!${1}${2}:${1}${3}", sheetName, CellExtensions.GetColumnLetter(itemPosition + 1), yOffset + 1, yCount + yOffset);
}
}
}
