html:
在这里插入代码片
<%@ 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>My JSP 'excel.jsp' starting page</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">
<script src="<%=path %>/index/js/jquery-2.1.4.js"></script>
</head>
<body>
<form action="<%=path %>/InportTitles"
data-validator-option="{theme:'bootstrap', timely:2, stopOnError:true}"
id="titleForm" class="form-horizontal" role="form"
enctype="multipart/form-data" method="post">
<div class="form-group">
<label class="control-label col-sm-3">上传文件:</label>
<div class="col-sm-6">
<input id="articleImageFile" name="f1" type="file"
class="form-control" style="width:100%; display: inline;" />
</div>
</div>
<div class="form-inline">
<div class="col-sm-offset-9">
<input type="submit" class="btn btn-primary btn-sm" value="导入" />
</div>
</div>
</form>
</body>
</html>
serclet:
在这里插入代码片
package uploadToDB;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
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.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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 org.apache.poi.ss.usermodel.DateUtil;
import db.db;
import model.User;
public class InportTitles extends HttpServlet {
private User t = null;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
String territoryIds = null;
Integer territoryId = 0;
if (territoryIds != null && !territoryIds.equals("")) {
territoryId = Integer.valueOf(territoryIds);
}
response.setContentType("text/html;charset=UTF-8");
try {
FileItemFactory factory = new DiskFileItemFactory();
// 文件上传核心工具类
ServletFileUpload upload = new ServletFileUpload(factory);
// 单个文件大小限制
upload.setFileSizeMax(10 * 1024 * 1024);
// 总文件大小限制
upload.setSizeMax(50 * 1024 * 1024);
// 对中文编码处理
upload.setHeaderEncoding("UTF-8");
if (ServletFileUpload.isMultipartContent(request)) {
List<FileItem> list = upload.parseRequest(request);
// 遍历
for (FileItem item : list) {
String name = item.getFieldName(); // 获取name属性的值(必须要写的,当前端的表单有text类型的数据时候),就这样获取表单的元素
String value = item.getString("utf-8"); // 获取value属性的值
if (name.equals("territory")) {
territoryIds = value;
}
if (!item.isFormField()) {
// 读取文件
readXls(item.getInputStream());
}
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
// 添加
// 处理添加进列表的数据
request.setAttribute("msg", "导入成功");
request.getRequestDispatcher("/admin/excel.jsp").forward(request, response);
}
public void readXls(InputStream path) throws IOException, SQLException {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(path);
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
System.out.println("总行数:" + ",总列数:" + hssfSheet.getLastRowNum());
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
t = new User();
HSSFCell id = hssfRow.getCell(0);
id.setCellType(id.CELL_TYPE_STRING);
HSSFCell mima = hssfRow.getCell(1);
mima.setCellType(id.CELL_TYPE_STRING);
HSSFCell name = hssfRow.getCell(2);
HSSFCell sex = hssfRow.getCell(3);
HSSFCell tel = hssfRow.getCell(4);
tel.setCellType(id.CELL_TYPE_STRING);
HSSFCell email = hssfRow.getCell(5);
HSSFCell type = hssfRow.getCell(6);
HSSFCell classid = hssfRow.getCell(7);
t.setId(getValue(id));
t.setMima(getValue(mima));
t.setName(getValue(name));
t.setSex(getValue(sex));
t.setTel(getValue(tel));
t.setEmail(getValue(email));
t.setType(getValue(type));
t.setClassid(getValue(classid));
insert(t);
}
}
}
}
private void insert (User user) throws SQLException{
db connDbBean = new db();
ResultSet RS_result_check=connDbBean.executeQuery("select * from users where id='"+user.getId()+"' ");
boolean check = true;
while(RS_result_check.next()){
check = false;
}
if(check){
String sql ="insert into users(id,mima,name,classid,sex,tel,email,type) "
+"values('"+user.getId()+"','"+user.getMima()+"','"+user.getName()+"','"+user.getClassid()+"','"+user.getSex()+"','"+user.getTel()+"','"+user.getEmail()+"','"
+user.getType()+"')";
connDbBean.executeUpdate(sql);
}
}
// 读取单元格的值
private String getValue(Cell cell) {
String result = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
break;
case Cell.CELL_TYPE_NUMERIC:
// 可能是普通数字,也可能是日期
if (HSSFDateUtil.isCellDateFormatted(cell)) {
result = DateUtil.getJavaDate(cell.getNumericCellValue())
.toString();
} else {
result = cell.getNumericCellValue() + "";
}
break;
}
return result;
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
jar包: