Java操作Excel

 

 一、小册子(原生POI)

gitee地址：https://gitee.com/zhang-zhixi/springboot-poi-excel.git

GitHub地址：https://github.com/zhangzhixi0305/springboot-poi-excel.git

1、新建SpringBoot项目导入Pom

?
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
<dependencies>
    <!--对Excel操作使用到的jar包-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
    </dependency>
</dependencies>

2、进行上传Excel的表单数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
 
<form action="/importExcel" method="post" enctype="multipart/form-data">
    选择文件：<input type="file" name="file"/><br>
    <input type="submit" value="上传"/>
</form>
 
</body>
</html>

3、处理Excel上传下载请求的Controller

?
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
223
package com.zhixi.controller;
 
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
 
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
 
/**
 * @author zhangzhixi
 * @version 1.0
 * @date 2021-12-28 16:36
 */
@RestController
public class ExcelController {
 
    private final Logger logger = LoggerFactory.getLogger(this.getClass());
 
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) throws Exception {
        // 模拟从数据库查询数据
        List<Student> studentList = new ArrayList<>();
        studentList.add(new Student(1L, "周深（web导出）", 28, "贵州", new SimpleDateFormat("yyyy-MM-dd").parse("1992-9-29"), 161.0, true));
        studentList.add(new Student(2L, "李健（web导出）", 46, "哈尔滨", new SimpleDateFormat("yyyy-MM-dd").parse("1974-9-23"), 174.5, true));
        studentList.add(new Student(3L, "周星驰（web导出）", 58, "香港", new SimpleDateFormat("yyyy-MM-dd").parse("1962-6-22"), 174.0, false));
 
        // 读取模板（实际开发可以放在resources文件夹下，随着项目一起打包发布）
        InputStream excelInputStream = new ClassPathResource("static/excel/student_info.xlsx").getInputStream();
        // XSSFWorkbook除了直接接收Path外，还可以传入输入流
        XSSFWorkbook workbook = new XSSFWorkbook(excelInputStream);
        // 获取模板sheet
        XSSFSheet sheet = workbook.getSheetAt(0);
        // 找到数据起始行（前两行是标题和表头，要跳过，所以是getRow(2)）
        XSSFRow dataTemplateRow = sheet.getRow(2);
        // 构造一个CellStyle数组，用来存放单元格样式。一行有N个单元格，数组初始长度就设置为N
        CellStyle[] cellStyles = new CellStyle[dataTemplateRow.getLastCellNum()];
        for (int i = 0; i < cellStyles.length; i++) {
            // 收集每一个格子对应的格式，你可以理解为准备了一把“格式刷”
            cellStyles[i] = dataTemplateRow.getCell(i).getCellStyle();
        }
 
        // 创建单元格，并设置样式和数据
        for (int i = 0; i < studentList.size(); i++) {
            // 注意是i+2，模板前两行是大标题和表头。你可能看着难受，想把上面for的i改为i+2，千万别。因为studentList必须从0开始取值
            XSSFRow row = sheet.createRow(i + 2);
            // 为每一行创建单元格并设置数据
            Student student = studentList.get(i);
 
            XSSFCell nameCell = row.createCell(0);// 创建单元格
            nameCell.setCellValue(student.getName());         // 设置值
            nameCell.setCellStyle(cellStyles[0]);             // 设置单元格样式
 
            XSSFCell ageCell = row.createCell(1);
            ageCell.setCellValue(student.getAge());
            ageCell.setCellStyle(cellStyles[1]);
 
            XSSFCell addressCell = row.createCell(2);
            addressCell.setCellValue(student.getAddress());
            addressCell.setCellStyle(cellStyles[2]);
 
            /**
             * 你可能有疑问，这里是日期类型，是不是要和上一次一样，设置单元格样式为日期类型？
             * 这回不用了，因为上面已经拷贝了模板的样式，生日一栏就是按日期类型展示的
             */
            XSSFCell birthdayCell = row.createCell(3);
            birthdayCell.setCellValue(student.getBirthday());
            birthdayCell.setCellStyle(cellStyles[3]);
 
            XSSFCell heightCell = row.createCell(4);
            heightCell.setCellValue(student.getHeight());
            heightCell.setCellStyle(cellStyles[4]);
 
            XSSFCell mainLandChinaCell = row.createCell(5);
            mainLandChinaCell.setCellValue(student.getIsMainlandChina());
            mainLandChinaCell.setCellStyle(cellStyles[5]);
        }
 
        /**
         * 之前通过本地文件流输出到桌面：
         * FileOutputStream out = new FileOutputStream("/Users/kevin/Documents/study/student_info_export.xlsx");
         * 现在用网络流：response.getOutputStream()
         * 注意，response的响应流没必要手动关闭，交给Tomcat关闭
         */
        String fileName = new String("学生信息表.xlsx".getBytes("UTF-8"), "ISO-8859-1");
        response.setContentType("application/octet-stream");
        response.setHeader("content-disposition", "attachment;filename=" + fileName);
        response.setHeader("filename", fileName);
        workbook.write(response.getOutputStream());
        workbook.close();
        logger.info("导出学生信息表成功！");
    }
 
    @PostMapping("/importExcel")
    public Map importExcel(MultipartFile file) throws Exception {
        // 直接获取上传的文件流，传入构造函数
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        // 获取工作表。一个工作薄中可能有多个工作表，比如sheet1 sheet2，可以根据下标，也可以根据sheet名称。这里根据下标即可。
        XSSFSheet sheet = workbook.getSheetAt(0);
 
        // 收集每一行数据（跳过标题和表头，所以int i = 2）
        int lastRowNum = sheet.getLastRowNum();
        List<Student> studentList = new ArrayList<>();
        for (int i = 2; i <= lastRowNum; i++) {
            // 收集当前行所有单元格的数据
            XSSFRow row = sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            List<String> cellDataList = new ArrayList<>();
            for (int j = 0; j < lastCellNum; j++) {
                cellDataList.add(getValue(row.getCell(j)));
            }
 
            // 把当前行数据设置到POJO。由于Excel单元格的顺序和POJO字段顺序一致，也就是数据类型一致，所以可以直接强转
            Student student = new Student();
            student.setName(cellDataList.get(0));
            student.setAge(Integer.parseInt(cellDataList.get(1)));
            student.setAddress(cellDataList.get(2));
            // getValue()方法返回的是字符串类型的 1962-6-22 00:00:00，这里按"yyyy-MM-dd HH:mm:ss"重新解析为Date
            student.setBirthday(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(cellDataList.get(3)));
            student.setHeight(Double.parseDouble(cellDataList.get(4)));
            student.setHeight(Double.parseDouble(cellDataList.get(4)));
            student.setIsMainlandChina(Boolean.valueOf(cellDataList.get(5)));
            studentList.add(student);
        }
 
        // 插入数据库
        saveToDB(studentList);
        logger.info("导入{}成功！", file.getOriginalFilename());
 
        Map<String, Object> result = new HashMap<>();
        result.put("code", 200);
        result.put("data", null);
        result.put("msg", "success");
        return result;
    }
 
    private void saveToDB(List<Student> studentList) {
        if (CollectionUtils.isEmpty(studentList)) {
            return;
        }
        // 直接打印，模拟插入数据库
        studentList.forEach(System.out::println);
    }
 
    /**
     * 提供POI数据类型 --> Java数据类型的转换
     * 由于本方法返回值设为String，所以不管转换后是什么Java类型，都要以String格式返回
     * 所以Date会被格式化为yyyy-MM-dd HH:mm:ss
     * 后面根据需要自己另外转换
     *
     * @param cell
     * @return
     */
    private String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
 
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // DateUtil是POI内部提供的日期工具类，可以把原本是日期类型的NUMERIC转为Java的Data类型
                    Date javaDate = DateUtil.getJavaDate(cell.getNumericCellValue());
                    String dateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(javaDate);
                    return dateString;
                } else {
                    /*
                     * 无论Excel中是58还是58.0，数值类型在POI中最终都被解读为Double。
                     * 这里的解决办法是通过BigDecimal先把Double先转成字符串，如果是.0结尾，把.0去掉
                     * */
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(num.toString());
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
 
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    static class Student {
        private Long id;
        private String name;
        private Integer age;
        private String address;
        private Date birthday;
        private Double height;
        private Boolean isMainlandChina;
    }
 
}

