C# Excel : Correct way to get Rows and Columns count

Question

I have problem with C# Interop Excel get valid range with big file

https://www.dropbox.com/s/betci638b1faw8g/Demo%20Training%20Data.xlsx?dl=0

This is my file but real size is 1000 but I got 49998

I used standard code

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(txtbTrainPath.Text);
Excel.Worksheet xlWorksheet = xlWorkbook.Worksheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;              
xlApp.Visible = true;
xlWorksheet.Columns[5].Delete();
xlWorksheet.Columns[3].Delete();              
rowCount = xlRange.Rows.Count;
colCount = xlRange.Columns.Count;

Only for this file is not work correctly, other files works well. Please help me to find what is the problem. How to resize worksheet for only valid size.

Answer 1

I would use this approach to get the Rows and Columns count which will return the result of the cells which are not empty.

// Find the last real row
lastUsedRow = worksheet.Cells.Find("*",System.Reflection.Missing.Value, 
                               System.Reflection.Missing.Value, System.Reflection.Missing.Value, 
                               Excel.XlSearchOrder.xlByRows,Excel.XlSearchDirection.xlPrevious, 
                               false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Row;

// Find the last real column
lastUsedColumn = worksheet.Cells.Find("*", System.Reflection.Missing.Value, 
                               System.Reflection.Missing.Value,System.Reflection.Missing.Value, 
                               Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlPrevious, 
                               false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Column;

Here is the complete code for your reference:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application xlApp     = null;
Excel.Workbook wb           = null;
Excel.Worksheet worksheet   = null;
int lastUsedRow             = 0;
int lastUsedColumn          = 0;
string srcFile              = @"Path to your XLSX file";

xlApp = new Excel.ApplicationClass();
xlApp.Visible = false;
wb = xlApp.Workbooks.Open(srcFile,
                               0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                               true, false, 0, true, false, false);

worksheet = (Excel.Worksheet)wb.Worksheets[1];
Excel.Range range

// Find the last real row
lastUsedRow = worksheet.Cells.Find("*",System.Reflection.Missing.Value, 
                               System.Reflection.Missing.Value, System.Reflection.Missing.Value, 
                               Excel.XlSearchOrder.xlByRows,Excel.XlSearchDirection.xlPrevious, 
                               false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Row;

// Find the last real column
lastUsedColumn = worksheet.Cells.Find("*", System.Reflection.Missing.Value, 
                               System.Reflection.Missing.Value,System.Reflection.Missing.Value, 
                               Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlPrevious, 
                               false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Column;

xlApp.Workbooks.Close();
xlApp.Quit();

Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(xlApp);

Answer 2

After downloading the excel file and taking a look at it, I found as I expected, formatting on row 49998 column C. The font was different, the number formatting was different and the font color was red. I simply deleted all the cells on row 49998 and this simply decreased the number of used range rows to 49997. You could play this guess which cells have formatting by deleting the last row then check again. I have a feeling ALL cells above row 49998 have some kind of different formatting.

To fix this one sheet to get the proper used range. You have two choices. 1) Select the WHOLE row starting at row 1001, then scroll down to row 49998. With the SHIFT key pressed, click on the WHOLE row 49998. With rows 1001 to 49998 selected, right click on the selection and select "Delete" and possibly, if it asks, shift the cells up. Or 2) copy the cells with data only and paste the cells into an new worksheet, delete the old worksheet and rename the new worksheet to the previous worksheet name. I hope this makes sense.