# poi-plus

**Repository Path**: izifeng/poi-plus

## Basic Information

- **Project Name**: poi-plus
- **Description**: 轻量级Excel导入、导出工具，支持自定义导出模板
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 6
- **Forks**: 0
- **Created**: 2019-01-23
- **Last Updated**: 2025-07-31

## Categories & Tags

**Categories**: excel-utils

**Tags**: None

## README

# poi-plus

#### 介绍
一款轻量级Excel导入、导出工具，支持自定义模板导出。支持spring mvc和spring boot运用。

注：还支持导出CSV哦~~~


#### poi-plus能做什么？
1. 支持自定义Excel模板导入、导出。有了poi-plus再也不用担心复杂表头的导出需求了。
2. 支持xls、xlsx、cvs文件导出
3. 支持海量数据导入
4. 支持支持多Sheet多出
5. 支持Excel模板导出


#### 安装教程

1、编译项目到本地Maven仓库

`mvn clean install -DskipTests`

2、在项目pom.xml中加入poi-plus

```
<dependency>
    <groupId>xin.yangda</groupId>
    <artifactId>poi-plus</artifactId>
    <version>1.0</version>
</dependency>
```


#### 使用说明

1、 单Sheet导入、导出Demo（更多案例，请查看单元测试）
```java
package xin.yangda.poiplus.test.controller;

import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import xin.yangda.poiplus.constants.PoiPlusFileExtend;
import xin.yangda.poiplus.designer.SimpleXlsDesigner;
import xin.yangda.poiplus.designer.csv.CsvDesigner;
import xin.yangda.poiplus.handler.ExcelReadHandler;
import xin.yangda.poiplus.loader.SimpleXlsxLoader;
import xin.yangda.poiplus.pojo.ExcelData;
import xin.yangda.poiplus.pojo.ExcelErrorField;
import xin.yangda.poiplus.test.entity.UserEntity;
import xin.yangda.poiplus.test.service.UserService;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

/**
 * @author yangdaxin
 * @version 创建时间 2019/1/21 16:32
 */
@RestController
@RequestMapping(value = "/user", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
public class UserController {
    private static final Logger LOGGER = LoggerFactory.getLogger(UserController.class);

    private static final String XLS_EXCEL_TEMP_PATH = "templates/user.xls";
    private static final String XLSX_EXCEL_TEMP_PATH = "templates/user.xlsx";

    @Autowired
    private UserService userService;

    /**
     * 下载excel（有模板）
     *
     * @param request
     * @param response
     */
    @GetMapping(value = "/excelTemp")
    public void exportExcelTemp(HttpServletRequest request, HttpServletResponse response) {
        // 打开模板
//        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(XLS_EXCEL_TEMP_PATH);
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(XLSX_EXCEL_TEMP_PATH);
        try (SimpleXlsDesigner simpleXlsDesigner = new SimpleXlsDesigner(inputStream)) {
            // 填充数据源
            Map<String, String> map = Maps.newConcurrentMap();
            map.put("createUser", "yangdaxin");
            map.put("createTime", "2019-01-18 18:05:00");

            ExcelData<UserEntity> excelData = new ExcelData<>(UserEntity.class, map, userService.queryList());
            simpleXlsDesigner.setData(excelData);

            // 数据加工
            simpleXlsDesigner.process();

            // 保存Excel
            simpleXlsDesigner.download(request, response, PoiPlusFileExtend.XLS);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 下载excel（无模板）
     *
     * @param request
     * @param response
     */
    @GetMapping(value = "/excel")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
        try (SimpleXlsDesigner simpleXlsDesigner = new SimpleXlsDesigner()) {
            // 填充数据源
            ExcelData<UserEntity> excelData = new ExcelData<>(UserEntity.class, userService.queryList());
            simpleXlsDesigner.setData(excelData);

            // 数据加工
            simpleXlsDesigner.process();

            // 保存Excel
            simpleXlsDesigner.download(request, response, PoiPlusFileExtend.XLS);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 下载模板
     *
     * @param request
     * @param response
     */
    @GetMapping(value = "/template")
    public void exportTemplate(HttpServletRequest request, HttpServletResponse response) {
        try (SimpleXlsDesigner simpleXlsDesigner = new SimpleXlsDesigner()) {
            // 填充数据源
            ExcelData<UserEntity> excelData = new ExcelData<>(UserEntity.class);
            simpleXlsDesigner.setData(excelData);

            // 数据加工
            simpleXlsDesigner.process(true);

            // 保存Excel
            simpleXlsDesigner.download(request, response, PoiPlusFileExtend.XLS);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 下载CSV
     *
     * @param request
     * @param response
     */
    @GetMapping(value = "/csv")
    public void exportCsv(HttpServletRequest request, HttpServletResponse response) {
        ExcelData<UserEntity> excelData = new ExcelData<>(UserEntity.class, userService.queryList());
        CsvDesigner.build(excelData).download(request, response);
    }

    /**
     * Excel导入
     *
     * @param file
     * @return
     * @throws IOException
     */
    @PostMapping("/import")
    public ResponseEntity<?> importExcel(@RequestParam("excelFile") MultipartFile file) throws IOException {
        // 执行文件导入.
        long beginTimeMillis = System.currentTimeMillis();
        final List<UserEntity> userList = Lists.newArrayList();
        final List<Map<String, Object>> error = Lists.newArrayList();

        SimpleXlsxLoader.build(UserEntity.class).readXlsx(file.getInputStream(), new ExcelReadHandler<UserEntity>() {
            @Override
            public void onSuccess(int sheet, int row, UserEntity userEntity) {
                // 当前行读取成功, 入库或加入批量入库队列.
                userList.add(userEntity);
            }

            @Override
            public void onError(int sheet, int row, List<ExcelErrorField> errorFields) {
                // 当前行读取失败, 获取失败详情.
                error.add(ImmutableMap.of("row", row, "errorFields", errorFields));
            }
        });

        long time = ((System.currentTimeMillis() - beginTimeMillis) / 1000L);

        LOGGER.info("数据量: {}, 耗时: {}秒", userList.size(), time);

        ImmutableMap<String, Object> retJsonMap = ImmutableMap.of(//
                "time", "耗时" + time + "秒",
                "data", userList,
                "error", error
        );

        return ResponseEntity.ok(retJsonMap);
    }
}
```

