ExcelUtils

package com.burypoint.utils;

import com.burypoint.constant.ExcelConstants;
import com.burypoint.entity.ExcelDTO;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;

/**

• Created by EX-LIFEI004 on 2019/3/7
  */
  public class ExcelUtils {

  public static void exportExcel(HttpServletResponse response, String fileName, ExcelDTO excelData)
  throws Exception {
  // 告诉浏览器用什么软件可以打开此文件
  response.setHeader(“content-Type”, “application/vnd.ms-excel”);
  // 下载文件的默认名称
  String finalFileName = URLEncoder.encode(fileName + “.” + ExcelConstants.FILE_NAME_POSTFIX, ExcelConstants.ENCODE);
  response.setHeader(“Content-Disposition”, “attachment;filename=” + finalFileName);

   export(excelData, response.getOutputStream());
  

  }

  /**

  • 导出
  • @param excelData 数据
  • @param out 输出流
  • @throws Exception 异常
    */
    private static void export(ExcelDTO excelData, OutputStream out) throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    try {
    // sheet页的名称
    String sheetName = excelData.getSheetName();
    if (StringUtils.isEmpty(sheetName)) {
    sheetName = ExcelConstants.DEFAULT_SHEET_NAME;
    }
    XSSFSheet sheet = wb.createSheet(sheetName);
    // 设置表头信息
    setTitle(wb, sheet, excelData.getTitles());
    // 设置内容
    setRows(wb, sheet, excelData.getRows());
    autoSizeColumn(sheet, excelData.getTitles().length + 1);
    wb.write(out);
    } catch (Exception e) {
    LogUtils.error(“excel导出出错：” + e.getMessage());
    throw new Exception(e);
    } finally {
    if (out != null) {
    out.close();
    }
    }
    }

  /**

  • 设置内容

  • @param wb excel

  • @param sheet sheet页

  • @param rows 行
    */
    private static void setRows(XSSFWorkbook wb, XSSFSheet sheet, List<List> rows) throws Exception {
    XSSFFont dataFont = wb.createFont();
    dataFont.setFontName(ExcelConstants.DATA_FONT_NAME);
    dataFont.setFontHeightInPoints(ExcelConstants.FONT_HEIGHT_IN_POINTS);
    dataFont.setColor(ExcelConstants.FONT_COLOR);

    XSSFCellStyle dataStyle = wb.createCellStyle();
    dataStyle.setAlignment(ExcelConstants.ALIGNMENT);
    dataStyle.setVerticalAlignment(ExcelConstants.VERTICAL_ALIGNMENT);
    dataStyle.setFont(dataFont);

    setBorder(dataStyle, ExcelConstants.BORDER_STYLE, ExcelConstants.BORDER_COLOR);

    // 因为第0行为title，所以从第一行开始
    for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
    List cells = rows.get(rowIndex);
    // 根据数据创建sheet页中的行
    Row dataRow = sheet.createRow(rowIndex + 1);
    // 设置字号
    dataRow.setHeightInPoints(ExcelConstants.HEIGHT_IN_POINTS);
    // 设置每一个单元格的数据
    for (int columnIndex = 0; columnIndex < cells.size(); columnIndex ++) {
    Object cellData = cells.get(columnIndex);
    // 创建一个单元格
    Cell cell = dataRow.createCell(columnIndex);
    // 设置数据
    if (cellData != null) {
    cell.setCellValue(cellData.toString());
    } else {
    cell.setCellValue("");
    }
    // 设置单元格样式
    cell.setCellStyle(dataStyle);
    }
    }
    }

  /**

  • 表头设置

  • @param wb excel

  • @param sheet sheet页

  • @param titles 表头数据
    */
    private static void setTitle(XSSFWorkbook wb, XSSFSheet sheet, String[] titles) {
    // 字体样式
    XSSFFont titleFont = wb.createFont();
    // 设置字体
    titleFont.setFontName(ExcelConstants.TITLE_FONT_NAME);
    // 设置粗体
    titleFont.setBoldweight(ExcelConstants.TITLE_BOLD_WEIGHT);
    // 设置字号
    titleFont.setFontHeightInPoints(ExcelConstants.FONT_HEIGHT_IN_POINTS);
    // 设置颜色
    titleFont.setColor(ExcelConstants.FONT_COLOR);

    // title样式
    XSSFCellStyle titleStyle = wb.createCellStyle();
    // 水平居中
    titleStyle.setAlignment(ExcelConstants.ALIGNMENT);
    // 垂直居中
    titleStyle.setVerticalAlignment(ExcelConstants.VERTICAL_ALIGNMENT);
    // 设置图案颜色
    titleStyle.setFillForegroundColor(ExcelConstants.FILL_FOREGROUND_COLOR);
    // 设置图案样式
    titleStyle.setFillPattern(ExcelConstants.FILL_PATTERN);
    // 设置字体样式
    titleStyle.setFont(titleFont);
    // 设置边框
    setBorder(titleStyle, ExcelConstants.BORDER_STYLE, ExcelConstants.BORDER_COLOR);

    // 设置行
    Row titleRow = sheet.createRow(0);

    for (int i = 0; i < titles.length; i++) {
    Cell cell = titleRow.createCell(i);
    cell.setCellValue(titles[i]);
    cell.setCellStyle(titleStyle);
    }
    }

  /**

  • 设置边框样式

  • @param style 列样式

  • @param border 边框样式

  • @param color 颜色
    */
    private static void setBorder(XSSFCellStyle style, short border, short color) {
    // 设置边框样式
    style.setBorderTop(border);
    style.setBorderLeft(border);
    style.setBorderRight(border);
    style.setBorderBottom(border);

    // 设置边框颜色
    style.setTopBorderColor(color);
    style.setLeftBorderColor(color);
    style.setRightBorderColor(color);
    style.setBottomBorderColor(color);
    }

  /**

  • 自动调整列宽
  • @param sheet sheet页
  • @param columnNumber 列长
    */
    private static void autoSizeColumn(Sheet sheet, int columnNumber) {
    for (int i = 0; i < columnNumber; i++) {
    int orgWidth = sheet.getColumnWidth(i);
    sheet.autoSizeColumn(i, true);
    int newWith = sheet.getColumnWidth(i) + 100;
    if (newWith > orgWidth) {
    sheet.setColumnWidth(i, newWith);
    } else {
    sheet.setColumnWidth(i, orgWidth);
    }
    }
    }
    }