二、狂神说Java(原生POI)

读操作用到的Excel表：会员消费商品明细表

1、创建一个普通的Java项目，导入依赖

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<dependencies>
    <!--原生POI依赖-->
    <!--xLs(03)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.2</version>
    </dependency>
    <!--xLsx(07)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.2</version>
    </dependency>
    <!--test-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

2、简要说明　

HSSFWorkbook ：对应 Excel 03 版本，最多支持65535行

XSSFWorkbook：对应 Excel 07 版本，行数无限制

SXSSFWorkbook：缓存版本的操作Excel方式。

03版最多支持到65536行，而07版不受限制，理论上无限

3、案例测试

?
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
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
 
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;
 
/**
 * @ClassName ExcelWriter
 * @Author zhangzhixi
 * @Description POI操作Excel
 * @Date 2022/8/29 13:06
 * @Version 1.0
 */
public class ExcelWriterReadTest {
 
    /**
     * 将Excel文件写入到什么地方
     */
    public final static String WRITER_PATH = "F:\\StudyProject\\狂神说\\Excel操作\\zhjixi-poi\\src\\main\\resources\\";
 
    /**
     * 写入Excel-03版本
     */
    @Test
    public void excelTarget03() {
        // 1、创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 2、创建工作表
        Sheet sheet = workbook.createSheet("考核成绩表");
        // 3、创建一行
        Row row1 = sheet.createRow(0);
        // 4、创建一个列(一个单元格)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("语文");
 
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("数学");
 
        Cell cell13 = row1.createCell(2);
        cell13.setCellValue("英语");
 
 
        // 创建一行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        Cell cell22 = row2.createCell(1);
        Cell cell23 = row2.createCell(2);
        cell21.setCellValue("75");
        cell22.setCellValue("70");
        cell23.setCellValue("66");
 
        // 5、生成工作簿
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(WRITER_PATH + "考核成绩表03.xls");
            workbook.write(fileOutputStream);
            System.out.println("工作表生成成功！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                assert fileOutputStream != null;
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
 
    /**
     * 写入Excel-07版本
     */
    @Test
    public void excelTarget07() {
        // 1、创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 2、创建工作表
        Sheet sheet = workbook.createSheet("考核成绩表");
        // 3、创建一行
        Row row1 = sheet.createRow(0);
        // 4、创建一个列(一个单元格)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("语文");
 
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("数学");
 
        Cell cell13 = row1.createCell(2);
        cell13.setCellValue("英语");
 
 
        // 创建一行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        Cell cell22 = row2.createCell(1);
        Cell cell23 = row2.createCell(2);
        cell21.setCellValue("75");
        cell22.setCellValue("70");
        cell23.setCellValue("66");
 
        // 5、生成工作簿
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(WRITER_PATH + "考核成绩表07.xlsx");
            workbook.write(fileOutputStream);
            System.out.println("工作表生成成功！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                assert fileOutputStream != null;
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
 
    /**
     * 写入大数据量Excel-03版本
     */
    @Test
    public void excelTargetBigData03() {
        long beginTime = System.currentTimeMillis();
        // 1、创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 2、创建工作表
        Sheet sheet = workbook.createSheet("考核成绩表");
        for (int rowNum = 0; rowNum < 6553; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int coluNum = 0; coluNum < 10; coluNum++) {
                Cell cell = row.createCell(coluNum);
                cell.setCellValue(coluNum);
            }
        }
 
        // 5、生成工作簿
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(WRITER_PATH + "考核成绩表大数据写入03.xls");
            workbook.write(fileOutputStream);
            System.out.println("工作表生成成功！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                assert fileOutputStream != null;
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        System.out.println("耗时：" + ((double) (System.currentTimeMillis()) - beginTime) / 1000 + "s");
    }
 
    /**
     * 写入大数据量Excel-07-普通版
     * 写入10w条数据：9.149s
     */
    @Test
    public void excelTargetBigData07() {
        long beginTime = System.currentTimeMillis();
        // 1、创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 2、创建工作表
        Sheet sheet = workbook.createSheet("考核成绩表");
        for (int rowNum = 0; rowNum < 100000; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int coluNum = 0; coluNum < 10; coluNum++) {
                Cell cell = row.createCell(coluNum);
                cell.setCellValue(coluNum);
            }
        }
 
        // 5、生成工作簿
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(WRITER_PATH + "考核成绩表大数据写入07.xlsx");
            workbook.write(fileOutputStream);
            System.out.println("工作表生成成功！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                assert fileOutputStream != null;
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
 
        System.out.println("耗时：" + ((double) (System.currentTimeMillis()) - beginTime) / 1000 + "s");
    }
 
    /**
     * 写入大数据量Excel-07-缓存版本
     * 写入10w条数据：2.161s
     */
    @Test
    public void excelTargetBigDataCache07() {
        long beginTime = System.currentTimeMillis();
        // 1、创建工作簿
        Workbook workbook = new SXSSFWorkbook();
        // 2、创建工作表
        Sheet sheet = workbook.createSheet("考核成绩表");
        for (int rowNum = 0; rowNum < 100000; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int coluNum = 0; coluNum < 10; coluNum++) {
                Cell cell = row.createCell(coluNum);
                cell.setCellValue(coluNum);
            }
        }
 
        // 5、生成工作簿
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(WRITER_PATH + "考核成绩表大数据写入Cache07.xls");
            workbook.write(fileOutputStream);
            System.out.println("工作表生成成功！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                assert fileOutputStream != null;
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        System.out.println("耗时：" + ((double) (System.currentTimeMillis()) - beginTime) / 1000 + "s");
    }
 
    /**
     * 读取文件并判断列的类型
     *
     * @throws Exception 异常
     */
    @Test
    public void testCellType() throws Exception {
 
        //获取文件流
        FileInputStream fis = new FileInputStream(WRITER_PATH + "会员消费商品明细表.xls");
 
        //创建一个工作簿。使用 excel能操作的这边他都可以操作
        Workbook workbook = new HSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
 
        //获取标题内容
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            //得到一行有多少列有数据
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + "|");
                }
            }
            System.out.println();
        }
 
        // 遍历所有行
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
 
            // 遍历一行中的所有列
            if (rowData != null) {
                assert rowTitle != null;
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
 
                    Cell cell = rowData.getCell(cellNum);
                    //匹配列的数据类型
                    if (cell != null) {
                        CellType cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType) {
                            //字符
                            case STRING:
                                System.out.print("【 String】");
                                cellValue = cell.getStringCellValue();
                                break;
                            //布尔
                            case BOOLEAN:
                                System.out.print("【 BOOLEAN】");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            //空
                            case BLANK:
                                System.out.print("【 BLANK】");
                                break;
                            //数字(日期、普通数字)
                            case NUMERIC:
                                System.out.print("【 NUMERIC】");
                                // 日期
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    System.out.print("--【日期】");
                                    LocalDateTime localDateTimeCellValue = cell.getLocalDateTimeCellValue();
                                    cellValue = localDateTimeCellValue.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                                } else {
                                    //不是日期格式，防止数字过长！
                                    System.out.print("--【转换为字符串输出】");
                                    cell.setCellType(CellType.STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            //错误
                            case ERROR:
                                System.out.print("【 数据类型错误】");
                                break;
                            default:
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        //关闭流
        fis.close();
    }
 
 
    public static int f(String s1, String s2) {
        if (s1.length() == 0 || s2.length() == 0) {
            return 0;
        }
 
        if (s1.charAt(0) == s2.charAt(0)) {
            return f(s1.substring(1), s2.substring(1)) + 1;
        } else {
            return Math.max(f(s1.substring(1), s2), f(s1, s2.substring(1)));
        }
    }
 
}

三、EasyExcel(推荐)

官方文档地址：https://easyexcel.opensource.alibaba.com/docs/current/

自己跟着文档练习即可。

导入Maven坐标：

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

四、POI操作：使用自定义注解补充信息（Excel导出）

创建Maven项目，依赖如下

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>5.3.23</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.2</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.2</version>
    </dependency>
</dependencies>

定义注解

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
 * @author zhixi
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.TYPE})
@Documented
public @interface Excel {
 
