App下載

關(guān)于Java操作excel的三種常見(jiàn)方法的代碼實(shí)例

猿友 2021-07-16 11:56:46 瀏覽數(shù) (2184)
反饋

常用方法:

一、Apache poi

大概在很久很久以前,微軟的電子表格軟件 Excel 以操作簡(jiǎn)單、存儲(chǔ)數(shù)據(jù)直觀方便,還支持打印報(bào)表,在誕生之初,可謂深得辦公室里的白領(lǐng)青睞,極大的提升了工作的效率,不久之后,便成了辦公室里的必備工具。

隨著更多的新語(yǔ)言的崛起,例如我們所熟悉的 java,后來(lái)便有一些團(tuán)隊(duì)開(kāi)始開(kāi)發(fā)一套能與 Excel 軟件無(wú)縫切換的操作工具!

當(dāng)然,在java生態(tài)體系里面,能與Excel無(wú)縫銜接的第三方工具還有很多,在開(kāi)始也給大家列出來(lái)三個(gè),因?yàn)?apache poi 在業(yè)界使用的最廣泛,因此其他的工具不做過(guò)多介紹!

話不多說(shuō),直接開(kāi)擼!

1.1 首先添加依賴(lài)

<dependencies>
    <!--xls(03)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
    </dependency>
    <!--xlsx(07)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.2</version>
    </dependency>
    <!--時(shí)間格式化工具-->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.6</version>
    </dependency>
</dependencies>

1.2 導(dǎo)出excel

導(dǎo)出操作,即使用 Java 寫(xiě)出數(shù)據(jù)到 Excel 中,常見(jiàn)場(chǎng)景是將頁(yè)面上的數(shù)據(jù)導(dǎo)出,這些數(shù)據(jù)可能是財(cái)務(wù)數(shù)據(jù),也可能是商品數(shù)據(jù),生成 Excel 后返回給用戶(hù)下載文件。

在 poi 工具庫(kù)中,導(dǎo)出 api 可以分三種方式

  • HSSF方式: 這種方式導(dǎo)出的文件格式為office 2003專(zhuān)用格式,即.xls,優(yōu)點(diǎn)是導(dǎo)出數(shù)據(jù)速度快,但是 最多65536行 數(shù)據(jù)
  • XSSF方式: 這種方式導(dǎo)出的文件格式為office 2007專(zhuān)用格式,即.xlsx,優(yōu)點(diǎn)是導(dǎo)出的數(shù)據(jù)不受行數(shù)限制,缺點(diǎn)導(dǎo)出速度慢
  • SXSSF方式: SXSSF 是 XSSF API的兼容流式擴(kuò)展,主要解決當(dāng)使用 XSSF 方式導(dǎo)出大數(shù)據(jù)量時(shí),內(nèi)存溢出的問(wèn)題,支持導(dǎo)出大批量的excel數(shù)據(jù)

1.2.1 HSSF方式導(dǎo)出(.xls方式)

HSSF方式,最多只支持65536條數(shù)據(jù)導(dǎo)出,超過(guò)這個(gè)條數(shù)會(huì)報(bào)錯(cuò)!

package cn.tedu.excel.test;

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 java.io.FileOutputStream;

/**
 * HSSF方式導(dǎo)出:HSSF方式,最多只支持65536條數(shù)據(jù)導(dǎo)出,超過(guò)這個(gè)條數(shù)會(huì)報(bào)錯(cuò)!
 * 就是.xls模式
 */
public class ExcelWrite2003Test {
    private static  String PATH = "/Users/lixin/Desktop/";//自己輸出的路徑

    public static void main(String[] args) throws Exception {
        //時(shí)間
        long begin = System.currentTimeMillis();

        //創(chuàng)建一個(gè)工作簿
        Workbook workbook = new HSSFWorkbook();
        //創(chuàng)建表
        Sheet sheet = workbook.createSheet();
        //寫(xiě)入數(shù)據(jù)
        for (int rowNumber = 0; rowNumber < 65536; rowNumber++) {
            //創(chuàng)建行
            Row row = sheet.createRow(rowNumber);
            for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
                //創(chuàng)建列
                Cell cell = row.createCell(cellNumber);
                cell.setCellValue(cellNumber);
            }
        }
        System.out.println("結(jié)束!");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "用戶(hù)信息表-XLS.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println("時(shí)間為:"+(double) (end - begin) / 1000);//2.262s
    }
}

1.2.2 XSSF方式導(dǎo)出(.xlsx)

