springmvc 百万级别数据导入 sax方式

 1.实现excel解析类 

import com.xakysoft.base.businessLog.FadException;
import com.xakysoft.creditRating.sheetHandler.ExcelParse;
import com.xakysoft.creditRating.sheetHandler.ISheetHandler;
import com.xakysoft.sys.entity.SysUser;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.springframework.web.multipart.MultipartFile;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ExcelUtils {

    public static String excelParse(ISheetHandler sheetHandler, MultipartFile file, HttpSession session) throws Exception {
        SysUser user = (SysUser) session.getAttribute("user");
        sheetHandler.setUser(user);
        ExcelParse excelParse = null;
        try {
            excelParse = new ExcelParse();
            try {
                parse(sheetHandler, file.getInputStream());
            } catch (FadException e) {
                e.printStackTrace();
                return e.getMessage();
            }
        } catch (IOException e) {
            e.printStackTrace();
            return "导入失败!!!";
        }
        return "导入成功!!!";
    }
    // 参数说明 自定义excel实现类 和 excel文件流
    public static void parse (ISheetHandler hl, InputStream in) throws Exception {
        //解析器
        //SheetHandler hl = new SheetHandler("0");
        //1.根据 Excel 获取 OPCPackage 对象
        //OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);
        //InputStream inputStream = new ByteArrayInputStream(spreadsheetData);
        DataFormatter formatter = new CustomDataFormatter();
        OPCPackage pkg = OPCPackage.open(in);
        try {
            //2.创建 XSSFReader 对象
            XSSFReader reader = new XSSFReader(pkg);
            //3.获取 SharedStringsTable 对象
            ReadOnlySharedStringsTable sst = new ReadOnlySharedStringsTable(pkg,false);
            //4.获取 StylesTable 对象
            StylesTable styles = reader.getStylesTable();
            XMLReader parser = XMLReaderFactory.createXMLReader();
            // 处理公共属性
            parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, hl,formatter,
                    false));
            XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)
                    reader.getSheetsData();

            //逐行读取逐行解析
            while (sheets.hasNext()) {
                InputStream sheetstream = sheets.next();
                InputSource sheetSource = new InputSource(sheetstream);
                try {
                    parser.parse(sheetSource);
                } finally {
                    sheetstream.close();
                }
            }
        } finally {
            pkg.close();
        }
    }
    
    //日期格式化问题
    private static class CustomDataFormatter extends DataFormatter {
        @Override
        public String formatRawCellContents(double value, int formatIndex, String formatString,
                                            boolean use1904Windowing) {
            // Is it a date?
            if (DateUtil.isADateFormat(formatIndex, formatString)) {
                if (DateUtil.isValidExcelDate(value)) {
                    Date d = DateUtil.getJavaDate(value, use1904Windowing);
                    try {
                        return new SimpleDateFormat("yyyy-MM-dd").format(d);
                    } catch (Exception e) {
                        //logger.log(Level.SEVERE, "Bad date value in Excel: " + d, e);
                        throw new FadException(BizExceptionEnum.DATE_FORMAT_ERROR);
                    }
                }
            }
            return new DecimalFormat("##0.#####").format(value);
        }
    }
}

 2.定义excel内容处理接口

package com.xakysoft.creditRating.sheetHandler;

import com.xakysoft.sys.entity.SysUser;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;

public interface ISheetHandler  extends  XSSFSheetXMLHandler.SheetContentsHandler{

     String covertDateFormat(String cellValue);
     void setUser(SysUser user);
     void isEmpty(String cellValue,String msg);
}

 3.为不同的导入excel模板实现不同的实现类

package com.xakysoft.creditRating.sheetHandler.impl;

import com.xakysoft.base.businessLog.BizExceptionEnum;
import com.xakysoft.base.businessLog.FadException;
import com.xakysoft.base.util.StringUtils;
import com.xakysoft.creditRating.entity.CompanyBackPay;
import com.xakysoft.creditRating.service.ILaborService;
import com.xakysoft.creditRating.sheetHandler.ISheetHandler;
import com.xakysoft.recordManager.service.IComInfoSubService;
import com.xakysoft.sys.entity.SysUser;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.util.HashMap;
import java.util.Map;

