How to add multiple tables in Excel using LightCellsDataProvider

Aspose.Cells Product Family
1

Hi! Amjad
I have a requirement, to print multiple tables containing millions of rows using LightCellDataProvider.
Is it possible to print multiple tables containing millions of rows and some text data on the top like the report title?

2

Also Team, Please let us know if there is any other better approach to render million rows of data apart from LightCellsDataProvider.
cc @Rohan_Wankar

3

@Rohan_Wankar, @Thilakbabu,

LightCells API is mainly used to insert data cell by cell into the worksheets (one sheet at a time) without creating the complete data model or streaming the whole data/contents. So, you may try it for your requirements, see the document with example codes for your reference if it gives you some help:

Please note, to import whole data from arrays, custom objects, list or datatable/database in one go or to render spreadsheet to PDF or images will not work in light weight mode, so you have to do it outside of the implementation of the class that implements the light cells interface, you got to perform these tasks in your main class and not in the class which implements light cells interface.

You may try to perform the task in normal mode (other than light cells mode). You may try to use MemorySetting.MemoryPreference option if you are only concerned about memory. Moreover, make sure you do not put everything in one sheet to make a big one, instead split your records/tables in different sheets, this might enhance the performance.

4

Thanks @Amjad_Sahi for the reply.

We still have below queries, please provide your thoughts.

  1. Say we have an excel wrokbook, in which we have a Reports worksheet. In the Reports worksheet we would like to add some texts like Title and couple of charts like bar and Pie charts at the beginning, which we will do it outside light cells interface. So after like 100 rows which contains the text and couple of charts, can we load the same Reports Worksheet using LightCells to start rendering the millions rows of data ?
  2. Our requirement is to get all the charts/text and millions row of data with in the same Reports worksheet. So please let us know if we can achieve using only LightCells interface or is there any other way where we can achieve it in an efficient way other than LightCells.

Thanks
Thilak

5

@Thilakbabu,

  1. You have to save the workbook first and then re-load in light weight mode. Reading bigger files in light weight mode would be useful if you need to read data in the worksheet(s) cell by cell sequentially and efficiently.
  2. There are two modes in Aspose.Cells: normal mode --> where you do your operations and other tasks without light cells APIs, light mode --> where you use light cells APIs in event driven mode (read/write data cell by cell in sequence). I think you may perform you task in normal mode.
6

Thanks @Amjad_Sahi

Reg. the first point, I can save the worbook after drawing chart and adding few text in a worksheet. I would like to open the same worksheet to add millions rows of data. In that case, I would like start the light cells to start writing the rows of data from 101st row without overriding the existing charts and text. Is it possible to write using the lightcells without overriding the existing content of the worksheet ?

Thanks,
Thilak

7

@Thilakbabu,

Checking your scenario/case which involves existing data and other drawing objects, I think you’d better use Aspose.Cells in normal mode. If you find serious performance issue, let us know with details and samples to reproduce the issue, we will check it soon.

8

@Thilakbabu,
Yes, it is possible for you to create other objects(such as Chart, Shape, …etc.) before saving the workbook, and fill cells data into worksheets while saving the workbook. If what you said of “few text” is those object such as textbox, they will never be modified while the saving process. If it is some text values of some cells, then while you implementing LightCellsDataProvider.StartCell(Cell), please check whether the cell has been initialized with the text you need to preserve, if yes then do not change it’s value.

If you get problem with this process, please provide us your example project so we can help to solve it.