diagbotcloud/icss-service/src/main/java/com/diagbot/facade/LisExcelResFacade.java

package com.diagbot.facade;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import com.diagbot.dto.RespDTO;
import com.diagbot.exception.CommonErrorCode;
import com.diagbot.exception.CommonException;
import com.diagbot.util.StringUtil;
import com.diagbot.vo.LisExcelResVO;
import com.diagbot.vo.LisExcelWrapperVO;
import com.diagbot.vo.LitAssayVO;

/**
 * 
 * @author wangfeng
 * @Description: 化验导入
 * @date 2018年11月29日 上午10:16:35
 */
@Component
public class LisExcelResFacade {

	public RespDTO<LitAssayVO> lisExcelAnalysis(MultipartFile file, HttpServletRequest request) {

		List<String> messages = new ArrayList<>();
		List<LisExcelWrapperVO> lisExcelWrapperList = new ArrayList<>();
		InputStream inputStream = null;
		Workbook wb = null;
		try {
			if (!file.isEmpty()) {
				inputStream = file.getInputStream();
				if (inputStream.available() > 512000) {
					messages.add("化验文件最大支持500KB！");
				} else {
					String fileName = file.getOriginalFilename();
					if (fileName.lastIndexOf(".") != -1) {
						String type = fileName.substring(fileName.lastIndexOf("."));
						if (type.equals(".xls")) {
							wb = new HSSFWorkbook(inputStream);
						} else if (type.equals(".xlsx")) {
							wb = new XSSFWorkbook(inputStream);
						}
						if (wb != null) {
							Sheet sheet = wb.getSheetAt(0);
							int count = 0;
							String mealName, itemName, unit, value, max, min, time;
							for (Row row : sheet) {
								count++;
								try {
									if (row != null) {
										mealName = getValue(row.getCell(0)).trim().replace(" ", "");
										itemName = getValue(row.getCell(1)).trim().replace(" ", "");
										unit = getValue(row.getCell(2)).trim();
										value = getValue(row.getCell(3)).trim();
										max = getValue(row.getCell(4)).trim();
										min = getValue(row.getCell(5)).trim();
										time = getValue(row.getCell(6)).trim();
										type = getValue(row.getCell(7)).trim();
									} else {
										mealName = null;
										itemName = null;
										unit = null;
										value = null;
										max = null;
										min = null;
										time = null;
										type = null;
									}

									if (StringUtil.isEmpty(mealName) && StringUtil.isEmpty(itemName)
											&& StringUtil.isEmpty(value)) {
										continue;
									}

									if (count == 1 && mealName.equals("套餐名")) {
										continue;
									}

									if (StringUtil.isEmpty(mealName) || StringUtil.isEmpty(itemName)
											|| StringUtil.isEmpty(value)) {
										// throw new
										// CommonException(CommonErrorCode.NOT_EXISTS,
										// "第"+count+"行数据不规范,套餐名、项目名、结果三项必填;");
										messages.add("第" + count + "行数据不规范,套餐名、项目名、结果三项必填;");
										continue;
									}
									LisExcelWrapperVO lisExcelWrapper = new LisExcelWrapperVO();

									lisExcelWrapper.setMealName(mealName);
									lisExcelWrapper.setItemName(itemName);
									lisExcelWrapper.setUnit(unit);
									lisExcelWrapper.setValue(value);
									lisExcelWrapper.setMax(max);
									lisExcelWrapper.setMin(min);
									lisExcelWrapper.setTime(time);
									lisExcelWrapper.setType(type);
									lisExcelWrapperList.add(lisExcelWrapper);
								} catch (Exception e) {
									e.printStackTrace();
									throw new CommonException(CommonErrorCode.NOT_EXISTS, e.toString());
									// logger.error("",e);
								}
							}
						} else {
							// throw new
							// CommonException(CommonErrorCode.NOT_EXISTS,
							// "非excel文件无法解析！");
							messages.add("非excel文件无法解析！");
						}
					} else {
						// throw new CommonException(CommonErrorCode.NOT_EXISTS,
						// "未知文件无法解析！");
						messages.add("未知文件无法解析！");
					}
				}
			} else {
				// throw new CommonException(CommonErrorCode.NOT_EXISTS,
				// "无文件上传！");
				messages.add("无文件上传！");
			}
			Map<String, List<LisExcelWrapperVO>> lixExMap = lisExcelWrapperList.stream()
					.collect(Collectors.groupingBy(LisExcelWrapperVO::getMealName));
			List<LisExcelResVO> LisExcelReslist = new ArrayList<LisExcelResVO>();
			for (String str : lixExMap.keySet()) {
				LisExcelResVO lisExcelResVO = new LisExcelResVO();
				lisExcelResVO.setMenus(str);
				lisExcelResVO.setLisExcelItem(lixExMap.get(str));
				LisExcelReslist.add(lisExcelResVO);
			}

			LitAssayVO litAssay = new LitAssayVO();
			litAssay.setMessages(messages);
			litAssay.setLisExcelRes(LisExcelReslist);
			return RespDTO.onSuc(litAssay);
		} catch (Exception e) {
			e.printStackTrace();
			throw new CommonException(CommonErrorCode.NOT_EXISTS, "化验excel文件解析出错！");
			// return response.failure("化验excel文件解析出错！");
		} finally {
			try {
				if (inputStream != null) {
					inputStream.close();
				}
				if (wb != null) {
					wb.close();
				}
			} catch (Exception e) {
			}
		}
	}

	private String getValue(Cell cell) {
		try {
			Object obj = null;
			switch (cell.getCellTypeEnum()) {
			case BOOLEAN:
				obj = cell.getBooleanCellValue();
				break;
			case ERROR:
				obj = cell.getErrorCellValue();
				break;
			case NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					obj = DateFormatUtils.format(date, "yyyy-MM-dd");
				} else {
					obj = cell.getNumericCellValue();
					DecimalFormat df = new DecimalFormat("0");
					obj = df.format(obj);
				}

				// obj = cell.getNumericCellValue();
				break;
			case STRING:
				obj = cell.getStringCellValue();
				break;
			default:
				break;
			}
			return obj.toString();
		} catch (Exception e) {
			return "";
		}
	}

}