一個關于導出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("屬性說明： \r\n 1、用戶類型：1 醫生 2患者 3專家 此處只能導入醫生")
.append("\r\n").append(" 2、性別：1 男 2 女")
.append("\r\n").append(" 3、組織架構為用戶所在的組織架構Id 1 肺癌聯盟")
.append("\r\n").append(" 4、醫院，科室均輸入編號即可")
.append("\r\n").append(" 5、 職稱：1 主任醫師 2 副主任醫師 3 主治醫師4 醫師 5 教授 6 副教授")
.append("\r\n").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;

?