Java操作Excel的示例詳解

更新時間：2023年01月10日 10:11:49 作者：糊涂涂是個小盆友

在平時可以使用IO流對Excle進行操作，但是現(xiàn)在使用更加方便的第三方組件來實現(xiàn)。本文就來和大家聊聊Java如何通過第三方組件實現(xiàn)操作Excel，需要的可以參考一下

使用場景

1、將用戶信息導(dǎo)出為Excel表格，導(dǎo)入數(shù)據(jù)

2、將Excel表中的數(shù)據(jù)錄入到網(wǎng)站數(shù)據(jù)庫（習題上傳）減輕網(wǎng)站的錄入量

3、開發(fā)中經(jīng)常會設(shè)計到Excel的處理，導(dǎo)入Excel到數(shù)據(jù)庫中

目前最流行的是 Apache POI以及阿里巴巴easyExcel

excel 03 和 07的區(qū)別

HSSF 對應(yīng)excel中的03版本該版本要求excel中最多只能寫65536行

后綴名為 03.xls

XSSF 對應(yīng)excel中的07版本該版本對于行數(shù)沒有要求

后綴名為 07.xlsx

POI

Apache提供的，會比較麻煩，比較原生

開放源碼函式庫，POI提供API給java程序?qū)ffice格式檔案讀和寫的功能

但是存在內(nèi)存問題 => POI將數(shù)據(jù)會先寫入內(nèi)存中，一旦寫入的內(nèi)容過多時會產(chǎn)生OOM，也叫做內(nèi)存溢出

easyExcel

https://github.com/alibaba/easyexcel

對POI進行了一些優(yōu)化，可以使開發(fā)者更加簡單，讀和寫代碼只需要1行

存在時間的問題 => easyExcel在寫數(shù)據(jù)時是一行一行往磁盤中寫，所以解決了POI的內(nèi)存問題，但是帶來了時間問題

解析excel表中的對象

由于java中萬物皆對象，所以需要先觀察一張excel表中有哪些對象~

1、工作簿

2、工作表

3、行

4、列 => 單元格

POI使用步驟

第一步：創(chuàng)建Maven項目

第二步：導(dǎo)入依賴

<dependencies>
        <!-- xls 03-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- xlsx 07-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- 日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>
        <!--測試-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
</dependencies>

POI 寫數(shù)據(jù)

第一步：基本文件的寫入

private String PATH = "E:\\JavaCode\\Maven\\excel-demo\\src";

03版本測試

@Test
    public void test03() throws Exception {
        // 1 創(chuàng)建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 2 創(chuàng)建工作表
        Sheet sheet1 = workbook.createSheet("表1");
        // 3 創(chuàng)建行  下標從0開始  第一行
        Row row1 = sheet1.createRow(0);

        // 4 創(chuàng)建單元格 (1,1)
        Cell cell1 = row1.createCell(0);
        // 5 往第一個單元格填入數(shù)據(jù)
        cell1.setCellValue("今日新加入");
        // 6 創(chuàng)建第二個單元格(1,2)
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("統(tǒng)計時間");

        // 創(chuàng)建第二行
        Row row2 = sheet1.createRow(1);
        // (2,1)
        Cell cell3 = row2.createCell(0);
        cell3.setCellValue("11000");
        // (2,2)
        Cell cell4 = row2.createCell(1);
        cell4.setCellValue(new DateTime().toString("yyyy-MM-dd hh:mm:ss"));

        // 生成表的IO流 03
        FileOutputStream fos = new FileOutputStream(PATH + "03版本excel.xls");
        // 將工作簿寫入
        workbook.write(fos);

        // 釋放流
        fos.close();
        System.out.println("創(chuàng)建成功");

    }

