导出模板和导入Excel

导出模板和导入Excel
html….

<div id="inputDiv" class="easyui-dialog" title="导入商品" style="width:300px;height:220px;padding:10px" data-options="buttons:'#inputDiv-buttons', closed:true,modal:true">
    <form action="<%=basePath %>admin/itme/exportExcel.html" method="post" id="exportForm" style="float:right;">
         <input type="submit" value="模板下载"/> 
    </form>
    <form class="inputForm" id="itmeInput" method="post" enctype="multipart/form-data">
    <table width="100%">
            <tr>
                <td align="center" style="width:100px;" valign="top">请选择要导入的EXCEL文件(文件格式:.xls)</td>
            </tr>
            <tr>
                <td align="left" style="width:100px;height: 10px;"></td>
            </tr>
            <tr>
                <td align="center">
                      <input type="file" name="importExcel" class="easyui-validatebox"  id="importExcel"/>
                </td>
            </tr>
            <tr>
                <td colspan="2" align="center">
                    <div style="margin:0 auto;margin-top:10px;">
                        <span style="margin-right:20px;">
                            <a href="javascript:inputData()" class="easyui-linkbutton" icon="icon-save">确定</a>
                        </span>
                        <a href="#" onclick="$('#inputDiv').dialog('close')" class="easyui-linkbutton" icon="icon-cancel">取消</a>
                    </div>
                </td>
            </tr>
        </table>
    </form>
</div>

script….

//批量导入库区
    function inputData(){
        $('#inputDiv').dialog('close');
        $('#itmeInput').form('submit', {
            url:"itme/insertManyItme.html",
            onSubmit: function(){
                var objtype=$("#importExcel").val().substring($("#importExcel").val().lastIndexOf(".")).toLowerCase();
                if($("#importExcel").val().length == 0){
                    $.messager.alert('提示','请选择导入文件！');
                    return false;
                }else if(objtype != '.xls'){
                    $.messager.alert('提示','导入类型不正确！');
                    return false;
                }else{
                     $.messager.progress({
                         title:'导入数据',
                         msg:'正在导入...'
                         });
                }
            },
            success:function(data){
                $.messager.progress('close');
                if(data == 'null' || data.length == 0){
                    $.messager.show({title:'导入数据',msg:"导入失败！",timeout:5000,showType:'show'});
                    return;
                }
                var data = eval(data);

                if(data.length == 2 && data[0] == -1){
                    $.messager.show({title:'导入数据',msg:data[1],timeout:5000,showType:'show'});
                    return;
                }

                if(data.length>0){
                    $('#itmeGrid').datagrid('load');
                    var message="";
                    for(var i=0;i<data.length;i++){
                        message+=data[i].cdskItemName+",";
                    }
                    $("#importExcel").attr("value","");
                    $.messager.show({title:'导入数据',msg:"添加商品名称："+message.substring(0,message.length-1)+"  成功！ ",timeout:5000,showType:'show'});
                }else{
                    $("#importExcel").attr("value","");
                    $.messager.show({title:'导入数据',msg:"导入失败！",timeout:5000,showType:'show'});
                }
            }
        });
    }

模板下载java….

public static void uploadModel(HttpServletResponse response,String fileName) {
        // TODO Auto-generated method stub
        try {
            // 定义输出类型
           response.setContentType("application/vnd.ms-excel;charset=UTF-8");// 定义输出类型
           response.reset();// 清除缓冲中的数据
           ////attachment --- 作为附件下载
           ////inline --- 在线打开
           response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\";");

           URL url = new URL((String) EhcacheUtil.get("saveUrl")+"modelExcel/"+fileName);
           URLConnection conn = url.openConnection(); 
            BufferedInputStream bufferedInputStream=new BufferedInputStream(conn.getInputStream());
            BufferedOutputStream bufferedOutput = new BufferedOutputStream(response.getOutputStream());
            int len=-1; 
            byte[] bt = new byte[1024];
            while ((len = bufferedInputStream.read(bt)) > 0) {
                 bufferedOutput.write(bt, 0, len);
                   bufferedOutput.flush();
            } 
            //关闭输入流
              bufferedOutput.close();
              bufferedInputStream.close();
        } catch (Exception e) { 
            e.printStackTrace(); 
        } 
    }

