Excel, Data table

datatable
excel
activities

#1

How to check whether an excel sheet is empty or not ?


#2

HI @vandanaYadav,
Try this way,

use read cell activity to get the value “A1”
If its is empty you can assume sheet is empty
if not empty you have the data.

but if suppose data start from any other position you will get issue

Regards,
Arivu


#3

@vandanaYadav

Read the excel sheet and store it in a datatable dt.
Then use OutputDataTable Activity and store dt in stra
Then check by using string.IsNullorEmpty(stra)

If its true the excel sheet is empty else it will have some data

Regards,
Mahesh


#4

@MAHESH1,

i think so not sure
If it is empty sheet if will follow your step you will get stra value as “Column1 Column1”

Try some other way.

Regards,
Arivu


#5

@vandanaYadav

(From p in dt.Select
where not string.IsNullOrEmpty(string.Join("",p.ItemArray))
Select p).ToList.Count>0

If the count is greater than zero the datatable is not empty else its empty

Regards,
Mahesh


#6

Can you please provide me a sample xaml file


#7

I tried this , but even though the excel sheet is empty it is giving output as data table is not empty


#8

Hi @vandanaYadav,

1.
use read cell activity to get the value “A1”
If its is empty you can assume sheet is empty
if not empty you have the data.

2.
Read the excel sheet and store it in a datatable dt.
Then use OutputDataTable Activity and store dt in stra
Then check by using stra fields is “Column1 Column1”
you can identify the sheet is empty

Regards,
Arivu


#10

Hi @vandanaYadav,

TEST.xaml (7.1 KB)

I have attached a workflow, please try this way. It is working.
Let me know whether it is working for you.

Best regards,
Avi


#11

Sub CheckSheetEmpty()
'UpdatebyExtendoffice20170830
If WorksheetFunction.CountA(ActiveSheet.UsedRange) = 0 And ActiveSheet.Shapes.Count = 0 Then
MsgBox “Sheet is empty”, vbInformation, “Kuol For Excel”
Else
MsgBox “Sheet is not empty”, vbInformation, “Kuol For Excel”
End If
End Sub


#12

Hi Avi7

i tried this but the value of DT.Rows.Count is coming as 1 although the sheet is empty.


#13

It might have space in some strarting cells, try once after deleting all cell by select all and the press delete in excel sheet.


#14

Hi Avi7,

Tried that too but still the count for DT.Rows.Count is coming as 1.


#15

HI @vandanaYadav,

Refer this one.
CheckSheetIsEmpty.xaml (12.0 KB)
Test1.xlsx (7.2 KB)

Regards,
Arivu


Identify the Excel Sheet is Empty
#16

I am working on two excel files, both have read however I am not able to get the output in another excel. Please help


#17

Hi arivu96,

Thanks , this is working as expected