# 操作Excel的3种方式

**Repository Path**: fpfgitmy_admin/three-ways-to-operate-excel

## Basic Information

- **Project Name**: 操作Excel的3种方式
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 1
- **Forks**: 0
- **Created**: 2021-04-28
- **Last Updated**: 2021-04-28

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

### 操作Excel的3种方式
+ 相同点：
    1. Easypoi 和 Easyexcel 都是基于Apache poi进行二次开发的
+ 不同点：
    1. Easypoi 在读写数据的时候，优先是先将数据写入内存，优点是读写性能非常高，但是当数据量很大的时候，会出现oom，当然它也提供了 sax 模式的读写方式，需要调用特定的方法实现
    2. Easyexcel 基于sax模式进行读写数据，不会出现oom情况，程序有过高并发场景的验证，因此程序运行比较稳定，相对于 Easypoi 来说，读写性能稍慢
#### Apache poi

##### 基础model
```
@Data
public class Bsheet {

    /**
     * sheet的名称
     */
    private String sheetName = Constants.SHEET_NAME;
    /**
     * 文件名称
     */
    private String fileName = LocalDateTime.now().getMinute() + "";
    /**
     * 是否需要合并第一行
     */
    private Boolean isMerge = false;
    /**
     * 合并的第一行的名称
     */
    private String firstMergeName = Constants.FIRST_MERGE_NAME;
    /**
     * 合并第一行的截至列
     */
    private int endCell;
    /**
     * 表格的头部
     */
    private String[] header;
    /**
     * 表格数据
     */
    private List<String[]> data;
}
```
##### 相关操作代码
######  添加依赖
```
<!--Apache poi操作Excel start-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        
        <!--时间格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.6</version>
        </dependency>
        <!--Apache poi操作Excel end-->
```
###### 编写util
```
public class ExcelUtil {

    // HSSFWorkbook导出 .xls方式
    private Workbook wb = null;
    // XSSF方式导出.xlsx方式
    private XSSFWorkbook xwb = null;
    // SXSSF方式导出.xlsx方式
    private SXSSFWorkbook swb = null;
    private Sheet sheet = null;
    private Row row = null;
    private Cell cell = null;

    // 样式的设置
    private CellStyle style = null;
    private Font font = null;

    private InputStream inputStream = null;
    // excel的格式 默认xlsx
    public static String type = Constants.EXCEL_TYPE_XLSX;


    public ExcelUtil(Bsheet bsheet) {
        switch (type) {
            case Constants.EXCEL_TYPE_XLS:
                // 最多只支持65536条数据导出，超过这个条数会报错
                wb = new HSSFWorkbook();
                sheet = wb.createSheet();
                style = wb.createCellStyle();
                font = wb.createFont();
                break;
            case Constants.EXCEL_TYPE_XLSX:
                // XSSF方式支持大批量数据导出，所有的数据先写入内存再导出，容易出现内存溢出
                xwb = new XSSFWorkbook();
                sheet = xwb.createSheet();
                style = xwb.createCellStyle();
                font = xwb.createFont();
                break;
            case Constants.EXCEL_TYPE_SXLSX:
                // XSSF方式支持大批量数据导出，所有的数据先写入内存再导出，容易出现内存溢出
                swb = new SXSSFWorkbook();
                sheet = swb.createSheet();
                style = swb.createCellStyle();
                font = swb.createFont();
                break;
            default:
                xwb = new XSSFWorkbook();
                sheet = xwb.createSheet();
                style = xwb.createCellStyle();
                font = xwb.createFont();
        }
    }

    /*-----------------------------------------------------------导出excel-----------------------------------------------------------*/

    /**
     * 创建Excel
     *
     * @param request
     * @param response
     * @param bsheet
     */
    public void createExcel(HttpServletRequest request, HttpServletResponse response, Bsheet bsheet) {
        String fileName = getFileName(request, bsheet.getFileName());
        // 是否合并第一行
        if (bsheet.getIsMerge()) {
            row = sheet.createRow(0);
            cell = row.createCell(0);
            if (StringUtils.isNotBlank(bsheet.getFirstMergeName())) {
                cell.setCellValue(bsheet.getFirstMergeName());
            }
            // 起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, bsheet.getEndCell()));
        }
        // 设置头数据
        setHeader(bsheet.getHeader(), bsheet.getIsMerge());
        // 设置表数据(默认设置header)
        addRow(bsheet.getData(), bsheet.getEndCell());
        try {
            response.reset();
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment;   filename=" + fileName);
            ServletOutputStream sos = response.getOutputStream();
            switch (type) {
                case Constants.EXCEL_TYPE_XLS:
                    wb.write(sos);
                    break;
                case Constants.EXCEL_TYPE_XLSX:
                    xwb.write(sos);
                    break;
                case Constants.EXCEL_TYPE_SXLSX:
                    swb.write(sos);
                    break;
                default:
                    xwb.write(sos);
            }
            sos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取文件名称
     *
     * @param request
     * @param fileName
     * @return
     */
    private String getFileName(HttpServletRequest request, String fileName) {
        fileName = fileName.replace(" ", "");
        String userAgent = request.getHeader("user-agent").toLowerCase();
        if (StringUtils.isBlank(fileName)) {
            fileName = System.currentTimeMillis() + "";
        }
        fileName = fileName + (type.equals("xls") ? ".xls" : ".xlsx");
        if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
            try {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        } else {
            try {
                fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        }
        return fileName;
    }

    /**
     * 设置头部数据
     *
     * @param headers
     * @param isMerge
     */
    private void setHeader(String[] headers, Boolean isMerge) {
        row = sheet.createRow(0);
        // 获取样式
        CellStyle style = getStyle();
        if (isMerge) {
            row = sheet.createRow(1);
        }
        for (int i = 0; i < headers.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(style);
        }
    }

    /**
     * 添加数据
     *
     * @param dataList
     * @param endCell
     */
    private void addRow(List<String[]> dataList, int endCell) {
        for (int i = 0; i < dataList.size(); i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < endCell; j++) {
                cell = row.createCell(j);
                cell.setCellValue(dataList.get(i)[j]);
            }
        }
    }

    /**
     * 获取行样式
     *
     * @return
     */
    private CellStyle getStyle() {
        // 设置样式
        style.setAlignment(HorizontalAlignment.CENTER); // 居中
        // 自动换行
        style.setWrapText(true);
        // 设置字体
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        style.setFont(font);
        return style;
    }

    /*-----------------------------------------------------------读取excel-----------------------------------------------------------*/

    public ExcelUtil(InputStream inputStream) {
        this.inputStream = inputStream;
        // 实例化操作
        initWorkBook();
    }

    /**
     * 获取workBook实例
     */
    public void initWorkBook() {
        try {
            switch (type) {
                // HSSF
                case Constants.EXCEL_TYPE_XLS:
                    wb = new HSSFWorkbook(inputStream);
                    sheet = wb.getSheetAt(0);
                    break;
                case Constants.EXCEL_TYPE_XLSX:
                    // XSSF
                    wb = new XSSFWorkbook(inputStream);
                    sheet = wb.getSheetAt(0);
                    break;
                case Constants.EXCEL_TYPE_SXLSX:
                    wb = new SXSSFWorkbook();
                    sheet = wb.getSheetAt(0);
                    break;
                default:
                    wb = new XSSFWorkbook(inputStream);
                    sheet = wb.getSheetAt(0);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据流读取excel数据
     */
    public String readExcel(int rowNum, int cellNum) {
        if (null == inputStream) {
            throw new RuntimeException("inputStream is not null");
        }
        row = sheet.getRow(rowNum);
        cell = row.getCell(cellNum);
        String value = getValue(cell);
        try {
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return value;
    }

    /**
     * 获取总行数
     *
     * @return
     */
    public int readRowCountNum() {
        return sheet.getLastRowNum();
    }

    /**
     * 获取总列数
     *
     * @param rowNum
     * @return
     */
    public int readCellCount(int rowNum) {
        row = sheet.getRow(rowNum);
        return row.getLastCellNum();
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static String getValue(Cell cell) {
        String cellValue = "";
        //匹配类型数据
        if (cell != null) {
            CellType cellType = cell.getCellType();
            switch (cellType) {
                case STRING: //字符串
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN: //布尔类型
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case BLANK: //空
                    break;
                case NUMERIC: //数字（日期、普通数字）
                    if (HSSFDateUtil.isCellDateFormatted(cell)) { //日期
                        System.out.print("[日期]");
                        Date date = cell.getDateCellValue();
                        cellValue = new DateTime(date).toString("yyyy-MM-dd");
                    } else {
                        //不是日期格式，防止数字过长
                        cell.setCellType(CellType.STRING);
                        cellValue = cell.toString();
                    }
                    break;
                case ERROR:
                    break;
                default:
            }
        }
        return cellValue;
    }
}

```
###### 编写测试方法
```
/**
     * 导出excel
     *
     * @param fileName
     * @param request
     * @param response
     */
    @GetMapping("excel/export")
    public void exportTest(
            @RequestParam String fileName,
            HttpServletRequest request,
            HttpServletResponse response
    ) {
        String[] headers = {"姓名", "年龄", "手机号", "余额"};

        ArrayList<String[]> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            dataList.add(new String[]{
                    "f1" + i,
                    i + 1 + "",
                    "1566495158" + i,
                    "1585869" + i
            });
        }
        Bsheet bsheet = new Bsheet();
        bsheet.setFileName(fileName);
        bsheet.setHeader(headers);
        bsheet.setEndCell(headers.length);
        bsheet.setData(dataList);
        // 设置导出方式
        ExcelUtil.type = Constants.EXCEL_TYPE_XLSX;
        ExcelUtil excelUtil = new ExcelUtil(bsheet);
        excelUtil.createExcel(request, response, bsheet);
    }


    /**
     * 以路径方式读取
     *
     * @throws FileNotFoundException
     */
    @GetMapping(value = "excel/read")
    public void readExcelForPathTest() throws FileNotFoundException {
        // 设置读取方式
        ExcelUtil.type = Constants.EXCEL_TYPE_XLSX;
        String path = "D:\\file\\excel\\22222.xlsx";

        FileInputStream fileInputStream = new FileInputStream(path);
        ExcelUtil excelUtil = new ExcelUtil(fileInputStream);
        int row = excelUtil.readRowCountNum();
        int cell = excelUtil.readCellCount(row);
        for (int j = 0; j < row; j++) {
            for (int k = 0; k < cell; k++) {
                String s = excelUtil.readExcel(j, k);
                System.out.println("读取到第" + (j + 1) + "行," + (k + 1) + "列的值=" + s);
            }
        }
    }

    /**
     * 以文件方式读取
     *
     * @param file
     * @throws IOException
     */
    @PostMapping(value = "excel/read", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public void readExcelForStreamTest(@RequestParam("file") MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        String filename = file.getOriginalFilename();
        // 设置读取方式
        if (filename.contains(".xlsx")) {
            ExcelUtil.type = Constants.EXCEL_TYPE_XLSX;
        } else if (filename.contains(".xls")) {
            ExcelUtil.type = Constants.EXCEL_TYPE_XLS;
        } else {
            ExcelUtil.type = Constants.EXCEL_TYPE_XLSX;
        }
        ExcelUtil excelUtil = new ExcelUtil(inputStream);
        int row = excelUtil.readRowCountNum();
        int cell = excelUtil.readCellCount(row);
        for (int j = 0; j < row; j++) {
            for (int k = 0; k < cell; k++) {
                String s = excelUtil.readExcel(j, k);
                System.out.println("读取到第" + (j + 1) + "行," + (k + 1) + "列的值=" + s);
            }
        }
    }
```

