POI 读取Excel 方法封装 开箱即用

781 阅读2分钟

之前自己写过一篇poi的excel文件的导入,但是大部分的列解析方法都已经废弃了,就又贴着头去看了下其他博客的写法,认识认识方法,然后自己写了一篇。

代码篇

pom

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

java

package com.seestech;

import com.seestech.exception.SeestechException;
import com.seestech.model.SysUser;
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 org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * Created by 神烦 on 2019/12/4.
 */
public class PoiReadTest extends AppTest {

    private static Logger logger = LoggerFactory.getLogger(PoiReadTest.class);

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    @Test
    public void readExcel() {

        ExcelReader.readExcel("path");
    }


    static class ExcelReader {

        static List<SysUser> readExcel(String fileName) {
            Workbook workbook = null;
            InputStream inputStream = null;

            //获取后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".")+1, fileName.length());
            File file = new File(fileName);
            if (!file.exists()) {
                logger.error("文件不存在");
                throw new SeestechException("文件不存在");
            }

            try {
                // 获取文件
                inputStream = new FileInputStream(file); //workbook = WorkbookFactory.create(file); 支持03 07 10
                workbook = getWorkbook(inputStream, fileType);
                return parseExcel(workbook);
            } catch (FileNotFoundException e) {
                logger.error("文件不存在");
                return null;
            } catch (IOException e) {
                logger.error("文件流异常");
                return null;
            } finally {
                try {
                    if (inputStream != null) {
                        inputStream.close();
                    }
                    if (workbook != null) {
                        workbook = null;
                    }
                } catch (Exception e) {
                    logger.error("流关闭异常");
                } finally {

                }
            }

        }

        //读取
        static Workbook getWorkbook(InputStream inputStream, String fileType)throws IOException {
            Workbook workbook = null;
            if (fileType.equalsIgnoreCase(XLS)){
                //03版本
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileType.equalsIgnoreCase(XLSX)) {
                //07版本
                workbook = new XSSFWorkbook(inputStream);
            }
            return workbook;
        }

        //解析
        static List<SysUser> parseExcel(Workbook workbook) {
            List<SysUser> list = new ArrayList<>();
            //解析
            for (int i = 0; i <workbook.getNumberOfSheets() ; i++) {

                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) {
                    continue;
                }
                //获取起始行数
                int firstNum = sheet.getFirstRowNum();
                //再获取行的数据
                Row row = sheet.getRow(firstNum);
                if (row == null) {
                    logger.warn("没有校验的行参");
                    throw new SeestechException("没有校验的行参");
                }

                //防止中间空行 需校验
                int startRowNum = firstNum+1;
                //int endRowNum = sheet.getLastRowNum(); 返回最后一行的索引?
                int endRowNum = sheet.getPhysicalNumberOfRows();

                for (int j = startRowNum; j < endRowNum; j++) {
                    row = sheet.getRow(j);
                    if (row == null) {
                        continue;
                    }

                    //获取到合法的行 则转换参数
                    SysUser user = convertRowToData(row);
                    list.add(user);
                }

            }
            return list;
        }


        static SysUser convertRowToData(Row row) {
            SysUser user = new SysUser();

            Cell cell;
            int cellNum = 0;

            cell = row.getCell(cellNum++); //1
            //cell可获取
            String sale = cell.getStringCellValue();
            user.setSalt(sale);

            //cell = row.getCell(cellNum++); //2

            return user;
        }
    }
}

可以拿来即用。

小提示

项目中的MultipartFile对象转换file用常规的强转方法总会有遗留文件,我也是刚刚找到新的没有遗留的方法。

FileUtils.copyInputStreamToFile(multipartFile.getInputStream, new File());