If Excel data exceeds its row limit which is more than 10Lakh then want to write remaining Datatable in next sheet of the same excel file

Help Studio
,
1

Hi Guys,

I need solution for the below use case -

If Excel data exceeds its row limit which is more than 10Lakh then want to write remaining Datatable in next sheet2 of the same excel file. And again if that sheet2 exceeds its row limit then append the data in next sheet3 and so on.

Thank You in advance ! :slight_smile:

2

Follow these steps:

  1. Use Read Range to read current capacity.
  2. Check the DataTable row count to be written.
  3. Do the math: Rows can be added = DataTable.RowCount - current capacity
  4. Use Write Range to write only Rows can be added.
  5. Remove the Rows can be added from Datatable
  6. Continue till end of the Datatable

Regards,
Ashok

3

Assign Activity:
TableList | List(Of DataTable) =

YourVeryBigDataTableVar.AsEnumerable.Chunk(ChunkSizeVar).Select(Function (x) x.CopytoDataTable).ToList

ChunkSizeVar is an int32 value controlling the segment size and has a max value of
grafik

So you can loop over TableList and write out the different DataTables to Excel

1 Like