英文:

How to Compare Aspose Cells to Files in NetWork Folder

问题

我是新手，正在寻求帮助，因为我一直在解决一个问题...

我被要求创建一个需要我将Aspose.Cells在**C#**中的一系列单元格与文件名的迭代进行比较的应用程序。如果它们不匹配，那么我必须将仅存在于网络文件夹中的文件名添加到Excel工作表中，使用Aspose。

我正在尝试比较来自工作簿的一系列单元格与来自网络文件夹的文件名列表（该列表不在工作簿中）。

我正在尝试做的是：

如果网络文件夹中的文件名列表（字符串，不是工作簿）
和
工作表单元格范围的值（工作簿）
不包含所有相同的值，
那么...将网络文件夹中不同的内容添加到工作表中。

我只是想知道是否可以按照我被要求的方式来做，如果可以的话...最好的方法是什么？

我对如何处理此事感到困惑，因为我尝试的一切都告诉我，当涉及条件语句时，我不能像我一样使用“Cells” - 其中包含整数和布尔值。

我对C#和Aspose.Cells都很陌生...所以感谢你们在帮助我弄清楚这个问题时的耐心

在尝试将单元格范围与来自网络文件夹的列表进行比较时，我尝试使用if语句，但似乎没有一种变化的方法能够奏效。任何帮助或见解将不胜感激。

网络文件夹列表的代码：

