Requirement description
Requires batch export of data in excel format.
Choose a way
Front desk + Back desk
I have encountered exporting problems in other projects before. The solution is to export the table directly in the foreground.
This time, I did not choose the method of exporting the foreground. Because I need to export all the data, I consider getting all the data directly in the background, and then exporting it directly. Finally, the foreground triggers the export API.
Backend implementation
The export uses POI. In the previous article, I have made a basic introduction, so I will not introduce the configuration here. Please refer to:POI implementation will import Excel files
Create a table
First, create a table. Here you need to create a table in .xlsx format, and use XSSFWorkbook:
Workbook workbook = new XSSFWorkbook();
Sheet sheet = ("new sheet");Next create the rows and cells of the table:
Row row = (0); (0);
Then set the header:
(0).setCellValue("Student Number");
(1).setCellValue("Name");
(2).setCellValue("phone number");Finally, get all the data and fill in the corresponding cells:
int i = 1;
for (Student student : studentList) {
row = (i);
(0).setCellValue(());
(1).setCellValue(());
(2).setCellValue(());
i++;
}
Output
This part has been a long time of confusion and has tried it repeatedly many times.
At the beginning, it is output directly in the form of a file output stream:
FileOutputStream output = new FileOutputStream("");
(output);This will generate the file correctly, but the problem is that it will be generated in the root of the project.
And the effect we want is to download it locally in our own folder.
To solve this problem, you need to add the corresponding information and return it to the browser:
OutputStream fos = ();
();
String fileName = "test";
fileName = (fileName, "utf8");
("Content-disposition", "attachment;filename="+ fileName+".xlsx");
("UTF-8");
("application/");
(fos);
();
Background code:
public void batchExport(HttpServletResponse response) {
("Create a worksheet");
Workbook workbook = new XSSFWorkbook();
Sheet sheet = ("new sheet");
("Access all students");
List<Student> studentList = (List<Student>) ();
("Create a header");
Row row = (0);
(0).setCellValue("Student Number");
(1).setCellValue("Name");
(2).setCellValue("phone number");
("Write student information into corresponding cell");
int i = 1;
for (Student student : studentList) {
row = (i);
(0).setCellValue(());
(1).setCellValue(());
(2).setCellValue(());
i++;
}
OutputStream fos;
try {
fos = ();
();
String fileName = "test";
fileName = (fileName, "utf8");
("Content-disposition", "attachment;filename="+ fileName+".xlsx");
("UTF-8");
("application/");// Set contentType to excel format (fos);
();
} catch (Exception e) {
();
}
}Front desk implementation
When calling in the front desk, I also experienced many failures. I Googled many articles and wrote various ways. I also tried it myself. The front desk and back desk made many attempts, but basically there were problems. Here I give the method for selecting the supporting background in the end.
// Background export routeconst exportUrl = '/api/student/batchExport';
// Create a tag and clicklet a = ('a');
(a);
('style', 'display:none');
('href', exportUrl);
();
(exportUrl);
The final implementation is still a relatively simple method, creating a tag and then clicking it implicitly.
I noticed that I did not use http requests here, mainly because it cannot trigger the browser download. After initiating the request, the file was not generated correctly. What is the specific one is still unclear. I will update this article after I figure it out later.
upgrade
In the above form, there is no problem when exporting all the data, but what if I want to bring some parameters?
In addition, our project is based on the same origin of nginx. Once a cross-domain problem occurs, the front desk will request it from the backend, and the browser will not carry cookies by default. Each request will be regarded as a new request.
Therefore, the above solution is limited.
So, how else can I write it?
file-saver
Here I will use FileSaver to help me generate excel files in the foreground.
('student/batchExport', { responseType: 'blob'})
.subscribe(data => {
let blob = new Blob([data], {type: 'application/;charset=utf-8'});
saveAs(blob, '');
});
Use httpClient to initiate a get request, declaring: the response type is blob.
A blob is an object used to store binary files.
Then create a blob object with type excel format.
Finally, use the saveAs function in file-saver to generate an excel file with the file name ''.
Adjust the background
Most of the backends here are the same as those in the previous one, but those with discerning eyes will find that after the frontend uses the backend method, the following code will be redundant:
String fileName = "test";
fileName = (fileName, "utf8");
("Content-disposition", "attachment;filename="+ fileName+".xlsx");
("UTF-8");
("application/");
Yes, we will leave this part to the front desk, so the corresponding part of the background can be deleted. Just use response to get the output stream:
OutputStream fos = (); (fos); ();
OK, using this method, we can add the parameters we want when initiating the http request.
Summarize
When we were on Google, we often couldn't find what we wanted at once, but it didn't mean that this was useless, because we often found inspiration in some similar articles.
So, when we do not directly find the result we want, we might as well make some bold attempts, because we will slowly understand the principle of the problem through failed attempts again and again.
The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.