Java 生成Excel（多sheet ）并通过浏览器下载

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;



@GetMapping("/dataExport")
    public void exportExcel(HttpServletResponse response,HttpServletRequest request, @RequestParam(name ="type")String type){
        /**
         *  type 1 : 光伏  2：空气能 3：充电桩
         */
        appExportService.appExportExcel(response,request,type);
        List<Map<String, Object>> dataList = new ArrayList<>();

        //excle 名字
        String eName = "xxxx";
        
        //学生信息sheet
        List<List<String>> d1List = new ArrayList<>();
        List<String> d1L = Arrays.asList("1","张三","13500000000","100"); //学生数据
        d1List.add(d1L);

        Map<String, Object> d1 = new HashMap<>();
        d1.put("header","id,学生姓名,手机号,年龄"); //表头
        d1.put("data",d1List);
        d1.put("sheetName","学习信息"); //sheet名


        //班级信息sheet
        List<List<String>> d2List = new ArrayList<>();
        List<String> d2L = Arrays.asList("1","三年级二班","张三","50"); //班级信息
        d2List.add(d2L);
        
        Map<String, Object> d2 = new HashMap<>();
        d2.put("header","id,班级名称,班主任,学生人数"); //表头
        d2.put("data",d2List);
        d2.put("sheetName","学习信息"); //sheet名
        dataList.add(d2);
        try{
            XSSFWorkbook workbook = new XSSFWorkbook();
            if(dataList.size()>0){
                for (int i = 0 ;i < dataList.size();i++){
                    Set<String> h = (Set<String>)dataList.get(i).get("header");
                    String[] header = h.stream().toArray(String[]::new);
                    String sheetName = String.valueOf(dataList.get(i).get("sheetName"));
                    List<List<String>> lists = (List<List<String>>)dataList.get(i).get("data");
                    ExcelUtil.exportExcel(workbook,i,sheetName,header,lists);
                }
            }
            OutputStream output;
            try {
                output = response.getOutputStream();
                //清空缓存
                response.reset();
                //定义浏览器响应表头，顺带定义下载名，比如students(中文名需要转义)
                response.setHeader("Content-disposition", "attachment;filename=" + new String(eName.getBytes(), "iso-8859-1") + ".xls");
                //定义下载的类型，标明是excel文件
                response.setContentType("application/vnd.ms-excel");
                //这时候把创建好的excel写入到输出流
                workbook.write(output);
                //养成好习惯，出门记得随手关门
                output.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }catch (Exception e){
            System.out.println(e);
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import java.util.List;

public class ExcelUtil {
    public static void exportExcel(XSSFWorkbook workbook,int sheetNum,String sheetTitle, String[] headers, List<List<String>> result) throws Exception {
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);

        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 产生表格标题行
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell((short) i);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
        }
        // 遍历集合数据，产生数据行
        if (result != null) {
            int index = 1;
            for (List<String> m : result) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                for (Object str : m) {
                    XSSFCell cell = row.createCell((short) cellIndex);
                    cell.setCellValue(str.toString());
                    cellIndex++;
                }
                index++;
            }
        }

    }

}