JAVA导入表格功能总结

秒速五厘米 2022-05-15 11:40 141阅读 0赞

**表格结构：**

![70][]

**SheetName：**sheet名称（辅材采购申请单）

**RowNum：**行数

**FirstCellNum/LastCellNum：**第一竖列/最后一竖列（从0开始），例：getCell(0) = 辅材编号

前台到后台上传操作：

**HTML：**

<div id="assetUploadDialog" class="pop" style="visibility: hidden;">
        <form id="assetUploadForm" method="post" enctype="multipart/form-data">
            <br/>
                  <input  type="file" name="assetUpload" />
            <font color="red">请按照模板导入辅材*</font>
            <br/>
        </form>
    </div>

**js：**

//上传文件
    function assetUploadFile(){
        MaskUtil.mask('正在导入，请等待...');
        var url= context_path + '/auxiliary/auxiliaryImport.do';
        $('#assetUploadForm').form('submit',{
            url:url,
            success:function(ret){
                var r =  JSON.parse(ret);
                $.messager.alert('消息',r.msg);
                if(r.result){
                    $('#assetUploadDialog').dialog('close');
                    loadDataGrid();//重新获取数据加载页面
                    MaskUtil.unmask();
                }else{
                    MaskUtil.unmask();
                }
            }
        });
    }

**Action：**

private File assetUpload; // 配置文件上传
    
     public File getAssetUpload() {
        return assetUpload;
     }
    
     public void setAssetUpload(File assetUpload) {
        this.assetUpload = assetUpload;
     }
    
     /**
         * 导入采购单
         */
        public void auxiliaryImport(){
    
            Map<String, Object> map = new HashMap<String, Object>();
            try {
                InputStream in = new FileInputStream(assetUpload);
                Workbook wb = new XSSFWorkbook(in);
                int auxiliaryIndex = 0;
                for(int i = 0; i < wb.getNumberOfSheets(); i++){
                    if(wb.getSheetName(i).equals("辅材采购申请单")){
                        auxiliaryIndex = i;
                    }
                }
                Sheet sheet1 = wb.getSheetAt(auxiliaryIndex);
                //1.声明sql数组
                String [] sqls =new String [sheet1.getLastRowNum()];
                String [] sqlFlowArr =new String [sheet1.getLastRowNum()];
                int indexss = 0;
                int indexFlow = 0;
                int sqlflag = 0;
    
                if ((wb.getSheetName(auxiliaryIndex).equals("辅材采购申请单"))) {
                    for (Row row : sheet1) {
                        if (row.getRowNum() >= 1) {
    
                        boolean flag = false;
                        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
                            if (row.getCell(c) != null && row.getCell(c).getCellType() != HSSFCell.CELL_TYPE_BLANK){
                                flag = true;
                            }
                        }
                        if(flag){
                            Auxiliary auxiliary  = new Auxiliary();
                            auxiliary.setCreatedby(user.getUname());
    
                            // 辅材id
                            if(row.getCell(0)!=null){
                                auxiliary.setId(Double.valueOf(row.getCell(0).toString().trim()).intValue());
                            }
    
                            //等等其他列
                            //保存对象操作
                            auxiliaryService.getInsertSql(auxiliary);
                }
                } else {
                    map.put("msg", "导入模板有误，请检查后重新上传！");
                    map.put("result", false);
                }
                map.put("msg", "成功导入或更新"+sqlflag+"条数据！");
                map.put("result", true);
            } catch (Exception e) {
                map.put("msg", "数据异常，请检查数据是否正确录入！");
                map.put("result", false);
                e.printStackTrace();
            }
    
            this.writeJson(map);
        }

[70]: /images/20220515/09d733b2b88140c7b968f54bec2946db.png