Java-Excel读写（POI的简单使用）

什么是POI

Apache POI官网 ：https://poi.apache.org/

Apache POI是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。

基本功能
	结构：
	HSSF--提供读写MicrosoftExcel格式档案的功能。 xls
	XSSF--提供读写MicrosoftExcel OOXML格式档案的功能。 xlsx
	SXSSF
	HWPF--提供读写MicrosoftExcel Word格式档案的功能。

POI的简单应用

创建Maven的Moudel

需先配置好Maven，参考我之前的博客：https://www.cnblogs.com/fancy2022/p/16334247.html

注意选择低版本的maven

引入pom依赖

 <!--导入依赖-->
    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
        </dependency>

        <!--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>
        <!--test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

读Excel

首先准备好Excel文件和相应路径

读单个单元格

批量读

读函数

package com.fancy;

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

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

public class ExcelReadTest {

    String PATH = "D:\\project\\JavaExcelTest\\Read\\";
    @Test
    public void testRead() throws Exception{
        //获取文件流
        FileInputStream inputStream = new FileInputStream(PATH+"demo01.xlsx");
        //1.创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        //2.得到表
        Sheet sheet = workbook.getSheetAt(0);
        //3.得到行
        Row row = sheet.getRow(0);
        //4.得到列
        Cell cell = row.getCell(0);
        //5.输出列
            //注意获取值的类型
        System.out.println(cell.getStringCellValue());
        //6.关闭流
        inputStream.close();
    }

    @Test
    public void testCellType() throws Exception{
        //获取文件流
        FileInputStream inputStream = new FileInputStream(PATH+"demo01.xlsx");
        //1.创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        //2.得到表
        Sheet sheet = workbook.getSheetAt(0);
        //3.获取表中的内容
            //读取标题行（第一行）
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null){
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount;cellNum ++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null){
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + "|");
                }
            }
            System.out.println();
        }
            //获取内容行
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1;rowNum < rowCount;rowNum++){
            Row rowData = sheet.getRow(rowNum);
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0;cellNum < cellCount;cellNum++){
                System.out.print("["+ (rowNum+1)+"-"+(cellNum+1)+"]");
                Cell cell = rowData.getCell(cellNum);
                //匹配列的数据类型
                if(cell != null){
                    int cellType =cell.getCellType();
                    String cellValue = "";

                    switch (cellType){
                        case XSSFCell.CELL_TYPE_STRING://字符串
                            System.out.print("【String】");
                            cellValue = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN://布尔
                            System.out.print("【BOOLEAN】");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case XSSFCell.CELL_TYPE_BLANK://空
                            System.out.print("【BLANK】");
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC://数字（日期、普通数字
                            System.out.println("【NUMERIC】");
                            if(HSSFDateUtil.isCellDateFormatted(cell)){
                                System.out.print("【日期】");
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy.MM.dd");
                            }else {//不是日期格式，防止数字过长。
                                System.out.print("【转换为字符串输出】");
                                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case XSSFCell.CELL_TYPE_ERROR:
                            System.out.println("【数据类型错误】");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
        //关闭流
        inputStream.close();

    }


    //将读取Excel的方法转变为一个工具类，传入输入流
    @Test
    public void testCellType(FileInputStream inputStream) throws Exception{
        //获取文件流

        //1.创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        //2.得到表
        Sheet sheet = workbook.getSheetAt(0);
        //3.获取表中的内容
        //读取标题行（第一行）
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null){
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount;cellNum ++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null){
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + "|");
                }
            }
            System.out.println();
        }
        //获取内容行
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1;rowNum < rowCount;rowNum++){
            Row rowData = sheet.getRow(rowNum);
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0;cellNum < cellCount;cellNum++){
                System.out.print("["+ (rowNum+1)+"-"+(cellNum+1)+"]");
                Cell cell = rowData.getCell(cellNum);
                //匹配列的数据类型
                if(cell != null){
                    int cellType =cell.getCellType();
                    String cellValue = "";

                    switch (cellType){
                        case XSSFCell.CELL_TYPE_STRING://字符串
                            System.out.print("【String】");
                            cellValue = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN://布尔
                            System.out.print("【BOOLEAN】");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case XSSFCell.CELL_TYPE_BLANK://空
                            System.out.print("【BLANK】");
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC://数字（日期、普通数字
                            System.out.println("【NUMERIC】");
                            if(HSSFDateUtil.isCellDateFormatted(cell)){
                                System.out.print("【日期】");
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy.MM.dd");
                            }else {//不是日期格式，防止数字过长。
                                System.out.print("【转换为字符串输出】");
                                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case XSSFCell.CELL_TYPE_ERROR:
                            System.out.println("【数据类型错误】");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
        //关闭流
        inputStream.close();

    }
    /**
     *  @Name testFormula
     *  @Description 通过POI使用Excel函数
     *  @Author Fancy
     *  @return 2022/6/5
     *  @Version 1.0
     */
    @Test
    public void testFormula() throws Exception{
        FileInputStream inputStream = new FileInputStream(PATH+"formulaTest.xlsx");
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(5);
        Cell cell = row.getCell(4);

        //拿到计算公式eval
        FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        //输出单元格的内容
        int cellType = cell.getCellType();
        switch (cellType){
            case Cell.CELL_TYPE_FORMULA:
                String formula = cell.getCellFormula();
                System.out.println(formula);

                //计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
        inputStream.close();
    }
}

写Excel

package com.fancy;

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 org.joda.time.DateTime;
import org.junit.Test;


import java.io.FileOutputStream;

public class ExcelWriteTest {
    @Test
    public void testWrite07() throws Exception {
        String Path = "D:\\project\\JavaExcelTest\\Read\\";
        //1.创建一个工作薄
        Workbook workbook = new XSSFWorkbook();
        //2.创建一个工作表
        Sheet sheet1 = workbook.createSheet();
        //3.创建第一行
        Row row1 = sheet1.createRow(0);
        //4.创建第一行第一列位置的单元格
        Cell cell11 = row1.createCell(0);
        //5.写入数据
        cell11.setCellValue("序号");
            //创建第一行第二列位置的单元格
        Cell cell12 = row1.createCell(1);
            //写入数据
        cell12.setCellValue("操作时间");
            //
            //创建第二行第一列位置的单元格
        Row row2 = sheet1.createRow(1);
        Cell cell21 = row2.createCell(0);
            //写入数据
        cell21.setCellValue("1");
        //创建第二行第二列位置的单元格
        Cell cell22 = row2.createCell(1);
            //创建指定格式的当前时间
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
            //写入数据
        cell22.setCellValue(time);
        //6.输出该Excel
            //创建输出流
        FileOutputStream file1 = new FileOutputStream(Path+"test1.xlsx");
            //写出该Excel
        workbook.write(file1);
            //关闭流
        file1.close();
        System.out.println("测试表1已生成！");

    }
}

直接运行

easyExcel读写Excel

出了Apache的POI还有阿里巴巴的easyExcel项目，处理Excel也非常方便

具体请参考：

GitHub地址：GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具

参考文档：EasyExcel · 语雀 (yuque.com)

 

posted @ 2022-06-05 23:07  Fancy[love]  阅读(252)  评论(0)  编辑  收藏  举报