java实现下载EXCEL模板及上传功能

1.VO准备

@Data
public class BuSuppMarterQuotaSettingImportVO implements Serializable {
	/**
	 * 序号
	 */
	@Excel(name = "序号" ,orderNum = "0",width = 20)
	private String seriNo;

	/**
	 * 物料编码
	 */
	@Excel(name="物料编码",orderNum = "1",width = 20)
	private String materCode;
	/**
	 * 物料名称
	 */
	@Excel(name="物料名称",orderNum = "2",width = 20)
	private String materName;
	/**
	 * PPM指标值
	 */
	@Excel(name="PPM指标值",orderNum = "3",width = 20)
	private String ppmQuota;
	/**
	 * IPTV指标
	 */
	@Excel(name="IPTV指标",orderNum = "4",width = 20)
	private String iptvQuota;
	/**
	 * 启停状态
	 */
	@Excel(name="启停状态",orderNum = "5",width = 20)
	private String startState;

}

2.Controller层

	/**
	 * 下载模板
	 * @param response
	 * @return
	 */
	@PostMapping(value = "/downloadTemplate", produces = "application/json;charset=UTF-8")
	@PreAuth(value = "buSuppMarterQuotaSettingYW:importExcel", project = ParamConstants.DEFAULT_PROJECT, connect = ParamConstants.DEFAULT_PERMISSION_CONNECT, logical = Logical.OR)
	@Log(title = TITLE_CONTENT + "下载模板", navName = NAV_NAME, actionUniqueKey = NEMU_NAME + "," + "buSuppMarterQuotaSettingYW:importExcel", interfaceType = InterfaceType.INSIDE, logType = "2")
	public void downloadTemplate(@RequestBody SrmRecVO vo, HttpServletResponse response) {
		buSuppMarterQuotaSettingYWService.downloadTemplate(response);
	}

3.Impl层

	/**
	 * 下载模板
	 *
	 * @param response
	 * @return
	 */
	@Override
	public void downloadTemplate(HttpServletResponse response) {
		List list = new ArrayList<>();
		HSSFWorkbook workbook = getImportErrorExcel(list, BuSuppMarterQuotaSettingImportVO.class);
		ServletOutputStream fos = null;
		try {
			fos = response.getOutputStream();
			//mime类型
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			response.setHeader("Content-disposition", "attachment;filename=template.xls");
			workbook.write(fos);
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取Excel信息
	 *
	 * @param list
	 * @return
	 */
	private HSSFWorkbook getImportErrorExcel(List<?> list, Class<?> objectClass) {
		ExportParams exportParams = new ExportParams("导入模板", "物料价格维护单", ExcelType.HSSF);
		Workbook book = ExcelExportUtil.exportExcel(exportParams, objectClass, list);
		HSSFWorkbook workbook = (HSSFWorkbook) book;
		HSSFSheet sheet = workbook.getSheetAt(0);
		//清空表格数据
		sheet.shiftRows(1, sheet.getLastRowNum(), -1, true, true);
		//冻结第一行7列
		sheet.createFreezePane(6, 1);
		//创建批注的绘图
		HSSFPatriarch patr = sheet.createDrawingPatriarch();
		HSSFRow row = sheet.getRow(0); //获取表头
		for (int i = 0; i < row.getLastCellNum(); i++) { //循环表头每个单元格，并设置批注
			//设置样式
			HSSFCell cell = row.getCell(i);
			CellStyle style = workbook.createCellStyle();
			HSSFFont font = workbook.createFont();
			font.setBold(true); //字体加粗
			style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());//设置背景色
			style.setFillPattern(FillPatternType.FINE_DOTS); //设置图案样式
			style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
			style.setAlignment(HorizontalAlignment.CENTER); //水平居中
			style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
			style.setLocked(true); // 单元格样式锁定 不可编辑

			//设置以下几列字体颜色
			if (cell.getStringCellValue().equals(BuSuppPerformanceManage.materCode) || cell.getStringCellValue().equals(BuSuppPerformanceManage.ppmQuota)
				|| cell.getStringCellValue().equals(BuSuppPerformanceManage.iptvQuota) || cell.getStringCellValue().equals(BuSuppPerformanceManage.startState)
			) {
				font.setColor(Font.COLOR_RED);
			}

			//设置批注
			if (cell.getStringCellValue().equals(BuSuppPerformanceManage.ppmQuota)) {
				HSSFComment comment = patr.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) i, 0, (short) (i + 1), 4)); //批注位置
				comment.setString(new HSSFRichTextString(BuSuppPerformanceManage.enterNumber));//设置批注内容
				cell.setCellComment(comment);
				font.setColor(Font.COLOR_RED);
			}
			if (cell.getStringCellValue().equals(BuSuppPerformanceManage.iptvQuota)) {
				HSSFComment comment = patr.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) i, 0, (short) (i + 1), 4)); //批注位置
				comment.setString(new HSSFRichTextString(BuSuppPerformanceManage.enterNumber));//设置批注内容
				cell.setCellComment(comment);
				font.setColor(Font.COLOR_RED);
			}
			if (cell.getStringCellValue().equals(BuSuppPerformanceManage.startState)) {
				HSSFComment comment = patr.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) i, 0, (short) (i + 1), 4)); //批注位置
				comment.setString(new HSSFRichTextString(BuSuppPerformanceManage.onOff));//设置批注内容
				cell.setCellComment(comment);
				font.setColor(Font.COLOR_RED);
			}
			cell.setCellStyle(style);
			style.setFont(font);
		}
		return workbook;
	}

效果：

结语：常规模板下载还是比较简单，下节记录一下导入功能，这个相对来说复杂点