preface
I recently encountered a requirement in a project to provide a back-end interface for downloading Csv and Excel tables. This interface receives query parameters from the front end and performs query operations on the database against these parameters. The queried results are generated into Excel and Csv files and returned to the front end as byte streams.
The front-end receives the stream file and initially uses Ajax to receive it, but the request is cancelled by the browser. It turns out that Ajax, which has been around for so long, doesn’t support streaming file downloads. This problem was fixed later with a primitive XMLHttpRequest front-end.
First give the address of the project source code. This is the source code, welcome everyone star or mention MR.
Csv
The new controller
Let’s start with a simple example. Start by creating such an interface in the Controller.
@GetMapping("csv")
public void csv( HttpServletRequest request, HttpServletResponse response ) throws IOException {
String fileName = this.getFileName(request, "Test data.csv");
response.setContentType(MediaType.APPLICATION_OCTET_STREAM.toString());
response.setHeader("Content-Disposition"."attachment; filename=\"" + fileName + "\";");
LinkedHashMap<String, Object> header = new LinkedHashMap<>();
LinkedHashMap<String, Object> body = new LinkedHashMap<>();
header.put("1"."Name");
header.put("2"."Age");
List<LinkedHashMap<String, Object>> data = new ArrayList<>();
body.put("1"."Xiao Ming");
body.put("2"."Wang");
data.add(header);
data.add(body);
data.add(body);
data.add(body);
FileCopyUtils.copy(ExportUtil.exportCSV(data), response.getOutputStream());
}
Copy the codeThe this.getfilename (request, “test data.csv “) function is used to get the file name of the export file. It is mentioned separately because the default encoding used by different browsers is different. For example, if the default UTF-8 encoding is used. Chinese garbled characters will appear when you download the chrome browser. Here’s the code.
private String getFileName(HttpServletRequest request, String name) throws UnsupportedEncodingException {
String userAgent = request.getHeader("USER-AGENT");
return userAgent.contains("Mozilla")?new String(name.getBytes(), "ISO8859-1") : name;
}
Copy the codeResponse.getoutputstream () is used to create byte output stream. At the end of the controller code of exporting CSV file, the byte stream is written into the output stream through the copy file function in the utility class, so that the CSV file is returned to the client in the form of byte stream.
When the current end accesses the server interface through an HTTP request, all HTTP request information is encapsulated in an HttpServletRequest object. For example, you can get the URL of the request, how the request was made, the client IP and full host name of the request, the IP and full host name of the Web server, parameters in the request line, parameters to get the request header, and so on.
For each HTTP request, the server automatically creates an HttpServletResponse object corresponding to the request object. The response object can redirect the current request, customize the header of the response body, set the return stream, and so on.
Create an export utility class
Let’s create a new export utility class that is specifically responsible for exporting files in various formats. Here’s the code.
public class ExportUtil {
public static byte[] exportCSV(List<LinkedHashMap<String, Object>> exportData) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
BufferedWriter buffCvsWriter = null;
try {
buffCvsWriter = new BufferedWriter(new OutputStreamWriter(out, StandardCharsets.UTF_8));
// Write the body data to the table
for (Iterator<LinkedHashMap<String, Object>> iterator = exportData.iterator(); iterator.hasNext(); ) {
fillDataToCsv(buffCvsWriter, iterator.next());
if(iterator.hasNext()) { buffCvsWriter.newLine(); }}// Flush the buffer
buffCvsWriter.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
// Release resources
if(buffCvsWriter ! =null) {
try {
buffCvsWriter.close();
} catch(IOException e) { e.printStackTrace(); }}}return out.toByteArray();
}
private static void fillDataToCsv(BufferedWriter buffCvsWriter, LinkedHashMap row) throws IOException {
Map.Entry propertyEntry;
for (Iterator<Map.Entry> propertyIterator = row.entrySet().iterator(); propertyIterator.hasNext(); ) {
propertyEntry = propertyIterator.next();
buffCvsWriter.write("\" " + propertyEntry.getValue().toString() + "\" ");
if (propertyIterator.hasNext()) {
buffCvsWriter.write(","); }}}}Copy the codeFillDataToCsv is mainly extracted to fill row by row with CSV data.
run
Then run the program, called http://localhost:8080/csv, you can download the sample CSV file. The following is an example.
Excel
The new controller
Create an interface for downloading XLSX files.
@GetMapping("xlsx")
public void xlsx( HttpServletRequest request, HttpServletResponse response ) throws IOException {
String fileName = this.getFileName(request, "Test data.xlsx");
response.setContentType(MediaType.APPLICATION_OCTET_STREAM.toString());
response.setHeader("Content-Disposition"."attachment; filename=\"" + fileName + "\";");
List<LinkedHashMap<String, Object>> datas = new ArrayList<>();
LinkedHashMap<String, Object> data = new LinkedHashMap<>();
data.put("1"."Name");
data.put("2"."Age");
datas.add(data);
for (int i = 0; i < 5; i++) {
data = new LinkedHashMap<>();
data.put("1"."Xiaoqing");
data.put("2"."White");
datas.add(data);
}
Map<String, List<LinkedHashMap<String, Object>>> tableData = new HashMap<>();
tableData.put("Daily report", datas);
tableData.put("Weekly report", datas);
tableData.put("Monthly Statement", datas);
FileCopyUtils.copy(ExportUtil.exportXlsx(tableData), response.getOutputStream());
}
Copy the codeSupplementary utility class
In the new export tool class above, only CSV export function, next we will add XLSX export function.
public static byte[] exportXlsx(Map<String, List<LinkedHashMap<String, Object>>> tableData) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
try {
HSSFWorkbook workbook = new HSSFWorkbook();
// Create multiple sheets
for (Map.Entry<String, List<LinkedHashMap<String, Object>>> entry : tableData.entrySet()) {
fillDataToXlsx(workbook.createSheet(entry.getKey()), entry.getValue());
}
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
return out.toByteArray();
}
/** * Write data from linkedHashMap to XLSX table **@param sheet
* @param data
*/
private static void fillDataToXlsx(HSSFSheet sheet, List<LinkedHashMap<String, Object>> data) {
HSSFRow currRow;
HSSFCell cell;
LinkedHashMap row;
Map.Entry propertyEntry;
int rowIndex = 0;
int cellIndex = 0;
for (Iterator<LinkedHashMap<String, Object>> iterator = data.iterator(); iterator.hasNext(); ) {
row = iterator.next();
currRow = sheet.createRow(rowIndex++);
for (Iterator<Map.Entry> propertyIterator = row.entrySet().iterator(); propertyIterator.hasNext(); ) {
propertyEntry = propertyIterator.next();
if (propertyIterator.hasNext()) {
String value = String.valueOf(propertyEntry.getValue());
cell = currRow.createCell(cellIndex++);
cell.setCellValue(value);
} else {
String value = String.valueOf(propertyEntry.getValue());
cell = currRow.createCell(cellIndex++);
cell.setCellValue(value);
break; }}if (iterator.hasNext()) {
cellIndex = 0; }}}Copy the codeFillDataToXlsx is used to brush data for each line of an XLSX file, the same as CSV.
run
Then run the program, called http://localhost:8080/xlsx, you can download the sample CSV file. The following is an example.
The project address
Finally, the project address is given again. If you don’t understand some of them, you may as well clone the project and operate it yourself.
reference
This is an important reference in the process of resolving a request cancelled by the browser.
- https://www.cnblogs.com/cdemo/p/5225848.html