java-poi

使用poi操作excel，仅供学习，实际中推荐easy_excel工具

https://github.com/alibaba/easyexcel/

https://www.yuque.com/easyexcel/doc/easyexcel

1.导入maven依赖

 <dependencies>
        <!--xls03-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <!--xlsx07-->
        <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>
        </dependency>
    </dependencies>

2.写的测试类

package com.coding;

import org.apache.poi.hssf.usermodel.HSSFSheet;
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 org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @description
 * @author: wuliang
 * @date: 2021/2/2310:04
 */
public class ExcelWriteTest {

    // 03 - 65536 ，超出这个记录就会报错
    private final static String PATH = "D:\coding_edu_api\text-excel-poi2";

    @Test
    public void testWriter03() throws IOException {
        //新建一个excel 工作簿 xls
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //创建一个默认的工作表 sheet0
        HSSFSheet sheet = hssfWorkbook.createSheet("会员统计");

        //创建一行记录，从下标0开始，0就代表row1
        Row row1 = sheet.createRow(0);
        //添加每一格数据(1-1)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日注册");
        //(1-2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("999");

        //创建一行记录，从下标0开始，1就代表row2
        Row row2 = sheet.createRow(1);
        //添加每一格数据(1-1)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
        //(1-2)
        Cell cell22 = row2.createCell(1);
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);

        //文件编写完毕，然后需要将数据导出成为xls
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\testWrite03.xls");
        hssfWorkbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("文件生成完毕");
    }

    @Test
    public void testWriter07() throws IOException {
        //easy_excel重新了poi对07版Excel的解析（原生的API太慢了）
        //新建一个excel工作簿xlsx07，对象的变化
        Workbook xssfSheets = new XSSFWorkbook();
        //创建一个默认的工作表 sheet0
        Sheet sheet = xssfSheets.createSheet("会员统计");

        //创建一行记录，从下标0开始，0就代表row1
        Row row1 = sheet.createRow(0);
        //添加每一格数据(1-1)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日注册");
        //(1-2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("999");

        //创建一行记录，从下标0开始，1就代表row2
        Row row2 = sheet.createRow(1);
        //添加每一格数据(1-1)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
        //(1-2)
        Cell cell22 = row2.createCell(1);
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);

        //文件编写完毕，然后需要将数据导出成为xlsx
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\testWrite07.xlsx");
        xssfSheets.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("文件生成完毕");
    }


    // 一般使用这个来处理大的对象！加速 SXSSF
    @Test
    public void testWriter07BigDataFast() throws IOException {

        long begin = System.currentTimeMillis();
        // 创建工作簿。 会产生临时文件，
        // 100条记录保存在内存中，超过这个数据，前面的内容就写入到 excel
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 填写数据
        for (int rowNum = 0; rowNum < 100000; rowNum++) {
            // 创建内容
            Row row = sheet.createRow(rowNum);
            // 填写列的数据
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }

        System.out.println("写入完毕");

        // 文件编写完毕，然后需要数据到处成为 xls
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\testWrite07-bigobject-fast.xls");
        workbook.write(fileOutputStream);
        // 关闭流
        fileOutputStream.close();

        // 建议：清除临时文件
        workbook.dispose();

        long end = System.currentTimeMillis();

        System.out.println((double) (end - begin) / 1000);
    }
}

3.读的测试类

package com.coding;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

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

/**
 * 首页
 *
 * @description
 * @author: wuliang
 * @date: 2021/2/2310:43
 */
public class ExcelReadTest {

    private final static String PATH = "D:\coding_edu_api\text-excel-poi2\";

    @Test
    public void testRead03() throws IOException {
        //要读取的文件
        FileInputStream is = new FileInputStream(PATH + "testWrite03.xls");
        //读取到workbook
        Workbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0); //获取表

