Java导出数据到Excel并从浏览器直接下载

 

基于Apache POI

 

0 导入gradle包

   // https://mvnrepository.com/artifact/org.apache.poi/poi     HSSFWorkbook包，对应2003版excel
    compile group: 'org.apache.poi', name: 'poi', version: '4.0.1'
  // https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml     XSSFWorkbook包，对应2007版excel
    compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.0.1'

 

1 后端代码实现 

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
public class WriteExcel{
    public static HSSFWorkbook writeExcel(){      

        //写入excel
        HSSFWorkbook mWorkbook = new HSSFWorkbook();   //创建excel
        HSSFSheet mSheet = mWorkbook.createSheet("Score");  //创建sheet

        creatCellHeader(mSheet);
        List<List<String>> personAllList = getTest();
        // 往Excel表中写入i行数据
        for (int i=0;i<personAllList.size();i++) {
            List<String> each = personAllList.get(i);
            createCellLine(each.get(0),each.get(1),each.get(2),each.get(3),each.get(4),each.get(5),each.get(6),each.get(7),each.get(8),each.get(9),each.get(10),each.get(11) ,mSheet);
        }
        File xlsFile = new File("./result.xls");
        try {
            mWorkbook.write(xlsFile);// 或者以流的形式写入文件 mWorkbook.write(new FileOutputStream(xlsFile));
            mWorkbook.close();
        }catch (IOException e){
            e.printStackTrace();
        }
        return mWorkbook;

    }

    //download excel文件到浏览器
    public static void downloadExcel(HttpServletResponse response){
        try {
            HSSFWorkbook mWorkbook = writeExcel();
            String fileName = "result";
            OutputStream output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes(),"iso-8859-1") + ".xls");
            response.setContentType("application/x-msdownload");
    
            mWorkbook.write(output);
    //            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
    //            mWorkbook.write(bufferedOutputStream);
            mWorkbook.close();
            output.close();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    // 创建Excel标题行，第一行
    private static void creatCellHeader(HSSFSheet mSheet){
        HSSFRow headRow = mSheet.createRow(0);
        headRow.createCell(0).setCellValue("姓名");
        headRow.createCell(1).setCellValue("政治素质");
        headRow.createCell(2).setCellValue("廉洁从业");
        headRow.createCell(3).setCellValue("团队合作");
        headRow.createCell(4).setCellValue("业务水平");
        headRow.createCell(5).setCellValue("创新能力");
        headRow.createCell(6).setCellValue("组织协调");
        headRow.createCell(7).setCellValue("工作实绩");
        headRow.createCell(8).setCellValue("责任意识");
        headRow.createCell(9).setCellValue("人才培养");
        headRow.createCell(10).setCellValue("履职成效");
        headRow.createCell(11).setCellValue("总分");
    }

    // 创建Excel的一行数据
    private static void createCellLine(String name, String s1, String s2,String s3,String s4,String s5,String s6,String s7,String s8,String s9,String s10, String s11,HSSFSheet sheet) {
        HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
        dataRow.createCell(0).setCellValue(name);
        dataRow.createCell(1).setCellValue(s1);
        dataRow.createCell(2).setCellValue(s2);
        dataRow.createCell(3).setCellValue(s3);
        dataRow.createCell(4).setCellValue(s4);
        dataRow.createCell(5).setCellValue(s5);
        dataRow.createCell(6).setCellValue(s6);
        dataRow.createCell(7).setCellValue(s7);
        dataRow.createCell(8).setCellValue(s8);
        dataRow.createCell(9).setCellValue(s9);
        dataRow.createCell(10).setCellValue(s10);
        dataRow.createCell(11).setCellValue(s11);
    }

    private static List<List<String>> getTest(){
        List<String> list = new ArrayList<>();
        List<List<String>> personList = new ArrayList<>();
        list.add("lzh");
        list.add("7");
        list.add("3");
        list.add("2");
        list.add("1");
        list.add("0");
        list.add("9");
        list.add("3");
        list.add("7");
        list.add("4");
        list.add("5");
        list.add("01");
        personList.add(list);
        return personList;
    }
}

2 前端代码实现 

//button里触发该事件，实现点击下载功能
window.location.href="api/xxx"