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(); //关闭输出流
}
如图所示：
/**
* @author 邓赢
*定制化RNA导入
*/
@SuppressWarnings("unchecked")
private Map rnaSampleSave(Workbook wb, File fileName,
HttpServletResponse response, HttpServletRequest request,
String sampleGroup) throws Exception {
wb = Workbook.getWorkbook(fileName);
Sheet sheet = wb.getSheet(0);
Map validateResult = RNACustomizedExcelImportValidateUtil.validate(sheet, response); //Excel导入验证
if(validateResult!=null&&!validateResult.isEmpty()){
return validateResult;
}
取值之前的读取execl，验证execl 在此不细说
/**
* 取得excel各单元格的数据
*/
Map map = null;
int rows = sheet.getRows();
OperateSample operateSample = null;
List<String> existSampleIds = new ArrayList<String>();
List<String> operateSampleIds = new ArrayList<String>();
List<OperateSample> excelList = new ArrayList<OperateSample>();
String section = (String) request.getSession().getAttribute("U_SECTION");
String userName = (String) request.getSession().getAttribute("U_NAME");
for(int i=3;i<rows;i++){
String language = sheet.getCell(45,i).getContents().trim();
//样品类型2
String sampleStyleStr = sheet.getCell(23,i).getContents().trim();
operateSample = new OperateSample();
String operateSampleId = UUID.randomUUID().toString();
operateSample.setOperateSampleId(operateSampleId);
//样品组别
operateSample.setSampleGroup(sampleGroup);
//实验室检测人
operateSample.setOperateMan(sheet.getCell(6, i).getContents());
String date = sheet.getCell(7, i).getContents().trim();
//实验室检测日期
operateSample.setOperateDate(DateUtil.stringToDate(date, "yyyyMMdd"));
//报告撰写人
operateSample.setReportMan(sheet.getCell(8, i).getContents());
//管数
operateSample.setSampleConduit2100(sheet.getCell(16, i).getContents().trim());
//样品类型2
operateSample.setSampleType(sampleStyleStr);
//Nanodrop - 检测前处理
operateSample.setNdpBeforeOperateMethod(sheet.getCell(26, i).getContents().trim());
//Nanodrop - 检测空白参照
operateSample.setNdpBlankReferce(sheet.getCell(27, i).getContents().trim());
//Nanodrop OD260/280
operateSample.setNdpOD260280(sheet.getCell(28, i).getContents().trim());
//Nanodrop OD260/230
operateSample.setNdpOD260230(sheet.getCell(29, i).getContents().trim());
//Nanodrop - 备注
operateSample.setNdpRemark(sheet.getCell(30, i).getContents().trim());
//Agilent2100/Caliper - 检测前处理
operateSample.setBeforeOperateMethod2100(sheet.getCell(31, i).getContents().trim());
//N -- Agilent2100/Caliper - 检测方法
operateSample.setName2100(sheet.getCell(32, i).getContents().trim());
//Agilent2100/Caliper - 检测试剂盒
operateSample.setKit2100(sheet.getCell(33, i).getContents().trim());
//Agilent2100/Caliper - 稀释倍数(×)
operateSample.setDilutionRate2100(sheet.getCell(34, i).getContents().trim());
//Agilent2100/Caliper - 检测浓度
String operateChroma = sheet.getCell(35, i).getContents().trim();
//Agilent2100/Caliper - 检测浓度
operateSample.setChroma2100(operateChroma);
//Agilent2100/Caliper - 检测浓度
operateSample.setOperateChroma(operateChroma);
String operateUl = sheet.getCell(36, i).getContents().trim();
//Agilent2100/Caliper - 体积
operateSample.setOperateUl(new BigDecimal(operateUl));
//Agilent2100 Caliper - RIN
operateSample.setOperateRin(sheet.getCell(37, i).getContents().trim());
//28s/18s
operateSample.setOperate28s18s(sheet.getCell(38, i).getContents().trim());
//N -- Agilent2100 Caliper - 5S峰
operateSample.setFives2100(sheet.getCell(39, i).getContents().trim());
//N -- Agilent2100 Caliper - 图谱基线
operateSample.setMapbase2100(sheet.getCell(40, i).getContents().trim());
//N -- Agilent2100 Caliper - 核糖体RNA比例(%)
operateSample.setContaminationRna2100(sheet.getCell(41, i).getContents().trim());
//Agilent2100 Caliper - 片段分布
operateSample.setInsertSize2100(sheet.getCell(42, i).getContents().trim());
//Agilent2100 Caliper - 备注
operateSample.setRemark2100(sheet.getCell(43, i).getContents().trim());
//样品ID
String sampleId = sheet.getCell(44, i).getContents().trim();
operateSample.setSampleInfoId(sampleId);
//语言
operateSample.setLanguage(language);
// double sampleCountDouble = 0; //样品总量
if(NumberUtils.isNumber(operateChroma) && NumberUtils.isNumber(operateSample.getDilutionRate2100())){
//检测浓度(ng/μL) * 稀释倍数(×) = 原液浓度(ng/μL)
BigDecimal a = new BigDecimal(operateChroma);
BigDecimal b = a.multiply(new BigDecimal(operateSample.getDilutionRate2100()));
b = DecimalFormatUtil.conversionNumber(4, b);
operateSample.setLiquidChroma(null==b?"0":b.toString());
}
// 总量(μg)= 原液浓度*样品体积
if(StringUtils.isNotBlank(operateSample.getLiquidChroma()) && NumberUtils.isNumber(operateSample.getLiquidChroma())){
BigDecimal a = new BigDecimal(operateSample.getLiquidChroma()); //原液浓度
BigDecimal b = a.multiply(operateSample.getOperateUl()); //总量(μg) = (原液浓度*样品体积)
BigDecimal sum = b.divide(new BigDecimal("1000")); //总量(μg)/1000
//sampleCountDouble= sum.doubleValue();//吧BigDecimal转换成为double\
sum = DecimalFormatUtil.conversionNumber(4, sum);
operateSample.setOperateUg(null==sum?"0":sum.toString());
}
operateSample.setInsertUserName(userName); //当前操作人
operateSample.setSection(section); //地区
operateSample.setOperateResult(sheet.getCell(24, i).getContents().trim());//结果说明
operateSample.setOperateRemark(sheet.getCell(25, i).getContents().trim());//备注（结果和备注都是手动输入）
// Start BMS_2011_002, zane_chen, 2011-1-27
List operateFlag = operateSampleService
.getOperateSampleBySampleId(sampleId);
if (null != operateFlag && operateFlag.size() > 0) {
existSampleIds.add(sampleId);
}
operateSampleIds.add(operateSampleId);
// End BMS_2011_002, zane_chen, 2011-1-27
excelList.add(operateSample);
}
// 关闭Workbook流
wb.close();
response.setCharacterEncoding("UTF-8");
String agent = request.getHeader("User-Agent");
// 如果是IE浏览器
if (agent.lastIndexOf("MSIE") >= 0) {
response.setContentType("text/json;charset=utf-8");
}
operateSampleService.saveOperateSample(excelList);
if (existSampleIds.size() > 0) {
map = new HashMap();
map.put("repeat", existSampleIds.toArray());
map.put("operateSampleIds", operateSampleIds.toArray());
return map;
}
return null;
}
-----------------当然项目中必须得有jxl.jar 祝大家工作顺利------------------------