jxl读取excel实现导入excel写入数据库

最新推荐文章于 2021-02-12 15:57:16 发布

weixin_30877493

最新推荐文章于 2021-02-12 15:57:16 发布

阅读量130

点赞数

文章标签：数据库 java xhtml

原文链接：http://www.cnblogs.com/pwenlee/p/4849519.html

版权


@RequestMapping(params = "method=import", method = RequestMethod.POST)
    @ResponseBody
    public String importConfig(HttpServletRequest request, HttpServletResponse response) throws Exception{
        response.setCharacterEncoding("GBK");
        String result = new String();;
        try{
            //获取请求中的文件
            MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
            MultipartFile contentFile = multipartHttpServletRequest.getFile(IMPORT_FILE_NAME); //IMPORT_FILE_NAME为前端中input的name
            //读取请求中的excel
            Workbook workbook = Workbook.getWorkbook(contentFile.getInputStream());
            Sheet sheet = workbook.getSheet(0);
            int rows=sheet.getRows();
            int columns=sheet.getColumns();
            //将excel中的内容读为ConfigInfo对象
            List<ConfigInfo> listConfigInfo = new ArrayList<ConfigInfo>();
            for(int i=1; i<rows; i++){
                ConfigInfo configInfo = new ConfigInfo();
                for(int j=0; j<columns; j++){
                    Cell cell = sheet.getCell(j, i);
                    String cellContent = cell.getContents();
                    switch (j){
                    case 0 : configInfo.setDataId(cellContent);
                    case 1 : configInfo.setGroup(cellContent);
                    case 2 : configInfo.setContent(cellContent);
                    }
                }
                listConfigInfo.add(configInfo);
            }
            //写入数据库
            int importFailedNum = 0;
            for(ConfigInfo configInfo:listConfigInfo){
                ConfigInfo configInfoExist = configService.findConfigInfo(configInfo.getDataId(), configInfo.getGroup());
                //写入数据库之前判断是否存在该条配置，并记录
                if (null != configInfoExist){
                    importFailedNum++;
　　　　　　　　　}else{
                    try{
                        configService.addConfigInfo(configInfo.getDataId(), configInfo.getGroup(),configInfo.getContent());
                    }catch(Exception operationDatabaseException){
                        log.error("导入配置出错"+operationDatabaseException);
                        return result;
                    }
                }
            }
            result = String.valueOf(importFailedNum);
            return result;
        }catch(Exception exception){
            log.error("导入配置出错"+exception);
            return result;
        }
    }

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html; charset=GBK" pageEncoding="GBK"%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=GBK" />
<title>导入配置信息</title>
    <script src="../../../js/jquery.js" type="text/javascript"></script>
    <script src="../../../js/ajaxfileupload.js" type="text/javascript"></script>
    <style type="text/css">
        body td {
            color: #333;
            font-family: Arial, Helvetica, sans-serif;
            font-size: 10pt;
        }
    </style>
    <script type="text/javascript">
        $(function () {
            $(":button").click(function () {
                if ($("#contentFile").val().length > 0) {
                    ajaxFileUpload();
                }
                else {
                    alert("请选择文件");
                }
            })
        })
        function ajaxFileUpload() {
            $.ajaxFileUpload
            (
                {
                    url: '/diamond-server/admin.do?method=import', //用于文件上传的服务器端请求地址
                    secureuri: false, //一般设置为false
                    fileElementId: 'contentFile', //文件上传空间的id属性  <input type="file" id="file" name="file" />
                    dataType: 'JSON', //返回值类型 一般设置为json
                    type:'post',
                    success: function (data, status)  //服务器成功响应处理函数
                    {
                        //判断返回的值
                        if (data == 0)
                            alert("导入配置成功");
                        else
                            alert("错误！第"+data+"条配置导入失败，请仔细对比要导入的配置，不能与已存在的配置重复");
                        if (typeof (data.error) != 'undefined') {
                            if (data.error != '') {
                                alert(data.error);
                            } else {
                                alert(data.msg);
                            }
                        }
                        window.location.href = "list.jsp";
                    },
                    error: function (data, status, e)//服务器响应失败处理函数
                    {
                        alert(e);
                        window.location.href = "list.jsp";
                    }
                }
            )
            return false;
        }
    </script>
</head>
<body>
    <center><h1><strong>导入配置信息</strong></h1></center>
    <p align='center'>
        <input type="file" id="contentFile" name="contentFile" />
        <input type="button" value="上传" />
    </p>
</body>
</html>

在MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;这句代码执行强转出错。发现我的配置文件里少了对multipart解析器的配置:

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
　　<property name="maxUploadSize" value="104857600"/>  <!-- 文件最大的大小 -->
　　<property name="maxInMemorySize" value="4096"/>
</bean>

同时需要依赖commons-fileupload-1.2.jar和commons-io-1.3.1。

转载于:https://www.cnblogs.com/pwenlee/p/4849519.html

weixin_30877493

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
jxl读取excel实现导入excel写入数据库

@RequestMapping(params = "method=import", method = RequestMethod.POST) @ResponseBody public String importConfig(HttpServletRequest request, HttpServletResponse response) throws Exception{...
复制链接

扫一扫