您的位置:首页 > 数据 >
POI 4.1.2 操作Excel的技巧 POI4.1.2操作Excel的快速应用_当前头条
来源:CSDN 2023-01-28 09:08:20

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

关键词:
相关文章
当前滚动:四川铁路电煤专列同比增长9.8%

当前滚动:四川铁路电煤专列同比增长9.8%

  今年春运以来,中国铁路成都局集团有限公司已抢卸电煤3万余车、190万余吨,其中,四川地区已抢卸电煤13万余车、83万余吨,卸车数同比增长9更多

2023-01-28 09:17:28
内蒙古:煤企生产忙 供应不断档

内蒙古:煤企生产忙 供应不断档

  春节假期,位于内蒙古乌海市的国家能源集团乌海能源公司加强煤炭生产组织,确保煤炭供应不断档。乌海能源公司提前制定计划,适时调整煤炭更多

2023-01-28 09:15:32
陕煤入渝发运量再创新高 2022年达1798.3万吨 环球速看料

陕煤入渝发运量再创新高 2022年达1798.3万吨 环

  据陕煤运销集团统计数据显示,2022年陕煤入渝发运量17983万吨,同比增长132%,入渝年度发运量创下历史新纪录,有效保障了重庆地区电力和重更多

2023-01-20 11:10:02
全球快看:为完成2023年煤炭供应目标 晋城市出台了7项措施

全球快看:为完成2023年煤炭供应目标 晋城市出台

  晋城市能源局消息,该市日前发布了煤炭增产保供和产能新增工作方案并提出,通过核增产能、扩产、新投产等方式,加快释放先进产能,充分发更多

2023-01-20 11:09:59
当前关注:2022年内蒙古煤电气产量对全国增长贡献均居首位

当前关注:2022年内蒙古煤电气产量对全国增长贡献

  2022年,内蒙古全区积极落实煤电增产保供工作,规模以上工业煤电气等主要能源产品产量均位居全国前列,对全国增长贡献率均位居首位,有力更多

2023-01-20 11:11:26
全球微速讯:山东:细化分解任务 坚决完成煤炭保供任务

全球微速讯:山东:细化分解任务 坚决完成煤炭保

  山东省政府工作报告提出,要抓好能源保供。菏泽代表团三名来自煤炭企业的省人大代表,围绕报告精神,就如何保障煤炭供应,加强能源保供建更多

2023-01-20 10:54:12
CCTD:短期煤市基本面仍难寻亮点

CCTD:短期煤市基本面仍难寻亮点

  据CCTD了解,节前最后一周,煤炭市场交易逐渐停滞,周初在封航港口库存迅速累积情况下,贸易商报价有小幅下跌。随后封航结束,港口调出恢更多

2023-01-20 11:02:24
每日观察!12月份内蒙古煤炭价格环比下降8.3%

每日观察!12月份内蒙古煤炭价格环比下降8.3%

  2022年12月份受疫情防控、原油价格变化,农业生产进程和建筑工程进展等因素影响,煤炭价格环比下降。据内蒙古自治区商务厅生产资料市场监更多

2023-01-19 10:54:37
促开采、增储备、抢维修、保运输,各地多措并举——力保群众温暖过冬过年 焦点速讯

促开采、增储备、抢维修、保运输,各地多措并举—

  中央经济工作会议要求,要做好岁末年初各项工作,强化市场保供稳价,加强能源调节,确保群众温暖安全过冬。冬季供暖事关千家万户,是重要更多

2023-01-19 10:08:31
生态环境部:推动保供煤矿全部按时保质完成环评办理

生态环境部:推动保供煤矿全部按时保质完成环评办

  生态环境部环境影响评价与排放管理司司长刘志全17日在新闻发布会上表示,2023年将落实环评审批三本台账和绿色通道机制,推进十四五重大工更多

2023-01-19 08:59:13