    /**
     * 字段别名
     *
     * @return 字段名称
     */
    String name() default "";
 
    /**
     * 字典值
     *
     * @return 字典值，用逗号分割，比如："0=女,1=男"
     */
    String dictValue() default "";
}

实体类

?
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
public class User {
 
    @Excel(name = "用户ID")
    private String id;
 
    @Excel(name = "姓名")
    private String name;
 
    @Excel(name = "年龄")
    private int age;
 
    /**
     * 性别 0-女 1-男
     */
    @Excel(name = "性别", dictValue = "0=女,1=男")
    private int gender;
 
    public String getId() {
        return id;
    }
 
    public void setId(String id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public int getAge() {
        return age;
    }
 
    public void setAge(int age) {
        this.age = age;
    }
 
    public int getGender() {
        return gender;
    }
 
    public void setGender(int gender) {
        this.gender = gender;
    }
 
    @Override
    public String toString() {
        return "User{" +
                "name='" + name + '\'' +
                ", age=" + age +
                ", gender=" + gender +
                '}';
    }
 
    public User(String name, int age, int gender) {
        this.name = name;
        this.age = age;
        this.gender = gender;
    }
 
    public User(String id, String name, int age, int gender) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.gender = gender;
    }
}

导出Excel

?
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
package com.zhixi;
 
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
 
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;
 
public class ExcelExporter {
 
 
    public static void export(List<User> users, OutputStream outputStream) {
        try (Workbook workbook = new XSSFWorkbook()) {
            // 创建一个工作表
            Sheet sheet = workbook.createSheet();
 
            // 获取所有的属性
            Field[] declaredFields = User.class.getDeclaredFields();
            // 获取所有包含Excel注解的字段
            List<Field> filteredFields = Stream.of(declaredFields)
                    .filter(f -> f.isAnnotationPresent(Excel.class))
                    .collect(Collectors.toList());
 
            // 写入标题行
            writeTitleRow(workbook,sheet, filteredFields);
 
            // 写入数据行
            for (int i = 0; i < users.size(); i++) {
                User user = users.get(i);
                // 数据从第二行开始写入（一行是标题）
                Row userRow = sheet.createRow(i + 1);
                writeDataRow(userRow, user, filteredFields);
            }
 
            // 写入文件
            workbook.write(outputStream);
        } catch (IOException | IllegalAccessException e) {
            throw new RuntimeException(e);
        }
 
    }
 
