java excel 导出多个sheet_java导出怎样一个文件里面 出现两个 secelt

第一步：需要一个通用的装载数据的实体类

package com.yfh.common.core.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.List;
import java.util.Map;

/**
 * @author zdj
 * @version 1.0
 * @date 2021/09/27 14:14
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelDataVo implements Serializable {
    /**
     * excel sheet的名称
     */
    private String sheetName;


    /**
     * excel sheet表的标题
     */
    private String sheetTopColHeaderTitle;


    /**
     * excel sheet表的列头名称
     */
    private String[] sheetTopColHeaderName;


    /**
     * excel sheet表的列头属性
     */
    private String[] sheetTopColHeaderAttribute;


    /**
     * excel sheet表的数据
     */
    private List<Map<String, Object>> sheetDataList;
}

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

第二步：通用的封装数据的工具类

package com.yfh.common.core.utils;

import com.yfh.common.core.domain.ExcelDataVo;
import com.yfh.common.core.exception.BaseException;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author zdj
 * @version 1.0
 * @date 2021/8/31 14:14
 */
public class PackExcelSheetsDataUtil {
    /*
     * 封装需要导出的数据
     * */
    public void packExcelSheetsData(HttpServletResponse response, String excelName, List<ExcelDataVo> excelDataVoList) throws Exception{
        try {
            // 对excel进行判断
            for(int j = 0; j < excelDataVoList.size(); j++){
                String[] headerName = excelDataVoList.get(j).getSheetTopColHeaderName();
                String[] headerAttribute = excelDataVoList.get(j).getSheetTopColHeaderAttribute();
                if(headerName.length != headerAttribute.length){
                    throw new BaseException("列头长度与属性长度不对应！");
                }
            }

            // 创建一个excel对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            OutputStream out = response.getOutputStream();

            //定义标题以及设置响应头信息
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(excelName+".xls", "UTF-8"));

            // 循环生成指定数量的sheet表
            for(int j = 0; j < excelDataVoList.size(); j++){
                // 获取单个sheet的数据
                String sheetName = excelDataVoList.get(j).getSheetName();
                String sheetTopColHeaderTitle = excelDataVoList.get(j).getSheetTopColHeaderTitle();
                String[] headers = excelDataVoList.get(j).getSheetTopColHeaderName();
                String[] headerCol = excelDataVoList.get(j).getSheetTopColHeaderAttribute();
                List<Map<String, Object>> sheetDataList = excelDataVoList.get(j).getSheetDataList();

                // 申请一个最后的list集合并封装数据
                List<Object[]> dataList = new ArrayList<Object[]>();
                Object[] objs = null;
                for(int i = 0; i < sheetDataList.size(); i++){
                    // 封装lie数据
                    objs = new Object[headers.length];
                    for (int k = 0; k < headers.length; k++) {
                        objs[k] = sheetDataList.get(i).get(headerCol[k]);
                    }

                    //数据添加到excel表格
                    dataList.add(objs);
                }

                //使用流将数据导出
                new ExcelSheetsUtil(sheetName, sheetTopColHeaderTitle, headers, dataList).export(workbook);
            }

            // 最后将整个excel全部写到浏览器
            workbook.write(out);
            out.flush();
            out.close();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
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
77
78
79

第三步：导出工具类

package com.yfh.common.core.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;

/**
 * @author zdj
 * @version 1.0
 * @date 2021/8/31 14:14
 */
public class ExcelSheetsUtil {

    //导出表的列名
    private String[] rowName;

    //导出表的头部标题
    private String oneheaders;

    //sheet表表名
    private String sheettitle;

    // 需要导出的数据集合
    private List<Object[]> dataList;

    /**
     * @param sheettitle 单个sheet名称
     * @param oneheaders 单个sheet头部名称
     * @param rowName  单个sheet对应的列头
     * @param dataList  单个sheet对应的数据
     */
    public ExcelSheetsUtil(String sheettitle, String oneheaders, String[] rowName, List<Object[]> dataList){
        this.dataList = dataList;
        this.oneheaders = oneheaders;
        this.rowName = rowName;
        this.sheettitle = sheettitle;
    }

    /*
     * 导出数据
     * */
    public void export(HSSFWorkbook workbook) throws Exception{
        try{
            // 创建工作表
            HSSFSheet sheet = workbook.createSheet(sheettitle);

            //获取列头样式对象
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);

            //单元格样式对象
            HSSFCellStyle style = this.getStyle(workbook);

            //第一行
            HSSFRow rowfirstName = sheet.createRow(0);
            //创建列头对应个数的单元格
            HSSFCell oneCellRowName = rowfirstName.createCell(0);
            //设置列头单元格的数据类型
            oneCellRowName.setCellType(CellType.STRING);
            HSSFRichTextString onetext = new HSSFRichTextString(oneheaders);
            //设置列头单元格的值
            oneCellRowName.setCellValue(onetext);
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(0,0,0, rowName.length - 1));
            //设置列头单元格样式
            oneCellRowName.setCellStyle(columnTopStyle);


            // 定义所需列数
            int columnNum = rowName.length;

            /***   绑定列头   ****/
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            HSSFRow rowRowName = sheet.createRow(1);
            //单元格样式对象
            HSSFCellStyle styleHearer = this.getStyle(workbook);
            Font headerFont = workbook.createFont();
            headerFont.setFontName("微软雅黑");
            headerFont.setFontHeightInPoints((short) 10);
            headerFont.setBold(true);
            headerFont.setColor(IndexedColors.BLACK.getIndex());
            styleHearer.setFont(headerFont);
            for(int n = 0; n < columnNum; n++){
                //创建列头对应个数的单元格
                HSSFCell  cellRowName = rowRowName.createCell(n);
                //设置列头单元格的数据类型
                cellRowName.setCellType(CellType.STRING);
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                //设置列头单元格的值
                cellRowName.setCellValue(text);
                //设置列头单元格样式
                cellRowName.setCellStyle(styleHearer);
            }


            /***   绑定数据   ****/
            //将查询出的数据设置到sheet对应的单元格中
            for(int i=0;i<dataList.size();i++){
                Object[] obj = dataList.get(i);
                //创建所需的行数（从第二行开始写数据）
                HSSFRow row = sheet.createRow(i+2);
                for(int j = 0; j < obj.length; j++){
                    //设置单元格的数据类型
                    HSSFCell  cell = row.createCell(j, CellType.STRING);;
                    if(!"".equals(obj[j]) && obj[j] != null){
                        //设置单元格的值
                        cell.setCellValue(obj[j].toString());
                    }
                    //设置单元格样式
                    cell.setCellStyle(style);
                }
            }

            /***   让列宽随着导出的列长自动适应   ****/
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == CellType.STRING) {
                            int length = 0;
                            try {
                                length = currentCell.getStringCellValue().getBytes().length;
                            } catch (Exception e) {
                                e.printStackTrace();
                            }

                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if(colNum == 0){
                    sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
                }else{
                    sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
                }
            }
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    /*
     * 列头单元格样式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)11);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("宋体");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /*
     * 列数据信息单元格样式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体名字
        font.setFontName("宋体");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }
}
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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213

第四步：测试导出

/**
     * 测试多个导出sheet
     */
    @ApiOperation(value = "测试多个导出sheet", notes = "测试多个导出sheet", httpMethod = "GET")
    @RequestMapping(value = "/exportTeenagerMoreSheet", method = RequestMethod.GET)
    public void exportTeenagerMoreSheet(HttpServletResponse response){
        // 定义学生信息
        List<Map<String, Object>> list1 = Lists.newArrayList();
        for (int i = 0; i < 3; i++) {
            Map<String, Object> map1 = Maps.newHashMap();
            map1.put("name", "张三"+i);
            map1.put("sex", "男"+i);
            map1.put("age", "23"+i);
            map1.put("isSchool", "是"+i);
            list1.add(map1);
        }

        // 定义学校信息
        List<Map<String, Object>> list2 = Lists.newArrayList();
        for (int i = 0; i < 4; i++) {
            Map<String, Object> map2 = Maps.newHashMap();
            map2.put("name", "杭州天长小学"+i);
            map2.put("adress", "浙江省杭州市"+i);
            map2.put("concat", "王五"+i);
            list2.add(map2);
        }

        // 定义其他信息
        List<Map<String, Object>> list3 = Lists.newArrayList();
        for (int i = 0; i < 10; i++) {
            Map<String, Object> map3 = Maps.newHashMap();
            map3.put("protity", "其他1"+i);
            map3.put("remark", "我是备注"+i);
            list3.add(map3);
        }


        // 封装需要导出的数据
        List<ExcelDataVo> excelDataVoList = Lists.newArrayList();

        // 封装学生信息
        ExcelDataVo excelDataSheetOne = new ExcelDataVo();
        excelDataSheetOne.setSheetName("学生信息");
        excelDataSheetOne.setSheetTopColHeaderTitle("学生信息");
        excelDataSheetOne.setSheetTopColHeaderName(new String[] {"姓名","性别","年龄", "学生"});
        excelDataSheetOne.setSheetTopColHeaderAttribute(new String[] {"name","sex","age", "isSchool"});
        excelDataSheetOne.setSheetDataList(list1);
        excelDataVoList.add(excelDataSheetOne);

        // 封装学校信息
        ExcelDataVo excelDataSheetTwo = new ExcelDataVo();
        excelDataSheetTwo.setSheetName("学校信息");
        excelDataSheetTwo.setSheetTopColHeaderTitle("学校信息");
        excelDataSheetTwo.setSheetTopColHeaderName(new String[] {"学校","地址","联系人"});
        excelDataSheetTwo.setSheetTopColHeaderAttribute(new String[] {"name","adress","concat"});
        excelDataSheetTwo.setSheetDataList(list2);
        excelDataVoList.add(excelDataSheetTwo);

        // 封装其他信息
        ExcelDataVo excelDataSheetThree = new ExcelDataVo();
        excelDataSheetThree.setSheetName("其他信息");
        excelDataSheetThree.setSheetTopColHeaderTitle("其他信息");
        excelDataSheetThree.setSheetTopColHeaderName(new String[] {"描述","备注"});
        excelDataSheetThree.setSheetTopColHeaderAttribute(new String[] {"protity","remark"});
        excelDataSheetThree.setSheetDataList(list3);
        excelDataVoList.add(excelDataSheetThree);

        // 导出数据
        try {
            new PackExcelSheetsDataUtil().packExcelSheetsData(response, "学生基础信息", excelDataVoList);
        } catch (Exception e) {
            throw new BaseException("导出异常!");
        }
    }
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