Apache POI 导入导出Excel表格

##添加maven依赖

	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.8</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.8</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml-schemas</artifactId>
		<version>3.8</version>
	</dependency>

Controller层

@Controller
@RequestMapping("imp")
public class ImportController extends BaseController {


@Autowired ImportService importService;

/**
 * 展示导入导出页面
 * @param request
	 * @return
 */
@RequestMapping(value = "import/view" , method = RequestMethod.GET)
public ModelAndView view (HttpServletRequest request){
	
	return new ModelAndView("imp/import");
	
}
/**
 * 导入Excel表格信息
 * @param import_id
 * @param file
 * @param request
 * @return
 */

@ResponseBody
@RequestMapping(value = "/import/{import_id}/excel", method = RequestMethod.POST)
public JsonResponse importExcel (@PathVariable int import_id, MultipartFile file, HttpServletRequest request){
	return importService.importExcel(import_id, file, getUser(request), request);
	
}
/**
 * 
 *导出Excel表格信息
 * @param import_id
 * @param ext
 * @param request
 * @param response
 */
@RequestMapping(value = "/import/{import_id}/excel", method = RequestMethod.GET)
public void downloadExcel(@PathVariable int import_id, @RequestParam("ext") String ext, HttpServletRequest request, HttpServletResponse response) {
	importService.createExcel(import_id, ext, getUser(request), request, response);
}
}

Service层

public interface ImportService {
//导入excel表格
public JsonResponse importExcel(int import_id, MultipartFile file, User loginUser, HttpServletRequest request);
//创建excel表格
public void createExcel(int import_id, String ext, User loginUser, HttpServletRequest request, HttpServletResponse response);

}

ServiceImpl层

