java实现点击查询数据生成excel文件并下载

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

须先导入关键maven包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>

请求的controller:
@GetMapping("/deposits/downloadexcel")
public void downloadExcel (HttpServletRequest request, HttpServletResponse response){
try {
//命名列名
List<String> cellNameList = new ArrayList<>();
cellNameList.add("充值时间");
cellNameList.add("充值金额");
cellNameList.add("说明");
//给文件命名
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
String dateformat = simpleDateFormat.format(new Date());
String excelPath="充值记录"+dateformat+".xls";
//给表命名
String title= "充值记录";
HSSFWorkbook excel = Excel.createExcel(title, cellNameList);
List<Deposit> Deposits = depositService.findAll();
int row = 1;
//从数据库读数据然后循环写入
for(Deposit deposit : Deposits){
List<String> excelData = new ArrayList<>();
excelData.add(deposit.getCreatedAt().toString());
excelData.add(deposit.getPrice().toString());
excelData.add(deposit.getComment());
excel = Excel.createExcelData(excel, excelData, row);
row++;
}
//输出数据
//FileOutputStream fos = new FileOutputStream(excelPath);
OutputStream out = null;
//防止中文乱码
String headStr = "attachment; filename=\"" + new String(excelPath.getBytes("utf-8"), "ISO8859-1" ) + "\"";
//response.setContentType("octets/stream");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", headStr);
out = response.getOutputStream();
//将excel写入流
excel.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
工具类：
public class Excel {
public static HSSFWorkbook createExcel(String sheetName, List<String> cellNameList) {
HSSFWorkbook excel = new HSSFWorkbook();
HSSFSheet sheet = excel.createSheet(sheetName);
HSSFRow row = sheet.createRow(0);
int cellIndex = 0;
for (String cellName : cellNameList) {
HSSFCell cell = row.createCell(cellIndex);
cell.setCellValue(cellName);
cellIndex++;
}
return excel;
}

public static HSSFWorkbook createExcelData(HSSFWorkbook excel,List<String> excelData,int rowIndex){
HSSFRow row=excel.getSheetAt(0).createRow(rowIndex);
for(int i = 0; i < excelData.size(); i++){
row.createCell(i).setCellValue(excelData.get(i));
}
return excel;
}
}
excel表格样式可以通过代码设置，具体设置百度，这里就不写了
实现效果