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