POI 数据导出到excel

旧城等待， 2022-03-15 01:54 445阅读 0赞

## 一.导入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）操作][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);
    		}
    	}

[Excel_HSSF_XSSF]: http://poi.apache.org/components/spreadsheet/