Java教程：使用POI读取excel文档（根据BV1bJ411G7Aw整理）

最近公司需要我做一个导出Excel表格的功能，为此来学习一下POI，在这里记录一下学习笔记。B站直接搜BV1bJ411G7Aw就能找到视频。

一、简介
在这里插入图片描述
poi如何操作表格

二、入门案例

2.1 从Excel文件读取数据
2.1.1 步骤文字描述

1.创建工作簿
2.获取工作表
3.遍历工作表获得行对象
4.遍历行对象获取单元格对象
5.获得单元格中的值

2.2.2 代码
先在pom中导入依赖在这里插入图片描述

<!--poi--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.14</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.14</version></dependency>

在桌面(你喜欢的地方)创建一个xlsx，输入一些简单数据
在这里插入图片描述

按照步骤书写代码

// 1.获取工作簿XSSFWorkbook workbook = new XSSFWorkbook("C:\\Users\\ghost\\Desktop\\hello.xlsx");// 2.获取工作表// xlsx第一个工作簿(Sheet1)，下标从0开始，0就是第一个XSSFSheet sheet = workbook.getSheetAt(0);/*使用加强for循环的方式*/// 3.获取行for (Row row : sheet) {// 4.获取单元格for (Cell cell: row) {// 获取单元格中的内容String value = cell.getStringCellValue();System.out.println(value);}}// 释放资源workbook.close();

运行代码
我们会发现能够正常读到，但是因为我们也打开着这个文档，所以显示被占用
在这里插入图片描述
关掉再运行就一切正常了

我们再试试不使用加强for循环的方式

// 1.获取工作簿XSSFWorkbook workbook = new XSSFWorkbook("C:\\Users\\ghost\\Desktop\\hello.xlsx");// 2.获取工作表// xlsx第一个工作簿(Sheet1)，下标从0开始，0就是第一个XSSFSheet sheet = workbook.getSheetAt(0);/*使用普通循环的方式*/// 开始索引(0) 结束索引int lastRowNum = sheet.getLastRowNum(); //行的结束索引for (int i = 0; i <= lastRowNum; i++) {// 拿到行XSSFRow row = sheet.getRow(i);if (row != null){short cellNum = row.getLastCellNum(); //单元格的结束索引for (int j = 0; j <= cellNum; j ++){XSSFCell cell = row.getCell(j);// 单元格不为空就去拿他的值if (cell != null){String stringCellValue = cell.getStringCellValue();System.out.println(stringCellValue);}}}}// 释放资源workbook.close();

能够正常运行
在这里插入图片描述

2.2 向Excel文件写入数据
2.2.1 步骤文字描述

1.创建一个Excel文件
2.创建工作表
3.创建行
4.创建单元格赋值
5.通过输出流将对象下载到磁盘

2.2.2 代码
在这里插入图片描述

// 1.创建工作簿XSSFWorkbook workbook = new XSSFWorkbook();// 2.创建工作表XSSFSheet sheet = workbook.createSheet("工作表一");// 3.创建行XSSFRow row1 = sheet.createRow(0); //第一行// 创建单元格row1.createCell(0).setCellValue("众"); //第一行第一格row1.createCell(1).setCellValue("子"); //第一行第二格row1.createCell(2).setCellValue("之"); //第一行第三格row1.createCell(3).setCellValue("爹"); //第一行第四格XSSFRow row2 = sheet.createRow(1); //第二行// 创建单元格row2.createCell(0).setCellValue("众"); //第二行第一格row2.createCell(1).setCellValue("子"); //第二行第二格row2.createCell(2).setCellValue("之"); //第二行第三格row2.createCell(3).setCellValue("爹"); //第二行第四格// 输出流FileOutputStream out = new FileOutputStream("C:\\Users\\ghost\\Desktop\\hello1.xlsx");// 写入workbook.write(out);// 刷新流out.flush();// 释放资源out.close();workbook.close();// 给个成功提示System.out.println("写入成功!");

运行代码
在这里插入图片描述
回到桌面查看可以看到多出来一个hello1

三、配合数据库使用

3.1 读取Excel文件数据
首先在随便一个地方（我这里在桌面）创建一个product.xlsx
在这里插入图片描述
然后写入一些数据

创建数据库

创建实体类(getset方法、有参无参构造、toString)

这里使用控制台来代替前端页面，主要是展示功能代码

