Java-POI使用

一、前言

• Apache POI-Microsoft文档的Java API
• JDK：1.8
• POI：4.11

参考：

环境说明：https://poi.apache.org/devel/index.html

Component Map - 对象概述、依赖说明：https://poi.apache.org/components/index.html

上手指南：https://poi.apache.org/components/spreadsheet/quick-guide.html

二、记录

1.依赖包选择

应用	组件	依赖包
OLE2 Filesystem	POIFS	poi
OLE2 Property Sets	HPSF	poi
Excel XLS	HSSF	poi
Escher common drawings	DDF	poi
PowerPoint PPT	HSLF	poi-scratchpad
Word DOC	HWPF	poi-scratchpad
Visio VSD	HDGF	poi-scratchpad
Publisher PUB	HPBF	poi-scratchpad
Outlook MSG	HSMF	poi-scratchpad
WMF drawings	HWMF	poi-scratchpad
PowerPoint PPT and PPTX	Common SL	poi-scratchpad and poi-ooxml
Excel XLS and XLSX	Common SS	poi-ooxml
Excel XLSX	XSSF	poi-ooxml
PowerPoint PPTX	XSLF	poi-ooxml
Word DOCX	XWPF	poi-ooxml
Visio VSDX	XDGF	poi-ooxml
OOXML	OpenXML4J	poi-ooxml plus either poi-ooxml-schemas or ooxml-schemas and ooxml-security

• 全部依赖（前三个是重点）

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/ooxml-security -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-security</artifactId>
    <version>1.1</version>
</dependency>

2.Excel读取

• 同时支持 .xls 和 .xlsx 格式

package com.demo.poi;

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

import java.io.File;
import java.io.IOException;

public class POIExcelDemo {

