Write Range

excel

#1

When I clone a data table, the new Data table will just have column header. When I write the new Data table to excel file (with Add headers Checked), the sheet is empty.I was expecting it to have header row. What is the reason? Is it the activity behavior which expects atleast 1 row or am I missing something?


#2

Not sure why its behaving like this.
Here are the scenario i tried. Someone can elaborate on this.

Read cell : add header(checked)(sheet1)
Write Cell :add header(Checked)(sheet2)
Result : No header in sheet 2.

Read cell : add header(checked)(sheet1)
Write Cell :add header(unChecked)(sheet2)
Result : No header in sheet 2.


Read cell : add header(unchecked)(sheet1)
Write Cell :add header(Checked)(sheet2)
Result : added default Column0 Column1 with (header1,header2 )in sheet 2.

Read cell : add header(unchecked)(sheet1)
Write Cell :add header(unChecked)(sheet2)
Result : added header in sheet 2.


#3

Thanks Dilip. Are you cloning the datatable (dt2=dt1.Clone), because last 2 options doesn’t work for me either (its Write Range, not Write Cell)


#4

No Vinay. I’m just passing the same Data Table to sheet2. :slight_smile:


#5

It would help if we could see the source code, but I’m suspecting a dt.Rows.Count > 0 check somewhere. Probably added in good faith (why fire up excel integration if DT is empty), without anticipating that writing just the headers and then using a looped append range is a valid use case, so it should check for something like if (!addHeaders && dt.Rows.Count == 0) return;.

@qateam ?


#6

In my case lets say I want to create dynamic excel templates (25+ columns) for the robot/human data entry (can’t say who works on it first)


#7

Hmm… There is a couple of ways:

  1. You could just iterate the column collection, get .ColumnName and WriteCell with moving index. But that will be slow (it saves after each write).
    or,

  2. You could get the column names as a string[]:
    cloneDT.Columns.OfType(Of DataColumn).Select(Of String)(Function(x As DataColumn) x.ColumnName).ToArray
    Create a new DT and loop an AddColumn (all strings, same column count), then add the array as a row (AddDataRow -> ArrayRow), then WriteRange without AddHeaders. If the clonedDT s already string-column only, you don’t need a new one of course.

Probably some other ways as well, but I have to run do some actual work for now :wink:


#8

Thanks for the help @andrzej.kniola . I have coded something similar to #2 for now.