获取流….

 //poi获取导入文件流：
    public static InputStream getInputStream(HttpServletRequest request, String fileName) throws Exception
    {
        boolean isMultipart = ServletFileUpload.isMultipartContent(request);
        try
        {
            if (isMultipart == true)
            {
                // 为该请求创建一个DiskFileItemFactory对象，通过它来解析请求
                FileItemFactory factory = new DiskFileItemFactory();
                ServletFileUpload upload = new ServletFileUpload(factory);
                // 将所有的表单项目都保存到List中
                List<FileItem> items = upload.parseRequest(request);
                Iterator<FileItem> itr = items.iterator();
                // 循环list，取得表单项
                while (itr.hasNext())
                {
                    FileItem item = (FileItem) itr.next();
                    // 检查当前项目是普通表单项目还是文件。
                    if (item.isFormField())
                    {
                        // 如果是普通表单项目，显示表单内容。
                        String fieldName = item.getFieldName();
                        // 对应form表单中type="text" name="name"
                        if (fieldName.equals(fileName))
                        {
                            //log.info("the field name is" + item.getString()); // 显示表单内容
                        }
                    } else
                    {
                        // 如果是文件
                        if (item.getFieldName().equals(fileName))
                        {
                            // 如果上传文件的file的name为" filecer"
                            InputStream inStream = item.getInputStream();
                            return inStream;
                        }
                    }
                }
            } else
            {
                //log.info("the enctype must be multipart/form-data");
            }
        } catch (Exception e)
        {
           // log.info("获取文件输入流有误........" + e.getMessage());
        }
        return null;
    } 

加载数据….