XSSF方式支持大批量數(shù)據(jù)導(dǎo)出,所有的數(shù)據(jù)先寫(xiě)入內(nèi)存再導(dǎo)出,容易出現(xiàn)內(nèi)存溢出!

package cn.tedu.excel.test;

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;

/**
 * .xlsx方式
 */
public class ExcelWrite2007Test {
    public static String PATH = "/Users/lixin/Desktop/";

    public static void main(String[] args) throws Exception {
        //時(shí)間
        long begin = System.currentTimeMillis();

        //創(chuàng)建一個(gè)工作簿
        Workbook workbook = new XSSFWorkbook();
        //創(chuàng)建表
        Sheet sheet = workbook.createSheet();
        //寫(xiě)入數(shù)據(jù)
        for (int rowNumber = 0; rowNumber < 65537; rowNumber++) {
            Row row = sheet.createRow(rowNumber);
            for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
                Cell cell = row.createCell(cellNumber);
                cell.setCellValue(cellNumber);
            }
        }
        System.out.println("結(jié)束");

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "用戶(hù)信息表-XLSX.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);//5.003s
    }
}

1.2.3、SXSSF方式導(dǎo)出

SXSSF方式是XSSF方式的一種延伸,主要特性是低內(nèi)存,導(dǎo)出的時(shí)候,先將數(shù)據(jù)寫(xiě)入磁盤(pán)再導(dǎo)出,避免報(bào)內(nèi)存不足,導(dǎo)致程序運(yùn)行異常,缺點(diǎn)是運(yùn)行很慢!

package cn.tedu.excel.test;

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.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;

public class ExcelWriteSXSSFTest {
    public static String PATH = "/Users/lixin/Desktop/";

    public static void main(String[] args) throws Exception {
        //時(shí)間
        long begin = System.currentTimeMillis();

        //創(chuàng)建一個(gè)工作簿
        Workbook workbook = new SXSSFWorkbook();

        //創(chuàng)建表
        Sheet sheet = workbook.createSheet();

        //寫(xiě)入數(shù)據(jù)
        for (int rowNumber = 0; rowNumber < 100000; rowNumber++) {
            Row row = sheet.createRow(rowNumber);
            for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
                Cell cell = row.createCell(cellNumber);
                cell.setCellValue(cellNumber);
            }
        }
        System.out.println("over");

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "用戶(hù)信息表-SXSSF.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);//6.39s
    }
}

1.3 導(dǎo)入excel

導(dǎo)入操作,即將 excel 中的數(shù)據(jù)采用java工具庫(kù)將其解析出來(lái),進(jìn)而將 excel 數(shù)據(jù)寫(xiě)入數(shù)據(jù)庫(kù)!

同樣,在 poi 工具庫(kù)中,導(dǎo)入 api 也分三種方式,與上面的導(dǎo)出一一對(duì)應(yīng)!

1.3.1 HSSF方式導(dǎo)入

package cn.tedu.excel.test;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.joda.time.DateTime;

import java.io.FileInputStream;
import java.util.Date;

public class ExcelRead2003Test {
    public static String PATH = "/Users/lixin/Desktop/";

    public static void main(String[] args) throws Exception {
        //獲取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "用戶(hù)信息表2003read.xls");

        //1.創(chuàng)建工作簿,使用excel能操作的這邊都看看操作
        Workbook workbook = new HSSFWorkbook(inputStream);
        //2.得到表
        Sheet sheet = workbook.getSheetAt(0);
        //3.得到行
        Row row = sheet.getRow(0);
        //4.得到列
        Cell cell = row.getCell(0);
        getValue(cell);
        inputStream.close();
    }

    public static void getValue(Cell cell){
        //匹配類(lèi)型數(shù)據(jù)
        if (cell != null) {
            CellType cellType = cell.getCellType();
            String cellValue = "";
            switch (cellType) {
                case STRING: //字符串
                    System.out.print("[String類(lèi)型]");
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN: //布爾類(lèi)型
                    System.out.print("[boolean類(lèi)型]");
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case BLANK: //空
                    System.out.print("[BLANK類(lèi)型]");
                    break;
                case NUMERIC: //數(shù)字(日期、普通數(shù)字)
                    System.out.print("[NUMERIC類(lèi)型]");
                    if (HSSFDateUtil.isCellDateFormatted(cell)) { //日期
                        System.out.print("[日期]");
                        Date date = cell.getDateCellValue();
                        cellValue = new DateTime(date).toString("yyyy-MM-dd");
                    } else {
                        //不是日期格式,防止數(shù)字過(guò)長(zhǎng)
                        System.out.print("[轉(zhuǎn)換為字符串輸出]");
                        cell.setCellType(CellType.STRING);
                        cellValue = cell.toString();
                    }
                    break;
                case ERROR:
                    System.out.print("[數(shù)據(jù)類(lèi)型錯(cuò)誤]");
                    break;
            }
            System.out.println(cellValue);
        }
    }
}

輸出結(jié)果類(lèi)似如圖所示:

輸出結(jié)果1

1.3.2 XSSF方式導(dǎo)入

package cn.tedu.excel.test;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;

import java.io.FileInputStream;
import java.util.Date;

public class ExcelRead2007Test {
    public static String PATH = "/Users/lixin/Desktop/";

    public static void main(String[] args) throws Exception {
        //獲取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "用戶(hù)信息表2007read.xlsx");

        //1.創(chuàng)建工作簿,使用excel能操作的這邊都看看操作
        Workbook workbook = new XSSFWorkbook(inputStream);
        //2.得到表
        Sheet sheet = workbook.getSheetAt(0);
        //3.得到行
        Row row = sheet.getRow(0);
        //4.得到列
        Cell cell = row.getCell(0);
        getValue(cell);
        inputStream.close();
    }
    public static void getValue(Cell cell){
        //匹配類(lèi)型數(shù)據(jù)
        if (cell != null) {
            CellType cellType = cell.getCellType();
            String cellValue = "";
            switch (cellType) {
                case STRING: //字符串
                    System.out.print("[String類(lèi)型]");
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN: //布爾類(lèi)型
                    System.out.print("[boolean類(lèi)型]");
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case BLANK: //空
                    System.out.print("[BLANK類(lèi)型]");
                    break;
                case NUMERIC: //數(shù)字(日期、普通數(shù)字)
                    System.out.print("[NUMERIC類(lèi)型]");
                    if (HSSFDateUtil.isCellDateFormatted(cell)) { //日期
                        System.out.print("[日期]");
                        Date date = cell.getDateCellValue();
                        cellValue = new DateTime(date).toString("yyyy-MM-dd");
                    } else {
                        //不是日期格式,防止數(shù)字過(guò)長(zhǎng)
                        System.out.print("[轉(zhuǎn)換為字符串輸出]");
                        cell.setCellType(CellType.STRING);
                        cellValue = cell.toString();
                    }
                    break;
                case ERROR:
                    System.out.print("[數(shù)據(jù)類(lèi)型錯(cuò)誤]");
                    break;
            }
            System.out.println(cellValue);
        }
    }
}

1.3.3 SXSSF方式導(dǎo)入

package cn.tedu.excel.test;

import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.stream.Collectors;

public class ExcelReadSXSSFTest {
    public static String PATH = "/Users/lixin/Desktop/";

    public static void main(String[] args) throws Exception {
        //獲取文件流

        //1.創(chuàng)建工作簿,使用excel能操作的這邊都看看操作
        OPCPackage opcPackage = OPCPackage.open(PATH + "用戶(hù)信息表2007read.xlsx");
        XSSFReader xssfReader = new XSSFReader(opcPackage);
        StylesTable stylesTable = xssfReader.getStylesTable();
        ReadOnlySharedStringsTable sharedStringsTable = new ReadOnlySharedStringsTable(opcPackage);
        // 創(chuàng)建XMLReader,設(shè)置ContentHandler
        XMLReader xmlReader = SAXHelper.newXMLReader();
        xmlReader.setContentHandler(new XSSFSheetXMLHandler(stylesTable, sharedStringsTable, new SimpleSheetContentsHandler(), false));
        // 解析每個(gè)Sheet數(shù)據(jù)
        Iterator<InputStream> sheetsData = xssfReader.getSheetsData();
        while (sheetsData.hasNext()) {
            try (InputStream inputStream = sheetsData.next();) {
                xmlReader.parse(new InputSource(inputStream));
            }
        }
    }
    /**
     * 內(nèi)容處理器
     */
    public static class SimpleSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

        protected List<String> row;

        @Override
        public void startRow(int rowNum) {
            row = new ArrayList<>();
        }

        @Override
        public void endRow(int rowNum) {
            if (row.isEmpty()) {
                return;
            }
            // 處理數(shù)據(jù)
            System.out.println(row.stream().collect(Collectors.joining("   ")));
        }

        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            row.add(formattedValue);
        }

        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {
        }
    }
}

輸出結(jié)果2

二、Easypoi

以前的以前,有個(gè)大佬程序員,跳到一家公司之后就和業(yè)務(wù)人員聊上了,這些業(yè)務(wù)員對(duì)excel報(bào)表有著許許多多的要求,比如想要一個(gè)報(bào)表,他的表頭是一個(gè)多行表頭,過(guò)幾天之后,他想要給這些表頭添加樣式,比如關(guān)鍵的數(shù)據(jù)標(biāo)紅,再過(guò)幾天,他想要再末尾添加一條合計(jì)的數(shù)據(jù),等等!

起初還好,都是copy、copy,之后發(fā)現(xiàn)系統(tǒng)中出現(xiàn)大量的重復(fù)代碼,于是有一天真的忍受不了了,采用注解搞定來(lái)搞定這些定制化成程度高的邏輯,將公共化抽離出來(lái),于是誕生了 easypoi!它的底層也是基于 apache poi 進(jìn)行深度開(kāi)發(fā)的,它主要的特點(diǎn)就是將更多重復(fù)的工作,全部簡(jiǎn)單化,避免編寫(xiě)重復(fù)的代碼!

下面,我們就一起來(lái)了解一下這款高大上的開(kāi)源工具:easypoi

2.1 添加依賴(lài)包

<dependencies>
    <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>
</dependencies>

2.2 采用注解導(dǎo)出導(dǎo)入

easypoi 最大的亮點(diǎn)就是基于注解實(shí)體類(lèi)來(lái)導(dǎo)出、導(dǎo)入excel,使用起來(lái)非常簡(jiǎn)單!

我們創(chuàng)建一個(gè)實(shí)體類(lèi)UserEntity,其中@Excel注解表示導(dǎo)出文件的頭部信息。

添加Lombok插件,替代set和get方法

2.2.1 導(dǎo)出操作

package cn.tedu.excel.easypoi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserEntity {
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年齡")
    private int age;
    @Excel(name = "操作時(shí)間",format="yyyy-MM-dd HH:mm:ss", width = 20.0)
    private Date time;

    public static void main(String[] args) throws Exception {
        List<UserEntity> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            UserEntity userEntity = new UserEntity();
            userEntity.setName("張三" + i);
            userEntity.setAge(20 + i);
            userEntity.setTime(new Date(System.currentTimeMillis() + i));
            dataList.add(userEntity);
        }
        //生成excel文檔
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用戶(hù)","用戶(hù)信息"),
                UserEntity.class, dataList);
        FileOutputStream fos = new FileOutputStream("/Users/lixin/Desktop/easypoi-user.xls");
        workbook.write(fos);
        fos.close();
    }
}

導(dǎo)出文件預(yù)覽圖如下:


202148100806411

2.2.2 導(dǎo)入操作

package cn.tedu.excel.easypoi;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.File;
import java.util.Date;
import java.util.List;
import org.json.simple.JSONArray;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class StudentEntity {
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年齡")
    private int age;
    @Excel(name = "操作時(shí)間",format="yyyy-MM-dd HH:mm:ss", width = 20.0)
    private Date time;

    public static void main(String[] args) {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        long start = new Date().getTime();
        List<StudentEntity> list = ExcelImportUtil.importExcel(new File("/Users/lixin/Desktop/easypoi-user1.xls"),
                UserEntity.class, params);
        System.out.println(new Date().getTime() - start);
        System.out.println(JSONArray.toJSONString(list));
    }
}

輸出結(jié)果為:

[UserEntity(name=張三0, age=20, time=Mon Mar 29 11:29:52 CST 2021),UserEntity(name=李四, age=21, time=Mon Mar 29 11:29:52 CST 2021),UserEntity(name=王武, age=22, time=Mon Mar 29 11:29:52 CST 2021),UserEntity(name=趙六, age=23, time=Mon Mar 29 11:29:52 CST 2021),UserEntity(name=null, age=0, time=null),UserEntity(name=null, age=0, time=null),UserEntity(name=null, age=0, time=null),UserEntity(name=null, age=0, time=null),UserEntity(name=null, age=0, time=null),UserEntity(name=null, age=0, time=null)]

2.3 自定義數(shù)據(jù)結(jié)構(gòu)導(dǎo)出導(dǎo)入

easypoi 同樣也支持自定義數(shù)據(jù)結(jié)構(gòu)導(dǎo)出導(dǎo)入excel。

自定義數(shù)據(jù)導(dǎo)出 excel

2.3.1 導(dǎo)出操作

