Select range in aspose

Question

Do you know an equivalent to VBA code:

Range(Selection, Selection.End(xlToRight)).Select 

In Aspose.Cells. It seems that its only possible to select the last cell in the entire row:

public Aspose.Cells.Cell EndCellInRow ( Int32 rowIndex )

Or the last cell on the right within a range:

public Aspose.Cells.Cell EndCellInRow ( Int32 startRow, Int32 endRow, Int32 startColumn, Int32 endColumn )

but then you must know more or less how big your table is going to be.

I found this from 2009: http://www.aspose.com/community/forums/permalink/196519/196405/showthread.aspx but that will not resolve my problem as I may have many tables in a sheet both horizontally and vertiacally. And I can't predict where they are going to be.

Edit1: Sorry if this is dumb question, but ctrl+shift+arrow is such a common operation that I can't believe it would be not implemented so I'm making sure I really have to re-invent the wheel.

Answer 1

Aspose.Cells provides the list of tables in a worksheet using property named 'Worksheet.ListObjects'. 'ListObjects' is a colloection of 'ListObject' type which represents a Table in an excel sheet. That means if one has more than one Tables in a worksheet, the ListObjects collection will give access to every table in the worksheet very conveniently. Each 'ListObject' in turn contains a property named 'DataRange' which specifies all the cells inside a Table. For the sake of convenience DataRange can be used for following operations on a Table:

1. To apply styles/formatting on the cells in Table
2. To get the data values
3. Merge or move the cells in Range
4. Export contents
5. To get enumerator to traverse through Table cells

To make selection of cells from DataRange, you can traverse using DataRange to get all the cells in a Row (This could also be done for a column)

Applying any operation on Table cells like after selecting cells using Ctrl+Shift+Arrow, could be performed using a workbook object as follows:

Workbook workbook = new Workbook(new FileStream("book1.xls", FileMode.Open));

if (workbook.Worksheets[0].ListObjects.Count > 0)
{
     foreach (ListObject table in workbook.Worksheets[0].ListObjects)
     {
          Style st = new Style();
          st.BackgroundColor = System.Drawing.Color.Aqua;
          st.ForegroundColor = System.Drawing.Color.Black;

          st.Font.Name = "Agency FB";
          st.Font.Size = 16;
          st.Font.Color = System.Drawing.Color.DarkRed;

          StyleFlag stFlag = new StyleFlag();
          stFlag.All = true;

          table.DataRange.ApplyStyle(st, stFlag);
     }
}

workbook.Save("output.xls");

There is also some worthy information available in Aspose docs about Table styles and applying formatting on a ListObject. For getting last Table cell in a certain row or column, I am sure this will help:

int iFirstRowIndex = table.DataRange.FirstRow;
int iFirstColumnIndex = table.DataRange.FirstColumn;

int iLastRowIndex = table.DataRange.RowCount + iFirstRowIndex;
int iLastColumnIndex = table.DataRange.ColumnCount + iFirstColumnIndex;

for (int rowIndex = 0; rowIndex < table.DataRange.RowCount; rowIndex++)
{
     //Get last cell in every row of table
     Cell cell = worksheet.Cells.EndCellInColumn(rowIndex + iFirstRowIndex, rowIndex + iFirstRowIndex, (short)iFirstColumnIndex, (short)(iLastColumnIndex - 1));

     //display cell value
     System.Console.WriteLine(cell.Value);
}