采用原始servlet接收excel文件，然后解析（解析第一个字段）存入数据库

package com.carfi.product;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
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.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
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 com.mysql.jdbc.PreparedStatement;

/**
 * 文件一键上传 Servlet implementation class excelUpload
 */
public class excelUploadServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	private String uploadPath = "D:\\temp"; // 上传文件的目录
	private String tempPath = "d:\\temp\\buffer\\"; // 临时文件目录
	File tempPathFile;

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		response.setCharacterEncoding("utf-8");

		// 将文件上传到服务器
		try {
			DiskFileItemFactory factory = new DiskFileItemFactory();

			factory.setSizeThreshold(4096); // 设置缓冲区大小，这里是4kb
			factory.setRepository(tempPathFile);// 设置缓冲区目录

			ServletFileUpload upload = new ServletFileUpload(factory);

			upload.setSizeMax(4194304); // 设置最大文件尺寸，这里是4MB

			List
 
   items = upload.parseRequest(request);// 得到所有的文件
			Iterator
  
    i = items.iterator(); File file = null; while (i.hasNext()) { FileItem fi = (FileItem) i.next(); String fileName = fi.getName(); if (fileName != null) { File fullFile = new File(fi.getName()); file = new File(uploadPath + "\\" + fullFile); File savedFile = new File(uploadPath, fullFile.getName()); fi.write(savedFile); } } // 文件已上传到D:\temp // 加载Excel文件对象 Workbook wb = null; try { wb = new HSSFWorkbook(new FileInputStream(file)); } catch (OfficeXmlFileException e) { wb = new XSSFWorkbook(new FileInputStream(file)); } // 读取一个sheet Sheet sheet = wb.getSheetAt(0); // list装载所有导入的id的产品信息 List
   
     list = new ArrayList
    
     (); List
     
       ids = new ArrayList
      
       ();// 装载查询id // 读取sheet中每一行 for (Row row : sheet) { // 跳过空行 if (row.getCell(0) == null || StringUtils.isBlank(row.getCell(0).getStringCellValue())) { continue; } String id = row.getCell(0).getStringCellValue(); ids.add(id); } // 创建批量查询语句 StringBuffer Str1 = new StringBuffer("insert into vrcp_device_id values "); StringBuffer Str2 = new StringBuffer("select * from device_status where id in ("); for (String id : ids) { String short_id = id.substring(id.length() - 11); if (ids.get(ids.size() - 1) == id) { Str1 = Str1.append("(" + id + "," + short_id + ")"); Str2 = Str2.append(short_id + ")"); } else { Str1 = Str1.append("(" + id + "," + short_id + "),"); Str2 = Str2.append(short_id + ","); } } String sql1 = Str1.toString(); String sql2 = Str2.toString(); //jdbc Class.forName("com.mysql.jdbc.Driver"); // 得到连接 Connection conn = DriverManager.getConnection("jdbc:mysql://39.108.60.83:3306/device_info", "readeruser", "qwezxc"); PreparedStatement statement1 = (PreparedStatement) conn.prepareStatement(sql1); PreparedStatement statement2 = (PreparedStatement) conn.prepareStatement(sql2); statement1.execute(); ResultSet resultSet = statement2.executeQuery(); // 遍历结果集装载 while (resultSet.next()) { device_status status = new device_status(); status.setId(resultSet.getString("id")); status.setGnss_time(resultSet.getDate("gnss_time")); status.setLat(resultSet.getDouble("lat")); status.setLng(resultSet.getDouble("lng")); list.add(status); } conn.close(); request.setAttribute("statusList", list); request.getRequestDispatcher("main.jsp").forward(request, response); } catch (Exception e) { e.printStackTrace(); } } public void init() throws ServletException { File uploadFile = new File(uploadPath); if (!uploadFile.exists()) { uploadFile.mkdirs(); } File tempPathFile = new File(tempPath); if (!tempPathFile.exists()) { tempPathFile.mkdirs(); } } }