public static void main(String[] args) throws Exception {
    //封裝表頭
    List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
    entityList.add(new ExcelExportEntity("姓名", "name"));
    entityList.add(new ExcelExportEntity("年齡", "age"));
    ExcelExportEntity entityTime = new ExcelExportEntity("操作時(shí)間", "time");
    entityTime.setFormat("yyyy-MM-dd HH:mm:ss");
    entityTime.setWidth(20.0);
    entityList.add(entityTime);
    //封裝數(shù)據(jù)體
    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("學(xué)生","用戶(hù)信息"), entityList, dataList);
    FileOutputStream fos = new FileOutputStream("/Users/lixin/Desktop/easypoi-user2.xls");
    workbook.write(fos);
    fos.close();
}

2.3.2 導(dǎo)入操作

public static void main(String[] args) {
    ImportParams params = new ImportParams();
    params.setTitleRows(1);
    params.setHeadRows(1);
    long start = new Date().getTime();
    List<Map<String, Object>> list = ExcelImportUtil.importExcel(new File("/Users/lixin/Desktop/easypoi-user2.xls"),
            Map.class, params);
    System.out.println(new Date().getTime() - start);
    System.out.println(JSONArray.toJSONString(list));
}

更多的 api 操作可以訪問(wèn) Easypoi - 接口文檔

三、Easyexcel

easyexcel 是阿里巴巴開(kāi)源的一款 excel 解析工具,底層邏輯也是基于 apache poi 進(jìn)行二次開(kāi)發(fā)的。不同的是,再讀寫(xiě)數(shù)據(jù)的時(shí)候,采用 sax 模式一行一行解析,在并發(fā)量很大的情況下,依然能穩(wěn)定運(yùn)行!

下面,我們就一起來(lái)了解一下這款新起之秀!

3.1 添加依賴(lài)包

<!-- EasyExcel -->
<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>2.2.6</version>
 </dependency>
 <!--常用工具庫(kù)-->
 <dependency>
     <groupId>com.google.guava</groupId>
     <artifactId>guava</artifactId>
     <version>29.0-jre</version>
 </dependency>

3.2 采用注解導(dǎo)出導(dǎo)入

easyexcel 同樣也支持采用注解方式進(jìn)行導(dǎo)出、導(dǎo)入!

首先,我們創(chuàng)建一個(gè)實(shí)體類(lèi)UserEntity,其中@ExcelProperty注解表示導(dǎo)出文件的頭部信息。

3.2.1 導(dǎo)出操作

package cn.tedu.excel.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.format.annotation.DateTimeFormat;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserEntity {
    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "年齡")
    private int age;

    @DateTimeFormat(fallbackPatterns = "yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = "操作時(shí)間")
    private Date time;

    public static void main(String[] args) {
        List<UserEntity> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            UserEntity userEntity = new UserEntity();
            userEntity.setName("張三" + i);
            userEntity.setAge(20 + i);
            userEntity.setTime(new Date(System.currentTimeMillis() + i));
            dataList.add(userEntity);
        }
        EasyExcel.write("/Users/lixin/Desktop/easyexcel-user1.xls", UserEntity.class).sheet("用戶(hù)信息").doWrite(dataList);
    }
}

導(dǎo)出預(yù)覽圖:

導(dǎo)出預(yù)覽圖2

3.2.2 導(dǎo)入操作

package cn.tedu.excel.easyexcel;

import com.alibaba.excel.EasyExcel;
import org.json.simple.JSONArray;

import java.util.List;

public class DemoData {
    public static void main(String[] args) {
        String filePath = "/Users/lixin/Desktop/easyexcel-user1.xls";
        List<DemoData> list = EasyExcel.read(filePath).head(UserEntity.class).sheet().doReadSync();
        System.out.println(JSONArray.toJSONString(list));
    }
}

結(jié)果顯示:

[UserEntity(name=張三0, age=20, time=Mon Mar 29 16:42:20 CST 2021),UserEntity(name=張三1, age=21, time=Mon Mar 29 16:42:20 CST 2021),UserEntity(name=張三2, age=22, time=Mon Mar 29 16:42:20 CST 2021),UserEntity(name=張三3, age=23, time=Mon Mar 29 16:42:20 CST 2021),UserEntity(name=張三4, age=24, time=Mon Mar 29 16:42:20 CST 2021),UserEntity(name=張三5, age=25, time=Mon Mar 29 16:42:20 CST 2021),UserEntity(name=張三6, age=26, time=Mon Mar 29 16:42:20 CST 2021),UserEntity(name=張三7, age=27, time=Mon Mar 29 16:42:20 CST 2021),UserEntity(name=張三8, age=28, time=Mon Mar 29 16:42:20 CST 2021),UserEntity(name=張三9, age=29, time=Mon Mar 29 16:42:20 CST 2021)]