@Service
public class ImportServiceImpl implements ImportService {

private Logger logger = Logger.getLogger(this.getClass());

@Autowired ImportMapper importMapper;
@Transactional
								//ID            文件                                                                    登录用户                                        request      
public JsonResponse importExcel(int import_id, MultipartFile multipartFile, User loginUser, HttpServletRequest request) {
	//通过import_id查找文件配置数据
	ImportConfig  importConfig = importMapper.queryImportConfig(import_id);
	//如果文件配置数据为空则直接抛出异常
	if(importConfig == null){
		//抛出异常信息
		throw ExceptionBuilder.unvalidated("未找到导入配置数据");
		
	}
	//定义错误
	StringBuffer errors = new StringBuffer();
	//获取输入者
	Importer importer = (Importer) SpringContext.getBean(importConfig.getImporter());
	//先定义Workbook文档对象为空
	Workbook wb = null;
	//创建文件
	try {
		wb = WorkbookFactory.create(multipartFile.getInputStream());
	} catch (Exception e) {
		logger.error(e);
		throw ExceptionBuilder.unvalidated("创建文件失败！");
		
	}
	
	//获取excel的行信息
	Sheet sheet = wb.getSheetAt(importer.getSheetNum());
	//获取最后一行的行数
	int lastRowNum = sheet.getLastRowNum();
	//如果刚开始的第一行小于最后一行的行数则抛出异常
	if (importer.getStartNum() > lastRowNum) {
		throw ExceptionBuilder.unvalidated("未找到可导入的数据！");
	}
	//获取这一列的所有信息
	List<ImportColumn> columns = importer.getImportColumns();
	//如果列的长度等于0则获取这一列
	if (columns.size() == 0)
		columns = importer.getImportColumns(request);
	
	//选择模板
	checkTemplate(sheet.getRow(importer.getTitleNum()), columns);

	List<List<ImportColumnUnique>> uniqueFields = new ArrayList<List<ImportColumnUnique>>();
	//获取这一列的所有数据
	List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();

	//循环获取这一列的每一行信息
	for (int i = importer.getStartNum(); i <= lastRowNum; i++) {
		Row row = sheet.getRow(i);

		if (row == null)
			continue;

		Map<String, Object> data = new HashMap<String, Object>();

		boolean rowValid = true;
		
		StringBuffer rowErrors = new StringBuffer();

		List<ImportColumnUnique> uniqueRowFields = new ArrayList<ImportColumnUnique>();

		for (int j = 0; j < columns.size(); j++) {
			ImportColumn column = columns.get(j);

			if (column.isUnimport()) {
				continue;
			}

			boolean cellValid = true;

			Cell cell = row.getCell(j);

			Object cellValue = null;

			try {
				cellValue = disposeCellValue(cell, column, wb);
			} catch (Exception e) {
				cellValid = false;
				rowErrors.append(column.getColumn_name_cn() + "类型错误！");
			}

			if (cellValid && column.isUnique()) {
				if (cellValue == null || UtilString.empty(cellValue.toString())) {
					cellValid = false;
					rowErrors.append(column.getColumn_name_cn() + "主键不能为空！");
				} else {
					uniqueRowFields.add(new ImportColumnUnique(i + 1, column, cellValue));
				}
			} else {
				if (cellValid) {
					cellValid = checkEmpty(cellValue, column, rowErrors);
				}
			}

			if (cellValid) {
				cellValid = checkLengthLimit(cellValue, column, rowErrors);
			}

			if (cellValid) {
				cellValid = checkDate(cellValue, column, rowErrors);
			}

			if (cellValid) {
				cellValid = checkRange(cellValue, column, rowErrors);
			}

			if (cellValid) {
				cellValid = checkKeyValues(cellValue, column, rowErrors);
			}

			if (cellValid) {
				if (column.getKeyValues() != null) {
					if (column.isMultiple()) {
						if (cellValue != null && !UtilString.empty(cellValue.toString())) {
							String[] array = cellValue.toString().split(",");
							List<Object> values = new ArrayList<Object>();
							for (String string : array) {
								for (Map<Object, Object> map : column.getKeyValues()) {
									if (map.containsKey(string)) {
										values.add(map.get(string));
									}
								}
							}
							data.put(column.getColumn_name_en(), values);
						} else {
							data.put(column.getColumn_name_en(), "");
						}
					} else {
						for (Map<Object, Object> map : column.getKeyValues()) {
							if (map.containsKey(cellValue)) {
								data.put(column.getColumn_name_en(), map.get(cellValue));
								break;
							}
						}
					}

				} else {
					data.put(column.getColumn_name_en(), cellValue);
				}
			} else {
				rowValid = false;
			}
		}

		if (uniqueRowFields.size() != 0) {
			for (int u = 0; u < uniqueFields.size(); u++) {
				if (uniqueFields.get(u).containsAll(uniqueRowFields)) {
					rowValid = false;
					for (int f = 0; f < uniqueRowFields.size(); f++) {
						rowErrors.append(uniqueRowFields.get(f).getImportColumn().getColumn_name_cn());
						if (f != uniqueRowFields.size() - 1) {
							rowErrors.append("、");
						}
					}
					rowErrors.append("和第" + uniqueFields.get(u).get(0).getRowNum() + "行重复！");
					break;
				}
			}
			uniqueFields.add(uniqueRowFields);
		}

		if (rowValid) {
			rowValid = importer.checkRow(sheet.getRow(i), data, columns, rowErrors);
		}

		if (rowValid) {
			rowValid = importer.checkRows(sheet.getRow(i), datas, columns, rowErrors);
		}

		if (rowValid) {
			datas.add(data);
		} else {
			errors.append("\r\n");
			errors.append("第" + (i + 1) + "行：");
			errors.append(rowErrors);
		}

	}

	int total_dobatch = 0;

	boolean success = errors.length() == 0 || importer.ignore() == true;
	if (success && datas.size() != 0) {
		total_dobatch = importer.doBatch(datas, loginUser, request);
	}

	ImportResult importResult = new ImportResult();

	importResult.setSuccess(success);
	importResult.setTotal(lastRowNum - importer.getStartNum() + 1);
	importResult.setTotal_success(datas.size());
	importResult.setTotal_dobatch(total_dobatch);
	importResult.setErrors(errors);
	//向数据库添加一条信息
	importMapper.insertImportLog(new ImportLog(import_id, importResult, loginUser.getUsername()));

	return JsonResponse.success(Constant.MESSAGE_SUCCESS, importResult);
};

public void createExcel(int import_id, String ext, User loginUser, HttpServletRequest request, HttpServletResponse response) {

	ImportConfig importConfig = importMapper.queryImportConfig(import_id);

	if (importConfig == null) {
		throw ExceptionBuilder.unvalidated("未找到导入配置数据！");
	}

	Importer importer = (Importer) SpringContext.getBean(importConfig.getImporter());

	Workbook wb = null;

	if ("xlsx".equalsIgnoreCase(ext)) {
		wb = new SXSSFWorkbook(5000);
	} else {
		wb = new HSSFWorkbook();
	}

	CellStyle headStyle1 = UtilPoi.CreateHeadStyle(wb);

	CellStyle headStyle2 = UtilPoi.CreateHeadStyle(wb, HSSFColor.LIGHT_YELLOW.index);

	Sheet sheet = wb.createSheet(importConfig.getText());

	List<ImportColumn> columns = importer.getImportColumns(request);
	if (columns.size() == 0)
		columns = importer.getImportColumns();

	Drawing drawing = sheet.createDrawingPatriarch();

	Row row = sheet.createRow(0);
	for (int i = 0; i < columns.size(); i++) {

		sheet.setColumnWidth(i, 25 * 256);

		Cell cell = row.createCell(i);

		if (columns.get(i).isUnimport()) {
			cell.setCellStyle(headStyle2);
			Comment ct = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, i, 0, 5, 2));
			ct.setString(new XSSFRichTextString("非导入列，对导入操作不产生影响"));
		} else {
			cell.setCellStyle(headStyle1);
		}

