java中Excel导出和下载

0.pom.xml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.6</version>
</dependency>

1.Excel工具类

public class Excel {
    public static HSSFWorkbook getExcel(String sheetName, String []title, List<String[]> values, HSSFWorkbook wb){

        //创建HSSFWorkbook
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 居中格式
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        //在sheet里创建第一行大标题,
        HSSFRow row=sheet.createRow(0);
        HSSFCell cell=row.createCell(0);
        cell.setCellValue(sheetName);
        cell.setCellStyle(style);
        //设置合并单元格内容
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,title.length-1));

        //创建标题
        row=sheet.createRow(1);
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //输入内容
        for(int i=0;i<values.size();i++){
            row = sheet.createRow(i+2);
            for(int j=0;j<title.length;j++){
                row.createCell(j).setCellValue(values.get(i)[j]);
            }
        }
        return wb;
    }
}

2.Controller

@GetMapping("DownloadWarehouseExcel")
public ResponseEntity<byte[]> DownloadWarehouseExcel(HttpServletResponse response) throws IOException {
    GetTime getTime=new GetTime();//时间工具类
    //excel表格数据
    List<Warehouse> warehouses = this.warehouseService.selWarehouseByTime(getTime.getYear() + "年");
    //excel标题
    String[] title = {"姓名","时间","原因","金额"};
    //excel文件名
    String fileName = "仓库损耗表-"+getTime.getYMD()+".xls";//getTime.getYMD()为获取当前年月日
    //sheet名
    String sheetName = "仓库损耗表";
    ArrayList<String[]> list= new ArrayList<String[]>();
    for (int i = 0; i <warehouses.size(); i++) {
        String [] strings=new String[title.length];
        strings[0] = warehouses.get(i).getD_name();
        strings[1] = warehouses.get(i).getTime();
        strings[2] = warehouses.get(i).getSituation();
        strings[3] = warehouses.get(i).getMoney().toString();
        list.add(strings);
    }
    //创建HSSFWorkbook
    HSSFWorkbook wb = Excel.getExcel(sheetName, title, list, null);
    
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    wb.write(bos);
    byte[] bytes=bos.toByteArray();
    HttpHeaders httpHeaders = new HttpHeaders();

    httpHeaders.setAccessControlExposeHeaders(Collections.singletonList("Content-Disposition"));
    httpHeaders.add("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8"));
    ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(bytes,httpHeaders, HttpStatus.OK);
    return responseEntity;
}

3.注意

获取文件名中文乱码问题（转自：https://blog.csdn.net/qq_38098125/article/details/87165351）

后 filename：URLEncoder.encode(fileName, "UTF-8")

前 decodeURI(resp.headers['content-disposition'].split(";")[1].split("filename=")[1]);

可以解决中文乱码问题