springmvc poi excel

最新推荐文章于 2022-12-12 16:03:34 发布

ETLshow

最新推荐文章于 2022-12-12 16:03:34 发布

阅读量350

点赞数

分类专栏：实践杂记文章标签： java excel poi

本文链接：https://blog.csdn.net/idea_boy/article/details/59147443

版权

实践杂记专栏收录该内容

12 篇文章 0 订阅

订阅专栏

本文使用的工具类参考了 http://raising.iteye.com/blog/2232167 这篇文章，由于报错做了改动，在此感谢原作者。
项目源码地址：https://github.com/zmdstr/demo
maven pom

        <!-- poi -->
        <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.14</version>
        </dependency>
        <!-- 文件上传 -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.2.2</version>
        </dependency>

jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>file upload</title>
<script type="text/javascript" src="${pageContext.request.contextPath }/js/jquery-1.11.1.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath }/js/ajaxfileupload.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath }/js/file-upload.js"></script>
</head>
<body>
<h3>批量导入excel测试</h3>
    <form action="">
        <input type="file" id="file" name="file"/>
        <input type="button" value="导入数据" onclick="upload()">
    </form>
</body>
</html>

file-upload.js

function upload(){
    console.log("点击上传按钮");
    $.ajaxFileUpload({  
        url : "upload1",  
        //dataType : 'json',  
        secureuri : false,  
        fileElementId : 'file',  
        success : function(res, status) { //服务器成功响应处理函数  
            if (status) {  
                console.log(status);
            }},
            error : function(res, status, e) {//服务器响应失败处理函数  
                alert("导入数据异常：文件导入过程异常。");  
            }  
        });  
}

springmvc.xml中需要增加的配置

<!-- 文件上传配置 -->
    <bean id="multipartResolver"
        class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <!-- 文件的最大上传大小 -->
        <property name="defaultEncoding" value="UTF-8" />
        <property name="maxUploadSize" value="1058576000" />
        <property name="uploadTempDir" value="/uploadFile/temp/" />
    </bean>

controller

@RequestMapping("/upload")
    public @ResponseBody String fileUpload(@RequestParam(value = "file") MultipartFile excelFile) {
        InputStream inputStream = null;
        String flag = "导入成功！";
        try {
            inputStream = excelFile.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
            flag = "导入失败";
        }
        List<Map<String, String>> excel = ExcelImportUtil.parseExcel(inputStream);
        for (Map<String, String> row : excel) {
            String id = row.get("id");
            String tbUniversityName = row.get("tb_university_name");
            String tbUniLoc = row.get("tb_uni_loc");
            String tag1 = row.get("tag1");
            String tag2 = row.get("tag2");
            String tag3 = row.get("tag3");
            String tag4 = row.get("tag4");
            UniversityBaseInfo baseInfo = new UniversityBaseInfo();
            baseInfo.setId(Integer.valueOf(id));
            baseInfo.setTag1(tag1);
            baseInfo.setTag2(tag2);
            baseInfo.setTag3(tag3);
            baseInfo.setTag4(tag4);
            baseInfo.setTbUniLoc(tbUniLoc);
            baseInfo.setTbUniversityName(tbUniversityName);
            myService.insert(baseInfo);
        }
        return flag;
    }
}

ExcelImportUtils

public class ExcelImportUtil {
    public static List<Map<String, String>> parseExcel(InputStream fis) {
        List<Map<String, String>> data = new ArrayList<Map<String, String>>();
        try {
            HSSFWorkbook book = new HSSFWorkbook(fis);
            HSSFSheet sheet = book.getSheetAt(0);
            int firstRow = sheet.getFirstRowNum();
            int lastRow = sheet.getLastRowNum();
            for (int i = firstRow; i < lastRow; i++) {
                Map<String, String> map = new HashMap<String, String>();
                HSSFRow row = sheet.getRow(i);
                int firstCell = row.getFirstCellNum();
                int lastCell = row.getLastCellNum();
                for (int j = firstCell; j < lastCell; j++) {
                    HSSFCell cell2 = sheet.getRow(firstRow).getCell(j);
                    String key = cell2.getStringCellValue();
                    HSSFCell cell = row.getCell(j);
                    String val = "";
                    if (null != cell) {
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        }
                        val = cell.getStringCellValue();
                    }
                    // 如果是表头，不放入map
                    if (i == firstRow) {
                        break;
                    } else {
                        map.put(key, val);
                    }
                    // System.out.println(map);
                }
                // 剔除表头
                if (i != firstRow) {
                    // 载入一行数据
                    data.add(map);
                    System.out.println(map);
                }
            }
            System.out.println(data);
            book.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return data;
    }
}

个人微信公众号，追寻自由，分享生活！
这里写图片描述

ETLshow

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
springmvc poi excel

本文使用的工具类参考了 http://raising.iteye.com/blog/2232167 这篇文章，由于报错做了改动，在此感谢原作者。项目源码地址：https://github.com/zmdstr/demo maven pom &lt;!-- poi --&gt; &lt;dependency&gt; &lt;...
复制链接

扫一扫

专栏目录