/// <summary>
/// Gets cell values.
/// </summary>
/// <param name="cFormula">
/// Cell range formula (c:f).
/// <c:cat>
/// <c:strRef>
/// <c:f>
/// Sheet1!$A$2:$A$3
/// </c:f>
/// </c:strRef>
/// </c:cat>
/// </param>
/// <param name="slideChart"></param>
internal static List <X.Cell> GetXCellsByFormula(C.Formula cFormula, SlideChart slideChart)
{
// Get all <x:c> elements of formula sheet
string filteredFormula = GetFilteredFormula(cFormula);
string[] sheetNameAndCellsRange = filteredFormula.Split('!'); //eg: Sheet1!A2:A5 -> ['Sheet1', 'A2:A5']
WorkbookPart workbookPart = slideChart.ChartWorkbook.WorkbookPart;
string chartSheetName = sheetNameAndCellsRange[0];
string chartSheetId = workbookPart.Workbook.Sheets.Elements <X.Sheet>()
.First(xSheet => xSheet.Name == chartSheetName).Id;
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(chartSheetId);
IEnumerable <X.Cell> allXCells = worksheetPart.Worksheet.GetFirstChild <X.SheetData>().ChildElements
.SelectMany(e => e.Elements <X.Cell>()); //TODO: use HashSet
List <string> formulaCellAddressList = new CellFormulaParser(sheetNameAndCellsRange[1]).GetCellAddresses();
var xCells = new List <X.Cell>(formulaCellAddressList.Count);
foreach (string address in formulaCellAddressList)
{
X.Cell xCell = allXCells.First(xCell => xCell.CellReference == address);
xCells.Add(xCell);
}
return(xCells);
}
internal static IReadOnlyList <double> GetNumbersFromCacheOrSpreadsheet(C.NumberReference numberReference,
SlideChart slideChart)
{
if (numberReference.NumberingCache != null)
{
// From cache
IEnumerable <C.NumericValue> cNumericValues =
numberReference.NumberingCache.Descendants <C.NumericValue>();
var pointValues = new List <double>(cNumericValues.Count());
foreach (var numericValue in cNumericValues)
{
var number = double.Parse(numericValue.InnerText, CultureInfo.InvariantCulture.NumberFormat);
var roundNumber = Math.Round(number, 1);
pointValues.Add(roundNumber);
}
return(pointValues);
}
// From Spreadsheet
List <X.Cell> xCells = GetXCellsByFormula(numberReference.Formula, slideChart);
var cellNumberValues = new List <double>(xCells.Count); // TODO: consider allocate on stack
foreach (X.Cell xCell in xCells)
{
string cellValueStr = xCell.InnerText;
cellValueStr = cellValueStr.Length == 0 ? "0" : cellValueStr;
cellNumberValues.Add(double.Parse(cellValueStr, CultureInfo.InvariantCulture.NumberFormat));
}
return(cellNumberValues);
}
internal static string GetSingleString(C.StringReference stringReference, SlideChart slideChart)
{
string fromCache = stringReference.StringCache?.GetFirstChild <C.StringPoint>().Single().InnerText;
if (fromCache != null)
{
return(fromCache);
}
List <X.Cell> xCell = GetXCellsByFormula(stringReference.Formula, slideChart);
return(xCell.Single().InnerText);
}
internal static CategoryCollection Create(
SlideChart slideChart,
OpenXmlElement firstChartSeries,
ChartType chartType)
{
if (chartType == ChartType.BubbleChart || chartType == ChartType.ScatterChart)
{
return(null);
}
var categoryList = new List <Category>();
// Get category data from the first series.
// Actually, it can be any series since all chart series contain the same categories.
// <c:cat>
// <c:strRef>
// <c:f>Sheet1!$A$2:$A$3</c:f>
// <c:strCache>
// <c:ptCount val="2"/>
// <c:pt idx="0">
// <c:v>Category 1</c:v>
// </c:pt>
// <c:pt idx="1">
// <c:v>Category 2</c:v>
// </c:pt>
// </c:strCache>
// </c:strRef>
// </c:cat>
C.CategoryAxisData cCatAxisData = firstChartSeries.GetFirstChild <C.CategoryAxisData>();
C.MultiLevelStringReference cMultiLvlStringRef = cCatAxisData.MultiLevelStringReference;
if (cMultiLvlStringRef != null) // is it chart with multi-level category?
{
categoryList = GetMultiCategories(cMultiLvlStringRef);
}
else
{
C.Formula cFormula;
IEnumerable <C.NumericValue> cachedValues; // C.NumericValue (<c:v>) can store string value
C.NumberReference cNumReference = cCatAxisData.NumberReference;
C.StringReference cStrReference = cCatAxisData.StringReference;
if (cNumReference != null)
{
cFormula = cNumReference.Formula;
cachedValues = cNumReference.NumberingCache.Descendants <C.NumericValue>();
}
else
{
cFormula = cStrReference.Formula;
cachedValues = cStrReference.StringCache.Descendants <C.NumericValue>();
}
int xCellIdx = 0;
var xCells = new ResettableLazy <List <X.Cell> >(() =>
ChartReferencesParser.GetXCellsByFormula(cFormula, slideChart));
foreach (C.NumericValue cachedValue in cachedValues)
{
categoryList.Add(new Category(xCells, xCellIdx++, cachedValue));
}
}
return(new CategoryCollection(categoryList));
}
