POIUtil Excel导入 POI 3.7-1

package com.hxzy.xazb.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
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.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**

• Created by Sunshine on 2020/8/11.
  */
  public class POIUtil {
  // 扩展名
  public final static String XLS = “xls”;
  public final static String XLSX = “xlsx”;

  /**

  • • 读取excel文件

  • @param excelFile excel文件

  • @param startRow 读取数据的起始行, 行号从0开始

  • @return

  • @throws IOException
    */
    public static List<List> readExcelFile(MultipartFile excelFile) throws Exception {
    // 检查文件
    String fileName = checkFile(excelFile);

    List<List> lists = null;

    if(fileName.endsWith(XLSX)){
    lists = readXlsx(excelFile.getInputStream());
    }else if(fileName.endsWith(XLS)){
    lists = readXls(excelFile.getInputStream());
    }
    return lists;
    }

  /**

  • 读取xlsx文档列
  • /
    private static String getXSSFCellValue(XSSFCell cell)throws Exception
    {
    String cellvalue = “”;
    if (cell!=null) {
    switch (cell.getCellType()) {
    case XSSFCell.CELL_TYPE_BOOLEAN:
    cellvalue = String.valueOf(cell.getBooleanCellValue());
    break;
    case XSSFCell.CELL_TYPE_NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
    Date date = cell.getDateCellValue();
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);//注意月份是MM
    cellvalue = simpleDateFormat.format(date);
    }else{
    cellvalue = String.valueOf(cell.getNumericCellValue());
    }
    break;
    case XSSFCell.CELL_TYPE_STRING:
    cellvalue = cell.getStringCellValue();
    break;
    case XSSFCell.CELL_TYPE_BLANK:
    break;
    case XSSFCell.CELL_TYPE_ERROR:
    break;
    case XSSFCell.CELL_TYPE_FORMULA:
    break;
    }
    }
    return cellvalue;
    }
    /*
  • 读取xls文档列
  • */
    private static String getHSSFCellValue(HSSFCell cell)throws Exception
    {
    String cellvalue = “”;
    if(cell!=null){
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BOOLEAN:
    cellvalue = String.valueOf(cell.getBooleanCellValue());
    break;
    case HSSFCell.CELL_TYPE_NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
    Date date = cell.getDateCellValue();
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);//注意月份是MM
    cellvalue = simpleDateFormat.format(date);
    }else{
    cellvalue = String.valueOf(cell.getNumericCellValue());
    }
    break;
    case HSSFCell.CELL_TYPE_STRING:
    cellvalue = cell.getStringCellValue();
    break;
    case HSSFCell.CELL_TYPE_BLANK:
    break;
    case HSSFCell.CELL_TYPE_ERROR:
    break;
    case HSSFCell.CELL_TYPE_FORMULA:
    break;
    }
    }
    return cellvalue;
    }

  /**

  • xlsx文档

  • @return 错误提示类型 0-正常 1-存在不能为空的列 2-格式不对 3-空文件

  • */
    private static List<List> readXlsx(InputStream inputstream) throws Exception {
    XSSFWorkbook hssfworkbook = new XSSFWorkbook(inputstream);
    XSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
    List<List> list = new ArrayList<>();
    if (hssfsheet != null) {
    int totalrows = hssfsheet.getPhysicalNumberOfRows();// --获取sheet总行数
    if (totalrows > 1)// 除了标题外，必须有数据
    {
    for (int i = 1; i < totalrows; i++) {
    List rowList = new ArrayList<>();
    XSSFRow hssfrow = hssfsheet.getRow(i);
    // 获取每一行的总列数
    int cellNum = hssfrow.getPhysicalNumberOfCells();

             if (cellNum==0){
                 continue;
             }
             for(int j = 1; j < cellNum; j++){
                 XSSFCell cell = hssfrow.getCell(j);
                 String xssfCellValue = getXSSFCellValue(cell);
                 rowList.add(xssfCellValue);
             }
             list.add(rowList);
         }
         return list;
     }
     else{
         return null;
     }
    

    }
    return null;
    }

  /**

  • xls文档

  • @return 错误提示类型 0-正常 1-存在不能为空的列 2-格式不对 3-空文件

  • */
    private static List<List> readXls(InputStream inputstream) throws Exception {
    HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);
    HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
    List<List> list = new ArrayList<>();
    if (hssfsheet != null) {
    int totalrows = hssfsheet.getPhysicalNumberOfRows();// --获取sheet总行数
    if (totalrows > 1)// 除了标题外，必须有数据
    {
    for (int i = 1; i < totalrows; i++) {
    List rowList = new ArrayList<>();
    HSSFRow hssfrow = hssfsheet.getRow(i);
    // 获取每一行的总列数
    int cellNum = hssfrow.getPhysicalNumberOfCells();

             if (cellNum==0){
                 continue;
             }
             for(int j = 1; j < cellNum; j++){
                 HSSFCell cell = hssfrow.getCell(j);
                 String xssfCellValue = getHSSFCellValue(cell);
                 rowList.add(xssfCellValue);
             }
             list.add(rowList);
         }
         return list;
     }else{
         return null;
     }
    

    }
    return null;
    }

  /**

  • 获得工作簿对象
  • @param excelFile excel文件
  • @return 工作簿对象
    */
    public static Workbook getWorkBook(MultipartFile excelFile) {
    // 获得文件名
    String fileName = excelFile.getOriginalFilename();
    // 创建Workbook工作簿对象，表示整个excel
    Workbook workbook = null;
    try {
    // 获得excel文件的io流
    InputStream is = excelFile.getInputStream();
    // 根据文件后缀名不同(xls和xlsx)获得不同的workbook实现类对象
    if (fileName.endsWith(XLS)) {
    // 2003版本
    workbook = new HSSFWorkbook(is);
    } else if (fileName.endsWith(XLSX)) {
    // 2007版本
    workbook = new XSSFWorkbook(is);
    }
    } catch (IOException e) {
    e.printStackTrace();
    }
    return workbook;
    }

  /**

  • 检查文件
  • @param excelFile excel文件
  • @throws IOException
    */
    public static String checkFile(MultipartFile excelFile) throws IOException {
    //判断文件是否存在
    if (null == excelFile) {
    throw new FileNotFoundException(“文件不存在”);
    }
    //获得文件名
    String fileName = excelFile.getOriginalFilename();
    //判断文件是否是excel文件
    if (!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
    throw new IOException(fileName + “不是excel文件”);
    }
    return fileName;
    }
    }

遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/youjianbo_han_87/article/details/7393916