jsp/servlt excel 导入数据库

最新推荐文章于 2023-04-23 13:36:54 发布

不死海的不死Niao

最新推荐文章于 2023-04-23 13:36:54 发布

阅读量626

点赞数

分类专栏： jsp/servlet 文章标签： jsp/servlet

本文链接：https://blog.csdn.net/qq_39523807/article/details/90208261

版权

jsp/servlet 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

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包：
在这里插入图片描述

不死海的不死Niao

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
jsp/servlt excel 导入数据库

html：在这里插入代码片<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerNam...
复制链接

扫一扫