java 将Excel（CSV）导入数据库（上传、读取文件）

xustar

浏览: 141385 次
性别:
来自: 成都

最近访客更多访客>>

plumcrane

dzcdhj

mt25367117

程序员的道路才刚刚开始

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

javascript
项目知识

最近由于项目的需要，必须使用Excel导入数据。

经过一翻查找，确定了思路：首先从客户端将文件上传到服务器，再有服务器读取，最后通过方法写入数据库。（更早的时候尝试过，在客户端获取文件的绝对路径，传传到服务器端直接读取写入数据库。事实证明这种做法是不可取的。首先必须得很纠结得去获取路径，最关键是服务器是无法直接去操作客户端的文件。）在这之前的一个Flex项目中曾经写过WORD导出数据，那个时候也是分为两步，首先将文件导出到服务器端得文件夹，再通过Flex所有的下载组件下载到客户端。

上传时使用插件：commons-fileupload-1.2.jar

读取Excel文件使用插件：jxl.jar

上传页面：

   <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">
   <script type="text/javascript">
         $(function() {
    $('#custom_file_upload').uploadify({
      'uploader'       : 'map/js/jquery.uploadify-v2.1.4/uploadify.swf',
      'script'         : 'servlet/PlateTypeServlet',//所调用的servlet
      'cancelImg'      : 'map/js/jquery.uploadify-v2.1.4/cancel.png',
    // 'folder'         : '/uploads',
       //'fileDataName'   : 'uploadify',
      'multi'          : false,
      'auto'           : false,
      'fileExt'        : '*.jpg;*.csv;*.xls',
      'fileDesc'       : 'Image Files (.csv,.xls)',
      'queueID'        : 'custom-queue',
      'queueSizeLimit' : 1,
      'simUploadLimit' : 1,
      'buttonText': 'browse',
       'removeCompleted': true,
       'sizeLimit': 512000,
      'onSelectOnce'   : function(event,data) {
          $('#status-message').text(data.filesSelected + ' 份文件等待上传……');
        },
        //'onComplete' : function(evt, queueID, fileObj, response, data){
       // $("#backImage").append("<div>"+response+"</div>");
       // },
      'onAllComplete' : function(event,data) {
          $('#status-message').text(data.filesUploaded + ' 份文件已经上传, ' + data.errors + ' 错误.');
          getPlatec(1);
        }
    });
   });
    </script>
<style type="text/css">
#plan_ly_custom-queue {
   border: 1px solid #E5E5E5;
   height: 20px;
margin-bottom: 10px;
   width: 280px;
}
</style>
</head>
<body>
<form id="plateinfo" method="post" enctype="multipart/form-data">
<table width="600" border="0" align="center" cellpadding="0" cellspacing="1" class="f12">

<tr>
    <td>
      <table width="95%" border="0" align="center" cellpadding="5" cellspacing="0" class="f12">
        <tr>
          <td width="90" align="left" valign="bottom">EXCEL 内部数据样式：</td>

        </tr>

      </table>
      <table id="" width="93%" border="0" id="unit2" align="center" cellpadding="3" cellspacing="1" bgcolor="#B7BABC" class="f12">

       <tr>
          <td align="center" bgcolor="#FFE6BF" class="f12"><strong>类型编码</strong></td>
          <td align="center" bgcolor="#FFE6BF" class="f12"><strong>车牌类别</strong></td>
          <td align="center" bgcolor="#FFE6BF" class="f12"><strong>类型备注</strong>
        </td>
        </tr>

      <tr>
            <td align="center" bgcolor="#FFFFFF" class="" style="cursor:pointer" onclick="" title="">
              <input name="" type="text" value="" size="22" />
            </td>
            <td align="center" bgcolor="#FFFFFF" class="" title="">
            <input name="" type="text" value="" size="22" /> </td>
            <td align="center" bgcolor="#FFFFFF">
            <input name="" type="text" value="" size="22" /> </td>
        </tr>
             <tr>
            <td align="center" bgcolor="#FFFFFF" class="" style="cursor:pointer" onclick="" title="">
              <input name="" type="text" value="" size="22" />
            </td>
            <td align="center" bgcolor="#FFFFFF" class="" title="">
            <input name="" type="text" value="" size="22" /> </td>
            <td align="center" bgcolor="#FFFFFF">
            <input name="" type="text" value="" size="22" /> </td>
        </tr>

      </table>


       <table width="95%" border="0" align="center" cellpadding="2" cellspacing="0" class="f12">
        <tr>
          <td>
         <div id="status-message">选择文件上传:</div>

