Compare column Values and count occurrences

Aspose.Cells Product Family
,
1

Good afternoon . Just creating a small algorithm to count the number of matched values within 2 columns and think i may follow a longer path than needed so just thought that might be a smoother way in Aspose to do that for sure. My code until now (also provided a one page document to aid you visualise the subtask to accomplish)
int maxRow = cellsApiVal.MaxDataRow;
CellArea areaApivalues = CellArea.CreateCellArea(1, 1, maxRow, 1);
Range rngApiId = cellsApiReport.CreateRange(“B6”, “B58”);

        // Algorithm to Lookup Col B from API Report ws tp Col B from apivalues
        FindOptions optsLookupB = new FindOptions();
        optsLookupB.LookAtType = LookAtType.EntireContent;
        optsLookupB.LookInType = LookInType.Values;
        optsLookupB.SetRange(areaApivalues);

        Cell iValue; Cell findRange; int countOccur = 0;
        for (int i = 0; i < rngApiId.CellCount; i++)
        {
            iValue = rngApiId[i, 0];
            findRange = wsApiValues.Cells.Find(iValue.Value, null, optsLookupB);
            if (findRange != null)
            {
                countOccur++;
                foreach (Cell cell in rngApiId)
                {
                      ...Here need some logic
                }
                //Debug.WriteLine(iValue.Name + "-" + findRange.Name);
            }
        }<a class="attachment" href="/uploads/default/45419">CompareColumnValuesAndCountOccurences.zip</a> (61.2 KB)
2

@Remus87,
You may share your input Excel file, expected output file and program output for our reference.

3

SampleProject.zip (41.6 KB)

I’ve created a console app with just the functionality required. You’ll find in the project folder 2 .ODS files: APITemplate (which currently outputs the program) and APITemp_EXPECTED OUTPUT (which needs to be outputted). It will match Count column from API Report worksheet with column B from apivalues worksheet and will add the count of occurrences of each element in Count column. In Listing column is just adding corresponding values from column A (apivalues worksheet)

4

@Remus87,
Thank you for providing data. We will provide our feedback soon.

5

Good afternoon! Please let me know when we have a solution. Thanks!

6

@Remus87,
You may try it by using the following sample code.

Workbook wb = new Workbook(@"APITemplate.ods");

Worksheet wsApiReport = wb.Worksheets["API_Report"];
Worksheet wsApiValues = wb.Worksheets["apivalues"];

Cells cellsApiReport = wsApiReport.Cells;
Cells cellsApiVal = wsApiValues.Cells;
var data = cellsApiVal.ExportArray(0, 1, 61, 1);
            
List<int> lsData = new List<int>();
foreach (var item in data)
    lsData.Add((int)item);
          
CellArea areaApivalues = CellArea.CreateCellArea(1, 1, cellsApiVal.MaxDataRow, 1);
Range rngApiId = cellsApiReport.CreateRange("B6", "B58");

for (int i = 0; i < rngApiId.CellCount; i++)
{
    int iValue = rngApiId[i, 0].IntValue;
    cellsApiReport[i + 5, 6].PutValue(lsData.Count(da => da == iValue));
}



wb.Save(@"APITemplate_20.9.11.ods");

If you find some other option using MS Excel, please share the details with us. We will assist you to achieve the same using Aspose.Cells.

7

Thank you for the solution! It solved part of my requirement, however on the same sample of project if you look at the output spreadsheet file you’ll see that on column H are inserted the values from the matched index of apivalues spreadsheet column A. (it is documented on the file sent)

8

@Remus87,
I am afraid that there is no direct method to perform this task using Aspose.Cells library. You may please device your own logic to implement desired functionality.

9

Thanks. I’ve found a solution to that. Just need that the records to come each on a new line in the same cell. So according to: New Line in Cells|Documentation. I’ve followed the example:
{
orderList += wsApiValues.Cells[item.Row, 0].Value + “\n”;
}
cellsApiReport[cellApiID.Row, 7].PutValue(orderList);
}
// Wrap text on column H
var stWrapText = wbAPIReport.CreateStyle(); stWrapText.IsTextWrapped = true;
colH.ApplyStyle(stWrapText, new StyleFlag() { WrapText = true});

And is not doing the records on new line. Is word wrapping but not what I want (a record on each new line) Any advise ?

10

@Remus87,
I have tried this sample and it is working fine like in each row, multi-line text is displayed in individual cell with proper line break. Please give it a try and share the feedback.

Workbook workbook = new Workbook(); // Creating a Workbook object

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells[2, 2].Value = "Use";
sheet.Cells[2, 2].Value += "\n with word wrap on to create a new line";

sheet.Cells[3, 2].Value = "Use";
sheet.Cells[3, 2].Value += "\n with word wrap on to create a new line";

sheet.Cells[4, 2].Value = "Use";
sheet.Cells[4, 2].Value += "\n with word wrap on to create a new line";

Style style = workbook.CreateStyle();

//Set Text Wrap property to true
style.IsTextWrapped = true;

//Set Cell's Style
sheet.Cells.Columns[2].ApplyStyle(style, new StyleFlag() { WrapText = true });

workbook.Save("test.xlsx");
11

It works fine for .Xlsx format, but for .ODS format (which I output) is not working.
I’ve tried with a simple text (“Hello\nWorld”), thought maybe is the data coming in, then using string interpolation, then using System.Environment NewLine object and still didn’t have a positive output with new line on .ODS

foreach (Cell item in matchingcell)
{
orderList += “\n” + wsApiValues.Cells[item.Row, 0].Value;
}

            cellsApiReport[cellApiID.Row, 7].PutValue($"{orderList}\n");
            cellsApiReport[cellApiID.Row, 8].PutValue($"\n {orderList}");
            cellsApiReport[cellApiID.Row, 9].PutValue("Hello \n World");
            cellsApiReport[cellApiID.Row, 10].PutValue("Hello" + System.Environment.NewLine + "World");

        // Wrap text on column H
        var stWrapText = wbAPIReport.CreateStyle(); stWrapText.IsTextWrapped = true;
        colH.ApplyStyle(stWrapText, new StyleFlag() { WrapText = true});
12

@Remus87,
We have observed the issue in output ODS file where wrap text does not take effect. This issue is logged in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-47781 - Wrap Text not working for ODS files

13

Thank you and waiting for update!

14

@Remus87,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

15

@Remus87,

Please try our latest version/fix: Aspose.Cells for .NET v20.12.1 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells20.12.1 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells20.12.1 For .Net4.0.Zip (5.5 MB)
Aspose.Cells20.12.1 For .NetStandard20.Zip (5.4 MB)

16

Problem solved. Works like a charm. Thank you!

17

@Remus87,

Good to know that your issue is resolved by the new fix. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

18

The issues you have found earlier (filed as CELLSNET-47781) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao