Java：apache.poi读写Excel文件

依赖

    org.apache.poi
    poi-ooxml
    3.16

备注：poi-ooxml 已经包含了poi、poi-ooxml-schemas、curvesapi，不需要单独引入

文件格式对应处理类

.xls  => HSSFWorkbook
.xlsx => XSSFWorkbook

快速入门

写入文件

package com.demo.excel;

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;
import java.io.IOException;
import java.io.OutputStream;

public class WriteExcel {
    public static void main(String[] args) throws IOException {

        // 创建工作表
        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet("名称");

        // 行
        Row row = sheet.createRow(0);

        // 单元格
        Cell cell = row.createCell(0);
        cell.setCellValue("姓名");

        // 写到文件
        OutputStream outputStream = new FileOutputStream("demo.xlsx");
        workbook.write(outputStream);
        outputStream.close();

        //关闭
        workbook.close();
    }
}

读取文件

package com.demo.excel;

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.*;

public class ReadExcel {
    public static void main(String[] args) throws IOException {

        // 创建工作表
        InputStream inputStream = new FileInputStream("demo.xlsx");
        Workbook workbook = new XSSFWorkbook(inputStream);

        Sheet sheet = workbook.getSheet("名称");

        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        String value = cell.getStringCellValue();
        System.out.println(value);

        //关闭
        inputStream.close();
        workbook.close();
    }
}

Excel读写工具

package com.demo.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel读写工具，兼容xls 和 xlsx格式
 */
public class ExcelUtil {

    /**
     * 获取扩展名
     *
     * @param filename 文件路径
     * @return
     */
    public static String getSuffix(String filename) {
        return filename.substring(filename.lastIndexOf("."));
    }

    /**
     * 兼容xls xlsx文件格式
     *
     * @param filename 文件路径
     * @return
     */
    public static Workbook readWorkBook(String filename) throws IOException {

        // 获取文件扩展名
        String ext = getSuffix(filename);

        InputStream inputStream = new FileInputStream(filename);

        Workbook workbook = null;
        // xls格式的需要使用HSSFWorkbook类来解析
        // xlsx格式的需要使用XSSFWorkbook格式来解析
        if (".xls".equals(ext.toLowerCase())) {
            workbook = new HSSFWorkbook(inputStream);
        } else {
            workbook = new XSSFWorkbook(inputStream);
        }

        return workbook;

    }

    /**
     * 创建一个文件
     *
     * @param filename
     * @return
     */
    public static Workbook createWorkBook(String filename) {
        // 获取文件扩展名
        String ext = getSuffix(filename);

        Workbook workbook = null;
        // xls格式的需要使用HSSFWorkbook类来解析
        // xlsx格式的需要使用XSSFWorkbook格式来解析
        if (".xls".equals(ext.toLowerCase())) {
            workbook = new HSSFWorkbook();
        } else {
            workbook = new XSSFWorkbook();
        }

        return workbook;
    }

    /**
     * 读取Excel 文件
     *
     * @param filename
     * @return 列表数据
     * @throws Exception
     */
    public static List readExcel(String filename) throws Exception {

        // 创建工作簿的对象
        Workbook workBook = readWorkBook(filename);

        // 定义要返回的数据结构
        List list = new ArrayList();

        // 根据索引获取Sheet对象
        Sheet sheet = workBook.getSheetAt(0);

        // 定义表头
        List rowList;

        // 获取所有行内容， 从第0 行开始 base 0
        for (int rowIndex = 0; rowIndex