SpringBoot整合POI实现Excel表格的读写操作(1)
- 导入Pom依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.1</version>
</dependency>
- POI写操作
package com.ss.poi.excel;
import com.ss.poi.pojo.User;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
@Service
public class WriteXLS {
public void exportExcel(List<User> list) {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook ();
HSSFSheet sheet = hssfWorkbook.createSheet ();
HSSFRow titleRow = sheet.createRow (0);
titleRow.createCell (0).setCellValue ("用户编号");
titleRow.createCell (1).setCellValue ("用户头像");
titleRow.createCell (2).setCellValue ("用户姓名");
titleRow.createCell (3).setCellValue ("用户密码");
titleRow.createCell (4).setCellValue ("用户手机");
titleRow.createCell (5).setCellValue ("用户地址");
titleRow.createCell (6).setCellValue ("用户邮箱");
titleRow.createCell (7).setCellValue ("用户余额");
titleRow.createCell (8).setCellValue ("用户支付密码");
for (User user : list) {
int lastRowNum = sheet.getLastRowNum ();
HSSFRow dataRow = sheet.createRow (lastRowNum + 1);
dataRow.createCell (0).setCellValue (user.getUserId ());
dataRow.createCell (1).setCellValue (user.getVia ()==null?"":user.getVia ());
dataRow.createCell (2).setCellValue (user.getUserName ()==null?"":user.getUserName ());
dataRow.createCell (3).setCellValue (user.getUserPassword ()==null?"":user.getUserPassword ());
dataRow.createCell (4).setCellValue (user.getPhone ()==null?"":user.getPhone ());
dataRow.createCell (5).setCellValue (user.getAddress ()==null?"":user.getAddress ());
dataRow.createCell (6).setCellValue (user.getUserEmail ()==null?"":user.getUserEmail ());
dataRow.createCell (7).setCellValue (user.getBalance ()==null?"":user.getBalance ());
dataRow.createCell (8).setCellValue (user.getPayPwd ()==null?"":user.getPayPwd ());
}
String fileName = "用户.xls";
try {
FileOutputStream fileOut = new FileOutputStream ("D:\\L\\" + fileName);
hssfWorkbook.write (fileOut);
hssfWorkbook.close ();
} catch (FileNotFoundException e) {
e.printStackTrace ();
} catch (IOException e) {
e.printStackTrace ();
}
}
}
- POI读操作
package com.ss.poi.excel;
import com.ss.poi.pojo.User;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Service;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Service
public class ReadXLS {
public List<User> importXLS() {
ArrayList<User> list = new ArrayList<>();
try {
String fileName = "用户.xls";
InputStream inputStream = new FileInputStream("D:\\L\\" + fileName);
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
HSSFSheet sheetAt = workbook.getSheetAt(0);
for (Row row : sheetAt) {
if (row.getRowNum() == 0) {
continue;
}
Integer userId = (int) row.getCell(0).getNumericCellValue();
String via = row.getCell(1).getStringCellValue();
String userName = row.getCell(2).getStringCellValue();
String userPassword = row.getCell(3).getStringCellValue();
String phone = row.getCell(4).getStringCellValue();
String address = row.getCell(5).getStringCellValue();
String userEmail = row.getCell(6).getStringCellValue();
String balance = row.getCell(7).getStringCellValue();
String payPwd = row.getCell(8).getStringCellValue();
User user = new User();
user.setUserId(userId);
user.setVia(via);
user.setUserName(userName);
user.setUserPassword(userPassword);
user.setPhone(phone);
user.setAddress(address);
user.setUserEmail(userEmail);
user.setBalance(balance);
user.setPayPwd(payPwd);
list.add(user);
}
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
}
- 实体
package com.ss.poi.pojo;
import lombok.Data;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Data
@Entity
@Table(name = "user")
public class User {
@Id
@Column(name = "user_id")
private Integer userId;
private String via;
@Column(name = "user_name")
private String userName;
@Column(name = "user_password")
private String userPassword;
private String phone;
private String address;
@Column(name = "user_email")
private String userEmail;
private String balance;
@Column(name = "pay_pwd")
private String payPwd;
}
- 数据库表
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/0c1301872b0fc69cd404e59836e96276.png#pic_center)
- Controller层接口
package com.ss.poi.controller;
import com.ss.poi.excel.ReadXLS;
import com.ss.poi.excel.WriteXLS;
import com.ss.poi.pojo.User;
import com.ss.poi.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.Mapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class ExcelController {
@Autowired
private ReadXLS readXLS;
@Autowired
private WriteXLS writeXLS;
@Autowired
private UserService userService;
@GetMapping("/writeExcel")
public String WriteExcel(){
List<User> userList = userService.selectAllUser ();
System.out.println (userList);
writeXLS.exportExcel (userList);
return "成功";
}
@GetMapping("/readExcel")
public String ReadExcel(){
List<User> userList = readXLS.importXLS ();
System.out.println (userList);
return "成功";
}
}
- 测试及结果
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/8b6a7646e0abf193e69c07aaf066d03e.png#pic_center)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/0549208c8d72e03181e0dcdc05706c39.png#pic_center)
8.可能会出现的异常
IOException、FileNotFoundException。
注意文件保存路径的位置。关于java利用poi操作Word文档的案例请看下一篇文章。