使用原始值搜索数据

有时，数据的值因为以某种方式格式化而被隐藏。例如，假设单元格 D4 公式为 =Sum(A1:A2) 并且其值为 20 但格式为—，那么值 20 将被隐藏，并且无法使用 Microsoft Excel 查找选项找到。但是，您可以使用 Aspose.Cells 使用 LookInType.OriginalValues 找到它

以下示例代码说明了上述观点。它会找到无法使用Microsoft Excel的查找选项找到的单元格D4，但Aspose.Cells可以使用LookInType.OriginalValues找到它。请阅读代码内的注释以获取更多信息。

使用 Node.js 通过原始值搜索数据的代码示例

const AsposeCells = require("aspose.cells.node");
const path = require("path");

// The path to the documents directory.
const dataDir = path.join(__dirname, "data");

// Create workbook object
const workbook = new AsposeCells.Workbook();

// Access first worksheet
const worksheet = workbook.getWorksheets().get(0);

// Add 10 in cell A1 and A2
worksheet.getCells().get("A1").putValue(10);
worksheet.getCells().get("A2").putValue(10);

// Add Sum formula in cell D4 but customize it as ---
const cell = worksheet.getCells().get("D4");

let style = cell.getStyle();
style.setCustom("---");
cell.setStyle(style);

// The result of formula will be 20 but 20 will not be visible because the cell is formatted as ---
cell.setFormula("=Sum(A1:A2)");

// Calculate the workbook
workbook.calculateFormula();

// Create find options, we will search 20 using original values otherwise 20 will never be found because it is formatted as ---
const options = new AsposeCells.FindOptions();
options.setLookInType(AsposeCells.LookInType.OriginalValues);
options.setLookAtType(AsposeCells.LookAtType.EntireContent);

let foundCell = null;
const obj = 20;

// Find 20 which is Sum(A1:A2) and formatted as ---
foundCell = worksheet.getCells().find(obj, foundCell, options);

// Print the found cell
console.log(foundCell);

// Save the workbook
workbook.save(path.join(dataDir, "output_out.xlsx"));

示例代码生成的控制台输出

这是上面示例代码的控制台输出。

  

Aspose.Cells.Cell [ D4; ValueType : IsNumeric; Value : ---; Formula:=SUM(A1:A2)]