    private static void writeTitleRow(Workbook workbook,Sheet sheet, List<Field> filteredFields) {
        // 创建新行
        Row row = sheet.createRow(0);
        // 设置表头加粗
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        cellStyle.setFont(font);
 
        for (int i = 0; i < filteredFields.size(); i++) {
            // 在所在行内创建新单元格
            Cell cell = row.createCell(i);
            Excel excel = filteredFields.get(i).getAnnotation(Excel.class);
            cell.setCellValue(excel.name());
            cell.setCellStyle(cellStyle);
        }
    }
 
    private static void writeDataRow(Row userRow, User user, List<Field> filteredFields) throws IllegalAccessException {
 
        // 构造字段的值字典(处理dictValue这个属性)
        Map<Field, Map<String, String>> fieldDict = buildFieldDict(filteredFields);
 
        for (int i = 0; i < filteredFields.size(); i++) {
            // 在行上面创建列，i表示列的下标
            Cell cell = userRow.createCell(i);
            Field field = filteredFields.get(i);
            field.setAccessible(true);
            // dictValue这个属性不为空，就从map字典中取出对应的值
            Excel annotation = field.getAnnotation(Excel.class);
            if (StringUtils.hasLength(annotation.dictValue())) {
                String key = String.valueOf(field.get(user));
                cell.setCellValue(fieldDict.get(field).get(key));
            } else {
                cell.setCellValue(String.valueOf(field.get(user)));
            }
            field.setAccessible(false);
        }
 
    }
 
