poi 导入导出模板

项目poi 导入调出
package com.yaorange.test;
import com.yaorange.entity.User;
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;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

/**

• 导入Excel 并把代码写到封装到user 对象 可以根据时间情况修改
  */
  public class TestImportExcel {
  public static void main(String[] args) {
  FileInputStream fileInputStream = null;
  ArrayList users = new ArrayList();
  Workbook workbook = null;
  try{
  File file = new File(“C:\Users\Administrator\Desktop\a导出.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 = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
           //从sheet对象中拿到Row对象（行对象）
           Row row = sheet.getRow(i);
           ArrayList<String> strings = new ArrayList<String>();
           if (row!=null){
               for (Cell cell:row
                    ) {
                   String stringCellValue = cell.getStringCellValue();
                   strings.add(stringCellValue);
               }
           }
           if (strings.size()>0){
               users.add(new User(strings.get(0),strings.get(1),Integer.valueOf(strings.get(2)),new SimpleDateFormat("yyyy-MM-dd").parse(strings.get(3))));
           }
       }
   } catch (FileNotFoundException e) {
       e.printStackTrace();
   } catch (IOException e) {
       e.printStackTrace();
   } catch (ParseException 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.yaorange.test;

/**

• @description:
• @company: ya orange
• @author: NiuNiu
• @version： 1.0
• @create: 2022-01-12 16:32:42
  */
  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> 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();
}
}
}
}
}

这是是导出到 excel

package com.yaorange.test;

import com.yaorange.entity.User;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**

• @description: 测试导出
• @company: ya orange
• @author: NiuNiu
• @version： 1.0
• @create: 2022-01-12 16:29:54
  */
  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 userList = new ArrayList();
  userList.add(user1);
  userList.add(user2);
  userList.add(user3);
  //数据格式处理
  List<List> dataList = new ArrayList<List>();
  for (User u : userList) {
  List 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 = “C:\Users\Administrator\Desktop\a”;
  ExcelUtil.exportExcel(ExcelUtil.createExcel(title, dataList, “导出测试”), “导出”, path);
  }
  }

这里是实体类
package com.yaorange.entity;

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;
}

}