@Override
    public String insertImportExcel(HttpServletRequest request,
            CondOrderLimit col, EsUser esUser) {
        try {
            InputStream in = LocalUpload.getInputStream(request,"importExcel");
            if(in!=null){
                return readXlsInputStreamToCarMemberList(in,col,esUser);
            }
        } catch (Exception e) {
            e.printStackTrace();
            TransactionAspectSupport.currentTransactionStatus().isRollbackOnly();
        }
        return null;
    }

    //poi获取的inputstream转换为excel,组装成对象：
    public String readXlsInputStreamToCarMemberList(InputStream ins,CondOrderLimit cols,EsUser user){
            List<CdWhItme> list = new ArrayList<CdWhItme>();
            List<String> resonList = new ArrayList<String>();//返回的结果提示
            resonList.add("-1");
            int totalRow = 0;//区分是不是表头
            try{
                POIFSFileSystem fs = new POIFSFileSystem(ins);
                HSSFWorkbook wb = new HSSFWorkbook(fs);
                HSSFSheet sheet = wb.getSheetAt(0);
                Iterator rows = sheet.rowIterator();
                int itemCode = Integer.parseInt(getNextCodeForImport(user.getEsCorCode()));//获取下一个编码规则
                int codeAdds = 0;//商品编码需要递增的
                // 循环行
                a:while (rows.hasNext()){
                    HSSFRow row = (HSSFRow) rows.next();
                    if (row.getRowNum() > 0){
                        // 循环列Cell
                        //0商品名称，1商品类型，2商品类别，3规格等级，4单位，5产地，6商品描述
                        Iterator cells = row.cellIterator();
                        CdWhItme cdWhItme = new CdWhItme();
                        // 循环列
                        while (cells.hasNext()){
                            HSSFCell cell = (HSSFCell) cells.next();
                            int col = cell.getCellNum();
                            switch (col){
                            // 商品名称
                            case 0:
                                totalRow ++;
                                if(totalRow == 1 && row.getRowNum() != 1){//表头行
                                    continue a;
                                }
                                String skuNameCase = "";
                                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
                                    skuNameCase = cell.getStringCellValue();
                                }else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
                                    skuNameCase = Integer.toString((int)(cell.getNumericCellValue()));
                                }
                                //判断商品名称是否重复
                                boolean returnFlag = true;
                                if(skuNameCase != null && skuNameCase != ""){
                                    if(skuNameCase.length() > 100){
                                        resonList.add("第"+(row.getRowNum()+1)+"行导入错误！原因：商品名称不能大于100字符！");
                                        return Tools.toJson(resonList);
                                    }
                                    if(this.isNameUnique(skuNameCase, user.getEsCorCode()) > 0){
                                        resonList.add("第"+(row.getRowNum()+1)+"行导入错误！原因：商品名称不能重复！");
                                        return Tools.toJson(resonList);
                                    }else{
                                        //遍历本Excel中商品名称是否重复
                                        for(CdWhItme sku : list){
                                            if(sku.getCdskItemName().equals(skuNameCase)){
                                                returnFlag = false;
                                                break;
                                            }
                                        }

                                    }
                                }else{
                                    resonList.add("第"+(row.getRowNum()+1)+"行导入错误！原因：商品名称不能为空！");
                                    return Tools.toJson(resonList);
                                }
                                if(returnFlag){
                                    cdWhItme.setCdskItemName(skuNameCase);
                                }else{
                                    resonList.add("第"+(row.getRowNum()+1)+"行导入错误！原因：导入Excel中商品名称重复！");
                                    return Tools.toJson(resonList);
                                }

                                break;
                            // 商品类型
                            case 1:
                                if(cdWhItme.getCdskItemName() == "" || cdWhItme.getCdskItemName() == null){
                                    resonList.add("第"+(row.getRowNum()+1)+"行导入错误！原因：商品名称不能为空！");
                                    return Tools.toJson(resonList);
                                }
                                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
                                    if(cell.getStringCellValue().length() > 100){
                                        resonList.add("第"+(row.getRowNum()+1)+"行导入错误！原因：商品类型不能大于100字符！");
                                        return Tools.toJson(resonList);
                                    }
                                    cdWhItme.setCdskItemCategory(cell.getStringCellValue());
                                }else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
                                    cdWhItme.setCdskItemCategory(Integer.toString((int)(cell.getNumericCellValue())));
                                }
                                break;
                            //商品类别
                            case 2:
                                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
                                    if(cell.getStringCellValue().length() > 100){
                                        resonList.add("第"+(row.getRowNum()+1)+"行导入错误！原因：商品类别不能大于100字符！");
                                        return Tools.toJson(resonList);
                                    }
                                    cdWhItme.setCdskItemChildCategory(cell.getStringCellValue());
                                }else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
                                    cdWhItme.setCdskItemChildCategory(Integer.toString((int)(cell.getNumericCellValue())));
                                }
                                break;
                            default:
                                //log.info("未找到对应的数据列");
                                break;
                            }
                        }
                        if (cdWhItme != null){
                            cdWhItme.setCdskIsactive(0);
                            cdWhItme.setCreateTime(new Date());
                            cdWhItme.setCreator(user.getEsId()+"");
                            cdWhItme.setCdskItemCode(user.getEsCorCode()+(itemCode+codeAdds));
                            list.add(cdWhItme);
                            codeAdds ++;
                        }
                    }
                }
                if(list.size() > 0){
                    int num = cdWhItmeMapper.insertManyItmeTable(list, "cd_wh_itme_"+user.getEsCorCode());
                    if(num >0){
                        ebOperateLogService.insertTable(new EbOperateLog(null, user.getEsId(), new Date(), "", "批量导入商品信息", null, "",
                                user.getEsCorCode(), "", Tools.listPageToJson(list)),user.getEsCorCode());
                        return Tools.toJson(list);
                    }
                }
            } catch (IOException e)
            {
                //log.error(e.getMessage());
            } finally
            {
                try
                {
                    ins.close();
                } catch (IOException e)
                {
                    //log.error(e.getMessage());
                }
            }
            return null;
        }