    public static void main(String[] args) {
        try {
            String path = "E://index.xlsx"; // Excel路径
            File file = new File(path);
            // 读取文档
            Workbook workbook = WorkbookFactory.create(file);
            // 读取第一页数据
            Sheet sheet = workbook.getSheetAt(0);
            // 循环行数据
            for(int i=0; i<=sheet.getLastRowNum(); i++){
                Row row = sheet.getRow(i);
                // 循环列数据
                for(int j=0; j<row.getLastCellNum(); j++){
                    Cell cell = row.getCell(j);
                    Object content = null;
                    // 判断数据类型
                    if(cell != null){
                        if(cell.getCellType().equals(CellType.NUMERIC)){ // 数字
                            if(DateUtil.isCellDateFormatted(cell)){ // 日期时间
                                content = cell.getDateCellValue();
                            }else{
                                content = cell.getNumericCellValue();
                            }
                        }else if(cell.getCellType().equals(CellType.STRING)){ // 字符串
                            content = cell.getStringCellValue();
                        }else if(cell.getCellType().equals(CellType.BLANK)){ // 空值
                            content = "";
                        }else if(cell.getCellType().equals(CellType.BOOLEAN)){ // 布尔
                            content = cell.getBooleanCellValue();
                        }else{ // CellType.FORMULA 或者 CellType.ERROR
                            content = cell.getStringCellValue();
                        }
                    }
                    System.out.println("读取第 "+j+" 列，第 "+i+" 行="+content);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

3.Excel导出

package com.demo.poi;

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

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.*;

public class POIExcelExpDemo {

    public static void main(String[] args) {
    	POIExcelExpDemo poiExcelExpDemo = new POIExcelExpDemo();
        List<Map<String, Object>> mapList = new LinkedList<>();
        for(int i=0; i<10; i++){
            Map<String, Object> map = new LinkedHashMap<>();
            map.put("id", i);
            map.put("name", "test-"+1);
            map.put("address", i+" 号大街");
            mapList.add(map);
        }
        String filePath = "F:/test.xlsx";
        poiExcelExpDemo.exportExcel(mapList, filePath);
    }

    /**
     * 导出到Excel文件
     * @param mapList 数据集合
     * @param filePath 文件保存路径
     * */
    public void exportExcel(List<Map<String, Object>> mapList, String filePath){
        if(mapList == null || mapList.size() == 0){
            System.out.println("数据对象为空或无可操作数据.");
            return; // 跳出方法
        }
        // 创建工作簿
        Workbook workbook = null;
        if(filePath.endsWith("xlsx")){
            workbook = new XSSFWorkbook();
        }else if(filePath.endsWith("xls")){
            workbook = new HSSFWorkbook();
        }else {
            System.out.println("当前文件格式不支持.【仅支持xls和xlsx】");
            return; // 跳出方法
        }
        // 创建表
        Sheet sheet = workbook.createSheet("sheet 1");
        // 创建行
        for(int i=0; i<=mapList.size(); i++){
            Row row = sheet.createRow(i); // 创建行
            Map<String, Object> tempMap = null; // 获取行数据

            // 设置标题列
            if(i==0){
                tempMap = mapList.get(0);
                Font font = workbook.createFont(); // 创建字体样式
                font.setBold(true);
                font.setFontHeightInPoints((short) 12);
                CellStyle cellStyle = workbook.createCellStyle(); // 创建表格样式
                cellStyle.setFont(font);
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                Iterator<String> keyIterator = tempMap.keySet().iterator();
                int t = 0; // 索引
                while (keyIterator.hasNext()){
                    Cell cell = row.createCell(t); // 创建列
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(keyIterator.next()); // 设置值
                    sheet.autoSizeColumn(t); // 自动调整列的宽度
                    t++;
                }
            }else{// 设置值列
                tempMap = mapList.get(i-1); // 0 行是标题，第1行的数据，需要减1获取
                Iterator<Object> valIterator = tempMap.values().iterator();
                int v = 0; // 索引
                while (valIterator.hasNext()){
                    Cell cell = row.createCell(v); // 创建列
                    Object nextVal = valIterator.next();
                    cell.setCellValue(nextVal == null ? "" : nextVal.toString()); // 设置值
                    v++;
                }
            }
        }
        // 输出文件
        try(OutputStream outputStream = new FileOutputStream(filePath)){
            workbook.write(outputStream);
            System.out.println("导出完成："+filePath);
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}

4.Word读取

package com.demo.poi;

import org.apache.poi.hwpf.extractor.WordExtractor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xwpf.extractor.XWPFWordExtractor;
import org.apache.poi.xwpf.usermodel.XWPFDocument;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

public class POIWordDemo {

    public static void main(String[] args) {
        try {
            POIWordDemo poiWordDemo = new POIWordDemo();
            String path = "E://index.docx"; // Wordl路径
            File file = new File(path);
            if(path.indexOf(".docx") != -1){
                System.out.println(poiWordDemo.readDocx(file));
            }else if(path.indexOf(".doc") != -1){
                System.out.println(poiWordDemo.readDoc(file));
            }else {
                System.out.println("不支持格式");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取 ‘.doc’ 格式
     * @param file 文件
     * */
    public String readDoc(File file) throws IOException {
        // 读取文档
        WordExtractor wordExtractor = new WordExtractor(new FileInputStream(file));
        return wordExtractor.getText();
    }

    /**
     * 读取 ‘.docx’ 格式
     * @param file 文件
     * */
    public String readDocx(File file) throws IOException {
        // 读取文档
        XWPFDocument xwpfDocument = new XWPFDocument(new FileInputStream(file));
        XWPFWordExtractor xwpfWordExtractor = new XWPFWordExtractor(xwpfDocument);
        return xwpfWordExtractor.getText();
    }
}

5.PPT读取

package com.demo.poi;

import org.apache.poi.hslf.usermodel.HSLFTable;
import org.apache.poi.hslf.usermodel.HSLFTableCell;
import org.apache.poi.hslf.usermodel.HSLFTextShape;
import org.apache.poi.sl.usermodel.PictureData;
import org.apache.poi.sl.usermodel.Slide;
import org.apache.poi.sl.usermodel.SlideShow;
import org.apache.poi.sl.usermodel.SlideShowFactory;
import org.apache.poi.sl.usermodel.Shape;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xslf.usermodel.XSLFTable;
import org.apache.poi.xslf.usermodel.XSLFTableCell;
import org.apache.poi.xslf.usermodel.XSLFTextShape;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.UUID;

public class POIPPTDemo {

    public static void main(String[] args) {
        try {
            String path = "E://index.pptx"; // PPT路径
            SlideShow slideShow = SlideShowFactory.create(new File(path));
            // == 读取文字数据
            List<Slide> slides = slideShow.getSlides();
            // 循环每一页
            for(Slide slide : slides){
                System.out.println("==================【"+ slide.getSlideName() +"】");
                List<Shape> shapes = slide.getShapes(); // 页内容
                for(Shape shape : shapes){
                    if(shape instanceof HSLFTextShape){ // 文本内容[.ppt]
                        System.out.println(((HSLFTextShape) shape).getText());
                    }
                    if(shape instanceof XSLFTextShape){ // 文本内容[.pptx]
                        System.out.println(((XSLFTextShape) shape).getText());
                    }
                    if(shape instanceof HSLFTable){ // 表格内容[.ppt]
                        int rowNum = ((HSLFTable) shape).getNumberOfRows();
                        int colNum = ((HSLFTable) shape).getNumberOfColumns();
                        for(int i=0; i<rowNum; i++){ // 行
                            for(int j=0; j<colNum; j++){ // 列
                                HSLFTableCell cell = ((HSLFTable) shape).getCell(i, j);
                                if(cell != null){
                                    System.out.println(cell.getText());
                                }
                            }
                        }
                    }
                    if(shape instanceof XSLFTable){ // 表格内容[.pptx]
                        int rowNum = ((XSLFTable) shape).getNumberOfRows();
                        int colNum = ((XSLFTable) shape).getNumberOfColumns();
                        for(int i=0; i<rowNum; i++){ // 行
                            for(int j=0; j<colNum; j++){ // 列
                                XSLFTableCell cell = ((XSLFTable) shape).getCell(i, j);
                                if(cell != null){
                                    System.out.println(cell.getText());
                                }
                            }
                        }
                    }
                }
            }
            // == 读取图片数据
            List<PictureData> pictureDatas = slideShow.getPictureData();
            System.out.println("pic size="+pictureDatas.size());
            for(PictureData pictureData : pictureDatas){
                byte [] bytes = pictureData.getData();
                String picPath = "E://index//"+ UUID.randomUUID()+".jpg";
                OutputStream outputStream = new FileOutputStream(new File(picPath));
                outputStream.write(bytes);
                outputStream.close();
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

三、其它

1.CellType类不存在

• 原因：低版本不支持
• 解决：可以使用 Cell 获取静态变量，如：Cell.CELL_TYPE_STRING

2.Excel获取行数和列数

方法	说明
sheet.getPhysicalNumberOfRows()	获取物理行数，排除空行
sheet.getLastRowNum()	获取最后一行的行数，从 0 开始
row.getPhysicalNumberOfCells()	获取物理列数，排除空列
row.getLastCellNum()	获取最后一列的列数，从 1 开始

3.读取的日期数字转日期

• 一般使用 DateUtil.isCellDateFormatted(cell) 进行判断，也可直接将数字转换为日期

// 日期格式
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
// poi 工具
Date date = DateUtil.getJavaDate(43441.0);
String dateStr = simpleDateFormat.format( date ) ;
System.out.println(dateStr);