POI读取EXCEL
选择EXCEL文件界面:
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>由Excel导入基本信息</title> </head> <body background="images/bottom.png"> <center> <br><br><br><br><br><br> <script> function check() { var excelfileName = importDriversInfoEXL.importEXLFile.value ; var exceltype = excelfileName.substring(excelfileName.lastIndexOf('.') + 1).toLowerCase(); if(excelfileName.length==0) { alert("请选择excel"); return false; } else if(!(exceltype=="xls")) { alert("格式必须为excel 2003"); return false; } } </script> <form action="<%=request.getContextPath()%>/ReadDriverEXL" method="get" enctype="multipart/form-data" name="importDriversInfoEXL" > 导入司机基本信息: <input name="importEXLFile" type="file" size="30" maxlength="50" onchange="check()"/> <br> <br> <input type="submit" class="importButton" value="确定" > </form> </center> </body> </html>
ReadDriverEXL.java(一个servlet,读取EXCEL,并调用UserInfo.java的方法将从EXCEL中读取到的内容写入数据库)
package shouqisystem.user; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.PrintWriter; import java.sql.SQLException; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.catalina.util.URLEncoder; 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.*; import org.apache.poi.xssf.usermodel.*; import org.eclipse.jdt.internal.compiler.ast.ThrowStatement; import com.sun.xml.internal.bind.v2.schemagen.xmlschema.List;; /** * Servlet implementation class ReadDriverEXL */ public class ReadDriverEXL extends HttpServlet { private static final long serialVersionUID = 1L; Driver driver=null; /** * @see HttpServlet#HttpServlet() */ public ReadDriverEXL() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub String filePath=new String(request.getParameter("importEXLFile")); // File uploadPath = new File(request.getRealPath("/downloadPath") ); // System.out.println(uploadPath ); System.out.println(filePath); ArrayList<Driver> list=readXls(filePath);//读取EXCEL文件,每行是一个driver对象,每列是这个driver对象的属性,一共多少行就有多少个driver对象,将这些对象存入list中 //PrintWriter out = response.getWriter(); for(int i=0;i<list.size();i++){ driver=list.get(i); boolean judge = false; try { judge = UserInfo.judgeEXLtoDB(driver);//判断数据库中是否已存在和这个driver对象相同的数据 } catch (Exception e1) { // TODO Auto-generated catch block e1.printStackTrace(); } if(judge){ try { UserInfo.EXLtoDB(driver);//如果不存在将这个driver新增至数据库 } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }else{ try { UserInfo.editEXLtoDB(driver);//如果存在则更新这个driver } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } response.setCharacterEncoding("utf-8"); response.setHeader("Content-type", "text/html;charset=UTF-8"); //out.print("<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">"); //out.print("<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>"); PrintWriter out=response.getWriter(); //out.print("<script>alert('hello world');</script>"); out.print("<script language='javascript'>alert('倒入Load Data Succeed!');window.location.href='http://localhost:8080/ShouQiSystem/driversInfo.jsp';</script>"); } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); } public ArrayList<Driver> readXls(String filePath) throws IOException{ InputStream is=new FileInputStream(filePath); HSSFWorkbook hssfWorkbook=new HSSFWorkbook(is); Driver driver=null; ArrayList<Driver> list=new ArrayList<Driver>(); for(int numSheet=0; numSheet<hssfWorkbook.getNumberOfSheets();numSheet++){ HSSFSheet hssfSheet=hssfWorkbook.getSheetAt(numSheet); if(hssfSheet==null){ continue; } for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){ HSSFRow hssfRow=hssfSheet.getRow(rowNum); if(hssfRow!=null){ driver=new Driver(); HSSFCell employeeCardNum=hssfRow.getCell(0); HSSFCell name=hssfRow.getCell(1); HSSFCell sex=hssfRow.getCell(2); HSSFCell ID=hssfRow.getCell(3); HSSFCell birthDate=hssfRow.getCell(4); HSSFCell nation=hssfRow.getCell(5); HSSFCell education=hssfRow.getCell(6); HSSFCell politicsStatus=hssfRow.getCell(7); HSSFCell workPlace=hssfRow.getCell(8); HSSFCell phone=hssfRow.getCell(9); HSSFCell address=hssfRow.getCell(10); HSSFCell workType=hssfRow.getCell(11); HSSFCell workerDegreeTech=hssfRow.getCell(12); HSSFCell beginWorkDate=hssfRow.getCell(13); HSSFCell enterWorkPlaceDate=hssfRow.getCell(14); HSSFCell domicilePlace=hssfRow.getCell(15); HSSFCell postalcode=hssfRow.getCell(16); HSSFCell drivingLicenseFileNum=hssfRow.getCell(17); HSSFCell carNum=hssfRow.getCell(18); HSSFCell singleDouble=hssfRow.getCell(19); driver.setEmployeeCardNum(getValue(employeeCardNum)); driver.setName(getValue(name)); driver.setSex(getValue(sex)); driver.setID(getValue(ID)); driver.setBirthDate(getValue(birthDate)); driver.setNation(getValue(nation)); driver.setEducation(getValue(education)); driver.setPoliticsStatus(getValue(politicsStatus)); driver.setWorkPlace(getValue(workPlace)); driver.setPhone(getValue(phone)); driver.setAddress(getValue(address)); driver.setWorkType(getValue(workType)); driver.setWorkerDegreeTech(getValue(workerDegreeTech)); driver.setBeginWorkDate(getValue(beginWorkDate)); driver.setEnterWorkPlaceDate(getValue(enterWorkPlaceDate)); driver.setDomicilePlace(getValue(domicilePlace)); driver.setPostalcode(getValue(postalcode)); driver.setDrivingLicenseFileNum(getValue(drivingLicenseFileNum)); driver.setCarNum(getValue(carNum)); driver.setSingleDouble(getValue(singleDouble)); list.add(driver); } System.out.println("********************************"); } return list; } return list; } private String getValue(HSSFCell hssfCell){ DecimalFormat df=new DecimalFormat("#"); switch (hssfCell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if(HSSFDateUtil.isCellDateFormatted(hssfCell)){ SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); System.out.println(sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())).toString()); return sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())).toString(); } System.out.println(df.format(hssfCell.getNumericCellValue()).toString()); return df.format(hssfCell.getNumericCellValue()).toString(); case HSSFCell.CELL_TYPE_STRING: System.out.println(hssfCell.getStringCellValue()); return hssfCell.getStringCellValue(); case HSSFCell.CELL_TYPE_FORMULA: return hssfCell.getCellFormula(); case HSSFCell.CELL_TYPE_BLANK: return ""; case HSSFCell.CELL_TYPE_BOOLEAN: return hssfCell.getBooleanCellValue() + ""; case HSSFCell.CELL_TYPE_ERROR: return hssfCell.getErrorCellValue() + ""; } return ""; } }
UserInfo.java中的相关方法
public static boolean judgeEXLtoDB(Driver driver) throws Exception{ String sql="SELECT * FROM driversinfo WHERE employeeCardNum=?"; try{ conn=UserInfo.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1,driver.getEmployeeCardNum()); rs=pstmt.executeQuery(); if(rs.next()){ rs.close(); pstmt.close(); return false; }else{ rs.close(); pstmt.close(); return true; } }catch(Exception e){ throw e; } } public static void EXLtoDB(Driver driver)throws SQLException{ pstmt=null; String sql="insert into driversinfo(employeeCardNum,name,sex,ID,birthDate,nation,education,politicsStatus,workPlace,phone,address,workType," + "workerDegreeTech,beginWorkDate,enterWorkPlaceDate,domicilePlace,postalcode,drivingLicenseFileNum,carNum,singleDouble)" + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; try{ conn=UserInfo.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1,driver.getEmployeeCardNum()); pstmt.setString(2,driver.getName()); pstmt.setString(3, driver.getSex()); pstmt.setString(4,driver.getID()); pstmt.setString(5, driver.getBirthDate()); pstmt.setString(6, driver.getNation()); pstmt.setString(7, driver.getEducation()); pstmt.setString(8, driver.getPoliticsStatus()); pstmt.setString(9, driver.getWorkPlace()); pstmt.setString(10, driver.getPhone()); pstmt.setString(11, driver.getAddress()); pstmt.setString(12, driver.getWorkType()); pstmt.setString(13, driver.getWorkerDegreeTech()); pstmt.setString(14, driver.getBeginWorkDate()); pstmt.setString(15, driver.getEnterWorkPlaceDate()); pstmt.setString(16, driver.getDomicilePlace()); pstmt.setString(17, driver.getPostalcode()); pstmt.setString(18, driver.getDrivingLicenseFileNum()); pstmt.setString(19, driver.getCarNum()); pstmt.setString(20, driver.getSingleDouble()); pstmt.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } public static void editEXLtoDB(Driver driver)throws SQLException{ pstmt=null; String sql="update driversinfo set name=?,sex=?,ID=?,birthDate=?,nation=?,education=?,politicsStatus=?,workPlace=?,phone=?,address=?,workType=?,workerDegreeTech=?,beginWorkDate=?,enterWorkPlaceDate=?," + "domicilePlace=?,postalcode=?,drivingLicenseFileNum=?,carNum=?,singleDouble=? where employeeCardNum=?"; try{ conn=UserInfo.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1,driver.getName()); pstmt.setString(2, driver.getSex()); pstmt.setString(3,driver.getID()); pstmt.setString(4, driver.getBirthDate()); pstmt.setString(5, driver.getNation()); pstmt.setString(6, driver.getEducation()); pstmt.setString(7, driver.getPoliticsStatus()); pstmt.setString(8, driver.getWorkPlace()); pstmt.setString(9, driver.getPhone()); pstmt.setString(10, driver.getAddress()); pstmt.setString(11, driver.getWorkType()); pstmt.setString(12, driver.getWorkerDegreeTech()); pstmt.setString(13, driver.getBeginWorkDate()); pstmt.setString(14, driver.getEnterWorkPlaceDate()); pstmt.setString(15, driver.getDomicilePlace()); pstmt.setString(16, driver.getPostalcode()); pstmt.setString(17, driver.getDrivingLicenseFileNum()); pstmt.setString(18, driver.getCarNum()); pstmt.setString(19, driver.getSingleDouble()); pstmt.setString(20,driver.getEmployeeCardNum()); pstmt.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }