言成言成啊 | Kit Chen's Blog

封装Excel快速业务开发模板

发布于2022-07-30 22:31:10，更新于2022-09-16 00:44:44，标签：java excel  文章会持续修订，转载请注明来源地址：https://meethigher.top/blog

主要是上班时，发现凡是涉及到excel的代码都有臭又烂，虽然保证了大家都是同样编程风格。

对于个人开发而言，再用那套又臭又烂的代码就不好了，所以结合工作经验，自己基于apache poi封装一套快速crud的。

一、封装

pom.xml

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
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>top.meethigher</groupId>
    <artifactId>excel-import</artifactId>
    <version>1.0.0</version>
    <name>excel-import</name>
    <description>excel-import</description>
    <properties>
        <!--指定idea中java编译版本-->
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <!--Java程序对Microsoft Office格式档案读和写的功能-->
        <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>

        <!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.34.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.github.gwenn/sqlite-dialect -->
        <dependency>
            <groupId>com.github.gwenn</groupId>
            <artifactId>sqlite-dialect</artifactId>
            <version>0.1.2</version>
        </dependency>

        <!--swagger支持-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>3.0.0</version>
        </dependency>

        <!--非空校验-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

1.1 Model校验

像@NotBlank、@NotNull、@Max等的校验，在Springboot中可以使用@Valid启用校验。

但是如果我想手动触发校验的话呢？

校验工具类

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
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

/**
 * 校验
 *
 * @author chenchuancheng github.com/meethigher
 * @since 2022/7/30 18:57
 */
public class ValidateUtil {


    private static final Validator validator = Validation.buildDefaultValidatorFactory().getValidator();

    private ValidateUtil() {
    }

    /**
     * 对{@link javax.validation}下的校验注解进行校验
     * 错误信息通过异常丢出。
     * 格式：msg1,msg2,msg3...
     *
     * @param t
     * @param <T>
     */
    public static <T> void validate(T t) {
        List<String> list = new LinkedList<>();
        Set<ConstraintViolation<T>> violations = validator.validate(t);
        for (ConstraintViolation<T> violation : violations) {
            String name = violation.getPropertyPath().toString();
            String message = violation.getMessage();
            list.add(name + message);
        }
        if (list.size() > 0) {
            throw new IllegalArgumentException(Arrays.toString(list.toArray()).replaceAll("\\]|\\[", ""));
        }
    }
}

结合实际业务，报错提示如图

1.2 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
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
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;

/**
 * excel工具类
 *
 * @author chenchuancheng github.com/meethigher
 * @since 2022/7/30 10:03
 */
public class ExcelUtil {

    /**
     * 支持的文件类型
     */
    public enum FileType {
        /**
         * name表示文件格式
         * fileCode文件格式对应的编号
         */
        XLS_2003("excel2003", "D0CF11E0"),
        XLSX_2007("excel2007", "504B0304");

        private final String name;

        private final String fileCode;


        FileType(String name, String fileCode) {
            this.name = name;
            this.fileCode = fileCode;
        }

        public String getName() {
            return name;
        }

        public String getFileCode() {
            return fileCode;
        }

        public static FileType getFileType(String fileCode) {
            for (FileType x : FileType.values()) {
                if (x.getFileCode().equals(fileCode)) {
                    return x;
                }
            }
            return null;
        }
    }


    /**
     * 获取浮点
     *
     * @param cell
     * @return
     */
    public static Double getDoubleCel(Cell cell) {
        String stringCellValue = getStringCel(cell);
        if (stringCellValue == null || stringCellValue.trim().length() <= 0) {
            return null;
        }
        try {
            return Double.valueOf(stringCellValue);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 获取字符串
     *
     * @param cell
     * @return
     */
    public static String getStringCel(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case BLANK:
                return "";
            default:
                return null;
        }
    }


    /**
     * 获取excel标题行的所有列内容
     *
     * @param xssfWorkbook
     * @return
     */
    public static String[] getExcelTitleCel(XSSFWorkbook xssfWorkbook) {
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        if (sheet == null) {
            return new String[0];
        }
        XSSFRow row = sheet.getRow(0);
        if (row == null) {
            return new String[0];
        }
        String[] strArr = new String[row.getPhysicalNumberOfCells()];
        for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
            strArr[i] = getStringCel(row.getCell(i));
        }
        return strArr;
    }

    /**
     * 校验excel的列是否符合模板规范
     *
     * @param realCel
     * @param validCel
     * @return
     */
    public static boolean isValidExcelCel(String[] realCel, String[] validCel) {
        boolean flag = true;
        for (int i = 0; i < validCel.length; i++) {
            try {
                String valid = validCel[i];
                String real = realCel[i];
                if (!valid.equals(real)) {
                    flag = false;
                    break;
                }
            } catch (Exception e) {
                flag = false;
                break;
            }
        }
        return flag;
    }

