黑马程序员__struts+poi+jstl上传Excel .-优快云博客

本文介绍如何使用Struts2框架结合POI组件实现从Excel文件中读取数据并将其导入到系统数据库的方法。首先通过Struts2上传组件将Excel文件上传到服务器，然后解析Excel内容，提取学生信息。

------- <a href="http://www.itheima.com" target="blank">android培训</a>、<a href="http://www.itheima.com" target="blank">java培训</a>、期待与您交流！ ----------

1. 导入Excel数据

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

2. 解决思路：

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

上传组件完成。

3. 搭建环境：

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

4. 定义两个值对象

Student类用来封装Student信息

 1 package com.strutspoi;
 2 
 3 import java.util.Date;
 4 
 5 public class Student {
 6     private int id;
 7     private String name;
 8     private String sex;
 9     private Date birthday;
10 
11     //生产set、get方法略
12             //为了方便看到结果，重写toString方法，打印到控制台
13     public String toString() {
14         return "Student [id=" + id + ", name=" + name + ", sex=" + sex
15                 + ", birthday=" + birthday + "]";
16     }
17 
18 }

ExcelWorkSheet用来封装Excel工具表信息

 1 import java.util.ArrayList;
 2 import java.util.List;
 3 
 4 public class ExcelWorkSheet<T> {
 5 
 6     private String sheetName;// 工作单名称
 7     private List<T> data = new ArrayList<T>();
 8     private List<String> columns;// 列名
 9         //get、set方法略
10 }

导入的Action

 1 import java.io.File;
 2 import java.io.FileInputStream;
 3 import java.io.IOException;
 4 import java.io.InputStream;
 5 import java.util.ArrayList;
 6 import java.util.Iterator;
 7 import java.util.List;
 8 
 9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
10 import org.apache.poi.ss.usermodel.Cell;
11 import org.apache.poi.ss.usermodel.Row;
12 import org.apache.poi.ss.usermodel.Sheet;
13 import org.apache.poi.ss.usermodel.Workbook;
14 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
15 import org.apache.struts2.ServletActionContext;
16 
17 import com.opensymphony.xwork2.ActionSupport;
18 
19 @SuppressWarnings("serial")
20 public class ImportExcelAction extends ActionSupport {
21     private File excelFile;// 上传文件
22 
23     private String excelFileFileName;// 保存原始文件名
24     private ExcelWorkSheet<Student> excelWorkSheet;
25 
26     public File getExcelFile() {
27         return excelFile;
28     }
29 
30     public void setExcelFile(File excelFile) {
31         this.excelFile = excelFile;
32     }
33 
34     public String getExcelFileFileName() {
35         return excelFileFileName;
36     }
37 
38     public void setExcelFileFileName(String excelFileFileName) {
39         this.excelFileFileName = excelFileFileName;
40     }
41 
42     private Workbook createWorkBook(InputStream is) throws IOException {
43         if (excelFileFileName.toLowerCase().endsWith("xls")) {
44             return new HSSFWorkbook(is);
45         }
46         if (excelFileFileName.toLowerCase().endsWith("xlsx")) {
47             return new XSSFWorkbook(is);
48 
49         }
50 
51         return null;
52 
53     }
54 
55     @Override
56     public String execute() throws Exception {
57         Workbook workbook = createWorkBook(new FileInputStream(excelFile));
58         Sheet sheet = workbook.getSheetAt(0);
59         excelWorkSheet = new ExcelWorkSheet<Student>();
60         // 保存工作单名称
61         excelWorkSheet.setSheetName(sheet.getSheetName());
62         Row firstRow = sheet.getRow(0);
63         Iterator<Cell> iterator = firstRow.iterator();
64         // 保存列名
65         List<String> cellNames = new ArrayList<String>();
66         while (iterator.hasNext()) {
67             cellNames.add(iterator.next().getStringCellValue());
68         }
69         excelWorkSheet.setColumns(cellNames);
70         for (int i = 1; i <= sheet.getLastRowNum(); i++) {
71             Row row = sheet.getRow(i);
72             Student student = new Student();
73             student.setId((int) row.getCell(0).getNumericCellValue());
74             student.setName(row.getCell(1).getStringCellValue());
75             student.setSex(row.getCell(2).getStringCellValue());
76             student.setBirthday(row.getCell(3).getDateCellValue());
77             System.out.println(student);
78             excelWorkSheet.getData().add(student);
79         }
80         
81         ServletActionContext.getRequest().setAttribute("excelWorkSheet", excelWorkSheet);
82         ServletActionContext.getServletContext().setAttribute("excelFileFileName", excelFileFileName);
83         return SUCCESS;
84 
85     }
86 
87 }

5、struts配置文件

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE struts PUBLIC
 3     "-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"
 4     "http://struts.apache.org/dtds/struts-2.0.dtd">
 5 <struts>
 6     <constant name="struts.ui.theme" value="simple" />
 7     <!--<constant name="struts.multipart.saveDir" value="/tmp"/> -->
 8 
 9     <package name="com.strutspoi" extends="struts-default">
10         <action name="import" class="com.strutspoi.ImportExcelAction">
11             <result>/disdata.jsp</result>
12         </action>
13     </package>
14 </struts>

6、前台页面使用struts标签，主要复习struts的基本知识

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <%@ taglib prefix="s" uri="/struts-tags"%>
 4 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 5 <html>
 6 <head>
 7 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
 8 <title>导入数据</title>
 9 </head>
10 <body>
11     <s:form action="import" method="post" enctype="multipart/form-data">
12   导入Excel文件<s:file name="excelFile" />
13         <br />
14 
15 
16         <s:submit value="导入"></s:submit>
17 
18     </s:form>
19 
20 </body>
21 </html>

展示页面使用jstl标签，使知识点复习更多

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
 4 <%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
 5 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 6 <html>
 7 <head>
 8 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
 9 <title>显示信息</title>
10 </head>
11 
12 <body>
13     <h1>${excelWorkSheet.sheetName}</h1>
14     <p>
15         <c:forEach items="${excelWorkSheet.columns}" var="col">
16             <c:out value="${col}" />
17         </c:forEach>
18     </p>
19     <c:forEach items="${excelWorkSheet.data}" var="stu">
20         <p>
21             ${stu.id}&nbsp;&nbsp; ${stu.name}&nbsp;&nbsp; ${stu.sex}&nbsp;&nbsp;
22             <fmt:formatDate value="${stu.birthday}" pattern="yyyy/MM/dd" />
23         </p>
24     </c:forEach>
25 </body>
26 </html>