程序小白随堂记：Java下载导出Excel

首先控制器代码如下

public  byte[] exportDetailToExcel(@RequestBody TmpPersonDetail tmpPersonDetailParam) throws Exception {
		
		ExcelTemplate excelemplate = new ExcelTemplate(ExcelConstants.模板id); //从数据库中寻找模板
		
		// 添加detail sheet
		//start
		this.addItemCompSheet(excelemplate, tmpPersonDetailParam);
		
		
		ExcelSheet reportSheet = new ExcelSheet("Report");
		excelemplate.addExcelSheet(reportSheet);
		
		Map<String, Object> simpleDataMap = new HashMap<String, Object>();
		simpleDataMap.put("month", NccMonthTypeEnum.GetAbbrByMonth(tPersonDetailParam.getYearMonth().substring(4, 6)));
		
		ExcelItem detailItem = new ExcelItem(ExcelConstants.SHEET_ITEM_TYPE_SIMPLE, simpleDataMap);
		reportSheet.addExcelItem(detailItem);
		//end
		return coreExcelExportService.exportExcel(excelemplate);
	}

通过 ExcelTemplate excelemplate = new ExcelTemplate（）;创建Excel模板
通过 ExcelSheet reportSheet = new ExcelSheet（“sheet页名”）
通过 addExcelSheet()方法向模板中添加sheet页

private void addItemCompSheet(ExcelTemplate excelemplate, TmpPersonDetail tmpPersonDetailParam) throws ParseException {
		ExcelSheet itemComSheet = new ExcelSheet("sheet页名");
		excelemplate.addExcelSheet(itemComSheet);

        //查询所要展示到excel上的数据
		List<NccComEntity> nccComList = nccMuService.getNccComparison();
		List<LinkedHashMap<String, Object>> allRowDataMapList = new ArrayList<LinkedHashMap<String, Object>>();
		
		for (NccComEntity nccComEntity : nccComList) {
			LinkedHashMap<String, Object> oneRowDataMap = new LinkedHashMap<String, Object>();
			oneRowDataMap.put("Item",                     nccComEntity.getType());
			oneRowDataMap.put("Financial Data",           nccComEntity.getNccData().get(0).getNcc());
			allRowDataMapList.add(oneRowDataMap);
		}
		ExcelItem itemCompItem = new ExcelItem(ExcelConstants.SHEET_ITEM_TYPE_LIST, "ItemComparisonList", allRowDataMapList, 2, 1); // （“数据类型”，“随便起个名”，“数据List”，“行”，“列”）

		// 添加excelitem到sheet
		itemComSheet.addExcelItem(itemCompItem);
		
		SimpleDateFormat sdf = new SimpleDateFormat("MMM", Locale.US);
		SimpleDateFormat sdfDateFormat = new SimpleDateFormat("yyyyMM");
		Date lastmonth = sdfDateFormat.parse(nccComList.get(0).getNccData().get(2).getYearmonth());
		List<LinkedHashMap<String, Object>> allRowMonthMapList = new ArrayList<LinkedHashMap<String, Object>>();
		LinkedHashMap<String, Object> rowMonthMap = new LinkedHashMap<String, Object>();
		rowMonthMap.put("Last Month",                 sdf.format(lastmonth));
		allRowMonthMapList.add(rowMonthMap);
		
		ExcelItem monthItem = new ExcelItem(ExcelConstants.SHEET_ITEM_TYPE_LIST, "ItemComparisonList", allRowMonthMapList, 1, 4);
		itemComSheet.addExcelItem(monthItem);
	}

以上为将一个sheet页添加到要导出的excel文件中，代码中的注解已经很详细了。

