Java:apache.poi读写Excel文件

依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.16</version>
</dependency>

备注:poi-ooxml 已经包含了poipoi-ooxml-schemascurvesapi,不需要单独引入

文件格式对应处理类

.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<List<String>> readExcel(String filename) throws Exception {

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

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

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

        // 定义表头
        List<String> rowList;

        // 获取所有行内容, 从第0 行开始 base 0
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            rowList = new ArrayList<>();

            Row row = sheet.getRow(rowIndex);

            // 获取所有列值 base 1
            for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
                Cell cell = row.getCell(columnIndex);

                //首先强制设置成string类型
                cell.setCellType(CellType.STRING);

                String value = cell.getStringCellValue();
                // System.out.println(value);
                rowList.add(value);
            }
            // System.out.println(map);
            list.add(rowList);

        }
        // 关闭工作簿
        workBook.close();

        return list;
    }

    /**
     * 写入到excel
     *
     * @param filename
     * @param list
     * @throws Exception
     */
    public static void writeExcel(String filename, List<List<Object>> list) throws Exception {

        Workbook workbook = createWorkBook(filename);

        Sheet sheet = workbook.createSheet();

        Row row;
        Cell cell;
        List<Object> rowList;

        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i);

            rowList = list.get(i);

            for (int j = 0; j < rowList.size(); j++) {
                cell = row.createCell(j);
                String value = String.valueOf(rowList.get(j));
                cell.setCellValue(value);
            }
        }

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

        workbook.close();
    }
}

测试

package com.demo.excel;


import org.junit.Test;

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

public class ExcelUtilTest {

    @Test
    public void testReadExcel() throws Exception {
        String filename = "demo.xlsx";
        List<List<String>> list = ExcelUtil.readExcel(filename);
        System.out.println(list);
        // [[姓名, 年龄], [刘备, 42]]
    }

    @Test
    public void testWriteExcel() throws Exception {
        String filename = "demo.xlsx";

        List<List<Object>> list = new ArrayList<>();
        List<Object> list1 = new ArrayList<>();
        list1.add("姓名");
        list1.add("年龄");

        List<Object> list2 = new ArrayList<>();
        list2.add("刘备");
        list2.add(42);

        list.add(list1);
        list.add(list2);

        System.out.println(list);
        // [[姓名, 年龄], [刘备, 42]]

        ExcelUtil.writeExcel(filename, list);
    }
}
上一篇:Mac下sublime text 的“package control”安装


下一篇:python模块之paramiko