3.3 自定義數(shù)據(jù)結(jié)構(gòu)導(dǎo)出導(dǎo)入

easyexcel 同樣也支持自定義數(shù)據(jù)結(jié)構(gòu)導(dǎo)出導(dǎo)入excel。

3.3.1 導(dǎo)出操作

public static void main(String[] args) {
    //表頭
    List<List<String>> headList = new ArrayList<>();
    headList.add(Lists.newArrayList("姓名"));
    headList.add(Lists.newArrayList("年齡"));
    headList.add(Lists.newArrayList("操作時(shí)間"));

    //數(shù)據(jù)體
    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("/Users/hello/Documents/easyexcel-user2.xls").head(headList).sheet("用戶(hù)信息").doWrite(dataList);
}

3.3.2 導(dǎo)入操作

public static void main(String[] args) {
    String filePath = "/Users/panzhi/Documents/easyexcel-user2.xls";
    UserDataListener userDataListener = new UserDataListener();
    EasyExcel.read(filePath, userDataListener).sheet().doRead();
    System.out.println("表頭:" + JSONArray.toJSONString(userDataListener.getHeadList()));
    System.out.println("數(shù)據(jù)體:" + JSONArray.toJSONString(userDataListener.getDataList()));
}

運(yùn)行結(jié)果如圖所示:

表頭:[{0:"姓名",1:"年齡",2:"操作時(shí)間"}]
數(shù)據(jù)體:[{0:"張三0",1:"20",2:"2021-03-29 16:31:39"},{0:"張三1",1:"21",2:"2021-03-29 16:31:39"},{0:"張三2",1:"22",2:"2021-03-29 16:31:39"},{0:"張三3",1:"23",2:"2021-03-29 16:31:39"},{0:"張三4",1:"24",2:"2021-03-29 16:31:39"},{0:"張三5",1:"25",2:"2021-03-29 16:31:39"},{0:"張三6",1:"26",2:"2021-03-29 16:31:39"},{0:"張三7",1:"27",2:"2021-03-29 16:31:39"},{0:"張三8",1:"28",2:"2021-03-29 16:31:39"},{0:"張三9",1:"29",2:"2021-03-29 16:31:39"}]

更多的 api 操作可以訪問(wèn) easyexcel - 接口文檔!

四、總結(jié)

總體來(lái)說(shuō),Easypoi 和 Easyexcel 都是基于Apache poi進(jìn)行二次開(kāi)發(fā)的。

不同點(diǎn)在于:

  • Easypoi 在讀寫(xiě)數(shù)據(jù)的時(shí)候,優(yōu)先是先將數(shù)據(jù)寫(xiě)入內(nèi)存,優(yōu)點(diǎn)是讀寫(xiě)性能非常高,但是當(dāng)數(shù)據(jù)量很大的時(shí)候,會(huì)出現(xiàn)oom,當(dāng)然它也提供了 sax 模式的讀寫(xiě)方式,需要調(diào)用特定的方法實(shí)現(xiàn)。
  • Easyexcel 基于sax模式進(jìn)行讀寫(xiě)數(shù)據(jù),不會(huì)出現(xiàn)oom情況,程序有過(guò)高并發(fā)場(chǎng)景的驗(yàn)證,因此程序運(yùn)行比較穩(wěn)定,相對(duì)于 Easypoi 來(lái)說(shuō),讀寫(xiě)性能稍慢!

Easypoi 與 Easyexcel 還有一點(diǎn)區(qū)別在于,Easypoi 對(duì)定制化的導(dǎo)出支持非常的豐富,如果當(dāng)前的項(xiàng)目需求,并發(fā)量不大、數(shù)據(jù)量也不大,但是需要導(dǎo)出 excel 的文件樣式千差萬(wàn)別,那么我推薦你用 easypoi;反之,使用 easyexcel !

以上就是關(guān)于 Java 操作 excel 的三種常見(jiàn)方法的代碼實(shí)例的全部?jī)?nèi)容,想要了解更多 Java 的內(nèi)容請(qǐng)搜索W3Cschool以前的文章或繼續(xù)瀏覽下面的相關(guān)文章,希望大家以后多多支持我們!


0 人點(diǎn)贊