static void SSLSearch(string dir, string rootDir = null)
{
    if (rootDir == null)
    {
        rootDir = dir;
    }
    try
    {
        foreach (string f in Directory.GetFiles(dir))
        {
            if (f.Contains(".pfx"))
            {
                string filename = f.Substring(rootDir.Length);
                Console.WriteLine(filename);
            }
        }
    }
    catch (System.Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Aspose范围的代码：

TxtLoadOptions txtLoadOptions = new TxtLoadOptions();
txtLoadOptions.Encoding = System.Text.Encoding.UTF8;
txtLoadOptions.ConvertNumericData = false;
txtLoadOptions.ConvertDateTimeData = false;

LoadOptions loadOptions = new LoadOptions(LoadFormat.Auto);
loadOptions.LoadFilter.LoadDataFilterOptions = LoadDataFilterOptions.CellData;

Workbook wb = new Workbook(fileName, loadOptions);

// 获取工作簿中的所有工作表
WorksheetCollection collection = wb.Worksheets;

// 根据工作表名称获取工作表
Worksheet worksheet = collection["certs"];

// 过期日期范围
Range expRn = worksheet.Cells.CreateRange("F2:F33");
expRn.Name = "ExpireRange";

// 证书范围
Range certRn = worksheet.Cells.CreateRange("B2:B33");
certRn.Name = "CertRange";

// 访问两者的范围
Range range = wb.Worksheets.GetRangeByName("ExpireRange");
Range certRange = wb.Worksheets.GetRangeByName("CertRange");

// 迭代范围中的单元格，打印它们的名称和值
IEnumerator expE = range.GetEnumerator();
IEnumerator certE = certRange.GetEnumerator();

while (certE.MoveNext() && expE.MoveNext())
{
    Cell c = (Cell)expE.Current;
    Cell cert = (Cell)certE.Current;

    Console.WriteLine("Certificate" + ": " + cert.StringValue);
    Console.WriteLine("Expires" + ": " + c.StringValue);
    // 其他操作...
}

<details>
<summary>英文:</summary>

I am new here and looking for some help, as I’ve been stuck on an issue…

I’ve been tasked with creating an app that requires me to compare a range of aspose cells in ** c#** to an iteration of file names. if they don’t match, then I have to add said files name that only exist in the network folder, into the excel sheet using aspose.

I am trying to compare a range of cells that comes from a workbook, to a list of file names that pulls from a network folder (the list is not in a workbook).



What I am trying to do is:

if network folder file name list (strings, not a workbook)
and
worksheet cells range values (a workbook)
do not contain all the same values,
then… add what is different in the network folder, to the worksheet

I am just wanting to know if it is possible to do it in the way I have been asked, and if so.. what is the best way to go about it?

I’m lost on how to go about this, as everything I’ve tried has told me I cannot use “Cells” like I am - with values of int and bool when it comes to conditional statements.

I’m new to both C# and Aspose.Cells… so thank you for your patience in helping me get this figured out :)

I have tried using if statements when trying to compare the cell range to the list from the network folder, but no variation of that seems to work. Any help or insight would be appreciated.


Code for network folder lists:

static void SSLSearch(string dir, string rootDir = null)
{
if (rootDir == null)
{
rootDir = dir;
}
try
{
foreach (string f in Directory.GetFiles(dir))
{
if (f.Contains(".pfx"))
{
string filename = f.Substring(rootDir.Length);
Console.WriteLine(filename);
}
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}

Code for aspose range:

TxtLoadOptions txtLoadOptions = new TxtLoadOptions();
txtLoadOptions.Encoding = System.Text.Encoding.UTF8;
txtLoadOptions.ConvertNumericData = false;
txtLoadOptions.ConvertDateTimeData = false;

        LoadOptions loadOptions = new LoadOptions(LoadFormat.Auto);
        loadOptions.LoadFilter.LoadDataFilterOptions = LoadDataFilterOptions.CellData;

        Workbook wb = new Workbook(fileName, loadOptions);

        //get all sheets in workbook
        WorksheetCollection collection = wb.Worksheets;

        //get worksheet from sheet name
        Worksheet worksheet = collection["certs"];

        //range of expirations
        Range expRn = worksheet.Cells.CreateRange("F2:F33");
        expRn.Name = "ExpireRange";

        //range of certificates
        Range certRn = worksheet.Cells.CreateRange("B2:B33");
        certRn.Name = "CertRange";

        //access ranges for both
        Range range = wb.Worksheets.GetRangeByName("ExpireRange");
        Range certRange = wb.Worksheets.GetRangeByName("CertRange");

        //iterate cells in range, print their names and values
        IEnumerator expE = range.GetEnumerator();
        IEnumerator certE = certRange.GetEnumerator();

        while (certE.MoveNext() && expE.MoveNext())
        {
            Cell c = (Cell)expE.Current;
            Cell cert = (Cell)certE.Current;

            Console.WriteLine("Certificate" + ": " + cert.StringValue);
            Console.WriteLine("Expires" + ": " + c.StringValue);

....

</details>


# 答案1
**得分**: 0

有两种方法可以完成您的任务，您可以选择以下任何一种。

1) 您可以使用一些Excel公式来进行比较/匹配任务。例如，您可以尝试使用[XLOOKUP][1]和/或[MATCH][2] MS Excel的公式。您可以将来自自定义方法调用的每个单独字符串（来自网络文件夹列表的文件名）与工作表中的单元格范围（例如，您已经有A2:A10范围/区域）进行比较/匹配。这两个公式（XLOOKUP和MATCH）都受Aspose.Cells的公式计算引擎支持，因此您可以将公式输入到相应的单元格中，以了解哪些文件名（来自您的自定义列表）与工作表中的现有范围进行比较/匹配时匹配/不匹配。您只需将文件名（来自自定义方法调用（网络文件夹））插入到某些单元格中，然后相应地应用公式。您可以通过Workbook.CalculateFormula方法计算公式，并通过Aspose.Cells API在运行时获取结果/结果值。

2) 您可以使用[查找/搜索功能][3]执行字符串比较/匹配任务。您可以在工作表中已粘贴的特定单元格/区域中搜索每个数据/字符串（来自文件名列表）。 

附注：我在Aspose担任支持开发者/传道者的职务。

  [1]: https://support.microsoft.com/en-au/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
  [2]: https://support.microsoft.com/en-au/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a
  [3]: https://docs.aspose.com/cells/net/find-or-search-data/

<details>
<summary>英文:</summary>

There can be two approaches for your task, you may pick any of the following.

1) You may make use of some Excel formulas for comparison/matching task. For example, you may try [XLOOKUP][1] and/or [MATCH][2] MS Excel’s formula(s). You may compare each individual string (which is coming from your custom method call for network folder list (names of the files)) to compare/match with the range of cells (e.g., you already have A2:A10 range/area in a worksheet). Both formulas (XLOOKUP and MATCH) are supported by Aspose.Cells’ formula calculation engine, so you may input the formula(s) into respective cell(s) to know which file names (from your custom list) are matching/not matching when comparing with your existing range in the worksheet. All you need to do is insert the file names (from your custom method call (network folder)) into some cell(s) and then apply formulas accordingly. You can calculate formula(s) via Workbook.CalculateFormula method and get the results/resultant values at runtime by Aspose.Cells APIs.

2) You can perform the string comparison/matching task using [Find/Search feature][3]. You can search using each data/string (from your file names list) in the specific range of cells/area where you have already pasted in the worksheet. 

PS. I am working as Support developer/ Evangelist at Aspose.


  [1]: https://support.microsoft.com/en-au/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
  [2]: https://support.microsoft.com/en-au/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a
  [3]: https://docs.aspose.com/cells/net/find-or-search-data/

</details>