/**
* 取得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 祝大家工作顺利------------------------