首页新闻找找看学习计划

求能导入excel文档的jsp页面代码

-1
悬赏园豆:200 [待解决问题]

<%@page language="java" import="java.util.*" pageEncoding="utf-8"%>

<div class="pageHeader">

<form method="post" action="well_upfile_save.jsp" enctype="multipart/form-data" target="navTab">

<div class="searchBar">

<table class="searchContent">

<tr>

<td><div class="pages">

<span>报表文件:</span>

<input type="file" name="upfile" size="50" accept="application/msexcel">

</div>

</td>

<td>

<div class="buttonActive">

<div class="buttonContent"><button type="submit">上传导入</button></div></div>

</td>

</tr>

</table>

</div>

</form>

</div>

<div id="report_data_imp" class="pageContent">

</div>

求代码中well_upfile_save.jsp的代码块,可以实现把导入的excel表中数据插入数据库表中,求各位大侠给个帮助,小妹不胜感激,愿把所有园豆奖励出去

问题补充:

一直在线等待答案

yangyanjie的主页 yangyanjie | 初学一级 | 园豆:2
提问于:2015-07-12 17:37
< >
分享
所有回答(2)
0
爱编程的大叔 | 园豆:29862 (高人七级) | 2015-07-12 17:50

不行,还是不会

支持(0) 反对(0) yangyanjie | 园豆:2 (初学一级) | 2015-07-12 17:58

我使用的是dwz框架

 

支持(0) 反对(0) yangyanjie | 园豆:2 (初学一级) | 2015-07-12 18:00
0

上传文件你会不?你的action="well_upfile_save.jsp",那就是在jsp上进行数据处理了,感觉好别扭的说。为什么不用一个servlet呢,起码代码清晰。

不过无所谓,执行起来都一样。我只能写伪代码,因为我jsp非常差。很少用

总之就是引入那些common-file里面的类。然后写代码,不过封装方法什么的很麻烦,先上传,然后获取文件,文件解析,插入数据库。建议你用后台action或者servlet

 

我有之前的代码,和你业务一样,虽然execl格式不一样

package cn.com.oaos.action.channel;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.hibernate.Session;

import cn.com.oaos.dao.BaseDao;
import cn.com.oaos.dao.imp.BaseDaoImp;
import cn.com.util.Callback;


public class FileUpload extends HttpServlet{
    
    protected void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        response.setHeader("Cache-Control", "no-cache");
        request.setCharacterEncoding("utf-8");
        DiskFileItemFactory factory = new DiskFileItemFactory();
        //获取文件需要上传到的路径
        String path = request.getRealPath("/upload");
        /*
         * 如果没以下两行设置的话,上传大的 文件 会占用 很多内存,设置暂时存放的 存储室 , 这个存储室,可以和 最终存储文件 的目录不同
         * 原理 它是先存到 暂时存储室,然后在真正写到 对应目录的硬盘上, 
         * 按理来说 当上传一个文件时,其实是上传了两份,第一个是以 .tem 格式的 
         * 然后再将其真正写到 对应目录的硬盘上
         */
        factory.setRepository(new File(path));
        //设置 缓存的大小,当上传文件的容量超过该缓存时,直接放到 暂时存储室
        //好处是能节省点内存。但是这里,即使是普通的文本也会生成个.tmp。所以都需要删除。
        //这句话是这样的。设置一个值,如果上传的大于这个值,就用暂时存储。如果小雨的话,就用内存保存。。需要看你jvm内存来看放哪里。
        factory.setSizeThreshold(0) ;
        //高水平的API文件上传处理
        ServletFileUpload upload = new ServletFileUpload(factory);
        try {
            //可以上传多个文件
            List<FileItem> list = (List<FileItem>)upload.parseRequest(request);
            for(FileItem item : list)
            {
                //获取表单的属性名字
                String name = item.getFieldName();
                //如果获取的 表单信息是普通的 文本 信息
                if(item.isFormField())
                {                    
                    //获取用户具体输入的字符串 ,名字起得挺好,因为表单提交过来的是 字符串类型的
                    String value = item.getString() ;
                    request.setAttribute(name, value);
                }
                //对传入的非 简单的字符串进行处理 ,比如说二进制的 图片,电影这些
                else
                {    
                    String type = request.getParameter("type");
                    if(request.getParameter("type")!=null && request.getParameter("type").equals("channel_info")){
                        handleChannelInfo(item.getInputStream());
                        response.getWriter().print("{'success':true}");
                    }else if(type!=null && "goodsstock".equals(type)){
                        String fullfilename = item.getName();
                        String [] tempstr =fullfilename.split("-");
                        if(tempstr.length>1){
                            String filename = tempstr[1];
                            if(filename.indexOf(".")>-1){
                                filename = filename.substring(0,filename.indexOf("."));
                            }
                            BaseDao dao = BaseDaoImp.newInstance();
                            if(!dao.getList("from channel_info where name=?", filename).isEmpty()){
                                handleGoodsInfo(item.getInputStream(),filename,fullfilename);
                                response.getWriter().print("{'success':true}");
                            }else{
                                response.getWriter().print("{'success':false,'error':'不存在该渠道信息'}");
                            }
                        }else{
                            response.getWriter().print("{'success':false,'error':'文件名格式不正确'}");
                        }
                    }else if(type!=null && "postageinfo".equals(type)){
                        this.handlePostageInfo(item.getInputStream());
                        response.getWriter().print("{'success':true}");
                    }
                    
                }
                //无论什么样的item,文本或者文件,都需要删除
                item.delete();
            }
        } catch (FileUploadException e) {
            e.printStackTrace();
            response.getWriter().print("{'success':false,'error':'"+e.getMessage()+"'}");
        }
        catch (Exception e) {
            e.printStackTrace();
            response.getWriter().print("{'success':false,'error':'"+e.getMessage()+"'}");
        }
    }
    /**
     * 解析channelinfo,余下的也都写这个servlet里面吧
     * @param is
     * @throws IOException 
     */
    @SuppressWarnings("unchecked")
    protected void handleChannelInfo(InputStream is) throws IOException{
        Map mapping = new HashMap();
        //添加映射中文和数据库字段映射,注意不要错别字
        mapping.put("简称","name");
        mapping.put("简写","simple_name");
        mapping.put("介绍","intro");
        mapping.put("类别","type");
        mapping.put("更新时间","update_date");
        mapping.put("配货率","distribution");
        mapping.put("售后率","service");
        mapping.put("当天截止下单时间","order_endtime");
        mapping.put("反馈时间","return_time");
        mapping.put("配货时间","distribution_time");
        mapping.put("库存情况","store");
        mapping.put("可选快递","express");
        mapping.put("更多说明","intro_more");
        mapping.put("级别","level");
        BaseDao dao = BaseDaoImp.newInstance();
        List notnull = new ArrayList();
        notnull.add("简称");
        List temp = readExcel2Entity(is, mapping,notnull);
        dao.saveOrUpdate("channel_info",temp);
    }
    /**
     * 统一解析excel
     * @throws IOException 
     */
    protected List<Map> readExcel2Entity(InputStream is,Map mapping,List notnull) throws IOException{
        String field = "";
        List<Map> list = new ArrayList<Map>();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        for(int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++){
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt( numSheet);
            //从第一行开始读,忽略标题行
            for(int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++){
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                Map map = new HashMap();
                boolean isadd=true;
                for(int cellNum = 0; cellNum < hssfSheet.getRow(0).getPhysicalNumberOfCells(); cellNum++){
                    field = hssfSheet.getRow(0).getCell(cellNum).toString().trim();
                    HSSFCell cell = hssfRow.getCell(cellNum);
                    if(cell == null){
                        if(notnull.contains(field)){
                            isadd = false;
                            break;
                        }
                        continue;
                    }
                    if(mapping.get(field)!=null){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String value = cell.getStringCellValue().trim();
                        if(value != null && !value.toLowerCase().equals("null") && !value.equals("")){
                            map.put(mapping.get(field),value);
                        }else{
                            if(notnull.contains(field)){
                                isadd = false;
                                break;
                            }
                        }
                    }
                }
                if(isadd){
                    list.add(map);
                }
            }
        }
        try {
            is.close();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return list;
    }
    
    /**
     * 库存上传单独的
     * @param is
     * @param filename
     * @throws IOException
     */
    protected List<Map> readExcel2Entity(InputStream is,Map mapping,List notnull,String defaultkey,String defaultvalue) throws IOException{
        String field = "";
        List<Map> list = new ArrayList<Map>();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        for(int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++){
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt( numSheet);
            //从第一行开始读,忽略标题行
            for(int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++){
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                Map map = new HashMap();
                boolean isadd=true;
                for(int cellNum = 0; cellNum < hssfSheet.getRow(0).getPhysicalNumberOfCells(); cellNum++){
                    field = hssfSheet.getRow(0).getCell(cellNum).toString().trim();
                    HSSFCell cell = hssfRow.getCell(cellNum);
                    if(mapping.get(field)!=null){
                        if(cell == null){
                            if(notnull.contains(field)){
                                isadd = false;
                                break;
                            }
                            continue;
                        }
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String value = cell.getStringCellValue().trim();
                        if(value != null && !value.toLowerCase().equals("null") && !value.equals("")){
                            if(field.equals("折扣")){
                                map.put(mapping.get(field), String.valueOf(new BigDecimal(value).add(new BigDecimal("0.01"))));
                            }else{
                                map.put(mapping.get(field),value);
                            }
                        }else{
                            if(notnull.contains(field)){
                                isadd = false;
                                break;
                            }
                        }
                    }
                }
                map.put(defaultkey, defaultvalue);
                if(isadd){
                    list.add(map);
                }
            }
        }
        try {
            is.close();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        
        return list;
    }
    protected List<Map> readCsv2Entity(InputStream is,Map mapping,List notnull,String defaultkey,String defaultvalue) throws IOException {
        String encode ="";
        BufferedInputStream bi = new BufferedInputStream(is);
        bi.mark(100);
        byte[] head = new byte[3];  
        bi.read(head);
        encode = "gbk";  
        if (head[0] == -1 && head[1] == -2 )  
            encode = "UTF-16";  
        if (head[0] == -2 && head[1] == -1 )  
            encode = "Unicode";  
        if(head[0]==-17 && head[1]==-69 && head[2] ==-65)
            encode = "UTF-8";
        else{
            encode = "gbk"; 
        }
        bi.reset();
        BufferedReader br = new BufferedReader(new InputStreamReader(bi,encode));
        String title = br.readLine();
        System.out.println(title);
        String [] keys = title.split(",");
        String line="";
        List result = new ArrayList();
        while((line=br.readLine())!=null){
            Map map = new HashMap();
            String []values = line.split(",");
            boolean isadd = true;
            for(int i = 0;i < values.length; i++){
                if(values[i].trim().equals("") && notnull.contains(keys[i])){
                    isadd = false;
                }
                map.put(mapping.get(keys[i].trim()), values[i]);
                if(keys[i].trim().equals("折扣")){
                    map.put(mapping.get(keys[i].trim()), String.valueOf(new BigDecimal(values[i]).add(new BigDecimal("0.01"))));
                }
                
            }
            if(isadd){
                result.add(map);
            }
        }
        try {
            br.close();
            bi.close();
            is.close();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        
        return result;
    }
    /**
     * 
     * @param is
     * @param filename
     * @throws IOException
     */
    protected void handleGoodsInfo(InputStream is,final String filename,String fullfilename) throws IOException{
        Map map = new HashMap();
        //添加映射中文和数据库字段映射,注意不要错别字
        map.put("货号","goodsNumber");
        map.put("品牌","brand");
        map.put("名称","goodNanme");
        map.put("类型","goodType");
        map.put("季节","season");
        map.put("性别","gender");
        map.put("系列","series");
        map.put("价格","price");
        map.put("尺码","size");
        map.put("数量","countNum");
        map.put("折扣","rebate");
        map.put("渠道", "channelName");
        List notnull = new ArrayList();
        notnull.add("货号");
        BaseDao dao = BaseDaoImp.newInstance();
        List temp = new ArrayList();
        if(fullfilename.endsWith(".csv")){
            temp = readCsv2Entity(is, map,notnull,"channelName",filename);
        }else{
            temp = readExcel2Entity(is, map,notnull,"channelName",filename);
        }
        dao.excuteUpdate("delete from goodsstock where channelName=?",filename);
        dao.saveOrUpdate("goodsstock",temp);
    }
    
    protected void handlePostageInfo(InputStream is) throws IOException{
        Map map = new HashMap();
        //添加映射中文和数据库字段映射,注意不要错别字
        map.put("级别编号","levelNumber");
        map.put("邮费级别","postageLevel");
        map.put("渠道编号","channelNumber");
        map.put("渠道","channelname");
        map.put("快递", "postageName");
        map.put("快递编号","postageNumber");
        map.put("省份","province");
        map.put("首重重量","firstweight");
        map.put("续重重量","addweigth");
        map.put("首重价格","firstPrice");
        map.put("续重价格","addprice");
        BaseDao dao = BaseDaoImp.newInstance();
        List notNulls = new ArrayList();
        notNulls.add("渠道");
        notNulls.add("省份");
        notNulls.add("快递名称");
        List temp = readExcel2Entity(is, map,notNulls);
        dao.saveOrUpdate("postageinfo",temp);
    }
}
View Code

代码有点长,整体流程和我上面说的一样。这种东西,还是自己写下来更有成就感。参考参考资料,思考思考,出来了。

洛城秋色 | 园豆:324 (菜鸟二级) | 2015-07-12 18:44

因为整个框架都是jsp页面集合,目前只有这个新加的功能没有实现,所以只能找这个解决方案

支持(0) 反对(0) yangyanjie | 园豆:2 (初学一级) | 2015-07-13 08:54

加你的qq行吗

支持(0) 反对(0) yangyanjie | 园豆:2 (初学一级) | 2015-07-13 08:55
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册