- Spire.XLS.png (32.93 KiB) Viewed 17 times
An excel file with 2 name Range
1st:NameOfRange1D(2 dimension array cell)
2nd:NameOfRange2D (2 dimension array cell)
Formula:
Sheet("target").Cells("A12").Value2 =COUNTIF(INDEX(NameOfRange2D,MATCH(B9,NameOfRange1D,0),),B9)
--------------------------------------------
MS.net core 8
Spire.XLS 14.9.1
Run at Windows 11 , Visual Studio 2022 IIS Express
--------------------------------------------
- Code: Select all
public void Test2D()
{
string fileName = @"D:\Temp\NameOfRange2D_Test1.xlsx";
//OpenFile
Workbook newWorkbook = new Workbook();
newWorkbook.LoadFromFile(fileName, ExcelVersion.Version2016);
if (newWorkbook != null)
{
//CalculateAllValue of workbook
newWorkbook.CalculateAllValue();
//get Sheet
string srcSheetName = "Source";
string targetSheetName = "Target";
Worksheet? _srcSheet = newWorkbook.Worksheets[srcSheetName];
Worksheet? _targetSheet = newWorkbook.Worksheets[targetSheetName];
if (_srcSheet != null && _targetSheet != null) {
//CalculateAllValue of sheet
_srcSheet.CalculateAllValue();
_targetSheet.CalculateAllValue();
//Name Range
newWorkbook.NameRanges.GetByName("NameOfRange1D");
newWorkbook.NameRanges.GetByName("NameOfRange2D");
//Get Formula String
string posString = "A11";
var _cell = _targetSheet[posString];
//Get Cell Type
XlsWorksheet.TRangeValueType cellType = _srcSheet.GetCellType(_cell.Row, _cell.Column, false);
//Get Cell Formula Value,
//Formula "=INDEX(NameOfRange2D,MATCH(B9,NameOfRange1D,0),)"
string _currentFormula = _cell.Value;
var formulaResult = newWorkbook.CalculateFormulaValue(_currentFormula);
Console.WriteLine(formulaResult);
//Get Formula String
posString = "A12";
_cell = _targetSheet[posString];
//Get Cell Type
cellType = _srcSheet.GetCellType(_cell.Row, _cell.Column, false);
//Get Cell Formula Value,
//Formula "=INDEX(NameOfRange2D,MATCH(B9,NameOfRange1D,0),)"
_currentFormula = _cell.Value;
formulaResult = newWorkbook.CalculateFormulaValue(_currentFormula);
Console.WriteLine(formulaResult);
//formulaResult should be 1, but get 0
}
//關閉
newWorkbook.Dispose();
}
