java poi 的使用

参考文档:https://www.cnblogs.com/fqh2020/p/14675105.html

1. 什么是poi

Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。

2. poi常用的包

HSSF － 提供读写Microsoft Excel XLS格式档案的功能。

XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。

HWPF － 提供读写Microsoft Word DOC格式档案的功能。

HSLF － 提供读写Microsoft PowerPoint格式档案的功能。

HDGF － 提供读Microsoft Visio格式档案的功能。

HPBF － 提供读Microsoft Publisher格式档案的功能。

HSMF － 提供读Microsoft Outlook格式档案的功能。

3. 引用依赖

<!--xls-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

        <!--xlsx-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

        <!--操作word文档-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.8</version>
        </dependency>

4.导出excel

package main;

import annotation.ColumnDesc;
import entity.Person;
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.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Jenson
 */
public class ExportExcelTest {

    public static void main(String[] args) throws IOException {
        List<Person> personList = new ArrayList<>();
        Person p1 = Person.builder().name("张三").age(12).sex("男").build();
        personList.add(p1);
        Person p2 = Person.builder().name("李四").age(15).sex("女").build();
        personList.add(p2);
        //创建workbook
        Workbook workbook = new HSSFWorkbook();
        //根据workbook创建sheet
        Sheet sheet = workbook.createSheet("会员列表");

        List<Field> cellFields = new ArrayList<>();
        Field[] fields = Person.class.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field fd = fields[i];
            fd.setAccessible(Boolean.TRUE);
            if (fd.isAnnotationPresent(ColumnDesc.class)) {
                cellFields.add(fd);
            }
        }

        if(cellFields.size()>0){
            Row row1 = sheet.createRow(0);
            for (int i = 0; i < cellFields.size(); i++) {
                Cell cell1 = row1.createCell(i);
                ColumnDesc cd = cellFields.get(i).getAnnotation(ColumnDesc.class);
                cell1.setCellValue(cd.value());
            }
        }

        for (int i = 0; i < personList.size(); i++) {
            //根据sheet创建row
            Row row = sheet.createRow(i+1);
            Person person = personList.get(i);
            for (int j = 0; j < cellFields.size(); j++) {
                //根据row创建cell
                Cell cell1 = row.createCell(j);
                //向cell里面设置值
                String value = null;
                try {
                    value = String.valueOf(cellFields.get(j).get(person));
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                cell1.setCellValue(value);
            }

        }

        //根据workbook创建sheet
        Sheet sheet2 = workbook.createSheet("合并单元格测试");
        Row sheet2Row1 = sheet2.createRow(0);
        Cell sheet2Row1Cell1 = sheet2Row1.createCell(0);
        sheet2Row1Cell1.setCellValue("班级");
        // 合并单元格
        CellRangeAddress region1 = new CellRangeAddress(0, 0, 0,1);
        sheet2.addMergedRegion(region1);


        Row sheet2Row2 = sheet2.createRow(1);
        Cell sheet2Row2Cell1 = sheet2Row2.createCell(0);
        sheet2Row2Cell1.setCellValue("三年二班");
        // 合并单元格
        CellRangeAddress region2 = new CellRangeAddress(1, 2, 0,1);
        sheet2.addMergedRegion(region2);



        //通过输出流写到文件里去
        FileOutputStream fos = new FileOutputStream("./01.xls");
        workbook.write(fos);
        fos.close();
    }
}

package entity;

import annotation.ColumnDesc;
import lombok.Builder;
import lombok.Data;

/**
 * @author Jenson
 */
@Data
@Builder
public class Person {

    @ColumnDesc("姓名")
    private String name;

    @ColumnDesc("年龄")
    private Integer age;

    @ColumnDesc("性别")
    private String sex;

}

5.读取Excel

package main;

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

/**
 * @author Jenson
 */
public class ReadExcelTest {

    public static void main(String[] args) throws IOException {
        FileInputStream fis=new FileInputStream("./01.xls");
        Workbook workbook=new HSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
    }

}

6.读取word文档

package main;

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

/**
 * @author Jenson
 */
public class ReadExcelTest {

    public static void main(String[] args) throws IOException {
        FileInputStream fis=new FileInputStream("./01.xls");
        Workbook workbook=new HSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
    }

}