Java导出Excel表的两种方式

背景

新公司会员分销报表管理系统有导出excel表的场景，通过查找，项目中并没有excel表相关工具类，故手写了工具类给大家分享一下，以备不时之需。

方式一（POI方式）

1.该工具类包含导出和导入解析功能，其中导入解析功能需读者根据自身业务情况做进一步封装。
2.工具类中有笔者自己的CommonException异常类，需读者根据自身业务做相应调整和替换。

import com.zzx.commons.exceptions.CommonException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.tomcat.util.http.fileupload.FileUploadException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.List;

public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger("info");
    
    /**
     * 下载execel表模板
     *
     * @param response   HttpServletResponse
     * @param fileName   导出文件名
     * @param list       目标表单
     * @param title      表头数组
     * @param properties 导出属性（与表头对应）
     */
    public static void download(HttpServletResponse response, String fileName, List list, String[] title, String[] properties) {
        HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook)
        HSSFSheet sheet = workbook.createSheet("sheet1");//创建工作表(Sheet)
        //设置第一列宽（3766）
        HSSFRow row = sheet.createRow(0);// 创建行,从0开始
        for (int i = 0; i < title.length; i++) {
            HSSFCellStyle style = workbook.createCellStyle();
            style.setDataFormat(workbook.createDataFormat().getFormat("@"));
            sheet.setDefaultColumnStyle(i,style);
            HSSFCell cells = row.createCell(i);// 设置单元格内容,重载
            styleOne(workbook, cells).setCellValue(title[i]);
        }
        for(int i = 0; i < list.size() ;i++){
            HSSFRow row_one = sheet.createRow(i+1);
            //反射得到所有属性
            Class cls = list.get(i).getClass();
            Field[] fields = cls.getDeclaredFields();
            styleTwo(workbook, row_one);
            for(int k = 0 ; k < properties.length ; k++) {
                for (Field field : fields) {
                    //得到属性
                    //打开私有访问
                    field.setAccessible(true);
                    //获取属性
                    Object value = null;
                    try {
                        value = field.get(list.get(i));
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    if (properties[k].equals(field.getName())) {
                        row_one.createCell(k).setCellValue(String.valueOf(value));
                    }
                }
            }
        }
        OutputStream outputStream = null;
        try {
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            outputStream = response.getOutputStream();
            workbook.write(outputStream);//保存Excel文件
            if (outputStream != null) {
                outputStream.close();//关闭文件流
            }
        } catch (Exception e) {
            logger.info("execel流输出时错误,错误详情：{}", e.getMessage());
            throw new CommonException("execel流输出时错误");
        }
        System.out.println("OK!");
    }


    /**
     * 上传并解析execel表
     *
     * @param file
     * @return
     * @throws IOException
     * @throws FileUploadException
     */
    public static String upLoadExecel(MultipartFile file) {

        byte[] b = new byte[0];
        try {
            b = file.getBytes();
        } catch (IOException e) {
            logger.info("上传文件出错,错误详情：{}", e.getMessage());
            throw new CommonException("上传文件出错");
        }
        InputStream is = new ByteArrayInputStream(b);
        HSSFWorkbook hssfWorkbook = null;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (IOException e) {
            logger.info("该文件为非execel文件,错误详情：{}", e.getMessage());
            throw new CommonException("该文件为非execel文件");
        }
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                // 循环列Cell
                for (int j = 0; j <= hssfRow.getLastCellNum(); j++) {
                    HSSFCell cell = hssfRow.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    System.out.println(getCellDate(cell));
                }
            }
        }
        return "数据上传成功！";
    }


    /**
     * 遍历获取相应类型值静态工具类
     *
     * @param cell
     * @return
     */
    private static String getCellDate(Cell cell) {

        String return_string = null;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                return_string = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                DecimalFormat format = new DecimalFormat("#,##0.000");
                return_string = String.valueOf(format.format(cell.getNumericCellValue()));
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                return_string = String.valueOf(cell.getBooleanCellValue());
            default:
                return_string = "";
                break;
        }
        return return_string;
    }

    
    /**
     * 样式1
     *
     * @param workbook
     * @param cell
     * @return
     */
    private static HSSFCell styleOne(HSSFWorkbook workbook, HSSFCell cell) {
        //创建CellStyle或HSSFCellStyle对象
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //设置单元格字体位置水平方向
        style.setAlignment(HorizontalAlignment.LEFT);
        //设置单元格字体位置垂直方向
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框
        style.setBorderBottom(BorderStyle.THIN);   //底部边框样式
        //通过颜色索引设置底部颜色
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); //底部边框颜色

        //同理，设置左边样式
        style.setBorderLeft(BorderStyle.THIN);    //左边边框样式
        style.setLeftBorderColor(IndexedColors.BLUE.getIndex());    //左边边框颜色

        //同理，设置右边样式
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREEN.getIndex());
        //最后，设置顶部样式
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BROWN.getIndex());
        //设置字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12); // 字体高度
        font.setFontName(" 宋体 "); // 字体
        style.setFont(font);
        cell.setCellStyle(style);
        return cell;
    }

    /**
     * 样式2
     *
     * @param workbook
     * @param cell
     * @return
     */
    private static HSSFRow styleTwo(HSSFWorkbook workbook, HSSFRow cell) {
        //设置单元格数据格式
        HSSFCellStyle textStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        textStyle.setDataFormat(format.getFormat("#,##0.000"));
        cell.setRowStyle(textStyle);
        return cell;
    }

}

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
214
215
216
217
218
219
220
221
222

