Excel 上传解析

pom.xml

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

package com.sceo.erp.modules.activiti_model.service.impl;
import com.sceo.erp.modules.common.util.Result;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class excelTool {
    public Result getBankListByExcel(MultipartFile file){

        try {
            InputStream inputStream = file.getInputStream();
            long size = file.getSize();
            if(size >1024 *1024) {
                return Result.ERROR_DATA("当前文件大小为" + Math.ceil(size / 1024 / 1024 / 10 / 100) + "M,超过1M",
                        "当前文件大小为" + Math.ceil(size * 100 / 1024 / 1024 / 10 / 100) + "M,超过1M");
            }
            //获得Excel文件名
            String originalFilename = file.getOriginalFilename();
            // 创建excel工作簿
            Workbook work =getWorkbook(inputStream, originalFilename);
            //存放Eecel
            List list =new ArrayList();
            Sheet sheet = null;
            Row row = null;
            Cell cell = null;
            //遍历每张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 || row.getFirstCellNum() == j) {
                        continue;
                    }
                    List<Object> li = new ArrayList<Object>();
                    //遍历每列
                    for (int y=row.getFirstCellNum();y<row.getLastCellNum();y++){
                        String value="0";
                        cell=row.getCell(y);
                        if(cell==null){
                            value="0";
                            li.add(value);
                        } else{
                            switch (cell.getCellType()) {
                                //数字
                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    //如果未时间格式的内容
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        //DateFormat format = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
                                        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                                        value = simpleDateFormat.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                                        //    Date parse = format.parse(value);
                                        li.add(value);
                                        break;
                                    } else {
                                        value = String.valueOf(cell.getNumericCellValue());
                                        li.add(value);
                                        break;
                                    }
                                    // 字符串
                                case HSSFCell.CELL_TYPE_STRING:
                                    value = cell.getStringCellValue();
                                    li.add(value);
                                    break;
                                // Boolean
                                case HSSFCell.CELL_TYPE_BOOLEAN:
                                    value = cell.getBooleanCellValue() + "";
                                    li.add(value);
                                    break;
                                // 空值
                                case HSSFCell.CELL_TYPE_BLANK:
                                    value = "";
                                    li.add(value);
                                    break;
                                // 故障
                                case HSSFCell.CELL_TYPE_ERROR:
                                    value = "非法字符";
                                    li.add(value);
                                    break;
                                // 公式
                                case HSSFCell.CELL_TYPE_FORMULA:
                                    value = cell.getCellFormula() + "";
                                    li.add(value);
                                    break;
                                default:
                                    value = "未知类型";
                                    li.add(value);
                                    break;
                            }
                        }
                    }
                    list.add(li);
                }
            }
            work.close();
            return  Result.OK_DATA(list);
        } catch (Exception e) {
            return Result.ERROR_DATA("Excel解析异常",e.getMessage());
        }
    }
    //判断文件格式
    public static Workbook getWorkbook(InputStream inputStream, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType=fileName.substring(fileName.lastIndexOf("."));
        if(".xls".equals(fileType)){
            workbook=new HSSFWorkbook(inputStream);
        }else if(".xlsx".equals(fileType)){
            workbook=new XSSFWorkbook(inputStream);
        }else {
            throw new Exception("请上传excel文件!");
        }
        return workbook;
    }

}