07版本測試

 @Test
    public void test07() throws Exception {
        // 1 創(chuàng)建工作簿
        Workbook workbook = new SXSSFWorkbook();    //todo
        // 2 創(chuàng)建工作表
        Sheet sheet1 = workbook.createSheet("表1");
        // 3 創(chuàng)建行  下標從0開始  第一行
        Row row1 = sheet1.createRow(0);

        // 4 創(chuàng)建單元格 (1,1)
        Cell cell1 = row1.createCell(0);
        // 5 往第一個單元格填入數(shù)據(jù)
        cell1.setCellValue("今日新加入");
        // 6 創(chuàng)建第二個單元格(1,2)
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("統(tǒng)計時間");

        // 創(chuàng)建第二行
        Row row2 = sheet1.createRow(1);
        // (2,1)
        Cell cell3 = row2.createCell(0);
        cell3.setCellValue("11000");
        // (2,2)
        Cell cell4 = row2.createCell(1);
        cell4.setCellValue(new DateTime().toString("yyyy-MM-dd hh:mm:ss"));

        // 生成表的IO流 03
        FileOutputStream fos = new FileOutputStream(PATH + "07版本excel.xlsx");
        // 將工作簿寫入
        workbook.write(fos);

        // 釋放流
        fos.close();
        System.out.println("創(chuàng)建成功");

    }

第二步：大數(shù)據(jù)寫入

HSSF 寫入

缺點：最多只能處理65536行數(shù)據(jù)，否則會拋出異常 java.lang.IllegalArgumentException

優(yōu)點：過程中寫入緩存，不操作磁盤，最后一次性寫入磁盤，速度快

//    03 版本  多數(shù)據(jù)寫入
    @Test
    public void test03BigData() throws Exception {
        long start = System.currentTimeMillis();

        Workbook workbook = new HSSFWorkbook();
        Sheet s1 = workbook.createSheet("表1");

        for (int row = 0; row < 65536; row++) {
            Row rows = s1.createRow(row);
            for (int cell = 0; cell < 10; cell++) {
                Cell cells = rows.createCell(cell);
                cells.setCellValue(cell);
            }
        }
        FileOutputStream fos = new FileOutputStream(PATH + "03BigData.xls");
        workbook.write(fos);
        fos.close();

        long end = System.currentTimeMillis();
        System.out.println("總耗時:" + (double) (end - start) / 1000 + "秒");
    }

XSSF 寫入

缺點：寫數(shù)據(jù)使速度非常慢，非常耗費內(nèi)存，也會發(fā)生內(nèi)存溢出，如寫100萬條數(shù)據(jù)

優(yōu)點：可以寫比HSSF大的數(shù)據(jù)量，如20萬條數(shù)據(jù)

//    07 版本 低性能  XSSF
    @Test
    public void test07BigData() throws Exception {
        long start = System.currentTimeMillis();

        Workbook workbook = new XSSFWorkbook();
        Sheet s1 = workbook.createSheet("表1");

        for (int row = 0; row < 65536; row++) {
            Row rows = s1.createRow(row);
            for (int cell = 0; cell < 10; cell++) {
                Cell cells = rows.createCell(cell);
                cells.setCellValue(cell);
            }
        }
        FileOutputStream fos = new FileOutputStream(PATH + "07BigData-XSSF.xlsx");
        workbook.write(fos);
        fos.close();

        long end = System.currentTimeMillis();
        System.out.println("總耗時:" + (double) (end - start) / 1000 + "秒");
    }

SXSSF 寫入

優(yōu)點：可以寫非常大的數(shù)據(jù)量，如100萬條，寫的速度也非?？?，占用更少的內(nèi)存

SXSSFWorkbook-來至官方的解釋∶實現(xiàn)"BigGridDemo"策略的流式XSSFWorkbook版本。這允許寫入非常大的文件而不會耗盡內(nèi)存，因為任何時候只有可配置的行部分被保存在內(nèi)存中。

請注意，仍然可能會消耗大量內(nèi)存，這些內(nèi)存基于您正在使用的功能，例如合并區(qū)域，注.…….然只存儲在內(nèi)存中，因此如果廣泛使用，可能需要大量內(nèi)存。可以使用jprofile來監(jiān)控