方式二（阿里巴巴EasyExcel）

• pom.xml导入依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>
1
2
3
4
5

• 实体类

import com.alibaba.excel.annotation.ExcelProperty;

public class UserEntity {

    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("年龄")
    private Integer age;
    @ExcelProperty("职业")
    private String occupation;
    @ExcelProperty("账户")
    private String account;
    @ExcelProperty("地址")
    private String address;
    
    //gettrt和setter省略。。。。
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• EasyExcel工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

public class ExcelUtil {

    public static void process(String fileName, List target, Class clazs , HttpServletResponse response) throws IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        EasyExcel.write(response.getOutputStream(), clazs).excelType(ExcelTypeEnum.XLS).autoCloseStream(Boolean.TRUE)
                .sheet("用户报表").doWrite(target);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

• EasyExcelListener类(用于导入)

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.List;

/**
 * @Author: zhuzixin
 * @CreateDate: 2021-07-04 15:09
 */
public class EasyExcelListener<T>  extends AnalysisEventListener<T> {

    private List<T> scoringPlayersBeanList;

    public EasyExcelListener() {
    }

    public EasyExcelListener(List<T> scoringPlayersBeanList) {
        this.scoringPlayersBeanList = scoringPlayersBeanList;
    }

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        System.out.println(t);
        scoringPlayersBeanList.add(t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

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

• Controller类[导出]

import com.husky.tinelion.entity.UserEntity;
import com.husky.tinelion.utils.ExcelUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@RestController
public class UserReportFormController {

    @GetMapping("/load")
    public void downloadReportData(HttpServletResponse response){
        String fileName = "用户详情报表数据.xls";
        List<UserEntity> userEntities = new ArrayList<>();
        UserEntity userEntity = new UserEntity();
        userEntity.setName("自鑫");
        userEntity.setAccount("9999999999999");
        userEntity.setAddress("成都");
        userEntity.setAge(18);
        userEntity.setOccupation("码农");
        userEntities.add(userEntity);
        try {
            ExcelUtil.process(fileName,userEntities,UserEntity.class,response);
        } catch (IOException 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

• 导出结果

• Controller类[导入]

@RestController
public class RegionExcelController {

    @Autowired
    private DicLocationProvinceMapper provinceMapper;
    @Autowired
    private DicLocationCityMapper cityMapper;
    @Autowired
    private DicLocationRegionMapper regionMapper;
    @GetMapping("/execute")
    public void executeUploadRegion() throws FileNotFoundException {
        FileInputStream inputStream = new FileInputStream(new File("C:\\Users\\Procedural.life\\Desktop\\tianjing\\regions.xlsx"));

		//此处为easyexcel解析List<Region>实例 
        List<Region> regions = new ArrayList<>();
        EasyExcelListener<Region> easyExcelListener = new EasyExcelListener<>(regions);
        EasyExcel.read(inputStream, Region.class, easyExcelListener).sheet().doRead();

        Map<String, List<Region>> p = regions.stream().collect(Collectors.groupingBy(Region::getProvince));
        Counter counter = new Counter();
        p.forEach((k, v) -> {
            counter.setA(counter.getA() + 1);
            DicLocationProvince province = new DicLocationProvince();
            province.setDicLocationCountryId(1);
            province.setDicLocationProvinceId(counter.getA());
            province.setProvinceName(k);
            provinceMapper.insertSelective(province);
            System.out.println(k + ":" + counter.getA());

            Map<String, List<Region>> c = v.stream().collect(Collectors.groupingBy(Region::getCity));
            c.forEach((m, n) -> {
                counter.setB(counter.getB() + 1);
                DicLocationCity city = new DicLocationCity();
                city.setDicLocationProvinceId(counter.getA());
                city.setDicLocationCountryId(1);
                city.setDicLocationCityId(counter.getB());
                city.setCityName(m);
                cityMapper.insertSelective(city);
                System.out.println(m + ":" + counter.getA()+ ":" + counter.getB());

                n.forEach(co -> {
                    counter.setC(counter.getC() + 1);
                    DicLocationRegion region = new DicLocationRegion();
                    region.setDicLocationCityId(counter.getB());
                    region.setDicLocationRegionId(counter.getC());
                    region.setRegionName(co.getCounty());
                    regionMapper.insertSelective(region);
                    System.out.println(co.getCounty()+ ":" + counter.getA()+ ":" + counter.getB()+ ":" + counter.getC());
                });
            });
        });
    }
}
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

上面的实例是将一个省市县的excel表解析保存到自己的省市县表，源文件如下：