How to get the last column header and the data of last row value from the excel sheet

Help Studio
, ,
1

I have to get the last column header and last row count and use delete range to delete the values in Excel.

I have got the last row value count using the below.
valueDT.Rows.count

When i tried to get the column header (A, B, C, D…I), it returns the count as “9” instead of “I”
I used this “valueDT.Columns.Count”

Please explain me how to get column header and use delete range to delete the cell value.
And also would like to know how to delete the entire sheet data?

2

Hi

Let’s go one by one

  1. For this

ValueDT.Columns(ValueDT.Columns.Count-1).ColumnName.ToString

Will give the column name

  1. For this

To get the row count

ValueDT.Rows.Count

Count usually starts from 1

If you want to know the last row index that is index position
Then it is -1 of count
Reason is Index start value is 0

So it’s like this

ValueDT.Rows.Count-1 Will give the last row index position

  1. And finallye to delete the data from sheet we have a look on this thread
    It has a solution

Cheers @Subha_Sundara_moorthi

1 Like
3

Hi,

Can you try to use UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter method?

For example, UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(9) returns “I” as the following.

img20211023-3

So, we can get address of right-bottom corner as the following expression.

 UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(valueDT.Columns.Count)+valueDT.Rows.count.ToString()

(Assumed there in no header in table. If there is table header and you read it with AddHeader option, row number need to be added 1)

If you want to delete all cell of table and table starts from “A1”, the range string is

 "A1:"+UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(valueDT.Columns.Count)+valueDT.Rows.count.ToString()

Hope this helps you.

Regards,

1 Like
4

I need the Column index as "A/B/C/D/… " or say whatever.

ValueDT.Columns(ValueDT.Columns.Count-1).ColumnName.ToString
The above returns the result as Column8.

I have the data in excel say 27 rows (including headers). To get the last row count, i will use valueDT.Rows.Count
I’m not clear with the -1 of count (last row index position). When i use this ValueDT.Rows.Count-1
It returns 26

5

This is total no of rows in a datatable
That’s correct

I mentioned that in addition for a knowledge that in order to get the last row index we need to include -1 as
index starts from 0 for first row
Count starts from 1 for first row

And for this

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(ValueDT.Columns.Count-1)

Even here if you see we are passing column index and that’s why we are including -1

Hope this clarifies
Cheers @Subha_Sundara_moorthi

1 Like
6

Thank you both :slight_smile:

1 Like
7

Hi

How can i achieve to print the last row of last column value in output panel.

I tried this,
Excel_Datatable.Rows(Excel_Datatable.Rows.Count-1)(Get_Column).ToString

where Excel_Datatable is the datatable name and Get_Column returns right bottom cornor value UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(Excel_Datatable.Columns.Count)+Excel_Datatable.Rows.Count.ToString

8

Hi,

Can you try the following?

Excel_Datatable.Rows(Excel_Datatable.Rows.Count-1)(Excel_Datatable.Columns.Count-1).ToString

Regards,

1 Like
9

Hi

We can try like this

Excel_Datatable.Rows(Excel_Datatable.Rows.Count-1)(Excel_Datatable.Columns.Count-1).ToString

Description

Excel_Datatable = datatable name

Excel_Datatable = getting the value with Rows

For Rows method

Rows(row index)(column index) = row index and column index both starts from 0
That’s why

Excel_Datatable.Rows.Count-1 = gives the last row index

Excel_Database.Columns.Count-1 = gives the last column index

Hope this clarifies the query

Cheers @Subha_Sundara_moorthi

1 Like
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.