POI 数据导出到excel

一.导入POI的jar包

        <poi.version>3.9</poi.version>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${poi.version}</version>
        </dependency>

 二.代码实现

####1.POI介绍
要想使用POI对Excel进行操作，我们需要先了解一下Excel的两种版本：一种是97-2003版本扩展名是“.xls”；一种是2007版本扩展名是“.xlsx”。POI分别针对这两种版本需要导入的jar包不同，操作类也不同。

HSSF：操作的是.xls；XSSF：操作的是.xlsx。

不管哪种操作，基本思路都是一致，先要对应一个Excel文件，然后在对应文件中的某个sheet，接下来在操作某一行和这一行中的某一列。对应POI包：文件（webbook）、sheet（sheet）、行（row）和具体单元格（cell）。

详细操作请参照POI官网的Excel（HSSF/XSSF）操作
 

简单的实现将list中的对象数据导入excel并进行下载

如直接将excel保存到项目服务路径 则在xssfWorkbook.write(fileOutputStream); 指明fileOutputStream 输出的路径。

  
        @ResponseBody
	@RequestMapping(value = "download")
	public ResponseData downloadingDetail(UserWalletRecordPageDTO userWalletRecordPageDTO, HttpServletRequest request,
			HttpServletResponse response, Model model) {

		// 获取钱包明细记录
		List<JoinUserWalletRecord> joinUserWalletRecordList = userWalletRecordService
				.listJoinUserWalletRecord(userWalletRecordPageDTO.getJoinUserWalletRecordExample());

		XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
		XSSFSheet createSheet = xssfWorkbook.createSheet("钱包明细");
		// 创建第一行
		XSSFRow createRow = createSheet.createRow(0);
		createRow.createCell(0).setCellValue("公司名称");
		createRow.createCell(1).setCellValue("用户编号");
		createRow.createCell(2).setCellValue("历史余额");
		createRow.createCell(3).setCellValue("当前金额");
		createRow.createCell(4).setCellValue("操作金额");
		createRow.createCell(5).setCellValue("操作类型");
		createRow.createCell(6).setCellValue("交易类型");
		createRow.createCell(7).setCellValue("交易备注");
		createRow.createCell(8).setCellValue("创建时间");
		if (null != joinUserWalletRecordList) {
			for (int i = 0; i < joinUserWalletRecordList.size(); i++) {
				// 创建第1+i行
				Row createRow2 = createSheet.createRow(i + 1);
				createRow2.createCell(0).setCellValue(joinUserWalletRecordList.get(i).getCompanyName());
				createRow2.createCell(1).setCellValue(joinUserWalletRecordList.get(i).getUserNo());
				createRow2.createCell(2).setCellValue(joinUserWalletRecordList.get(i).getBeforeBalanceY());
				createRow2.createCell(3).setCellValue(joinUserWalletRecordList.get(i).getAfterBalanceY());
				createRow2.createCell(4).setCellValue(joinUserWalletRecordList.get(i).getOperationBalanceY());

				String operateType = "";
				if (joinUserWalletRecordList.get(i).getType().equals("1")) {
					operateType = "提现";
				} else if (joinUserWalletRecordList.get(i).getType().equals("2")) {
					operateType = "进账";
				}
				createRow2.createCell(5).setCellValue(operateType);
				// 交易类型
				String transactionType = "";
				if (joinUserWalletRecordList.get(i).getTransactionType().equals("1")) {
					transactionType = "分润";
				} else if (joinUserWalletRecordList.get(i).getTransactionType().equals("2")) {
					transactionType = "推荐奖";
				} else if (joinUserWalletRecordList.get(i).getTransactionType().equals("3")) {
					transactionType = "提现";
				}
				createRow2.createCell(6).setCellValue(transactionType);
				createRow2.createCell(7).setCellValue(joinUserWalletRecordList.get(i).getTransactionDescribe());
				createRow2.createCell(8).setCellValue(
						DateUtils.date2String(joinUserWalletRecordList.get(i).getCreateDate(), "yyyy-MM-dd HH:mm:ss"));
			}
		}
		// 创建一个文件夹
		if (!new File(request.getSession().getServletContext().getRealPath("/") + "/download").exists()) {
			new File(request.getSession().getServletContext().getRealPath("/") + "/download").mkdir();
		}
		// 创建文件
		try {
			FileOutputStream fileOutputStream = new FileOutputStream(
					request.getSession().getServletContext().getRealPath("/") + "/download/" + "钱包明细" + ".xlsx");

			xssfWorkbook.write(fileOutputStream);
			// 获取resp输出流
			OutputStream out = response.getOutputStream();

			String filename = "钱包明细" + ".xlsx";
			// mime属性
			String mimeType = request.getSession().getServletContext().getMimeType(filename);

			// 响应头
			response.setHeader("Content-Type", mimeType);
			// 获取文件流
			File file = new File(
					request.getSession().getServletContext().getRealPath("/") + "/download/" + "钱包明细" + ".xlsx");
			InputStream is = new FileInputStream(file);
			String agent = request.getHeader("User-Agent");
			// 判断是否是火狐浏览器
			if (agent.contains("Firefox")) {
				filename = base64EncodeFileName(filename);
			} else {
				filename = URLEncoder.encode(filename, "UTF-8");
			}

			byte[] buf = new byte[1024];
			int len;
			// 响应头
			response.setHeader("Content-Disposition", "attachment;filename=" + filename);

			// 输出流
			while ((len = is.read(buf)) != -1) {
				out.write(buf, 0, len);
			}
			is.close();
			out.flush();
			out.close();

			File file1 = new File(
					request.getSession().getServletContext().getRealPath("/") + "/download/" + "钱包明细" + ".xlsx");
			file1.delete();

			// response.getWriter().append("Served at: ").append(request.getContextPath());

		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 
	 * 描述：针对于火狐浏览器的转码
	 * 
	 * @author liuqiyu
	 * @date 2018年10月23日
	 * @param fileName
	 * @return
	 */
	private String base64EncodeFileName(String fileName) {
		BASE64Encoder base64Encoder = new BASE64Encoder();
		try {
			return "=?UTF-8?B?" + new String(base64Encoder.encode(fileName.getBytes("UTF-8"))) + "?=";
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}