Export excel on the fly

Aspose.Cells Product Family
1

Hi,


I am new to Aspose, so please guide me how I can use Aspose for my requirement explained below.

My application is REST based. We are using javax.ws.rs.* library. When user clicks ‘Export All Data’ button in UI, we need to download the data in Excel format. The data can be millions. So in the back-end java server code, we batch the data retrieval from db. For each batch, we need to append the data to excel. At the same time, we need to stream the excel that is generated to the user.

So build the file and send it to the user on the fly. I guess it’s possible using Aspose. Please share any link or example which I can refer for the same.

Thanks,
Sindhu
2

Hi,

Thanks for your posting and considering Aspose.Cells.

You can retrieve your data from your database into a resultset object and then import the date into worksheet from your resultset object. You can also save your workbook object into memory stream like byte stream and then send that stream as response stream from your webpage.

Please see the following documentation article that explains how to import the date into worksheet.

( Import and Export Data|Documentation )

3

Hi,


I am trying the options you suggested, also this API has been used in my company by different team. They are also helping me.

I have one quick question,
In my case, I cannot predict the number of sheets I am going to write at first. So can I create a new sheet when MAX_ROWS in the sheet limit has been reached ?

Thanks,
Sindhu
4

Hi Sindhu,

Thanks for your posting and considering Aspose.Cells.

Please use XLSX format, it supports 1 million records in a sheet i.e 1048576 rows.

You can add any number of worksheets using the following code. Please also see the article relating to managing worksheets for your reference.

( Manage Worksheets|Documentation )

Java
//Create workbook in XLSX format
Workbook wb = new Workbook(FileFormatType.XLSX);

int shtIdx = wb.getWorksheets().add();
Worksheet sheet = wb.getWorksheets().get(shtIdx);

5

Thanks for the reply!


Yes, I tried what you mentioned and it works :slight_smile:

Now my question is about license, My company Moodys already purchased the license, So I am using the version 8.0.0 that is in our repository but when I am generating excel, it’s having the “Evaluation Copy” water mark. I think I am NOT handling the license correctly. Should I manage in manage in java program ? If so, how. Please guide me

Sindhu
6
Hi,

Do you set your license in your code/ program before using any other APIs of the product? Please make sure that your licensing code should run at least one time in the whole life cycle of your application/ program. It looks like your licensing code does not process at all. You should place the licensing code at a place where it should be processed first and fine before using any other APIs of Aspose.Cells component. For example, you may place the licensing code in your class library which it should be run when you create any object for that class library (e.g in the constructor) at the start before using any other APIs of the product.

Also, please see the document for your reference:
http://www.aspose.com/docs/display/cellsjava/Licensing

Hope, this helps a bit.

Thank you.
7

Hi,


I did send some questions in Aspose forum when I was doing some export feature using Aspose in my company. It’s working great!

Now We have an enhancement in the current feature. Previously the requirement was to generate the excel file on the fly from the dat in the database. This time we need to create a csv file on the fly from the data in the database.

I did look at the documentation in Aspose Website,


Is it saving a workbook to csv format or can I create a CSV file directly from resultSet object (data from DB).

Please share any document link or exmples for the same.

Thanks,
Sindhu
8

Hi Sindhu,


Thank you for writing back.

I am not sure if there is any sophisticated approach to directly export the data from database to CSV format. Moreover, as the requirement does not involve Aspose.Cells APIs therefore you have to research it on your own.

By the way, if you wish to use Aspose.Cells for Java APIs for this requirement, you can import the data from database onto the worksheet cells then export the workbook to CSV format.

9

Hi,


Thanks for the reply.

I have some follow-up questions.

As there is no direct Aspose.Cells API to directly export the data from database, you are suggesting to import the data from db onto the worksheet cells. Since the data is big (millions), now we are retrieving the data by batch from DB, create the excel for that batch and stream on the fly. So we are not persisting the excel in the db. It’s asaved to user’s machine.

1. Can we create CSV from partial EXCEL, (excel created for each batch) and stream that CSV on the fly to client’s machine. (We are now doing the same for excel. When user clicks the 'export ’ button in UI, we retrieve data for one batch from db, create the excel for that batch, stream to user’s machine. Streaming will stop when all the batch is done in server side).

Please let me know.
Thanks,
Sindhu


10
Hi again,

sdmoodys:
1. Can we create CSV from partial EXCEL, (excel created for each batch) and stream that CSV on the fly to client's machine. (We are now doing the same for excel. When user clicks the 'export ' button in UI, we retrieve data for one batch from db, create the excel for that batch, stream to user's machine. Streaming will stop when all the batch is done in server side).


Thank you for writing back.

Please note, the process to save the partial CSV is same as saving the partial spreadsheet, but with different second parameter to the Workbook.save method, that is; for CSV you either pass an instance of TxtSaveOptions or SaveFormat.CSV to the Workbook.save method. Rest of your logic will remain same as you have currently opted to stream the partial spreadsheet after importing one batch of the database records.