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;
 }
}

以上就是本文的全部內容,希望對大傢的學習有所幫助,也希望大傢多多支持WalkonNet。