一：Java POI 基本简介
Apache POI（Poor Obfuscation Implementation）是用Java编写的免费开源的跨平台的Java API，Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能，其中使用最多的就是使用POI操作Excel文件
它是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）
POI发行版可以对许多文档文件格式的支持。这种支持是在几个JAR文件中提供的。并不是每种格式都需要所有的JAR。下面我介绍一下POI组件、Maven存储库标记和项目的Jar文件之间的关系 【 详细POI API 】
组件 操作格式 依赖 说明
XSSF Excel XLSX poi-ooxml
XSLF PowerPoint PPTX poi-ooxml
XWPF Word DOCX poi-ooxml
XDGF Visio VSDX poi-ooxml
POIFS OLE2 Filesystem poi 需要处理基于OLE2/POIFS的文件
HPSF OLE2 Property Sets poi
HSSF Excel XLS poi 仅支持HSSF（只可以操作XLS）
DDF Escher common drawings poi
HSLF PowerPoint PPT poi-scratchpad
HWPF Word DOC poi-scratchpad
HDGF Visio VSD poi-scratchpad
HPBF Publisher PUB poi-scratchpad
HSMF Outlook MSG poi-scratchpad
HWMF WMF drawings poi-scratchpad
特殊：
Common SL: PPT和PPTX poi-scratchpad 和 poi-ooxml
SL代码在核心POIjar中，但是实现的是poi-scratchpad和poi-ooxml
Common SS: XLS和XLSX poi-ooxml
WorkbookFactory和其它实现都需要poi-ooxml，而不仅仅是核心po
OpenXML4J: OOXML poi-ooxml和poi-ooxml-lite或poi-ooxml-full
maven关于POI操作的整套依赖：
<!--设置maven使用什么版本的jdk解析编译 注意4.1.2版本的jdk编译要是1.8及以上 -->
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.9</maven.compiler.source>
<maven.compiler.target>1.9</maven.compiler.target>
</properties>
<dependencies>
<!--前3个坐标按照上表搭配-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.2</version>
</dependency>
<!--poi其它依赖包，正常使用可以不用添加-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
</dependencies>
POI数据可视化maven依赖
二：POI操作之XSSF（基本）
XSSF是我们最常用的api了，它可以操作我们最常用的Excel XLSX表格，而Excel XLS老板格式的表格慢慢淡出了我们的视线，因为老版格式的表格只能存在65535行，而XLSX就不会出现这个问题，我针对XSSF详细介绍
1：简单创建xlsx之流程了解
操作XLSX文件我们得使用XSSFWorkbook类来构建我们的工作簿，其实查询继承关系，这一类的工作簿都继承自Workbook接口，它实现了HSSFWorkbook，SXSSFWorkbook，XSSFWorkbook等
public static void main(String[] args) throws IOException {
//①：创建工作簿
Workbook workbook = new XSSFWorkbook();
//②：创建工作表 打开Xlsx文件最下边的sheet1、sheet2工作表
Sheet sheet = workbook.createSheet("蚂蚁小哥日常统计");
//③：通过下面2行代码创建一个坐标为0,0 并写上内容 对应Excel 1,1 位置
//创建行 当前创建的为0行 对应Excel第一行
Row row = sheet.createRow(0);
//创建列 当前创建列为0列 对应Excel第一列
Cell cell = row.createCell(0);
//写入内容 今天买菜花300
cell.setCellValue("今天买菜花300");
//或者链式调用写法：sheet.createRow(0).createCell(0).setCellValue("今天买菜花300");
//④：把构建的内存数据写入到磁盘 确保有这个磁盘
OutputStream out = new FileOutputStream("h://test.xlsx");
workbook.write(out);
//⑤：关闭资源
out.close();
workbook.close();
}
创建xlsx基本流程
2：xlsx文件创建日期类型及不同类型的单元格
对于写出的数据中包含日期类型的话，我们就得进行样式转换其中操作样式的 CellStyle 接口方法，它内部定义了各种样式操作，如边框颜色、对齐方式、字体、颜色等等，但是我要介绍的是样式里的 setDataFormat 方法，可是它接收的是short参数，这就使得我要介绍 CreationHelper 接口，它里面的createDataFormat方法可以创建DataFormat实例并返回short参数
public static void main(String[] args) throws IOException {
//创建工作簿
Workbook workbook = new XSSFWorkbook();
//创建工作表 不指定名则默认Sheet(0~N)
Sheet sheet = workbook.createSheet();
//创建第一行
Row row = sheet.createRow(0);
//创建第一列
Cell cellA = row.createCell(0);
//在0,0的位置写入日期数据
cellA.setCellValue(new Date());
//返回一个对象，该对象处理XSSF各种实例的具体类的实例化
CreationHelper creationHelper = workbook.getCreationHelper();
//创建新的DataFormat实例。获取与给定格式字符串匹配的格式索引，在需要时创建一个新的格式条目。
short format = creationHelper.createDataFormat().getFormat("yyyy-MM-dd");
//通过Workbook来获取一个样式操作类来对已有的表设置样式
CellStyle cellStyle = workbook.createCellStyle();
//把日期格式设置当当前样式cellStyle中
cellStyle.setDataFormat(format);
//在1,1位置写入日期数据 并对1,1位置写入样式
Cell cellB = row.createCell(1);
cellB.setCellValue(new Date());
cellB.setCellStyle(cellStyle);
//把构建的内存数据写入到磁盘
OutputStream out = new FileOutputStream("h://test1.xlsx");
workbook.write(out);
//关闭资源
out.close();
workbook.close();
}
插入日期类型及转换
public static void main(String[] args) throws IOException {
//创建工作簿
Workbook workbook = new XSSFWorkbook();
//创建工作表 不指定名则默认Sheet(0~N)
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("我是字符串");
row.createCell(1).setCellValue(25.5555);
row.createCell(2).setCellValue(true);
//对0,3 0,4 时间类和日历类
row.createCell(3).setCellValue(new Date());
row.createCell(4).setCellValue(Calendar.getInstance());
//把构建的内存数据写入到磁盘
OutputStream out = new FileOutputStream("h://test1.xlsx");
workbook.write(out);
//关闭资源
out.close();
workbook.close();
}
创建不同类型单元格
3：总结上面1~2节知识来个总结
学完基础的我们就可以自己动手写个简单的创建xlsx格式的文件了，再接下来的几节我将创建更好看的样式表格及读xlsx文件
public static void main(String[] args) throws IOException {
//创建数据源
Object[] title = {"ID", "姓名", "零花钱", "生日", "是否被删除"};
Object[] stu1 = {"tx001", "许龄月", 66.66, new Date(), true};
Object[] stu2 = {"tx002", "周星驰", 66.66, new Date(213465654L), false};
List<Object[]> list = new ArrayList<>();
list.add(title);
list.add(stu1);
list.add(stu2);
//创建工作簿
Workbook workbook = new XSSFWorkbook();
//创建工作表
Sheet sheet = workbook.createSheet("总结");
//循环创建
for (int r = 0; r < list.size(); r++) {
//创建行
Row row = sheet.createRow(r);
for (int c = 0; c < list.get(r).length; c++) {
if (r == 0) {
//标题写入
//创建列
Cell cell = row.createCell(c);
cell.setCellValue(list.get(r)[c].toString());
} else {
Object o = list.get(r)[c]; //获取每个单元格数据然后匹配类型
//创建列
Cell cell = row.createCell(c);
//此时数据是要改变样式
if (o instanceof Date) {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy-mm-dd"));
cell.setCellValue((Date) list.get(r)[c]);
cell.setCellStyle(cellStyle);
} else {
//写入正文
cell.setCellValue(list.get(r)[c].toString());
}
}
}
}
//写出及关闭
OutputStream out = new FileOutputStream("h:\\test02.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
小练习
4：从xlsx文件中遍历数据
遍历数据可谓是重点了，我们得创建一个工作簿并传入xlsx文件，其中获取起始行和结尾行则使用 getFirstRowNum 和 getLastRowNum 方法，但是获取的每个单元格表格数据类型不同则需要使用类型匹配
public static void main(String[] args) throws IOException {
//创建工作簿并传入一个真实存在的文件
Workbook workbook = new XSSFWorkbook("h:\\abc.xlsx");
//获取文件的第一个工作表
Sheet sheetAt = workbook.getSheetAt(0);
//获取工作表中的起始行
int rowStart = Math.max(0, sheetAt.getFirstRowNum());
//获取工作表中的结束行,如果大于21行则按照21行读 0开始-20
int rowEnd = Math.min(20, sheetAt.getLastRowNum());
//循环行rowStart ~ rowEnd
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
//创建行实例
Row row = sheetAt.getRow(rowNum);
//为空则全部结束
if (row == null) {
continue;
}
//不为空则创建每行的列 lastColumn 如果读到的行数大于11行则按照11行读取
int lastColumn = Math.min(10, row.getLastCellNum());
//循环每行的列0 ~ lastColumn
for (int colNum = 0; colNum < lastColumn; colNum++) {
//创建列的实例
Cell cell = row.getCell(colNum);
//列为空的话就跳出循环去遍历下一列
if (cell == null) {
continue;
} else {
//switch来匹配类型 ，因为表格的每个类型不一样
switch (cell.getCellType()) {
case NUMERIC: //数字类型 （这个相对复杂，还要判断是数字还是日期）
//通过查找StylesSource获取格式字符串的内容
String formatString = cell.getCellStyle().getDataFormatString();
//判断当前的字符串形式的内容是否以m/d/yy的日期格式
if (formatString.equals("m/d/yy")) {
System.out.print(cell.getDateCellValue() + " | ");
} else {
System.out.print(cell.getNumericCellValue() + " | ");
}
break;
case STRING: //字符串类型
System.out.print(cell.getStringCellValue() + " | ");
break;
case BOOLEAN: //真假值
System.out.print(cell.getBooleanCellValue() + " | ");
break;
case ERROR: //错误类型
System.out.print(cell.getErrorCellValue() + " | ");
break;
case BLANK: //空数据
System.out.print("空数据" + " | ");
break;
case FORMULA: //计算类型
System.out.print(cell.getCellFormula() + " | ");
case _NONE: //未知类型
System.out.print("未知类型" + " | ");
}
}
}
System.out.println();
}
workbook.close();
}
遍历查询全部单元格及数据
三：POI操作之XSSF（样式）
1：设置单元格的对齐方式
说到对齐方式我们就得谈谈2个枚举类了 HorizontalAlignment（水平对齐）VerticalAlignment（垂直对齐），但是我们设置对齐方式还得借助CellStyle样式接口内部的实现类完成，通过 setAlignment 和 setVerticalAlignment 方法来对单元格设置对齐方式
public static void main(String[] args) throws IOException {
//创建工作簿和创建工作表
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
//设置行
Row row = sheet.createRow(0);
//设置行高度
row.setHeightInPoints(60);
//创建0，1单元格
Cell cell = row.createCell(1);
cell.setCellValue("啦啦");
//创建样式
CellStyle cellStyle = workbook.createCellStyle();
//设置单元格的水平对齐类型。 此时水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置单元格的垂直对齐类型。 此时垂直靠底边
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
//把样式设置到单元格上
cell.setCellStyle(cellStyle);
//写出及关闭
OutputStream out = new FileOutputStream("h:\\test03.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
单元格的对齐方式代码演示
2：设置单元格的边框样式及边框的颜色
感觉这个用处并不大，要设置漂亮样式可以使用一番 主要是通过 CellStyle 来设置边框和边框颜色，具体的就得参照BorderStyle、IndexedColors这2个枚举类来获取具体的样式
public static void main(String[] args) throws IOException {
//创建工作簿和创建工作表
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
//设置行
Row row = sheet.createRow(1);
//为了可以看到样式 特地设置大高度
row.setHeightInPoints(60);
//创建0，1单元格
Cell cell = row.createCell(1);
cell.setCellValue("啦");
//创建样式
CellStyle cellStyle = workbook.createCellStyle();
//通过set设置边框样式及边框颜色 反之通过get可以获取设置的样式
cellStyle.setBorderBottom(BorderStyle.DOTTED);
cellStyle.setBottomBorderColor(IndexedColors.GREEN.getIndex());
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setTopBorderColor(IndexedColors.CORAL.getIndex());
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setLeftBorderColor(IndexedColors.RED.getIndex());
cellStyle.setBorderRight(BorderStyle.DASH_DOT_DOT);
cellStyle.setRightBorderColor(IndexedColors.AQUA.getIndex());
//别忘了把刚才这些设置的样式设置到单元格上
cell.setCellStyle(cellStyle);
//写出及关闭
OutputStream out = new FileOutputStream("h:\\test03.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
设置单元格边框样式及颜色
3：设置单元格填色
有时候我们会对单元格前景/背景进行色彩设置，以及对单元格的填充图案，这时候我们就会用到样式类里面的填充方法，具体看代码
public static void main(String[] args) throws IOException {
//创建工作簿和创建Sheet0工作表
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
//设置第一列的宽度是20个字符宽度
sheet.setColumnWidth(1, 20*256);
//创建第一行
Row row = sheet.createRow(1);
row.setHeightInPoints(60);
//创建样式对象
CellStyle style = workbook.createCellStyle();
//设置背景色
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
//设置填充图案（纹理）填充图案默认为黑色
style.setFillPattern(FillPatternType.BIG_SPOTS);
//此时我设置填充图案（前景色为红色）
style.setFillForegroundColor(IndexedColors.RED.getIndex());
//创建行（1，1）并设置文本加样式
Cell cell = row.createCell(1);
cell.setCellValue("蚂蚁小哥");
cell.setCellStyle(style);
//创建流并写出文件关闭流
OutputStream out = new FileOutputStream("h:\\test3.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
纹理/背景/纹理颜色设置
<!--Enum FillPatternType:--> <!--Enum：IndexedColors：--> 常量名 汉译 常量名 汉译 常量名 汉译 常量名 汉译 ALT_BARS： 宽点 BLACK： 黑色 GREEN： 绿色 DARK_RED：深红色 BIG_SPOTS： 大斑点 BLACK1： 黑色1 VIOLET： 紫罗兰 DARK_BLUE： 深蓝色 BRICKS： 砖状布局 WHITE： 白色 TEAL： 蓝绿色 DARK_YELLOW：深黄色 DIAMONDS： 钻石 WHITE1： 白色1 MAROON： 栗色 DARK_GREEN： 深绿色 FINE_DOTS： 小细点 RED： 红色 ROSE： 粉红色 DARK_TEAL： 深青色 LEAST_DOTS： 最小点 RED1： 红色1 AQUA： 水绿色 LIGHT_GREEN： 浅绿色 LESS_DOTS： 少点 BRIGHT_GREEN：亮绿色 LIME： 石灰色 LIGHT_YELLOW： 浅黄色 NO_FILL： 无背景 BRIGHT_GREEN1：亮绿色1 GOLD： 金 LIGHT_ORANGE：淡橙色 SOLID_FOREGROUND： 实填 BLUE： 蓝色 LAVENDER：淡紫色 LIGHT_BLUE： 浅蓝色 SPARSE_DOTS： 稀疏点 BLUE1： 蓝色1 BROWN： 棕色 LIGHT_CORNFLOWER_BLUE：浅矢车菊蓝 SQUARES： 正方形 YELLOW： 黄色 PLUM： 紫红色 PALE_BLUE： 淡蓝色 THICK_BACKWARD_DIAG：厚厚的后向对角线 YELLOW1： 黄色1 INDIGO： 靛蓝色 SEA_GREEN： 海绿色 THICK_FORWARD_DIAG： 厚正面对角线 PINK： 粉红 TAN： 棕黄色 BLUE_GREY： 蓝灰 THICK_HORZ_BANDS： 厚水平带 PINK1： 粉色1 ORCHID： 兰花色 SKY_BLUE： 天空蓝色 THICK_VERT_BANDS： 厚垂直带 TURQUOISE： 青绿色 CORAL： 珊瑚色 GREY_25_PERCENT：灰25% THIN_BACKWARD_DIAG： 薄后向对角线 TURQUOISE1： 青绿色1 ROYAL_BLUE：皇家蓝 GREY_40_PERCENT：灰40% THIN_FORWARD_DIAG： 细正对角线 LEMON_CHIFFON： 柠檬雪纺 ORNFLOWER_BLUE：矢车菊蓝 GREY_50_PERCENT：灰50% THIN_HORZ_BANDS： 薄水平带 LIGHT_TURQUOISE：浅青绿色 ORANGE： 桔黄色的 GREY_80_PERCENT：灰80% THIN_VERT_BANDS： 薄垂直带 LIGHT_TURQUOISE1：浅青绿色1 OLIVE_GREEN： 橄榄绿 AUTOMATIC：自然色关于FillPatternType和IndexedColors枚举类的中文汉译
4：合并单元格
有时候我们要设置单元格的合并，这个时候我们就可以使用addMergedRegion方法完成
public static void main(String[] args) throws IOException {
//创建工作簿和创建Sheet0工作表
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
//设置单元格合并
//第一个参数和第二个参数设置左位置(1,1) 0开始
//第三个参数和第四个参数设置右位置(1,5) 0开始
//连接2点的范围的形状就是单元格合并的范围 注意只是合并单元格 不能跨行！！想单元格高点就设置高度
sheet.addMergedRegion(new CellRangeAddress(1,1,1,5));
//创建行
Row row = sheet.createRow(1);
//创建列此时为1,1 并为单元格设置信息
Cell cell = row.createCell(1);
cell.setCellValue("蚂蚁小哥");
//创建流并写出文件关闭流
OutputStream out = new FileOutputStream("h:\\test3.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
合并单元格练习
5：字体样式设置
对字体的设置我们得通过工作簿先创建字体样式类，然后再进行字体的设置
public static void main(String[] args) throws IOException {
//创建工作簿和创建Sheet0工作表
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
//创建行
Row row = sheet.createRow(1);
//创建字体类
Font font = workbook.createFont();
//设置字体颜色
font.setColor(IndexedColors.RED1.getIndex());
//设置字体 true粗体（默认） false细
font.setBold(false);
//设置字体大小
font.setFontHeightInPoints((short) 60);
//设置字体名 如楷体，微软雅黑....中文和英文表示都行
font.setFontName("楷体");
//倾斜设置
font.setItalic(true);
//设置删除线
font.setStrikeout(true);
//创建样式类
CellStyle cellStyle = workbook.createCellStyle();
//样式设置font样式
cellStyle.setFont(font);
//创建列此时为1,1 并为单元格设置信息
Cell cell = row.createCell(1);
cell.setCellValue("蚂蚁小哥");
cell.setCellStyle(cellStyle);
//创建流并写出文件关闭流
OutputStream out = new FileOutputStream("h:\\test3.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
字体样式设置
6：使用函数方便构建样式及单元格合并
通过上面的样式创建和单元格的创建我们发现，每次设置样式都要创建对象，麻烦，还浪费内存，其实ReginUtil（区域工具）CellUtil（样式工具）可以很快构建我们的样式，可以简写很多代码
public static void main(String[] args) throws IOException {
//创建工作簿和创建Sheet0工作表
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("new sheet");
//创建2行 对应表中的第1行和第2行 0开始
Row row = sheet.createRow(1);
Row row2 = sheet.createRow(2);
//创建1,1的单元格
Cell cell = row.createCell(1);
cell.setCellValue("蚂蚁小哥");
//此类可以创建单元格的范围 B2（1,1）：E5（4,4）
CellRangeAddress region = CellRangeAddress.valueOf("B2:E5");
//传递给合并单元格方法里
sheet.addMergedRegion(region);
//设置四边样式及颜色 但是得传递Sheet和CellRangeAddress
RegionUtil.setBorderBottom(BorderStyle.MEDIUM_DASHED, region, sheet);
RegionUtil.setBorderTop(BorderStyle.MEDIUM_DASHED, region, sheet);
RegionUtil.setBorderLeft(BorderStyle.MEDIUM_DASH_DOT, region, sheet);
RegionUtil.setBorderRight(BorderStyle.MEDIUM_DASHED, region, sheet);
RegionUtil.setBottomBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);
RegionUtil.setTopBorderColor(IndexedColors.AQUA.getIndex(), region, sheet);
RegionUtil.setLeftBorderColor(IndexedColors.RED.getIndex(), region, sheet);
RegionUtil.setRightBorderColor(IndexedColors.AQUA.getIndex(), region, sheet);
//创建样式类
CellStyle style = workbook.createCellStyle();
//设置空格数以缩进单元格中的文本
style.setIndention((short) 4);
CellUtil.createCell(row, 8, "my name is ant", style);
Cell cell2 = CellUtil.createCell(row2, 8, "阿三大苏打");
//设置居中
CellUtil.setAlignment(cell2, HorizontalAlignment.CENTER);
//创建流并写出文件关闭流
OutputStream out = new FileOutputStream("h:\\test3.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
使用函数方法来构建样式及区域合并
.