JXL(针对Excel操作)系列之五:简单的(WEB)导入操作

     今天开始到过年,没有什么事情做,所以就打算学点.net的东西,也许就是快过年的缘故,所以没有什么事情可以做.公司也许就等着放假了,而我还得干点什么,混时间也得找点事情来做.所以还是把上次没有写完的最后一个写完吧.不管怎么样,也算是对今年的一个交代了.

 

     这个是最后一个关于JXL操作Excel-->WEB导入的例子了也许还是那样的简单,不过都是大同小异的,跟那天写的导出其实一个样.这里只是读数据写数据上方法不一样罢了.流程其实差不多,软件工程,数据结构这里用不了多少,呵呵,看来我都快成为一个低级程序员了.

 

     上次写过一个导入的操作,不过是关于EXT做的导入的例子,其实这次跟上次的差不多,应该说是一个模子出来的,但是还是提出来单独写一下,因为上次包含了其他的东西,这次就不会了,这次就是单纯的导入操作.

 

     周末放了两天假,呵呵,也就那样的胡乱过去了,浪费了不少时间.想想以前的生活,呵呵,昨天看了那个囧男孩,就不发表个人评论了.因为很多东西,说与不说,其实分别不大.

 

     好了,还是来说说程序吧.

 

 

      首先的流程是:页面显示所有的数据--->导入按钮--->选择文件--->导入--->先上传--->读取文件写入数据库--->返回页面显示全部.

 

       过程相当简单,就是普通的页面操作而已,如果要结合到其他的技术,运用复杂一点,当然也是可以的.这里只是说明简单的操作过程,这里保留了上次的导出操作,也就是添加了一个导入操作而已,这个导入是重新添加的一个servlet,然后,这个servlet指向页面的内容,具体的见程序.

 

 

 

首先是JSP-->

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="java.sql.*" %> <%String webapp=request.getContextPath(); %> <!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>简单的WEB导入</title> <mce:script type="text/javascript"><!-- //点击按钮就进行导出操作,简单吧,既不需要其他的操作,就是直接的简单的点击一下,其他就交给前台处理,当然只要有可能,你可以自己写不同的导出操作. function download(){ rul='<%=webapp%>/servlet/DownloadServlet?action=download'; window.location.href=rul; } // --></mce:script> </head> <body> <%-- 导入操作. --%> <!-- enctype=一定要设置正确"multipart/form-data". --> <form action="<%=webapp%>/servlet/WebUploadServlet" name="webupForm" method="POST" enctype="multipart/form-data"> <p align="center">导入操作:</p> <p align="center"> <input type="file" name="uploadFile"><font color="red">*请注意格式要正确</font> <!-- //这里同样,是一个简单的按钮就执行了所有的导入操作,这里导入放到后台处理,当然也可以直接在页面上处理.点击提交就可以了 --> <input type="submit" name="doUpload" value="导入" onclick="upload()"> </p> <%-- 导出操作+显示所有 --%> <% try{ Class.forName("com.mysql.jdbc.Driver").newInstance(); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:5885/excel","root","5885"); String sql = "select * from upload"; String sql1 = "select count(*) from upload"; Statement stat = conn.createStatement(); ResultSet resl = stat.executeQuery(sql1); resl.next(); int row = resl.getInt(1);//取得记录总数,可以不要,愿意想用其他的列表之类的列出来,后来用到了它自身的取得数据, //所以这个就是没有用的,但是后来没有删除掉,所以就保留着. resl = stat.executeQuery(sql); %> <p align="center">所有结果显示如下:</p> <p align="center"><input type="button" value="导出" onclick="download()"></input></p> <% if(row>0){ %> <table border="1" align="center" width="100%"> <tr><td width="30%" align="center"> ---编号--- </td><td width="40%" align="center"> ---姓名--- </td><td width="30%" align="center"> ---地址--- </td></tr> <% //for(int i = 0;i<=row;i++){ while(resl.next()){ %> <tr> <td><%=String.valueOf(resl.getInt(1)) %></td> <td><%=String.valueOf(resl.getString(2)) %></td> <td><%=String.valueOf(resl.getString(3)) %></td> </tr> <%} %> </table> <%}%> <% resl.close(); stat.close(); conn.close(); }catch(Exception e){ // } %> </form> </body> </html>

