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);
// }
}