struts+poi+jstl上传Excel

来源：互联网发布：子午谷奇谋知乎编辑：程序博客网时间：2024/05/24 06:01

1. 导入Excel数据

在web应用中需要将Excel文件中的数据导入到系统中，保存到数据库里。

2. 解决思路：

把excel文件（*.xls或者*.xlsx）上传到服务器，上传的过程我们可以使用Struts2中

上传组件完成。

3. 搭建环境：

将Struts2下的blank项目里的lib下的jar包拷入到WEB-INF下的lib文件下，并加入POI组件包。

4. 定义两个值对象

import java.util.Date;

publicclass Student {

privateintid;

private Stringname;

private Stringsex;

private Datebirthday;

publicint getId() {

returnid;

}

publicvoid setId(int id) {

this.id = id;

}

public String getName() {

returnname;

}

publicvoid setName(String name) {

this.name = name;

}

public String getSex() {

returnsex;

}

publicvoid setSex(String sex) {

this.sex = sex;

}

public Date getBirthday() {

returnbirthday;

}

publicvoid setBirthday(Date birthday) {

this.birthday = birthday;

}

@Override

public String toString() {

return"Student [id=" +id + ", name=" +name +", sex=" + sex

+ ", birthday=" + birthday +"]";

}

import java.util.ArrayList;

import java.util.List;

publicclass ExcelWorkSheet<T> {

private StringsheetName;//工作单名称

private List<T>data =new ArrayList<T>();

private List<String>columns;//列名

public String getSheetName() {

returnsheetName;

}

publicvoid setSheetName(String sheetName) {

this.sheetName = sheetName;

}

public List<T> getData() {

returndata;

}

publicvoid setData(List<T> data) {

this.data = data;

}

public List<String> getColumns() {

returncolumns;

}

publicvoid setColumns(List<String> columns) {

this.columns = columns;

}

5. 导入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.apache.struts2.ServletActionContext;

import com.opensymphony.xwork2.ActionSupport;

@SuppressWarnings("serial")

publicclass ImportExcelActionextends ActionSupport {

private FileexcelFile;//上传文件

private StringexcelFileFileName;//保存原始文件名

private ExcelWorkSheet<Student>excelWorkSheet;

public File getExcelFile() {

returnexcelFile;

}

publicvoid setExcelFile(File excelFile) {

this.excelFile = excelFile;

}

public String getExcelFileFileName() {

returnexcelFileFileName;

}

publicvoid setExcelFileFileName(String excelFileFileName) {

this.excelFileFileName = excelFileFileName;

}

private Workbook createWorkBook(InputStream is)throws IOException {

if (excelFileFileName.toLowerCase().endsWith("xls")) {

returnnew HSSFWorkbook(is);

}

if (excelFileFileName.toLowerCase().endsWith("xlsx")) {

returnnew XSSFWorkbook(is);

}

returnnull;

}

@Override

public String execute()throws Exception {

Workbook workbook = createWorkBook(new FileInputStream(excelFile));

Sheet sheet = workbook.getSheetAt(0);

excelWorkSheet =new ExcelWorkSheet<Student>();

//保存工作单名称

excelWorkSheet.setSheetName(sheet.getSheetName());

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++) {

Row row = sheet.getRow(i);

Student student = new Student();

student.setId((int) row.getCell(0).getNumericCellValue());

student.setName(row.getCell(1).getStringCellValue());

student.setSex(row.getCell(2).getStringCellValue());

student.setBirthday(row.getCell(3).getDateCellValue());

System.out.println(student);

excelWorkSheet.getData().add(student);

}

ServletActionContext.getRequest().setAttribute("excelWorkSheet",excelWorkSheet);

ServletActionContext.getServletContext().setAttribute("excelFileFileName",excelFileFileName);

returnSUCCESS;

}

6. struts配置文件

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE struts PUBLIC

"-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"

"http://struts.apache.org/dtds/struts-2.0.dtd">

<result>/disdata.jsp</result>

</action>

</package>

</struts>

7. 前台展示页面

<%@ page language="java"contentType="text/html; charset=UTF-8"

pageEncoding="UTF-8"%>

<%@ taglib prefix="c"uri="http://java.sun.com/jsp/jstl/core"%>

<%@ taglib prefix="fmt"uri="http://java.sun.com/jsp/jstl/fmt"%>

<!DOCTYPEhtmlPUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN""http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<metahttp-equiv="Content-Type"content="text/html; charset=ISO-8859-1">

</head>

<body>

<h1>${excelWorkSheet.sheetName}</h1>

<p>

<c:forEachitems="${excelWorkSheet.columns}"var="col">

<c:outvalue="${col}"/>

</c:forEach>

</p>

<c:forEachitems="${excelWorkSheet.data}"var="stu">

<p>

${stu.id}   ${stu.name}   ${stu.sex}  

<fmt:formatDatevalue="${stu.birthday}"pattern="yyyy/MM/dd"/>

</p>

</c:forEach>

</body>

</html>