注意

過程中會產(chǎn)生臨時文件，需要清理臨時文件

默認由100條記錄被保存到內(nèi)存中，如果超過這個數(shù)量，則最前面的數(shù)據(jù)就被寫入臨時文件，如果想自定義內(nèi)存中數(shù)據(jù)的數(shù)量，可以使用 new SXSSFWorkbook（數(shù)量）

 //    07 版本 高性能  SXSS   性能優(yōu)化
    @Test
    public void test07BigData2() throws Exception {
        long start = System.currentTimeMillis();

        Workbook workbook = new SXSSFWorkbook();
        Sheet s1 = workbook.createSheet("表1");

        for (int row = 0; row < 65536; row++) {
            Row rows = s1.createRow(row);
            for (int cell = 0; cell < 10; cell++) {
                Cell cells = rows.createCell(cell);
                cells.setCellValue(cell);
            }
        }
        FileOutputStream fos = new FileOutputStream(PATH + "07BigData-SXSS.xlsx");
        workbook.write(fos);
        fos.close();

        // todo 清除臨時文件 需要強轉(zhuǎn)類型
        ((SXSSFWorkbook) workbook).dispose();

        long end = System.currentTimeMillis();
        System.out.println("總耗時:" + (double) (end - start) / 1000 + "秒");
    }

POI 讀數(shù)據(jù)

在讀取excel表格中單元格中的數(shù)據(jù)的時候，需要注意一點的是:單元格中的數(shù)據(jù)可以有String類型、Number類型、Date類型等，所以需要通過Switch-case來進行判斷獲取，否則會報錯

private String PATH = "E:\\JavaCode\\Maven\\excel-demo\\src";

不用清除臨時文件

HSSF 讀數(shù)據(jù)

@Test
    public void Read03() throws Exception {
        // 0、得到文件輸入流
        FileInputStream fis = new FileInputStream(PATH + "03版本excel.xls");
        // 1、工作簿
        Workbook workbook = new HSSFWorkbook(fis);
        // 2.得到表   可以根據(jù)索引也可以根據(jù)表的名稱
        Sheet sheet = workbook.getSheetAt(0);
        // 3、得到行
        Row row = sheet.getRow(1);
        // 4、得到列   鎖定單元格
        Cell cell = row.getCell(0);
        // 5、根據(jù)類型得到單元格中的內(nèi)容
        String value = cell.getStringCellValue();

        System.out.println(value);
    }

XSSF 讀數(shù)據(jù)

@Test
    public void Read07() throws Exception {
        // 0、得到文件輸入流
        FileInputStream fis = new FileInputStream(PATH + "07版本excel.xls");
        // 1、工作簿
        Workbook workbook = new XSSFWorkbook(fis);
        // 2.得到表   可以根據(jù)索引也可以根據(jù)表的名稱
        Sheet sheet = workbook.getSheetAt(0);
        // 3、得到行
        Row row = sheet.getRow(1);
        // 4、得到列   鎖定單元格
        Cell cell = row.getCell(0);
        // 5、根據(jù)類型得到單元格中的內(nèi)容
        String value = cell.getStringCellValue();

        System.out.println(value);
    }

循環(huán)讀取多個不同類型的數(shù)據(jù)

 // 讀取多個
    @Test
    public void Read03teset02() throws Exception {
       String path = PATH + "人員.xlsx";
       getData(path);
    }
    public static void getData(String path){
        // 0、得到文件輸入流
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(path);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(0);

            // 1.拿到第一行所有為String類型的數(shù)據(jù)
            Row row = sheet.getRow(0);
            if (row != null) {
                int cellCount = row.getPhysicalNumberOfCells();  // 該行總共的單元格數(shù)
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if (cell != null) {
                        String cellTitle = cell.getStringCellValue();
                        System.out.print(cellTitle + "|");
                    }

                }
                System.out.println("");
            }

            // 2.拿到剩下行數(shù)中的數(shù)據(jù)
            int rowCount = sheet.getPhysicalNumberOfRows();  // 總行數(shù)
            for (int rowNum = 1; rowNum < rowCount; rowNum++) {
                Row rowData = sheet.getRow(rowNum);
                int cellCount = rowData.getPhysicalNumberOfCells();  //總單元格數(shù)

                String cellValue = "";

                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = rowData.getCell(cellNum);
                    if (cell != null) {
                        // 判斷類型
                        int cellType = cell.getCellType();

                        switch (cellType) {

                            case Cell.CELL_TYPE_STRING:   //  todo 字符串
                                cellValue = cell.getStringCellValue();
                                break;

                            case Cell.CELL_TYPE_NUMERIC:   // todo 數(shù)值 || 日期
                                if (HSSFDateUtil.isCellDateFormatted(cell)){
                                    // 日期
                                    Date date= cell.getDateCellValue();
                                    cellValue = new DateTime().toString("yyyy-MM-dd hh:mm:ss");
                                }else{
                                    // 數(shù)字   防止數(shù)字過長
                                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;

                            case Cell.CELL_TYPE_BOOLEAN:  // todo 布爾
                                boolean boolean_value = cell.getBooleanCellValue();
                                cellValue = String.valueOf(boolean_value);
                                break;

                            case Cell.CELL_TYPE_BLANK:   //  todo 為空
                                System.out.print("[BLANK]");
                                break;

                            case Cell.CELL_TYPE_ERROR:   //  todo 數(shù)據(jù)類型錯誤
                                System.out.print("[數(shù)據(jù)類型錯誤]");
                                break;
                        }

                        System.out.print(cellValue+"|");
                    }
                }
                System.out.println("");
            }


        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            // 3 釋放資源
            try {
                fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

計算公式

在excel表格中存在著很多個公式如：sum、排序、求平均值等。這個時候就需要進行判斷。了解即可，需要時可以再看

 @Test
    public void test1() throws Exception {
        FileInputStream fis = new FileInputStream("E:\\JavaCode\\Maven\\excel-demo\\公式.xls");
        Workbook workbook = new HSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(2);
        Cell cell = row.getCell(0);

        // 得到表中的計算公式
        FormulaEvaluator FormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

        // 得到單元格的內(nèi)容
        int cellType = cell.getCellType();
        switch (cellType){
            case Cell.CELL_TYPE_FORMULA:  // 公式
                // 拿到公式
                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);
                //計算
                CellValue evaluate = FormulaEvaluator.evaluate(cell);
                System.out.println("evaluate---"+evaluate); // org.apache.poi.ss.usermodel.CellValue [700.0]
                String value = evaluate.formatAsString(); // 將數(shù)據(jù)格式化為字符串
                System.out.println(value);
                break;
        }
        
    }

easyExcel讀寫數(shù)據(jù)

根據(jù)實體類自動生成表

第一步：導(dǎo)入依賴

該依賴中自帶了很多種依賴，如lombok、spring-boot等，需要我們在引入依賴時將自己已經(jīng)導(dǎo)入的依賴刪除，不然會報依賴沖突的錯誤

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>3.1.4</version>
</dependency>
<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<version>1.18.8</version>
</dependency>
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>fastjson</artifactId>
	<version>1.2.75</version>
</dependency>

寫數(shù)據(jù)

第二步：創(chuàng)建excel表對應(yīng)的實體類

@Data
public class DemoData {
    @ExcelProperty("字符串標題")
    private String string;
    @ExcelProperty("日期標題")
    private Date date;
    @ExcelProperty("數(shù)字標題")
    private Double aDouble;
    // 忽略該字段
    @ExcelIgnore
    private String ignore;
}

第三步：編寫設(shè)置數(shù)據(jù)的方法

使用集合list來寫入excel

 public List easyTest1(){
       List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串"+i);
            data.setDate(new Date());
            data.setADouble(0.12);
            list.add(data);
        }
        return list;
    }

第四步：編寫測試類

