struts2 excel导入数据到数据库中去

最新推荐文章于 2022-08-31 14:39:36 发布

谱写

最新推荐文章于 2022-08-31 14:39:36 发布

阅读量739

点赞数

分类专栏：上传文件

本文链接：https://blog.csdn.net/baidu_37366055/article/details/90642379

版权

上传文件专栏收录该内容

3 篇文章 0 订阅

订阅专栏

实现excel导入需要加入相关jar包：

poi-3.15-beta1.jar.jar

poi-examples-3.15-beta1.jar

poi-excelant-3.15-beta1.jar

poi-ooxml-3.15-beta1.jar

poi-ooxml-schemas-3.15-beta1.jar

poi-scratchpad-3.15-beta1.jar

导入excel版本等于或高于2007

则还需要导入 xmlbeans-2.6.0

好了可以开始项目了

数据格式：

index.jsp

    <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
    <%
    String path = request.getContextPath();
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
    %>

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
      <head>
      </head>

      <body>
        <form action="addStudents.action" enctype="multipart/form-data" method="post" id="fm">
           <input type="file" name="file" style="display: none;" οnchange="fm.submit();">
           <input type="button" value="上传Execel" οnclick="file.click();"/>
        </form>
        </br>
      </body>
    </html>

web.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" version="3.0">
      <display-name>struts2_Excel2003_demo</display-name>
      <filter>
        <filter-name>struts2</filter-name>
        <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
      </filter>
      <filter-mapping>
        <filter-name>struts2</filter-name>
        <url-pattern>*.action</url-pattern>
      </filter-mapping>
    </web-app>

struts.xml

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd">
    <struts>

    
        <constant name="struts.multipart.maxSize" value="10701096"/>
        <package name="aaa" extends="struts-default" namespace="/">
           <action name="addStudents" class="com.action.StudentAction" method="addStudents">
               <result name="success">/msg.jsp</result>
           </action>
        </package>

    </struts>

服务端后台
StudentAction.java

    package com.action;

    import java.io.File;
    import java.io.FileInputStream;

    import javax.servlet.http.HttpServletRequest;

    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.apache.struts2.ServletActionContext;

    import com.opensymphony.xwork2.Action;
    import com.opensymphony.xwork2.ActionSupport;

    public class StudentAction extends ActionSupport {

       private File file;
       private String fileFileName;
       private String msg;

       public String getMsg() {
           return msg;
       }

       public void setMsg(String msg) {
           this.msg = msg;
       }

       public File getFile() {
           return file;
       }

       public void setFile(File file) {
           this.file = file;
       }

       public String getFileFileName() {
           return fileFileName;
       }

       public void setFileFileName(String fileFileName) {
           this.fileFileName = fileFileName;
       }

       public String addStudents() {
           HttpServletRequest request = ServletActionContext.getRequest();
           if (!this.getFileFileName().toLowerCase().endsWith(".xls") && !this.getFileFileName().toLowerCase().endsWith(".xlsx")) {
               msg = "请导入Excel文件";
               return Action.SUCCESS;
           }
           try {
               FileInputStream fi = new FileInputStream(file);
               Workbook wb = null;
               if (this.getFileFileName().toLowerCase().endsWith(".xls")) {
                   wb = new HSSFWorkbook(fi);
               }else{
                   wb = new XSSFWorkbook(fi);
               }
               wb.close();
               Sheet sheet = wb.getSheetAt(0);
               int rowsNum =sheet.getLastRowNum()+1;
               System.out.println("工作表共有"+rowsNum+"行");
               if (rowsNum == 0) {
                   msg = "数据表为空";
                   return Action.SUCCESS;
               }else{
                   for (int i = 1; i < rowsNum; i++) {
                       System.out.println(
                               "用户名："+getStringCellValue(sheet.getRow(i).getCell(0)).trim()+
                               ",姓名："+getStringCellValue(sheet.getRow(i).getCell(1)).trim()+
                               ",性别"+getStringCellValue(sheet.getRow(i).getCell(2)).trim()
                               );
                   }
                   msg = "上传成功";
               }

           } catch (Exception e) {
               // TODO: handle exception
               System.out.println(e.toString());
               msg = "服务器正在忙碌...";
           }
           return Action.SUCCESS;
       }

       private String getStringCellValue(Cell cell) {
           String cellValue = null;
                switch(cell.getCellType()){ //判断excel单元格内容的格式，并对其进行转换，以便插入数据库
                    case 0 : cellValue = String.valueOf((int)cell.getNumericCellValue()); break;
                    case 1 : cellValue = cell.getStringCellValue(); break;
                    case 2 : cellValue = String.valueOf(cell.getDateCellValue()); break;
                    case 3 : cellValue = ""; break;
                    case 4 : cellValue = String.valueOf(cell.getBooleanCellValue()); break;
                    case 5 : cellValue = String.valueOf(cell.getErrorCellValue()); break;
                }
                return cellValue;
       }
    }

谱写

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
struts2 excel导入数据到数据库中去

实现excel导入需要加入相关jar包：poi-3.15-beta1.jar.jarpoi-examples-3.15-beta1.jarpoi-excelant-3.15-beta1.jarpoi-ooxml-3.15-beta1.jarpoi-ooxml-schemas-3.15-beta1.jarpoi-scratchpad-3.15-beta1.jar导入ex...
复制链接

扫一扫