How to Select all the cells in a worksheet in Excel.Range object of c#?

I am trying to select all the cells in an Excel sheet in the Excel.Range object of C# for applying auto fit, border etc. I have some merged cells within the开发者_如何学运维 sheets.

Is there any simple trick to do so?

Excel.Range theRange = (Excel.Range)CurrentSheet.UsedRange;

In this example, CurrentSheet is the variable where you have stored the sheet you are currently using.

public void refreshSheetColumsSize(Worksheet ws)
 {
    ws.get_Range("a1").EntireRow.EntireColumn.Select();         
 }

Taken from here, this will select all cells in the worksheet:

lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow, lastCol)).Select

Officially, Excel.Worksheet.UsedRange.Rows and Excel.Worksheet.UsedRange.Columns.

In practice, it's buggy, you have to subtract the start row and column. The closest-to-correct answer is:

   Public ReadOnly Property LastColumn() As Integer
        Get
            Return ExcelWorksheet.UsedRange.Columns.Count + _
                   ExcelWorksheet.UsedRange.Column - 1
        End Get
    End Property
    Public ReadOnly Property LastRow() As Integer
        Get
            Return ExcelWorksheet.UsedRange.Rows.Count + _
                   ExcelWorksheet.UsedRange.Row - 1
        End Get
    End Property

This returns at least all the used cells, sometimes a little more. The 'little more' can be due to blank cells (rather than empty), and other random things. Form the research I did, this is the best that can be done.

If you really want to select everything then

ExcelWorksheet.Activate()
ExcelWorksheet.Cells.Select()

I've not done any excel development for a while (Excel 2003) but I always found that recording a macro while performing the tasks I was wanting to implement in code have sufficient pointers to help.

In this case, selecting all cells and autofitting gives the code:

Sub Macro1()
    Cells.Select
    Cells.EntireColumn.AutoFit
End Sub

which I would imagine would roughly translate to:

((Microsoft.Office.Interop.Excel.Range)_sheet.Cells.Select()).AutoFit();

where _sheet is the instance of the Worksheet you're using. (untested)

To consider all cells of a worksheet you can write like:

workSheet.Cells[workSheet.Rows.Count,workSheet.Columns.Count]

OR

 workSheet.get_Range("A1","IV65536")

To consider used cells of a worksheet, you can write:

workSheet.Rows.SpecialCells(XlCellType.xlCellTypeLastCell, XlSpecialCellsValue.xlTextValues)

Where "worksheet" represents the sheet you are working on. Both code sample returns a range.

Hope it helps!

Much cleaner and doesn't depend on the number of rows/cols which were increased in Excel 2007:

Provided your sheet is in a variable called wsData:

wsData.Range(wsData.Cells(1, 1), wsData.Cells(wsData.Rows.Count, wsData.Columns.Count))

Very simple:

xlWorkSheet.UsedRange.Columns.Select()

worksheet.Columns.AutoFit()

Where "worksheet" is a variable of type Worksheet

        xlWorksheet.get_Range("a1").EntireRow.EntireColumn.AutoFit();
        xlWorksheet.get_Range("a1").EntireColumn.EntireRow.AutoFit();

After cell filling has been finished.