Strurts2下导入Excel文件数据-CSDN博客

本文链接：https://blog.csdn.net/u011067352/article/details/48418461

在项目中导入Excel是一项比较常见的功能，使用的还是POI技术，如果读者有兴趣请自行百度了解更多，我这里不多做介绍。好了还是老规矩开始贴代码。

导入Excel文件的jsp:

<%@page pageEncoding="utf-8" isELIgnored="false"%>
<%@taglib uri="/struts-tags" prefix="s"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
			
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  
导入Excel文件：<s:file name="excelFile"></s:file>
<s:submit value="导入数据" align="left"></s:submit>  
</s:form>

与Excel一一对应的Bean:

package com.lmc.entity;

public class Userinfo implements java.io.Serializable{

	
	// Fields  
	  
    private Integer id;  
    private String name;  
    private String pass;  
    private String lastname;  
    private String addres;  
    private String remark;  
  
    // Constructors  
  
    /** default constructor */  
    public Userinfo() {  
    }  
  
    /** full constructor */  
    public Userinfo(String name, String pass, String lastname, String addres,  
            String remark) {  
        this.name = name;  
        this.pass = pass;  
        this.lastname = lastname;  
        this.addres = addres;  
        this.remark = remark;  
    }  
  
    // Property accessors  
  
    public Integer getId() {  
        return this.id;  
    }  
  
    public void setId(Integer id) {  
        this.id = id;  
    }  
  
    public String getName() {  
        return this.name;  
    }  
  
    public void setName(String name) {  
        this.name = name;  
    }  
  
    public String getPass() {  
        return this.pass;  
    }  
  
    public void setPass(String pass) {  
        this.pass = pass;  
    }  
  
    public String getLastname() {  
        return this.lastname;  
    }  
  
    public void setLastname(String lastname) {  
        this.lastname = lastname;  
    }  
  
    public String getAddres() {  
        return this.addres;  
    }  
  
    public void setAddres(String addres) {  
        this.addres = addres;  
    }  
  
    public String getRemark() {  
        return this.remark;  
    }  
  
    public void setRemark(String remark) {  
        this.remark = remark;  
    }  
  
}

数据存放的类

package com.<span style="font-family:Arial, Helvetica, sans-serif;">lmc</span>.bean;

import java.util.ArrayList;  
import java.util.List;  
import org.apache.poi.hssf.record.formula.functions.T;  

public class ExcelWorkSheet<T>{

	private String sheetName;  
    private List<T> data = new ArrayList<T>();  //数据行  
    private List<String> columns; //列名  
    
    public String getSheetName() {  
        return sheetName;  
    }  
    public void setSheetName(String sheetName) {  
        this.sheetName = sheetName;  
    }  
    public List<T> getData() {  
        return data;  
    }  
    public void setData(List<T> data) {  
        this.data = data;  
    }  
    public List<String> getColumns() {  
        return columns;  
    }  
    public void setColumns(List<String> columns) {  
        this.columns = columns;  
    }  
	
}

最重要的Action类：

package com.lmc.action;

import java.io.File;  


import java.io.FileInputStream;  
import java.io.IOException;  
import java.io.InputStream;  
import java.util.ArrayList;  
import java.util.Iterator;  
import java.util.List;  
  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
  
import com.lmc.bean.ExcelWorkSheet;  
import com.lmc.entity.Userinfo;  
import com.opensymphony.xwork2.ActionSupport;  
/**
 *导入的Action类：
 */
@Controller
@Scope("prototype")
public class InputExcelAction extends BaseAction {  

	private static final long serialVersionUID = 1L;
	
	private File excelFile; //上传的文件  
      
    private String excelFileFileName; //保存原始文件名  
      
    //将Excel文件解析完毕后信息存放到这个对象中  
    private ExcelWorkSheet<Userinfo> excelWorkSheet;  
      
    public File getExcelFile() {  
        return excelFile;  
    }  
  
    public void setExcelFile(File excelFile) {  
        this.excelFile = excelFile;  
    }  
  
    public String getExcelFileFileName() {  
        return excelFileFileName;  
    }  
  
    public void setExcelFileFileName(String excelFileFileName) {  
        this.excelFileFileName = excelFileFileName;  
    }  
  
    public ExcelWorkSheet<Userinfo> getExcelWorkSheet() {  
        return excelWorkSheet;  
    }  
  
    public void setExcelWorkSheet(ExcelWorkSheet<Userinfo> excelWorkSheet) {  
        this.excelWorkSheet = excelWorkSheet;  
    }  
  
      
      
    //判断文件类型  
    public Workbook createWorkBook(InputStream is) throws IOException{  
        if(excelFileFileName.toLowerCase().endsWith("xls")){  
            return new HSSFWorkbook(is);  
        }  
        if(excelFileFileName.toLowerCase().endsWith("xlsx")){  
            return new XSSFWorkbook(is);  
        }  
        return null;  
    }  
    //导入方法  
    public String inputExcel() throws Exception{  
        Workbook book = createWorkBook(new FileInputStream(excelFile));  
//        book.getNumberOfSheets();  //判断Excel文件有多少个sheet  
        Sheet sheet =  book.getSheetAt(0); //读取第一个sheet   
        excelWorkSheet = new ExcelWorkSheet<Userinfo>();  
        //保存工作单名称  
        Row firstRow = sheet.getRow(0);  
        Iterator<Cell> iterator = firstRow.iterator();  
          
        //保存列名  
        List<String> cellNames = new ArrayList<String>();  
        while (iterator.hasNext()) {  
            cellNames.add(iterator.next().getStringCellValue());  
        }  
        excelWorkSheet.setColumns(cellNames);  
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {  //for循环获取Excel中的数据
            Row ros = sheet.getRow(i);  
            Userinfo user = new Userinfo();  
            user.setId((int)ros.getCell(0).getNumericCellValue());  
            user.setName(ros.getCell(1).getStringCellValue());  
            user.setPass(ros.getCell(2).getStringCellValue());  
            user.setLastname(ros.getCell(3).getStringCellValue());  
            user.setAddres(ros.getCell(4).getStringCellValue());  
            user.setRemark(ros.getCell(5).getStringCellValue());  
            excelWorkSheet.getData().add(user);  
        } 
        //excelWorkSheet对象存放的是Excel中的数据，
        //在这里你可以将数据存放到数据库中或者做其他操作就随你发挥了，
        //我这里输出其中的一项到控制台
        for (int i = 0; i < excelWorkSheet.getData().size(); i++) {  
            Userinfo info = excelWorkSheet.getData().get(i);  
            System.out.println(info.getLastname());  
        }
        //把导入的数据展示到页面
        targetURL = "/WEB-INF/excel/inputExcelSuccess.jsp";
        return SUCCESS;  
    }
    
}

Excel导入后数据输出的jsp:

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
			
%>
<h1><s:property value="excelWorkSheet.sheetName" /> </h1>  
  <p>  
    <s:iterator value="excelWorkSheet.columns">  
        <s:property />  ||   
    </s:iterator>  
  </p>  
    
  <s:iterator var="user" value="excelWorkSheet.data">  
    <p>  
        <s:property value="#user.id"/>     
        <s:property value="#user.name"/>     
        <s:property value="#user.pass"/>     
        <s:property value="#user.lastname"/>     
        <s:property value="#user.addres"/>      
        <s:property value="#user.remark"/>      
    </p>  
  </s:iterator>

完成。

附上我使用的Excel文件截图和成功后的效果图：