response.setContentType("application/vnd.ms-excel"); //设置格式，导出文件名
// 生成导出的文件名
String filename = "sample_";
SimpleDateFormat dateFormate = new SimpleDateFormat("yyMMdd_HHmmss");
Calendar now = Calendar.getInstance();
String nowStr = dateFormate.format(now.getTime());
response.setHeader("Content-disposition", "attachment;filename=" + filename + nowStr + ".xls");
/**
* @author 邓赢
*定制化RNA导出
*/
@SuppressWarnings("unused")
private void rnaSampleTemplate(HttpServletResponse response,
List<String> idList, String language, String group)
throws Exception {
WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream()); //创建输出流
WritableSheet sheet = book.createSheet("sampleOperate", 0);
WritableCellFormat normalFormat = new WritableCellFormat(NumberFormats.TEXT); //创建单元格的格式
normalFormat.setAlignment(Alignment.CENTRE);
normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
normalFormat.setWrap(true);
normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
normalFormat.setBackground(Colour.IVORY);
normalFormat.setLocked(false);// 是否锁定单元格
// 锁定样式
WritableCellFormat lockFormat = new WritableCellFormat(NumberFormats.TEXT);
lockFormat.setAlignment(Alignment.CENTRE);
lockFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
lockFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
lockFormat.setLocked(true); // 是否锁定单元格，锁定之后的单元格数据将不能修改
lockFormat.setWrap(true);
// 锁定样式
// WritableFont wfTitleLock = new WritableFont(WritableFont.ARIAL,
// WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD, false);
WritableCellFormat wfTitleLockFormat = new WritableCellFormat(NumberFormats.TEXT);
wfTitleLockFormat.setAlignment(Alignment.CENTRE);
wfTitleLockFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
wfTitleLockFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
wfTitleLockFormat.setLocked(true);// 是否锁定单元格
wfTitleLockFormat.setWrap(true);
// 表头
List<String> title = new ArrayList<String>();
// 项目信息
title.add("项目名称");
title.add("项目编号");
title.add("客户姓名");
title.add("客户单位");
title.add("实验室收样人");
title.add("实验室收样日期");
title.add("实验室检测人");
title.add("实验室检测日期");
title.add("报告撰写人");
// 客户信息
title.add("样品类型1");
title.add("样品状态");
title.add("物种来源");
title.add("样品名称");
title.add("样品编号");
title.add("组织名称");
title.add("组织编号");
title.add("管数");
title.add("浓度(ng/μL)");
title.add("体积(μl)");
title.add("总量(μg)");
title.add("OD260/280");
title.add("OD260/230");
title.add("备注");
//检测结果
title.add("样品类型2");
title.add("结果说明");
title.add("备注");
// Nanodrop
title.add("检测前处理");
title.add("检测空白参照");
title.add("OD260/280");
title.add("OD260/230");
title.add("备注");
// Agilent 2100
title.add("检测前处理");
title.add("检测方法");
title.add("检测试剂盒");
title.add("稀释倍数(X)");
title.add("检测浓度(ng/μL)");
title.add("体积(μL)");
title.add("RIN");
title.add("28S/18S");
title.add("5S峰");
title.add("图谱基线");
title.add("rRNA contamination(%)");
title.add("片度分布");
title.add("备注");
title.add("样品ID");
title.add("语言");
// 项目信息
sheet.mergeCells(0, 0, 8, 0); // 合并单元格，参数格式（开始列，开始行，结束列，结束行）
sheet.addCell(new jxl.write.Label(0, 0, "项目信息", wfTitleLockFormat));
// 客户信息
sheet.mergeCells(9, 0, 22, 0); // 合并单元格，参数格式（开始列，开始行，结束列，结束行）
sheet.addCell(new jxl.write.Label(9, 0, "客户信息", wfTitleLockFormat));
sheet.mergeCells(23, 0, 45, 0); // 合并单元格，参数格式（开始列，开始行，结束列，结束行）
sheet.addCell(new jxl.write.Label(21, 0, "检测结果", wfTitleLockFormat));
//Nanodrop
sheet.mergeCells(26, 1, 30, 1); // 合并单元格，参数格式（开始列，开始行，结束列，结束行）
sheet.addCell(new jxl.write.Label(26, 1, "Nanodrop", wfTitleLockFormat));
sheet.mergeCells(31, 1, 45, 1); // 合并单元格，参数格式（开始列，开始行，结束列，结束行）
sheet.addCell(new jxl.write.Label(31, 1, "Agilent 2100", wfTitleLockFormat));
for(int i=0; i < title.size(); i++){
if(i < 26){
sheet.mergeCells(i, 1, i, 2); // 合并单元格，参数格式（开始列，开始行，结束列，结束行）
sheet.addCell(new jxl.write.Label(i, 1, title.get(i), lockFormat));
}else{
sheet.addCell(new jxl.write.Label(i, 2, title.get(i), lockFormat)); //列、行
}
}
String text = null;
Label label = null;
List<SampleManagerMx> result = sampleManagerMxService.getSampleMxWithItem(idList);
for(int i=0;i<result.size();i++){
SampleManagerMx smm = result.get(i);
sheet.setRowView(i + 3, 1000); //作用是指定第i+1行的高度
//项目名称
sheet.addCell(new jxl.write.Label(0, i + 3, StringUtil.ObjectToString(""), lockFormat));
//项目编号
sheet.addCell(new jxl.write.Label(1, i + 3, StringUtil.ObjectToString(smm.getItemNumber()), lockFormat));
//客户姓名
sheet.addCell(new jxl.write.Label(2, i + 3, StringUtil.ObjectToString(smm.getSampleConnMan()), lockFormat));
//客户单位
sheet.addCell(new jxl.write.Label(3, i + 3, StringUtil.ObjectToString(smm.getSampleConnCompany()), lockFormat));
//实验室收样人
sheet.addCell(new jxl.write.Label(4, i + 3, StringUtil.ObjectToString(smm.getSampleOutReMan()), lockFormat));
//实验室收样日期
Date sampleRDate = smm.getSampleOutTime();
sheet.addCell(new jxl.write.Label(5, i + 3, sampleRDate == null ? "" : sampleRDate.toLocaleString(), lockFormat));
sheet.addCell(new jxl.write.Label(6, i + 3, StringUtil.ObjectToString(""), normalFormat));
sheet.addCell(new jxl.write.Label(7, i + 3, StringUtil.ObjectToString(""), normalFormat));
sheet.addCell(new jxl.write.Label(8, i + 3, StringUtil.ObjectToString(""), normalFormat));
// 样品类型1
sheet.addCell(new jxl.write.Label(9, i + 3, StringUtil.ObjectToString(smm.getSampleType()), lockFormat));
// 样品状态
sheet.addCell(new jxl.write.Label(10, i + 3, StringUtil.ObjectToString(smm.getSampleSendTj()), lockFormat));
// 物种来源
sheet.addCell(new jxl.write.Label(11, i + 3, StringUtil.ObjectToString(smm.getSampleSpecies()), lockFormat));
// 样品名称
sheet.addCell(new jxl.write.Label(12, i + 3, StringUtil.ObjectToString(smm.getSampleName()), lockFormat));
//样品编号
sheet.addCell(new jxl.write.Label(13, i + 3, StringUtil.ObjectToString(smm.getSampleSerialNumber()), lockFormat));
//组织名称
sheet.addCell(new jxl.write.Label(14, i + 3, StringUtil.ObjectToString(""), lockFormat));
//组织编号
sheet.addCell(new jxl.write.Label(15, i + 3, StringUtil.ObjectToString(""), lockFormat));
//管数
//sheet.addCell(new jxl.write.Label(16, i + 3, StringUtil.ObjectToString(smm.getSampleConduit()), lockFormat));
sheet.addCell(new jxl.write.Label(16, i + 3, smm.getSampleConduit() == null ? "1" : smm.getSampleConduit().toString(), lockFormat));
//浓度(ng/μl)
sheet.addCell(new jxl.write.Label(17, i + 3, StringUtil.ObjectToString(smm.getSampleChroma()), lockFormat));
//体积(μl)
sheet.addCell(new jxl.write.Label(18, i + 3, StringUtil.ObjectToString(smm.getSampleBulk()), lockFormat));
//总量(μg)
sheet.addCell(new jxl.write.Label(19, i + 3, StringUtil.ObjectToString(smm.getSampleCount()), lockFormat));
//260280
sheet.addCell(new jxl.write.Label(20, i + 3, StringUtil.ObjectToString(smm.getSampleOd260280()), lockFormat));
//260230
sheet.addCell(new jxl.write.Label(21, i + 3, StringUtil.ObjectToString(smm.getSampleOd260230()), lockFormat));
// 备注
sheet.setColumnView(22, 25); //指定第列宽度
sheet.addCell(new jxl.write.Label(22, i + 3, StringUtil.ObjectToString(smm.getSampleMxRemarks()), lockFormat));
//样品类型2
sheet.setColumnView(23, 30); //指定第列宽度
label = new jxl.write.Label(23, i + 3, StringUtil.ObjectToString(""), normalFormat);
if(group.equals(GroupType.CUSTOMIZED_RNA)){ //CUSTOMIZED_RNA组
if(language.equals(com.genomics.ib.sampleReport.Constants.LANGUAGE_CN)){
label.setCellFeatures(addColumnList(ConstansCustomizedRNA.textBoxlist1));
}else{
label.setCellFeatures(addColumnList(ConstansCustomizedRNA.textBoxlist1_eng));
}
}
sheet.addCell(label);
//结果说明
sheet.addCell(new jxl.write.Label(24, i + 3, StringUtil.ObjectToString(""), normalFormat));
//备注
sheet.setColumnView(25, 25); //指定第列宽度
sheet.addCell(new jxl.write.Label(25, i + 3, StringUtil.ObjectToString(smm.getSampleMxRemarks()), normalFormat));
// Nanodrop(检测前处理)
if (language.equals("chi")) {
text = "将样品在冰上融化后，离心并充分混匀，取适量样品进行检测。";
}else{
text = " After the sample melted the ice, fully mixed and centrifuged, take appropriate samples for testing.";
}
//检测前处理
sheet.addCell(new jxl.write.Label(26, i + 3, StringUtil.ObjectToString(text), normalFormat));
//检测空白参照
sheet.addCell(new jxl.write.Label(27, i + 3, "", normalFormat));
//260280
sheet.addCell(new jxl.write.Label(28, i + 3, StringUtil.ObjectToString(""), normalFormat));
//260230
sheet.addCell(new jxl.write.Label(29, i + 3, StringUtil.ObjectToString(""), normalFormat));
// 备注
sheet.addCell(new jxl.write.Label(30, i + 3, StringUtil.ObjectToString(""), normalFormat));
//检测前处理
sheet.addCell(new jxl.write.Label(31, i + 3, StringUtil.ObjectToString(text), normalFormat));
//检测方法
sheet.addCell(new jxl.write.Label(32, i + 3, StringUtil.ObjectToString(""), normalFormat));
//检测试剂盒
sheet.setColumnView(33, 25); //指定第列宽度
label = new jxl.write.Label(33, i + 3, StringUtil.ObjectToString(""), normalFormat);
if(group.equals(GroupType.CUSTOMIZED_RNA)){ //CUSTOMIZED_RNA组
label.setCellFeatures(addColumnList(ConstansCustomizedRNA.textBoxlist2));
}
sheet.addCell(label);
//sheet.addCell(new jxl.write.Label(33, i + 3, StringUtil.ObjectToString(smm.getSampleMxRemarks()), normalFormat));
//稀释倍数
sheet.addCell(new jxl.write.Label(34, i + 3, StringUtil.ObjectToString(""), normalFormat));
//检测浓度
sheet.addCell(new jxl.write.Label(35, i + 3, StringUtil.ObjectToString(""), normalFormat));
//体积
sheet.addCell(new jxl.write.Label(36, i + 3, StringUtil.ObjectToString(""), normalFormat));
//RIN
sheet.addCell(new jxl.write.Label(37, i + 3, StringUtil.ObjectToString(""), normalFormat));
//28s/18s
sheet.addCell(new jxl.write.Label(38, i + 3, StringUtil.ObjectToString(""), normalFormat));
//5s峰
sheet.addCell(new jxl.write.Label(39, i + 3, StringUtil.ObjectToString(""), normalFormat));
//图谱基线
sheet.addCell(new jxl.write.Label(40, i + 3, StringUtil.ObjectToString(""), normalFormat));
//rRNA contamination(%)
sheet.addCell(new jxl.write.Label(41, i + 3, StringUtil.ObjectToString(""), normalFormat));
//片段分布
sheet.addCell(new jxl.write.Label(42, i + 3, StringUtil.ObjectToString(""), normalFormat));
//备注
sheet.setColumnView(43, 25); //指定第列宽度
sheet.addCell(new jxl.write.Label(43, i + 3, StringUtil.ObjectToString(""), normalFormat));
//样品ID
//语言
String sampleInfoID = (idList.get(i)==null ? "" : idList.get(i).toString());
sheet.addCell(new jxl.write.Label(44, i + 3, sampleInfoID, lockFormat));
sheet.addCell(new jxl.write.Label(45, i + 3, language, lockFormat));
}
sheet.setProtected(true);// 保护Sheet
book.write();
book.close(); //关闭输出流
}
如图所示：