POI报表导出

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

一:pom.xml导入依赖

        <!-- excel poi -->
        <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();

 

亲测有效.