POI
1. 快速入门
HSSF是2003版的Excel,后缀为xls
XSSF是2007版的Excel,后缀为xlsx
XSSF和HSSF操作基本一致,只需要把H换成X就行
- 依赖
<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>
1.1. 从Excel表格读取数据
在使用POI操作Excel表格时,Excel表格必须是关闭状态
- 创建excel表格,填写内容
- 获取工作簿
- 获取工作表
- 获取行
- 获取单元格(列)
- 获取单元格的值
/**
* 从excel表格中获取数据
* @author yxk
* Date 2021/4/9 11:13
*/
@Test
public void test01() throws IOException {
//1.创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook("D:\\Java\\2.xlsx");
//2.获取工作表--根据名称或索引
XSSFSheet sheet = workbook.getSheetAt(0);
//3.获取行--遍历获取所有行
for (Row row : sheet) {
//4.获取单元格--遍历行获取所有单元格
for (Cell cell : row) {
//5.获取单元格的值
String stringValue = cell.getStringCellValue();
System.out.print(stringValue+" ");
}
System.out.println();
}
//关流
workbook.close();
}
/**
* 从excel表格中获取数据
* @author yxk
* Date 2021/4/9 11:13
*/
@Test
public void test02() throws IOException {
//1.创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook("D:\\Java\\2.xlsx");
//2.获取工作表--根据名称或索引
XSSFSheet sheet = workbook.getSheetAt(0);
//3.获取行-使用索引遍历
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i < lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);
if (row != null) {
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
XSSFCell cell = row.getCell(j);
if (cell != null) {
String value = cell.getStringCellValue();
System.out.print(value+" ");
}
}
System.out.println();
}
}
//关流
workbook.close();
}
1.2. 往Excel表格写入数据
- 创建工作簿
- 创建工作表
- 创建行
- 创建单元格并赋值
- 通过IO流把数据写到磁盘文件
/**
* 往excel表格中写入数据
* @author yxk
* Date 2021/4/9 11:41
*/
@Test
public void test03() throws IOException {
//1.创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//2.创建工作表
XSSFSheet sheet = workbook.createSheet();
for (int i = 0 ; i < 10;i++) {
//3.创建行 - 0代表第一行
XSSFRow row = sheet.createRow(i);
//4.创建单元格 - 0代表第一列
XSSFCell cell = row.createCell(0);
//赋值
cell.setCellValue("Java"+"-"+i);
}
//5.通过IO流把数据写到磁盘文件
FileOutputStream stream = new FileOutputStream("D:\\Java\\2.xlsx");
workbook.write(stream);
//刷新
stream.flush();
//关流
stream.close();
workbook.close();
}
结果:
往第一列第i行写入数据
遇到的问题
当把XSSFRow row = sheet.createRow(i);的i改成0,XSSFCell cell = row.createCell(0);的0改成i
按理说第一行的第一列到第十列都应该有数据的,但是实际上只有第十列有数据
2. 结合Web端实现Excel文件数据导入与导出
2.1 准备
- 创建Excel表格
- 创建数据库表
- 创建实体类User
- 在ssm环境下进行的,请自行搭建
2.2. 导入
把Excel表格的数据导入到数据库表中
- import.jsp
获取Excel表格并提交到后台
<body>
<hi>导入Excel表格文件,本质就是文件上传</hi><br>
<form action="${pageContext.request.contextPath}/import/excel.do" method="post" enctype="multipart/form-data">
<input type="file" name="excelFile" value="导入excel表格">
<input type="submit" value="提交">
</form>
<br>
<p>${msg}</p>
</body>
- showUsers.jsp
Excel表格的内容存储到数据库之后,在页面展示
<body>
<table border="1px" width="500px" align="center">
<tr>
<th>用户id</th>
<th>用户名称</th>
<th>用户密码</th>
<th>用户地址</th>
<th>用户年龄</th>
</tr>
<c:forEach items="${users}" var="user" varStatus="status">
<tr>
<td>${user.userId}</td>
<td>${user.username}</td>
<td>${user.password}</td>
<td>${user.userAddress}</td>
<td>${user.userAge}</td>
</tr>
</c:forEach>
</table>
<a href="${pageContext.request.contextPath}/export/excel.do">导出所有用户信息</a>
</body>
- ExcelUtil.java
本来想写工具类的,但是没有成功,所以ExcelUtil.java只适合这里
/**
* 校验excelFile文件
* @author yxk
* @param excelFile
* @return java.lang.String
* Date 2021/4/9 19:18
*/
public static StringBuffer checkExcel(MultipartFile excelFile) {
StringBuffer msg = null;
//1是否为null
if (excelFile == null) {
msg = new StringBuffer("文件不存在");
}else {
//2格式是否正确
String filename = excelFile.getOriginalFilename();
String xls = "xls";
String xlsx = "xlsx";
assert filename != null;
if (!filename.endsWith(xls) && !filename.endsWith(xlsx)) {
msg = new StringBuffer("文件不是Excel文件");
}
}
return msg;
}
/**
* 通过工具类ExcelUtil得到行对象集合,通过增强for循环得到每一行,然后获得每一行单元格的值封装到实体类里
* 在这个方法里只是一个示例,一位List的泛型,和for循环里面的内容都是写死的
* 在实际应用中可以把该方法移除工具类.然后具体情况具体分析
* @author yxk
* @param excelFile
* @return java.util.List<com.zy.pojo.User>
* Date 2021/4/9 19:54
*/
public static List<User> readXssf02(MultipartFile excelFile) {
List<User> users = new ArrayList<>();
List<XSSFRow> xssfRows = ExcelUtil.readXssf01(excelFile);
for (XSSFRow row : xssfRows) {
int userId = (int)row.getCell(0).getNumericCellValue();
String username = row.getCell(1).getStringCellValue();
String password = row.getCell(2).getStringCellValue();
String userAddress = row.getCell(3).getStringCellValue();
int userAge = (int)row.getCell(4).getNumericCellValue();
users.add(new User(userId,username,password,userAddress,userAge));
}
return users;
}
/**
* Excel表格导入 --后缀为xlsx
* @author yxk
* @param excelFile
* @return java.util.List<com.zy.pojo.User>
* Date 2021/4/9 19:19
*/
public static List<XSSFRow> readXssf01(MultipartFile excelFile) {
List<XSSFRow> list = null;
try {
//从文件excelFile获取字节输入流
InputStream inputStream = excelFile.getInputStream();
//构建工作簿
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
//获取工作表
XSSFSheet sheet = workbook.getSheetAt(0);
//获取第一行索引和最后一行索引
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
//声明一个容器
list = new ArrayList<>();
//遍历工作表的到行
for (int i = firstRowNum+1; i <= lastRowNum; i++) {
//获取工作表中的一个个行对象
XSSFRow row = sheet.getRow(i);
//如果行为空,跳过
if (row == null) {
continue;
}
list.add(row);
}
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
- UserController.java
/**
* @author yxk
* @version 1.0
* Date 2021/4/9 15:37
*/
@Controller
@RequestMapping("/import")
public class ExcelUploadController {
@Resource
private UserService userService;
/**
* Excel文件导入----文件上传
* @author yxk
* @param excelFile
* @param model
* @return java.lang.String
* Date 2021/4/9 15:40
*/
@RequestMapping("/excel.do")
public String excelUpload(@RequestParam("excelFile") MultipartFile excelFile, Model model) {
//1.校验excelFile文件
String filename = excelFile.getOriginalFilename();
String xlsx = "xlsx";
//1.1判断是否为null和格式是否正确
StringBuffer msg = ExcelUtil.checkExcel(excelFile);
if (msg != null) {
model.addAttribute("msg",msg);
return "/import";
}
//1.2不同格式使用不同的对象
assert filename != null;
if (filename.endsWith(xlsx)) {
//使用XSSFWorkbook
List<User> users = ExcelUtil.readXssf02(excelFile);
userService.addUser(users);
model.addAttribute("users",users);
return "/showUsers";
}else {
//假设Excel是xls的后缀在这里写逻辑代码,基本和xlsx后缀的一样
//使用HSSFWorkbook获取工作簿
}
return null;
}
}
UserServiceImpl.java
@Resource
private UserMapper userMapper;
@Override
public void addUser(List<User> users) {
for (User user : users) {
userMapper.addUser(user);
}
}
UserMapper.xml
<!--Excel文件导入 文件上传-->
<insert id="addUser" parameterType="user">
insert into user values(#{userId},#{username},#{password},#{userAddress},#{userAge})
</insert>
2.3 导出
把数据库的表中数据下载到Excel表格里
- Controller.java
/**
* @author yxk
* @version 1.0
* Date 2021/4/9 16:38
*/
@Controller
@RequestMapping("/export")
public class ExcelExportController {
@Resource
private UserService userService;
@RequestMapping("/excel.do")
public void downLoadExcel(HttpServletResponse response) {
List<User> users = userService.queryAllUsers() ;
//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//创建工作表
XSSFSheet sheet = workbook.createSheet("用户详情表");
//创建行
XSSFRow xssfRow = sheet.createRow(0);
//创建单元格
xssfRow.createCell(0).setCellValue("用户ID");
xssfRow.createCell(1).setCellValue("用户姓名");
xssfRow.createCell(2).setCellValue("用户密码");
xssfRow.createCell(3).setCellValue("用户地址");
xssfRow.createCell(4).setCellValue("用户年龄");
//往表格里插入数据
for (int i = 1; i < users.size() ; i++) {
//创建行
XSSFRow row = sheet.createRow(i+1);
row.createCell(0).setCellValue(users.get(i).getUserId());
row.createCell(1).setCellValue(users.get(i).getUsername());
row.createCell(2).setCellValue(users.get(i).getPassword());
row.createCell(3).setCellValue(users.get(i).getUserAddress());
row.createCell(4).setCellValue(users.get(i).getUserAge());
}
try {
//获取输出流
ServletOutputStream outputStream = response.getOutputStream();
//设置响应头
response.setHeader("Content-Disposition","attachment;filename=user.xlsx");
//设置内容类型
response.setContentType("application/msexcel");
//写入工作簿
workbook.write(outputStream);
//刷新
outputStream.flush();
//关流
outputStream.close();
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
service层和mapper层基本和导入一致,省略
扫一扫
951
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