    /**
     * 校验是否为excel文件
     * .xls的所有大小写 和 .xlsx的所有大小写，都属于合法excel
     *
     * @param fileName
     * @return
     */
    public static boolean isValidExcel(String fileName) {
        if (fileName == null) {
            return false;
        }
        return fileName.matches("^.+\\.(?i)(xls)$")
                || fileName.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 校验文件类型
     *
     * @param file File
     * @return 文件类型
     */
    public static FileType isValidExcelType(MultipartFile file) {
        String fileCode = getFileCode(file);
        return FileType.getFileType(fileCode);
    }


    /**
     * 获取文件头
     * 前4个字节标识文件类型
     *
     * @param file 文件
     * @return 前4个字节转换的十六进制字符串
     */
    private static String getFileCode(MultipartFile file) {
        InputStream is = null;
        String value = null;
        try {
            is = file.getInputStream();
            byte[] b = new byte[4];
            int read = is.read(b, 0, b.length);
            if (read != -1) {
                value = bytesToHexString(b);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != is) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return value;
    }


    /**
     * 字节数组转换为十六进制
     *
     * @param bytes 字节数组
     * @return 字节数组转换后的十六进制
     */
    private static String bytesToHexString(byte[] bytes) {
        StringBuilder sb = new StringBuilder();
        if (bytes == null || bytes.length <= 0) {
            return null;
        }
        String s;
        for (byte b : bytes) {
            //十六进制0xff，即二进制11111111，即十进制255
            //计算机存储二进制是使用补码处理，正数的补码相同。b&0xff针对负数，做补码一致性，对正数不影响。
            //直白点说，就是byte范围是[-128,127]，通过b&0xff转换为范围为[0,255]的int
            s = Integer.toHexString(b & 0xFF).toUpperCase();
            if (s.length() < 2) {
                sb.append(0);
            }
            sb.append(s);
        }
        return sb.toString();
    }

}

1.3 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
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import top.meethigher.constant.ResponseEnum;
import top.meethigher.exception.WebCommonException;
import top.meethigher.rest.controller.service.CommonExcelService;
import top.meethigher.utils.ExcelUtil;

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

/**
 * 公共excel操作
 *
 * @author chenchuancheng github.com/meethigher
 * @since 2022/7/30 10:30
 */
@Service
public class CommonExcelServiceImpl implements CommonExcelService {
    @Override
    public void templateDown(HttpServletResponse response, String fileName, XSSFWorkbook xssfWorkbook) throws IOException {
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setContentType("application/octet-stream");
        // 下载文件能正常显示中文
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-Disposition",
                "attachment; filename=\"" + fileName + "\"; filename*=utf-8''" + fileName);
        // 实现文件下载
        OutputStream os = response.getOutputStream();
        xssfWorkbook.write(os);

    }

    @Override
    public XSSFWorkbook verifyExcel(MultipartFile file, XSSFWorkbook templateXssfWorkBook) throws WebCommonException, IOException {
        String originalFilename = file.getOriginalFilename();
        //校验扩展名
        if (!ExcelUtil.isValidExcel(originalFilename)) {
            throw new WebCommonException(ResponseEnum.EXCEL_NOT_SUPPORT);
        }
        //校验文件内容格式,MultipartFile获取流是多例的
        ExcelUtil.FileType fileType = ExcelUtil.isValidExcelType(file);
        if (fileType == null) {
            throw new WebCommonException(ResponseEnum.EXCEL_CONTENT_ERROR);
        }
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
        if (!ExcelUtil.isValidExcelCel(ExcelUtil.getExcelTitleCel(xssfWorkbook),
                ExcelUtil.getExcelTitleCel(templateXssfWorkBook))) {
            throw new WebCommonException(ResponseEnum.EXCEL_NOT_MATCH);
        }
        return xssfWorkbook;
    }
}

1.4 优雅地crud

meethigher/excel-import: 优雅地写excel crud

直接上图好了，本身也没啥技术含量的。

二、调优

在使用中，对象尽可能使用单例或者复用。不然在大数据量导入时，会创建大量的实例对象，导致效率严重低下。

我就拿校验工具类两个写法进行比较。

使用单例，耗时4秒。

使用多例，耗时50秒。

为了可以查看JVM中现存实例对象，可以使用jmap。

语法是jmap -histo:live PID

在linux环境下。

分页查看

1
jmap -histo:live 1793|less

动态查看实例对象数量，每隔一秒自动刷新

1
watch -n 1 "jmap -histo:live 2184|grep Total"

如图

三、插曲

今天无意间看了一个面试题

1
2
3
4
5
6
7
8
9
10
11
12
@Test
void name() {
    Integer a1=4;

    Integer a2=4;
    System.out.println(a1==a2);//true

    Integer a3=128;
    Integer a4=128;

    System.out.println(a3==a4);//false
}

具体原因这里讲得很清楚Integer127==127结果为true为什么Integer128==128结果为false？_x瓜皮的博客-CSDN博客

简而言之，Integer a1=128相当于Integer a1=Integer.valueOf(128)

四、关于&0xff的思考

4.1 原码, 反码和补码的概念

先说重点，计算机存储的是补码，Integer.toBinaryString获取的也是补码。

原码是方便人来转换十进制的，反码是原码与补码的桥梁，而补码是计算机用来存储的，同时也是用于 &、| 等计算时所使用的编码。

4.1.1 原码

原码就是符号位加上真值的绝对值, 即用第一位表示符号, 其余位表示值. 比如如果是8位二进制:

[+1]原 = 0000 0001

[-1]原 = 1000 0001

第一位是符号位. 因为第一位是符号位, 所以8位二进制数的取值范围就是:

[1111 1111 , 0111 1111]

即

[-127 , 127]

原码是人脑最容易理解和计算的表示方式.

4.1.2 反码

反码的表示方法是:

正数的反码是其本身

负数的反码是在其原码的基础上, 符号位不变，其余各个位取反.

[+1] = [00000001]原 = [00000001]反

[-1] = [10000001]原 = [11111110]反

可见如果一个反码表示的是负数, 人脑无法直观的看出来它的数值. 通常要将其转换成原码再计算.

4.1.3 补码

补码的表示方法是:

正数的补码就是其本身

负数的补码是在其原码的基础上, 符号位不变, 其余各位取反, 最后+1. (即在反码的基础上+1)

[+1] = [00000001]原 = [00000001]反 = [00000001]补

[-1] = [10000001]原 = [11111110]反 = [11111111]补

对于负数, 补码表示方式也是人脑无法直观看出其数值的. 通常也需要转换成原码在计算其数值.

4.2 理解&0xff

校验可以通过文件二进制头来校验，原因就是文件的前4个字节，标识了文件类型。这比扩展名更准确

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
/**
 * 获取文件头
 * 前4个字节标识文件类型
 *
 * @param file 文件
 * @return 前4个字节转换的十六进制字符串
 */
private static String getFileCode(File file) {
    InputStream is = null;
    String value = null;
    try {
        is = new FileInputStream(file);
        byte[] b = new byte[4];
        int read = is.read(b, 0, b.length);
        if (read != -1) {
            value = bytesToHexString(b);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (null != is) {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    return value;
}


/**
 * 字节数组转换为十六进制
 *
 * @param bytes 字节数组
 * @return 字节数组转换后的十六进制
 */
private static String bytesToHexString(byte[] bytes) {
    StringBuilder sb = new StringBuilder();
    if (bytes == null || bytes.length <= 0) {
        return null;
    }
    String s;
    for (byte b : bytes) {
        //十六进制0xff，即二进制11111111，即十进制255
        //计算机存储二进制是使用补码处理，正数的补码相同。b&0xff针对负数，做补码一致性，对正数不影响。
        //直白点说，就是byte范围是[-128,127]，通过b&0xff转换为范围为[0,255]的int
        s = Integer.toHexString(b & 0xFF).toUpperCase();
        if (s.length() < 2) {
            sb.append(0);
        }
        sb.append(s);
    }
    return sb.toString();
}

但是对字节进行&0xff，我不理解。

如果我从流中读一个字节，读成int，范围就是[0,255]

如果我从流中读一个字节，读成byte，范围却是[-128,127]

byte的[-128, -1]依次对应于int的[128,255]

如果byte=-1，则(int) byte=-1，这肯定是不对的，正确的应该是255。所以&0xff的目的是为了将(int) byte转换为正确的int值。

下面演示下转换过程

通过上图可知，

[-1]补 = 11111111111111111111111111111111

[255]补 = 11111111（即原码）

&运算符，如果运算的两个二进制相同位，值都为1，则该位得1，否则0。

所有的计算都是使用补码。

[-1]补&[255]补=[x]补

[x]补=00000000000000000000000011111111

由于符号位是0，表示是正数

所以[x]原=[x]反=[x]补

x=255

即-1&255=255

五、参考致谢

byte为什么要与上0xff？ - 陈其苗 - 博客园

原码/反码/补码计算器 - 一个工具箱 - 好用的在线工具都在这里！

java基础–Java 字节读取流的read方法返回int的原因_奉天逍遥19的博客-CSDN博客

发布：2022-07-30 22:31:10
修改：2022-09-16 00:44:44
链接：https://meethigher.top/blog/2022/quick-excel-crud/
标签：java excel 
付款码 打赏 分享
访客数calculating人
Shift+Ctrl+1 可控制工具栏