How to get range of last column excel (spreadsheetgear)

Question

I'm trying to get the total number of columns so that i can set my range from column C to N column. I'm trying to go through each column and pull out the data i need into an array until i reach the last column with data in it.

I tried the below which works for one row, but none of the others i have.

IRange strategyname = foratworkbook.Worksheets["Strategy"].Cells["C4"].CurrentRegion;

C           D           E 
9.040%      10.910%     6.050%
4.920%      5.510%      3.430%
53.030%     64.400%     36.030%

Any Ideas? Thanks

show a screen shot of your sheet data structure. which programming language you use? state it in question tags, please. — Kazimierz Jawor, Dec 05 '13 at 11:32
I've only put 3 columns just as an example. But it could be C:E or C:L .. — Marc Howard, Dec 05 '13 at 11:52
`.CurrentRegion` will work if your range is continuous, without any row of column brake. If you have such a break you need to refer to your range differently. Best option is to find `top-left cell` and `bottom-right cell` of your range and next you could use the following structure `.Range(cells(top-left cell), cells(bottom-right cell))`. There are a lot of other solutions and possible ways to get your correct range. — Kazimierz Jawor, Dec 05 '13 at 11:58
The range for the second piece of data is continuous but it seems to take data from the whole page instead of just that row. How am i supposed to know what the bottom right cell will be though? as it will always be expanding or contracting..? — Marc Howard, Dec 05 '13 at 12:14
to give you any additional support I need to know precisely how your data is organised- you still didn't answer my questions from comments. So, is there any certain starting point like C4? etc... quick tip for now- use `.End()` property according to [THIS](http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.end.aspx) — Kazimierz Jawor, Dec 05 '13 at 12:33
The data is organised in columns, so i need to take the data from column C row 4 then column D row 4 and so on? until it reaches a column which is null. So the range would then give me C4:Q4 for example. — Marc Howard, Dec 05 '13 at 13:41
in VBA you will use something like this (mix VBA-C# coding, just a kind of syntax): `...=foratworkbook.Worksheets["Strategy"].Range(Cells["C4"], foratworkbook.Worksheets["Strategy"].Cells["C4"].End(xltoRight));`. Try to adjust to your needs on your own. — Kazimierz Jawor, Dec 05 '13 at 14:05
Thanks for the help. Only thing is i'm using the spreadsheet gear framework so that syntax doesn't fit. i'll try to work around it.. — Marc Howard, Dec 05 '13 at 14:56

score 2 · Accepted Answer · answered Dec 06 '13 at 00:42

If you know the maximum size of the array you can pull the entire table A:N x row nums into C# as a DataTable, find the first null column and first null row and then remove the columns and rows that are null from that DataTable. That is a reasonably quick and straightforward procedure.

These functions should return the first null row and column in a datatable

   public static int findNullRow(DataTable dt)
   {
       int row = 0;

       for (int a = 0; a < dt.Rows.Count; a++)
       {
           if (dt.Rows[a][0] == null)
           {
               row = a;
               break;
           }
       }

       return row;
   }

   public static int findNullColumn(DataTable dt)
   {
       int col = 0;

       for (int i = 0; i < dt.Columns.Count; i++)
       {
           if (dt.Rows[0][i] == null)
           {
               col = i;
               break;
           }
       }

       return col;
   }

The alternative is work iteratively across the first row of the spreadsheet pulling out the individual cell values and testing to find the first null column. Do the same for the first column of data until you find the first null row.

My feeling is that the first method would be quicker unless the potential number of rows is very large.

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

Following links will Help You for reading the data up to last edited range. SpreadSheetGear - read unique values from Excel column and Get the number of rows of data with SpreadSheetGear?

edited May 23 '17 at 12:06

Community

1
1

answered Oct 24 '14 at 07:08

Ammar

152
2
12

1

Ammar right, use ```sheet.UsedRange.Columns.Count``` and ```sheet.UsedRange.Rows.Count``` to get actual data. – Anton May 30 '15 at 11:51

How to get range of last column excel (spreadsheetgear)

2 Answers2