一.上传10m以内的excel2007文件进行处理。

1.前端使用vue 封装data信息。

    <div class="col-sm-2 control-label" >上传月使用量表格(仅支持excel)</div>
    <div class="col-sm-10">
        <input id="usageUPload" type="file" class="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" @change="pushUsageExcel()"/>
        <button v-on:click="uploadUsageExcel">上传文件</button>
    </div>

var vm = new Vue({
        el: '#app',
        data: {
            formData : null,
            importInfo:{
                //上传的Excel
                fileType : "Excel",
                uploadFileName : "",
                base64Str : ""
            }
        },
        methods:{
uploadUsageExcel : function() {
                //这里主要做一些数据的判断，或者其他参数的一个绑定。
                if( "" === vm.importUsageInfo.base64Str || null == vm.importUsageInfo.base64Str){
                    alert("请上传表格1！");
                    // layer.msg("请上传表格！")
                    return;
                }
                $.ajax({
                    type: "POST",
                    url: "api/uploadUsedFlowData",
                    contentType: "application/json",
                    data: JSON.stringify(vm.importUsageInfo),
                    success: function (a) {
                        alert("上传成功！");
                        vm.showList = true;
                        vm.showImport = false;
                        location.reload();
                    }
                })
                // window.alert("上传文件警告框！");
            },

普通小文件前端使用了 vue 带  ajax Post  将文件转码  base64Str 字符串的方式来传送文件到后端服务器上。

2.后端处理。

    @RequestMapping("api/uploadUsedFlowMathFile")
    public List<CardPooled12MonthUsage>uploadUsedFlowMathFile(@RequestBody ExcelUploadMeta excelBase64) throws Exception
    {
        return upLoadService.receiveUpload(excelBase64.getBase64Str());
    }

service类：

@Service
@Slf4j
public class UploadUsedFlowDataService extends BaseService {

    @Autowired
    CardPooledMonthlyUsageMapper monthlyUsageMapper;

    public void receiveUpload(String excelBase64) throws Exception {
        log.warn("文件开始接收!");
        Workbook wb = Base64Util.getWorkbookByBase64(Base64Util.trimHeadFromFront(excelBase64));
        log.warn("文件接受完成！");
        execute(wb,factoryConfig.sqlSession());
    }

    /**
     * 执行插入或者更新数据
     *
     * @param wb
     * @param factory
     */
    public void execute(Workbook wb, SqlSessionFactory factory) throws InterruptedException {
        //先读取所有excel记录过滤掉流量为0 的记录
        //然后多线程查询所有记录判断是否有已经存在当月流量记录
        //把源数据分开保存为两个list
        //没有的执行批量插入
        //存在的执行批量更新。
}

Base64Util 类 ：

        public static String encode(byte[] srcString) {
        return Base64.encodeBase64String(srcString);
    }

    public static byte[] decode(String srcString) {
        return Base64.decodeBase64(srcString);
    }

    public static Workbook getWorkbookByBase64(String base64Code) {
        byte[] buffer = decode(base64Code);
        InputStream input = new ByteArrayInputStream(buffer);
        Workbook workbook = null;

        try {
            workbook = WorkbookFactory.create(input);
        } catch (Exception var5) {
            var5.printStackTrace();
        }

        return workbook;
    }

    public static void main(String... strings) throws Exception {
    }

这样文件就传到服务器后端 进行业务逻辑处理了。

二. 大批量的excel数据上传处理。

前面提供了一种 excel 文件上传服务器后端处理的防范。但是指适用于10m以内的文件使用。如果excel数据量超过了10m甚至更大那么 就会报错如下：

 这是因为 的 HSS XSS类 只能支持 10000000 长度的字符串处理，那么这里我们就要换一种poi的处理方式。以XSSFEvent 的方式来处理了

pom依赖:

        <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.11.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version>
        </dependency>

前端使用vue 绑定data信息，post

    <div>
        <input type="file" @change="getFile($event)">
        <input type="button" value="上传" @click="pushBigUsage()">
    </div>

        data: {
            formData : null
        }


            pushBigUsage() {
                $.ajax({
                    type: "POST",
                    url: "api/uploadBigUsedFlow",
                    processData: false ,    // 不处理数据
                    contentType: false,    // 不设置内容类型
                    data: vm.formData,
                    success: function (a) {
                        alert("上传成功！");
                        vm.showList = true;
                        vm.showImport = false;
                        location.reload();
                    }
                })
            },
            getFile(event) {
                let file = event.target.files[0];
                let fileName = file.name;
                let index = fileName.lastIndexOf(".");
                let fileType = ['png','jpeg','jpg','jif','doc','pdf','xls','xlsx']
                if (index != -1) {
                    let suffix = fileName.substr(index + 1).toLowerCase();
                    if (fileType.includes(suffix)) {
                        vm.formData = new FormData();
                        vm.formData.append("file",file);
                    }else {
                        this.$message.error("文件格式错误！请选择'png','jpeg','jpg','jif','doc','pdf','xls','xlsx'格式的文件")
                    }
                }
            }

后端处理：

    @RequestMapping("api/uploadBigUsedFlow")
    public ResponseEntity uploadBidUsedFlow(MultipartFile file) throws Exception {
        uploadBigUsedFlowService.receiveUpload(file);
        return ResponseEntity.ok("上传成功!");
    }

service 类：

@Service
@Slf4j
public class UploadBigUsedFlowService  extends BaseService {
    @Autowired
    CardPooledMonthlyUsageMapper monthlyUsageMapper;
    @Value("${uploadFilePath}")
    String uploadFilePath;

    /**
     * receiveUpload
     * @param file 文件流
     * @throws Exception 例外
     */
    public void receiveUpload(MultipartFile file) throws Exception {
        log.warn("文件开始接收保存数据!");
        String format = DateUtil.getDateStr(2);
        String realPath =  uploadFilePath + "/upload" + format;
        String  filePath;
        filePath = FileUtil.saveFileWithUUID(realPath,file);
        log.warn("文件接受完成！,将数据从文件中读入list缓存中");
        XSSFEventUtil ssf = new XSSFEventUtil();
        ssf.processAllSheets(filePath);
        List<CardPooledMonthlyUsage> sourceList = ssf.getMonthlyUsageList();
        execute(sourceList,factoryConfig.sqlSession());
    }

    /**
     * execute
     * @param sourceList 使用量列表
     * @param factory session工厂
     */
    public void execute( List<CardPooledMonthlyUsage>sourceList ,  SqlSessionFactory factory) throws InterruptedException {
        //判断是否用当月流量记录并分配到不同记录中
        log.warn("开始分配卡的历史流量..");
        int saizePerList= sourceList.size()/50;
        if(saizePerList==0){saizePerList=1;}
        List<List<CardPooledMonthlyUsage>> iccidListsplit = ListUtil.splitListByPerListSize(sourceList, saizePerList);
        int threadNum=iccidListsplit.size();
        RunCallerUploadUsedFlowDataService runCaller = new RunCallerUploadUsedFlowDataService();
        runCaller.call(threadNum, new Object[]{iccidListsplit,factory});
        log.warn("完成更新卡的历史流量...");
    }

}

XSSFEventUtil  类

@Slf4j
public class XSSFEventUtil {

    private List<CardPooledMonthlyUsage> monthlyUsageList;

    public void processOneSheet(String filename) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader( pkg );
        SharedStringsTable sst = (SharedStringsTable) r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);
        // To look up the Sheet Name / Sheet Order / rID,
        //  you need to process the core Workbook stream.
        // Normally it's of the form rId# or rSheet#
        InputStream sheet2 = r.getSheet("rId2");
        InputSource sheetSource = new InputSource(sheet2);
        parser.parse(sheetSource);
        sheet2.close();
    }
    public void processAllSheets(String filename) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader( pkg );
        SharedStringsTable sst = (SharedStringsTable) r.getSharedStringsTable();
        ContentHandler handler = new XSSFEventUtil.SheetHandler(sst);
        XMLReader parser = XMLHelper.newXMLReader();
        parser.setContentHandler(handler);
        Iterator<InputStream> sheets = r.getSheetsData();
        while(sheets.hasNext()) {
            System.out.println("Processing new sheet:\n");
            InputStream sheet = sheets.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
            System.out.println("*********************");
        }
        XSSFEventUtil.SheetHandler sheetHandler=(XSSFEventUtil.SheetHandler)handler;
        monthlyUsageList = sheetHandler.getMonthlyUsageList();
        log.warn("monthlyUsageListSize{}" , monthlyUsageList.get(0).getIccid());

    }
    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException, ParserConfigurationException {
        XMLReader parser = XMLHelper.newXMLReader();
        ContentHandler handler = new XSSFEventUtil.SheetHandler(sst);
        parser.setContentHandler(handler);
        return parser;
    }

    public List<CardPooledMonthlyUsage> getMonthlyUsageList(){
        return monthlyUsageList;
    }

    /**
     * See org.xml.sax.helpers.DefaultHandler javadocs
     */
    private static class SheetHandler extends DefaultHandler {
        private SharedStringsTable sst;
        private String lastContents;
        private boolean nextIsString;
        private long cellIndex=0;
        private SheetHandler(SharedStringsTable sst) {
            this.sst = sst;
        }
        private CardPooledMonthlyUsage monthlyUsage = new CardPooledMonthlyUsage();
        private List<CardPooledMonthlyUsage> monthlyUsageList = new ArrayList<>();

        @Override
        public void startElement(String uri, String localName, String name,
                                 Attributes attributes) throws SAXException {
            // c => cell
            if(name.equals("c")) {
                System.out.print(attributes.getValue("r") + " - ");
                String cellType = attributes.getValue("t");
                if(cellType != null && cellType.equals("s")) {
                    nextIsString = true;
                } else {
                    nextIsString = false;
                }
            }
            lastContents = "";
        }
        @Override
        public void endElement(String uri, String localName, String name)
                throws SAXException {
            if(nextIsString) {
                int idx = Integer.parseInt(lastContents);
                lastContents = sst.getItemAt(idx).getString();
                nextIsString = false;
            }
            //根据序列把数据存入List<CardPooledMonthlyUsage> 中
            if(name.equals("v")) {
                if(cellIndex >4) {
                    int a = (int)(cellIndex % 5);
                    System.out.println("a" + a);
                    switch (a) {
                        case 0:
                            //TODO 保存字段usageMonth
                            monthlyUsage.setUsageMonth(lastContents);
                            System.out.println("保存字段usageMonth" + lastContents);
                            break;
                        case 1:
                            //TODO 用不到的字段
                            break;
                        case 2:
                            //TODO 保存字段iccid
                            monthlyUsage.setIccid(lastContents);
                            System.out.println("保存字段iccid" + lastContents);
                            break;
                        case 3:
                            //TODO 保存字段usedFlow
                            monthlyUsage.setUsedFlow(Double.parseDouble(lastContents));
                            System.out.println("保存字段usedFlow" + lastContents);
                            break;
                        case 4:
                            //TODO 保存字段descCnt,monthUsage加入list中,刷新 monthlyUsage
                            monthlyUsage.setDescCnt(lastContents);
                            System.out.println("保存字段descCnt" + lastContents);
                            if(monthlyUsage.getUsedFlow()>0)
                            {monthlyUsageList.add(monthlyUsage);}
                            monthlyUsage = new CardPooledMonthlyUsage();
                            break;
                        default:
                            break;
                    }
                }
                cellIndex ++;
            }

        }
        @Override
        public void characters(char[] ch, int start, int length) {
            lastContents += new String(ch, start, length);
        }

        public List<CardPooledMonthlyUsage> getMonthlyUsageList(){
            return monthlyUsageList;
        }


    }

    public static void main(String[] args) throws Exception {
        XSSFEventUtil example = new XSSFEventUtil();
//        example.processOneSheet("D:\\uploadFile\\upload220722\\76994759-cfee-43c8-9625-c4542e4c288b.xlsx");
        example.processAllSheets("D:\\uploadFile\\upload220722\\76994759-cfee-43c8-9625-c4542e4c288b.xlsx");

    }
}

这里是将 excel 格式文件转换层xml 来处理 对于有兴趣了解 xlsx 文件格式的朋友可以深入了解一下期原理。参考资料：The New Halloween Document (apache.org)

spring.servlet.multipart.max-request-size=100MB
spring.servlet.multipart.max-file-size=200MB

spring 配置文件里需要把 servlet 上传文件最大值 设置一下。不然无法上传大文件。

 

 

这可是 一次处理46万条记录哟