一个关于导出excel模板的实例

1 首先jsp页面

点击模板下载，会自动下载模板ｅｘｃｅｌ，效果如下

让我们看源码：

１　ｊｓｐ页面

<div class="tab-pane" id="profile">
&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input type="button" id="export" onClick="toModelExport()" value="模板下载" >
&nbsp;&nbsp;&nbsp;&nbsp;
<input type="button" id="export" onClick="toExport()" value="批量导入" >
</div>

主要是两个按钮

ｊｓ函数

function toModelExport(){

$("<form>").attr({
"action":"${ctx}/doctor/toModelDownLoad",
"method":"get"
}).submit();toModelExport

}

２后台ｃｏｎｔｒｏｌｌｅｒ

//模板导入
@RequestMapping(value = "/toModelDownLoad")
public void toModelDownLoad(HttpServletRequest request, HttpServletResponse response) {
WritableWorkbook wb = null;
OutputStream os = null;
String fileName = "doctorImportStand.xls";
try {
os = response.getOutputStream();
wb =new Importmodel().createWorkbookModel(os);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename="" + fileName + """);
wb.write();
wb.close();
os.close();
response.flushBuffer();
} catch (Exception e) {
e.printStackTrace();
}
}

核心就是这个new Importmodel().createWorkbookModel(os);

３Importmodel类

public class Importmodel {
protected final Log log = LogFactory.getLog(getClass());
//医生模板
public WritableWorkbook createWorkbookModel(OutputStream outputStream) {
WritableWorkbook wwb = null;
try {
wwb = Workbook.createWorkbook(outputStream);
WritableSheet sheet =wwb.createSheet("医生信息录入模板",1);
//解决jxl下拉框254字符限制
WritableSheet sheet2 = wwb.createSheet("录入说明", 1);
WritableFont font = new WritableFont(jxl.write.WritableFont
.createFont("隶书"),14);
WritableCellFormat format = new WritableCellFormat(font);
Label label1 = new Label(0,0,"性别",format);
sheet2.addCell(label1);
label1 = new Label(0,1,"1",format);
sheet2.addCell(label1);
label1 = new Label(1,1,"男",format);
sheet2.addCell(label1);
label1 = new Label(0,2,"2",format);
sheet2.addCell(label1);
label1 = new Label(1,2,"女",format);
sheet2.addCell(label1);
label1 = new Label(0,3,"用户类型",format);
sheet2.addCell(label1);
label1 = new Label(0,4,"1",format);
sheet2.addCell(label1);
label1 = new Label(1,4,"医生",format);
sheet2.addCell(label1);
label1 = new Label(0,5,"2",format);
sheet2.addCell(label1);
label1 = new Label(1,5,"患者",format);
sheet2.addCell(label1);
label1 = new Label(0,6,"3",format);
sheet2.addCell(label1);
label1 = new Label(1,6,"专家",format);
sheet2.addCell(label1);
label1 = new Label(0,7,"职称",format);
sheet2.addCell(label1);
label1 = new Label(0,8,"1",format);
sheet2.addCell(label1);
label1 = new Label(1,8,"主任医师",format);
sheet2.addCell(label1);
label1 = new Label(0,9,"2",format);
sheet2.addCell(label1);
label1 = new Label(1,9,"副主任医师",format);
sheet2.addCell(label1);
label1 = new Label(0,10,"3",format);
sheet2.addCell(label1);
label1 = new Label(1,10,"主治医师",format);
sheet2.addCell(label1);
label1 = new Label(0,11,"4",format);
sheet2.addCell(label1);
label1 = new Label(1,11,"医师",format);
sheet2.addCell(label1);
label1 = new Label(0,12,"5",format);
sheet2.addCell(label1);
label1 = new Label(1,12,"教授",format);
sheet2.addCell(label1);
label1 = new Label(0,13,"6",format);
sheet2.addCell(label1);
label1 = new Label(1,13,"副教授",format);
sheet2.addCell(label1);
label1 = new Label(0,14,"医院代码",format);
sheet2.addCell(label1);
label1 = new Label(0,15,"1",format);
sheet2.addCell(label1);
label1 = new Label(1,15,"中山医院",format);
sheet2.addCell(label1);

label1 = new Label(0,16,"2",format);
sheet2.addCell(label1);
label1 = new Label(1,16,"金泽镇社区卫生服务中心",format);
sheet2.addCell(label1);
label1 = new Label(0,17,"3",format);
sheet2.addCell(label1);
label1 = new Label(1,17,"青浦区中心医院",format);
sheet2.addCell(label1);
label1 = new Label(0,18,"组织架构",format);
sheet2.addCell(label1);
label1 = new Label(0,19,"1",format);
sheet2.addCell(label1);
label1 = new Label(1,19,"肺癌联盟",format);
sheet2.addCell(label1);
label1 = new Label(0,20,"科室",format);
sheet2.addCell(label1);
label1 = new Label(0,21,"1",format);
sheet2.addCell(label1);
label1 = new Label(1,21,"呼吸内科",format);
sheet2.addCell(label1);
label1 = new Label(0,22,"2",format);
sheet2.addCell(label1);
label1 = new Label(1,22,"全科",format);
sheet2.addCell(label1);
sheet.mergeCells(0,0,13,0);
sheet.setRowView(0, 400);
StringBuffer strBuf = new StringBuffer();
strBuf.append("属性说明： 1、用户类型：1 医生 2患者 3专家 此处只能导入医生")
.append(" ").append(" 2、性别：1 男 2 女")
.append(" ").append(" 3、组织架构为用户所在的组织架构Id 1 肺癌联盟")
.append(" ").append(" 4、医院，科室均输入编号即可")
.append(" ").append(" 5、 职称：1 主任医师 2 副主任医师 3 主治医师4 医师 5 教授 6 副教授")
.append(" ").append(" 6、 请严格按照模板格式进行导入，每一项都要填写，否则不能导入");
Label label = new Label(0,0,strBuf.toString());
sheet.addCell(label);
label = new Label(0,1,"用户类型");
sheet.addCell(label);
label = new Label(0,2,"1");
sheet.addCell(label);
label = new Label(1,1,"手机号");
sheet.addCell(label);
label = new Label(1,2,"13５２４９２９８１３");
sheet.addCell(label);
label = new Label(2,1,"姓名");
sheet.addCell(label);
label = new Label(2,2,"赵");
sheet.addCell(label);
label = new Label(3,1,"性别");
sheet.addCell(label);
label = new Label(3,2,"2");
sheet.addCell(label);
label = new Label(4,1,"组织架构");
sheet.addCell(label);
label = new Label(4,2,"1");
sheet.addCell(label);
label = new Label(5,1,"医院");
sheet.addCell(label);
label = new Label(5,2,"2");
sheet.addCell(label);
label = new Label(6,1,"科室");
sheet.addCell(label);
label = new Label(6,2,"2");
sheet.addCell(label);
label = new Label(7,1,"职称");
sheet.addCell(label);
label = new Label(7,2,"1");
sheet.addCell(label);
label = new Label(8,1,"出生日期");
sheet.addCell(label);
label = new Label(8,2,"1980.08.08");
sheet.addCell(label);
label = new Label(9,1,"医生角色");
sheet.addCell(label);
label = new Label(9,2,"3");
sheet.addCell(label);
label = new Label(10,1,"坐诊时间");
sheet.addCell(label);
label = new Label(10,2,"1-0,1-1,2-0,2-1");
sheet.addCell(label);
label = new Label(11,1,"会诊时间");
sheet.addCell(label);
label = new Label(11,2,"3");
sheet.addCell(label);
label = new Label(12,1,"会诊价格");
sheet.addCell(label);
label = new Label(12,2,"2000");
sheet.addCell(label);
label = new Label(13,1,"简介");
sheet.addCell(label);
label = new Label(13,2,"副教授、医学博士、呼吸内科执行主任，青浦区学科带头人、上海市医学会肺科学会青年委员。从事呼吸内科专业临床医疗、科研及教学工作20年，基础理论知识扎实，擅长慢性阻塞性肺疾病、支气管哮喘、肺心病、呼吸衰竭、肺部感染性疾病、肺部肿瘤、间质性肺疾病、胸膜疾病等呼吸系统常见疾病的诊治，对于呼吸系统各种疑难、危重病症的诊治及抢救技术，机械通气技术和呼吸内镜及介入诊疗技术具有丰富的临床经验。 近年来，主持市级科研课题2项，参与国家自然基金项目、市级科研课题项目多项;作为第一作者在SCI收录及国内核心期刊发表专业学术论文20余篇。");
sheet.addCell(label);
} catch (IOException e) {
log.error("ERROR:createWorkbookModel",e);
} catch (RowsExceededException e) {
log.error("ERROR:createWorkbookModel",e);
} catch (WriteException e) {
log.error("ERROR:createWorkbookModel",e);
}
return wwb;
}

至此，可以顺利实现功能

所用到的依赖

import java.io.IOException;
import java.io.OutputStream;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;