#### EasyPoi
+ 简介： easypoi底层也是基于 apache poi 进行深度开发的，它主要的特点就是将更多重复的工作，全部简单化，避免编写重复的代码！

##### 添加依赖
```
<!--Easy poi操作Excel start-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>com.googlecode.json-simple</groupId>
            <artifactId>json-simple</artifactId>
            <version>1.1.1</version>
        </dependency>
        <!--Easy poi操作Excel end-->
```
##### 相关操作代码

###### 基础model
```
public class UserExcel {

    @Excel(name = "昵称")
    private String nickName;
    @Excel(name = "真实姓名")
    private String realName;
    @Excel(name = "头像")
    private String avatar;
    @Excel(name = "性别")
    private Integer sex;
    @Excel(name = "类型")
    private Integer type;
    @Excel(name = "手机号")
    private String phone;
    @Excel(name = "创建时间")
    private Date createTime;

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public String getRealName() {
        return realName;
    }

    public void setRealName(String realName) {
        this.realName = realName;
    }

    public String getAvatar() {
        return avatar;
    }

    public void setAvatar(String avatar) {
        this.avatar = avatar;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
}
```
###### 编写测试方法
```
public class EasyPoiExcelUtil {

    /**
     * excel导出操作
     */
    @Test
    public void exportExcel() throws IOException {
        List<UserExcel> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            UserExcel userEntity = new UserExcel();
            userEntity.setNickName("张三" + i);
            userEntity.setAvatar("头像" + i);
            userEntity.setSex(i % 2 == 0 ? 1 : i % 2);
            userEntity.setAvatar("头像" + i);
            userEntity.setRealName("真实姓名" + i);
            userEntity.setPhone("1599485698" + i);
            userEntity.setCreateTime(new Date(System.currentTimeMillis() + i));
            dataList.add(userEntity);
        }
        //生成excel文档
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户", "用户信息"),
                UserExcel.class, dataList);
        FileOutputStream fos = new FileOutputStream("D:\\file\\excel\\easypoi-user.xls");
        workbook.write(fos);
        fos.close();
    }

    @Test
    public void exportDefinitionExcel() throws IOException {
        //封装表头
        List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
        entityList.add(new ExcelExportEntity("姓名", "name"));
        entityList.add(new ExcelExportEntity("年龄", "age"));
        ExcelExportEntity entityTime = new ExcelExportEntity("操作时间", "time");
        entityTime.setFormat("yyyy-MM-dd HH:mm:ss");
        entityTime.setWidth(20.0);
        entityList.add(entityTime);
        //封装数据体
        List<Map<String, Object>> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Map<String, Object> userEntityMap = new HashMap<>();
            userEntityMap.put("name", "张三" + i);
            userEntityMap.put("age", 20 + i);
            userEntityMap.put("time", new Date(System.currentTimeMillis() + i));
            dataList.add(userEntityMap);
        }
        //生成excel文档
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("学生","用户信息"), entityList, dataList);
        FileOutputStream fos = new FileOutputStream("D:\\file\\excel\\easypoi-definition.xls");
        workbook.write(fos);
        fos.close();
    }

    /**
     * 导入excel操作
     */
    @Test
    public void importExcel() {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        long start = System.currentTimeMillis();
        List<UserExcel> list = ExcelImportUtil.importExcel(new File("D:\\file\\excel\\easypoi-user1.xls"), UserExcel.class, params);
        System.out.println("耗时" + (System.currentTimeMillis() - start) + "秒");
        System.out.println(JSONArray.toJSONString(list));
    }
}
```
#### EasyExcel
+ 简介：easyexcel 是阿里巴巴开源的一款 excel 解析工具，底层逻辑也是基于 apache poi 进行二次开发的。不同的是，再读写数据的时候，采用 sax 模式一行一行解析，在并发量很大的情况下，依然能稳定运行

