Apache POI 可以对Microsoft Office 进行操作, 下面是工作中使用的对Excel进行读写操作的常用方式.

引入依赖

  1. <!-- excel poi -->
  2.     <dependency>
  3.         <groupId>org.apache.poi</groupId>
  4.         <artifactId>poi</artifactId>
  5.         <version>3.17</version>
  6.     </dependency>
  7.     <dependency>
  8.         <groupId>org.apache.poi</groupId>
  9.         <artifactId>poi-ooxml</artifactId>
  10.         <version>3.17</version>
  11.     </dependency>

简单使用

主要介绍读写时, 分别常用到的一些对象及其含义, 方便自己编写util.

  1. package com.liuzhihang.tool.excel.poi;
  2. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  3. import org.apache.poi.ss.usermodel.Row;
  4. import org.apache.poi.ss.usermodel.Sheet;
  5. import org.apache.poi.ss.usermodel.Workbook;
  6. import org.apache.poi.xssf.usermodel.XSSFSheet;
  7. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  8. import java.io.File;
  9. import java.io.FileOutputStream;
  10. import java.io.IOException;
  11. import java.io.OutputStream;
  12. /**
  13.  * @author liuzhihang
  14.  * @date 2018/4/20 16:12
  15.  */
  16. public class ExcelTest {
  17.     public static void main(String[] args) throws Exception {
  18.         // readerTest();
  19.         writerTest();
  20.     }
  21.     private static void writerTest() throws IOException {
  22.         File file = new File("c:Users/liuzhihang/Desktop/test.xlsx");
  23.         if (file.exists()) {
  24.             System.out.println("读取的文件存在!");
  25.             file.delete();
  26.         }
  27.         file.createNewFile();
  28.         // 操作 .xls 的 workbook
  29.         Workbook hssfWorkbook = new HSSFWorkbook();
  30.         // 操作 .xlsx 的 workbook
  31.         XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
  32.         // 创建 sheet 页
  33.         XSSFSheet sheet = xssfWorkbook.createSheet();
  34.         // 创建 0 行 操作对象
  35.         Row row0 = sheet.createRow(0);
  36.         // 创建单元格并赋值
  37.         row0.createCell(0).setCellValue("序号");
  38.         OutputStream outputStream = new FileOutputStream(file);
  39.         // 写入文件
  40.         xssfWorkbook.write(outputStream);
  41.     }
  42.     private static void readerTest() throws Exception {
  43.         File file = new File("c:Users/liuzhihang/Desktop/parkingLotTempLate.xlsx");
  44.         Workbook workBook = ExcelUtil.getWorkBook(file);
  45.         // 获取 excel 页
  46.         // Sheet sheetByIndex = workBook.getSheetAt(0);
  47.         // Sheet sheetByName = workBook.getSheet("Sheet0");
  48.         // 操作 sheet
  49.         Sheet sheet = workBook.getSheetAt(0);
  50.         // 获取最后一行行数 从 0 开始
  51.         int lastRowNum = sheet.getLastRowNum();
  52.         // 获取总行数
  53.         int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
  54.         // 操作行 获取第0行
  55.         Row row = sheet.getRow(0);
  56.         String value = row.getCell(0).getStringCellValue();
  57.     }
  58. }

ExcelUtil 简单工具

