JAVA导出Excel文档

冷不防 2022-11-17 05:11 187阅读 0赞

### JAVA导出excel文档 ###

*  导包
 *  创建
 *   *  导出数据
     *  设置response

# 导包 #

[maven地址][maven]

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.0.0</version>
    </dependency>

# 创建 #

## 导出数据 ##

private void applyExcl(HSSFWorkbook workbook, String fileName, ...){ 
    	...
    	//创建文档
    	//HSSFWorkbook workbook = new HSSFWorkbook();
    	//设置头部样式
    	CellStyle headerCellStyle = workbook.createCellStyle();
    	//左对齐
    	headerCellStyle.setAlignment(HorizontalAlignment.LEFT);
    	//垂直居中
    	headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    	//背景/颜色/背景色/样式
    	headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    	headerCellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    	headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    	//字体
    	HSSFFont font = workbook.createFont();
    	font.setFontName("黑体");
    	font.setBold(true);
    	font.setFontHeightInPoints((short) 14);
    	//创建行
    	HSSFSheet sheet = workbook.createSheet(fileName);
    	HSSFRow headerRow = sheet.createRow(0);
    	//行高
    	headerRow.setHeight(500);
    	//表头数据
    	final String[] headers = { "通过好友验证时间", "昵称", "ID", "粉昵称", "粉ID", "粉资源号"};
    	for (int i = 0; i < headers.length; i++) { 
    	  //创建单元格
    	  HSSFCell cell = headerRow.createCell(i);
    	  cell.setCellValue(headers[i]);
    	  cell.setCellStyle(headerCellStyle);
    	}
    	//设置内容
    	List<Data> dataList = ...;
    	for (int i = 0; i < dataList.size(); i++) { 
    		  Data data = dataList.get(i);
    	      HSSFRow bodyRow = sheet.createRow(i + 1);
    	      bodyRow.setHeight(400);
    	      for (int j = 0; j < headers.size; j++) { 
    	              HSSFCell cell = bodyRow.createCell(j);
    	              cell.setCellStyle(bodyCallStyle);
    	              switch (j) { 
    	                  case 0 :
    	                      cell.setCellValue(data.getAddTime());
    	                      break;
    	                  case 1:
    	                      cell.setCellValue(data.getNickName());
    	                      break;
    	                  case 2:
    	                      cell.setCellValue(data.getId());
    	                      break;
    	                  case 3:
    	                      ...
    	                  default:
    	                      break;
    	              }
    	          }
    	  }
    	//设置单元格自适应/中文自适应
    	for (int i = 0; i < headers.length; i++) { 
    	      sheet.autoSizeColumn(i);
    	      sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
    	}
    	...
    }

## 设置response ##

...
    
    public void download(HttpServletResponse response, WechatContactApplyVO vo){ 
    		response.setCharacterEncoding("UTF-8");
    		response.setLocale(new java.util.Locale("zh", "CN"));
    		...
    		try { 
    			HSSFWorkbook workbook = new HSSFWorkbook();
    			applyExcl( workbook, fileName...);
    			response.setContentType("application/x-xls");
    // response.setContentType("application/ms-excel;charset=UTF-8");
    // response.setContentType("application/octet-stream");
    // response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.UTF_8) + ".xls");
    // response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
    			//设置导出文件名与页面打开方式
    			response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
    			//刷新缓存
    			response.flushBuffer();
    			//输出流
    			workbook.write(response.getOutputStream());
    		} catch (Exception e) { 
                log.error("【导出申请好友统计信息】 导出已通过记录异常 uins:{} msg:{}", vo.getUins(), e.getMessage());
                e.printStackTrace();
                throw new BusinessEception(ResultCode.RESULT_HANDLER_FAILED, e.message);
            }
    }
    
    ...

[maven]: https://mvnrepository.com/artifact/org.apache.poi/poi