简易POI导入导出模板

先写一份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();
                }
            }
        }
    }
}

导出的工具类差不多可以通用，只要处理好需要输出的数据即可。