`

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

 
阅读更多

    最近由于项目的需要,必须使用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>

      <div id="custom-queue"></div>
     <input id="custom_file_upload" type="hidden"/>
          </td>
         
        </tr>
     
      </table> 
      <table width="90%" border="0" align="center" cellpadding="0" cellspacing="0" class="f12">
        <tr>
          <td height="40" align="right" valign="middle">
                <img src="bayonet_images/right_34.gif" width="70" onclick="javascript:jQuery('#custom_file_upload').uploadifyUpload()"  height="22" border="0" />   
               <img src="bayonet_images/right_15.gif" width="70" onclick="close_plateinfo()" height="22" border="0" /> 
                </td>
        </tr>
      </table>
      </td>
  </tr>
</table>
</form>
</body>

</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
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics