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" border="0" align="center" cellpadding="5" cellspacing="0" align="left" valign="bottom">EXCEL 内部数据样式：</td>
????????
??????? </tr>
?????
????? </table>
????? <table id="" width="93%" border="0" id="unit2" align="center" cellpadding="3" cellspacing="1" bgcolor="#B7BABC" bgcolor="#FFE6BF" bgcolor="#FFE6BF" bgcolor="#FFE6BF" bgcolor="#FFFFFF" style="cursor:pointer"? onclick="" title="">
????????????? <input name="" type="text"? value="" size="22" />
??????????? </td>
??????????? <td align="center" bgcolor="#FFFFFF" 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" style="cursor:pointer"? onclick="" title="">
????????????? <input name="" type="text"? value="" size="22" />
??????????? </td>
??????????? <td align="center" bgcolor="#FFFFFF" 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" type="hidden"/>
????????? </td>
?????????
??????? </tr>
?????
????? </table>?
????? <table width="90%" border="0" align="center" cellpadding="0" cellspacing="0" 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);?
? //? }?
?}
