How to determine header row while using ClosedXML

Question

i have a small winforms application im working on and using ClosedXML to handle our excel files. Im trying to build the read logic in a way that no matter what row the headers are on, i can find that row and work with the data below that. Because our reports come from our enterprise reporting system, the files are not always the same in where they start with the data because the exports from our system appends the report filters and selections to the top x rows then below that it starts the data dump. So right now that only way i can get it to work is if i manually remove all those rows at the top and make the header row the first row.

Im looking for some assistance in how i can find the "header" row based on column names or any other method. I have already looked thru their wiki https://github.com/ClosedXML/ClosedXML/wiki but that only has mention of working with printing headers and footers..

Here is where i believe i need to focus my work, but unclear where to start:

    // Look for the first row used
    var firstRowUsed = ws.FirstRowUsed(); //{'Precision Calculator D'!A1:XFD1}
    //var firstRowUsed = "'Precision Calculator D'!A9:XFD9";

    // Narrow down the row so that it only includes the used part
    var udasRow = firstRowUsed.RowUsed(); //{'Precision Calculator D'!A10:A10}
    //var udasRow = "'Precision Calculator D'!A10:A10}";

    // Move to the next row (it now has the titles)
    udasRow = udasRow.RowBelow();

There are reports ive tried that have the header starting on row 5 and others that start on row 7 and so on, so there is no actual row that they will alays be on, so need to find a way to determine it automatically. is there anyway to determine the row that the column names are in? The columns will always be in the same order, so those i have determined.

So ran across this in a mention of closedXML and it def may help get me where i need to be, but unclear how to implement

var foundMonth = ws.Search("Month", System.Globalization.CompareOptions.OrdinalIgnoreCase);

Since it returns a IEnumerable there is a chance that there may be more than one cell with the value "Month" and in my file that im testing with, there is 2 rows that contain the word and not sure how i can determine in this case that i want the last cell it found if there are multiple.

Addressed the concern about the multiple cells returned, and can now determine which row the headers are on with the following:

    var foundMonth = ws.Search("Month", System.Globalization.CompareOptions.OrdinalIgnoreCase);
    var monthRow = foundMonth.Last().Address.ToString();

Still unclear how to implement this into the original code post above, so that the firstRowUsed is reflected correctly in this case would be A11:XFD11

CubanGT CubanGT · Accepted Answer · 2020-04-01T14:58:30

After exhausting search of ClosedXML and reading thru a number of other questions, i was able to find a solution. Below is the code that will help set the used range based on my current data structure within the file..

    var foundMonth = ws.Search("Month", System.Globalization.CompareOptions.OrdinalIgnoreCase);            
    var monthRow = foundMonth.Last().Address;  // A11
    var lastcell = ws.LastCellUsed().Address; // BC3950
    var rangeUsed = ws.Range(monthRow, lastcell);

Since i have no idea where my header row will be from file to file, im searching for my column header name in column A, since all the usable data is mostly numbers i can safely assume that in column A, the last found instance of the word "Month" is my header row. With that and the last cell used i am able to determine my data range as seen above. Although i still need to figure out how to replace my firstRowUsed logic to work the same way, this is a step closer to a final solution. Ill post back my findings on that one before i mark this question answered.

How to determine header row while using ClosedXML

2 Answers