Apache POI｜Excel读取导入、解析工具类
#教程 #工具 [字体 小·中·大]操作 Excel 设置单元格样式是比较繁琐的，还有导入导出是设置的 Header 信息，为了简化这些操实现了如下工具类。工具类具有设置单元格的基本背景颜色、字体、字号、字体颜色、边框等，还实现了导入导出、磁盘读写 Excel。
这个工具类来源于我们项目的实践，简化了一些重复性的工作，欢迎尝试，欢迎提出问题。
预览 & 功能
1.设置简单的单元格样式。如字体、字号、对齐方式、颜色、边框
2.便捷实现下拉框
3.提供一组默认属性方便设置行高和字体
4.提供上传文件的读取（读取 Request 流）、下载文件导出（导出到 Response 流）、指定磁盘文件导出、指定磁盘文件读取
5.Excel 数据解析为 Java List
实现
导出一个 excel 大致的代码
这是使用工具类来完成一个表格的生成到导出。
1 // 导出赛事
2 public HSSFWorkbook downloadTemplate(Long competitionId, HttpServletResponse response) {
3 // work book
4 HSSFWorkbook workbook = new HSSFWorkbook();
5 String sheetName = "导入模板";
6
7 // sheet
8 HSSFSheet sheet = workbook.createSheet(sheetName);
9
10 // 默认高度 16像素
11 sheet.setDefaultRowHeightInPoints(16F);
12
13 //设置指定列宽
14 sheet.setColumnWidth(0, 14 * 256);
15 sheet.setColumnWidth(1, 14 * 256);
16 sheet.setColumnWidth(2, 16 * 256);
17 sheet.setColumnWidth(3, 16 * 256);
18 sheet.setColumnWidth(4, 32 * 256);
19 sheet.setColumnWidth(5, 64 * 256);
20
21 // 单元格样式
22 HSSFCellStyle testStyle = new ExcelUtil.HSSFStyleBuilder()
23 .createStyle(workbook)
24 .font("宋体", (short) 11, true)
25 .colorRed()
26 .alignCenter()
27 .backgroundBlue()
28 .build();
29
30 // 单元格样式
31 HSSFCellStyle tipStyle = new ExcelUtil.HSSFStyleBuilder()
32 .createStyle(workbook)
33 .font("宋体", (short) 11, true)
34 .horizontalAlignLeft()
35 .verticalAlignCenter()
36 .wrapText()
37 .build();
38
39 // 单元格样式
40 HSSFCellStyle titleRedStyle = new ExcelUtil.HSSFStyleBuilder()
41 .createStyle(workbook)
42 .font("宋体", (short)11, true)
43 .colorRed()
44 .alignCenter()
45 .wrapText()
46 .build();
47
48 // 单元格样式
49 HSSFCellStyle titleBlackStyle = new ExcelUtil.HSSFStyleBuilder()
50 .createStyle(workbook)
51 .font("宋体", (short)11, true)
52 .colorBlack()
53 .alignCenter()
54 .wrapText()
55 .build();
56
57 // 设置单元格合并
58 CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 5);
59 sheet.addMergedRegion(rowRegion);
60
61 // 第一行 row=0
62 HSSFRow row = sheet.createRow(0);
63
64 Competition competition = adminCompetitionMapper.selectById(competitionId);
65
66 row.setHeightInPoints(110);
67 HSSFCell cell = row.createCell(0);
68 StringBuilder info = new StringBuilder(256);
69 info.append(sheetName).append("赛事编号为：<").append(competitionId).append(">\r\n")
70 .append("赛事名称为：").append(competition.getName()).append("\r\n")
71 .append("说明信息：\r\n")
72 .append("1) 请不要将此模板应用用在除(").append(competition.getName()).append(")以为的赛事。\r\n")
73 .append("2) 红色标题的字段必填。“序号”字段不做要求，性别、参数经验、赛事名称可从下拉列表框选取。\r\n")
74 .append("3) 填在此表格中的用户必须已注册在系统中，需保证手机号码正确。\r\n")
75 .append("4) 填写时，行与行之间禁止留有空行，禁止修改标题，不要动其他信息。");
76
77 // 设置说明信息
78 cell.setCellValue(info.toString());
79 cell.setCellStyle(tipStyle);
80
81 // 第二行 row=1
82 row = sheet.createRow(1);
83 row.setHeightInPoints(18F);
84
85 // 标题 excelTitles = new String[] {"序号","用户名","手机号","性别（男/女）","有无参赛经验（有/无）","赛事名称"};
86 ExcelUtil.cellSetter(row.createCell(0), titleBlackStyle).setCellValue(excelTitles[0]);
87 ExcelUtil.cellSetter(row.createCell(1), titleRedStyle).setCellValue(excelTitles[1]);
88 ExcelUtil.cellSetter(row.createCell(2), titleRedStyle).setCellValue(excelTitles[2]);
89 ExcelUtil.cellSetter(row.createCell(3), titleRedStyle).setCellValue(excelTitles[3]);
90 ExcelUtil.cellSetter(row.createCell(4), titleRedStyle).setCellValue(excelTitles[4]);
91 ExcelUtil.cellSetter(row.createCell(5), titleRedStyle).setCellValue(excelTitles[5]);
92
93 //设置约束，下拉列表
94 ExcelUtil.createDataValidation(sheet, new String[]{"男", "女"}, 2, 1000, 3, 3);
95 ExcelUtil.createDataValidation(sheet, new String[]{"有", "无"}, 2, 1000, 4, 4);
96
97 // 导出
98 ExcelUtil.exportXlsExcel(response, "赛事导入模板", workbook);
99 }
以下会单独讲工具类的使用。
单元格样式：字体、字号、对齐方式、颜色、边框
通过工具类可以创建简单的单元格样式，如果想看具体的样式如何设置请看工具类 HSSFStyleBuilder 类的具体设置。
设置宋体、11 号字体、加粗、黑色字体、文本超出换行、细线边框、蓝色背景、居中对齐。
1// 生成style对象
2HSSFCellStyle titleStyle = new ExcelUtil.HSSFStyleBuilder(wb)
3 .font("宋体", (short)11, true)
4 .alignCenter()
5 .colorBlack()
6 .backgroundBlue()
7 .border()
8 .wrapText()
9 .build();
10// 使用样式
11ExcelUtil.cellSetter(row.createCell(0), titleBlackStyle).setCellValue("序号");
12
13// ExcelUtil.cellSetter方法实现如下, 它仅完成设置单元格样式这一步
14public static HSSFCell cellSetter(@NotNull HSSFCell cell, HSSFCellStyle style){
15 cell.setCellStyle(style);
16 return cell;
17}
实现下拉框效果
在需要进行数据验证，限定一部分数据的时候，可以使用下拉框。
设置后的下拉框仅对数据进行验证，默认不选择任何一条数据。
1// 通过工具类为sheet的第四列设置只能填入男或女验证的下拉框
2ExcelUtil.createDataValidation(sheet, new String[]{"男", "女"}, 2, 1000, 3, 3);
3
4//ExcelUtil.createDataValidation 类的实现
5public static void createDataValidation(@NotNull Sheet sheet, @NotNull String[] options,
6 int firstRow, int lastRow, int firstCol, int lastCol) {
7 // 下拉框
8 DataValidationHelper helper = sheet.getDataValidationHelper();
9
10 // CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)设置行列范围
11 CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
12
13 DataValidationConstraint constraint = helper.createExplicitListConstraint(options);
14 DataValidation dataValidation = helper.createValidation(constraint, addressList);
15
16 //处理Excel兼容性问题
17 if (dataValidation instanceof XSSFDataValidation) {
18 dataValidation.setSuppressDropDownArrow(true);
19 dataValidation.setShowErrorBox(true);
20 } else {
21 dataValidation.setSuppressDropDownArrow(false);
22 }
23 sheet.addValidationData(dataValidation);
24}
使用默认一些值
通常在导出的 excel 只需要设置行高，然后所有的列根据内容自适应就行了。
标题字体推荐 11、12 字号，行高 18 像素。
正文字体推荐 10 号字体，行高 16 像素。
设置示例：
1 // 整体默认高度，另外，不推荐设置默认列宽
2 sheet.setDefaultRowHeightInPoints(16F);
3
4 // 设置指定列宽
5 sheet.setColumnWidth(0, 14 * 256);
6 sheet.setColumnWidth(1, 14 * 256);
7
8 // 字号通过HSSFStyleBuilder设置
9 HSSFCellStyle testStyle = new ExcelUtil.HSSFStyleBuilder()
10 .createStyle(workbook)
11 // 字体，字号，是否加粗
12 .font("宋体", (short) 11, true)
13 .build();
14
15 // ---------------分割线----------------------
16
17 // 原始方式设置字体样式 style
18 HSSFFont font = workbook.createFont();
19 HSSFCellStyle style = workbook.createCellStyle();
20 // 设置字体
21 font.setFontName(DEFAULT_FONT_NAME);
22 // 设置字号
23 font.setFontHeightInPoints((short)11);
24 // 加粗
25 font.setBold(true);
26 // 设置字体
27 style.setFont(font);
以下是工具类默认常量，也是推荐的行高、列宽、字号的值。
1// 以下是一些常用参数，在创建workbook后可以通过一些设置默认参数
2// 默认，正文推荐字体、字号
3public static final String DEFAULT_FONT_NAME = "宋体";
4public static final short DEFAULT_FONT_SIZE = 10;
5
6// 标题推荐字体和行高
7public static final short TITLE_FONT_SIZE = 12;
8public static final float TITLE_ROW_HEIGHT_POINT = 18F;
9
10// 推荐行高列宽
11public static final float DEFAULT_ROW_HEIGHT_POINT = 16F;
12public static final int DEFAULT_COL_WIDTH = 10 * 256;
工作簿导入导出、读取、写入
excel 导入 - 读取 request 流中的数据
1 // 工具类方法，导出从流中读取并生成工作簿Workbook
2 public static Workbook readWorkBook(@NotNull InputStream input) {
3 Workbook wb = null;
4 try {
5 wb = WorkbookFactory.create(input);
6 } catch (InvalidFormatException | IOException e) {
7 e.printStackTrace();
8 }
9 return wb;
10 }
11
12 // 使用
13 1. 通过MultipartFile或HttptServletRequest对象获取输入流: file.getInputStream() 或 request.getInputStream()
14 2. 调用工具类方法
excel 导出 - 输出到 response 流
掉用工具类如下方法。
1 /**
2 * 导出 .xls （excel 2007）格式的excel
3 *
4 * @param response HttpServletResponse
5 * @param fileName excel文件名称
6 * @param workbook 工作簿
7 * @throws IOException
8 */
9 public static void exportXlsExcel(@NotNull HttpServletResponse response, @NotNull String fileName,
10 @NotNull HSSFWorkbook workbook) throws IOException {
11 fileName = new String(fileName.getBytes("GB2312"), StandardCharsets.ISO_8859_1);
12 fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
13
14 // 设置响应体
15 response.setContentType("application/vnd.ms-excel;charset=utf-8");
16 response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
17 // 将Excel工作簿数据写入字节输出流
18 ByteArrayOutputStream os = new ByteArrayOutputStream();
19 workbook.write(os);
20 // 发送数据
21 doExport(os, response);
22 }
读取磁盘 Excel、写入磁盘 Excel
工具类方法。
1/**
2* Description：以 “绝对路径+文件名” 读取一个excel
3*<pre>
4* win:
5* ExcelUtil.readWorkBook("D:\\workspace\\测试数据.xls");
6*
7* unix/linux:
8* ExcelUtil.readWorkBook("/root/workspace/测试数据.xls");
9*</pre>
10* @param abstractPathname 全路径名称
11* @return
12*/
13static Workbook readWorkBook(@NotNull String abstractPathname);
解析 excel 数据
传入一个工作簿，将每个 sheet 的数据解析出来放到 List 中。解析出来的均为 String 类型。
1 /**
2 * Description：读取workbook的数据
3 *
4 * @param wb 工作簿
5 * @return
6 */
7 private static List<List<List<String>>> analysisWorkBook(@NotNull Workbook wb) {
8 Sheet sheet = null;
9 Row row = null;
10 List<List<List<String>>> excelDataList = null;
11 // 解析数据
12 if (wb != null) {
13 try {
14 int sheetNumber = wb.getNumberOfSheets();
15 excelDataList = new ArrayList<>(sheetNumber);
16 // 循环页签
17 for (int sheetNum = 0; sheetNum < sheetNumber; sheetNum++) {
18 // 指定页签的值
19 sheet = wb.getSheetAt(sheetNum);
20
21 int lastRowNum = sheet.getLastRowNum();
22
23 // 定义存放一个页签中所有数据的List, 容量为行总数
24 List<List<String>> sheetList = new ArrayList<>(lastRowNum);
25
26 // 循环行
27 for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
28 // 指定行的值
29 row = sheet.getRow(rowNum);
30 if(row == null){
31 continue;
32 }
33
34 short lastCellNum = row.getLastCellNum();
35
36 // 定义存放一行数据的List
37 List<String> rowList = new ArrayList<>(lastCellNum);
38 // 循环列
39 for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
40 Cell cell = sheet.getRow(rowNum).getCell(cellNum);
41 rowList.add(getStringCellValue(cell));
42 }
43 sheetList.add(rowList);
44 }
45 excelDataList.add(sheetList);
46 }
47 } catch (Exception e) {
48 e.printStackTrace();
49 throw new RuntimeException("解析Excel出错");
50 }
51 } else {
52 throw new RuntimeException("工作簿 work book 读取为null");
53 }
54 return excelDataList;
55 }
工具类源码
这个工具类产生于我们项目的实践，并经过测试使用，目前无问题，若出现问题可评论反馈。
工具类依赖 Apache POI jar 包，Web 上 Excel 导入导出依赖 Servlet API。
pom 文件 POI 依赖如下：
1<dependency>
2 <groupId>org.apache.poi</groupId>
3 <artifactId>poi</artifactId>
4 <version>3.17</version>
5</dependency>
6<dependency>
7 <groupId>org.apache.poi</groupId>
8 <artifactId>poi-ooxml</artifactId>
9 <version>3.17</version>
10</dependency>
工具类代码
1package marchsoft.modules.admin.utils;
2
3import lombok.extern.slf4j.Slf4j;
4import org.apache.poi.hssf.usermodel.HSSFCell;
5import org.apache.poi.hssf.usermodel.HSSFCellStyle;
6import org.apache.poi.hssf.usermodel.HSSFFont;
7import org.apache.poi.hssf.usermodel.HSSFWorkbook;
8import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
9import org.apache.poi.ss.usermodel.*;
10import org.apache.poi.ss.util.CellRangeAddressList;
11import org.apache.poi.xssf.usermodel.XSSFDataValidation;
12import org.apache.poi.xssf.usermodel.XSSFWorkbook;
13
14import javax.servlet.ServletOutputStream;
15import javax.servlet.http.HttpServletResponse;
16import javax.validation.constraints.NotNull;
17import java.io.*;
18import java.net.URLEncoder;
19import java.nio.charset.StandardCharsets;
20import java.text.SimpleDateFormat;
21import java.util.ArrayList;
22import java.util.Date;
23import java.util.List;
24import java.util.regex.Pattern;
25
26/**
27 * Description: excel工具类, 导出格式总为xls（excel 2007）
28 *
29 * @author: liuqichun
30 * @date: 2021/4/15 11:16
31 */
32@Slf4j
33public class ExcelUtil {
34
35 /**
36 * Description: 为单元格设置style的工具方法，使用如下。
37 *
38 * <p>示例：
39 * <pre>
40 * ExcelUtil.cellSetter(row.createCell(0),titleStyle).setCellValue("序号");
41 * </pre>
42 *
43 * @param cell HSSFCell 单元格
44 * @param style 要设置的样式
45 * @return
46 */
47 public static HSSFCell cellSetter(@NotNull HSSFCell cell, HSSFCellStyle style){
48 cell.setCellStyle(style);
49 return cell;
50 }
51
52 /**
53 * Description：以 “绝对路径+文件名” 读取一个excel
54 *<pre>
55 * win:
56 * ExcelUtil.readExcelFormDisk("D:\\workspace\\测试数据.xls");
57 *
58 * unix/linux:
59 * ExcelUtil.readExcelFormDisk("/root/workspace/测试数据.xls");
60 *</pre>
61 * @param abstractPathname 全路径名称
62 * @return
63 */
64 public static List<List<List<String>>> readExcelFormDisk(@NotNull String abstractPathname) {
65 Workbook wb = readWorkBook(abstractPathname);
66 return analysisWorkBook(wb);
67 }
68
69 /**
70 * 读取一个流中的数据，常用在上传是request中的input流。
71 * @param input
72 * @return
73 */
74 public static List<List<List<String>>> readExcelFormInputStream(@NotNull InputStream input) {
75 Workbook wb = readWorkBook(input);
76 return analysisWorkBook(wb);
77 }
78
79 /**
80 * 读取excel文件
81 *
82 * @param abstractPathname 文件全路径名称
83 * @return org.apache.poi.ss.usermodel.Workbook
84 */
85 public static Workbook readWorkBook(@NotNull String abstractPathname) {
86 Workbook wb = null;
87 File file = new File(abstractPathname);
88 if (file.isDirectory() || !file.exists()) {
89 throw new RuntimeException("path 是目录而非文件或文件不存在");
90 }
91 try {
92 wb = WorkbookFactory.create(file);
93 } catch (InvalidFormatException | IOException e) {
94 e.printStackTrace();
95 }
96 return wb;
97 }
98
99 /**
100 * Description: 读输入流生成工作簿对象Workbook
101 *
102 * @param input 输入流（通常是request中）
103 * @return org.apache.poi.ss.usermodel.Workbook
104 */
105 public static Workbook readWorkBook(@NotNull InputStream input) {
106 Workbook wb = null;
107 try {
108 wb = WorkbookFactory.create(input);
109 } catch (InvalidFormatException | IOException e) {
110 e.printStackTrace();
111 }
112 return wb;
113 }
114
115 /**
116 * 创建excel
117 *
118 * @param workbook 工作簿
119 * @param abstractPathname 文件全路径名
120 */
121 public static void createExcel(@NotNull Workbook workbook, @NotNull String abstractPathname) {
122 FileOutputStream fileOut = null;
123 try {
124 fileOut = new FileOutputStream(abstractPathname);
125 workbook.write(fileOut);
126 } catch (Exception e) {
127 log.error("Error create excel: {}", e.getMessage());
128 } finally {
129 try {
130 if (fileOut != null) {
131 fileOut.close();
132 }
133 } catch (IOException e) {
134 e.printStackTrace();
135 }
136 }
137 }
138
139 /**
140 * Description：将工作簿数据写入文件
141 *
142 * @param workbook 工作簿
143 * @param file 一个文件
144 * @throws IOException io异常
145 */
146 public static void writeWorkBookToDisk(Workbook workbook, File file) throws IOException {
147 if (workbook == null || file == null) {
148 throw new RuntimeException("参数为null");
149 }
150 if (!file.exists()) {
151 try {
152 file.createNewFile();
153 } catch (IOException e) {
154 log.error("创建file文件失败");
155 throw new RuntimeException("创建file文件失败");
156 }
157 }
158 if (file.isDirectory()) {
159 throw new RuntimeException("file为目录而非文件");
160 }
161
162 // work book 写出字节数据
163 ByteArrayOutputStream os = new ByteArrayOutputStream();
164 workbook.write(os);
165
166 // Excel 数据 src
167 byte[] content = os.toByteArray();
168 os.close();
169
170 // 输出
171 FileOutputStream out = new FileOutputStream(file);
172
173 // 缓存传输
174 bufferTransfer(content, out);
175 }
176
177 /**
178 * Description: 判断一个字符串是否为：整数、小数、科学计数小数. 常用来判断手机号码
179 *
180 * @param str 字符串
181 * @return boolean
182 * @author: liuqichun
183 * @date: 2021/4/17 14:58
184 */
185 public static boolean isNumeric(String str) {
186 if (null == str || "".equals(str)) {
187 return false;
188 }
189 String regx = "[+-]*\\d+\\.?\\d*[Ee]*[+-]*\\d+";
190 Pattern pattern = Pattern.compile(regx);
191 boolean isNumber = pattern.matcher(str).matches();
192 if (isNumber) {
193 return isNumber;
194 }
195 regx = "^[-\\+]?[.\\d]*$";
196 pattern = Pattern.compile(regx);
197 return pattern.matcher(str).matches();
198 }
199
200 /**
201 * Description: 为sheet创建下拉列表
202 *
203 * <pre>
204 * 示例：
205 *
206 * //为sheet设置下拉列表框，范围是第4列从第3行到1001行
207 * ExcelUtil.createDataValidation(sheet, new String[]{"男", "女"}, 2, 1000, 3, 3);
208 * </pre>
209 *
210 * @param sheet
211 * @param options String array {"选项一", "选项二", "选项三",};
212 * @param firstRow 第一行
213 * @param lastRow 最后一行
214 * @param firstCol 第一列
215 * @param lastCol 最后一列
216 * @author: liuqichun
217 * @date: 2021/4/16 10:39
218 */
219 public static void createDataValidation(@NotNull Sheet sheet, @NotNull String[] options,
220 int firstRow, int lastRow, int firstCol, int lastCol) {
221 // 下拉框
222 DataValidationHelper helper = sheet.getDataValidationHelper();
223
224 // CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)设置行列范围
225 CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
226
227 DataValidationConstraint constraint = helper.createExplicitListConstraint(options);
228 DataValidation dataValidation = helper.createValidation(constraint, addressList);
229
230 //处理Excel兼容性问题
231 if (dataValidation instanceof XSSFDataValidation) {
232 dataValidation.setSuppressDropDownArrow(true);
233 dataValidation.setShowErrorBox(true);
234 } else {
235 dataValidation.setSuppressDropDownArrow(false);
236 }
237 sheet.addValidationData(dataValidation);
238 }
239
240 /**
241 * 导出 .xls （excel 2007）格式的excel
242 *
243 * @param response HttpServletResponse
244 * @param fileName excel文件名称
245 * @param workbook 工作簿
246 * @throws IOException
247 */
248 public static void exportXlsExcel(@NotNull HttpServletResponse response, @NotNull String fileName,
249 @NotNull HSSFWorkbook workbook) throws IOException {
250 fileName = new String(fileName.getBytes("GB2312"), StandardCharsets.ISO_8859_1);
251 fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
252
253 // 设置响应体
254 response.setContentType("application/vnd.ms-excel;charset=utf-8");
255 response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
256 // 将Excel工作簿数据写入字节输出流
257 ByteArrayOutputStream os = new ByteArrayOutputStream();
258 workbook.write(os);
259 // 发送数据
260 doExport(os, response);
261 }
262
263 /**
264 * 导出 .xlsx 格式的excel
265 *
266 * @param response HttpServletResponse
267 * @param fileName excel文件名称
268 * @param workbook 工作簿
269 * @throws IOException
270 */
271 public static void exportXlsxExcel(@NotNull HttpServletResponse response, @NotNull String fileName,
272 @NotNull XSSFWorkbook workbook)
273 throws IOException {
274
275 fileName = new String(fileName.getBytes("GB2312"), StandardCharsets.ISO_8859_1);
276 fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
277
278 // 设置响应体
279 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
280 response.addHeader("Content-Disposition", "attachment;filename="
281 + new String((fileName + ".xlsx").getBytes(), StandardCharsets.UTF_8));
282
283 // 将Excel工作簿数据写入字节输出流
284 ByteArrayOutputStream os = new ByteArrayOutputStream();
285 workbook.write(os);
286 // 发送数据
287 doExport(os, response);
288 }
289
290 // -------------------------------------- private methods ----------------------------------------------------------
291 /**
292 * Description：读取workbook的数据
293 *
294 * @param wb 工作簿
295 * @return
296 */
297 private static List<List<List<String>>> analysisWorkBook(@NotNull Workbook wb) {
298 Sheet sheet = null;
299 Row row = null;
300 List<List<List<String>>> excelDataList = null;
301 // 解析数据
302 if (wb != null) {
303 try {
304 int sheetNumber = wb.getNumberOfSheets();
305 excelDataList = new ArrayList<>(sheetNumber);
306 // 循环页签
307 for (int sheetNum = 0; sheetNum < sheetNumber; sheetNum++) {
308 // 指定页签的值
309 sheet = wb.getSheetAt(sheetNum);
310
311 int lastRowNum = sheet.getLastRowNum();
312
313 // 定义存放一个页签中所有数据的List, 容量为行总数
314 List<List<String>> sheetList = new ArrayList<>(lastRowNum);
315
316 // 循环行
317 for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
318 // 指定行的值
319 row = sheet.getRow(rowNum);
320 if(row == null){
321 continue;
322 }
323
324 short lastCellNum = row.getLastCellNum();
325
326 // 定义存放一行数据的List
327 List<String> rowList = new ArrayList<>(lastCellNum);
328 // 循环列
329 for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
330 Cell cell = sheet.getRow(rowNum).getCell(cellNum);
331 rowList.add(getStringCellValue(cell));
332 }
333 sheetList.add(rowList);
334 }
335 excelDataList.add(sheetList);
336 }
337 } catch (Exception e) {
338 e.printStackTrace();
339 throw new RuntimeException("解析Excel出错");
340 }
341 } else {
342 throw new RuntimeException("工作簿 work book 读取为null");
343 }
344 return excelDataList;
345 }
346
347 /**
348 * 导出处理
349 *
350 * @param output 输出流
351 * @param response 输出流对象，为 HttpServletResponse 中的 ServletOutputStream
352 * @throws IOException
353 */
354 private static void doExport(ByteArrayOutputStream output, HttpServletResponse response) throws IOException {
355 // Excel 所有数据 src
356 byte[] content = output.toByteArray();
357 output.close();
358
359 // 输出
360 ServletOutputStream sout = response.getOutputStream();
361
362 // 缓存传输
363 bufferTransfer(content, sout);
364 }
365
366 /**
367 * Description: 缓存传输数据
368 * @param content 传输内存 baty array
369 * @param out 输出流
370 * @author: liuqichun
371 * @date: 2021/4/18 11:02
372 */
373 private static void bufferTransfer(byte[] content, OutputStream out) {
374 // 传输内容作为输入流
375 ByteArrayInputStream in = new ByteArrayInputStream(content);
376
377 // 缓存传输
378 try (
379 // 输入输出缓冲
380 BufferedInputStream bis = new BufferedInputStream(in);
381 BufferedOutputStream bos = new BufferedOutputStream(out)
382 ) {
383 // 缓冲池
384 byte[] buff = new byte[2048];
385 // 读取字节数量
386 int bytesRead;
387 // Simple read/write loop.
388 while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
389 bos.write(buff, 0, bytesRead);
390 }
391 } catch (Exception e) {
392 log.error("导出xls的Excel出现异常:", e);
393 }
394 }
395
396 /**
397 * Description: 转换单元格的值
398 *
399 * @param cell
400 * @return String
401 */
402 private static String getStringCellValue(Cell cell) {
403 String cellvalue = "";
404
405 if (cell == null) {
406 return cellvalue;
407 }
408
409 switch (cell.getCellTypeEnum()) {
410 case STRING:
411 cellvalue = cell.getStringCellValue();
412 break;
413 case NUMERIC:
414 if (DateUtil.isCellDateFormatted(cell)) {
415 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
416 Date date = cell.getDateCellValue();
417 cellvalue = sdf.format(date);
418 } else {
419 cellvalue = String.valueOf(cell.getNumericCellValue());
420 }
421 break;
422 case BOOLEAN:
423 cellvalue = String.valueOf(cell.getBooleanCellValue());
424 break;
425 default: cellvalue="";
426 }
427 return cellvalue;
428 }
429
430 // ------------------------------- static inner class --------------------------------------------------------------
431 /**
432 * Description: 构建HSSFCellStyle工具, 使用这个工具可以轻松构建简单的excel样式而不必反复设置各种参数。
433 * 使用方法参考下方示例。
434 *
435 * <p> 示例:
436 * <pre>
437 * HSSFCellStyle testStyle = new ExcelUtil.HSSFStyleBuilder()
438 * .createStyle(workbook)
439 * .font("宋体", (short) 11, true)
440 * .colorRed()
441 * .alignCenter()
442 * .backgroundBlue()
443 * .build();
444 * </pre>
445 * @author: liuqichun
446 * @date: 2021/4/17 17:14
447 */
448 public static class HSSFStyleBuilder{
449 private HSSFCellStyle style;
450 private HSSFFont font;
451
452 // 以下是一些常用参数，在创建workbook后可以通过一些设置默认参数
453 // 默认，正文推荐字体、字号
454 public static final String DEFAULT_FONT_NAME = "宋体";
455 public static final short DEFAULT_FONT_SIZE = 10;
456
457 // 标题推荐字体和行高
458 public static final short TITLE_FONT_SIZE = 12;
459 public static final float TITLE_ROW_HEIGHT_POINT = 18F;
460
461 // 推荐行高列宽
462 public static final float DEFAULT_ROW_HEIGHT_POINT = 16F;
463 public static final int DEFAULT_COL_WIDTH = 10 * 256;
464
465
466 /**
467 * <p> 示例:
468 * <pre>
469 * // 宋体11号加粗，红色字体，单元格居中，蓝色背景
470 * HSSFCellStyle testStyle = new ExcelUtil.HSSFStyleBuilder()
471 * .createStyle(workbook)
472 * .font("宋体", (short) 11, true)
473 * .colorRed()
474 * .alignCenter()
475 * .backgroundBlue()
476 * .build();
477 * </pre>
478 */
479 public HSSFStyleBuilder(){
480
481 }
482
483 /**
484 * <p> 示例:
485 * <pre>
486 * // 宋体11号加粗，红色字体，单元格居中，蓝色背景
487 * HSSFCellStyle testStyle = new ExcelUtil.HSSFStyleBuilder(workbook)
488 * .font("宋体", (short) 11, true)
489 * .colorRed()
490 * .alignCenter()
491 * .backgroundBlue()
492 * .build();
493 * </pre>
494 */
495 public HSSFStyleBuilder(@NotNull HSSFWorkbook workbook){
496 style = workbook.createCellStyle();
497 font = workbook.createFont();
498 }
499
500 /**
501 * 创建Style，这个使用builder的第一步。如果创建builder时传入workbook则不用执行此方法。
502 *
503 * @param workbook 工作簿
504 * @return HSSFStyleBuilder
505 */
506 public HSSFStyleBuilder createStyle(@NotNull HSSFWorkbook workbook){
507 style = workbook.createCellStyle();
508 font = workbook.createFont();
509 return this;
510 }
511
512 /**
513 * 设置默认字体
514 * @return HSSFStyleBuilder
515 */
516 public HSSFStyleBuilder font(){
517 preCheck();
518 font(null,null,null);
519 return this;
520 }
521
522 /**
523 * 设置字体
524 *
525 * @param fontName 字体名称
526 * @param isBold 是否加粗
527 * @return
528 */
529 public HSSFStyleBuilder font(String fontName, Boolean isBold){
530 preCheck();
531 font(fontName, null, isBold);
532 return this;
533 }
534
535 /**
536 * 设置字体
537 *
538 * @param fontName 字体名称
539 * @param size 字号
540 * @param isBold 是否加粗
541 * @return HSSFStyleBuilder
542 */
543 public HSSFStyleBuilder font(String fontName, Short size, Boolean isBold){
544 preCheck();
545 if (fontName != null){
546 font.setFontName(fontName);
547 }else{
548 font.setFontName(DEFAULT_FONT_NAME);
549 }
550
551 if( size != null ){
552 font.setFontHeightInPoints(size);
553 }else{
554 font.setFontHeightInPoints(DEFAULT_FONT_SIZE);
555 }
556
557 if(isBold != null){
558 font.setBold(isBold);
559 }
560 return this;
561 }
562
563 /**
564 * 设置红色字体
565 *
566 * @return HSSFStyleBuilder
567 */
568 public HSSFStyleBuilder colorRed(){
569 preCheck();
570 font.setColor(HSSFFont.COLOR_RED);
571 return this;
572 }
573
574 /**
575 * 设置黑色字体（默认字体颜色）
576 * @return
577 */
578 public HSSFStyleBuilder colorBlack(){
579 preCheck();
580 font.setColor(HSSFFont.COLOR_NORMAL);
581 return this;
582 }
583
584 /**
585 * 字体居中对齐
586 *
587 * @return HSSFStyleBuilder
588 */
589 public HSSFStyleBuilder alignCenter(){
590 preCheck();
591 style.setVerticalAlignment(VerticalAlignment.CENTER);
592 style.setAlignment(HorizontalAlignment.CENTER);
593 return this;
594 }
595
596 /**
597 * 字体垂直靠上对齐
598 *
599 * @return HSSFStyleBuilder
600 */
601 public HSSFStyleBuilder verticalAlignTop(){
602 preCheck();
603 style.setVerticalAlignment(VerticalAlignment.TOP);
604 return this;
605 }
606
607 /**
608 * 字体垂直居中对齐
609 *
610 * @return HSSFStyleBuilder
611 */
612 public HSSFStyleBuilder verticalAlignCenter(){
613 preCheck();
614 style.setVerticalAlignment(VerticalAlignment.CENTER);
615 return this;
616 }
617
618 /**
619 * 字体垂直底部对齐
620 *
621 * @return HSSFStyleBuilder
622 */
623 public HSSFStyleBuilder verticalAlignBottom(){
624 preCheck();
625 style.setVerticalAlignment(VerticalAlignment.BOTTOM);
626 return this;
627 }
628
629 /**
630 * 字体水平靠左对齐
631 * @return HSSFStyleBuilder
632 */
633 public HSSFStyleBuilder horizontalAlignLeft(){
634 preCheck();
635 style.setAlignment(HorizontalAlignment.LEFT);
636 return this;
637 }
638
639 /**
640 * 字体水平居中对齐
641 *
642 * @return HSSFStyleBuilder
643 */
644 public HSSFStyleBuilder horizontalAlignCenter(){
645 preCheck();
646 style.setAlignment(HorizontalAlignment.CENTER);
647 return this;
648 }
649
650 /**
651 * 字体水平靠右对齐
652 *
653 * @return HSSFStyleBuilder
654 */
655 public HSSFStyleBuilder horizontalAlignRight(){
656 preCheck();
657 style.setAlignment(HorizontalAlignment.RIGHT);
658 return this;
659 }
660
661 /**
662 * 红色背景
663 *
664 * @return HSSFStyleBuilder
665 */
666 public HSSFStyleBuilder backgroundRed(){
667 preCheck();
668 style.setFillForegroundColor(IndexedColors.RED.getIndex());
669 style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
670 return this;
671 }
672
673 /**
674 * 灰色背景
675 *
676 * @return HSSFStyleBuilder
677 */
678 public HSSFStyleBuilder backgroundGray(){
679 preCheck();
680 style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
681 style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
682 return this;
683 }
684
685 /**
686 * 绿色背景
687 *
688 * @return HSSFStyleBuilder
689 */
690 public HSSFStyleBuilder backgroundGreen(){
691 preCheck();
692 style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
693 style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
694 return this;
695 }
696
697 /**
698 * 蓝色背景
699 *
700 * @return HSSFStyleBuilder
701 */
702 public HSSFStyleBuilder backgroundBlue(){
703 preCheck();
704 style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
705 style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
706 return this;
707 }
708
709 /**
710 * 细线黑色边框
711 *
712 * @return HSSFStyleBuilder
713 */
714 public HSSFStyleBuilder border(){
715 preCheck();
716 //设置下边框的线条粗细（有14种选择，可以根据需要在BorderStyle这个类中选取）
717 style.setBorderBottom(BorderStyle.THIN);
718 //设置下边框的边框线颜色（颜色和上述的颜色对照表是一样的）
719 style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
720 style.setBorderLeft(BorderStyle.THIN);//左边框
721 style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
722 style.setBorderTop(BorderStyle.THIN); //上边框
723 style.setRightBorderColor(IndexedColors.BLACK.getIndex());
724 style.setBorderRight(BorderStyle.THIN);//右边框
725 style.setTopBorderColor(IndexedColors.BLACK.getIndex());
726 return this;
727 }
728
729 /**
730 * 文本超出自动换行，如果一个单元格写多行也许开启换行功能
731 *
732 * @return
733 */
734 public HSSFStyleBuilder wrapText(){
735 preCheck();
736 style.setWrapText(true);
737 return this;
738 }
739
740 /**
741 * 获取构建的Style
742 *
743 * @return
744 */
745 public HSSFCellStyle build(){
746 preCheck();
747 style.setFont(font);
748 return style;
749 }
750
751 /**
752 * 检查是否初始化
753 */
754 private void preCheck(){
755 if (style == null) {
756 throw new RuntimeException("请先初始化style，调用create方法");
757 }
758 }
759 }
760}
总结
这个工具类操作的大多是 HSSFWorkbook(excel 2008, xls), 如果是 XSSFWorkbook 那就要再写一些方法了，不过目前常见的 Excel 导入导出完全可以胜任。