一：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操作之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(); }
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：自然色
4：合并单元格
有时候我们要设置单元格的合并，这个时候我们就可以使用addMergedRegion方法完成
public static void main(String[] args) throws IOException { //创建工作簿和创建Sheet0工作表 Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); //设置单元格合并 0开始索引 //前2个参数设置开始行到结束行 //后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(); }
.