其次是WebUploadServlet-->

package upload; import java.io.File; import java.io.IOException; import java.io.PrintWriter; import java.sql.*; import java.util.*; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; 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.FileItemFactory; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; @SuppressWarnings("serial") public class WebUploadServlet extends HttpServlet { /** * Constructor of the object. */ public WebUploadServlet() { super(); } /** * Destruction of the servlet. <br> */ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } /** * The doGet method of the servlet. <br> * * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doPost(request, response); } /** * The doPost method of the servlet. <br> * * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); //首先是上传操作.这里直接就上传到服务器的目录下.然后读取它就可以了.这里仍然用file-upload Connection conn = null; Statement stat = null; ResultSet resl; PreparedStatement prepare = null; try{ Class.forName("com.mysql.jdbc.Driver").newInstance(); conn = DriverManager.getConnection("jdbc:mysql://localhost:5885/excel", "root", "5885"); stat = conn.createStatement(); }catch(Exception e){ // } //上传加导入 try{ boolean isMultipart = ServletFileUpload.isMultipartContent(request); if(isMultipart){ //这里直接做简单的上传,其他的格式不需要设置. FileItemFactory factory = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(factory); List /* FileItem */ items = upload.parseRequest(request); Iterator iter = items.iterator(); while(iter.hasNext()){ FileItem item = (FileItem) iter.next(); String saveFileName = String.valueOf(System.currentTimeMillis()); String save_name = request.getRealPath("/") + "Uploaded/" + saveFileName + ".xls"; File saveFile = new File(save_name); item.write(saveFile); Workbook wb = Workbook.getWorkbook(saveFile); String id = "";//这三个对应数据库的字段 String name = ""; String address = ""; Sheet sheet = wb.getSheet(0);//这里就不用数组了,因为只是第一页写数据就够了. int rowNum = sheet.getRows(); for(int iRow = 0;iRow < rowNum;iRow++){ Cell[] cells = sheet.getRow(iRow); if(cells!=null||cells.length>0){ id = cells[0].getContents(); name = cells[1].getContents(); address = cells[2].getContents(); System.out.println(id+"-----"+name+"----"+address); /**这里注意,自己写在Excel里面的数据不要出错,因为id是主键,当然,你可以不设置主键,字符长度设为"无限",要不然就自己写判断语句.冲突的进行处理.*/ String insertSql = "insert into upload(Id,name,address) values (?,?,?)"; prepare = conn.prepareStatement(insertSql); prepare.setInt(1, Integer.parseInt(id)); prepare.setString(2, name); prepare.setString(3, address); prepare.executeUpdate(); System.out.println("---success---"); //stat.executeUpdate(); } } } } }catch(Exception e){ } //定向到页面. this.getServletContext().getRequestDispatcher("/webupload/webupload.jsp").forward(request, response); } /** * Initialization of the servlet. <br> * * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }

然后是WEB.XML-->

<?xml version="1.0" encoding="UTF-8"?> <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <servlet> <servlet-name>Upload</servlet-name> <servlet-class>upload.Upload</servlet-class> </servlet> <servlet> <servlet-name>DownloadServlet</servlet-name> <servlet-class>download.DownloadServlet</servlet-class> </servlet> <servlet> <servlet-name>WebUploadServlet</servlet-name> <servlet-class>upload.WebUploadServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>Upload</servlet-name> <url-pattern>/servlet/Upload</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>DownloadServlet</servlet-name> <url-pattern>/servlet/DownloadServlet</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>WebUploadServlet</servlet-name> <url-pattern>/servlet/WebUploadServlet</url-pattern> </servlet-mapping> <listener> <listener-class> org.apache.commons.fileupload.servlet.FileCleanerCleanup </listener-class> </listener> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> </web-app>

差不多就这些了,算是自己的起步操作吧,当然很不完善,不过实际当中,自己完善其中的操作绝对行的.