Reading only the unhidden columns

Help Studio
, ,
1

My source spreadsheet have hidden columns and rows in it. But I don’t want them to be included in the final data table.

Which activity or activity option(s) should I use to achieve the requirement?

2

Hi @redanime94

While reading the spreadsheet data, check the PreserveFormat property in the read range panel & observe whether it’s taking the hidden data or not.

Hope this helps,
Best Regards.

3

Hi,

Can you try the following sample?

image
image967×1799 51.2 KB 

workbook = New XLWorkbook(targetFile)
arrIsHidden = workbook.Worksheet("Sheet1").Columns.Select(Function(c) c.IsHidden).ToArray()

Then

dt = dt.DefaultView.ToTable(False,dt.Columns.Cast(Of DataColumn).Select(Function(dc) dc.ColumnName).Where(function(s,i) not arrIsHidden(i)).ToArray)

Sample20230601-3Lv2.zip (9.1 KB)

Note: the above assume target table start from ColumnA

Edit: modify argument of ToTable

Regards,

4

Hi Yoichi,

Thanks for that. It worked.

However I forgot to mention earlier that I have several sheets in the workbook that have the same settings (same number of columns including the hidden ones). How do I make it loop? And also I need to all the sheets into one DT.

Atm, I am able to get the worksheet names within the workbook and reads the range from all the sheets.

5

Hi,

Can you try the following sample using ForEachExcelSheet?

image
image1361×2508 185 KB

Sample20230601-3Lv3.zip (10.2 KB)

Regards,

6

I tried it with the actual spreadsheet and I am getting an error: Index was outside the bounds of the array.

7

@Yoichi here’s a screenshot to give you a fair idea on how the spreadsheet looks like:

image
image906×379 4.92 KB

  1. Structure of the sheets are the same all throughout
  2. Sheet names are named almost the same as the screenshot
  3. At the bottom part of the Net Amount, there is a total amount (this is something that should not be added when adding it to the DT)
8

Hi,

Can you check the following?

  1. Did you set your actual sheet name in the following expression?

    arrIsHidden = workbook.Worksheet("Sheet1").Columns.Select(Function(c) c.IsHidden).ToArray()

  2. Please set Start cell of ReadRange like Excel.Sheet(CurrentSheet.Name).Range("A2")

If the above still doesn’t work, can you share your workbook? It’s no problem if dummy data.

Regards,

1 Like
9

Hi @Yoichi

#1 - Yes I am using the actual sheet name.

#2 Issue seems to have disappeared. However the hidden column still appears. But they appear as “Column1”, “Column2”, etc for those columns that were previously hidden.

10

Hi Yoichi,

The suggested solution works. I just needed to delete the previous version of the file or create a new version of the output file and the issue disappeared.

Thanks.