JavaNote 3.6 POI处理Excel文件

一、Java代码

package com.test;

import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;

public class RedXls {
    private static Logger logger = Logger.getLogger(RedXls.class);

    public static void main(String args[]) {
        //加载log4j配置文件
        PropertyConfigurator.configure("D:\\workspace\\AutoTestInterface\\src\\main\\java\\com\\conf\\log4jproperties");
        File file = new File("D://test.xlsx");
        RedXls readXl = new RedXls();
        readXl.readXls(file);
    }

    public void readXls(File file) {
        //判断文件名和文件后缀
        if (file.getName().toString().equals("test")) {
            if (file.getName().endsWith(".xls")) {
                logger.error("请确认文件名并使用2007版本excel");
                return;
            }
        }
        //取第一列单元格为key，第二列为value存储在map中
        Map<String, String> map = new HashMap();
        FileInputStream fileInputStream;
        XSSFWorkbook workbook = null;
        try {
            fileInputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(fileInputStream);
        } catch (FileNotFoundException e) {
            logger.error("找不到文件");
        } catch (IOException e) {
            logger.error("IO错误");
        }
        //获取工作簿的sheet
        XSSFSheet hssfSheet = workbook.getSheetAt(0);
        //iterator循环获取row的值
        Iterator iterator = hssfSheet.rowIterator();
        int rownum = 0;
        while (iterator.hasNext()) {
            if (iterator.next() != null) {
                rownum++;
            }
        }
        //iterator循环获取cell的值
        int cellnum = 0;
        Iterator iterator1 = hssfSheet.getRow(0).cellIterator();
        while (iterator1.hasNext()) {
            if (iterator1.next() != null) {
                cellnum++;
            }
        }
        //对中间存在空行的情况进行处理，如果存在一个空行则rownum加1
        for (int i = 0; i < rownum; i++) {
            if (hssfSheet.getRow(i) == null && i < hssfSheet.getLastRowNum()) {
                rownum += 1;
                logger.info("第" + (i + 1) + "行为空");
                continue;
            }
            //定义两个String类型变量用于存储map的键值对
            String parm1 = null;
            String parm2 = null;
            for (int j = 0; j < cellnum; j++) {
                try {
                    //对单元格的CellType进行变更
                    hssfSheet.getRow(i).getCell(j).setCellType(CellType.STRING);
                    if (j == 0) {
                                /*把第一列数据存储在parm1中，第二列存储在parm2中
                                这里要对单元格为空的情况进行空指针异常捕获
                                 */
                        parm1 = hssfSheet.getRow(i).getCell(j).getStringCellValue();
                    } else {
                        parm2 = hssfSheet.getRow(i).getCell(j).getStringCellValue();
                    }
                } catch (NullPointerException e) {
                    logger.error("第" + (i + 1) + "行第" + (j + 1) + "个单元格为空");
                    continue;
                }
            }
            //对键值对为null的进行处理
            if (parm1 != null && parm2 != null) {
                map.put(parm1, parm2);
            }
        }
        // 循环map集合
        for (Map.Entry entry : map.entrySet()) {
            System.out.println("key= " + entry.getKey() + " and value= " + entry.getValue());
        }
    }
}

二、结果展示