【Java】POI实现Excel导入和导出（源码测试）

Mxin5

已于 2023-10-10 22:22:46 修改

阅读量4.5k

点赞数 7

分类专栏： Java 文章标签： excel

于 2022-11-30 22:40:03 首次发布

本文链接：https://blog.csdn.net/m0_64210833/article/details/128123484

版权

Java 专栏收录该内容

39 篇文章 4 订阅

订阅专栏

简介：

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

注意：

1.HSSFworkbook对象导出的excel为xls格式

2.XSSFworkbook对象导出的excel为xlsx格式

3.xls导出最大行数为65535，xlsx最大行数为104万

4.xlsx和xls在数据量相同的情况下xlsx文件更小（数据量越大差距越大），xlsx是一种压缩的格式所以文件更小，（可以将xlsx文件后缀直接改为.zip打开）

5.使用XSSFworkbook导出xlsx格式时最好设置最大行数，不然容易发生内存溢出的情况。xls格式行数上限小不容易内存溢出。

6.SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存

因为实际开发中很多需求都需要实现Excel批量导入和导出，所以今天就来写一个后端demo实现Excel的导入和导出。

需求：

        Excel的导入

        1对文件路径为D:\Users\Mixi\IdeaProjects\javapoi-anli\product-test.xlsx 的Excel文件导入到数据库；

        Excel的导出

        2将数据库product表的数据导出到文件路径为D:\Users\Mixi\IdeaProjects\javapoi-anli\product-test1.xlsx的Excel中；

Excel的导入

1原有数据库product表中数据

2需要导入的excel文件数据

文件路径：D:\Users\Mixi\IdeaProjects\javapoi-anli\product-test.xlsx

3Excel导入执行结果

Excel的导出

1原有数据库product表中数据

2Excel导出执行结果

源码：

1层级关系

web层的Show类：可以理解为Controller层，接收前端的请求实现Excel的导入和导出

/**
 * 模仿前端发送请求到后端
 * 前端传递的应该是一个axios请求到后端，然后将文件的路径传递到后端
 * 后端通过判断是导入还是导出，根据文件地址进行写入数据库或者导出数据库数据到Excel
 * */
public class Show {
    public static void main(String[] args) throws IOException {
        //通过键盘录入Scanner
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入你要选择的功能: 1.导入 2.导出");
        int num = sc.nextInt();
        ProductService productService = new ProductServiceImpl();
        if (num == 1) {
            /**
             * 1.导入（导入就是将Excel中的数据存储到数据库）
             * 1.1读取excel表中的数据
             * 前端选择一个路径下的Excel文件进行导入到数据库
             * */
            System.out.println("请输入您要读取的文件位置(不包含空格)");
            String path = sc.next();
            // add
            path = URLDecoder.decode(path, "UTF-8");
            // 将excel文件进行读取（读取成一个个的Product集合）
            List<Product> productList = read(path);
            System.out.println(productList);
            //1.2将数据写入到数据库中（导入）
            productService.save(productList);
            System.out.println("数据已存入数据库中!");
        } else if (num == 2) {
            /**
             * 2.导出（Excel导出==就是将数据库中的数据读取处理啊）
             * 2.1 读取数据库中的数据(从数据库读取的数据是Product的集合)
             * findAll查找到所有的数据
             * */
            List<Product> productList = productService.findAll();
            System.out.println(productList);
            //2.2将数据写入到excel表格中
            System.out.println("请输入要写入的文件位置:");
            String path = sc.next();
            // 将数据库中的所有数据写入到Excel表中并存放在指定位置
            write(productList, path);
            System.out.println("写入成功!");
        } else {
            System.out.println("输入有误,请重新启动");
        }
    }
    /**
     * read方法是用来将Excel文件中的数据导入到数据库
     */
    public static List<Product> read(String path) throws IOException {
        /**
         * 存放多个Product，就是多条数据
         * */
        List<Product> productList = new ArrayList<>();
        //1.获取工作薄 path就是需要导出的Excel文件路径
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(path);
        //2.获取工作表
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        // 获取到最后一行
        int lastRowNum = sheet.getLastRowNum();
        // 从第一行进行获取数据
        for (int i = 1; i <= lastRowNum; i++) {
            // 工作表获取到每一行
            XSSFRow row = sheet.getRow(i);
            // 空值校验
            if (row != null) {
                List<String> list = new ArrayList<>();
                // 对行进行遍历获取单元格
                for (Cell cell : row) {
                    // 空值校验
                    if (cell != null) {
                        // 为了避免有的单元格不是String类型，所以统一设置成String格式
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        // 再获取单元格中的数据（从而避免类型转换异常）
                        String value = cell.getStringCellValue();//读取数据
                        /**
                         * 将每一行中单元格中的数据获取到过后存储到String类型的list集合当中
                         * 最后将list集合中的每个数据使用有参构造的方式封装到Product对象中
                         * */
                        if (value != null && !"".equals(value)) {
                            list.add(value);
                        }
                    }
                }
                /**
                 * 最后将list集合中的每个数据使用有参构造的方式封装到Product对象中
                 * 通过下标的方式
                 * */
                // 集合的size（）大于0
                if (list.size() > 0) {
                    // 因为Product中的类型不一样，需要转换
                    Product product = new Product(Integer.parseInt(list.get(0)), list.get(1), Double.parseDouble(list.get(2)), Integer.parseInt(list.get(3)));
                    // 添加到Product类型的集合中去
                    productList.add(product);
                }
            }
        }
        // 返回集合
        return productList;

    }
    /**
     * 导出Excel
     * write方法是用来导出Excel
     * 传递的集合是数据库中的所有数据，和导出Excel存放的地址
     */
    public static void write(List<Product> productList, String path) throws IOException {
        //1.创建一个工作薄
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("商品");
        //创建单元格样式
        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex());
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //字体样式
        XSSFFont font = xssfWorkbook.createFont();
        font.setFontName("黑体");
        font.setColor(IndexedColors.BLUE.getIndex());
        cellStyle.setFont(font);

        //3.创建行【第一行】
        XSSFRow row = sheet.createRow(0);
//       row.createCell(0).setCellValue("商品编号");
//       row.createCell(1).setCellValue("商品名称");
//       row.createCell(2).setCellValue("商品价格(单位:元/斤)");
//       row.createCell(3).setCellValue("商品库存(单位:吨)");

        XSSFCell cell = row.createCell(0);
        cell.setCellValue("商品编号");
        // 设计样式
        cell.setCellStyle(cellStyle);

        XSSFCell cell1 = row.createCell(1);
        cell1.setCellValue("商品名称");
        cell1.setCellStyle(cellStyle);

        XSSFCell cell2 = row.createCell(2);
        cell2.setCellValue("商品价格(单位:元/斤)");
        cell2.setCellStyle(cellStyle);

        XSSFCell cell3 = row.createCell(3);
        cell3.setCellValue("商品库存(单位:吨)");
        cell3.setCellStyle(cellStyle);

        for (int i = 0; i < productList.size(); i++) {
            // 从第二行开始创建
            XSSFRow row1 = sheet.createRow(i + 1);
            // 创建单元格进行赋值
            row1.createCell(0).setCellValue(productList.get(i).getPid());
            row1.createCell(1).setCellValue(productList.get(i).getPname());
            row1.createCell(2).setCellValue(productList.get(i).getPrice());
            row1.createCell(3).setCellValue(productList.get(i).getPstock());
        }
        // 创建文件输出流【IO流的方式】
        FileOutputStream fileOutputStream = new FileOutputStream(path);
        // 将文件输出流写到Excel中
        xssfWorkbook.write(fileOutputStream);
        // 刷新
        fileOutputStream.flush();
        // 关流
        fileOutputStream.close();
        xssfWorkbook.close();
    }
}

ProductService：Show类中调用的接口

public interface ProductService {

    void save(List<Product> productList);

    List<Product> findAll();

}

ProductServiceImpl:接口的实现类

public class ProductServiceImpl implements ProductService {
    private ProductDao productDao = new ProductDaoImpl();

    /**
     * 导入
     * 将Excel中的每行数据封装成Product对象再封装到集合当中进行遍历保存到数据库
     */
    @Override
    public void save(List<Product> productList) {
        for (Product product : productList) {
            productDao.save(product);
        }

    }

    /**
     * 导出
     */
    @Override
    public List<Product> findAll() {

        return productDao.findAll();
    }
}

ProductDao

public interface ProductDao {
    /**
     * Excel导入到数据库
     */
    void save(Product product);

    /**
     * 数据库导出到Excel
     */
    List<Product> findAll();

}

ProductDaoImpl:JDBC来实现增删改查，使用xml文件书写也是可以的

public class ProductDaoImpl implements ProductDao {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 保存，用来Excel的导入
     */
    @Override
    public void save(Product product) {
        // https://www.cnblogs.com/TestAndDevelp/p/12378898.html 参考文章
        String sql = "insert into product values(?,?,?,?)";
        // 将sql中的占位符进行一个替换
        jdbcTemplate.update(sql, product.getPid(), product.getPname(), product.getPrice(), product.getPstock());

    }

    /**
     * 查找数据，用来Excel的导出
     */
    @Override
    public List<Product> findAll() {
        String sql = "select * from product";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Product>(Product.class));
    }


}

domain层的Product：实体类

/**
 * Excel表对应的实体类
 * */
public class Product {
    private Integer pid;
    private String pname;
    private double price;
    private int pstock;

    @Override
    public String toString() {
        return "Product{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                ", price=" + price +
                ", pstock=" + pstock +
                '}';
    }

    public Product(Integer pid, String pname, double price, int pstock) {
        this.pid = pid;
        this.pname = pname;
        this.price = price;
        this.pstock = pstock;
    }

    public Product() {
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public int getPstock() {
        return pstock;
    }

    public void setPstock(int pstock) {
        this.pstock = pstock;
    }
}

测试：执行main方法，输入1或者2，输入导入文件的路径或Excel文件需要导出的路径

1测试导入功能