java中Excel导出模板

1、首先在pom文件中导入poi的jar：

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<exclusions>
				<exclusion>
					<artifactId>commons-collections4</artifactId>
					<groupId>org.apache.commons</groupId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<artifactId>commons-collections4</artifactId>
			<groupId>org.apache.commons</groupId>
			<version>4.4</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
</dependency>

2、后端代码如下：

public void getExportListToFinancialOut(HttpServletResponse response, HttpServletRequest request,
														   FinacialOutParam finacialOutParam) {													   
		//1、查询结果集
		List<FinacialOutVo> exportList = accountInfoDao.selecFinancialOutToExport(finacialOutParam);
		
		//2、将结果集打包，准备生成Excel（注意：String[]拼接的顺序就是生成后Excel表头的顺序）
		List<String[]> resultList = new ArrayList<>();
		for(FinacialOutVo finacialOutVo : exportList){
			String[] strings = new String[]{
					finacialOutVo.getUniquelyNum(),finacialOutVo.getInvoiceCode(),finacialOutVo.getInvoiceNo(),
											finacialOutVo.getAmount()+"",finacialOutVo.getTaxRate(),
											finacialOutVo.getInvVat()+"",finacialOutVo.getUnCertifiedSubject(),
											finacialOutVo.getCertifiedSubject(),finacialOutVo.getReferenceStatus(),
											finacialOutVo.getDocumentId(),finacialOutVo.getDocAuditedDate(),
											finacialOutVo.getSourceModule(),finacialOutVo.getComeSystem(),
											finacialOutVo.getDocAuditedBy(),finacialOutVo.getAccountStatus(),
											finacialOutVo.getUploadTime(),finacialOutVo.getUserName()};

			resultList.add(strings);
		}

		//3、设置Excel标头
		List<String> title = new ArrayList<>();
		title.add("出账批名");
		title.add("发票代码");
		title.add("发票号码");
		title.add("发票金额");
		title.add("税率");
		title.add("税额");
		title.add("借方科目");
		title.add("贷方科目");
		title.add("引用状态");
		title.add("业务号");
		title.add("审核时间");
		title.add("来源渠道");
		title.add("来源系统");
		title.add("审核人员");
		title.add("上传状态");
		title.add("上传时间");
		title.add("录入人员");

		//4、生成Excel
		String fileName = "财务出账数据";
		ExcelUtil.getHSSFWorkbookTome(response,request,fileName,title,resultList);
	}


	//导出模板
	/**
	 *
	 * @param response
	 * @param request
	 * @param sheetName 文件名
	 * @param title 标题
	 * @param values 内容值
	 */
	public static void getHSSFWorkbookTome(HttpServletResponse response, HttpServletRequest request,
										   String sheetName, List<String> title, List<String[]> values) {
		//创建Excel对象
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFCellStyle stylePALE_BLUE = workbook.createCellStyle();
		stylePALE_BLUE.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
		stylePALE_BLUE.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		//创建工作表sheet
		HSSFSheet sheet = workbook.createSheet();
		// 添加表头
		HSSFRow row = sheet.createRow(0);
		// 设置高度
		row.setHeight((short) (17.5 * 20));
		// 插入第一行数据的表头
		HSSFCell cell = null;
		HSSFPalette pt = workbook.getCustomPalette();

		//表头赋值
		for (int i = 0; i < title.size(); i++) {
			// 宽度
			sheet.setColumnWidth(i, 21 * 256);
			// 设置样式赋值
			cell = row.createCell(i);
			cell.setCellStyle(stylePALE_BLUE);
			cell.setCellValue(title.get(i));
		}

		//给表格内容赋值
		for(int i = 0; i < values.size(); i++){
			HSSFRow row1 = sheet.createRow(i+1);
			for (int j = 0; j < title.size(); j++) {
				cell = row1.createCell(j);
				if(values.get(i)[j] == null || "null".equals(values.get(i)[j])){
					cell.setCellValue("");
				}else{
					cell.setCellValue(values.get(i)[j]+"");
				}
			}
		}

		// 响应到客户端
		try {
			response.setCharacterEncoding("utf-8");
			OutputStream out = null;
			response.setContentType("application/x-msdownload");
			ExcelUtil.setResponseHeader(response, sheetName.toString(), request);
			out = response.getOutputStream();
			workbook.write(out);
			out.flush();
			out.close();
		}catch (Exception e){
			e.printStackTrace();
		}
	}

	// 发送响应流方法
	public static void setResponseHeader(HttpServletResponse response, String fileName,HttpServletRequest request) {
		
		try {
			// 设置表文件名的字符编码，不然中文文件名会乱码
			fileName = encodeFileName(fileName, request);
			response.setContentType("application/octet-stream;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName+".xls", "UTF-8"));
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	* 
	* @Title: encodeFileName
	* @Description: 导出文件转换文件名称编码
	* @param @param fileNames
	* @param @param request
	* @param @return    设定文件
	* @return String    返回类型
	* @throws
	*/
	public static String encodeFileName(String fileNames ,HttpServletRequest request) {  
	       String codedFilename = null;  
	       try {  
	           String agent = request.getHeader("USER-AGENT");
	           if (null != agent && -1 != agent.indexOf("MSIE") || null != agent  
	                   && -1 != agent.indexOf("Trident") || null != agent && -1 != agent.indexOf("Edge")) {// ie浏览器及Edge浏览器  
	               String name = java.net.URLEncoder.encode(fileNames, "UTF-8");  
	               codedFilename = name;  
	           } else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,Chrome等浏览器  
	               codedFilename = new String(fileNames.getBytes("UTF-8"), "iso-8859-1");  
	           }
	       } catch (Exception e) {  
	           e.printStackTrace();  
	       }  
	       return codedFilename ;  
	
	}