    private static Map<Field, Map<String, String>> buildFieldDict(List<Field> filteredFields) {
        Map<Field, Map<String, String>> fieldDict = new HashMap<>();
 
        for (Field field : filteredFields) {
            Excel annotation = field.getAnnotation(Excel.class);
            if (StringUtils.hasLength(annotation.dictValue())) {
                String d = annotation.dictValue();
                String[] kvs = d.split(",");
                Map<String, String> map = new HashMap<>();
                for (String kv : kvs) {
                    String[] split = kv.split("=");
                    map.put(split[0], split[1]);
                }
                fieldDict.put(field, map);
            }
        }
        return fieldDict;
    }
 
    public static void main(String[] args) throws IOException {
        List<User> users = new ArrayList<>();
        users.add(new User("1", "张三", 20, 1));
        users.add(new User("2", "李四", 30, 0));
        users.add(new User("3", "王五", 40, 1));
        System.out.println(Paths.get(".").getFileName());
        OutputStream outputStream = Files.newOutputStream(Paths.get("users.xlsx"));
        ExcelExporter.export(users, outputStream);
    }
}

 

posted @   Java小白的搬砖路  阅读(530)  评论(0)  编辑  收藏  举报
编辑推荐：
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现
· 25岁的心里话
历史上的今天：
2020-12-28 8、Mybatis分页
2020-12-28 7、mybatis日志工厂
2020-12-28 6、ResultMap结果集映射-解决实体类属性名和数据库字段名不一致的问题
2020-12-28 5、mybatis生命周期和作用域

喜欢请打赏

扫描二维码打赏

支付宝打赏

点击右上角即可分享
微信分享提示
AI IDE Trae
❉
❉
❉
❉