Java报表以及表格
文章目录
一、POI操作Excel
1.1引入依赖
<!-- 低版本Excel所依赖的jar -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<!-- 高版本Excel所依赖的jar -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<!-- 百万级别数据导出所依赖的jar -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
1.2 POI操作Excel高低版本区别
在POI包中有如下几个主要的对象和excel的几个对应
| 对应Excel名称 | 低版本中的类名 | 高版本中的类名 |
|---|---|---|
| 工作簿 | HSSFWordBook | XSSFWordBook |
| 工作表 | HSSFSheet | XSSFSheet |
| 行 | HSSFRow | XSSFRow |
| 单元格 | HSSFCell | XSSFCell |
| 单元格样式 | HSSFCellStyle | XSSFCellStyle |
1.3 入门案例
【需求】：创建一个新的工作簿，里面随便写一句话
1.3.1 低版本的Excel
这里使用的是Junit测试
@Test
public void testLowExcel() throws Exception, IOException {
// 创建一个工作簿 低版本的Excel
Workbook workbook = new HSSFWorkbook();
// 在工作簿中创建新的工作表
Sheet sheet = workbook.createSheet("POI操作Excel");
// 在工作表中创建行 参数是创建的行的角标
Row row = sheet.createRow(0);
// 在行中创建单元格
Cell cell = row.createCell(0);
// 在单元格中创建内容
cell.setCellValue("你好，这是一个简单的POI程序");
// 写出去
workbook.write(new FileOutputStream(new File("d:/testLow.xls")));
System.out.println("操作POI成功...");
}
1.3.2 高版本的Excel
@Test
public void testHeightExcel() throws Exception, IOException {
// 创建一个工作簿 低版本的Excel
Workbook workbook = new XSSFWorkbook();
// 在工作簿中创建新的工作表
Sheet sheet = workbook.createSheet("POI操作Excel");
// 在工作表中创建行 参数是创建的行的角标
Row row = sheet.createRow(0);
// 在行中创建单元格
Cell cell = row.createCell(0);
// 在单元格中创建内容
cell.setCellValue("你好，这是一个简单的POI程序");
// 写出去
workbook.write(new FileOutputStream(new File("d:/testHeight.xlsx")));
System.out.println("操作POI成功...");
}
1.3.3 读取并操作Excel
@Test
public void testReadExcel() throws FileNotFoundException, Exception {
// 有内容的工作簿
Workbook workbook = new XSSFWorkbook(new FileInputStream(new File("E://用户导入测试数据.xlsx")));
// 需要第一个sheet的内容
Sheet sheet = workbook.getSheetAt(0);
// 读取工作表中的内容
// 获取最后一行的索引值
int lastRowIndex = sheet.getLastRowNum();
Row row = null;
User user = null;
ArrayList<User> list = new ArrayList<User>();
for (int i = 1; i <= lastRowIndex; i++) {
user = new User();
row = sheet.getRow(i);
user.setUserName(row.getCell(0).getStringCellValue()); // 用户名
user.setPhone(row.getCell(1).getStringCellValue()); // 手机号
user.setProvince(row.getCell(2).getStringCellValue()); // 省份
user.setCity(row.getCell(3).getStringCellValue()); // 城市
Integer salary = ((Double)row.getCell(4).getNumericCellValue()).intValue();
user.setSalary(salary); // 工资
user.setHireDate(sdf.parse(row.getCell(5).getStringCellValue())); // 入职日期
user.setBirthday(sdf.parse(row.getCell(6).getStringCellValue())); // 出生日期
user.setAddress(row.getCell(7).getStringCellValue()); // 现住址
list.add(user);
}
for (User user2 : list) {
System.out.println(user2);
}
}
🔥小tips：如何防止手机号在Excel中单元格类型是数值型，但是Java中实体类型是String类型
try { // 如果说用户输入的手机号是一个字符串型的，那么这里使用getString的方 法来获取 user.setPhone(row.getCell(1).getStringCellValue()); // 手机号 } catch (Exception e) { // 如果说用户输入的手机号是一个数值型的，那么这>里使用getNumeric的方法来获取 // TODO Auto-generated catch block user.setPhone(row.getCell(1).getNumericCellValue()+""); // 手机号 }
1.3.4 用戶的导入操作
我们都知道使用SpringBoot操作的文件上传下载，实际上是使用了SpringMVC的MultipartFile这个类，所以这里导入也就不过是把Excel通过POI技术解析成对应的实体类完成一个插入数据库的操作。
【具体实现】：
/**
* 描述：上传Excel的Service层业务具体实现方法
*
* @Title: uploadExcel
* @param file
* @author weiyongpeng
* @date 2022年9月4日 上午9:17:59
*/
public void uploadExcel(MultipartFile file) throws Exception {
// TODO Auto-generated method stub
// 有内容的工作簿
org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(file.getInputStream());
// 需要第一个sheet的内容
Sheet sheet = workbook.getSheetAt(0);
// 读取工作表中的内容
// 获取最后一行的索引值
int lastRowIndex = sheet.getLastRowNum();
Row row = null;
User user = null;
ArrayList<User> list = new ArrayList<User>();
for (int i = 1; i <= lastRowIndex; i++) {
user = new User();
row = sheet.getRow(i);
user.setUserName(row.getCell(0).getStringCellValue()); // 用户名
try { // 如果说用户输入的手机号是一个字符串型的，那么这里使用getString的方法来获取
user.setPhone(row.getCell(1).getStringCellValue()); // 手机号
} catch (Exception e) { // 如果说用户输入的手机号是一个数值型的，那么这里使用getNumeric的方法来获取
// TODO Auto-generated catch block
user.setPhone(row.getCell(1).getNumericCellValue() + ""); // 手机号
}
user.setProvince(row.getCell(2).getStringCellValue()); // 省份
user.setCity(row.getCell(3).getStringCellValue()); // 城市
Integer salary = ((Double) row.getCell(4).getNumericCellValue()).intValue();
user.setSalary(salary); // 工资
user.setHireDate(sdf.parse(row.getCell(5).getStringCellValue())); // 入职日期
user.setBirthday(sdf.parse(row.getCell(6).getStringCellValue())); // 出生日期
user.setAddress(row.getCell(7).getStringCellValue()); // 现住址
userMapper.insert(user);
}
}
启动项目，点击选择文件，并选择需要导入的文件。
查看后台有无报错，如无报错，查看数据库是否有数据：
ok，文件导入操作也运行成功。
1.3.5 用户导出下载Excel
与用户上传导入操作相似，都是使用MultipartFile进行文件的相关操作。
【具体实现】：
/**
* 描述：使用POI导出用户数据的Service层代码
*
* @Title: downLoadXlsxByPoi
* @param response
* @author weiyongpeng
* @throws Exception
* @date 2022年9月4日 上午9:49:16
*/
public void downLoadXlsxByPoi(HttpServletResponse response) throws Exception {
/* ===============思路================== */
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// 创建一个全新的Excel工作簿 Workbook
org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook();
// 创建一个全新的工作表 Sheet
Sheet sheet = workbook.createSheet("用户数据Sheet");
sheet.setColumnWidth(0, 5*256);
sheet.setColumnWidth(1, 8*256);
sheet.setColumnWidth(2, 15*256);
sheet.setColumnWidth(3, 15*256);
sheet.setColumnWidth(4, 30*256);
// 在工作表中创建一个标题 title Row(0)
String[] title = new String[] { "编号", "姓名", "手机号", "入职日期", "现住址" };
Row t_row = sheet.createRow(0);
for (int i = 0; i < title.length; i++) {
Cell cell = t_row.createCell(i);
cell.setCellValue(title[i]);
}
// 遍历数据 创建数据行，
List<User> list = userMapper.selectAll();
for (int i = 0; i < list.size(); i++) {
Row row = sheet.createRow(i + 1);
// id列
Cell idcell = row.createCell(0);
idcell.setCellValue(list.get(i).getId());
// name列
Cell namecell = row.createCell(1);
namecell.setCellValue(list.get(i).getUserName());
// phone列
Cell phonecell = row.createCell(2);
phonecell.setCellValue(list.get(i).getPhone());
// hire_date列
Cell hireDatecell = row.createCell(3);
hireDatecell.setCellValue(sdf.format(list.get(i).getHireDate()));
// address列
Cell addresscell = row.createCell(4);
addresscell.setCellValue(list.get(i).getAddress());
}
// 写出数据
String filename = "POI导出Excel.xlsx";
response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(),"ISO8859-1"));
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
// 释放资源
workbook.close();
outputStream.close();
}
1.3.6 导出带样式的Excel
在日常的开发工作中，往往会遇到一些客户对Excel导出的样式有特殊的需求，这些我们都可以理解，毕竟每个人都有自己的审美和品味，只要有对应的及技术可以做到，那么我们就有办法实现客户的需求，
有办法想办法，没有办法，创造办法也要上。
常见的样式需求
- 字体的需求
- 标题的需求
- 边框线的需求
- 对齐方式
- 行高、列宽
- …
以上是我在工作中客户所提过的需求
在使用POI操作Excel的样式的时候，我们需要先进行创建样式的对象🔥🔥🔥：
// 创建样式
CellStyle cellStyle = workbook.createCellStyle();
创建完样式的对象后，我们便可以设置我们想要的样式了：
【字体的设置】
// 设置字体
Font font = workbook.createFont(); // 注意：字体需要由Wworkbook来创建
font.setFontName("黑体"); // 字体类型
font.setBold(true); // 加粗
font.setFontHeightInPoints((short) 18); // 字体大小
cellStyle.setFont(font);
【行高的设置】
// 创建一个行
Row row = sheet.createRow(0);
// 设置行高
row.setHeightInPoints(42);
【合并单元格】
// 合并单元格 参数： int firstRow, int lastRow, int firstCol, int lastCol
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
【水平垂直对齐】
// 设置对齐方式
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐
【边框设置】
// 边框线
cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
cellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
cellStyle.setBorderRight(BorderStyle.THIN); // 右边框
cellStyle.setBorderTop(BorderStyle.THIN); // 上边框
【小案例】
@Test
public void testExport() throws Exception {
// 创建一个新的工作簿
Workbook workbook = new XSSFWorkbook();
// 创建一个新的工作表
Sheet sheet = workbook.createSheet("有样式的sheet表");
// 创建标题 合并并居中 设置行高
sheet.setColumnWidth(0, 8 * 256);
sheet.setColumnWidth(1, 10 * 256);
sheet.setColumnWidth(2, 15 * 256);
sheet.setColumnWidth(3, 15 * 256);
sheet.setColumnWidth(4, 30 * 256);
// 合并单元格 参数： int firstRow, int lastRow, int firstCol, int lastCol
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
// 创建样式
CellStyle cellStyle = workbook.createCellStyle();
// 边框线
cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
cellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
cellStyle.setBorderRight(BorderStyle.THIN); // 右边框
cellStyle.setBorderTop(BorderStyle.THIN); // 上边框
// 设置对齐方式
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐
// 设置字体
Font font = workbook.createFont(); // 注意：字体需要由Wworkbook来创建
font.setFontName("黑体"); // 字体类型
font.setBold(true); // 加粗
font.setFontHeightInPoints((short) 18); // 字体大小
cellStyle.setFont(font);
// 创建一个行
Row row = sheet.createRow(0);
// 设置行高
row.setHeightInPoints(42);
String[] title = new String[] { "编号", "姓名", "手机号", "入职日期", "现住址" };
for (int i = 0; i < title.length; i++) {
Cell cell0 = row.createCell(0);
cell0.setCellStyle(cellStyle);
// 写入内容
cell0.setCellValue("用户数据");
}
// 正文列标题
Row row2 = sheet.createRow(1);
row2.setHeightInPoints((float) 31.5);
// 创建样式
CellStyle cellStyle2 = workbook.createCellStyle();
// 边框线
cellStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
cellStyle2.setBorderLeft(BorderStyle.THIN); // 左边框
cellStyle2.setBorderRight(BorderStyle.THIN); // 右边框
cellStyle2.setBorderTop(BorderStyle.THIN); // 上边框
Font font2 = workbook.createFont();
font2.setFontHeightInPoints((short) 12);
font2.setFontName("宋体");
font2.setBold(true);
cellStyle2.setAlignment(HorizontalAlignment.CENTER);
cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle2.setFont(font2);
for (int i = 0; i < title.length; i++) {
Cell cell = row2.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(cellStyle2);
}
// 查询数据并放入数据到Excel
// List<User> list = userService.findAll();
Row row3 = null;
// 创建样式
CellStyle cellStyle3 = workbook.createCellStyle();
// 边框线
cellStyle3.setBorderBottom(BorderStyle.THIN); // 下边框
cellStyle3.setBorderLeft(BorderStyle.THIN); // 左边框
cellStyle3.setBorderRight(BorderStyle.THIN); // 右边框
cellStyle3.setBorderTop(BorderStyle.THIN); // 上边框
Font font3 = workbook.createFont();
font3.setFontHeightInPoints((short) 10);
font3.setFontName("宋体");
// cellStyle3.setAlignment(HorizontalAlignment.CENTER);
// cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
for (int i = 0; i < 1; i++) {
row3 = sheet.createRow(2);
// 编号
Cell IdCell = row3.createCell(0);
IdCell.setCellValue("1");
IdCell.setCellStyle(cellStyle3);
// 姓名
Cell UserNameCell = row3.createCell(1);
UserNameCell.setCellValue("测试数据");
UserNameCell.setCellStyle(cellStyle3);
// 电话号码
Cell PhoneCell = row3.createCell(2);
PhoneCell.setCellValue("19891056567");
PhoneCell.setCellStyle(cellStyle3);
// 入职日期
Cell HireDateCell = row3.createCell(3);
HireDateCell.setCellValue("2022-04-18");
HireDateCell.setCellStyle(cellStyle3);
// 现住址
Cell AddressCell = row3.createCell(4);
AddressCell.setCellValue("山东省威海市新威大厦蓝海银行");
AddressCell.setCellStyle(cellStyle3);
}
workbook.write(new FileOutputStream(new File("e://带样式的Excel.xlsx")));
workbook.close();
}
有了以上的测试经验，我们便可以来优化一下我们的POI导出Excel的接口了：
/**
* 描述：带样式的导出Excel功能
*
* @Title: downLoadXlsxByPoiWithCellStyle
* @param response
* @author weiyongpeng
* @date 2022年9月6日 下午7:57:59
*/
public void downLoadXlsxByPoiWithCellStyle(HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub
// 创建一个新的工作簿
org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook();
// 创建一个新的工作表
Sheet sheet = workbook.createSheet("有样式的sheet表");
// 创建标题 合并并居中 设置行高
sheet.setColumnWidth(0, 8 * 256);
sheet.setColumnWidth(1, 10 * 256);
sheet.setColumnWidth(2, 15 * 256);
sheet.setColumnWidth(3, 15 * 256);
sheet.setColumnWidth(4, 30 * 256);
// 合并单元格 参数： int firstRow, int lastRow, int firstCol, int lastCol
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
// 创建样式
CellStyle cellStyle = workbook.createCellStyle();
// 边框线
cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
cellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
cellStyle.setBorderRight(BorderStyle.THIN); // 右边框
cellStyle.setBorderTop(BorderStyle.THIN); // 上边框
// 设置对齐方式
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐
// 设置字体
Font font = workbook.createFont(); // 注意：字体需要由Wworkbook来创建
font.setFontName("黑体"); // 字体类型
font.setBold(true); // 加粗
font.setFontHeightInPoints((short) 18); // 字体大小
cellStyle.setFont(font);
// 创建一个行
Row row = sheet.createRow(0);
// 设置行高
row.setHeightInPoints(42);
String[] title = new String[] { "编号", "姓名", "手机号", "入职日期", "现住址" };
for (int i = 0; i < title.length; i++) {
Cell cell0 = row.createCell(0);
cell0.setCellStyle(cellStyle);
// 写入内容
cell0.setCellValue("用户数据");
}
// 正文列标题
Row row2 = sheet.createRow(1);
row2.setHeightInPoints((float) 31.5);
// 创建样式
CellStyle cellStyle2 = workbook.createCellStyle();
// 边框线
cellStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
cellStyle2.setBorderLeft(BorderStyle.THIN); // 左边框
cellStyle2.setBorderRight(BorderStyle.THIN); // 右边框
cellStyle2.setBorderTop(BorderStyle.THIN); // 上边框
Font font2 = workbook.createFont();
font2.setFontHeightInPoints((short) 12);
font2.setFontName("宋体");
font2.setBold(true);
cellStyle2.setAlignment(HorizontalAlignment.CENTER);
cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle2.setFont(font2);
for (int i = 0; i < title.length; i++) {
Cell cell = row2.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(cellStyle2);
}
// 查询数据并放入数据到Excel
// List<User> list = userService.findAll();
Row row3 = null;
// 创建样式
CellStyle cellStyle3 = workbook.createCellStyle();
// 边框线
cellStyle3.setBorderBottom(BorderStyle.THIN); // 下边框
cellStyle3.setBorderLeft(BorderStyle.THIN); // 左边框
cellStyle3.setBorderRight(BorderStyle.THIN); // 右边框
cellStyle3.setBorderTop(BorderStyle.THIN); // 上边框
Font font3 = workbook.createFont();
font3.setFontHeightInPoints((short) 10);
font3.setFontName("宋体");
List<User> list = userMapper.selectAll();
System.out.println("数据条数：" + list.size());
// cellStyle3.setAlignment(HorizontalAlignment.CENTER);
// cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
for (int i = 0; i < list.size(); i++) {
row3 = sheet.createRow(2 + i);
// 编号
Cell IdCell = row3.createCell(0);
IdCell.setCellValue(list.get(i).getId());
IdCell.setCellStyle(cellStyle3);
// 姓名
Cell UserNameCell = row3.createCell(1);
UserNameCell.setCellValue(list.get(i).getUserName());
UserNameCell.setCellStyle(cellStyle3);
// 电话号码
Cell PhoneCell = row3.createCell(2);
PhoneCell.setCellValue(list.get(i).getPhone());
PhoneCell.setCellStyle(cellStyle3);
// 入职日期
Cell HireDateCell = row3.createCell(3);
HireDateCell.setCellValue(sdf.format(list.get(i).getHireDate()));
HireDateCell.setCellStyle(cellStyle3);
// 现住址
Cell AddressCell = row3.createCell(4);
AddressCell.setCellValue(list.get(i).getAddress());
AddressCell.setCellStyle(cellStyle3);
}
// 写出数据
String filename = "POI导出带样式的Excel.xlsx";
response.setHeader("content-disposition",
"attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
// 释放资源
workbook.close();
outputStream.close();
}
通过以上的代码我们可以看出，如果要想让Excel美观好看，我们的Java代码量是非常巨大的。如果我们使用纯的POI来到出，代码量是很巨大的。不切实际
1.3.7 基于模板的复杂样式的导出
基于模板的导出数据，我们可以理解为，像一个有样式的Excel表里，但是这个Excel没有数据，我们的操作就仅仅是往这个Excel里放数据即可。
【思路】：
- 我们准备一个模板，这个模板放到像目录下
- 我们读取这个模板
- 查询数据，放着个Excel模板里放数据
第一步，创建一个模板并放置在项目中
第二步，读取这个样式模板
在SpringBoot中，提供了一个工具类用于读取Resources目录下的资源文件
ResourceUtils.getURL("资源路径")
// 1、获取导出模板
String basePath = ResourceUtils.getURL("classpath:").getPath(); // 获取根目录
System.out.println("项目的根目录：" + basePath);
File rootFile = new File(basePath);
File templateFile = new File(rootFile, "/excel_template/userTemplate.xlsx");
/ 创建一个基于模板拿的WorkBook
org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(templateFile);
第三步，查询数据并放入模板
// 2、查询数据并往模板里放数据、
List<User> list = userMapper.selectAll();
// 3、放入到模板中
Sheet sheet = workbook.getSheetAt(0);
Row row3 = null;
Cell cell = null;
// 获取准备好的单元格样式
CellStyle cellStyle3 = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();
for (int i = 0; i < list.size(); i++) {
row3 = sheet.createRow(2 + i);
// 编号
cell = row3.createCell(0);
cell.setCellValue(list.get(i).getId());
cell.setCellStyle(cellStyle3);
// 姓名
cell = row3.createCell(1);
cell.setCellValue(list.get(i).getUserName());
cell.setCellStyle(cellStyle3);
// 电话号码
cell = row3.createCell(2);
cell.setCellValue(list.get(i).getPhone());
cell.setCellStyle(cellStyle3);
// 入职日期
cell = row3.createCell(3);
cell.setCellValue(sdf.format(list.get(i).getHireDate()));
cell.setCellStyle(cellStyle3);
// 现住址
cell = row3.createCell(4);
cell.setCellValue(list.get(i).getAddress());
cell.setCellStyle(cellStyle3);
}
// 4、导出文件
String filename = "POI导出基于模板的Excel.xlsx";
response.setHeader("content-disposition",
"attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
// 5、关闭资源
workbook.close();
outputStream.close();
8228
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
