数据导出到Excel

请求的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 out = 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;
    }
}

 

posted @ 2021-12-09 14:40  季白二十四  阅读(96)  评论(0编辑  收藏  举报