javaEE中Excel导入数据库范例

/* * Generated by MyEclipse Struts * Template path: templates/java/JavaClass.vtl */ package com.ccf.struts.action; import java.io.File; import java.io.UnsupportedEncodingException; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import javax.servlet.ServletContext; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import org.apache.commons.fileupload.FileItem; import org.apache.commons.fileupload.FileUploadException; import org.apache.commons.fileupload.FileUploadBase.SizeLimitExceededException; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; import org.apache.struts.action.ActionForm; import org.apache.struts.action.ActionForward; import org.apache.struts.action.ActionMapping; import org.apache.struts.actions.DispatchAction; import com.ccf.db.DBManager; /** * MyEclipse Struts Creation date: 04-13-2009 * * XDoclet definition: * code：七目豕 * @struts.action validate="true" * @struts.action-forward name="succeed" path="succeed.jsp" */ public class UpdateExcelAction extends DispatchAction { /* * Generated Methods */ /** * Method execute * * @param mapping * @param form * @param request * @param response * @return ActionForward */ public ActionForward updateFile(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) { // TODO Auto-generated method stub final long MAX_SIZE = 10 * 1024 * 1024;// 设置上传文件最大为 10M // 允许上传的文件格式的列表 final String[] allowedExt = new String[] { "xls", "XLS", "Xls", "xLs", "xlS" }; response.setContentType("text/html;charset=gbk"); // 设置字符编码为UTF-8, 这样支持汉字显示 response.setCharacterEncoding("gbk"); // 在struts中获取JSP中的application用于获取WEB根目录 ServletContext application = request.getSession().getServletContext(); // 实例化一个硬盘文件工厂,用来配置上传组件ServletFileUpload DiskFileItemFactory dfif = new DiskFileItemFactory(); dfif.setSizeThreshold(4096);// 设置上传文件时用于临时存放文件的内存大小,这里是4K.多于的部分将临时存在硬盘 // 设置存放临时文件的目录 // web根目录下的uploadfile目录 dfif .setRepository(new File(application.getRealPath("/") + "uploadfile")); // 用以上工厂实例化上传组件 ServletFileUpload sfu = new ServletFileUpload(dfif); // 设置最大上传尺寸 sfu.setSizeMax(MAX_SIZE); // 从request得到 所有 上传域的列表 List fileList = null; try { fileList = sfu.parseRequest(request); } catch (FileUploadException e) {// 处理文件尺寸过大异常 if (e instanceof SizeLimitExceededException) { request.setAttribute("errorInfo", "文件尺寸超过规定大小:" + MAX_SIZE + "字节！！！"); return mapping.findForward("error"); } e.printStackTrace(); } // 得到所有上传的文件 Iterator fileItr = fileList.iterator(); // 循环处理所有文件 while (fileItr.hasNext()) { FileItem fileItem = null; String path = null; long size = 0; // 得到当前文件 fileItem = (FileItem) fileItr.next(); // 忽略简单form字段而不是上传域的文件域(<input type="text" />等) if (fileItem == null || fileItem.isFormField()) { try { // 获取表单除file外的其它元素 System.out.println(fileItem.getFieldName() + ":" + new String(fileItem.getString().getBytes("ISO-8859-1"), "GBK")); request.setAttribute("tableName", new String(fileItem.getString().getBytes("ISO-8859-1"), "GBK")); } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block request.setAttribute("errorInfo", e.getMessage()); return mapping.findForward("error"); } continue; } // 得到文件的完整路径 path = fileItem.getName(); // 得到文件的大小 size = fileItem.getSize(); if ("".equals(path) || size == 0) { request.setAttribute("errorInfo", "文件大小为0无法读取！！！"); return mapping.findForward("error"); } // 得到去除路径的文件名 String t_name = path.substring(path.lastIndexOf("\\") + 1); // 得到文件的扩展名(无扩展名时将得到全名) String t_ext = t_name.substring(t_name.lastIndexOf(".") + 1); // 拒绝接受规定文件格式之外的文件类型 boolean errorFile = true; for (int i = 0; i < allowedExt.length; i++) { if (t_ext.equals(allowedExt[i])) { errorFile = false; } } if (errorFile) { request.setAttribute("errorInfo", "不允许上传的文件格式！！！"); return mapping.findForward("error"); } long now = System.currentTimeMillis(); // 根据系统时间生成上传后保存的文件名 // 保存的最终文件完整路径,保存在web根目录下的uploadfile目录下 String fame = t_name; String u_name = application.getRealPath("/") + "uploadfile\\" + fame; try { // 保存文件 fileItem.write(new File(u_name)); System.out.println("\n文件上传成功！！！\n已保存为: " + u_name + "\n文件大小: " + size + "字节\n"); } catch (Exception e) { request.setAttribute("errorInfo", e.getMessage()); return mapping.findForward("error"); } request.setAttribute("filePath", u_name); } return this.toPageView(mapping, form, request, response); } public ActionForward toPageView(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) { String path = (String) request.getAttribute("filePath"); List<String> tableHead = new ArrayList<String>(); // 表头 List<String> list = null;// 一行数据 PreparedStatement stmt = null; Map<Integer, List<String>> item = new HashMap<Integer, List<String>>(); // 整张表 try { Workbook book = Workbook.getWorkbook(new File(path)); // get a Sheet object. Sheet sheet = book.getSheet(0); int columns = sheet.getColumns(); int rows = sheet.getRows(); // get 1st-Column,1st-Row content. for (int i = 0; i < rows; i++) { list = new ArrayList<String>();// 新建一行数据存放 for (int j = 0; j < columns; j++) { Cell cell = sheet.getCell(j, i); String result = cell.getContents() == null || cell.getContents() == "" ? "-" : cell .getContents(); //如果某个单元格为空则以“-”代替 list.add(result); if (i == 0) { tableHead.add(result); // 添加表头 } } item.put(i, list); } book.close(); } catch (Exception e) { request.setAttribute("errorInfo", e.getMessage()); return mapping.findForward("error"); } StringBuffer tempStr1 = new StringBuffer(""); for (int i = 0; i < tableHead.size(); i++) { String str = tableHead.get(i); if (i == tableHead.size() - 1) { tempStr1.append(str + " varchar(200)"); break; } tempStr1.append(str + " varchar(200),"); } try { String sql = "create table " + request.getAttribute("tableName") + "(id int identity(0,1) primary key," + tempStr1 + ")"; try { stmt = DBManager.getDBConnection().prepareStatement(sql); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block request.setAttribute("errorInfo", e.getMessage()); return mapping.findForward("error"); } int res = stmt.executeUpdate(); if (res > 0) { System.out.println("创建表" + request.getAttribute("tableName") + "失败！\n"); } else { System.out.println("创建表" + request.getAttribute("tableName") + "成功！\n"); } DBManager.closeDB(); } catch (SQLException e) { request.setAttribute("errorInfo", e.getMessage()); return mapping.findForward("error"); } StringBuffer tempStr2 = new StringBuffer(""); int index = 0; //控制忽略表头插入数据库 for (List<String> templist : ite