先写一份poi的导入导出Excel模板,记录一下,方便后面查找和回顾。
maven依赖
<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>
导入
package com.mywork;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
/**
* 导入Excel
*/
public class TestImportExcel {
public static void main(String[] args) {
FileInputStream fileInputStream = null;
Workbook workbook = null;
try{
File file = new File("E:/MyProject/Mywork/src/main/resources/template/test.xlsx");
fileInputStream = new FileInputStream(file);
workbook = null;
//判断导入的excel类型
if (file.getName().endsWith("xlsx")){
workbook = new XSSFWorkbook(fileInputStream);
}
if (file.getName().endsWith("xls")){
workbook = new HSSFWorkbook(fileInputStream);
}
if (workbook == null){
return;
}
//从workbook对象中拿到sheet对象
Sheet sheet = workbook.getSheetAt(0);
//这里获取到该sheet工作簿中,行的物理数量
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
//从sheet对象中拿到Row对象(行对象)
Row row = sheet.getRow(i);
for (int j = 0 ;j<row.getLastCellNum();j++){
//根据业务解析单元格的数据格式,每个单元格解析的数据类型都不同
Cell cell = row.getCell(j); //从Row对象拿到单元格对象
String cellValue = cell.getStringCellValue();
System.out.println(cellValue);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
if (workbook != null){
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fileInputStream != null){
try {
fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
导出
实体类
package com.entiy;
import java.util.Date;
public class User {
private String userName ;
private String password ;
private Integer age ;
private Date birthday ;
public User() {
}
public User(String userName, String password, Integer age, Date birthday) {
this.userName = userName;
this.password = password;
this.age = age;
this.birthday = birthday;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
导出实现类
package com.mywork;
import com.entiy.User;
import com.util.ExcelUtil;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* 导出Excel
*/
public class TestExportExcel {
private static SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd");
public static void main(String[] args) {
String[] title = {"账号", "密码","生日", "年龄"}; //模拟表头
//模拟数据
User user1 = new User("zhangsan", "123", 18,new Date());
User user2 = new User("lisi", "456", 19,new Date());
User user3 = new User("admin", "root", 20,new Date());
List<User> userList = new ArrayList<User>();
userList.add(user1);
userList.add(user2);
userList.add(user3);
//数据格式处理
List<List<String>> dataList = new ArrayList<>();
for (User u : userList) {
List<String> value = new ArrayList<>();
value.add(u.getUserName());
value.add(u.getPassword());
value.add(SDF.format(u.getBirthday()));
value.add(String.valueOf(u.getAge()));
dataList.add(value);
}
String path = "E:/MyProject/Mywork/src/main/resources/template/";
ExcelUtil.exportExcel(ExcelUtil.createExcel(title, dataList, "导出测试"), "导出", path);
}
}
ExcelUtil.java
package com.util;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
public class ExcelUtil {
public static XSSFWorkbook createExcel(String[] title, List<List<String>> dataList, String sheetName) {
XSSFWorkbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
Row head = sheet.createRow(0);
//设置表头
for (int i = 0; i < title.length; i++) {
Cell cell = head.createCell(i);
cell.setCellValue(title[i]);
}
//设置数据
for (int i = 0; i < dataList.size(); i++) {
Row body = sheet.createRow(i + 1);
for (int j = 0; j < dataList.get(i).size(); j++) {
Cell cell = body.createCell(j);
cell.setCellValue(dataList.get(i).get(j));
}
}
return workbook;
}
public static void exportExcel(Workbook workbook, String fileName, String exportPath) {
FileOutputStream out = null;
try {
out = new FileOutputStream(exportPath + fileName + ".xlsx");
workbook.write(out);
out.flush();
System.out.println("导出成功");
} catch (IOException e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fileName != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
导出的工具类差不多可以通用,只要处理好需要输出的数据即可。