Java 使用POI读写Excel文件

Excel相关概念

Workbook:工作簿,即一个Excel文件,Excel文件分两种,分别是后缀名为xls的HSSFWorkBook(2003版本及以前),和后缀名为xlsx的XSSFWorkBook(2007版本及以后)

Sheet:表格,一个Workbook中可以有多个表格

Row:行

Col:列

cell:单元格

依赖库

maven的依赖库为:

<dependencies>
		...
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.14</version>
		</dependency>
		...
</dependencies>

在添加依赖过程中需要注意的问题是poi和poi-ooxml两个库不能同时添加,否则会报错,一般添加poi-ooxml即可操作两种Excel文件。

使用gradle管理项目时因为依赖库产生的问题更多,目前暂未搞清,可以自己先尝试摸索一下。

 

代码实现

笔者以读写一个自建的Store类为例演示使用POI对文件的读写操作

package com.jieao.POITest;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

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;

public final class ExcelUtils {
    
    private final String TAG = "ExcelUtils";

    public static Workbook getWorkbook(String filePath) {
        Workbook workbook = null;
        try {
            InputStream inputStream = new FileInputStream(filePath);
            if (filePath.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (filePath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }
    
    public static boolean saveWorkbook(String filePath, Workbook workbook) {
        try {
            File file = new File(filePath);
            FileOutputStream fileoutputStream = new FileOutputStream(file);
            workbook.write(fileoutputStream);
            fileoutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public static List<Store> readStoreListFromExcel(String filePath) {
        List<Store> list = new LinkedList<Store>();
        Workbook workbook = getWorkbook(filePath);
        if (workbook == null) {
            return null;
        }
        Sheet sheet = workbook.getSheetAt(0);
        int rowLength = sheet.getLastRowNum()+1;
        Row row = sheet.getRow(0);
        int colLength = row.getLastCellNum();
        //Cell hssfCell = row.getCell(0);
        //CellStyle cellStyle = hssfCell.getCellStyle();
        boolean breakFlag = false;
        for (int i = 0; i < rowLength && !breakFlag; i++) {
            //获取Excel工作表的行
            Row rowi = sheet.getRow(i);
            for (int j = 0; j < colLength && !breakFlag; j++) {
                //获取指定单元格
                Cell cell = rowi.getCell(j);
                //获取每一列中的值
                if (cell != null);// System.out.print(cell.toString() + "\t");
                else continue;
                
                if (isStoreField(cell.toString())) {
                    Map<String, Integer> map = new HashMap<String, Integer>();
                    for (int index = j; index < colLength; index++) {
                        map.put(rowi.getCell(index).toString(), index);
                    }
                    for (int rowIndex = i + 1; rowIndex < rowLength; rowIndex++) {
                        Row row2 = sheet.getRow(rowIndex);
                        Store store = new Store();
                        store.setName(row2.getCell(map.get("店名")).toString());
                        store.setStoreKeeper(row2.getCell(map.get("店主")).toString());
                        store.setAddress(row2.getCell(map.get("地址")).toString());
                        store.setTelephone(row2.getCell(map.get("电话")).toString());
                        list.add(store);
                    }
                    breakFlag = true;
                }
            }
            System.out.println(list.size());
        }
        return list;
    }

    private static boolean isStoreField(String fieldName) {
        if (fieldName.equals("店名") || fieldName.equals("店主") || fieldName.equals("电话")
                || fieldName.equals("地址")) {
            return true;
        } else {
            return false;
        }
    }
    
    public static boolean writeExcelFile(String filePath, List<Store> list) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("0");
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.createRow(i);
            row.createCell(0).setCellValue(list.get(i).getName());
            row.createCell(1).setCellValue(list.get(i).getStoreKeeper());
            row.createCell(2).setCellValue(list.get(i).getTelephone());
            row.createCell(3).setCellValue(list.get(i).getAddress());
        }
        return saveWorkbook(filePath, workbook);
    }
}

测试

package com.jieao.POITest;

import java.util.List;


public class Main {
    public static void main(String[] args) {
        List<Store> stores = ExcelUtils.readStoreListFromExcel("D:\\excel.xlsx");
        for (Store store : stores) {
            System.out.println(store.getName() + " " + store.getStoreKeeper() + " "
                    + store.getTelephone() + " " + store.getAddress());
        }
        ExcelUtils.writeExcelFile("D:\\writeexcel.xlsx", stores);
    }
}

excel.xlsx

Java 使用POI读写Excel文件

writeexcel.xlsx

Java 使用POI读写Excel文件