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