POI组件：POI操作Excel

时间：2021-02-04 20:26:42


1、Excel简介

一个excel文件就是一个工作簿workbook，一个工作簿中可以创建多张工作表sheet，而一个工作表中包含多个单元格Cell，这些单元格都是由列（Column）行(Row)组成，列用大写英文字母表示，从A开始到Z共26列，然后再从AA到AZ又26列，再从BA到BZ再26列以此类推。行则使用数字表示，例如；A3 表示第三行第一列，E5表示第五行第五列。


2、POI工具包

POI全称 Poor Obfuscation Implementation,直译为“可怜的模糊实现”，利用POI接口可以通过JAVA操作Microsoft office 套件工具的读写功能。官网：http://poi.apache.org，POI支持office的所有版本（.xls和.xlsx）。


我下载的是3.14版本

POI组件：POI操作Excel

解压下载的poi-bin-3.14.zip文件

POI组件：POI操作Excel

将需要的jar包导入到项目当中

版本 jar包
操作2003 及以前版本的excel poi-3.14-20160307.jar
对2007及以后版本进行操作

poi-ooxml-3.14-20160307.jar

poi-ooxml-schemas-3.14-20160307.jar

以及ooxml-lib目录下的jar包



3、POI编程


3.1、POI对象与Excel之间的对象关系


在POI包中有如下几个主要对象和excel的几个对象对应：

HSSFWorkbook Excel 工作簿workbook
HSSFSheet Excel 工作表 sheet
HSSFRow Excel 行
HSSFCell Excel 单元格


HSSFHorrible Spreadsheet Format (Java implementation of Excel)


HSSF 是Horrible SpreadSheet Format的缩写，也即“讨厌的电子表格格式”。 也许HSSF的名字有点滑稽，就本质而言它是一个非常严肃、正规的API。通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。


HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。前者很好理解，后者比较抽象，但操作效率要高得多。


工作表（属于工作簿）

行（属于工作表）

单元格（属于行；由行和列确定）


-------------操作excel

1、创建/读取工作簿

2、创建/读取工作表

3、创建/读取行

4、创建/读取单元格


3.2、对03版本Excel的写入操作

@Testpublic void testWrite03Excel() throws Exception{
//1、创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//2、创建工作表
HSSFSheet sheet = workbook.createSheet("Hello World");//指定工作表名
//3、创建行：创建第3行（第一行从0开始）
HSSFRow row = sheet.createRow(2);
//4、创建单元格：创建第3行第3列（第一列从0开始）
HSSFCell cell = row.createCell(2);
cell.setCellValue("Hello World");

//输出到硬盘
FileOutputStream outputStream = new FileOutputStream("D:/rk/测试.xls");
//把Excel输出到具体的地址
workbook.write(outputStream);
workbook.close();
outputStream.close();
}


3.3、对07版本Excel的写入操作

@Testpublic void testWrite07Excel() throws Exception{//1、创建工作簿XSSFWorkbook workbook = new XSSFWorkbook();//2、创建工作表XSSFSheet sheet = workbook.createSheet("Hello");//指定工作表名//3、创建行：创建第3行XSSFRow row = sheet.createRow(2);//4、创建单元格：创建第3行第3列XSSFCell cell = row.createCell(2);cell.setCellValue("Hello World");//输出到硬盘FileOutputStream outputStream = new FileOutputStream("D:/rk/测试.xlsx");//把Excel输出到具体的地址workbook.write(outputStream);workbook.close();outputStream.close();}


3.4、对03版本Excel的读取操作

@Testpublic void testRead03Excel() throws Exception{FileInputStream inputStream = new FileInputStream("D:/rk/测试.xls");//1、读取工作簿：将输入流转化为Workbook对象HSSFWorkbook workbook = new HSSFWorkbook(inputStream);//2、读取第一个工作表HSSFSheet sheet = workbook.getSheetAt(0);//3、读取行：读取第3行HSSFRow row = sheet.getRow(2);//4、读取单元格：读取第3行第3列HSSFCell cell = row.getCell(2);System.out.println("第3行第3列单元格的内容为：" + cell.getStringCellValue());workbook.close();inputStream.close();}


3.5、对07版本Excel的读取操作

@Testpublic void testRead07Excel() throws Exception{FileInputStream inputStream = new FileInputStream("D:/rk/测试.xlsx");//1、读取工作簿XSSFWorkbook workbook = new XSSFWorkbook(inputStream);//2、读取第一个工作表XSSFSheet sheet = workbook.getSheetAt(0);//3、读取行：读取第3行XSSFRow row = sheet.getRow(2);//4、读取单元格：读取第3行第3列XSSFCell cell = row.getCell(2);System.out.println("第3行第3列单元格的内容为：" + cell.getStringCellValue());workbook.close();inputStream.close();}


3.6、以统一方式读取03版本和07版本的Excel数据

在\poi-3.14\docs\apidocs目录下，打开index.html查看POI api文档

POI组件：POI操作Excel

POI组件：POI操作Excel

POI组件：POI操作Excel



从api文档中我们了解到：

HSSFWorkbook和 XSSFWorkbook 都实现了Workbook接口；

HSSFSheet和 XSSFSheet 实现了Sheet接口；

HSSFRow和 XSSFRow 实现了Row接口；

HSSFCell和 XSSFCell 实现了Cell接口；

因为这两类处理对象共同实现了对应的同一接口，这将大大方便和简化了处理不同格式的excel文件的编码工作：在处理03和07版本的excel文件时，利用统一的接口就可以做到处理两个版本的excel数据。

@Testpublic void testRead03and07Excel() throws Exception{//String fileName = "D:/rk/测试.xls";String fileName = "D:/rk/测试.xlsx";if(fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){boolean is03Excel = fileName.matches("^.+\\.(?i)(xls)$");FileInputStream inputStream = new FileInputStream(fileName);//1、读取工作簿Workbook workbook = is03Excel ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);//2、读取第一个工作表Sheet sheet = workbook.getSheetAt(0);//3、读取行：读取第3行Row row = sheet.getRow(2);//4、读取单元格：读取第3行第3列Cell cell = row.getCell(2);System.out.println("第3行第3列单元格的内容为："+cell.getStringCellValue());workbook.close();inputStream.close();}}


正则表达式中的(?i)


Case-insensitive matching can also be enabled via the embedded flag expression (?i). 


3.7、格式化Excel


在POI中可以利用格式化对象来格式化excel文档；也即设置excel内容的样式。POI中主要的格式化对象常用的有合并单元格、设置单元格字体、边框，背景颜色等。


（1）合并单元格

（2）单元格样式

    单元格内容位置：水平居中、垂直居中

    字体、加粗、颜色、大小

    单元格背景色

    设置列宽、行高


合并单元格对象（CellRangeAddress）属于工作簿；运用于工作表


CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) 起始行号，结束行号，起始列号，结束列号



样式是属于工作簿的；运用于单元格


字体是属于工作簿的；加载于样式；通用样式运用于单元格

 


@Testpublic void testExcelStyle() throws Exception{//1、创建工作簿HSSFWorkbook workbook = new HSSFWorkbook();//1.1、创建合并单元格对象：合并第3行的第3列至第5列CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 2, 2, 4);//起始行号，结束行号，起始列号，结束列号//1.2、创建单元格样式HSSFCellStyle style = workbook.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中//1.3、创建字体HSSFFont font = workbook.createFont();font.setFontName(HSSFFont.FONT_ARIAL);//字体为Arialfont.setColor(HSSFColor.BLUE.index);//字体颜色为Bluefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体font.setFontHeightInPoints((short)16);//设置字体大小//加载字体style.setFont(font);//单元格背景//设置单元格填充模式，模式为全部前景色style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置填充背景色style.setFillBackgroundColor(HSSFColor.YELLOW.index);//设置填充前景色style.setFillForegroundColor(HSSFColor.RED.index);//2、创建工作表HSSFSheet sheet = workbook.createSheet("hello");//指定工作表名//2.1、加载合并单元格对象sheet.addMergedRegion(cellRangeAddress);sheet.setColumnWidth(0, 20*256);//列宽单位是字符的1/256//3、创建行：创建第3行HSSFRow row = sheet.createRow(2);row.setHeightInPoints(100); //设置行的高度//4、创建单元格：创建第3行第3列HSSFCell cell = row.createCell(2);//加载样式cell.setCellStyle(style);cell.setCellValue("Hello World");//输出至硬盘FileOutputStream outputStream = new FileOutputStream("D:/rk/测试样式.xls");//把Excel输出到具体的地址workbook.write(outputStream);workbook.close();outputStream.close();}



完整的代码TestPOI.java

package com.rk.test;import java.io.FileInputStream;import java.io.FileOutputStream;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;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 org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.junit.Test;public class TestPOI {@Testpublic void testWrite03Excel() throws Exception{//1、创建工作簿HSSFWorkbook workbook = new HSSFWorkbook();//2、创建工作表HSSFSheet sheet = workbook.createSheet("Hello World");//指定工作表名//3、创建行：创建第3行（第一行从0开始）HSSFRow row = sheet.createRow(2);//4、创建单元格：创建第3行第3列（第一列从0开始）HSSFCell cell = row.createCell(2);cell.setCellValue("Hello World");//输出到硬盘FileOutputStream outputStream = new FileOutputStream("D:/rk/测试.xls");//把Excel输出到具体的地址workbook.write(outputStream);workbook.close();outputStream.close();}@Testpublic void testWrite07Excel() throws Exception{//1、创建工作簿XSSFWorkbook workbook = new XSSFWorkbook();//2、创建工作表XSSFSheet sheet = workbook.createSheet("Hello");//指定工作表名//3、创建行：创建第3行XSSFRow row = sheet.createRow(2);//4、创建单元格：创建第3行第3列XSSFCell cell = row.createCell(2);cell.setCellValue("Hello World");//输出到硬盘FileOutputStream outputStream = new FileOutputStream("D:/rk/测试.xlsx");//把Excel输出到具体的地址workbook.write(outputStream);workbook.close();outputStream.close();}@Testpublic void testRead03Excel() throws Exception{FileInputStream inputStream = new FileInputStream("D:/rk/测试.xls");//1、读取工作簿：将输入流转化为Workbook对象HSSFWorkbook workbook = new HSSFWorkbook(inputStream);//2、读取第一个工作表HSSFSheet sheet = workbook.getSheetAt(0);//3、读取行：读取第3行HSSFRow row = sheet.getRow(2);//4、读取单元格：读取第3行第3列HSSFCell cell = row.getCell(2);System.out.println("第3行第3列单元格的内容为：" + cell.getStringCellValue());workbook.close();inputStream.close();}@Testpublic void testRead07Excel() throws Exception{FileInputStream inputStream = new FileInputStream("D:/rk/测试.xlsx");//1、读取工作簿XSSFWorkbook workbook = new XSSFWorkbook(inputStream);//2、读取第一个工作表XSSFSheet sheet = workbook.getSheetAt(0);//3、读取行：读取第3行XSSFRow row = sheet.getRow(2);//4、读取单元格：读取第3行第3列XSSFCell cell = row.getCell(2);System.out.println("第3行第3列单元格的内容为：" + cell.getStringCellValue());workbook.close();inputStream.close();}@Testpublic void testRead03and07Excel() throws Exception{//String fileName = "D:/rk/测试.xls";String fileName = "D:/rk/测试.xlsx";if(fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){boolean is03Excel = fileName.matches("^.+\\.(?i)(xls)$");FileInputStream inputStream = new FileInputStream(fileName);//1、读取工作簿Workbook workbook = is03Excel ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);//2、读取第一个工作表Sheet sheet = workbook.getSheetAt(0);//3、读取行：读取第3行Row row = sheet.getRow(2);//4、读取单元格：读取第3行第3列Cell cell = row.getCell(2);System.out.println("第3行第3列单元格的内容为："+cell.getStringCellValue());workbook.close();inputStream.close();}}@Testpublic void testExcelStyle() throws Exception{//1、创建工作簿HSSFWorkbook workbook = new HSSFWorkbook();//1.1、创建合并单元格对象：合并第3行的第3列至第5列CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 2, 2, 4);//起始行号，结束行号，起始列号，结束列号//1.2、创建单元格样式HSSFCellStyle style = workbook.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中//1.3、创建字体HSSFFont font = workbook.createFont();font.setFontName(HSSFFont.FONT_ARIAL);//字体为Arialfont.setColor(HSSFColor.BLUE.index);//字体颜色为Bluefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体font.setFontHeightInPoints((short)16);//设置字体大小//加载字体style.setFont(font);//单元格背景//设置单元格填充模式，模式为全部前景色style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置填充背景色style.setFillBackgroundColor(HSSFColor.YELLOW.index);//设置填充前景色style.setFillForegroundColor(HSSFColor.RED.index);//2、创建工作表HSSFSheet sheet = workbook.createSheet("hello");//指定工作表名//2.1、加载合并单元格对象sheet.addMergedRegion(cellRangeAddress);sheet.setColumnWidth(0, 20*256);//列宽单位是字符的1/256//3、创建行：创建第3行HSSFRow row = sheet.createRow(2);row.setHeightInPoints(100); //设置行的高度//4、创建单元格：创建第3行第3列HSSFCell cell = row.createCell(2);//加载样式cell.setCellStyle(style);cell.setCellValue("Hello World");//输出至硬盘FileOutputStream outputStream = new FileOutputStream("D:/rk/测试样式.xls");//把Excel输出到具体的地址workbook.write(outputStream);workbook.close();outputStream.close();}}


标签：

相关文章