《JavaWeb---简单应用---练习JDBC，JSTL》---上传Excel文件，利用工具包解析，将数据储存到数据库中，可以查询删除
<%@ 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> <base href="<%=basePath%>"> <title>上传查看表格</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">--><style type="text/css">body{text-align: center;}table,td{height: 30px;text-align: center;border: 1px green solid;border-collapse: collapse;}</style> </head> <body> <table> <tr style="color: red"> <td>说明</td> <td>操作</td> </tr> <tr> <td> 查看数据库中的数据</td> <td> <a href="${pageContext.request.contextPath }/servlet/Read" >查看</a> </td> </tr> <tr> <td> 管理数据库中的数据</td> <td> <a href="${pageContext.request.contextPath }/servlet/Control" >管理</a> </td> </tr> <tr> <form action="${pageContext.request.contextPath }/servlet/Receive" enctype="multipart/form-data" method="post"> <td> <input type="file" name="filexls"/><br/> </td> <td> <input type="submit" value="上传"/> </td> </form> </tr> </table> </body></html>package com.strong.upload.receive;import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.PrintWriter;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.commons.fileupload.FileItem;import org.apache.commons.fileupload.FileItemFactory;import org.apache.commons.fileupload.FileUploadException;import org.apache.commons.fileupload.disk.DiskFileItemFactory;import org.apache.commons.fileupload.servlet.ServletFileUpload;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import com.strong.upload.dao.DaoUtils;import com.strong.upload.javabean.User;public class Receive extends HttpServlet {public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {//设置编码response.setCharacterEncoding("UTF-8");response.setContentType("text/html;charset=UTF-8");boolean isMultipart = ServletFileUpload.isMultipartContent(request);if (isMultipart){FileItemFactory factory = new DiskFileItemFactory();ServletFileUpload upload = new ServletFileUpload(factory);List<FileItem> items = null;try {//转换请求数据items = upload.parseRequest(request);} catch (FileUploadException e) {throw new RuntimeException("转换请求失败！");}//遍历请求Iterator iter = items.iterator();while (iter.hasNext()) { FileItem item = (FileItem) iter.next(); if (!item.isFormField()) { //判断是上传文件，并且是指定上传框内的文件 if (item.getContentType().equals("application/vnd.ms-excel")){ if (item.getFieldName().equals("filexls")){ ArrayList<User> users = sava2Bean(item); request.setAttribute("users", users); DaoUtils.createUsers(users);//将数据储存到数据库中 //DaoUtils.readUsers(); }else{ System.out.println("文件名不正确"); } } }}}request.setAttribute("type", "0");request.getRequestDispatcher("/WEB-INF/jsp/showusers.jsp").forward(request, response);}public ArrayList<User> sava2Bean(FileItem item){HSSFWorkbook hwb = null;try {//得到上传的文件hwb = new HSSFWorkbook(item.getInputStream());} catch (Exception e) {throw new RuntimeException("获取文件失败！");}//获取一个表HSSFSheet sheet = hwb.getSheetAt(0);//创建储存数据的集合ArrayList<User> users = new ArrayList<User>();//解析表中的数据，并将数据封装for (int i = 1; i <= sheet.getLastRowNum(); i++){HSSFRow row = sheet.getRow(i);//暂时储存一行数据ArrayList<String> data = new ArrayList<String>();for (int j = 0; j < row.getLastCellNum(); j++){HSSFCell cell = row.getCell(j);cell.setCellType(Cell.CELL_TYPE_STRING);data.add(cell.toString());}users.add(new User(data.get(0),data.get(1),data.get(2),data.get(3),data.get(4)));data.clear();}//以文件的形式保存到硬盘上String fallname = item.getName();String filename = fallname.substring(fallname.lastIndexOf("\\")+1, fallname.lastIndexOf("."));try {hwb.write(new FileOutputStream(new File("c:\\"+filename+"_upload.xls")));} catch (Exception e) {throw new RuntimeException("保存文件失败！");}return users;}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}}
package com.strong.upload.dao;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import com.strong.upload.javabean.User;/*create table users(id varchar(32) not null,name varchar(32) not null,age varchar(32) not null,phone varchar(32) not null,email varchar(32) not null); */public class DaoUtils {static Connection conn = null;static Statement st = null;static {//建立连接conn = JdbcUtils.getConnection();//创建语句try {st = conn.createStatement();} catch (SQLException e) {throw new RuntimeException("创建语句失败！");}}public static void createUsers(ArrayList<User> users){if (conn != null && st != null){for (int i = 0; i < users.size(); i++){User user = users.get(i);String sql = "insert into users(id,name,age,phone,email) values('"+user.getId()+"','"+user.getName()+"','"+user.getAge()+"','"+user.getPhone()+"','"+user.getEmail()+"')";try {st.executeUpdate(sql);} catch (SQLException e) {throw new RuntimeException("更新数据失败！");}}}}public static ArrayList<User> readUsers(){ArrayList<User> users = new ArrayList<User>();if (conn != null && st != null){String sql = "select * from users";try {ResultSet rs = st.executeQuery(sql);while(rs.next()){String id = (String) rs.getObject("id");String name = (String) rs.getObject("name");String age = (String) rs.getObject("age");String phone = (String) rs.getObject("phone");String email = (String) rs.getObject("email");users.add(new User(id, name, age, phone, email));}} catch (SQLException e) {throw new RuntimeException("查询失败！");}}return users;}public static void deleteUser(String id){if (conn != null && st != null){String sql = "delete from users where id="+id;try {st.executeUpdate(sql);} catch (SQLException e) {throw new RuntimeException("删除失败！");}}}}
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><html> <head> <c:if test="${requestScope.type == 0}" var="condition" scope="request"><meta http-equiv="Refresh" content="3;url=${pageContext.request.contextPath }"/></c:if> <title>显示信息</title> <meta http-equiv="description" content="this is my page"/> <meta http-equiv="content-type" content="text/html; charset=UTF-8"/> <link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath }/css/showusers.css"/> </head> <body><c:if test="${requestScope.type == 0}" var="condition" scope="request"> <h1>上传成功</h1>3秒后未跳转，<a href="${pageContext.request.contextPath }">点击这里...</a></c:if><c:if test="${requestScope.type == 1}" var="condition" scope="request"> <h1>数据库中的信息</h1></c:if><c:if test="${requestScope.type == 2}" var="condition" scope="request"> <h1>管理信息</h1></c:if><c:if test="${requestScope.control == 'delete'}" var="condition" scope="request"> <font color="red">删除成功！</font></c:if> <table border="1" width="50%" > <tr style="background-color: #00FF00;"> <td>id</td><td>name</td><td>age</td><td>phone</td><td>email</td><c:if test="${requestScope.type == 2}" var="condition" scope="request"><td>control</td></c:if> </tr> <c:forEach var="user" items="${users }" varStatus="i"><tr class="${i.count%2==0? 'even':'odd' }"><td>${user.id }</td><td>${user.name }</td><td>${user.age }</td><td>${user.phone }</td><td>${user.email }</td><c:if test="${requestScope.type == 2}" var="condition" scope="request"><td><a href="${pageContext.request.contextPath }/servlet/Delete?id='${user.id }'">删除</a></td></c:if></tr></c:forEach> </table> <c:if test="${requestScope.type == 1 || requestScope.type == 2}" var="condition" scope="request"> <a href="${pageContext.request.contextPath }">返回</a></c:if> </body></html>
