SpringBoot实现excel文件生成和下载

使用SpringBoot实现excel生成和下载，生成模板如下

controller

@RequestMapping(value = { "/downloadExcelTemplate" }, method = RequestMethod.GET)
 public String downloadExcelTemplate(HttpSession httpSession, HttpServletResponse response) {
 try {
  dealExcelService.downloadExcelTemplate(response);
  return "success";
 } catch (Exception e) {
  logger.error("downloadExcelTemplate_error", e);
  return "failure";
 }
}

service

public void downloadExcelTemplate(HttpServletResponse response) throws Exception {
 //文件名
 SimpleDateFormat format3 = new SimpleDateFormat("yyyyMMddHHmm");
 String fileName = new String(("文件名" + format3.format(new Date()) + "导入模板").getBytes(), "ISO8859_1");
 //配置请求头
 ServletOutputStream outputStream = response.getOutputStream();
 // 组装附件名称和格式
 response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
 // 创建一个workbook 对应一个excel应用文件
 XSSFWorkbook workBook = new XSSFWorkbook();
 // 在workbook中添加一个sheet,对应Excel文件中的sheet
 XSSFSheet sheet = workBook.createSheet("模板");
 ExportUtil exportUtil = new ExportUtil(workBook, sheet);
 XSSFCellStyle headStyle = exportUtil.getHeadStyle();
 XSSFCellStyle bodyStyle = exportUtil.getBodyStyle2();
 // 构建表头
 XSSFRow headRow = ExportUtil.createRow(sheet, 0);
 XSSFCell cell;
 
 String[] titles = {"表头一", "表头二", "表头三"};
 int index = 0;
 for (String title : titles) {
  cell = ExportUtil.createCell(headRow, index);
  cell.setCellStyle(headStyle);
  cell.setCellValue(title);
  index++;
 }
 
 try {
  workBook.write(outputStream);
  outputStream.flush();
  outputStream.close();
 } catch (IOException e) {
  e.printStackTrace();
 } finally {
  try {
  outputStream.close();
  } catch (IOException e) {
  e.printStackTrace();
  }
 }
}

ExportUtil导出工具类

package com.shengsheng.utils;
 
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
 
/**
 * excel 表格导出工具类
 *
 * @author shengshenglalala
 */
public class ExportUtil {
 private XSSFWorkbook wb;
 
 private XSSFSheet sheet;
 
 /**
 * @param wb
 * @param sheet
 */
 public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet) {
 this.wb = wb;
 this.sheet = sheet;
 }
 
 /**
 * 合并单元格后给合并后的单元格加边框
 *
 * @param region
 * @param cs
 */
 public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {
 
 int toprowNum = region.getFirstRow();
 for (int i = toprowNum; i <= region.getLastRow(); i++) {
  XSSFRow row = sheet.getRow(i);
  for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
  XSSFCell cell = row.getCell(j);
  cell.setCellStyle(cs);
  }
 }
 }
 
 /**
 * 设置表头的单元格样式
 *
 * @return
 */
 public XSSFCellStyle getHeadStyle() {
 // 创建单元格样式
 XSSFCellStyle cellStyle = wb.createCellStyle();
 // // 设置单元格的背景颜色为淡蓝色
 cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
 cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
 // 设置单元格居中对齐
 cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
 // 设置单元格垂直居中对齐
 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
 // 创建单元格内容显示不下时自动换行
 // cellStyle.setWrapText(true);
 // 设置单元格字体样式
 XSSFFont font = wb.createFont();
 // 设置字体加粗
 font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
 font.setFontName("宋体");
 // font.setFontHeight((short) 200);
 cellStyle.setFont(font);
 // 设置单元格边框为细线条
// cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
// cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
// cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
// cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
 return cellStyle;
 }
 
 /**
 * 设置表体的单元格样式
 *
 * @return
 */
 public XSSFCellStyle getBodyStyle2() {
 // 创建单元格样式
 // 创建单元格样式
 XSSFCellStyle cellStyle = wb.createCellStyle();
 // 创建单元格内容显示不下时自动换行
 // cellStyle.setWrapText(true);
 // 设置单元格字体样式
 XSSFFont font = wb.createFont();
 // 设置字体加粗
 // font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
 font.setFontName("宋体");
 font.setFontHeight((short) 200);
 font.setColor(HSSFColor.BLACK.index);
 cellStyle.setFont(font);
 // 设置单元格边框为细线条
 return cellStyle;
 }
 
 /**
 * 没有行，就创建行
 *
 * @param sheet
 * @param index
 * @return
 */
 public static XSSFRow createRow(XSSFSheet sheet, Integer index) {
 XSSFRow row = sheet.getRow(index);
 if (row == null) {
  return sheet.createRow(index);
 }
 return row;
 }
 
 /**
 * 如果没有列，就创建列
 *
 * @param row
 * @param index
 * @return
 */
 public static XSSFCell createCell(XSSFRow row, Integer index) {
 XSSFCell cell = row.getCell(index);
 if (cell == null) {
  return row.createCell(index);
 }
 return cell;
 }
}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持猪先飞。