How to Compare Aspose Cells to Files in NetWork Folder

Question

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

Answer 1

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 and/or MATCH 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. 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.