CODEWITHSUNDEEP
c#.netexcelspreadsheetgear
JDo
JDo

Reputation: 358

How to get actual columns range Spreadsheetgear

I have an Excel worksheet which has 50 Rows and 38 columns "Filled" with actual data but when I try to get the .UsedRange I am getting More than 1025 Rows and 250 columns as Used range! Please let me know how can I get only actual Used ranges with filled data and retrive the actual range data using Spreadsheetgear lib? I tried something like, but when user added some column, it's does not work.

var workbook = Factory.GetWorkbook(filePath);
var worksheet = workbook.Worksheets[0];
var cells = worksheet.UsedRange;

var headerCount = 0;

// get columns size
for (var columnCount = 0; columnCount <= range.ColumnCount; ++columnCount)
{
    if (columnCount > headers.Length && string.IsNullOrEmpty(range[HeaderRow, columnCount].Text))
    {
        headerCount = columnCount - 1;
        break;
    }
}

Upvotes: 1

Views: 1365

Answers (1)

Tim Andersen
Tim Andersen

Reputation: 3184

SpreadsheetGear (and Excel itself) will include cells into the UsedRange that are empty but have formatting of some kind. For instance, if cell ABC123 has a custom NumberFormat, Font color or similar, the UsedRange will include that cell, thereby potentially blowing up your UsedRange A1:ABC123 even if the actual "value-populated" or "filled" portion of the worksheet is much smaller.

If this is a problem and you need to only include the portion of a sheet that is actually populated with cell values, below is one possible routine you might be able to use to only include cells that have cell values of some kind. It is written as an extension method off of IWorksheet to make utilization of it easier, and includes a bool flag that you can pass in so as to return either the "normal" UsedRange (including cells with other types of formatting) or the "altered" UsedRange discussed here:

public static class SGExtensionMethods
{
    public static IRange GetUsedRange(this IWorksheet worksheet, bool ignoreEmptyCells)
    {
        IRange usedRange = worksheet.UsedRange;
        if (!ignoreEmptyCells)
            return usedRange;

        // Find last row in used range with a cell containing data.
        IRange foundCell = usedRange.Find("*", usedRange[0, 0], FindLookIn.Formulas,
            LookAt.Part, SearchOrder.ByRows, SearchDirection.Previous, false);
        int lastRow = foundCell?.Row ?? 0;

        // Find last column in used range with a cell containing data.
        foundCell = usedRange.Find("*", usedRange[0, 0], FindLookIn.Formulas,
            LookAt.Part, SearchOrder.ByColumns, SearchDirection.Previous, false);
        int lastCol = foundCell?.Column ?? 0;

        // Return a new used range that clips of any empty rows/cols.
        return worksheet.Cells[worksheet.UsedRange.Row, worksheet.UsedRange.Column, lastRow, lastCol];
    }
}

A couple additional notes about this approach. It does generally take into account hidden rows or columns--meaning hidden rows or columns that have cell values will be included in the UsedRange. One exception / edge-case to this inclusion of hidden rows or columns is if AutoFilters is enabled on the worksheet. This puts the worksheet in a special "mode" that excludes hidden rows from search results. So if AutoFilters is enabled (in such cases IWorksheet.AutoFilterMode will be true), you may not be able to rely on this approach if the last row(s) or column(s) of the AutoFiltered range could possibly have been filtered out.

Upvotes: 2

Related Questions