用JXLS操作进行excel操作

【准备工作】
软件环境：jxls-core-0.9.5.jar,spring2.0,jspSmartUpload.jar.
首先需要新建一个XML文件和excel模板。(说明XML文件是为了写入数据库的准备的excel模板为了导出数据)可以在应用根目录下新建一个excel文件夹把这两个文件放在这个文件夹下。XML文件格式如下：
  【导入需要的XML文件 】：
<?xml version="1.0" encoding="UTF-8"?>
<workbook>
<worksheet name="Sheet1">
<section startRow="0" endRow="0">
</section>
<loop startRow="1" endRow="1" items="orgs"
var="org" varType="com.ultrapower.accredit.common.value.Organise">
<section startRow="1" endRow="1">
<mapping row="1" col="0">org.dept_id</mapping>
<mapping row="1" col="1">org.super_id</mapping>
<mapping row="1" col="2">org.dept_name</mapping>
<mapping row="1" col="3">org.node</mapping>
<mapping row="1" col="4">org.org_phone</mapping>
<mapping row="1" col="5">org.org_fax</mapping>
</section>
<loopbreakcondition>
<rowcheck offset="0">
<cellcheck offset="0">
end
</cellcheck>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>
</workbook>
导出excel表格的格式为EL表达式为类名.要导出的属性名。网上搜索下就知道怎么写了。
【后台方法】
  【操作EXCEL接口：】
