POI 4.1.2 操作 Excel
1. POI 简介
【资料图】
POI（Poor Obfuscation Implementation），直译为“可怜的模糊实现”，利用POI接口可以通过 Java操作 Microsoft office 套件工具的读写功能。POI支持office的所有版本。
org.apache.poipoi4.1.2org.apache.poipoi-ooxml4.1.2
2. POI 的快速应用
（1）操作后缀为 .xls的 Excel文件
在 POI 中的几个主要对象：
HSSFWorkbookExcel 工作簿workbook
HSSFSheetExcel 工作表 sheet
HSSFRowExcel 行
HSSFCellExcel 单元格
使用 POI 创建 Excel
/** * 创建 Excel 测试 * @throws IOException 文件操作异常 */@Testpublic void createTest() throws IOException { // 新建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 新建工作表 HSSFSheet sheet = workbook.createSheet(); // 创建行，行号为 0 HSSFRow row = sheet.createRow(0); // 创建单元格，表示为第 3 个单元格 HSSFCell cell = row.createCell(2); // 输入值 cell.setCellValue("Hello World!"); // 将 Excel 进行文件导出 // 获取文件路径和文件 Path path = Paths.get(".").resolve("hello.xls"); // 文件已存在则删除 if (Files.exists(path)){ Files.delete(path); } // 创建文件 File file = Files.createFile(path).toFile(); // 写入数据 workbook.write(file);}
运行结果：
使用 POI 向 Excel 中追加
/** * 向已存在的 Excel 中追加内容 * @throws IOException */@Testpublic void appendTest() throws IOException { // 获取文件输入流 String path = "./hello.xls"; File file = new File(path); int rowIndex = 4; HSSFWorkbook workbook = null; HSSFSheet sheet = null; if(file.exists()){ FileInputStream fis = new FileInputStream(file); // 新建工作簿 workbook = new HSSFWorkbook(fis); // 新建工作表 sheet = workbook.getSheet("sheet0"); } else{ // 新建工作簿 workbook = new HSSFWorkbook(); // 新建工作表 sheet = workbook.createSheet(); } // 获取行号 HSSFRow row = sheet.getRow(rowIndex); // 判断是否存在,不存在则创建 if (row == null){ row = sheet.createRow(rowIndex); } // 创建单元格，表示为第 3 个单元格 HSSFCell cell = row.createCell(5); // 输入值 cell.setCellValue("追加3!"); // 将 Excel 进行文件导出 // 写入数据 workbook.write(file);}
运行结果：
使用 POI 读取 Excel
/** * 读取 Excel 测试 * @throws IOException 文件操作异常 */@Testpublic void readTest() throws IOException { // 读取 Excel 文件 // 获取文件路径和文件 FileInputStream fis = new FileInputStream("./hello.xls"); // 将输入流转换为工作簿对象 HSSFWorkbook workbook = new HSSFWorkbook(fis); // 获取第一个工作表 HSSFSheet sheet = workbook.getSheet("sheet0"); // 使用索引获取工作表 // HSSFSheet sheet = workbook.getSheetAt(0); // 获取指定行 HSSFRow row = sheet.getRow(0); // 获取指定列 HSSFCell cell = row.getCell(2); // 打印 System.out.println(cell.getStringCellValue());}
运行结果：
（2）操作后缀为 .xlsx的 Excel文件
在 POI 中的几个主要对象：
XSSFWorkbookExcel 工作簿workbook
XSSFSheetExcel 工作表 sheet
XSSFRowExcel 行
XSSFCellExcel 单元格
使用 POI 创建 Excel 表
/** * 创建以 .xlsx 为后缀的 Excel 测试 * @throws IOException 文件操作异常 */@Testpublic void createXlsxTest() throws IOException { // 新建工作簿 XSSFWorkbook workbook = new XSSFWorkbook(); // 新建工作表 XSSFSheet sheet = workbook.createSheet(); // 创建行，行号为 0 XSSFRow row = sheet.createRow(0); // 创建单元格，表示为第 3 个单元格 XSSFCell cell = row.createCell(2); // 输入值 cell.setCellValue("Hello World!"); // 将 Excel 进行文件导出 // 获取文件 File file = new File("./hello.xlsx"); // 如果文件存在则删除 if (file.exists()){ file.delete(); } // 创建输出流 FileOutputStream fos = new FileOutputStream(file); // 写入 workbook.write(fos); fos.close();}
运行结果：
使用 POI 追加 Excel 文件
/** * 向已存在以 .xlsx 为后缀的 Excel 中追加内容 * @throws IOException */@Testpublic void appendXlsxTest() throws IOException { // 获取文件输入流 String path = "./hello.xlsx"; File file = new File(path); int rowIndex = 4; XSSFWorkbook workbook = null; XSSFSheet sheet = null; if(file.exists()){ FileInputStream fis = new FileInputStream(file); // 新建工作簿 workbook = new XSSFWorkbook(fis); // 新建工作表 sheet = workbook.getSheet("sheet0"); } else{ // 新建工作簿 workbook = new XSSFWorkbook(); // 新建工作表 sheet = workbook.createSheet(); } // 创建行，行号为 0 XSSFRow row = sheet.getRow(rowIndex); // 判断是否存在,不存在则创建 if (row == null){ row = sheet.createRow(rowIndex); } // 创建单元格，表示为第 3 个单元格 XSSFCell cell = row.createCell(5); // 输入值 cell.setCellValue("追加内容1"); // 将 Excel 进行文件导出 FileOutputStream fos = new FileOutputStream(file); // 写入数据 workbook.write(fos);}
运行结果：
使用 POI 读取 Excel 文件
/** * 读取以 .xlsx 为后缀的 Excel 测试 * @throws IOException 文件操作异常 */@Testpublic void readXlsxTest() throws IOException { // 读取 Excel 文件 // 获取文件路径和文件 FileInputStream fis = new FileInputStream("./hello.xlsx"); // 将输入流转换为工作簿对象 XSSFWorkbook workbook = new XSSFWorkbook(fis); // 获取第一个工作表 XSSFSheet sheet = workbook.getSheet("sheet0"); // 使用索引获取工作表 // XSSFSheet sheet = workbook.getSheetAt(0); // 获取指定行 XSSFRow row = sheet.getRow(0); // 获取指定列 XSSFCell cell = row.getCell(2); // 打印 System.out.println(cell.getStringCellValue());}
运行结果：
（3）根据后缀名读取 Excel 文件
通过正则表达式读取
/** * 根据后缀名读取 Excel 文件 */@Testpublic void readAllExcel() throws IOException { // 两个不同后缀名文件的路径 String path1 = "./hello.xlsx"; String path2 = "./hello.xls"; readExcel(path1); readExcel(path2);}/** * 根据文件路径读取 Excel 文件 * @param path 文件路径 * @throws IOException 文件操作异常 */public void readExcel(String path) throws IOException { // 获取后缀 String suffix = path.substring(path.lastIndexOf(".")); // 判断后缀为 xls 还是 xlsx if (path.matches("^.+\\.(?i)((xls)|(xlsx))$")){ // 创建输入流 FileInputStream fis = new FileInputStream(path); // 判断是否为以 .xls 结尾的 Excel 文件 boolean isXlsExcel = path.matches("^.+\\.(?i)(xls)$"); // 判断后缀生成 工作簿 Workbook workbook = isXlsExcel ? new HSSFWorkbook(fis) : new XSSFWorkbook(fis); // 获取工作表 Sheet sheet = workbook.getSheet("sheet0"); // 获取指定行 Row row = sheet.getRow(0); // 获取指定列 Cell cell = row.getCell(2); // 输出值 System.out.println(suffix + " :\t" + cell.getStringCellValue()); }}
运行结果：
通过 WorkbookFactory 读取
/** * 通用读取的方法 */@Testpublic void generalRead() throws IOException { String path1 = "./hello.xls"; String path2 = "./hello.xlsx"; generalRead(path1); generalRead(path2);}public void generalRead(String path) throws IOException { File file = new File(path); Workbook workbook = WorkbookFactory.create(file); Sheet sheet = workbook.getSheet("sheet0"); String value = sheet.getRow(0).getCell(2).getStringCellValue(); String suffix = path.substring(path.lastIndexOf(".")+1); System.out.println(suffix + " :\t" + value);}
运行结果：
3. POI 的高级操作
（1）合并单元格
/** * 合并单元格 * CellRangeAddress(firstRow,lastRow,firstCol,lastCol) * 四个参数分别为 起使行号,终止行号,起使列,终止列 * * @throws IOException 文件操作异常 */@Testpublic void mergeCells() throws IOException { // 新建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 新建工作表 HSSFSheet sheet1 = workbook.createSheet("sheet1"); // 设置要合并的单元格范围，合并第二行的第二列到第五列为一个单元格 CellRangeAddress rangeAddress = new CellRangeAddress(1, 1, 1, 4); sheet1.addMergedRegion(rangeAddress); // 设置合并后的单元格内容 HSSFRow row = sheet1.createRow(1); HSSFCell cell = row.createCell(1); cell.setCellValue("我是合并后的单元格"); FileOutputStream outputStream = new FileOutputStream("./工作簿.xls"); workbook.write(outputStream); outputStream.close();}
运行结果：
注：上图中合并单元格后，单元格的名称是第一个单元格；即上面中合并了第二行的第二列到第五列，合并后的单元格叫 B2，而其它被合并的单元格已经无效了，不能对无效单元格设置值。如果进行了设置将不显示。
（2）设置单元格样式
/** * 设置单元格样式 * @throws IOException 文件操作异常 */@Testpublic void cellStyle() throws IOException { // 新建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 新建工作表 HSSFSheet sheet = workbook.createSheet("工作表1"); // 创建行 HSSFRow row = sheet.createRow(3); // 创建单元格 HSSFCell cell = row.createCell(3); cell.setCellValue("单元格内容"); // 设置sheet的第四列的宽度, 列宽单位是字符的1 / 256. sheet.setColumnWidth(3, 20 * 256); // 创建单元格样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置单元格内容水平居中 style.setAlignment(HorizontalAlignment.CENTER); // 设置单元格内容垂直居中 style.setVerticalAlignment(VerticalAlignment.CENTER); // 创建字体 HSSFFont font = workbook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL);//字体为Arial font.setColor(HSSFFont.COLOR_RED);//字体颜色为红色 font.setBold(true);//设置为粗体 font.setFontHeightInPoints((short) 16);//设置字体大小 style.setFont(font); // 设置填充模式，模式为全部前景色 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置前景色为绿色 style.setFillForegroundColor(HSSFColorPredefined.GREEN.getColor().getIndex()); // 设置背景色，如果填充模式为其它填充模式，这个前景和背景色将相互交映显示 style.setFillBackgroundColor(HSSFColorPredefined.RED.getColor().getIndex()); // 为特定单元格设置样式 cell.setCellStyle(style); FileOutputStream fos = new FileOutputStream("./工.xls"); workbook.write(fos); fos.close();}
运行结果：
使用 POI 导入 Excel 文件到 MySQL 中
// 使用 Spring Test 测试，数据库操作可更改为普通的 JDBC 操作@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations = "classpath:applicationContext.xml")public class TestPoi { @Autowired private DataSource dataSource; @Test public void test() throws IOException, SQLException { // Excel 路径 String path = "./xxx.xlsx"; File file = new File(path); // 获取数据库连接 Connection conn = dataSource.getConnection(); // 插入的 SQL 语句 String sql = "insert into sheet1 values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement ps = conn.prepareStatement(sql); // 获取工作簿 Workbook workbook = WorkbookFactory.create(file); // 获取当前表 Sheet sheet = workbook.getSheetAt(0); // 获取总行数 int totalRows = sheet.getPhysicalNumberOfRows(); // 获取表头的总列数 int totalCols = sheet.getRow(0).getPhysicalNumberOfCells(); // 遍历行 for (int i = 1; i < totalRows; i++) { // 遍历列 for (int j = 0; j < totalCols; j++) { // 获取 i 行 j 列 Cell cell = sheet.getRow(i).getCell(j); // 判断该列是否为 null if (cell == null || cell.getCellType() == CellType.BLANK){ ps.setString(j+1,null); continue; } // 由于测试的 Excel 此处的最后一位为日期类型,这里使用日期输出 if (j==totalCols-1){ cell.setCellType(CellType.NUMERIC); System.out.println(new Date(cell.getDateCellValue().getTime())); ps.setDate(j+1,new Date(cell.getDateCellValue().getTime())); }else { // 判断是否是字符类型 if(cell.getCellType() == CellType.STRING){ ps.setString(j+1,cell.getStringCellValue()); }else{ // 单元格为数值类型 cell.setCellType(CellType.NUMERIC); ps.setInt(j+1, (int) cell.getNumericCellValue()); } } } // 执行 SQL ps.executeUpdate(); } ps.close(); conn.close(); }}
