/// <summary>
/// Creates a new named range with a name in the format [SHEET_NAME]_[NAME].
/// Any non-alphanumeric characters are replaced with an underscore.
/// If an existing named range exists with the same name then an underscore
/// followed by a number is added.
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="name">The name of the named range.</param>
/// <param name="columnIndex">Index of the column.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="columnCount">The number of columns in the named range.</param>
/// <param name="rowCount">The number of rows in the named range.</param>
public static void AddDefinedName(
this Workbook workbook,
string sheetName,
string name,
uint columnIndex,
uint rowIndex,
int columnCount,
int rowCount)
{
if (workbook.DefinedNames == null)
{
workbook.DefinedNames = new DefinedNames();
}
string formattedName = FormatName(workbook.DefinedNames, name);
string start = CellExtensions.GetCellFormula(columnIndex, rowIndex);
string finish = CellExtensions.GetCellFormula(
(uint)(columnIndex + columnCount - 1),
(uint)(rowIndex + rowCount - 1));
workbook.DefinedNames.Append(
new DefinedName()
{
Name = formattedName,
Text = string.Format("\'{0}\'!{1}:{2}", sheetName, start, finish)
});
}
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));
}
}
/// <summary>
/// Gets a cell if exists in the worksheet. Returns null if cell does not exist.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="columnIndex">Index of the column.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <returns></returns>
public static Cell GetCellIfExists(this SheetData sheetData, uint columnIndex, uint rowIndex)
{
Row row = GetRowIfExists(sheetData, rowIndex);
if (row != null)
{
string cellReference = CellExtensions.GetCellReference(columnIndex, rowIndex);
return(row.OfType <Cell>().FirstOrDefault(c => c.CellReference == cellReference));
}
return(null);
}
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);
}
}
}
