POI的 HSSFWorkbook 操作(Web应用)
一、介绍
Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成,其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF,在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页和API文档
二、环境配置
1、maven环境
本案例是在SpringBoot环境下完成的,maven配置如下。
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.0.RELEASE</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--Excel解析-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</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
23
24
2、Jakarta POI HSSF API组件
常用组件:
HSSFWorkbook excel的文档对象
HSSFSheet excel的表单
HSSFRow excel的行
HSSFCell excel的格子单元
HSSFFont excel字体
HSSFDataFormat 日期格式
HSSFHeader sheet头
HSSFFooter sheet尾(只有打印的时候才能看到效果)
样式:
HSSFCellStyle cell样式
辅助操作包括:
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
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
3、基本操作步骤
一个Excel的文件的组成,一个Excel文件对应一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(HSSFSheet)组成,一个sheet是由多个row(HSSFRow)组成,一个row是由多个cell(HSSFCell)组成。
基本操作步骤:
1、用HSSFWorkbook打开或者创建“Excel文件对象”
2、用HSSFWorkbook对象返回或者创建Sheet对象
3、用Sheet对象返回行对象,用行对象得到Cell对象
4、对Cell对象读写。
1
2
3
4
5
6
7
这里举出一个下载Excel文件的简单例子(这里直接写到Controller层)
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) {
//创建HSSFWorkbook对象
HSSFWorkbook wb = new HSSFWorkbook();
//创建HSSFSheet对象
HSSFSheet sheet = wb.createSheet("sheet0");
//创建HSSFRow对象
HSSFRow row = sheet.createRow(0);
//创建HSSFCell对象
HSSFCell cell = row.createCell(0);
//设置单元格的值
cell.setCellValue("单元格");
//发送到客户端的响应的内容类型
response.setContentType("application/vnd.ms-excel;charset=utf-8");
try {
OutputStream os = response.getOutputStream();
//下载中文文件名的做法
// String fileName = URLEncoder.encode("下载Excel", "UTF-8");
// response.setHeader("Content-disposition","attachment;filename="+fileName+".xls");
response.setHeader("Content-disposition", "attachment;filename=test.xls");
wb.write(os);
os.flush();
os.close();
}catch (Exception e){
e.printStackTrace();
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
HSSF读取文件还是使用这几个对象,只是将相应的creatXXX方法变成了getXXX方法即可。
response.setContentType()解析
application/vnd.ms-excel ---------------- 响应的类型为excel文件
charset=utf-8 ---------------- 设置字符编码
详情了解请看这篇文章 response.setContentType()的作用及参数
response.setHeader()解析
Content-disposition 告诉浏览器这个文件的名字和类型
详情了解请看这篇文章 response.setHeader()的用法
三、主要方法
在实际应用中导出的Excel文件往往需要阅读和打印的,这就需要对输出的Excel文档进行排版和样式的设置,主要操作有合并单元格、设置单元格样式、设置字体样式等。
1、设置单元格行高,列宽,最终高度由字体的大小决定
HSSFSheet sheet = wb.createSheet("sheet0");
//设置默认行高 代表两个字符的高度
sheet.setDefaultRowHeight((short)(2*256));
//设置默认列宽
sheet.setDefaultColumnWidth(22);
//设置指定列宽,256 * 50这种写法是因为width参数单位是单个字符的256分之一
sheet.setColumnWidth(cell.getColumnIndex(), 256 * 50);
1
2
3
4
5
6
7
2、设置单元格样式
//创建HSSFCellStyle
HSSFCellStyle cellStyle = wb.createCellStyle();
//设置单元格纵向居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置单元格横向居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
/* 设置单元格的填充方式,以及前景颜色和背景颜色
三点注意:
1.如果需要前景颜色或背景颜色,一定要指定填充方式,两者顺序无所谓;
2.如果同时存在前景颜色和背景颜色,前景颜色的设置要写在前面;
3.前景颜色不是字体颜色。
4.颜色参考
https://blog.csdn.net/qq_38025219/article/details/82760471
*/
//设置填充方式(填充图案)
cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);
//设置前景色 一般情况设置前景
cellStyle.setFillForegroundColor(HSSFColor.RED.index);
//可以搭配 cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
//设置背景颜色
cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
// 设置单元格底部的边框及其样式和颜色
// 这里仅设置了底边边框,左边框、右边框和顶边框同理可设
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);
cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index);
//设置日期型数据的显示样式
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
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
颜色参考链接
四、读取用户上传的Excel文件简单案例
实现将已存在的Excel文件中的数据导入到系统中的基本步骤同导出十分的类似,关键在于要了解要导入Excel文件的结构,比如数据有哪些列、读取数据起始位置(有效数据从第几行几列开始)等。在实际项目中由于这些数据(Excel文件)往往来自于日常办公人员或其他系统平台产生的业务数据,因此这些Excel文件的数据格式要有统一的要求,并提供访问接口(指访问途径),这样在所需数据的系统中就可通过提供这个访问接口调用方法,从而获得数据。
1、上传Excel文件内容
2、前端form表单提交代码
注意enctype属性
<form action="/submitExcel" method="post" enctype="multipart/form-data" >
<input type="file" name="file" placeholder="选择文件">
<input type="submit" placeholder="提交">
</form>
```
1
2
3
4
```java
3、读取Excel文件代码
@RequestMapping("/submitExcel")
@ResponseBody
public String submitExcel(@RequestParam(value = "file",required = false) MultipartFile file){
Workbook workbook = null;
try {
//根据指定的文件输入流导入Excel
workbook = WorkbookFactory.create(file.getInputStream());
}catch (Exception e){
e.printStackTrace();
}
//获取Excel的第一个表单
Sheet sheet = workbook.getSheetAt(0);
int startNum = 1;
//获取最后一行
int endNum = sheet.getLastRowNum();
for (int i = startNum; i <= endNum ; i++) {
Row r = sheet.getRow(i);
System.out.println(r.getCell(0).getStringCellValue()+
"=="+r.getCell(1).getNumericCellValue()+
"=="+r.getCell(2).getNumericCellValue());
}
return "success";
}
代码示例 :
/**
* @Description
*/
@Test
public void test() {
// String fileName = (String) context.getDataValue("batchFileName");
String fileName = ""; // 文件地址
String strListTemp = "";
int totalQueryNum = 0;
File file = new File(fileName);
if (!file.exists()) {
log.error("errorCode", "CSR801002");
log.error("errorMsg", "文件不存在!");
return;
}
FileInputStream fis = null;
try {
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFRow row = null;
//可以捕获内存缓冲区的数据,转换成为字节数组
// ByteArraysInputStream 可以将字节数组转化为字节输入流
ByteArrayOutputStream byteOS = new ByteArrayOutputStream();
fis = new FileInputStream(fileName);
byte[] by = new byte[512];
int t = fis.read(by, 0, by.length);
while (t > 0) {
byteOS.write(by, 0, 512);
t = fis.read(by, 0, by.length);
}
byteOS.close(); // 释放转化为
InputStream byteIS = new ByteArrayInputStream(byteOS.toByteArray());
workbook = new HSSFWorkbook(byteIS);
while (true) {
String row1 = "";
String row2 = "";
String str = "";
if (totalQueryNum - 1 > 1000) {
log.error("errorCode", "CSR801005");
log.error("errorMsg", "上传总笔数最多不得超过1000笔");
return;
}
sheet = workbook.getSheetAt(0);
row = sheet.getRow(totalQueryNum);
if (row != null) {
if (totalQueryNum == 0) {
totalQueryNum++;
continue;
}
if (row.getCell((short) 0) != null) {
try {
row1 = nvl(row.getCell((short) 0).getStringCellValue());
} catch (Exception e) {
log.error("errorCode", "CSR801002");
log.error("errorMsg", "第" + (totalQueryNum + 1) + "行, Excel数据格式有误,请设置成文本格式或重新下载模板");
return ;
}
} else {
row1 = "";
}
if (row.getCell((short) 1) != null) {
try {
row2 = nvl(row.getCell((short) 1).getStringCellValue());
} catch (Exception e) {
log.error("errorCode", "CSR801002");
log.error("errorMsg", "第" + (totalQueryNum + 1) + "行, Excel数据格式有误,请设置成文本格式或重新下载模板");
return ;
}
} else {
row2 = "";
}
str = "@@@@" + row1 + "@" + row2 + "@@";
strListTemp += str + "#";
totalQueryNum++;
} else {
break;
}
}
} catch (Exception e) {
log.error("errorCode", "CSR801003");
log.error("errorMsg", "文件读取错误");
EMPLog.log(EMPConstance.EMP_FLOW, EMPLog.ERROR, 0, e.getMessage(), e);
return ;
} finally {
if (fis != null) {
try {
fis.close();
} catch (Exception e) {
log.error("文件读取失败");
}
}
}
}
private static String nvl(String s) {
return s = (null == s || "".equals(s.trim()) || "null".equals(s.trim())) ? "" : s.trim();
}
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