在做web開發時，我們經常會用到資料表格的導入導出功能，這樣可以幫我們節省人工操作的時間，極大提高辦事效率，下面，直入正題：
筆者所做的導入導出是針對java springMVC架構、工作簿poi以及前端plupload.js插件設計的。
第一步、總體介紹
首先，來看頁面展示，如下圖：
導入菜單包含模闆下載下傳和導入資料，如下圖：
點選模闆下載下傳，彈出模闆下載下傳對話框，如下圖：
點選導入資料，彈出導入對話框，如下圖：
接下來，再來看看導出，包含導出目前頁和導出全部，如下圖：
點選導出目前頁或是導出全部後，生成excel，如下圖：
最後再來看看導入模闆和生成的excel，如下圖：
我的模闆是放在src/main/webapp/template下的。
第二步、模闆下載下傳和導入
首先，來看看前台導入、導出的jsp代碼：
<!-- 導出，目前頁和頁大小隐藏域 -->
<input type="hidden" name="currentPage" id="current" value="0"/>
<input type="hidden" name="pageSize" id="size" value="0"/>
ID：<input type="text" name="id" id="userId"/> 姓名：<input type="text" name="userName"/> <a href="javascript:void(0);" id="search">搜尋</a>
</form>
</div>
<!-- 導入 -->
<a
type="import"
id="import"
class="easyui-menubutton"
suffix="xls"
resouseCode="user"
importurl="sys/user/importData.do"
callback="myCallback">導入</a>
<!-- 導出 -->
<a type="export" id="export" class="easyui-menubutton">導出</a>
然後，再來看看js：
$(function() {
var uploadsubmenu = '<div id="upload_sub" style="width:100px;">'+
'<div id="download_templ"><span class="exportDownload"></span>模闆下載下傳</div>'+
'<h1 class="excel-line" id="line1"></h1>'+
'<div id="import_data"><span class="exportData"></span>導入資料</div>'+
'</div>';
if($("#upload_sub").length<){
$("body").append(uploadsubmenu);
}
//将導入設為菜單按鈕
var importMenu = $('#import').menubutton({
menu: "#upload_sub",
iconCls:"icon-import"
});
//為子菜單添加點選事件
$(importMenu.menubutton('options').menu).menu({
onClick: function (item) {
if(item.id == 'download_templ'){
var resouseCode = $('#import').attr('resouseCode');
var suffix = $('#import').attr('suffix');
loadTemplate(resouseCode, suffix);
}
if(item.id == 'import_data'){
var importurl = $('#import').attr('importurl');
var callback = $('#import').attr('callback');
var resouseCode = $('#import').attr('resouseCode');
makerUpload(importurl, callback, resouseCode);
}
}
});
});
/**
* 模闆下載下傳
*/
function loadTemplate(resouseCode, suffix) {
//檔案名+字尾名
var fileName = resouseCode + '.' + suffix
var templatediv = '<div id="templatediv" style="width:400px;height:200px;">'+
'</div>';
if (window.top.$('#templatediv').length == ) {
window.top.$("body").append(templatediv);
}
var templistspan = "";
templistspan +='<div class="download-list">'
templistspan += '<span><a id="downloadBtn" href="file/fileHandle/downloadtemplate.do?fileName='+encodeURI(encodeURI( fileName))+'">'+fileName+'</a></span>'
templistspan +='</div>';
window.top.$("#templatediv").html(templistspan);
//彈出下載下傳模闆
window.top.$("#templatediv").dialog({
title:"下載下傳模闆",
modal:true
});
//下載下傳模闆完了就關閉對話框
window.top.$("#downloadBtn").click(function() {
window.top.$("#templatediv").dialog('close');
});
}
/**
* 上傳導入
*/
function makerUpload(importurl, callback, resouseCode) {
console.log('彈出導入對話框。。');
//使用者詳情對話框
var importWindow = window.top.$('#importWin');
if(importWindow.length <= ) {
importWindow = window.top.$("<div id='importWin'/>").appendTo(window.top.document.body);
}
importWindow.window({
title:'導入',
closed: false,
closable: true,
draggable: true,
resizable: true,
width: ,
height: ,
modal: true,
href: 'sys/redirect/redirectHomePage.do?path=import&callback=' + callback + '&resouseCode=' + resouseCode + '&importurl=' + importurl,
onClose: function() {
importWindow.window("destroy");
}
});
}
然後，再來看看導出對話框對應的jsp頁面import.jsp：
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String callback = request.getParameter("callback");
String importurl = request.getParameter("importurl");
String resouseCode = request.getParameter("resouseCode");
System.out.println("callback:" + callback);
System.out.println("importurl:" + importurl);
System.out.println("resouseCode:" + resouseCode);
%>
<link rel="stylesheet" href="js/plugs/plupload/queue/css/jquery.plupload.queue.css" type="text/css"></link>
<script type="text/javascript" src="js/plugs/plupload/plupload.js"></script>
<script type="text/javascript" src="js/plugs/plupload/plupload.html4.js"></script>
<script type="text/javascript" src="js/plugs/plupload/plupload.html5.js"></script>
<script type="text/javascript" src="js/plugs/plupload/plupload.flash.js"></script>
<script type="text/javascript" src="js/plugs/plupload/zh_CN.js"></script>
<script type="text/javascript" src="js/plugs/plupload/queue/jquery.plupload.queue.js"></script>
<script type="text/javascript">
var callback = '<%=callback%>';
var resouseCode = '<%=resouseCode%>';
var importurl = '<%=importurl%>';
console.log('callback:', callback);
console.log('resouseCode:', resouseCode);
console.log('importurl:', importurl);
</script>
<!-- <div style="width:100%;height:100%;position:relative;">
<button id="startImport" style="position:relative;top:224px;left:50px;height:20px;">開始導入</button>
</div> -->
<form id="form">
<div id="uploader">
</div>
</form>
<script type="text/javascript" src="resources/logicJS/common/common.js"></script>
<script type="text/javascript" src="resources/logicJS/common/import.js"></script>
再來看看其對應的import.js:
/*$(function() {
console.log('windows是否一緻', window === window.top);
$('#startImport').click(function() {
console.log('開始導入');
var iframe = $('#mainPanel > iframe' ,window.top.document)[0];
var currentChildWindow = iframe.contentWindow || iframe.window;
console.log('callback', callback);
currentChildWindow[callback]('qiyongkang');
});
});*/
$(function() {
var files = [];
var errors = [];
var type = 'file';
var chunk = true;
var startbutton = null;
var des = "<div class='upload-sm'>";
des += "單個檔案最大不超過1mb; ";
des += "批量上傳檔案個數不超過1個; ";
des += "上傳檔案格式為:xls";
des += "</div>";
$("#uploader").pluploadQueue($.extend({
runtimes : 'flash,html4,html5',
url : 'file/fileHandle/upload.do?resouseCode=' + resouseCode,
max_file_size : '1mb',
file_data_name:'file',
filters : [],
dragdrop : false,
flash_swf_url : 'js/plugs/plupload/plupload.flash.swf',
init:{
Init:function(uploader){
//開始上傳按鈕
startbutton = $(".plupload_start");
$(".plupload_header").html(des);
},
QueueChanged:function(uploader){
if(uploader.files.length > ){
var error = "<div class='upload-sm'>";
error += "<font color=red>最多隻能上傳1個</font>";
error += "</div>";
$(".plupload_header").html(error);
startbutton.hide();
}else{
$(".plupload_header").html(des);
startbutton.show();
}
},
FileUploaded:function(uploader,file,response){
if(response.response){
var rs = $.parseJSON(response.response);
console.log(rs);
if(rs.status){
files.push(file.name);
}else{
errors.push(file.name);
}
}
},
UploadProgress:function(uploader,fs){
var begin = "<div class='upload-sm'>";
begin +="開始上傳檔案<img src='js/plugs/plupload/queue/img/uploading.gif'>";
begin +="</div>";
$(".plupload_header").html(begin);
},
//上傳完畢後觸發
UploadComplete:function(uploader,fs){
var e= errors.length ? ",失敗"+errors.length+"個("+errors.join("、")+")。" : "。";
var begin = "<div class='upload-sm'>";
var value=parseInt(fs.length)-parseInt(errors.length);
begin +="上傳完成！共"+fs.length+"個。成功"+value+"個";
begin +="</div>";
$(".plupload_header").html(begin);
importdata(importurl,callback);
}
}
},(chunk ? {chunk_size:'1mb'} : {})));
});
/**
* 導入資料
* @param importurl
* @param callback
*/
function importdata(importurl,callback){
var des = "<div class='upload-sm'>";
des +="正在解析入庫<img src='js/plugs/plupload/queue/img/uploading.gif'>";
des +="</div>";
$(".plupload_header").html(des);
$.ajax({
url:importurl,
type:"POST",
dataType: "json",
success: function (data) {
var msg = "<div class='upload-sm'>";
msg +=data.msg;
msg +="</div>";
$(".plupload_header").html(msg);
//回調，重新加載資料
var iframe = $('#mainPanel > iframe' ,window.top.document)[];
var currentChildWindow = iframe.contentWindow || iframe.window;
console.log('callback', callback);
currentChildWindow[callback]('qiyongkang');
}
});
}
其中的chunk的含義，筆者稍作解釋，如果檔案的大小超過chunk_size，那麼此檔案将分多次請求上傳，待會兒就可以看到背景處理上傳的請求邏輯。
第三步、導出目前頁和導出全部
對應的jsp代碼已在第二步給出，再來看導出對應的js:
/**
* 初始化導出按鈕
*/
function initMyExportMenu(){
var exportsubmenu = '<div id="exportMenu_sub">'+
'<div id="export_crrent"><span class="exportcurrent"></span>導出目前頁</div>'+
'<h1 class="excel-line"></h1>'+
'<div id="export_all"><span class="exportall"></span>導出全部</div>'+
'</div>';
$("body").append(exportsubmenu);
var exports = $("a[type='export']");
$.each(exports,function(data){
var exportMenu = $(this).menubutton({
menu: "#exportMenu_sub",
iconCls:"icon-export"
});
$(exportMenu.menubutton('options').menu).menu({
onHide:function(){
},
onClick: function (item) {
if(item.id=="export_crrent"){
console.log('導出目前頁');
var options = $('#userListTab').datagrid('getPager').data("pagination").options;
var curr = options.pageNumber;
console.log('目前頁：', curr);
var pageSize = options.pageSize;
console.log('頁大小', pageSize);
//通過隐藏域傳參數
$('#current').val(curr);
$('#size').val(pageSize);
$("#queryForm").form('submit',{
url : 'sys/user/exportData.do',
onSubmit : function() {
return $(this).form('validate');
},
success : function(data) {
}
});
}
if(item.id=="export_all"){
//通過隐藏域傳參數
$('#current').val();
$('#size').val();
console.log('導出全部');
$("#queryForm").form('submit',{
url : 'sys/user/exportData.do',
onSubmit : function() {
return $(this).form('validate');
},
success : function(data) {
console.log('data', data);
}
});
}
}
});
});
}
/**
* 回調函數
* @returns
*/
function myCallback(name) {
console.log('回調函數執行。。' + name);
//重新加載datagrid
$("#userListTab").datagrid('reload');
}
上面的回調函數是用于導入成功後重新加載datagrid。
第四步、導入導出的背景處理邏輯
首先，來看看UserController.java對應的代碼：
/**
*
* importTeleFraudEvent:導入資料. <br/>
*
* @author qiyongkang
* @param request
* @return
* @since JDK 1.6
*/
@RequestMapping
@ResponseBody
public ExtJsObject importData(HttpServletRequest request) {
ExtJsObject result = new ExtJsObject(false, "導入不成功", null);
try {
String path = request.getSession().getAttribute("justpath").toString();
File f = new File(path);
FileInputStream fis = new FileInputStream(f);
HSSFWorkbook book = new HSSFWorkbook(fis);
result = userService.importData(book);
} catch (Exception e) {
e.printStackTrace();
return renderObject(false, "導入不成功", null);
}
return result;
}
@RequestMapping
@ResponseBody
public ExtJsObject exportData(User user, HttpServletRequest request,
HttpServletResponse response) {
List<User> userList = null;
try {
if (user.getCurrentPage() != null && !user.getCurrentPage().equals()) {
// 導出目前頁
userList = userService.getPageList(user);
} else {
// 導出所有
userList = userService.listAll(user);
}
if (userList != null && userList.size() > ) {
// 開始導出，擷取模闆路徑
String templatePath = request.getSession().getServletContext().getRealPath("template/user.xls");
// 寫入工作簿
userService.exportData(userList, templatePath, response);
}
} catch (Exception e) {
log.error("導出失敗", e);
return new ExtJsObject(false, "導出失敗", null);
}
return new ExtJsObject(true, "導出成功", null);
}
以及對應的service實作：
@Override
public ExtJsObject importData(HSSFWorkbook workBook) {
HSSFSheet sheet = workBook.getSheetAt();
int startReadRow = ;
int lastRowNum = sheet.getLastRowNum();
for(int i = startReadRow; i <= lastRowNum; i++) {
try {
User entity = new User();
HSSFRow row = sheet.getRow(i);
//使用者編号
long id = Long.valueOf(ExcelUtil.getCellValue(row.getCell()));
entity.setId(id);
//使用者姓名
String userName = ExcelUtil.getCellValue(row.getCell());
entity.setUserName(userName);
//年齡
int age = Integer.valueOf(ExcelUtil.getCellValue(row.getCell()));
entity.setAge(age);
userMapper.addUser(entity);
} catch (Exception e) {
e.printStackTrace();
return new ExtJsObject(true, "導入失敗", null);
}
}
return new ExtJsObject(true, "導入成功", null);
}
@Override
public List<User> getPageList(User user) {
return userMapper.listUser(user);
}
@Override
public void exportData(List<User> userList, String templatePath, HttpServletResponse response) throws Exception {
//擷取檔案輸入流，建立工作簿
FileInputStream fis = new FileInputStream(new File(templatePath));
HSSFWorkbook workBook = new HSSFWorkbook(fis);
//設定導出檔案名，并編碼
String fileName = "使用者資訊導出_" + System.currentTimeMillis()+".xls";
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
response.setContentType("application/octet-stream;charset=UTF-8");
//建立輸出流
OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
exportUserData(workBook, userList);
//删除模闆頁
workBook.removeSheetAt();
//将工作簿寫入輸出流
workBook.write(outputStream);
//關閉資源流
fis.close();
outputStream.flush();
outputStream.close();
}
/**
*
* exportUserData:将資料寫入工作簿. <br/>
*
* @author qiyongkang
* @param workBook
* @param userList
* @since JDK 1.6
*/
private void exportUserData(HSSFWorkbook workBook, List<User> userList) {
//克隆模闆sheet
HSSFSheet singleSheet = workBook.cloneSheet();
//設定克隆的sheet名稱
workBook.setSheetName(, "使用者資訊表");
//擷取cellStyle
HSSFCellStyle style = ExcelUtil.getStyle(workBook);
//定義起始行，從第二行開始
int singleRowIndex = ;
//開始周遊
for (User user : userList) {
try {
//起始列索引
int singleColIndex=;
//建立行
HSSFRow singleRow = singleSheet.createRow(singleRowIndex++);
//建立列
HSSFCell singleCell = null;
//使用者編号,0
singleCell=singleRow.createCell(singleColIndex++);
singleCell.setCellValue(user.getId());
singleCell.setCellStyle(style);
//使用者姓名,1
singleCell=singleRow.createCell(singleColIndex++);
singleCell.setCellValue(user.getUserName());
singleCell.setCellStyle(style);
//使用者年齡,2
singleCell=singleRow.createCell(singleColIndex++);
singleCell.setCellValue(user.getAge());
singleCell.setCellStyle(style);
} catch (Exception e) {
e.printStackTrace();
continue;
}
}
}
@Override
public List<User> listAll(User user) {
return userMapper.listAll(user);
}
再來看看模闆下載下傳以及檔案上傳的處理類FileHandleCtrl.java:
/**
* Project Name:qyk_testSpringMVC
* File Name:FileHandleCtrl.java
* Package Name:com.qiyongkang.file.ctrl
* Date:2015年11月1日下午3:01:05
* Copyright (c) 2015, CANNIKIN(http://http://code.taobao.org/p/cannikin/src/) All Rights Reserved.
*
*/
package com.qiyongkang.file.ctrl;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLDecoder;
import java.util.Iterator;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import com.qiyongkang.file.util.FileUtil;
import com.qiyongkang.sys.controller.BaseController;
/**
* ClassName:FileHandleCtrl <br/>
* Function: TODO ADD FUNCTION. <br/>
* Reason: TODO ADD REASON. <br/>
* Date: 2015年11月1日 下午3:01:05 <br/>
* @author qiyongkang
* @version
* @since JDK 1.6
* @see
*/
@Controller
@RequestMapping
public class FileHandleCtrl extends BaseController {
/**
*
* downloadtemplate:下載下傳模闆. <br/>
*
* @author qiyongkang
* @param request
* @param response
* @throws IOException
* @since JDK 1.6
*/
@RequestMapping
public void downloadtemplate(HttpServletRequest request, HttpServletResponse response) throws IOException {
response.setContentType("text/html;charset=utf-8");
request.setCharacterEncoding("UTF-8");
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
//擷取template的目錄路徑
String templatePath = request.getSession().getServletContext().getRealPath("template");
System.out.println("templatePath:" + templatePath);
String downLoadPath = templatePath + File.separator + URLDecoder.decode(new String(request.getParameter("fileName")), "UTF-8");
if("/".equals(File.separator)){
downLoadPath = downLoadPath.replace("\\", "/");
}
try {
File file = new File(downLoadPath);
long fileLength = file.length();
response.setContentType("application/x-msdownload;");
response.setHeader("Content-disposition", "attachment; filename="+new String(file.getName().getBytes("UTF-8"), "ISO8859-1" ));
response.setHeader("Content-Length", String.valueOf(fileLength));
bis = new BufferedInputStream(new FileInputStream(downLoadPath));
bos = new BufferedOutputStream(response.getOutputStream());
byte[] buff = new byte[];
int bytesRead;
while (- != (bytesRead = bis.read(buff, , buff.length))){
bos.write(buff, , bytesRead);
}
} catch (Exception e){
e.printStackTrace();
} finally {
if(bis != null){
bis.close();
}
if(bos != null){
bos.close();
}
}
}
/**
*
* upload:檔案上傳. <br/>
*
* @author qiyongkang
* @param request
* @param response
* @since JDK 1.6
*/
@RequestMapping
public void upload(HttpServletRequest request, HttpServletResponse response) {
String justpath = "";
String savePath = FileUtil.getResouseValue("uploadpath");
String filename = null;
// 目前正在處理的檔案分塊序号
int chunk = Integer.valueOf(request.getParameter("chunk"));
// 分塊上傳總數
int chunks = Integer.valueOf(request.getParameter("chunks"));
//檔案名
String resouseCode = request.getParameter("resouseCode");
// 判斷目前表單是否為"multipart/form-data"
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
try {
if (isMultipart) {
MultipartHttpServletRequest multiReq = (MultipartHttpServletRequest) request;
Iterator<?> i = multiReq.getFileNames();
while (i.hasNext()) {
multiReq.getAttribute("chunk");
MultipartFile f = multiReq.getFile((String) i.next());
InputStream input = f.getInputStream();
// 檔案名
filename = f.getOriginalFilename();
if (!FileUtil.savedFileNameCache.containsKey(filename)) {
FileUtil.savedFileNameCache.put(filename,
FileUtil.getSavedFileName(filename));
}
// 儲存檔案目錄絕對路徑
File dir = new File(savePath+"/"+resouseCode);
if (!dir.isDirectory() || !dir.exists()) {
dir.mkdirs();
}
// 儲存檔案絕對路徑
justpath = dir + "/"+ FileUtil.savedFileNameCache.get(filename);
if (chunk == ) {
File file = new File(justpath);
if (file.exists()) {
file.delete();
}
// 上傳檔案
FileUtil.uploadFile(input, justpath);
}
if (chunk > ) {
// 追加檔案
FileUtil.uploadFile(input, justpath, true);
}
if (chunk + == chunks || chunks == ) {
FileUtil.savedFileNameCache.remove(filename);
break;
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
request.getSession().setAttribute("justpath", justpath);
}
}
第五步、總結
其實，導入導出的核心邏輯就是首先上傳檔案，然後再對檔案進行讀寫，而與excel操作相關的類就是工作簿類了。
以上的導入導出，筆者講得很概括，隻是貼出了代碼，具體的含義還得大家去領悟，由于不同的架構所使用的導入導出有可能不一緻，不過大體的思路應該是一緻的，有興趣的小夥伴可以自己去嘗試下，挺有意思的！