/// <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); } } }