</html>
所调用servlet ：

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

  response.setContentType("text/html");
  PrintWriter out = response.getWriter();
  out
    .println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
  out.println("<HTML>");
  out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
  out.println(" <BODY>");
  out.print("    This is ");
  out.print(this.getClass());
  out.println(", using the POST method");
  out.println(" </BODY>");
  out.println("</HTML>");
  out.flush();
  out.close();
}
public void service (HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException{
  request.setCharacterEncoding("utf-8");
  response.setContentType("text/html");
  DiskFileItemFactory fac = new DiskFileItemFactory();
  PrintWriter out = response.getWriter();

  ServletFileUpload upload = new ServletFileUpload(fac);

  upload.setHeaderEncoding("utf-8");

  IPlateColorService Service = (IPlateColorService)SpringContext.getBean(IPlateColorService.class);

  List fileList = null;//Spring 中使用注解后实例化service接口
  try {
      fileList = upload.parseRequest(request); //获取附件数据
  } catch (FileUploadException ex) {
      return;
  }
  Iterator<FileItem> it = fileList.iterator();
  String uploadPath = null;
  String filename = ""; //id
  ServletContext sc= this.getServletContext();
  uploadPath = sc.getRealPath("/");
  uploadPath += "bayonet_file\\excel\\";
  //System.out.println(uploadPath);

  while (it.hasNext()) {
      FileItem item = it.next();

      if (!item.isFormField()) {
            File file=new File(uploadPath);
            filename = item.getName();
            if(!file.exists()){
               file.mkdir();
            }
              try {
      item.write(new File(uploadPath+filename));
     } catch (Exception e) {
      e.printStackTrace();
     }
            }
      out.print(true);
  }
  String filePath = uploadPath+filename;
  //System.out.println("==="+filePath);
  try {
   Service.addExcel(filePath);//调用service方法。
  } catch (Exception e) {
   e.printStackTrace();
  }

所调用的service方法：

public void addExcel(String filepath){
  JxlRead jr= new JxlRead();
  try{
   jr.readExcel(filepath);
      }catch(Exception e){
     e.printStackTrace();
      }


}

读取Excel 类并且调用方法写入：

public class JxlRead {

public void readExcel(String filePath) throws Exception {
        InputStream is = null;
        Workbook workbook = null;
       try {
            is = new FileInputStream(filePath);
            workbook = Workbook.getWorkbook(is);
            // sheet row column 下标都是从0开始的
            Sheet sheet = workbook.getSheet(0);

            int column = sheet.getColumns();
            int row = sheet.getRows();
            System.out.println("共有" + row + "行，" + column + "列数据");
            plateType pt= new plateType();
            IPlateTypeService pts = (IPlateTypeService)SpringContext.getBean(IPlateTypeService.class);

            for(int i=1;i<row;i++){
            for(int j=0;j<column;j++){
              if(j==0){
               Cell cellA1 = sheet.getCell(0, i);
                  pt.setTbpt_id(cellA1.getContents());
              }
              if(j==0){
               Cell cellA1 = sheet.getCell(1, i);
                  pt.setTbpt_name(cellA1.getContents());
              }
              if(j==0){
               Cell cellA1 = sheet.getCell(2, i);
                  pt.setTbpt_memo(cellA1.getContents());
              }

            }
            pts.addEntity(pt);
            }
            // A1是字符
            //Cell cellA1 = sheet.getCell(0, 0);
           // System.out.println("A1 type:" + cellA1.getType());
          // if (cellA1.getType().equals(CellType.LABEL)) {
           //     System.out.println("A1 content:" + cellA1.getContents());
          // }

            // 操作完成时，关闭对象，释放占用的内存空间
            workbook.close();
            is.close();
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (is != null) {
                is.close();
            }
        }
    }

    /**
     * @param args
     * @throws Exception
     */
// public static void main(String[] args) throws Exception {
//      String filePath = "D:\\test\\testjxlread.xls";
   //     JxlRead jxlRead = new JxlRead();
   //     jxlRead.readExcel(filePath);
// }
}

jxl.jar (708.7 KB)
下载次数: 82

commons-fileupload-1.2.jar (51.8 KB)
下载次数: 53

分享到：

编程命名规范（匈牙利命名法）C/C++ | Oracle 权限浅析

2011-07-07 18:38
浏览 16444
评论(0)
分类:Web前端
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论