##### 相关依赖
```
<!-- EasyExcel 操作Excel start -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
        <!--常用工具库-->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>29.0-jre</version>
        </dependency>
        <!-- EasyExcel 操作Excel end -->
```
##### 相关操作代码

###### 相关model
```
public class UserEasyExcel {

    @ExcelProperty(value = "昵称")
    private String nickName;
    @ExcelProperty(value = "真实姓名")
    private String realName;
    @ExcelProperty(value = "头像")
    private String avatar;
    @ExcelProperty(value = "性别")
    private Integer sex;
    @ExcelProperty(value = "类型")
    private Integer type;
    @ExcelProperty(value = "手机号")
    private String phone;
    @ExcelProperty(value = "创建时间")
    private Date createTime;

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public String getRealName() {
        return realName;
    }

    public void setRealName(String realName) {
        this.realName = realName;
    }

    public String getAvatar() {
        return avatar;
    }

    public void setAvatar(String avatar) {
        this.avatar = avatar;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }


}
```
###### 编写测试方法
```
public class EasyExcelUtil {

    @Test
    public void exportExcel(){
        List<UserEasyExcel> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            UserEasyExcel userEntity = new UserEasyExcel();
            userEntity.setNickName("张三" + i);
            userEntity.setAvatar("头像" + i);
            userEntity.setSex(i % 2 == 0 ? 1 : i % 2);
            userEntity.setAvatar("头像" + i);
            userEntity.setRealName("真实姓名" + i);
            userEntity.setPhone("1599485698" + i);
            userEntity.setCreateTime(new Date(System.currentTimeMillis() + i));
            dataList.add(userEntity);
        }
        EasyExcel.write("D:\\file\\excel\\easyexcel-user1.xls", UserEasyExcel.class).sheet("用户信息").doWrite(dataList);
    }

    @Test
    public void exportDefinitionExcel(){
        //表头
        List<List<String>> headList = new ArrayList<>();
        headList.add(Lists.newArrayList("姓名"));
        headList.add(Lists.newArrayList("年龄"));
        headList.add(Lists.newArrayList("操作时间"));

        //数据体
        List<List<Object>> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<Object> data = new ArrayList<>();
            data.add("张三" + i);
            data.add(20 + i);
            data.add(new Date(System.currentTimeMillis() + i));
            dataList.add(data);
        }
        EasyExcel.write("D:\\file\\excel\\easyexcel-user2.xls").head(headList).sheet("用户信息").doWrite(dataList);
    }

    @Test
    public void importExcel(){
        String filePath = "D:\\file\\excel\\easyexcel-user1.xls";
        List<UserEasyExcel> list = EasyExcel.read(filePath).head(UserEasyExcel.class).sheet().doReadSync();
        System.out.println(JSONArray.toJSONString(list));
    }
}

```