		cell.setCellValue(columns.get(i).getColumn_name_cn());

		if (columns.get(i).getKeyValues() != null && columns.get(i).getKeyValues().size() <= 40) {

			Sheet dataValidationSheet = wb.createSheet(columns.get(i).getColumn_name_cn());

			DataValidationHelper helper = sheet.getDataValidationHelper();

			CellRangeAddressList addressList = new CellRangeAddressList(0, 65535, i, i);

			// 设置下拉框数据
			int size = columns.get(i).getKeyValues().size();
			for (int j = 0; j < size; j++) {
				dataValidationSheet.createRow(j).createCell(0).setCellValue((String) columns.get(i).getKeyValues().get(j).entrySet().iterator().next().getKey());
			}

			String strFormula = columns.get(i).getColumn_name_cn() + "!$A$1:$A$" + size;

			DataValidationConstraint constraint = null;
			if ("xlsx".equalsIgnoreCase(ext)) {
				constraint = new XSSFDataValidationConstraint(ValidationType.LIST, strFormula);
			} else {
				constraint = DVConstraint.createFormulaListConstraint(strFormula);
			}
			sheet.addValidationData(helper.createValidation(constraint, addressList));
		}

	}

	sheet.createFreezePane(0, importer.getStartNum());

	response.reset();

	try {
		response.setContentType("application/octet-stream");
		response.addHeader("Content-Disposition", "attachment;filename=" + new String((importConfig.getText() + "." + ext).getBytes("gb2312"), "iso8859-1"));

		wb.write(response.getOutputStream());

	} catch (Exception e) {
		throw ExceptionBuilder.internal(e);
	}
};

private void checkTemplate(Row row, List<ImportColumn> columns) {
	for (int i = 0; i < columns.size(); i++) {
		if (row.getCell(i) == null || row.getCell(i).getStringCellValue() == null || !UtilString.equals(columns.get(i).getColumn_name_cn(), row.getCell(i).getStringCellValue().trim())) {
			throw ExceptionBuilder.unvalidated("模板格式不正确，请重新下载模板！");
		}
	}
}

private Object disposeCellValue(Cell cell, ImportColumn column, Workbook wb) {
	if (cell != null) {
		if ("string".equals(column.getColumn_type())) {
			if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
				DecimalFormat df = new DecimalFormat("0");
				return df.format(cell.getNumericCellValue()) + "";
			} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
				return cell.getStringCellValue().trim();
			} else {
				return cell.toString().trim();
			}
		} else if ("number".equals(column.getColumn_type())) {
			if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
				return cell.getNumericCellValue();
			} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
				return Double.parseDouble(cell.getStringCellValue().trim());
			} else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
				FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
				try {
					return evaluator.evaluate(cell).getNumberValue();
				} catch (Exception e) {
					return cell.getNumericCellValue();
				}
			} else {
				return 0.0;
			}
		} else if ("integer".equals(column.getColumn_type())) {
			if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
				return (int) cell.getNumericCellValue();
			} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
				return Integer.parseInt(cell.getStringCellValue().trim());
			} else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
				FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
				try {
					return evaluator.evaluate(cell).getNumberValue();
				} catch (Exception e) {
					return cell.getNumericCellValue();
				}
			} else {
				return 0;
			}
		} else if ("date".equals(column.getColumn_type())) {
			if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
				return UtilDate.getDateTimeStr(DateUtil.getJavaDate(cell.getNumericCellValue()), column.getDate_fomart());
			} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
				return cell.getStringCellValue().trim();
			}
		}
	}
	return null;
}

