JAVA导出Excel文档

导包

maven地址

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<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);
        }
}

...