public class PioShow {public static void main(String[] args) throws IOException {// 通过键盘录入ScannerScanner scanner = new Scanner(System.in);System.out.println("请输入你要选择的功能：1、导入；2、导出");int num = scanner.nextInt();if (num == 1){// 1.导入// 1.1读取excel表中的数据System.out.println("请输入您要读取的文件位置（不包含空格）：");String path = scanner.next();List<Pio> pioList = read(path);System.out.println(pioList);// 1.2将数据接入到数据库中}else if (num == 2){// 2.导出// 2.1读取数据库中的数据// 2.2将数据写入到excel表格中}else {System.out.println("输入有误，请重新启动!");}}public static List<Pio> read(String path) throws IOException {// 用来存储下面的存储单元格的listList<Pio> pioList = new ArrayList<>();// 1.获取工作簿XSSFWorkbook xssfWorkbook = new XSSFWorkbook(path);// 2.获取工作表XSSFSheet sheet = xssfWorkbook.getSheetAt(0); //0代表第一个工作表int lastRowNum = sheet.getLastRowNum();// i=1，第一行的商品编号、商品名称、商品价格（单位：元/斤）、商品库存（单位：吨）不拿，不然报错java.lang.NumberFormatException: For input string: "商品编号"for (int i = 1; i <= lastRowNum; i++) {// 拿到行XSSFRow row = sheet.getRow(i);if (row != null){// 用户存储每次获取的单元格内容List<String> list = new ArrayList<>();// 拿到单元格for (Cell cell : row){if (cell != null){// 手动设置为字符串类型，避免乱码cell.setCellType(Cell.CELL_TYPE_STRING);// 获取单元格中的内容String value = cell.getStringCellValue();// 防止某些单元格为空if (value != null && !"".equals(value)){// 获取到的单元格内容放到list里面list.add(value);}}}if (list.size() > 0){// 0-3对应：主键，商品名称，商品价格，商品库存。除了商品名称本身就是String类型，其他全部要转换为对应类型Pio pio = new Pio(Integer.parseInt(list.get(0)), list.get(1), Double.parseDouble(list.get(2)), Integer.parseInt(list.get(3)));// 封装到List里pioList.add(pio);}}}return pioList;}
}

启动，然后输入文件路径，可以看到能够读取到里面的数据
在这里插入图片描述
最后添加保存到数据库的代码(具体见视频)

3.2 写出到Excel文件
这里以springBoot为例（之前自己写的一个小说网站），需要jdbc的例子的到视频看

注释掉的部分是固定写到桌面；这里用的是jsp，等待jsp返回地址，不为空就执行导出到excel
在这里插入图片描述

// 从数据库读取数据写到Excel中@RequestMapping("/writePio")public String writePio(String path) throws IOException {//FileSystemView fsv = FileSystemView.getFileSystemView();  //注意了，这里重要的一句//System.out.println(fsv.getHomeDirectory());               //得到桌面路径//path = fsv.getHomeDirectory() + "\\aaa.xlsx";// 2.导出// 2.1读取数据库中的数据List<Pio> pioList = novelService.selectPioAll();// 2.2将数据写入到excel表格中if(path != null) {write(pioList, path);System.out.println("写入成功！");}return "writePio";}

novelService.selectPioAll()的内容，就是简单的查询表里全部字段

在这里插入图片描述

write方法，正常需要封装到util里，这里就随便放了
在这里插入图片描述
百分比可以做通过率或者什么比例的数据展示

public void write(List<Pio> pioList, String path) throws IOException {// 1.创建一个工作簿XSSFWorkbook xssfWorkbook = new XSSFWorkbook();// 2.创建工作表XSSFSheet sheet = xssfWorkbook.createSheet("商品");// 3.创建行XSSFRow row = sheet.createRow(0);row.createCell(0).setCellValue("商品编号");row.createCell(1).setCellValue("商品名称");row.createCell(2).setCellValue("商品价格（单位：元/斤）");row.createCell(3).setCellValue("商品库存（单位：吨）");DecimalFormat df = new DecimalFormat("0.0");//设置保留位数，下面除法的百分比用到。直接用"/"是保留整数的除，"%"是取余，都不适用for (int i = 0; i < pioList.size(); i++) {XSSFRow row1 = sheet.createRow(i + 1);row1.createCell(0).setCellValue(pioList.get(i).getPid());row1.createCell(1).setCellValue(pioList.get(i).getPname());row1.createCell(2).setCellValue(pioList.get(i).getPrice() + "/" + pioList.get(i).getPrice() + "(" + df.format((float)pioList.get(i).getPrice()/pioList.get(i).getPrice()*100) + "%" + ")");row1.createCell(3).setCellValue(pioList.get(i).getPstock());}FileOutputStream fileOutputStream = new FileOutputStream(path);xssfWorkbook.write(fileOutputStream);fileOutputStream.flush();fileOutputStream.close();xssfWorkbook.close();}

jsp里的内容
在这里插入图片描述

数据库的内容
在这里插入图片描述
在主界面随便找个地方作为入口

到主界面点进去

输入地址，这里输入在桌面创建一个product.xlsx

回到桌面查看

3.3 展示数据和一键导出为Excel表格
controller层

// 从数据库读取数据写到Excel中@RequestMapping("/writePio")public String writePio(String path, HttpServletResponse servletResponse, Model model) throws IOException {/*FileSystemView fsv = FileSystemView.getFileSystemView();  //注意了，这里重要的一句System.out.println(fsv.getHomeDirectory());               //得到桌面路径path = fsv.getHomeDirectory() + "\\aaa.xlsx";*/// 2.导出// 2.1读取数据库中的数据List<Pio> pioList = novelService.selectPioAll();model.addAttribute("pioList", pioList);// 2.2将数据写入到excel表格中if(path != null) {write(pioList, path);download(path, servletResponse);System.out.println("写入成功！");}return "writePio";}

调用Uitl里的两个方法：write，download
write方法
在这里插入图片描述

public void write(List<Pio> pioList, String path) throws IOException {// 1.创建一个工作簿XSSFWorkbook xssfWorkbook = new XSSFWorkbook();// 2.创建工作表XSSFSheet sheet = xssfWorkbook.createSheet("商品");// 3.创建行XSSFRow row = sheet.createRow(0);row.createCell(0).setCellValue("商品编号");row.createCell(1).setCellValue("商品名称");row.createCell(2).setCellValue("商品价格（单位：元/斤）");row.createCell(3).setCellValue("商品库存（单位：吨）");DecimalFormat df = new DecimalFormat("0.0");//设置保留位数，下面除法的百分比用到。直接用"/"是保留整数的除，"%"是取余，都不适用for (int i = 0; i < pioList.size(); i++) {XSSFRow row1 = sheet.createRow(i + 1);row1.createCell(0).setCellValue(pioList.get(i).getPid());row1.createCell(1).setCellValue(pioList.get(i).getPname());row1.createCell(2).setCellValue(pioList.get(i).getPrice() + "/" + pioList.get(i).getPrice() + "(" + df.format((float)pioList.get(i).getPrice()/pioList.get(i).getPrice()*100) + "%" + ")");row1.createCell(3).setCellValue(pioList.get(i).getPstock());}FileOutputStream fileOutputStream = new FileOutputStream(path);xssfWorkbook.write(fileOutputStream);fileOutputStream.flush();fileOutputStream.close();xssfWorkbook.close();}

download方法
在这里插入图片描述

 /*** @param path     想要下载的文件的路径* @param response* @功能描述 下载文件: 将文件以流的形式一次性读取到内存，通过响应输出流输出到前端*/@RequestMapping("/download")public void download(String path, HttpServletResponse response) {try {// path是指想要下载的文件的路径File file = new File(path);System.out.println(file.getPath());// 获取文件名String filename = file.getName();// 获取文件后缀名String ext = filename.substring(filename.lastIndexOf(".") + 1).toLowerCase();System.out.println(("文件后缀名：" + ext));// 将文件写入输入流FileInputStream fileInputStream = new FileInputStream(file);InputStream fis = new BufferedInputStream(fileInputStream);byte[] buffer = new byte[fis.available()];fis.read(buffer);fis.close();// 清空responseresponse.reset();// 设置response的Headerresponse.setCharacterEncoding("UTF-8");// Content-Disposition的作用：告知浏览器以何种方式显示响应返回的文件，用浏览器打开还是以附件的形式下载到本地保存// attachment表示以附件方式下载   inline表示在线打开   "Content-Disposition: inline; filename=文件名.mp3"// filename表示文件的默认名称，因为网络传输只支持URL编码的相关支付，因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));// 告知浏览器文件的大小response.addHeader("Content-Length", "" + file.length());OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());response.setContentType("application/octet-stream");outputStream.write(buffer);outputStream.flush();} catch (IOException ex) {ex.printStackTrace();}}

数据库
在这里插入图片描述
jsp页面

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--Created by IntelliJ IDEA.User: ghostDate: 2022/1/5Time: 11:37To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>导出文档</title>
</head>
<body>
<%--展示查出的数据--%><table><tr><%--<c:forEach var="pioList" items="${pioList}" varStatus="s">--%><td style="padding-left: 40px">商品名称</td><td style="padding-left: 50px">商品价格（单位：元/斤）</td><td style="padding-left: 50px">商品库存（单位：吨）</td><%--</c:forEach>--%></tr></table><ul style="list-style:none;"><c:forEach var="pioList" items="${pioList}" varStatus="s"><li style="display: inline; padding-left: 10px; color: fuchsia">${pioList.pname}</li><li style="display: inline; padding-left: 120px; color: #ff9fa5">${pioList.price}</li><li style="display: inline; padding-left: 200px; color: #d4af7a">${pioList.pstock}</li><li> </li></c:forEach></ul>
<%--导出为Excel表格--%>
<form action="/novel/writePio">
<%--<label for="path" style="height: 30px; color: #03bbe8">文件名 ：&nbsp;</label>
<input type="text" name="path" id="path"  size="50px" style="height: 30px" placeholder="请输入文件名（以.xlsx结尾，否则导出的不是Excel格式）：">--%><%--导出的默认文件名为商品信息.xlsx--%><select  name="path" id="path" style="display:none"><option>商品信息.xlsx</option></select>
<input type="submit" value="导出为Excel表格" style="width: 150px; height: 60px; margin-top: 10px; margin-left: 100px"/>
</form>
</body>
</html>

最终效果
在这里插入图片描述
点击“导出为Excel表格就可以导出”