POI报表导出

Dear 丶 2022-05-19 01:16 207阅读 0赞

在实际项目中会遇到一些报表导出功能的开发,接下来废话不多说,直接把我在项目中用到的分享给大家

一:pom.xml导入依赖

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.9</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.9</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-scratchpad</artifactId>
                <version>3.9</version>
            </dependency>
            <dependency>
                <groupId>xerces</groupId>
                <artifactId>xercesImpl</artifactId>
                <version>2.9.1</version>
            </dependency>

二:核心代码

InputStream inputStream = null;
    		try {
    			inputStream = ProductController.class.getClassLoader()
    					.getResourceAsStream("templet/report/productInformation.xlsx");模板路径
    		} catch (Exception e) {
    			logger.error("-----------读取模板失败-------------");
    		}
    		XSSFWorkbook wb = new XSSFWorkbook(inputStream);
    		XSSFSheet sheet0 = wb.getSheetAt(0);//获取第一个单元格
    		XSSFCellStyle cstyle = wb.createCellStyle(); // 样式对象
    		// 设置边框
    		cstyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    		cstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    		cstyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    		cstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    		XSSFFont font1 = wb.createFont();
    		font1.setFontHeightInPoints((short) 10);
    		font1.setFontName("Calibri");
    		cstyle.setFont(font1);
    		cstyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    		cstyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    	   // 数据处理
    		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    		XSSFRow row = null;
    		XSSFCell cell = null;
    
    		// 货品列表
    		Page showPage = new Page(0, 500);
    		logger.info(URLDecoder.decode(productName, "UTF-8"));
    		List<ProductOutputInfo> productList = this.productService.queryProductListByParems(showPage, URLDecoder.decode(productName, "UTF-8"), productDesc, productId);//实际项目中需要的数据列表
    		if(null != productList && productList.size()>0){
               //单独封装一个方法用于进行单元格cell填充内容
    			SetProductExcel(productList, sheet0, cstyle, row, cell, formatter);
    			// 末尾
    			String tempFileName = "productInformation.xlsx";
    			response.setContentType("application/vnd.ms-excel");
    			response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(tempFileName, "utf-8"));
    			OutputStream outputStream = response.getOutputStream();
    			try {
    				wb.write(outputStream);
    			} catch (Exception e) {
    				// TODO: handle exception
    			} finally {
    				outputStream.flush();
    				outputStream.close();
    			}

三:填充数据方法代码:

private void SetProductExcel(List<ProductOutputInfo> records, XSSFSheet sheet, XSSFCellStyle cstyle, XSSFRow row,
    			XSSFCell cell, SimpleDateFormat formatter) {
    
    		if (records != null && records.size() > 0) {
    			int rowInt = 0; //从sheet表的第一行开始,第一行为0;
    			for (int i = 0; i < records.size(); i++) {
    				rowInt = i + 1; //代表第二行
    				row = sheet.createRow(rowInt);
    				// update_time
    				int j = 0; //单元cell从0开始
    				cell = row.createCell(j++);
    				cell.setCellValue(null==records.get(i).getUpdateDate()?"":records.get(i).getUpdateDate());
    				cell.setCellStyle(cstyle);
    				//sap_code
    				cell = row.createCell(j++);
    				cell.setCellValue(null==records.get(i).getSapCode()?"":records.get(i).getSapCode());
    				cell.setCellStyle(cstyle);
    				//MH...需要填充的字段
    			
    			}
    		}
    	}
    
    补充：实际中可能出现导出excel名带空格，导出的为+号连接，解决这个用：
    SimpleDateFormat ft = new SimpleDateFormat("yyyyMMdd");
    		String dateFt = ft.format(Calendar.getInstance().getTime());
    		// 末尾
    		String tempFileName = "Bottles Collection - Active Users List v" + dateFt + ".xlsx";
    		response.setContentType("application/vnd.ms-excel");
    		// replace("\\+","%20") 解决空格变成"+"的bug
    		String filename = URLEncoder.encode(tempFileName, "utf-8").replace("+", "%20");
    		response.setHeader("Content-Disposition", "attachment; filename=" + filename);
    		OutputStream outputStream = response.getOutputStream();

亲测有效.