excel上传解析

private List<Map<String, Object>> check(Workbook workBook) {
		List<Map<String, Object>> excelValueList = null;
		// 待导入的数据
		int import_wb_sheetNos = workBook.getNumberOfSheets();

		if (import_wb_sheetNos == 0) {
			throw new ServiceException("NumberOfSheets 数据为0，无数据可导入");
		}
		excelValueList = new ArrayList<>();
		// 创建人工号
		String EMP_CODE = UserUtil.getCurrentUser().getEmpCode();
		// 查询网点信息
		List<UmUserBranch> umUserBranchList = umUserBranchDao
				.queryUmUserBranchByCorpCode(null, EMP_CODE);
		if (umUserBranchList == null || umUserBranchList.size() == 0) {
			throw new ServiceException("没有网点权限");
		}
		UmUserBranch umUserBranch = umUserBranchList.get(0);

		for (int i = 0; i < import_wb_sheetNos; i++) {
			// 获取sheet
			Sheet import_sheet = workBook.getSheetAt(i);

			for (int heigth = 1, count = import_sheet.getLastRowNum() + 1; heigth < count; heigth++) {
				Row row = import_sheet.getRow(heigth);
				int FACE_AMT = 0;
				Cell FACE_AMT_CELL = row.getCell(0);
				try {
					FACE_AMT_CELL.setCellType(Cell.CELL_TYPE_STRING);
					FACE_AMT = Integer.valueOf(FACE_AMT_CELL.getStringCellValue());
				} catch (Exception e) {
					String errorMessage = workBook.getSheetName(i) + "的第"
							+ heigth + "行的面额不是数字";
					throw new ServiceException(errorMessage);
				}
				if(FACE_AMT <= 0) {
					String errorMessage = workBook.getSheetName(i) + "的第"
							+ heigth + "行的面额小于等于0";
					throw new ServiceException(errorMessage);
				}
				int BEN = 0;
				Cell BEN_CELL = row.getCell(1);
				try {
					BEN_CELL.setCellType(Cell.CELL_TYPE_STRING);
					BEN = Integer.valueOf(BEN_CELL.getStringCellValue());
				} catch (Exception e) {
					String errorMessage = workBook.getSheetName(i) + "的第"
							+ heigth + "行的数量不是数字";
					throw new ServiceException(errorMessage);

				}
				int SPEC = 0;
				Cell SPEC_CELL = row.getCell(2);
				try {
					SPEC_CELL.setCellType(Cell.CELL_TYPE_STRING);
					SPEC = Integer.valueOf(SPEC_CELL.getStringCellValue());
				} catch (Exception e) {
					String errorMessage = workBook.getSheetName(i) + "的第"
							+ heigth + "行的规格不是数字";
					throw new ServiceException(errorMessage);
				}
				if(!validateSpec(String.valueOf(SPEC))) {
					String errorMessage = workBook.getSheetName(i) + "的第"
							+ heigth + "行的规格不存在";
					throw new ServiceException(errorMessage);
				}
				Cell APPLYER_ID_CELL = row.getCell(3);
				String APPLYER_ID = null;
				if(null == APPLYER_ID_CELL) {
					String errorMessage = workBook.getSheetName(i) + "的第"
							+ heigth + "行的申请人不能为空";
					throw new ServiceException(errorMessage);
				}else{
					APPLYER_ID_CELL.setCellType(Cell.CELL_TYPE_STRING);
					if (StringUtils.isBlank(APPLYER_ID = APPLYER_ID_CELL.getStringCellValue())) {
						String errorMessage = workBook.getSheetName(i) + "的第"
								+ heigth + "行的申请人不能为空";
						throw new ServiceException(errorMessage);
						
					}
				}
				//新增申请人姓名
				Cell APPLYER_NAME_CELL = row.getCell(4); 
				String APPLYER_NAME = null;
				if(null == APPLYER_NAME_CELL) {
					String errorMessage = workBook.getSheetName(i) + "的第"
							+ heigth + "行的申请人姓名不能为空";
					throw new ServiceException(errorMessage);
				}else{
					APPLYER_NAME_CELL.setCellType(Cell.CELL_TYPE_STRING);
					if (StringUtils.isBlank(APPLYER_NAME = row.getCell(4).getStringCellValue())) {
						String errorMessage = workBook.getSheetName(i) + "的第"
								+ heigth + "行的申请人姓名不能为空";
						throw new ServiceException(errorMessage);
						
					}
				}
				Map<String, Object> value = new HashMap<>();
				value.put("FACE_AMT", FACE_AMT);
				value.put("NEED_BEN", BEN);
				value.put("SPEC", SPEC);
				value.put("APPLYER_ID", APPLYER_ID);
				value.put("APPLYER_NAME", APPLYER_NAME);
				value.put("createId", EMP_CODE);
				value.put("BUKRS", umUserBranch.getCorpCode());
				value.put("TOTAL_AMT", FACE_AMT * BEN * SPEC);
				value.put("CREATE_ID", EMP_CODE);
				value.put("BRANCH_CODE", umUserBranch.getBranchCode());
				value.put("BRANCH_NAME", umUserBranch.getBranchName());
				value.put("ZDISNUM", umUserBranch.getZDISNUM());
				// 待汇总状态
				value.put("EXTEND_STATUS",
						InvoiceDtlApplyExtendStatusEnum.WAIT_COLLECT.getValue());
				// 收派员待网点分配状态
				value.put("STATUS",
						InvoiceApplyStatusEnum.PEOPLE_WAIT_BRANCH_ALLOT
								.getValue());
				value.put("TYPE", InvoiceInputTypeEnum.FIXED_INVOICE.getValue());
				excelValueList.add(value);
			}
		}

		return excelValueList;
	}



