jxl学习笔记

 实现将Excel表导入到Mysql库

一.将文件上传到服务器,fileupload

1.引入相关JAR commons-fileupload-1.2.jar,commons-io-1.3.2.jar

2.检测文件格式等,上传到服务器文件夹

  path = getServletContext().getRealPath("uploadfile");//取得服务器端存取文件的文件夹路径

 

   DiskFileItemFactory factory = new DiskFileItemFactory();

   factory.setSizeThreshold(4096); // 设置缓冲区大小

   ServletFileUpload upload = new ServletFileUpload(factory);

   upload.setSizeMax(4194304);//设置上传文件的最大值

   List items = upload.parseRequest(request);// 得到所有的文件

   Iterator i = items.iterator();

   while(i.hasNext()){

        FileItem fi = (FileItem) i.next();

        String fileName = fi.getName();

        String postfix = fileName.substring(fileName.length() - 3);

        if (!postfix.equals("xls"))
            {

                out.print("<div style='padding-top:5px;padding-bottom:5px;' align=center>");
                out.print("<font size=2></font><a href='excel.jsp'><fontsize=2 color=red>选择其他格式重新上传</font></a>");
                out.print("</div>");
            } else {
                if (fileName != null) {

                     File savedFile = new File(path, fileName );

                     fi.write(savedFile);

             }

                  …………

 

           }

   }

 

3.取得文件在服务端路径

 

二.读取上传的Excel表内容,插入库

1.引入相关JAR  jxl.jar

2.创建Workbook

       Workbook workbook = Workbook.getWorkbook(new java.io.File(path));

3.取得工作表Sheet

       Sheet sheet = workbook.getSheet(0);

    或Sheet sheet = workbook.getSheet("name");

4.取得单元格内容Cell

      Cell cell = sheet.getCell(1,1); //第一个参数为列,第二个参数为行,起始值为0

      String contents = cell.getContents();

5.取得数据库表各字段数据格式,将contents转换格式,生成相应Sql语句

      ResultSet rs = dc.executeQuery("select * from tablename limit 1");

      switch (rs.getMetaData().getColumnType(columnNum)) {

          case:CHAR:

                   ……break;

      }

6.执行，插入库