POI 4.1.2 操作Excel的技巧 POI4.1.2操作Excel的快速应用

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();    }}