public interface ExcelManagerHandler {
public boolean writeExcle(List dataList, String exportFileUrl);
public List readExcel(InputStream inputXLS);
}
【实现接口方法:】
public class OrganiseExcelManagerHandlerImpl implements ExcelManagerHandler {
private String excleModelPath = "excel/organise.xls";// 导出excel模板

private String xmlModelPath = "excel/organise.xml";// 导入XML模板

private static String path;

static {
path = new OrganiseExcelManagerHandlerImpl().getClass()
.getResource("/").getPath();
path = path.substring(1, path.indexOf("WEB-INF"));
}

public List readExcel(InputStream inputXLS) {
try {

if (inputXLS != null) {
InputStream inputXML = new BufferedInputStream(
new FileInputStream(path + xmlModelPath));

XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);

List orgs = new ArrayList();
Map beans = new HashMap();
beans.put("orgs", orgs);
XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
return orgs;
} else {

}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}

public boolean writeExcle(List dataList, String exportFileUrl) {

Map<String, List<?>> beans = new HashMap<String, List<?>>();
beans.put("organise", dataList);

XLSTransformer transformer = new XLSTransformer();

try {
transformer.transformXLS(path + excleModelPath, beans, path+ exportFileUrl);
return true;
} catch (ParsePropertyException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

return false;
}

public String getExcleModelPath() {
return excleModelPath;
}

public void setExcleModelPath(String excleModelPath) {
this.excleModelPath = excleModelPath;
}

public String getXmlModelPath() {
return xmlModelPath;
}

public void setXmlModelPath(String xmlModelPath) {
this.xmlModelPath = xmlModelPath;
}

}
【控制器里的代码：导出EXCEL】
public class ExportOrganiseController  extends BaseRedirectController{
private static Logger logger = Logger.getLogger(ExportOrganiseController.class);
private OrganiseManagerHandler organiseManagerHandler; //具体操作对象接口
private ExcelManagerHandler organiseExcelManager;
private String exportFileUrl;
protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception {
List list = organiseManagerHandler.getOrganises();
organiseExcelManager.writeExcle(list,exportFileUrl);
return null;
}

public void afterPropertiesSet() throws Exception {

}
public OrganiseManagerHandler getOrganiseManagerHandler() {
return organiseManagerHandler;
}
public void setOrganiseManagerHandler(
OrganiseManagerHandler organiseManagerHandler) {
this.organiseManagerHandler = organiseManagerHandler;
}

public ExcelManagerHandler getOrganiseExcelManager() {
return organiseExcelManager;
}
public void setOrganiseExcelManager(ExcelManagerHandler organiseExcelManager) {
this.organiseExcelManager = organiseExcelManager;
}
public String getExportFileUrl() {
return exportFileUrl;
}
public void setExportFileUrl(String exportFileUrl) {
this.exportFileUrl = exportFileUrl;
}

}
【导入EXCEL：】
public class Affix  implements Serializable {
/**
* 附件ID
*/
private String id = "";
/**
*附件名称
*/
private String affixName = "";
/**
*附件路径
*/
private String url = "";
/**
*附件类型
*/
private String type = "";
/**
*用于临时存放项目物理路径
*/
private String path = "";//
/**
*上传文件
*/
private MultipartFile file;//上传文件
public Affix() {
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getPath() {
return path;
}
public void setPath(String path) {
this.path = path;
}
public MultipartFile getFile() {
return file;
}
public void setFile(MultipartFile file) {
this.file = file;
}
public String getAffixName() {
return affixName;
}
public void setAffixName(String affixName) {
this.affixName = affixName;
}
}


public class ImportOrganiseController  extends BaseFormController {
private OrganiseManagerHandler organiseManagerHandler;
private ExcelManagerHandler organiseExcelManager;
protected Object formBackingObject(HttpServletRequest request) throws Exception {
Affix affix = new Affix(); //文件操作类
return affix;
}
protected ModelAndView onSubmit(HttpServletRequest request, HttpServletResponse response,
Object command, BindException errors) throws Exception {

Affix affix = (Affix)command;
MultipartFile file = affix.getFile();

InputStream inputXLS = file.getInputStream();

List organiseList = organiseExcelManager.readExcel(inputXLS);

// System.err.println(accountList);
if(organiseList == null){
request.setAttribute("info", "导入数据空");
}else{
String info = organiseManagerHandler.addOrganise_BS(organiseList);
String showInfo = i18n("security_new.importexcel");
request.setAttribute("info", info);
}

return new ModelAndView(getSuccessView());
}
public void afterPropertiesSet() throws Exception {

}
public ExcelManagerHandler getOrganiseExcelManager() {
return organiseExcelManager;
}
public void setOrganiseExcelManager(ExcelManagerHandler organiseExcelManager) {
this.organiseExcelManager = organiseExcelManager;
}
public OrganiseManagerHandler getOrganiseManagerHandler() {
return organiseManagerHandler;
}
public void setOrganiseManagerHandler(
OrganiseManagerHandler organiseManagerHandler) {
this.organiseManagerHandler = organiseManagerHandler;
}

}
【BEAN的配置】
<bean id="organiseExcelManager" class="com.ultrapower.accredit.service.impl.OrganiseExcelManagerHandlerImpl">
<property name="excleModelPath" value="excel/organise.xls"/>
<property name="xmlModelPath" value="excel/organise.xml"/>
</bean>

<!--导出EXCEL配置-->
      <bean id="exportOrganise" class="com.ultrapower.accredit.controller.organise.ExportOrganiseController"> <property name="organiseManagerHandler" ref="OrganiseManagerHandlerImpl"></property>
  <property name="organiseExcelManager" ref="organiseExcelManager"></property>
  <property name="exportFileUrl" value="excel/organiseList.xls"></property>
    </bean>

<!--导入EXCEL配置-->
<bean id="importOrganise" class="com.ultrapower.accredit.controller.organise.ImportOrganiseController">
        <property name="sessionForm" value="true" />
<property name="commandName" value="affix" />
<property name="commandClass" value="com.ultrapower.accredit.common.value.Affix" />
<property name="formView" value="tree/organization/exceloperation" />
<property name="successView" value="tree/organization/exceloperation" />
  <property name="organiseManagerHandler" ref="OrganiseManagerHandlerImpl"></property>
  <property name="organiseExcelManager" ref="organiseExcelManager"></property>
</bean>

【JSP】代码:
因为EXCEL数据在导出的时候已经把它放置在服务器下EXCEL文件夹里只需要在前台提供下载方式就可以把EXCEL文件下载放到本地任何位置。
<%@ page contentType="text/html;charset=gb2312" import="com.jspsmart.upload.*" %>
<%
SmartUpload su = new SmartUpload();
su.initialize(pageContext);
su.setContentDisposition(null);
String path= application.getRealPath("/");
path = path+"excel\\organiseList.xls";
System.out.println(path);
su.downloadFile(path);
response.flushBuffer();
out.clear();
out=pageContext.pushBody();
%>
【导入EXCEL exceloperation.jsp】
<%@ include file="/jsp/tree/include.jsp" %>
<%@ page language="java" pageEncoding="GBK"%>
<%
String path = request.getContextPath();
request.setAttribute("path",path);
%>
<html>
<head>
<link type="text/css" rel="stylesheet" href="<%=path%>/css/adduser.css">
<link type="text/css" rel="stylesheet" href="<%=path%>/css/content.css">

<script type="text/javascript">
function winClose(){
window.top.opener = null;
window.open('','_parent','');  
//window.close();
top.close();
}

function checkForm(){
if(form1.file.value == ""){
alert("请选择待导入文件");
}else{
if(checkType(form1.file.value)){
document.getElementById("cover").innerHTML = "";
form1.submit();
}
}
}

//截取图片名字
function checkType(path){
var start = path.lastIndexOf(".")+1;
var end = path.length;
var sufix = path.substring(start,end);

if(sufix != "xls"){
alert("文件类型不正确");
form1.file.value = "";
return false;
}
return true;
}

</script>

</head>
<body>
<form name="form1" method="post" enctype="multipart/form-data" action="<%=path%>/organization/importOrganise.htm" target="hidden_frame">
<table align="center" cellpadding="1" cellspacing="1">
<tr align="center">
<th>
<ins>组织机构导入</ins>
</th>
</tr>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0">

<tr>
<td align="center" colspan="2">
<a href="<%=path%>/excel/organiseModule.xls">下载导入模板</a>
</td>
</tr>
<tr title="格式限xls">
<td  align="right"><font color="red">*</font>选择导入文件</td>
<td  align="left">
<input type="file" name="file" id="file" value="" onChange="checkType(this.value)"
onblur="this.className='onblur'" onFocus="this.className='onfocus'">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="center" height="10"><div id="cover"></div></td>
</tr>
<tr>
<td align="center">
<input type="button" value=" 提 交 " class="btn1_mouseout"
onmouseover="this.className='btn1_mouseover'"
onMouseOut="this.className='btn1_mouseout'" onclick="checkForm()"/>
<input type="reset" value=" 重 置 " class="btn1_mouseout"
onmouseover="this.className='btn1_mouseover'"
onMouseOut="this.className='btn1_mouseout'"/>
</td>
</tr>
<tr><td>&nbsp;</td></tr>
</table>
<iframe name="hidden_frame" id="hidden_frame" style="display:none"></iframe>
</form>

  <body>
 
<script type="text/javascript">

function init(){
if("${info}" != null && "${info}" != ""){
document.getElementById("cover").innerHTML = "";
if(confirm("${info}" + "\n点击确定后关闭当前页面")){
winClose();
}else{

}
}
}

window.onload = init;
</script>  

</html>

评论

2 楼 hufangdong 2013-03-13  

真需要 学习了 太感谢分享了

1 楼 chaodilei 2012-08-03  

请问一下楼主 readExcel(InputStream inputXLS)函数 中
List orgs = new ArrayList();
Map beans = new HashMap();
beans.put("orgs", orgs);
XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
return orgs;

orgs实例化之后也没有加入值，做这么多操作有什么用？