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

引入依赖

    <!-- excel poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>

简单使用

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

package com.liuzhihang.tool.excel.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

/**
 * @author liuzhihang
 * @date 2018/4/20 16:12
 */
public class ExcelTest {

    public static void main(String[] args) throws Exception {

        // readerTest();
        writerTest();
    }

    private static void writerTest() throws IOException {

        File file = new File("c:Users/liuzhihang/Desktop/test.xlsx");
        if (file.exists()) {
            System.out.println("读取的文件存在!");
            file.delete();
        }
        file.createNewFile();
        // 操作 .xls 的 workbook
        Workbook hssfWorkbook = new HSSFWorkbook();
        // 操作 .xlsx 的 workbook
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

        // 创建 sheet 页
        XSSFSheet sheet = xssfWorkbook.createSheet();
        // 创建 0 行 操作对象
        Row row0 = sheet.createRow(0);
        // 创建单元格并赋值
        row0.createCell(0).setCellValue("序号");

        OutputStream outputStream = new FileOutputStream(file);
        // 写入文件
        xssfWorkbook.write(outputStream);
    }

    private static void readerTest() throws Exception {

        File file = new File("c:Users/liuzhihang/Desktop/parkingLotTempLate.xlsx");

        Workbook workBook = ExcelUtil.getWorkBook(file);

        // 获取 excel 页
        // Sheet sheetByIndex = workBook.getSheetAt(0);
        // Sheet sheetByName = workBook.getSheet("Sheet0");

        // 操作 sheet
        Sheet sheet = workBook.getSheetAt(0);
        // 获取最后一行行数 从 0 开始
        int lastRowNum = sheet.getLastRowNum();
        // 获取总行数
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();

        // 操作行 获取第0行
        Row row = sheet.getRow(0);
        String value = row.getCell(0).getStringCellValue();

    }


}

ExcelUtil 简单工具

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

package com.liuzhihang.tool.excel.poi;

import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;


/**
 * @author liuzhihang
 * @date 2018/4/20 12:02
 */
@Log4j2
public class ExcelUtil {

    /**
     * 读取两列excel 返回第二列的集合
     *
     * @param workbook
     * @return
     */
    public static List<String> readExcelForTwoColumns(Workbook workbook) {

        if (workbook == null) {
            log.info("获取 workbook 为null");
            return null;
        }

        List<String> list = new ArrayList<>();

        try {
            Sheet sheet = workbook.getSheetAt(0);
            //获取总行数
            int rowNum = sheet.getLastRowNum();
            //正文内容应该从第二行开始，第一行为文件的标头的标题
            for (int i = 0; i < rowNum; i++) {
                Row row = sheet.getRow(i + 1);
                String value = getCellValue(row.getCell(1)).toString();
                if (StringUtils.isNotBlank(value)) {
                    list.add(value);
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage());
        }
        return list;
    }

    /**
     * 写 excel
     *
     * @param excelFile
     * @param list
     */
    public static void writerExcelForTwoColumns(File excelFile, List<String> list) {

        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(excelFile);

            Workbook workBook = null;
            String fileName = excelFile.getName();
            if (fileName.endsWith(".xls")) {
                workBook = new HSSFWorkbook();
            } else if (fileName.endsWith(".xlsx")) {
                workBook = new XSSFWorkbook();
            } else {
                log.info("文件格式不正确!, 当前文件名:{}", fileName);
                throw new Exception("文件格式不正确");
            }
            // 创建第 0 页
            Sheet sheet = workBook.createSheet();
            Row row1 = sheet.createRow(0);
            row1.createCell(0).setCellValue("序号");
            row1.createCell(1).setCellValue("编号");

            for (int i = 0; i < list.size(); i++) {
                Row row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue(i + 1);
                row.createCell(1).setCellValue(list.get(i));
            }

            workBook.write(outputStream);
        } catch (Exception e) {
            log.error("写excel失败", e);
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取工作表
     *
     * @param file
     * @return
     */
    public static Workbook getWorkBook(File file) throws Exception {

        String fileName = file.getName();
        Workbook workbook = null;

        try {
            InputStream inputStream = new FileInputStream(file);
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                log.info("文件格式不正确!, 当前文件名:{}", fileName);
                throw new Exception("文件格式不正确");
            }
        } catch (Exception e) {
            throw e;
        }
        return workbook;
    }

    /**
     * 获取单元格的数据
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {

        if (cell != null) {
            switch (cell.getCellTypeEnum()) {
                // 数字
                case NUMERIC:
                    return cell.getNumericCellValue();
                // 字符串
                case STRING:
                    return cell.getStringCellValue();
                // 公式
                case FORMULA:
                    return cell.getCellFormula();
                // 布尔
                case BOOLEAN:
                    return cell.getBooleanCellValue();
                case ERROR:
                    return cell.getErrorCellValue();
                // 空
                default:
                    return "";
            }
        }
        return "";
    }

}