private List <string> GetCellStrValues(C.Formula formula, OpenXmlPart xlsxPackagePart) //EmbeddedPackagePart : OpenXmlPart { var exist = _spContext.PreSettings.XlsxDocuments.TryGetValue(xlsxPackagePart, out var xlsxDoc); if (!exist) { xlsxDoc = SpreadsheetDocument.Open(xlsxPackagePart.GetStream(), false); _spContext.PreSettings.XlsxDocuments.Add(xlsxPackagePart, xlsxDoc); } var filteredFormula = formula.Text.Replace("'", string.Empty, StringComparison.Ordinal) .Replace("$", string.Empty, StringComparison.Ordinal); //eg: Sheet1!$A$2:$A$5 -> Sheet1!A2:A5 var sheetNameAndCellsFormula = filteredFormula.Split('!'); //eg: Sheet1!A2:A5 -> ['Sheet1', 'A2:A5'] var wbPart = xlsxDoc.WorkbookPart; string sheetId = wbPart.Workbook.Descendants <Sheet>().First(s => sheetNameAndCellsFormula[0].Equals(s.Name, StringComparison.Ordinal)).Id; var wsPart = (WorksheetPart)wbPart.GetPartById(sheetId); var sdkCells = wsPart.Worksheet.Descendants <Cell>(); //TODO: use HashSet var addresses = new CellFormulaParser(sheetNameAndCellsFormula[1]).GetCellAddresses(); //eg: [1] = 'A2:A5' var strValues = new List <string>(addresses.Count); foreach (var address in addresses) { var sdkCellValueStr = sdkCells.First(c => c.CellReference == address).InnerText; strValues.Add(sdkCellValueStr); } xlsxDoc.Close(); return(strValues); }
public static IList <double> FromFormula(C.Formula formula, EmbeddedPackagePart embeddedPackagePart) { //TODO: caching embeddedPackagePart Check.NotNull(formula, nameof(formula)); Check.NotNull(embeddedPackagePart, nameof(embeddedPackagePart)); var filteredFormula = formula.Text.Replace("'", string.Empty, StringComparison.Ordinal) .Replace("$", string.Empty, StringComparison.Ordinal); var sheetNameAndCellsFormula = filteredFormula.Split('!'); var stream = embeddedPackagePart.GetStream(); var doc = SpreadsheetDocument.Open(stream, false); var wbPart = doc.WorkbookPart; string sheetId = wbPart.Workbook.Descendants <Sheet>().First(s => sheetNameAndCellsFormula[0].Equals(s.Name, StringComparison.Ordinal)).Id; var wsPart = (WorksheetPart)wbPart.GetPartById(sheetId); var sdkCells = wsPart.Worksheet.Descendants <Cell>(); //TODO: use HashSet var addresses = new CellFormulaParser(sheetNameAndCellsFormula[1]).GetCellAddresses(); var result = new List <double>(addresses.Count); foreach (var address in addresses) { var sdkCellValueStr = sdkCells.First(c => c.CellReference == address).InnerText.Replace(".", ",", StringComparison.Ordinal); sdkCellValueStr = sdkCellValueStr == string.Empty ? "0" : sdkCellValueStr; result.Add(double.Parse(sdkCellValueStr)); } doc.Close(); stream.Close(); return(result); }