poi读写 excel 的简单工具 ExcelUtil, 实际工作中可结合javaBean使用并重新编写util.

  1. package com.liuzhihang.tool.excel.poi;
  2. import lombok.extern.log4j.Log4j2;
  3. import org.apache.commons.lang.StringUtils;
  4. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  5. import org.apache.poi.ss.usermodel.Cell;
  6. import org.apache.poi.ss.usermodel.Row;
  7. import org.apache.poi.ss.usermodel.Sheet;
  8. import org.apache.poi.ss.usermodel.Workbook;
  9. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  10. import java.io.*;
  11. import java.util.ArrayList;
  12. import java.util.List;
  13. /**
  14.  * @author liuzhihang
  15.  * @date 2018/4/20 12:02
  16.  */
  17. @Log4j2
  18. public class ExcelUtil {
  19.     /**
  20.      * 读取两列excel 返回第二列的集合
  21.      *
  22.      * @param workbook
  23.      * @return
  24.      */
  25.     public static List<String> readExcelForTwoColumns(Workbook workbook) {
  26.         if (workbook == null) {
  27.             log.info("获取 workbook 为null");
  28.             return null;
  29.         }
  30.         List<String> list = new ArrayList<>();
  31.         try {
  32.             Sheet sheet = workbook.getSheetAt(0);
  33.             //获取总行数
  34.             int rowNum = sheet.getLastRowNum();
  35.             //正文内容应该从第二行开始，第一行为文件的标头的标题
  36.             for (int i = 0; i < rowNum; i++) {
  37.                 Row row = sheet.getRow(i + 1);
  38.                 String value = getCellValue(row.getCell(1)).toString();
  39.                 if (StringUtils.isNotBlank(value)) {
  40.                     list.add(value);
  41.                 }
  42.             }
  43.         } catch (Exception e) {
  44.             log.error(e.getMessage());
  45.         }
  46.         return list;
  47.     }
  48.     /**
  49.      * 写 excel
  50.      *
  51.      * @param excelFile
  52.      * @param list
  53.      */
  54.     public static void writerExcelForTwoColumns(File excelFile, List<String> list) {
  55.         OutputStream outputStream = null;
  56.         try {
  57.             outputStream = new FileOutputStream(excelFile);
  58.             Workbook workBook = null;
  59.             String fileName = excelFile.getName();
  60.             if (fileName.endsWith(".xls")) {
  61.                 workBook = new HSSFWorkbook();
  62.             } else if (fileName.endsWith(".xlsx")) {
  63.                 workBook = new XSSFWorkbook();
  64.             } else {
  65.                 log.info("文件格式不正确!, 当前文件名:{}", fileName);
  66.                 throw new Exception("文件格式不正确");
  67.             }
  68.             // 创建第 0 页
  69.             Sheet sheet = workBook.createSheet();
  70.             Row row1 = sheet.createRow(0);
  71.             row1.createCell(0).setCellValue("序号");
  72.             row1.createCell(1).setCellValue("编号");
  73.             for (int i = 0; i < list.size(); i++) {
  74.                 Row row = sheet.createRow(i + 1);
  75.                 row.createCell(0).setCellValue(i + 1);
  76.                 row.createCell(1).setCellValue(list.get(i));
  77.             }
  78.             workBook.write(outputStream);
  79.         } catch (Exception e) {
  80.             log.error("写excel失败", e);
  81.         } finally {
  82.             try {
  83.                 outputStream.close();
  84.             } catch (IOException e) {
  85.                 e.printStackTrace();
  86.             }
  87.         }
  88.     }
  89.     /**
  90.      * 获取工作表
  91.      *
  92.      * @param file
  93.      * @return
  94.      */
  95.     public static Workbook getWorkBook(File file) throws Exception {
  96.         String fileName = file.getName();
  97.         Workbook workbook = null;
  98.         try {
  99.             InputStream inputStream = new FileInputStream(file);
  100.             if (fileName.endsWith(".xls")) {
  101.                 workbook = new HSSFWorkbook(inputStream);
  102.             } else if (fileName.endsWith(".xlsx")) {
  103.                 workbook = new XSSFWorkbook(inputStream);
  104.             } else {
  105.                 log.info("文件格式不正确!, 当前文件名:{}", fileName);
  106.                 throw new Exception("文件格式不正确");
  107.             }
  108.         } catch (Exception e) {
  109.             throw e;
  110.         }
  111.         return workbook;
  112.     }
  113.     /**
  114.      * 获取单元格的数据
  115.      *
  116.      * @param cell
  117.      * @return
  118.      */
  119.     public static Object getCellValue(Cell cell) {
  120.         if (cell != null) {
  121.             switch (cell.getCellTypeEnum()) {
  122.                 // 数字
  123.                 case NUMERIC:
  124.                     return cell.getNumericCellValue();
  125.                 // 字符串
  126.                 case STRING:
  127.                     return cell.getStringCellValue();
  128.                 // 公式
  129.                 case FORMULA:
  130.                     return cell.getCellFormula();
  131.                 // 布尔
  132.                 case BOOLEAN:
  133.                     return cell.getBooleanCellValue();
  134.                 case ERROR:
  135.                     return cell.getErrorCellValue();
  136.                 // 空
  137.                 default:
  138.                     return "";
  139.             }
  140.         }
  141.         return "";
  142.     }
  143. }