前言：在我们的工作当中，时常会有将Excel文档进行导入导出的操作，针对于此项需求，有很多的处理方法。而我们今天就讲一下其中最为常用的POI。
1.POI介绍
POI是Apache软件基金会的开放源码函式库。
POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
2.Excel介绍
Excel文档有两种格式，分别是
- 03版的excel，文件后缀为.xls （最多能容纳65536行数据）
- 07版的excel，文件后缀为.xlsx（最多能容纳1048576行数据）
3.导入依赖
导入poi依赖，可以根据需求导入相对应的依赖。
poi可以操作03版的excel，后缀为.xls
poi-ooxml可以操作07版的excel，后缀为.xlsx
<!--03版excel 后缀为.xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--07版excel 后缀为.xlsx-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>4.认识类与方法
- HSSFWorkbook：一般用于操作03版以前的excel（包括03版excel），扩展名是.xls。
- XSSFWorkbook：一般用于操作07版excel及以上的版本，扩展名是.xlsx。
- SXSSFWorkbook（POI 3.8+版本）：一般用于大数据量的导出。比如数据量超过5000条即可考虑这种工作表
这三个类可以根据需求来进行选择。
下面介绍一下三个类共有的方法。了解了这些方法，就可以更加快捷的去导入导出Excel
//获取工作薄
Workbook workbook=new HSSFWorkbook();
//获取工作表
Sheet sheet = workbook.createSheet();
//生成第一行 参数为行的下标，下标从0开始
Row row = sheet.createRow(0);
//生成第一列 参数为列的下标，下标从0开始
Cell cell1 = row.createCell(0);
//给第一行第一列的单元格赋值
cell1.setCellValue("姓名");
//输出流导出文档
FileOutputStream fileOutputStream = new FileOutputStream(new File(PATH + "hhh.xls"));
workbook.write(fileOutputStream);//读取excel文档 生成工作薄对象
FileInputStream fileInputStream = new FileInputStream(new File("D:\\Users\\idea\\test\\lilun\\excel\\srchhh.xls"));
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
//读取第一个工作表 参数为工作表下标 下标从0开始
HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);
//读取工作表第一行数据 参数为行下标 下标从0开始
HSSFRow row = sheetAt.getRow(0);
//一行有几列 返回列数
int num = row.getPhysicalNumberOfCells();
//根据行取对应的列 获取单元格 参数为行下标 下标从0开始
HSSFCell cell = row.getCell(0);
//获取单元格的数据类型 返回值是一个int类型
int cellType = cell.getCellType();获取类型后就可以进行判断
CELL_TYPE_NUMERIC 数值型 0
CELL_TYPE_STRING 字符串型 1
CELL_TYPE_FORMULA 公式型 2
CELL_TYPE_BLANK 空值 3
CELL_TYPE_BOOLEAN 布尔型 4
CELL_TYPE_ERROR 错误 5
根据数据类型的不同采用不同的方法获取单元格内的值
//判断单元格数据类型
switch (cellType){
case 0:
//单元格返回double类型
double numericCellValue = cell.getNumericCellValue();
break;
case 1:
//单元格返回string字符串类型
String stringCellValue = cell.getStringCellValue();
break;
case 2:
//单元格返回公式
String cellFormula = cell.getCellFormula();
break;
case 3:
//单元格为空
break;
case 4:
//单元格返回boolean类型
boolean booleanCellValue = cell.getBooleanCellValue();
break;
case 5:
//单元格返回错误
byte errorCellValue = cell.getErrorCellValue();
break;
default:
break;
}5.读取Excel的方法
/**
* 读取Excel文档
* @throws IOException
*/
@Test
public void inExcel() throws IOException {
//读取excel文档 生成工作薄对象
FileInputStream fileInputStream = new FileInputStream(new File("D:\\Users\\idea\\test\\lilun\\excel\\srchhh.xls"));
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
//读取第一个工作表 参数为工作表下标 下标从0开始
HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);
//读取工作表第一行数据 参数为行下标 下标从0开始
HSSFRow row = sheetAt.getRow(0);
if(row!=null){
//一行有几列 返回列数
int num = row.getPhysicalNumberOfCells();
for (int i=0;i<num;i++){
//根据行取对应的列 获取单元格 参数为行下标 下标从0开始
HSSFCell cell = row.getCell(i);
if(cell!=null){
//获取单元格的数据类型
int cellType = cell.getCellType();
//判断单元格数据类型
switch (cellType){
case 0:
//单元格返回double类型
double numericCellValue = cell.getNumericCellValue();
break;
case 1:
//单元格返回string字符串类型
String stringCellValue = cell.getStringCellValue();
break;
case 2:
//单元格返回公式
String cellFormula = cell.getCellFormula();
break;
case 3:
//单元格为空
break;
case 4:
//单元格返回boolean类型
boolean booleanCellValue = cell.getBooleanCellValue();
break;
case 5:
//单元格返回错误
byte errorCellValue = cell.getErrorCellValue();
break;
default:
break;
}
}
}
}
fileInputStream.close();
}6.生成Excel的方法
String PATH="D:\\Users\\idea\\test\\lilun\\excel\\";
/**
* 生成Excel文档
* @throws IOException
*/
@Test
public void toExcel() throws IOException {
//获取工作薄
Workbook workbook=new HSSFWorkbook();
//获取工作表
Sheet sheet = workbook.createSheet();
//生成第一行 参数为行的下标，下标从0开始
Row row = sheet.createRow(0);
//生成第一列 参数为列的下标，下标从0开始
Cell cell1 = row.createCell(0);
//给第一行第一列的单元格赋值
cell1.setCellValue("姓名");
Cell cell2 = row.createCell(1);
cell2.setCellValue("性别");
Row row1 = sheet.createRow(1);
Cell cell11 = row1.createCell(0);
cell11.setCellValue("hh");
Cell cell22 = row1.createCell(1);
cell22.setCellValue("man");
//输出流导出文档
FileOutputStream fileOutputStream = new FileOutputStream(new File(PATH + "hhh.xls"));
workbook.write(fileOutputStream);
fileOutputStream.close();
}如果想要更加符合真实的逻辑，与前台数据库交互，那就可以借助工具类。
下面小编将会举一个案例，来使用poi进行导入导出Excel
7.导入导出POI工具类
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.util.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import javax.sql.DataSource;
import java.io.*;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @Description: TODO
* @author: sakura
* @date: 2020/6/20 17:18
* @version: V1.0
**/
public class PoiUtil {
/**
* 导出到excel中
* @param connection 与数据库的连接
* @param sql 查询的sql语句 select * from 表名
* @param tableName excel的sheet名
* @param filePath 输出的文件路径
*/
public static void exportToExcel(Connection connection, String sql, String tableName, String filePath){
ResultSet resultSet = null;
PreparedStatement statement = null;
OutputStream os=null;
//创建一个excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建表 并设置sheet的名称
HSSFSheet sheet = workbook.createSheet(tableName);
//创建标题行
HSSFRow row = sheet.createRow(0);
//日期格式化
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFCreationHelper creationHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
try {
//通过传进来的sql 查询表中的数据
statement = connection.prepareStatement(sql);
//获取结果集
resultSet = statement.executeQuery();
//获取元数据 用来获取字段名
ResultSetMetaData metaData = resultSet.getMetaData();
//每一行的列数
int columnCount = metaData.getColumnCount();
//动态根据字段名设置列名 即标题
for (int i = 0; i < columnCount; i++) {
String labelName = metaData.getColumnLabel(i + 1);
row.createCell(i).setCellValue(labelName);
}
int i=1;
//临时行
HSSFRow tempRow;
//遍历结果集 往excel中写数据
while (resultSet.next()){
//创建临时行 即当前行
tempRow = sheet.createRow(i);
for (int j = 0; j < columnCount; j++) {
//获取当前单元格
HSSFCell tempCell = tempRow.createCell(j);
//获取数据库中与当前格对应的数据
Object temp=resultSet.getObject(j+1);
//如果获取到的数据为空则跳过该单元格
if (temp==null || "".equals(temp)){
continue;
}
//如果获取到的是时间 则格式化时间再写到excel中
if (temp instanceof java.util.Date){
//给日期设置样式
tempCell.setCellStyle(cellStyle);
tempCell.setCellValue((java.util.Date) temp);
}else if(temp instanceof Boolean){
tempCell.setCellValue((Boolean) temp);
}else if (temp instanceof Double){
tempCell.setCellValue((Double) temp);
}else {
tempCell.setCellValue(temp.toString());
}
}
i++;
}
os=new BufferedOutputStream(new FileOutputStream(filePath));
//将excel表格写出到指定的路径下
workbook.write(os);
System.out.println(filePath+"导出成功");
} catch (Exception e) {
e.printStackTrace();
}finally {
//关闭资源
IOUtils.closeQuietly(os);
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
*
* 从excel导入到mysql中
* @param t 与数据库中要插入的表对应的实体类
* @param file excel文件流
* @param <T>
* @return 实体类的list集合
*/
public static <T> List<T> importToMysql(Class<T> t, InputStream file){
//准备一个list用来存放结果
List<T> list=new ArrayList<>();
HSSFWorkbook workbook= null;
try {
//通过文件路径创建一个工工作簿
workbook = new HSSFWorkbook(file);
} catch (IOException e) {
System.out.println("文件读取失败");
System.out.println(e.getMessage());
}
//获取当前excel的第一个sheet表格 如果有多个sheet就自行遍历
HSSFSheet sheet = workbook.getSheetAt(0);
//获取excel表的第一行 用来获取表的字段名
HSSFRow tempRow = sheet.getRow(0);
//获取总的行数
int lastRowNum = sheet.getLastRowNum();
//遍历行 从第二行开始 第一行为表的字段名
for (int i = 1; i <= lastRowNum; i++) {
HSSFRow row = sheet.getRow(i);
//获取列数
int cells = row.getPhysicalNumberOfCells();
//实例化实体类对象
T tempT= null;
try {
tempT = t.newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
//遍历当前行的每一列
for (int j = 0; j < cells; j++) {
//当前列的值
HSSFCell hsscell = row.getCell(j);
//当前列的名称
HSSFCell cellName= tempRow.getCell(j);
Field field = null;
try {
//根据列名获取实体类与之对应的属性
field = tempT.getClass().getDeclaredField(cellName.toString());
} catch (NoSuchFieldException e) {
System.out.println(field.getName()+"属性获取失败");
System.out.println(e.getMessage());
}
//如果该字段为空 则跳过
if (hsscell==null){
continue;
}
try {
//根据从excel获取到的数据的类型设值
if (field!=null){
//私有属性授权
field.setAccessible(true);
if (hsscell.getCellTypeEnum()== CellType.NUMERIC){
if(HSSFDateUtil.isCellDateFormatted(hsscell)){//日期
if (field.getType()== Date.class){
field.set(tempT,hsscell.getDateCellValue());
}
}else if(field.getType()==Integer.class){
//int类型
field.set(tempT,Integer.valueOf(hsscell.getStringCellValue()));
}else if(field.getType()==Double.class){
//double类型
field.set(tempT,Double.parseDouble(hsscell.toString()) );
}
}else if (hsscell.getCellTypeEnum()==CellType.BOOLEAN){
//布尔值
if (field.getType()==Boolean.class){
field.set(tempT,hsscell.getBooleanCellValue() );
}
}else if(hsscell.getCellTypeEnum()==CellType.STRING){
if (field.getType()==Integer.class){
field.set(tempT,Integer.parseInt(hsscell.toString()));
}else if (field.getType()==Double.class){
field.set(tempT,Double.valueOf(hsscell.toString()) );
}else if (field.getType()==String.class){
field.set(tempT,hsscell.toString() );
}else if(HSSFDateUtil.isCellDateFormatted(hsscell)){
if (field.getType()== Date.class){
field.set(tempT,hsscell.getDateCellValue());
}
}
}
}
} catch (IllegalAccessException e) {
System.out.println(field.getName()+"设值失败");
e.printStackTrace();
}
}
//添加到list集合中
list.add(tempT);
}
//将封装好的list集合返回
return list;
}
}8.控制层
/**
* <p>
* 前端控制器
* </p>
*
* @author LH
* @since 2020-06-04
*/
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserDao userDao;
/**
* 导出excel
* @param size导出数据条数
* @param name 文件的名称
* @return
*/
@RequestMapping("toExcel")
public ResponseResult toExcel(Integer size,String name){
ResponseResult responseResult = ResponseResult.getResponseResult();
try {
//通过数据源获取与数据库的连接 没配置spring的可用原生的jdbc来获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?serverTimezone=UTC", "root", "root");
//查询的sql语句
StringBuilder sql=new StringBuilder("select * from base_user");
if(size!=null && size!=0){
sql.append(" limit 0,"+size);
}
//生成的excel表的路径 注意文件名要和数据库中表的名称一致 因为导入时要用到。
String filePath="D:\\Desktop\\"+name+".xls";
//导出
PoiUtil.exportToExcel(connection,sql.toString(), "base_user", filePath);
responseResult.setCode(200);
responseResult.setSuccess("导出excel成功");
return responseResult;
}catch (Exception x){
x.printStackTrace();
responseResult.setCode(500);
responseResult.setSuccess("导出excel失败");
return responseResult;
}
}
@Autowired
private KafkaTemplate kafkaTemplate;
/**
* 导入excel
* @param file
* @return
*/
@RequestMapping("insertExcel")
public ResponseResult insertExcel(@RequestParam("file") MultipartFile file){
ResponseResult responseResult = ResponseResult.getResponseResult();
try {
List<User> users = PoiUtil.importToMysql(User.class, file.getInputStream());
userDao.insertUser(users);
responseResult.setCode(200);
responseResult.setSuccess("导入excel成功");
return responseResult;
}catch (Exception x){
x.printStackTrace();
responseResult.setCode(500);
responseResult.setSuccess("导入excel失败");
return responseResult;
}
}
}9.实体类
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
/**
* @Description: user实体类
* @author: sakura
* @date: 2020/6/6 8:30
* @version: V1.0
**/
@Data
public class User implements Serializable {
private Integer id;
private Integer version;
private String userName;
private String passWord;
private String loginName;
private String email;
private String tel;
private String buMen;
private String salt;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date updateTime;
private String start;
private String end;
private String img;
private Integer sex;
private Integer activate;
}10.数据访问层
此案例运用mybatis进行数据持久化
/**
* @Description: user数据访问层
* @author: sakura
* @date: 2020/6/4 14:33
* @version: V1.0
**/
@Mapper
public interface UserDao {
public void insertUser(List<User> user);
}11.mapper
<insert id="insertUser">
insert into base_user(version,userName,loginName,passWord,email,tel,buMen,salt,createTime,updateTime,img,sex,activate) values
<foreach collection="list" item="u" separator=",">
(0,#{u.userName},#{u.loginName},'DE2E529EBBBDE7EB08B63B2452B9802E',#{u.email},#{u.tel},'实习生','cascade',#{u.createTime},#{u.updateTime},'http://106.12.200.44:9000/test01/222.jpg',#{u.sex},0)
</foreach>
</insert>到这里，案例就展示完毕了。
如果有任何不足，还请大佬指正。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