@Component
public class CompanyBackPaySheetHandler implements ISheetHandler {

    @Autowired
    private ILaborService laborService;
    @Autowired
    private IComInfoSubService comInfoSubService;
    private CompanyBackPay companyBackPay = null;
    private SysUser user = null;
    private Integer index = 0;

    private static CompanyBackPaySheetHandler serverHandler;

    @PostConstruct //通过@PostConstruct实现初始化bean之前进行的操作
    public void init() {
        serverHandler = this;
        serverHandler.laborService = this.laborService;
        serverHandler.comInfoSubService = this.comInfoSubService;
        // 初使化时将已静态化的testService实例化
    }

    @Override
    public String covertDateFormat(String cellValue) {
        return null;
    }

    @Override
    public void setUser(SysUser user) {
        this.user = user;
    }

    @Override
    public void isEmpty(String cellValue, String msg) {
        if(StringUtils.isEmpty(cellValue)) {
            throw new FadException(400,msg);
        }
    }
    //行开始处理
    @Override
    public void startRow(int rowNum) {
        if(rowNum == 0) {
            companyBackPay = null;
        } else {
            companyBackPay = new CompanyBackPay();
        }
    }
    //行结束处理
    @Override
    public void endRow(int rowNum) {
        if(rowNum == 0) {
            if(index!=5) {
                throw new FadException(BizExceptionEnum.TEMPLATE_ERROR);
            }
        } else {
            if (user != null) {
                Map resultMap = serverHandler.comInfoSubService.selectCompanySubBySoc(new HashMap<String, Object>(){{put("socialCreditCode",companyBackPay.getSocialCreditCode());}});
                if(resultMap!= null && StringUtils.isNotEmptyObject(resultMap.get("id"))) {
                    companyBackPay.setCompanyId(Long.valueOf(resultMap.get("id").toString()));
                }
                serverHandler.laborService.saveCompanyBackPay(companyBackPay, user);
            }
        }
    }
    //处理一行单元格
    @Override
    public void cell(String cellName, String cellValue, XSSFComment comment) {
        if(companyBackPay == null) {
            index++;
        }
        if (companyBackPay != null) {
            String letter = cellName.substring(0, 1); //每个单元格的首字母
            switch (letter) {
                case "A": {
                    companyBackPay.setSocialCreditCode(StringUtils.isNotEmptyObject(cellValue) ? cellValue.trim() : "");
                    break;
                }
                case "B": {
                    companyBackPay.setCompanyName(cellValue);
                    break;
                }
                case "C": {
                    companyBackPay.setPunishmentReason(cellValue);
                    break;
                }
                case "D": {
                    companyBackPay.setPunishmentTime(cellValue);
                    break;
                }
                case "E": {
                    companyBackPay.setPunishmentDepartment(cellValue);
                    break;
                }
            }
        }
    }

    @Override
    public void headerFooter(String text, boolean isHeader, String tagName) {

    }
}

4.service调用

 public String saveCompanyBackPayExcelIn(MultipartFile myfile, HttpServletRequest request, HttpServletResponse response, HttpSession session) throws Exception {
        CompanyBackPaySheetHandler sheetHandler = new CompanyBackPaySheetHandler();
        return ExcelUtils.excelParse(sheetHandler,myfile,session);
    }

5.controller调用

   @RequestMapping(value="/saveCompanyBackPayExcelIn",produces = "text/html; charset=utf-8")
    @ResponseBody
    public String saveCompanyBackPayExcelIn(HttpServletRequest request, HttpServletResponse response, @RequestParam MultipartFile myfile) throws Exception {
        return laborService.saveCompanyBackPayExcelIn(myfile, request, response,request.getSession());
    }