ColCountDataOnly counts column with formatting only

FLX TMS FlexCel Studio for .NET
1

I have five columns of data (1-5). After deleting columns 6-20 (or so), ColCountDataOnly returns 5 as expected. If I change the font type in cell 1,6, then ColCountDataOnly returns 6. I understand this to be a bug. Can anyone confirm this? .Net Core 3.1.302; FlexCel 7.1.1; macOS Catalina 10.15.6 (19G73)

2

Hi,
It is not a bug. A cell with a different font is a blank cell, and counts as data, and this is the same as in Excel.
You can try it in VBA: Format the cell F1 as bold, then run this macro:

Sub Macro1()
    Range("a1").FormulaR1C1 = ActiveSheet.UsedRange.Address
End Sub

Here it returns $A$1:$F$1

There are many ways you can "count" columns, but we only have 2 public options:
ColCount: It is the max between the max cell with data (again, blank counts as data), and the max formatted column.

ColCountOnlyData: It is the max cell with data (including blanks): It doesn't include formatted columns.

There are many more that we use internally but don't expose: For example we have MaxPrintableCol which is the maximum cell that would be printed if you print the sheet. For a MaxPrintableCol, a yellow blank cell counts to the count, but a bold blank cell doesn't. (because a yellow blank cell is visible when you print, and a bold blank cell isn't). There are others, like it seems the one you are looking for here (MaxColOnlyDataWithoutBlanks) or MaxColOnlyDataWithoutBlanksAndZeros, and even more cases (should a cell with whitespace count as empty? Should we consider cells in hidden columns?). And you can multiply by 2 the options by including formulas: Should a formula which results in an empty cell be counted or not? It might depend in your case (for example, for a MaxPrintableColCount it shouldn't count, unless you have a setting to show blank formulas as 0, in which case the cell becomes printable)

In addition to all variants that you might want depending on what you want the MaxColumn to count, there is the extra problem that MaxColumn is a very slow method, because cells in FlexCel are stored by rows, and to count the max column (in any variant) means traversing all rows to find the max. So any MaxColumn... method is not a method we want to encourage to use, and that is another reason to not offer a MaxColumnVariant for every possible definition of "count"

Normally what you will want to use is ColCountInRow
See
https://doc.tmssoftware.com/flexcel/net/guides/performance-guide.html#avoid-calling-colcount

ColCountInRow will still count blank cells as having "data" (because they have it), but it is easy to ignore them. If you still need a special ColCount (and there are still cases where you might want one), you can define it:

int RowCount = xls.RowCount;
    int MaxCol = 0;
    for (int row = 1; row <= RowCount; row++)
    {
        int ColCountInRow = xls.ColCountInRow(row);
        for (int colIndex = ColCountInRow; colIndex >=1; colIndex--)
        {
            int XF = -1;
            object cellValue = xls.GetCellValueIndexed(row, colIndex, ref XF);
            if (c == null) continue; // you can add some other condition here like if it is a RichString and the richstring is NullOrWhiteSpace()
            
            int c = xls.ColFromIndex(row, colIndex));
            if (c > MaxCol) MaxCol = c;
            break;
        }
    }
    return MaxCol;

The code above is similar to what FlexCel does when you call ColCount or ColCountOnlyData, because it doesn’t have the max column stored, so it has to loop over all rows. So this code is no slower than what a built-in function would be.

3

Thanks for your thorough response. I had no idea that changing font (and other things) would result in the cell being a "blank cell" and therefore considered to "have data". I also understand that it's not practical to address all the permutations of possibilities in what to count.

I have found FlexCel incredibly useful over the years and the documentation very good. I recommend adding a variation of what you explained in the documentation for ColCountDataOnly, to help developers coming to the documentation from a context like mine: "I just changed the font type, and the documentation states that ColCountDataOnly doesn't include formatted columns."

Thanks again.

4

Thanks for the kind words and also for the suggestion! In fact, I did realize that the docs in ColCountDataOnly weren't enough, so I had already added some extra information this morning, and just now I am finishing writing a new tip explaining more in detail the situation. (with a link from ColCountDataOnly to the tip)