package com.sf.novatar.tpl.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.dispatcher.multipart.MultiPartRequestWrapper;

import com.sf.novatar.tpl.enums.FileType;
import com.sf.novatar.tpl.servlet.HttpServletHolder;
import com.sfpay.framework.base.exception.ServiceException;

/**
 * 描述：
 */
public abstract class AbstractExcelReader {

	public abstract void doParse(Workbook workbook);

	public void Reader() throws Exception {

		Reader((InputStream inputStream) -> {

			String fileType = getType(inputStream);
			if (StringUtils.isBlank(fileType)
					|| (!FileType.XLSX.getValue().equals(fileType)
							&& !FileType.XLS_DOC.getValue().equals(fileType) && !FileType.XLSX_DOCX
							.getValue().equals(fileType))) {
				throw new ServiceException("上传失败,请选择Excel类型文件! ");
			}

		});
	}

	public void Reader(Validate validate) throws Exception {
		MultiPartRequestWrapper mpRequest = (MultiPartRequestWrapper) HttpServletHolder
				.getCurrentRequest();

		// 导入的文件
		File files = mpRequest.getFiles("excel")[0];
		if (null == files) {
			throw new FileNotFoundException("解析的文件不存在");
		}
		try (InputStream fileInputStream = new FileInputStream(files);) {
			validate.doValidate(fileInputStream);

			if (!fileInputStream.markSupported()) {
				try (BufferedInputStream bufferedInputStream = new BufferedInputStream(
						fileInputStream);) {
					Workbook workbook = null;
					if (POIFSFileSystem.hasPOIFSHeader(bufferedInputStream)) {
						workbook = new HSSFWorkbook(bufferedInputStream);
					} else if (POIXMLDocument
							.hasOOXMLHeader(bufferedInputStream)) {
						workbook = new XSSFWorkbook(
								OPCPackage.open(bufferedInputStream));
					} else {
						throw new ServiceException("没有获得相应的workbook");
					}
					doParse(workbook);
				}
			}
		}
	}

	/**
	 * * 判断文件类型
	 * 
	 * @param inputStream
	 * 
	 * @return 文件类型
	 * 
	 */
	private static String getType(InputStream inputStream) throws IOException {
		if (inputStream == null) {
			return null;
		}
		String fileHead = getFileContent(inputStream);
		if (fileHead == null || fileHead.length() == 0) {
			return null;
		}
		fileHead = fileHead.toUpperCase();
		FileType[] fileTypes = FileType.values();
		for (FileType type : fileTypes) {
			if (fileHead.startsWith(type.getValue())) {
				return type.getValue();
			}
		}
		throw new ServiceException("没有获取到响应的文件类型");
	}

	/**
	 * 得到文件头
	 * 
	 * @param filePath
	 *            文件路径
	 * @return 文件头
	 * @throws IOException
	 */
	private static String getFileContent(InputStream inputStream)
			throws IOException {
		int length = 28;
		byte[] b = null;
		if (inputStream != null) {
			inputStream.read(b = new byte[length], 0, length);
			inputStream.skip(-length);
		}
		return FileTypeJudge.bytesToHexString(b);
	}

	public interface Validate {

		public void doValidate(InputStream inputStream) throws Exception;
	}
}