C# Number of filled column return value 1

Question

I know there are a few approach to find the total number of filled rows and columns based on individual requirements. One of the approach I'm trying to use Cells.Find approach to find the number of filled rows and columns. I found one relevant post here: C# Excel : Correct way to get Rows and Columns count, this is exactly what I need. I copied the format and replaced the variables and worked fine. However the return value of lastUsedColumn is 1 instead of 4, whereas the lastUsedRow return 5 which is exactly correct. The file format for my Excel file is in .xls not .csv. I had been studying C# for a while now by reading online documents, but still couldn't figure why xlByColumns isn't working here, or I'm missing something..? Can anyone help to explain..? Thanks.

Example of data in my Excel(.xls)

0.02 | 1.352 | 2.447 | -3.9924

0.04 | 2.991 | 9.556 | 3.227

0.06 | -9.119 | 1.883 | 2.004

0.08 | 5.382 | -9.003 | 7.441

1.00 | -8.803 | - 6.443 | 7.210

*The symbol | represent the column, so there are a total of 4 columns and a total of 5 rows.

Excel.Application app = null; 
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
double lastUsedRow = 0;
double lastUsedColumn = 0;

string FFile = @"C:\Users\Student\Downloads\FFile.xls"; 
StreamWriter rowvalue = File.CreateText(@"C:\Users\Student\Downloads\rowvalue.xls"); 
StreamWriter colvalue = File.CreateText(@"C:\Users\Student\Downloads\colvalue.xls"); 

app = new Excel.Application();
app.Visible = false;
wb = app.Workbooks.Open(FFile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

ws = (Excel.Worksheet)wb.Worksheets[1];

object missV = System.Reflection.Missing.Value;

// Find the last real row
// Return result 5 (correct)
lastUsedRow = ws.Cells.Find("*", missV, missV, missV, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, true, missV).Row;

rowvalue.WriteLine(lastUsedRow);

// Find the last real column
// Return result 1 (wrong)
lastUsedColumn = ws.Cells.Find("*", missV, missV, missV, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious, false, true, missV).Column;

colvalue.WriteLine(lastUsedColumn);

Ive always used Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); or if i needed the values, read them all and used the array counts — BugFinder, Jan 08 '20 at 11:17
Why not use ws.UsedRange.Rows.Count and ws.UsedRange.Columns.Count ? — Mark PM, Jan 08 '20 at 11:23
Are you using text/csv-files saved with an .xls extension. What do you see when you open the file with Notepad? — GvS, Jan 08 '20 at 11:32
@BugFinder Thanks for your contributions by suggesting different methods, it might come in handy! — YPCor, Jan 09 '20 at 05:37
@MarkPM I know ws.UsedRange.Rows.Count and ws.UsedRange.Columns.Count is one of the methods. I'd like to try Cells.Find approach to get the number. Solved :) — YPCor, Jan 09 '20 at 05:44
@GvS I'm not using either of the files to save it as .xls extension. The data in the file is transferred from another .xls file by using Read/Write method — YPCor, Jan 09 '20 at 05:47

score 0 · Accepted Answer · answered Jan 08 '20 at 15:56

You are importing a text file into Excel, and Excel does not recognize the delimiters. Thereby reading the data from one line into a single column. The lines are converted to a rows, so thats why you get the correct number of rows.

Look at your .xls file (with Notepad) and see what delimiter character is used. Replace the 5 from the Open call to the correct number as described in the documentation.

Value Delimiter
1 Tabs
2 Commas
3 Spaces
4 Semicolons
5 Nothing
6 Custom character (see the Delimiter argument)

I have just figured it out just now! Yes this is the solutions, replacing the 5 with 1 give the correct answer. Thanks for helping out by the way, appreciate your time! :) — YPCor, Jan 09 '20 at 03:45

C# Number of filled column return value 1

1 Answers1