使用鏈式編寫的方式

write(文件路徑，excel表對應(yīng)的java類)

sheet(設(shè)置表名)

doWrite(數(shù)據(jù))

 @Test
    public void test1(){
        String fileName ="E:\\JavaCode\\Maven\\excel-demo\\easyEasyData.xlsx";
        EasyExcel.write(fileName,DemoData.class).sheet("表1").doWrite(data());
    }

讀數(shù)據(jù)

1、每執(zhí)行一條excel表中的數(shù)據(jù)都會執(zhí)行一次監(jiān)聽文件中的invoke方法，所以如果需要修改可以修改invoke方法中的內(nèi)容

2、DemoDataListener 不能被spring管理，要每次讀取excel都要new,然后里面用到spring可以構(gòu)造方法傳進去

第二步：準備一個對應(yīng)excel表中字段的類

與寫操作中使用同一個類

@Data
public class DemoData {
    @ExcelProperty("字符串標題")
    private String string;
    @ExcelProperty("日期標題")
    private Date date;
    @ExcelProperty("數(shù)字標題")
    private Double aDouble;
    // 忽略該字段
    @ExcelIgnore
    private String ignore;
}

第三步：創(chuàng)建數(shù)據(jù)層 Mapper || Dao

public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,盡量別直接調(diào)用多次insert,自己寫一個mapper里面新增一個方法batchInsert,所有數(shù)據(jù)一次性插入
    }
}

第四步：創(chuàng)建監(jiān)聽器

package excel.readEasy;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import excel.easy.DemoData;
import lombok.extern.slf4j.Slf4j;

import java.util.List;
import java.util.Map;

// 有個很重要的點 DemoDataListener 不能被spring管理，要每次讀取excel都要new,然后里面用到spring可以構(gòu)造方法傳進去
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {
    //每隔5條存儲數(shù)據(jù)庫，實際使用中可以100條，然后清理list ，方便內(nèi)存回收
    private static final int BATCH_COUNT = 100;
    //緩存的數(shù)據(jù)
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);


    //假設(shè)這個是一個DAO，當然有業(yè)務(wù)邏輯這個也可以是一個service。當然如果不用存儲這個對象沒用
    private DemoDAO demoDAO;

    // 這里是demo，所以隨便new一個。實際使用如果到了spring,請使用下面的有參構(gòu)造函數(shù)
    public DemoDataListener() {
        demoDAO = new DemoDAO();
    }

    //如果使用了spring,請使用這個構(gòu)造方法。每次創(chuàng)建Listener的時候需要把spring管理的類傳進來
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }


    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {

    }

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        System.out.println("111");
    }

    //這個每一條數(shù)據(jù)解析都會來調(diào)用
    @Override
    public void invoke(DemoData data, AnalysisContext analysisContext) {
        System.out.println("2222");
        System.out.println(JSON.toJSONString(data));
        //  log.info("解析到一條數(shù)據(jù):{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 達到BATCH_COUNT了，需要去存儲一次數(shù)據(jù)庫，防止數(shù)據(jù)幾萬條數(shù)據(jù)在內(nèi)存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存儲完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {

    }

    //所有數(shù)據(jù)解析完成了 都會來調(diào)用
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        log.info("所有數(shù)據(jù)解析完成！");
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return false;
    }

    /**
     * 加上存儲數(shù)據(jù)庫
     */
    private void saveData() {
        log.info("{}條數(shù)據(jù)，開始存儲數(shù)據(jù)庫！", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存儲數(shù)據(jù)庫成功！");
    }
}

第五步：測試

@Test
    public void test3() {
        String fileName = "E:\\JavaCode\\Maven\\excel-demo\\easyEasyData.xlsx";
        EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
            for (DemoData demoData : dataList) {
                System.out.println(JSON.toJSONString(demoData));
            }
        })).sheet().doRead();
    }

以上就是Java操作Excel的示例詳解的詳細內(nèi)容，更多關(guān)于Java操作Excel的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: