博客
关于我
POI 的使用
阅读量：793 次
发布时间：2023-03-03

本文共 16469 字，大约阅读时间需要 54 分钟。

POI 使用指南

Apache POI 是 Apache 软件基金会开发的开放源代码函式库，主要用于通过 Java 程序读取和写入 Microsoft Office 格式文件。对于 .NET 开发人员，NPOI 是专门为其提供支持的工具包。

POI 结构说明

POI 包含多个子包，每个子包负责处理特定类型文件：

  • HSSF：处理 Microsoft Excel XLS 格式文件。
  • XSSF：处理 Microsoft Excel XLSX 格式文件。
  • HWPF：处理 Microsoft Word DOC 格式文件。
  • HSLF：处理 Microsoft PowerPoint 格式文件。
  • HDGF：处理 Microsoft Visio 格式文件。
  • HPBF：处理 Microsoft Publisher 格式文件。
  • HSMF：处理 Microsoft Outlook MSG 格式文件。

Excel 常用操作方法

1. 获取 Excel 常用对象

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/test.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(i);
HSSFCell cell = row.getCell((short) j);

2. 建立 Excel 常用对象

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell = sheet.createCell((short) 0);
HSSFCellStyle style = wb.createCellStyle();
cell.setCellStyle(style);
cell.setCellValue(1);

3. sheet 相关操作

wb.setSheetName(0, "12月合同到期");
int sheetCount = wb.getNumberOfSheets();
HSSFSheet sheet = wb.createSheet("Output");
sheet.setSelected(true);

4. 有效行和单元格个数

int rowCount = sheet.getLastRowNum();
row.getLastCellNum();

5. 单元格值类型读写

public String getCellStringValue(HSSFCell cell) {
    String cellValue = "";
    switch (cell.getCellType()) {
        case CELL_TYPE_STRING:
            cellValue = cell.getStringCellValue();
            break;
        case CELL_TYPE_NUMERIC:
            cellValue = String.valueOf(cell.getNumericCellValue());
            break;
        case CELL_TYPE_FORMULA:
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cellValue = String.valueOf(cell.getNumericCellValue());
            break;
        case CELL_TYPE_BLANK:
            cellValue = " ";
            break;
        case CELL_TYPE_BOOLEAN:
            break;
        case CELL_TYPE_ERROR:
            break;
        default:
            break;
    }
    return cellValue;
}

6. 设置列宽和行高

sheet.setColumnWidth((short) column, (short) width);
row.setHeight((short) height);

7. 合并区域和单元格

Region region = new Region((short) rowFrom, (short) columnFrom, (short) rowTo, (short) columnTo);
sheet.addMergedRegion(region);
int mergedCellCount = sheet.getNumMergedRegions();

8. 设置单元格边框格式

HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);
style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);

9. 设置字体和内容位置

HSSFFont font = wb.createFont();
font.setFontName("华文行楷");
font.setFontHeightInPoints((short) 11);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setColor(HSSFColor.RED.index);
font.setUnderline(FontFormatting.U_SINGLE);
font.setTypeOffset(FontFormatting.SS_SUPER);
font.setStrikeout(true);
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setRotation(short rotation);
style.setWrapText(true);
HSSFDataFormat df = wb.createDataFormat();
style.setDataFormat(df.getFormat("0.00%"));

10. 调整行数据位置

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("row sheet");
sheet.shiftRows(5, 10, -5);

11. 填充和颜色设置

HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.AQUA.index);
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("X");
style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell.setCellStyle(style);

12. 工作表缩放

sheet.setZoom(1, 2);
sheet.setZoom(75);

13. 使用公式

cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(C2:C3)");

14. 设置超链接

cell.setCellFormula("hyperlink(\"http://www.yiibai.com/testng/\",\"testng\")");

15. 插入图片

