We have some Spring REST APIs which fetch data from our backend DB and return them to client as JSON data. AngularJS is the client side JavaScript framework we are using now. Now we need to save some of the data as Excel file so customers can download them. The file need to be downloaded as a stream because some of the file may be in big size. Is there any elegant solution for the problem?
Add a comment
|
1 Answer
I assume that you have an endpoint that generates a JSON report. Let's name it /api/reports/report1. So a request like this:
GET /api/reports/report1 HTTP/1.1
Accept: application/json
returns the report in JSON format:
HTTP/1.1 200 OK
Content-Type: application/json; charset=UTF-8
[
{
"fullName": "John Doe",
"annualIncome": 100000
},
(...)
]
Option 1
Extend the endpoint to produce application/vnd.ms-excel. So the conversation would go like this:
GET /api/reports/report1 HTTP/1.1
Accept: application/vnd.ms-excel
HTTP/1.1 200 OK
Content-Type: application/vnd.ms-excel
(... binary excel data ...)
This will not work well if you want to embed the downloads as a HTML links because there's no way to set the Accept header then.
Option 2
Generate download links. This will work with large downloads (which is the case here as you mentioned). You will need a different URL for the Excel representation of your report in this case though (e.g. /api/reports/report1/excel or /api/reports/report1?format=excel). Or you could go with Option 1 and make Excel the default mime for your enpoint (when Accept header is set to */* in the request).
Implementation
- You can use Apache POI for generating the EXCEL files - see this SO thread about creating XLS with Java
- For downloading a file in angular see this and this
Additional reference
3 Comments
Bruce Li
Thanks for the detailed answer. We've tried the POI solution which turned out not very efficient when there are many cells in the excel, especially when you need to draw lines for all cells. I will give the first option a try.
jannis
What efficiency problems did you have exactly and what do you mean by "draw lines for all cells"? It's expected that generating a large spreadsheet will take a while and how quickly Excel handles it is up to Excel. If you describe the problems in more detail maybe I can help you.
Bruce Li
By "draw lines for all cells" I mean set borders and styles for all cells, which is very slow in our testing. We just figured out a way to render a JSP page on server side and on client side we use the FileSaver.js to download the page as excel file. This now works very well for us. Thanks a lot for you help anyway.