java 处理上传exl数据 并导入数据库

思路：处理上传exl表格，并读取文件，数据，讲数据放去集合，循环插入数据库。

重点，读取文件时需要读数据，格式和数据是否正确，（因为只是把整条路打通，所以这块没有细弄）；

项目使用ssm框架，

struts：

        <action name="uploadExcel" class="com.payment.action.PaymentAction" method="uploadExcel">
            <result>/center/uploadExcel.ftl</result>
        </action>

action:

public String uploadExcel(){
        boolean ret = false;
        try{
            if(excel == null){
                this.getRequest().setAttribute("message", "请选择一个Excel文件!");
            }else{
                ret = paymentBo.excelToAps(excel,excel_type);
                if(!ret){
                    this.getRequest().setAttribute("message", "Excel添加失败,请确定格式是否正确!");    
                }else{
                    this.getRequest().setAttribute("message", "添加成功");    
                }
            }
            }catch(Exception e){
                e.printStackTrace();
            }
        return success;
    }

dao:

public boolean excelToAps(File file,String type ) throws Exception {
        System.out.println("paymentBo.excelToAps进入");
        
        try {     
            Workbook workbook = getWeebWork(type,file);
            Sheet sheet = workbook.getSheetAt(0);
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            int rownum = sheet.getLastRowNum();// 获取总行数
            int minRowIx = sheet.getFirstRowNum();  
            int maxRowIx = sheet.getLastRowNum(); 
            List<String> list = new ArrayList<String>();  //测试存放数据的list
            List<PaymentDataVo> listPayment = new ArrayList<PaymentDataVo>();//将exl中的数据提取后存放的list
            System.out.println("rownum:"+rownum+"minRowIx:"+minRowIx+",maxRowIx:"+maxRowIx);
            for (int rowIx = 1; rowIx <= maxRowIx; rowIx++) {  
                Row row = sheet.getRow(rowIx);  
                StringBuilder sb = new StringBuilder();  
                short minColIx = row.getFirstCellNum();  
                short maxColIx = row.getLastCellNum(); 
                PaymentDataVo payvoDataVo =new PaymentDataVo();
                //System.out.println("minColIx:"+minColIx+"maxColIx:"+maxColIx );
                System.out.print("第"+rowIx+"行数据：");
                for (short colIx = 0; colIx < maxColIx; colIx++) {  
                    Cell cell = row.getCell(new Integer(colIx));  
                    CellValue cellValue = evaluator.evaluate(cell);
                    
                    /******/
                    DecimalFormat    df   = new DecimalFormat("######0.00"); 
                    
                    if(colIx == 0){//日期
                        String   date =cellValue.getNumberValue()+"" ;
                         System.out.print(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(cell.getDateCellValue())+",");
                         payvoDataVo.setCreateTime(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(cell.getDateCellValue()));
                    }else if(colIx== 1){//卡类
                        System.out.print((int)cellValue.getNumberValue()+",");
                        payvoDataVo.setPaymentId((int)cellValue.getNumberValue());
                    }else if(colIx == 2){//消费类型
                        System.out.print((int)cellValue.getNumberValue()+",");
                        //payvoDataVo.setConsumprionId(cellValue.getNumberValue());
                    }else if(colIx == 3){//明细类型
                        System.out.print(cellValue.getStringValue()+",");
                        payvoDataVo.setDataType(cellValue.getStringValue());
                    }else if(colIx == 4){//消费简述
                        System.out.print(cellValue.getStringValue()+",");
                        payvoDataVo.setDataName(cellValue.getStringValue());
                    }else if(colIx == 5){//消费金额
                        System.out.print(df.format(cellValue.getNumberValue()));
                        payvoDataVo.setDataAMT(Double.parseDouble(df.format(cellValue.getNumberValue())));
                    }else if(cellValue.getCellType()==0){
                         break;
                    }
                    
                    
                    
                    /***此处代码为网络摘录****/
                    
//                    if (cellValue == null) {  
//                        continue;  
//                    }  
//                    // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了  
//                    // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html  
//                    switch (cellValue.getCellType()) {  
//                    case Cell.CELL_TYPE_BOOLEAN:  
//                        sb.append(SEPARATOR + cellValue.getBooleanValue());  
//                        break;  
//                    case Cell.CELL_TYPE_NUMERIC:  
//                        // 这里的日期类型会被转换为数字类型，需要判别后区分处理  
//                        if (DateUtil.isCellDateFormatted(cell)) {  
//                            sb.append(SEPARATOR + cell.getDateCellValue()+"---"); 
//                        } else {  
//                            sb.append(SEPARATOR + cellValue.getNumberValue()+"+++"); 
//                        }  
//                        break;  
//                    case Cell.CELL_TYPE_STRING:  
//                        sb.append(SEPARATOR + cellValue.getStringValue());  
//                        break;  
//                    case Cell.CELL_TYPE_FORMULA:  
//                        break;  
//                    case Cell.CELL_TYPE_BLANK:  
//                        break;  
//                    case Cell.CELL_TYPE_ERROR:  
//                        break;  
//                    default:  
//                        break;  
//                    }  
                } 
                System.out.println();
                list.add(sb.toString());
                listPayment.add(payvoDataVo);
            } 
            paymentDao.addPaymentDataVos(listPayment);
            
          
        } catch (Exception e) {
            return false;
            //e.printStackTrace();
        }
        return true;
    }

mybatis:

    <insert id="addPaymentDataVos" parameterType="java.util.List" >
        INSERT into payments_data VALUES 
 
        <foreach collection="list" item="item" index="index" separator="," >  
        (null,#{item.createTime},#{item.paymentId},#{item.consumprionId},#{item.dataType},#{item.dataName},#{item.dataAMT})  
        </foreach> 
    </insert>

 

有问题会再跟进的！