excel文件解析-java

compile 'org.apache.poi:poi:4.1.2'
compile 'org.apache.poi:poi-ooxml:4.1.2'

工具类

package cn.togeek.util;

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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;

/**
 * @author admin
 */
public class ExcelUtils {

   /**
    * @Description：获取IO流中的数据，组装成List<List<Object>>对象
    */
   public static List<List<Object>> getListByExcel(InputStream in) throws Exception {
      List<List<Object>> list;

      //创建Excel工作薄
      Workbook work = new XSSFWorkbook(in);
      Sheet sheet;  //页数
      Row row;  //行数
      Cell cell;  //列数

      list = new ArrayList<>();

      //遍历Excel中所有的sheet
      for(int i = 0; i < work.getNumberOfSheets(); i++) {
         sheet = work.getSheetAt(i);
         if(sheet == null) {
            continue;
         }

         //遍历当前sheet中的所有行
         for(int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if(row == null) {
               continue;
            }

            //遍历所有的列
            List<Object> li = new ArrayList<>();
            for(int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
               cell = row.getCell(y);
               li.add(getValue(cell));
            }
            list.add(li);
         }
      }

      return list;

   }

   /**
    * 对表格中数值进行格式化   解决excel类型问题，获得数值
    */
   public static String getValue(Cell cell) {
      String value = "";
      if(null == cell) {
         return value;
      }
      switch(cell.getCellType()) {
         //数值型
         case NUMERIC:
            if(DateUtil.isCellDateFormatted(cell)) {
               //如果是date类型则 ，获取该cell的date值
               Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
               SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
               value = format.format(date);
            }
            else {// 纯数字
               BigDecimal big = BigDecimal.valueOf(cell.getNumericCellValue());
               String s = "0";
               value = big.toString();
               //解决1234.0  去掉后面的.0
               if(null != value && !"".equals(value.trim())) {
                  String[] item = value.split("[.]");
                  if(1 < item.length && s.equals(item[1])) {
                     value = item[0];
                  }
               }
            }
            break;
         //字符串类型
         case STRING:
            value = cell.getStringCellValue();
            break;
         // 公式类型
         case FORMULA:
            //读公式计算值
            value = String.valueOf(cell.getNumericCellValue());
            String nan = "NaN";
            if(nan.equals(value)) {// 如果获取的数据值为非法值,则转换为获取字符串
               value = cell.getStringCellValue();
            }
            break;
         // 布尔类型
         case BOOLEAN:
            value = " " + cell.getBooleanCellValue();
            break;
         default:
            value = cell.getStringCellValue();
      }
      String ss = "null";
      if(ss.endsWith(value.trim())) {
         value = "";
      }
      return value;
   }
}

service类
public List<WorkImportantText> getExcelText(MultipartFile multipartFile) {
   List<WorkImportantText> workImportantTextList = new ArrayList<>();
   try {
      InputStream inputStream = multipartFile.getInputStream();

      List<List<Object>> list = ExcelUtils.getListByExcel(inputStream);

      //遍历除第一行以外的Excel表格中的值
      for(int i = 1; i < list.size(); i++) {

         //rows是某一行，i = 1 为第二行， i = 2 为第三行
         List<Object> rows = list.get(i);

         //数据库数据的实体类
         WorkImportantText questionBank = new WorkImportantText();

         //遍历这一行所有的值
         if(StringUtils.isNotBlank(rows.get(0).toString())) {
            questionBank.setWorkTask(rows.get(0).toString());
         }
         else if(StringUtils.isBlank(rows.get(0).toString()) && i >= 2) {
            questionBank.setWorkTask(list.get(i - 1).get(0).toString());
         }

         questionBank.setWorkText(rows.get(1).toString());
         questionBank.setKeyInitiatives(rows.get(2).toString());
         questionBank.setIntendedTarget(rows.get(3).toString());
         questionBank.setImportantProgress(rows.get(4).toString());
         questionBank.setResponsibleDepartment(rows.get(5).toString());
         questionBank.setImplementation(rows.get(6).toString());
         questionBank.setImportantId(UUIDUtil.getUUID());
         questionBank.setDeleteState("1");

         workImportantTextList.add(questionBank);
      }
   }
   catch(Exception ignored) {
   }
   return workImportantTextList;
}