Java导出模板和导入Excel
Java导出模板和导入Excel
夏至未至_super 发表于2年前
Java导出模板和导入Excel
  • 发表于 2年前
  • 阅读 71
  • 收藏 1
  • 点赞 1
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

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,timeout:5000,showType:'show'});
                    return;
                }
                
                if(data.length>0){
                    $('#itmeGrid').datagrid('load');
                    var message="";
                    for(var i=0;i<data.length;i++){
                        message+=data.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 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;
                            default:
                                //log.info("未找到对应的数据列");
                                break;
                            }
                        }
                        list.add(cdWhItme);
                    }
                }
                if(list.size() > 0){
                    return Tools.toJson(list);
                }
            } catch (IOException e){} finally{}
            return null;
        }


共有 人打赏支持
粉丝 0
博文 6
码字总数 1975
×
夏至未至_super
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: