• Office Excel导入数据库总结


    项目是在SSH框架内实现:

    使用的jar包

    CDSN地址:http://download.csdn.net/download/lg_xulei/10148892

    使用Maven:

    pom.xml配置:

           <dependency>
               <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.9</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.9</version>
            </dependency>

    项目maven做了限制导入,可以使用

     <repositories>  
            <repository>  
                <id>central</id>  
                <name>Maven Central Repository</name>  
                <url>http://repo1.maven.org/maven2</url>  
                <snapshots>  
                    <enabled>true</enabled>  
                </snapshots>  
            </repository>  
        </repositories>  

    具体JAVA实现:

    包引用:

    // 导入Excel
         public String ExcelInto() throws Exception {
            String directory = "/file";
            String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
    //定义成员变量 File target
    = UploadFileInfo.Upload(uploadFile, uploadFileFileName,targetDirectory); List<DangerLevel> sList = new ArrayList<DangerLevel>(); /*Workbook wb = new HSSFWorkbook(excelFile);*/ Workbook wb=null;
    //这个地方在第一次定义局部变量时,后台报了一个stream关闭错误,是因为在try{}catch(){}时,
    //第一次格式化数据没通过,流通道关闭了,所以只能使用如下的方式进行数据导入
    try { wb = new HSSFWorkbook(new FileInputStream(target)); } catch (OfficeXmlFileException e) { try { wb = new XSSFWorkbook(new FileInputStream(target)); } catch (Exception e2) { e2.printStackTrace(); } } catch (Exception e) { e.printStackTrace(); } Sheet sheet = wb.getSheetAt(0); int rowNum = sheet.getLastRowNum() + 1; for (int i = 3; i < rowNum; i++) { DangerLevel dangerLevel = new DangerLevel(); Row row = sheet.getRow(i); int cellNum = row.getLastCellNum(); for (int j = 0; j < cellNum; j++) { SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd"); Cell cell = row.getCell(j); String cellValue = null; switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库 case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型 if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式 if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { sdf = new SimpleDateFormat("HH:mm"); } else {// 日期 sdf = new SimpleDateFormat("yyyy-MM-dd"); } Date date = cell.getDateCellValue(); cellValue = sdf.format(date); } else if (cell.getCellStyle().getDataFormat() == 58) { // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58) sdf = new SimpleDateFormat("yyyy-MM-dd"); double value = cell.getNumericCellValue(); Date date = DateUtil.getJavaDate(value); cellValue = sdf.format(date); } else { double value = cell.getNumericCellValue(); CellStyle style = cell.getCellStyle(); DecimalFormat format = new DecimalFormat(); String temp = style.getDataFormatString(); // 单元格设置成常规 if (temp.equals("General")) { format.applyPattern("#"); } cellValue = format.format(value); } break; case HSSFCell.CELL_TYPE_STRING:// String类型 cellValue = cell.getRichStringCellValue().toString(); break; case HSSFCell.CELL_TYPE_BLANK: cellValue = ""; default: cellValue = ""; break; } switch (j) {// 通过列数来判断对应插如的字段 case 0: dangerLevel.setNote1(cellValue); break; case 1: dangerLevel.setRummager(cellValue); break; case 2: dangerLevel.setCheckTime(sdf.parse(cellValue)); break; case 3: dangerLevel.setCheckType(cellValue); break; case 4: dangerLevel.setSpotName(cellValue); break; case 5: dangerLevel.setDangerDesc(cellValue); break; case 6: dangerLevel.setClassification(cellValue); break; case 7: dangerLevel.setDangerLevel(cellValue); break; case 8: dangerLevel.setDangerType(cellValue); break; case 9: dangerLevel.setIssuedTime(sdf.parse(cellValue)); break; case 10: dangerLevel.setIssuedMan(cellValue); break; case 11: dangerLevel.setRectifyPlan(cellValue); break; case 12: dangerLevel.setPostponedDeadLine(sdf.parse(cellValue)); break; case 13: dangerLevel.setRectifyUserName(cellValue); break; case 14: dangerLevel.setRectifyCompany(cellValue); break; case 15: dangerLevel.setRectifyResult(cellValue); break; case 16: dangerLevel.setRectifyTime(sdf.parse(cellValue)); break; case 17: dangerLevel.setRecheckerCompany(cellValue); break; case 18: dangerLevel.setRecheckerResult(cellValue); break; case 19: dangerLevel.setRecheckerName(cellValue); break; case 20: dangerLevel.setRecheckerTime(sdf.parse(cellValue)); break; case 21: dangerLevel.setClearName(cellValue); break; case 22: dangerLevel.setClearTime(sdf.parse(cellValue)); break; case 23: dangerLevel.setRecheckerName(cellValue); break; case 24: dangerLevel.setDangerLevelStatus(cellValue); break; case 25: dangerLevel.setFileName(cellValue); break; case 26: dangerLevel.setFilePath(cellValue); break; } } sList.add(dangerLevel); } dangerLevelService.addDangerLevel(sList); return "dangerLevel_middle"; }
    package com.hfky.crs.busi.domain;
    
    import java.io.File;
    import java.io.Serializable;
    
    import org.apache.commons.io.FileUtils;
    
    public class UploadFileInfo implements Serializable {
    
        private static final long serialVersionUID = -3076586641759098763L;
        
        /**
         * 待上传文件
         */
        private File upload;
        /**
         * 待上传文件名
         */
        private String uploadFileName;
        /**
         * 待上传文件的MIME类型
         */
        private String uploadContentType;
        
        /**
         * 成功上传的文件相对与根目录的文件名
         */
        private String uploadRelName;
        
        public File getUpload() {
            return upload;
        }
    
        public void setUpload(File upload) {
            this.upload = upload;
        }
    
        public String getFileName() {
            return uploadFileName;
        }
    
        public void setUploadFileName(String uploadFileName) {
            this.uploadFileName = uploadFileName;
        }
    
        public String getContentType() {
            return uploadContentType;
        }
    
        public void setUploadContentType(String uploadContentType) {
            this.uploadContentType = uploadContentType;
        }
    
        public String getUploadRelName() {
            return uploadRelName;
        }
    
        public void setUploadRelName(String uploadRelName) {
            this.uploadRelName = uploadRelName;
        }
    
        public String getUploadFileName() {
            return uploadFileName;
        }
    
        public String getUploadContentType() {
            return uploadContentType;
        }
        public static File Upload(File uploadFile, String uploadFileFileName,String targetDirectory)throws Exception {
            File target = new File(targetDirectory, uploadFileFileName);
            // 如果文件已经存在,则删除原有文件
            if (target.exists()) {
                target.delete();
            }
            // 复制file对象,实现上传
            FileUtils.copyFile(uploadFile, target);
            return target;
        }
        
    }
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
    <%@ include file="/resources/pages/system/inc-easyui.jsp"%>
    <html>
    <head>
    <base href="<%=basePath %>"/>
    <title>数据导入</title>
    <link href="styles/css/blue.css" type="text/css" rel="stylesheet" />
    </head>
     <body class="crs_ep_tablebodybg">
     <form action="danger!ExcelInto.action" enctype="multipart/form-data" method="post">
        <table border="0" cellspacing="0" cellpadding="0" class="crs_ep_tablestyle"> 
            <tr>
              <td colspan="99" id="more">
                <input type="file" name="uploadFile" id="uploadFile"/>
                <input type="submit" value="上传"/>
                <input type="reset" value="重置"/>
              </td>
            </tr>
          </table> 
      </form>
     </body>
    </html>

    这里使用的是from表单提交,为了效果,在List页面中使用的是Iframe,当Action导入完成return的是一个 result地址,所以关键问题是:上传后不能关闭iframe页面;

    这里使用一个非常不优雅的方式解决【JQuery版本低,用户群使用IE8.9.10】:使用一个跳转过度页面,在这个过度页面使用js关闭ifarem,和刷新List页面。如下:

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
    <%@ include file="/resources/pages/system/inc-easyui.jsp"%>
    <html>
    <head>
    <base href="<%=basePath %>"/>
    <title>隐患数据导入</title>
    <link href="styles/css/blue.css" type="text/css" rel="stylesheet" />
     <script type="text/javascript">
      $(function(){
    //获取父也页面对象 parent.document.getElementById(
    'cancelBtn').click();
    //获取父页面的id="
    cancelBtn".使用click()鼠标触发事件。
    parent.document.location.reload(); });
    </script>
    </head>
    <body>
    </body
    </html>
  • 相关阅读:
    Asp.net MVC3 Razor语法小记
    asp.net4的webform使用路由
    判断数据库中要创建的存储过程、函数等是否已经存在
    visual studio 解决方案版本互转
    sql语句创建表的时候加表注释和列注释
    Jquery在指定元素内查找元素(相对定位)
    .net便利的小方法
    sqlserver2008秘钥
    jquery星级评定效果(原创)
    清除GridView自带样式
  • 原文地址:https://www.cnblogs.com/flytogalaxy/p/7997605.html
Copyright © 2020-2023  润新知