2、 多Sheet导入、导出Demo（更多案例，请查看单元测试）
```java
package xin.yangda.poiplus.test.controller;

import com.google.common.collect.Lists;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import xin.yangda.poiplus.constants.PoiPlusFileExtend;
import xin.yangda.poiplus.designer.MultipleXlsDesigner;
import xin.yangda.poiplus.pojo.ExcelData;
import xin.yangda.poiplus.test.entity.OrderEntity;
import xin.yangda.poiplus.test.entity.UserEntity;
import xin.yangda.poiplus.test.service.OrderService;
import xin.yangda.poiplus.test.service.UserService;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.List;

/**
 * @author yangdaxin
 * @version 创建时间 2019/1/21 16:32
 */
@RestController
@RequestMapping(value = "/userOrder", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
public class UserOrderController {
    private static final String XLS_EXCEL_TEMP_PATH = "templates/userOrder.xls";
    private static final String XLSX_EXCEL_TEMP_PATH = "templates/userOrder.xlsx";

    @Autowired
    private UserService userService;

    @Autowired
    private OrderService orderService;

    /**
     * 下载excel（有模板）
     *
     * @param request
     * @param response
     */
    @GetMapping(value = "/excelByTemplate")
    public void exportExcelTemp(HttpServletRequest request, HttpServletResponse response) {
        // 打开模板
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(XLS_EXCEL_TEMP_PATH);
        //InputStream inputStream = getClass().getClassLoader().getResourceAsStream(XLSX_EXCEL_TEMP_PATH);
        try (MultipleXlsDesigner multipleXlsDesigner = new MultipleXlsDesigner(inputStream)) {
            // 填充数据源
            ExcelData<UserEntity> userData = new ExcelData<>(UserEntity.class, userService.queryList());
            ExcelData<OrderEntity> orderData = new ExcelData<>(OrderEntity.class, orderService.queryList());

            List<ExcelData> excelDataList = Lists.newArrayList();
            excelDataList.add(userData);
            excelDataList.add(orderData);

            multipleXlsDesigner.setData(excelDataList);

            // 数据加工
            multipleXlsDesigner.process();

            // 保存Excel
            multipleXlsDesigner.download(request, response, PoiPlusFileExtend.XLS);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 下载excel（无模板）
     *
     * @param request
     * @param response
     */
    @GetMapping(value = "/excel")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
        try (MultipleXlsDesigner multipleXlsDesigner = new MultipleXlsDesigner()) {
            // 填充数据源
            ExcelData<UserEntity> userData = new ExcelData<>(UserEntity.class, userService.queryList());
            ExcelData<OrderEntity> orderData = new ExcelData<>(OrderEntity.class, orderService.queryList());

            List<ExcelData> excelDataList = Lists.newArrayList();
            excelDataList.add(userData);
            excelDataList.add(orderData);

            multipleXlsDesigner.setData(excelDataList);

            // 数据加工
            multipleXlsDesigner.process();

            // 保存Excel
            multipleXlsDesigner.download(request, response, PoiPlusFileExtend.XLS);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 下载模板
     *
     * @param request
     * @param response
     */
    @GetMapping(value = "/template")
    public void exportTemplate(HttpServletRequest request, HttpServletResponse response) {
        try (MultipleXlsDesigner multipleXlsDesigner = new MultipleXlsDesigner()) {
            // 填充数据源
            ExcelData<UserEntity> userData = new ExcelData<>(UserEntity.class);
            ExcelData<OrderEntity> orderData = new ExcelData<>(OrderEntity.class);

            List<ExcelData> excelDataList = Lists.newArrayList();
            excelDataList.add(userData);
            excelDataList.add(orderData);

            multipleXlsDesigner.setData(excelDataList);

            // 数据加工
            multipleXlsDesigner.process(true);

            // 保存Excel
            multipleXlsDesigner.download(request, response, PoiPlusFileExtend.XLS);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
```

#### 更新日志
##### 2019-01-28 11:00
1. 修复导出Excel模板时，当数据源数据为空时，导出异常bug
2. 优化创建导出数据源方式
3. 修复导入Excel异常bug

##### 2019-02-21 15:35
1. 优化xlsx导入，支持自定义传入开始导入起始行号


#### 参与贡献

1. 本项目为个人项目，各码友有任何问题都可以给我发邮件，程序问题欢迎提issues
2. 还可以关注我的个人博客：http://www.yangda.xin/topic/12.html
3. 扫一扫下面的二维码可以关注我的小程序哦~~

<div align=center style="width=100%">
    <img src="http://www.yangda.xin/upload/image/20190125/1548382988970574.jpg" align=center width="200px" height="200px" title="小程序码"/>
</div>