private boolean checkEmpty(Object cellValue, ImportColumn column, StringBuffer errors) {
	if (!column.isEmpty()) {
		if (cellValue == null || UtilString.empty(cellValue.toString())) {
			errors.append(column.getColumn_name_cn() + "不能为空！");
			return false;
		}
	}
	return true;
}

private boolean checkLengthLimit(Object cellValue, ImportColumn column, StringBuffer errors) {
	if (cellValue != null && !UtilString.empty(cellValue.toString())) {
		if (column.getLength_limit() != 0 && "string".equals(column.getColumn_type()) && (cellValue.toString()).length() > column.getLength_limit()) {
			errors.append(column.getColumn_name_cn() + "长度不能超过" + column.getLength_limit() + "！");
			return false;
		}
	}
	return true;
}

private boolean checkRange(Object cellValue, ImportColumn column, StringBuffer errors) {
	if (cellValue != null && !UtilString.empty(cellValue.toString())) {
		if (column.getMin() != null) {
			if ("number".equals(column.getColumn_type())) {
				if ((Double) cellValue < Double.parseDouble(column.getMin())) {
					errors.append(column.getColumn_name_cn() + "不能小于" + Double.parseDouble(column.getMin()) + "！");
					return false;
				}
			} else if ("integer".equals(column.getColumn_type())) {
				if ((Integer) cellValue < Integer.parseInt(column.getMin())) {
					errors.append(column.getColumn_name_cn() + "不能小于" + Double.parseDouble(column.getMin()) + "！");
					return false;
				}
			} else if ("date".equals(column.getColumn_type())) {
				if (UtilDate.compareDate(cellValue.toString(), column.getMin().toString(), column.getDate_fomart()) == -1) {
					errors.append(column.getColumn_name_cn() + "不能早于" + column.getMin().toString() + "！");
					return false;
				}
			}
		}
		if (column.getMax() != null) {
			if ("number".equals(column.getColumn_type())) {
				if ((Double) cellValue > Double.parseDouble(column.getMax())) {
					errors.append(column.getColumn_name_cn() + "不能大于" + Double.parseDouble(column.getMax()) + "！");
					return false;
				}
			} else if ("integer".equals(column.getColumn_type())) {
				if ((Integer) cellValue > Integer.parseInt(column.getMax())) {
					errors.append(column.getColumn_name_cn() + "不能大于" + Double.parseDouble(column.getMax()) + "！");
					return false;
				}
			} else if ("date".equals(column.getColumn_type())) {
				if (UtilDate.compareDate(cellValue.toString(), column.getMax().toString(), column.getDate_fomart()) == -1) {
					errors.append(column.getColumn_name_cn() + "不能晚于" + column.getMax().toString() + "！");
					return false;
				}
			}
		}
	}
	return true;
}

private boolean checkDate(Object cellValue, ImportColumn column, StringBuffer errors) {
	if (cellValue != null && !UtilString.empty(cellValue.toString())) {
		if ("date".equals(column.getColumn_type())) {
			SimpleDateFormat format = new SimpleDateFormat(column.getDate_fomart());
			try {
				format.setLenient(false);
				format.parse(cellValue.toString());
			} catch (ParseException e) {
				errors.append(column.getColumn_name_cn() + "日期格式不符合" + column.getDate_fomart() + "！");
				return false;
			}
		}
	}
	return true;
}

private boolean checkKeyValues(Object cellValue, ImportColumn column, StringBuffer errors) {
	if (cellValue != null && !UtilString.empty(cellValue.toString())) {
		if (column.getKeyValues() != null) {
			if (column.isMultiple()) {
				String[] array = cellValue.toString().split(column.getMultiple_split());
				boolean valid = true;
				for (String string : array) {
					boolean hasKey = false;
					for (Map<Object, Object> map : column.getKeyValues()) {
						if (map.containsKey(string)) {
							hasKey = true;
							break;
						}
					}
					if (!hasKey) {
						errors.append(column.getColumn_name_cn() + ":" + string + "不在有效的取值范围内，多个值用" + column.getMultiple_split() + "分割！");
						valid = false;
					}
				}
				return valid;
			} else {
				for (Map<Object, Object> map : column.getKeyValues()) {

					if (map.containsKey(cellValue)) {
						return true;
					}
				}
				errors.append(column.getColumn_name_cn() + ":" + cellValue + "不在有效的取值范围内！");
			}
			return false;
		}
	}
	return true;
	}

 }