Excel Range in C# using interop.Excel. Range both empty and not empty?

I have a Excel sheet that looks like this. |A1|B1| "BLANK" |D1|E1|F1| I.e. only the first row is populated and the third column is blank. I parse this in C# using interop.excel in the following way:

Excel.Application exApp = OpenExcel(); String mySheet = @"C:\c#\rapport.xlsx"; Excel.Workbook wb = exApp.Workbooks.Open(mySheet); Excel.Worksheet ws = wb.Sheets[1]; Excel.Range row = ws.Rows[1];

I create a new range only containing the non-empty cells in row 1 by

Excel.Range rng = row.SpecialCells(Excel.XlCellType.xlCellTypeConstants);

Now: if I use rng.Select all the non-empty cells in my sheet is selected. And if I use rng.Count it will return 5, which is the number of non-empty cells in my sheet. But still when I print cell by cell using:

Console.WriteLine(rng[1,i].Value.ToString());

It shows that rng[1, 1-2] contains "A1 and B1", this is nice. But rng[1, 3] is empty or null. And rng[1, 4-7] contains D1-F1.

How is this possible?

My main goal is to store all non-empty values in a string array. But I can't select the right values because my range rng is both empty and non-empty is some weird way.

Answer1:

Try using:

ws.Columns.ClearFormats(); ws.Rows.ClearFormats();

to be sure your Range excludes formatted but empty cells.

Another try could be to get :

Excel.Range rng = row.SpecialCells(Excel.XlCellType.xlCellTypeConstants,Type.Missing);

Answer2:

This solved it! However I didn't use the .Item method to store the values as Rory suggested suggested. Thnx Rory!

string[] str = new String[rng.Count]; int i = 0; foreach (Excel.Range cell in rng.Cells) { str[i] = cell.Value.ToString(); i++; } for (int j = 0; j < str.Length; j++) Console.WriteLine(str[j]);

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Excel Range in C# using interop.Excel. Range both empty and not empty?