poi读取Excel

需要根据excel文档来动态上传相关信息，今天整合下了poi读取excel。

package com.seestech.sell;

import com.alibaba.fastjson.JSON;
import com.seestech.sell.domain.model.Goods;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.text.NumberFormat;

/**
 * Created by 神烦 on 2019/9/18.
 */
public class ExcelTest extends AppTest{
    private static Logger logger = LoggerFactory.getLogger(ExcelTest.class);

    @Test
    public void readGoodInfoByExcel() throws InvalidFormatException,IOException{
        // 获取文件
        File file = new File("D://demo.xlsx");
        //获取工作簿
        XSSFWorkbook wb = new XSSFWorkbook(file); // XSSFWorkbook支持2007格式
        //获取excel表sheet
        XSSFSheet sheet = wb.getSheet("sheet1");
        if (sheet == null) {
            logger.info("工作簿sheet1为空");
            return;
        }
        // 遍历sheet所有行
        for (int row = 0; row <= 9; row++) {
            XSSFRow row1 = sheet.getRow(row); // 获取行
            if (row1 == null) {
                continue;
            }
            Goods goods = new Goods();
            //遍历所有列
            for (int cell=0; cell<= row1.getLastCellNum(); cell++){
                XSSFCell cell1 = row1.getCell(cell);
                if (cell1 == null) {
                    continue;
                }
                String strVal = readCellSecondMethod(cell1);
                logger.info(strVal);
            }

        }
    }

    /**
     * 列获取值操作
     * @param cell
     * @return
     */
    public static String readCellSecondMethod(XSSFCell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            //数字
            case XSSFCell.CELL_TYPE_NUMERIC:
                NumberFormat nf = NumberFormat.getInstance(); //数字默认的是返回double类型，所以读取时会不一致
                nf.setGroupingUsed(false);
                return nf.format(cell.getNumericCellValue());
            //字符串
            case XSSFCell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            //公式
            case XSSFCell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            //空白
            case XSSFCell.CELL_TYPE_BLANK:
                return "";
            //布尔取值
            case XSSFCell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() + "";
            //错误类型
            case XSSFCell.CELL_TYPE_ERROR:
                return cell.getErrorCellValue() + "";
        }

        return "";
    }


}

测试结果:

2019-09-18 19:43:48.603 [main] INFO  com.seestech.sell.ExcelTest.[readGoodInfoByExcel:79] => goodsId 
2019-09-18 19:43:48.603 [main] INFO  com.seestech.sell.ExcelTest.[readGoodInfoByExcel:79] => code 
2019-09-18 19:43:48.604 [main] INFO  com.seestech.sell.ExcelTest.[readGoodInfoByExcel:79] => 999999999 
2019-09-18 19:43:48.604 [main] INFO  com.seestech.sell.ExcelTest.[readGoodInfoByExcel:79] => 8888888 
2019-09-18 19:43:48.604 [main] INFO  com.seestech.sell.ExcelTest.[readGoodInfoByExcel:79] => 6666666666 
2019-09-18 19:43:48.604 [main] INFO  com.seestech.sell.ExcelTest.[readGoodInfoByExcel:79] => 7777777 

这里需要注意的是多种Workbook所支持版本不同的问题，多测试即可。上面代码可以直接使用，如果那里错误或者不足的地方欢迎指正。