基础依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-poi</artifactId>
<version>5.6.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!-- 推荐引入poi-ooxml,这个包会自动关联引入poi包,且可以很好的支持Office2007+的文档格式 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<!-- 如果需要使用Sax方式读取Excel,需要引入以下依赖(POI-4.x以上这个非必须)-->
<dependency>
<groupId>xerces</groupId>
<artifactId>xercesImpl</artifactId>
<version>2.12.0</version>
</dependency>
1. 输出自定义excel模板
@RequestMapping("getTemplate")
public void getTemplate(HttpServletResponse response) throws UnsupportedEncodingException {
// 设置模板第一行信息
List<String> row = CollUtil.newArrayList("姓名", "年龄", "性别", "地址");
List<List<String>> rows = CollUtil.newArrayList(Collections.singleton(row));
ExcelWriter writer = ExcelUtil.getWriter();
// 设置excel文件的文件名, 文件名需要设置code为utf-8 否则乱码
response.setContentType("application/vnd.ms-excel;charset=utf-8");
String fileName = URLEncoder.encode("上传模板", "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
// 写出
writer.write(rows, true);
ServletOutputStream out = null;
try {
out = response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
e.printStackTrace();
} finally {
writer.close();
}
IoUtil.close(out);
}
实现效果:
2. 输出数据库中的数据到excel中
实体bean
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelBean implements Serializable {
private String name;
private Integer age;
private String sex;
private String address;
}
逻辑代码
List<Person> personList = new ArrayList<>();
// 模拟数据库查询操作
for (int i = 0; i < 10; i++) {
personList.add(new Person("小"+i,i,"男","地址"+i));
}
ExcelWriter writer = ExcelUtil.getWriter();
// 输出别名
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("age", "年龄");
writer.addHeaderAlias("sex", "性别");
writer.addHeaderAlias("address", "地址");
writer.write(personList, true);
// 设置excel文件的文件名, 文件名需要设置code为utf-8 否则乱码
response.setContentType("application/vnd.ms-excel;charset=utf-8");
String fileName = URLEncoder.encode("下载文件", "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
ServletOutputStream out = null;
try {
out = response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
e.printStackTrace();
} finally {
writer.close();
}
IoUtil.close(out);
生成文件效果
3. 读取excel中的数据
逻辑代码
public static void main(String[] args) {
// 获取文件
ExcelReader reader = ExcelUtil.getReader("C:\\Users\\hj\\Downloads\\下载文件.xls");
// 校验表头是否符合输入要求
List<List<Object>> first = reader.read(0, 0);
AtomicReference<String> checkString = new AtomicReference<>("");
first.get(0).forEach(data ->{
checkString.updateAndGet(v -> v + data.toString());
});
System.out.println(checkString);
// 从第2行开始读取到最后一行
List<List<Object>> list = reader.read(1, reader.getRowCount());
List<Person> personList = new ArrayList<>();
list.forEach(data -> {
// 逐行读取excel的内容
Person person = new Person();
person.setName( data.get(0).toString());
person.setAge(Integer.parseInt(data.get(1).toString()));
person.setSex(data.get(2).toString());
person.setAddress(data.get(3).toString());
personList.add(person);
});
personList.forEach(System.out::println);
}
实现效果