        //读取记录（1-1）
        Row row = sheet.getRow(1);
        Cell cell = row.getCell(1);
        System.out.println(cell.getStringCellValue());
        // 关闭流
        is.close();
    }

    @Test
    public void testRead07() throws IOException {
        //  要读取的文件
        FileInputStream is = new FileInputStream(PATH + "testWrite07.xlsx");
        // 读取到Workbook
        Workbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);  // 获取表

        // 读取记录 （1-1）
        Row row = sheet.getRow(1);
        Cell cell = row.getCell(1);

        System.out.println(cell.getStringCellValue());

        // 关闭流
        is.close();
    }

    //读取 数据类型问题，封装为工具类

    @Test
    public void testCellType() throws Exception {
        FileInputStream is = new FileInputStream(PATH + "会员消费商品明细表.xls");
        Workbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);  // 获取表

        // 读取所有的标题（0）
        Row rowTitle = sheet.getRow(0);

        if (rowTitle != null) {
            //读取cell
            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.println(cellValue + "|");
                }
            }
            System.out.println();
        }
        // 读取所有的内容,去掉头
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            // 行
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                //列
                int cellCount = rowData.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    //打印数据
                    System.out.println("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
                    Cell cell = rowData.getCell(cellNum);
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        //判断单元格类型
                        String cellValue = "";
                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING: // 字符串
                                System.out.print("【String】");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔值
                                System.out.print("【BOOLEAN】");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: // 空
                                System.out.print("【BLANK】");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC: // 日期
                                System.out.print("【NUMERIC】");
//                                cellValue = cell.getNumericCellValue();
                                // 日期判断 ， 如果不是日期
                                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期
                                    System.out.println("【日期】");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    System.out.println("【转换为字符串】");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case Cell.CELL_TYPE_ERROR: // 错误
                                System.out.print("【数据类型错误】");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        is.close();
    }


    // 代码实现单元格统计 (数据=> 汇总计算)
    @Test
    public void testFormula() throws Exception {

        //  要读取的文件
        FileInputStream is = new FileInputStream(PATH+"计算公式.xls");
        // 读取到Workbook  03
        Workbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);  // 获取表

        // 读取 5,1   题库（全部都是Excel录入的）
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);

        // 公式计算器 eval
        HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

        // formulaEvaluator 输出单元的内容
        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;
        }

    }
}

4.可以封装成工具类

package com.coding;

//import lombok.Data;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

//@Data
public class ExcelImportUtil {

    private HSSFFormulaEvaluator formulaEvaluator;
    private HSSFSheet sheet;
    private String pattern;// 日期格式

    public ExcelImportUtil() {
        super();
    }

    public ExcelImportUtil(InputStream is) throws IOException {
        this(is, 0, true);
    }

    public ExcelImportUtil(InputStream is, int sheetIndex) throws IOException {
        this(is, sheetIndex, true);
    }

    public ExcelImportUtil(InputStream is, int sheetIndex, boolean evaluateFormular) throws IOException {
        super();
        HSSFWorkbook workbook = new HSSFWorkbook(is);
        this.sheet = workbook.getSheetAt(sheetIndex);
        if (evaluateFormular) {
            this.formulaEvaluator = new HSSFFormulaEvaluator(workbook);
        }
    }

    public String getCellValue(Cell cell) throws Exception {

        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC://0

                if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
                    Date date = cell.getDateCellValue();
                    if (pattern != null) {
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        return sdf.format(date);
                    } else {
                        return date.toString();
                    }
                } else {
                    // 不是日期格式，则防止当数字过长时以科学计数法显示
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    return cell.toString();
                }

            case Cell.CELL_TYPE_STRING://1
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_FORMULA://2

                if (this.formulaEvaluator == null) {//得到公式
                    return cell.getCellFormula();
                } else {//计算公式
                    CellValue evaluate = this.formulaEvaluator.evaluate(cell);
                    return evaluate.formatAsString();
                }
            case Cell.CELL_TYPE_BLANK://3
                //注意空和没有值不一样，从来没有录入过内容的单元格不属于任何数据类型，不会走这个case
                return "";
            case Cell.CELL_TYPE_BOOLEAN://4
                return String.valueOf(cell.getBooleanCellValue());
            case Cell.CELL_TYPE_ERROR:
            default:
                throw new Exception("Excel数据类型错误");
        }
    }
}