struts2 excel导入

最新推荐文章于 2021-02-16 09:21:56 发布

gxy_2015

最新推荐文章于 2021-02-16 09:21:56 发布

阅读量3.5k

点赞数

分类专栏： struts2 文章标签： struts2.0 excel jar

本文链接：https://blog.csdn.net/gxy_2015/article/details/51800691

版权

struts2 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

这篇博客介绍了如何在Struts2项目中实现Excel导入功能，需要添加一系列Apache POI库的jar包，包括poi-3.15-beta1等，并且当处理2007版或更高版本的Excel时，还需要xmlbeans-2.6.0。作者提供了jar包的下载链接，并提到了可以从Apache POI官网获取。

摘要由CSDN通过智能技术生成

实现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

这些包可以在我的网盘中找到：http://pan.baidu.com/s/1dFK7glF

如果没有可以去官网上下载（https://poi.apache.org/）

好了可以开始项目了

数据格式：

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>

<!-- 指定允许上传的文件最大字节数。默认值是2097152(2M) -->
    <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;
	}
}