Apache POI读写Excel

• 2020 年 11 月 20 日
• 笔记
• JAVA

Apache POI是Apache软件基金会的开放源码函式库，POIAPI给Java程序对Microsoft Office格式档案读和写的功能。

官方文档

[//poi.apache.org/]

相关类

XLSX相关类及方法

常用类

常用方法

构造方法

XSSFWorkbook常用方法

XSSFSheet常用方法

XSSFRow常用方法

XSSFCell常用方法

XSSFFont常用方法

XSSFCellStyle常用方法

示例

导包

将下载下来的包中所有的docs文件夹同目录下的所有jar包、lib文件夹、ooxml-lib文件夹导入到工程中

读入

样例表

import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.io.InputStream;

public class Main {
    public static void main(String[] args) throws IOException {
        InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("test.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(is);

        //获取索引为0的sheet表格
        XSSFSheet sheet = workbook.getSheetAt(0);
        //获取表格中有内容的首行行号
        int first = sheet.getFirstRowNum();
        //获取表格中有内容的尾行行号
        int last = sheet.getLastRowNum();

        //从首行开始获取，首行为表头
        XSSFRow row = sheet.getRow(0);
        XSSFCell cell = row.getCell(0);
        System.out.print(cell.toString() + "\t");
        cell = row.getCell(1);
        System.out.print(cell.toString() + "\t");
        cell = row.getCell(2);
        System.out.print(cell.toString() + "\t");
        cell = row.getCell(3);
        System.out.print(cell.toString() + "\t");
        cell = row.getCell(4);
        System.out.print(cell.toString() + "\t");
        cell = row.getCell(5);
        System.out.println(cell.toString() + "\t");
        for (int i = first + 1|i <= last|++i) {
            row = sheet.getRow(i);
            cell = row.getCell(0);
            int uid = Integer.valueOf(cell.toString().replace(".0", ""));
            cell = row.getCell(1);
            String uname = cell.toString();
            cell = row.getCell(2);
            String upass = cell.toString();
            cell = row.getCell(3);
            String truename = cell.toString();
            cell = row.getCell(4);
            String sex = cell.toString();
            cell = row.getCell(5);
            int age = Integer.valueOf(cell.toString().replace(".0", ""));
            System.out.println(new User(uid, uname, upass, truename, sex, age));
        }
    }
}

用户编号	用户名	密码	姓名	性别	年龄	
1	zs	123.0	张三	男	18
2	ls	ls	李四	男	18
3	rsb	rsb	rsb	男	16
5	rbj	rbj	rbj	男	15
7	ssq	ssq	ssq	女	14
8	iow	iow	iow	男	7
9	auf	auf	auf	男	9
10	atv	atv	atv	男	12
11	soc	soc	soc	女	5
12	wqo	wqo	wqo	男	12

写出

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Random;

public class Write {
    public static void main(String[] args) throws IOException {
        //新建一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //在工作薄中新建一个名为写入的sheet表
        XSSFSheet sheet = workbook.createSheet("写入");
        //创建一行作为表头
        XSSFRow row = sheet.createRow(0);
        //向表头写入内容
        XSSFCell cell = row.createCell(0);
        cell.setCellValue("用户编号");
        cell = row.createCell(1);
        cell.setCellValue("用户名");
        cell = row.createCell(2);
        cell.setCellValue("密码");
        cell = row.createCell(3);
        cell.setCellValue("姓名");
        cell = row.createCell(4);
        cell.setCellValue("性别");
        cell = row.createCell(5);
        cell.setCellValue("年龄");

        //写入其他内容同理

        //测试合并单元格
        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue(1);
        cell = row.createCell(1);
        cell.setCellValue(2);
        cell = row.createCell(2);
        cell.setCellValue(3);

        sheet.addMergedRegion(new CellRangeAddress(1,1,0,3));
        cell = row.getCell(0);
        //让工作薄创建一个单元格格式
        XSSFCellStyle style = workbook.createCellStyle();
        //设置水平对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐方式
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        cell.setCellStyle(style);

        workbook.write(new FileOutputStream("write.xlsx"));
    }
}