public static void test1() {
    FileOutputStream fileOut = null;
    BufferedImage bufferImg = null;
    try {
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        bufferImg = ImageIO.read(new File(picture));
        ImageIO.write(bufferImg, "jpg", byteArrayOut);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 4, 37);
        HSSFClientAnchor anchor2 = new HSSFClientAnchor(500, 0, 0, 0, (short) 5, 1, (short) 9, 38);
        HSSFClientAnchor anchor3 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 10, 10);
        HSSFClientAnchor anchor4 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 8, 8);
        HSSFClientAnchor anchor5 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 12, 12);
        HSSFClientAnchor anchor6 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 16, 16);
        HSSFClientAnchor anchor7 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 20, 20);
        HSSFClientAnchor anchor8 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 24, 24);
        HSSFClientAnchor anchor9 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 28, 28);
        HSSFClientAnchor anchor10 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 32, 32);
        HSSFClientAnchor anchor11 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 36, 36);
        HSSFClientAnchor anchor12 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 40, 40);
        HSSFClientAnchor anchor13 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 44, 44);
        HSSFClientAnchor anchor14 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 48, 48);
        HSSFClientAnchor anchor15 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 52, 52);
        HSSFClientAnchor anchor16 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 56, 56);
        HSSFClientAnchor anchor17 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 60, 60);
        HSSFClientAnchor anchor18 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 64, 64);
        HSSFClientAnchor anchor19 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 68, 68);
        HSSFClientAnchor anchor20 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 72, 72);
        HSSFClientAnchor anchor21 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 76, 76);
        HSSFClientAnchor anchor22 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 80, 80);
        HSSFClientAnchor anchor23 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 84, 84);
        HSSFClientAnchor anchor24 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 88, 88);
        HSSFClientAnchor anchor25 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 92, 92);
        HSSFClientAnchor anchor26 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 96, 96);
        HSSFClientAnchor anchor27 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 100, 100);
        HSSFClientAnchor anchor28 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 104, 104);
        HSSFClientAnchor anchor29 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 108, 108);
        HSSFClientAnchor anchor30 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 112, 112);
        HSSFClientAnchor anchor31 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 116, 116);
        HSSFClientAnchor anchor32 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 120, 120);
        HSSFClientAnchor anchor33 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 124, 124);
        HSSFClientAnchor anchor34 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 128, 128);
        HSSFClientAnchor anchor35 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 132, 132);
        HSSFClientAnchor anchor36 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 136, 136);
        HSSFClientAnchor anchor37 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 140, 140);
        HSSFClientAnchor anchor38 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 144, 144);
        HSSFClientAnchor anchor39 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 148, 148);
        HSSFClientAnchor anchor40 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 152, 152);
        HSSFClientAnchor anchor41 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 156, 156);
        HSSFClientAnchor anchor42 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 160, 160);
        HSSFClientAnchor anchor43 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 164, 164);
        HSSFClientAnchor anchor44 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 168, 168);
        HSSFClientAnchor anchor45 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 172, 172);
        HSSFClientAnchor anchor46 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 176, 176);
        HSSFClientAnchor anchor47 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 180, 180);
        HSSFClientAnchor anchor48 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 184, 184);
        HSSFClientAnchor anchor49 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 188, 188);
        HSSFClientAnchor anchor50 = new HSSFClientAnchor(0, 0, 1023, 255, (short) 0, 0, (short) 192, 192);
        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor2, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor3, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor4, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor5, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor6, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor7, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor8, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor9, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor10, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor11, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor12, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor13, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor14, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor15, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor16, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor17, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor18, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor19, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor20, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor21, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor22, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor23, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor24, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor25, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor26, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor27, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor28, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor29, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor30, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor31, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor32, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor33, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor34, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor35, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor36, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor37, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor38, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor39, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor40, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor41, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor42, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor43, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor44, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor45, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor46, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor47, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor48, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor49, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor50, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        fileOut = new FileOutputStream(filepath + "/excel.xls");
        wb.write(fileOut);
        System.out.println("test1");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

16. 从 Excel 文件中提取图片

public static void testRead() {
    InputStream inp = new FileInputStream(filepath + "/excel.xls");
    HSSFWorkbook workbook = new HSSFWorkbook(inp);
    List
   
     pictures = workbook.getAllPictures();
    for (int i = 0; i < pictures.size(); i++) {
        HSSFPictureData picture = pictures.get(i);
        // 提取图片数据
        byte[] pictureBytes = picture.getImageData();
        // 处理图片数据
    }
}

POI CellType 类型

CellType 类型 值 描述
CELL_TYPE_NUMERIC 0 数值型
CELL_TYPE_STRING 1 字符串型
CELL_TYPE_FORMULA 2 公式型
CELL_TYPE_BLANK 3 空值
CELL_TYPE_BOOLEAN 4 布尔型
CELL_TYPE_ERROR 5 错误

读取单元格数据

  • 通过 setCellType 方法：

    • 将单元格类型设置为字符串。
    • 使用 getRichStringCellValue 方法读取单元格内容，然后将其转换为对应的数据类型。

  • 通过 getCellType 方法：

    • 根据单元格类型调用相应的方法读取数据，如 getNumericCellValue()、getBooleanCellValue() 等。

    • POI 版本

    • POI-bin-3.17-20170915.tar.gz

    参考链接

    你可能感兴趣的文章
    Plotly 域变量解释(多图)
    查看>>
    Plotly 绘制表面 3D 未显示
    查看>>
    Plotly-Dash 存在未知问题并创建“加载依赖项时出错“；通过使用 Python-pandas.date_range
    查看>>
    Plotly-Dash:如何过滤具有多个数据框列的仪表板?
    查看>>
    Plotly:如何为 x 轴上的时间序列设置主要刻度线/网格线的值?
    查看>>
    Plotly:如何从 x 轴删除空日期?
    查看>>
    Plotly:如何从单条迹线制作堆积条形图?
    查看>>
    Plotly:如何以 Root 样式绘制直方图，仅显示直方图的轮廓?
    查看>>
    Plotly:如何使用 Plotly Express 组合散点图和线图?
    查看>>
    Plotly:如何使用 plotly.graph_objects 和 plotly.express 定义图形中的颜色?
    查看>>
    Plotly:如何使用 Python 对绘图对象条形图进行颜色编码?
    查看>>
    Plotly:如何使用 updatemenus 更新一个特定的跟踪?
    查看>>
    Plotly:如何使用长格式或宽格式的 pandas 数据框制作线图?
    查看>>
    Plotly:如何向烛台图添加交易量
    查看>>
    Plotly:如何在 plotly express 中找到趋势线的系数?
    查看>>
    Plotly:如何在桑基图中设置节点位置?
    查看>>
    Plotly:如何处理重叠的颜色条和图例?
    查看>>
    Plotly:如何手动设置 plotly express 散点图中点的颜色?
    查看>>
    Plotly:如何结合 make_subplots() 和 ff.create_distplot()?
    查看>>
    Plotly:如何绘制累积的“步骤“；直方图?
    查看>>
    喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
    当前时间: 2026-07-02 14:19:27   当前IP: 10.8.13.103   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2025 baihongyu.com 京ICP备2021015314号-2