public byte[] exportExcel(ExcelTemplate excelTemplate) throws Exception {
		if (CollectionUtils.isEmpty(excelTemplate.getExcelSheetList())) {
			throw new BaseException("", "no sheet data error");
		}
		
		long start = System.currentTimeMillis();
		long start0 = start;
		long end = start;

		// 返回的excel字节数组
		byte[] out = null;
		XSSFWorkbook workbook = null;
		
		try {
			
			workbook=this.workbookData(excelTemplate);
			end = System.currentTimeMillis();
//			SXSSFWorkbook wb = new SXSSFWorkbook(workbook, 100);
			logger.debug("组装exportExcel End time: " + (end - start0));
			ByteArrayOutputStream output = new ByteArrayOutputStream();
			workbook.write(output);
			out = output.toByteArray();
			workbook.close();
			output.close();
			end = System.currentTimeMillis();
			logger.debug("exportExcel End time: " + (end - start0));
			
		} catch (Exception e) {
			throw new BaseException(e);
		}

		return out;
	}

public XSSFWorkbook workbookData(ExcelTemplate excelTemplate) throws Exception{
		long start = System.currentTimeMillis();
		long end = start;
		XSSFWorkbook workbook = null;
		InputStream in = null;
		ExcelTemplate excelTemplateFile = coreExcelMapper.getExcelTemplate(excelTemplate.getExcelTemplateId());
		if (excelTemplateFile == null) {
			logger.info("Excel模板【" + excelTemplate.getExcelTemplateId() + "】不存在,请补充");
			throw new Exception("Excel模板【" + excelTemplate.getExcelTemplateId() + "】不存在");
		}
		if ("YES".equals(excelTemplateFile.getTempLateFromFile())) {
			logger.info("从文件服务器获取Excel模板【" + excelTemplate.getExcelTemplateId() + "】");
			FileManager fileManager = new FileManager();
			fileManager.setType("TempLate");
			fileManager.setSubType("Excel");
			fileManager.setMyopsFileId(excelTemplate.getExcelTemplateId());
			byte[] fileByte = fileManagerService.getFile(fileManager);
			
			in = new ByteArrayInputStream(fileByte);
		} else {
			in = new ByteArrayInputStream(excelTemplateFile.getExcelTemplate());
		}
		workbook = new XSSFWorkbook(in);
		
		start = System.currentTimeMillis();
		
		// 循环所有sheet，进行数据填充
		for (ExcelSheet excelSheet : excelTemplate.getExcelSheetList()) {
			this.fillSheetData(workbook, excelSheet);
		}
		
		end = System.currentTimeMillis();
		logger.debug("fillSheetData time: " + (end - start));
		start = end;
		
		// 重新计算excel的所有公式
		HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
		end = System.currentTimeMillis();
		logger.debug("evaluateAllFormulaCells time: " + (end - start));
		start = end;
		return workbook;
	}



private void fillSheetData(XSSFWorkbook workbook, ExcelSheet excelSheet) throws Exception {
		// 获取target sheet
		XSSFSheet targetSheet = workbook.getSheet(excelSheet.getSheetName());
		
		if (targetSheet == null) {
//			throw new BaseException("", "no target sheet");
			throw new Exception("no target sheet");
		}
		
		// Item数据为空不需要填充
		if (CollectionUtils.isEmpty(excelSheet.getExcelItemList())) {
			return;
		}
		
		for (ExcelItem excelItem : excelSheet.getExcelItemList()) {
			// list数据类型时
			if (ExcelConstants.SHEET_ITEM_TYPE_LIST.equals(excelItem.getType())) {
				// 填充动态Title到sheet里
				this.setDynamicTitle(targetSheet, excelItem);
				// 填充List数据到sheet里
				this.fillListData(targetSheet, excelItem);
			} else if (ExcelConstants.SHEET_ITEM_TYPE_SIMPLE.equals(excelItem.getType())) {
				// simple类型的还没有实现
				this.fillSimpleData(targetSheet, excelItem);
			}
		}

以上为一个完整的Excel导出过程代码，程序小白的随做随记 不到位的地方还有很多我会继续努力的。