背景
有个项目需要导出数据库数据为Excel,实践过程中发现,先要从数据库取数据,再拼装成Excel文件,再发送给用户,整个过程非常长,用户需要在网页卡很久才会出现下载框,体验很差。
希望做到数据分页的从数据库查出来,直接发给用户,查多少发多少,提升用户体验。
方案
我们拿到HttpServletResponse的OutputStream,采用流的方式向OutputStream里面直接写入数据实现想要的功能。
package com.example.demo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
@RestController
@RequestMapping("/")
@Slf4j
public class Controller {
@GetMapping("/")
public void doGet(HttpServletResponse resp)
throws IOException {
resp.setContentType("application/octet-stream");
resp.setHeader("Content-disposition", "attachment; filename=sample.txt");
try(InputStream in = new FileInputStream(ResourceUtils.getFile("classpath:sample.txt"));
OutputStream out = resp.getOutputStream()) {
byte[] buffer = new byte[10];
int numBytesRead;
while ((numBytesRead = in.read(buffer)) > 0) {
try {
Thread.sleep(100);
log.info("模拟从数据库取数据...");
} catch (InterruptedException e) {
e.printStackTrace();
}
out.write(buffer, 0, numBytesRead);
out.flush();
}
}
}
@GetMapping("/csvFile")
public void getCSVFile(HttpServletResponse resp) throws IOException {
List<String[]> dataLines = new ArrayList<>();
dataLines.add(new String[]
{ "John", "Doe", "38", "Comment Data\nAnother line of comment data" });
dataLines.add(new String[]
{ "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" });
dataLines.add(new String[]
{ "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" });
dataLines.add(new String[]
{ "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" });
dataLines.add(new String[]
{ "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" });
dataLines.add(new String[]
{ "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" });
resp.setContentType("application/octet-stream");
resp.setHeader("Content-disposition", "attachment; filename=sample.csv");
try (OutputStream out = resp.getOutputStream()) {
for(String[] data : dataLines) {
final String s = this.convertToCSV(data) + System.lineSeparator();
out.write(s.getBytes(Charset.forName("UTF-8")));
out.flush();
try {
// 模拟数据库延迟
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
}
public String convertToCSV(String[] data) {
return Stream.of(data)
.map(this::escapeSpecialCharacters)
.collect(Collectors.joining(","));
}
public String escapeSpecialCharacters(String data) {
String escapedData = data.replaceAll("\\R", " ");
if (data.contains(",") || data.contains("\"") || data.contains("'")) {
data = data.replace("\"", "\"\"");
escapedData = "\"" + data + "\"";
}
return escapedData;
}
}
package com.example.demo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
@RestController
@RequestMapping("/")
@Slf4j
public class Controller {
@GetMapping("/")
public void doGet(HttpServletResponse resp)
throws IOException {
resp.setContentType("application/octet-stream");
resp.setHeader("Content-disposition", "attachment; filename=sample.txt");
try(InputStream in = new FileInputStream(ResourceUtils.getFile("classpath:sample.txt"));
OutputStream out = resp.getOutputStream()) {
byte[] buffer = new byte[10];
int numBytesRead;
while ((numBytesRead = in.read(buffer)) > 0) {
try {
Thread.sleep(100);
log.info("模拟从数据库取数据...");
} catch (InterruptedException e) {
e.printStackTrace();
}
out.write(buffer, 0, numBytesRead);
out.flush();
}
}
}
@GetMapping("/csvFile")
public void getCSVFile(HttpServletResponse resp) throws IOException {
List<String[]> dataLines = new ArrayList<>();
dataLines.add(new String[]
{ "John", "Doe", "38", "Comment Data\nAnother line of comment data" });
dataLines.add(new String[]
{ "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" });
dataLines.add(new String[]
{ "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" });
dataLines.add(new String[]
{ "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" });
dataLines.add(new String[]
{ "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" });
dataLines.add(new String[]
{ "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" });
resp.setContentType("application/octet-stream");
resp.setHeader("Content-disposition", "attachment; filename=sample.csv");
try (OutputStream out = resp.getOutputStream()) {
for(String[] data : dataLines) {
final String s = this.convertToCSV(data) + System.lineSeparator();
out.write(s.getBytes(Charset.forName("UTF-8")));
out.flush();
try {
// 模拟数据库延迟
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
}
public String convertToCSV(String[] data) {
return Stream.of(data)
.map(this::escapeSpecialCharacters)
.collect(Collectors.joining(","));
}
public String escapeSpecialCharacters(String data) {
String escapedData = data.replaceAll("\\R", " ");
if (data.contains(",") || data.contains("\"") || data.contains("'")) {
data = data.replace("\"", "\"\"");
escapedData = "\"" + data + "\"";
}
return escapedData;
}
}
package com.example.demo; import lombok.extern.slf4j.Slf4j; import org.springframework.util.ResourceUtils; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.nio.charset.Charset; import java.util.ArrayList; import java.util.List; import java.util.stream.Collectors; import java.util.stream.Stream; @RestController @RequestMapping("/") @Slf4j public class Controller { @GetMapping("/") public void doGet(HttpServletResponse resp) throws IOException { resp.setContentType("application/octet-stream"); resp.setHeader("Content-disposition", "attachment; filename=sample.txt"); try(InputStream in = new FileInputStream(ResourceUtils.getFile("classpath:sample.txt")); OutputStream out = resp.getOutputStream()) { byte[] buffer = new byte[10]; int numBytesRead; while ((numBytesRead = in.read(buffer)) > 0) { try { Thread.sleep(100); log.info("模拟从数据库取数据..."); } catch (InterruptedException e) { e.printStackTrace(); } out.write(buffer, 0, numBytesRead); out.flush(); } } } @GetMapping("/csvFile") public void getCSVFile(HttpServletResponse resp) throws IOException { List<String[]> dataLines = new ArrayList<>(); dataLines.add(new String[] { "John", "Doe", "38", "Comment Data\nAnother line of comment data" }); dataLines.add(new String[] { "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" }); dataLines.add(new String[] { "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" }); dataLines.add(new String[] { "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" }); dataLines.add(new String[] { "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" }); dataLines.add(new String[] { "Jane", "Doe, Jr.", "19", "She said \"I'm being quoted\"" }); resp.setContentType("application/octet-stream"); resp.setHeader("Content-disposition", "attachment; filename=sample.csv"); try (OutputStream out = resp.getOutputStream()) { for(String[] data : dataLines) { final String s = this.convertToCSV(data) + System.lineSeparator(); out.write(s.getBytes(Charset.forName("UTF-8"))); out.flush(); try { // 模拟数据库延迟 Thread.sleep(1000); } catch (InterruptedException e) { e.printStackTrace(); } } } } public String convertToCSV(String[] data) { return Stream.of(data) .map(this::escapeSpecialCharacters) .collect(Collectors.joining(",")); } public String escapeSpecialCharacters(String data) { String escapedData = data.replaceAll("\\R", " "); if (data.contains(",") || data.contains("\"") || data.contains("'")) { data = data.replace("\"", "\"\""); escapedData = "\"" + data + "\""; } return escapedData; } }
参考资料:
https://www.baeldung.com/java-csv
https://stackoverflow.com/questions/4069028/write-string-to-output-stream