ExcelUtil导出工具类

ExcelUtil导出工具类

package com.hkcw.zy.intelligentsearch.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;

/**

• @ClassName: ExcelUtil

• @Author: wangYaNan

• @Date: 2022/3/2 14:20

• @Version: 1.0

• @Description:
  /
  @Slf4j
  public class ExcelUtil {
  /*

  • 统一编码 ，默认UTF-8
    **/
    public static final String ENCODING_UTF = “UTF-8”;
    private ExcelUtil() {
    }

  /**

  • 导出excel

  • @param response

  • @param sheetName sheet名称

  • @param excelName excel文件名

  • @param list 数据

  • @param count 列长度
    */
    public static void expExcel(HttpServletResponse response, String sheetName, String excelName, List<String[]> list, int count) {
    log.info(“需要导出Excel总条数:” + list.size());
    // 定义输出流
    OutputStream os = null;
    try (HSSFWorkbook wb = new HSSFWorkbook()) {
    HSSFSheet sheet = wb.createSheet(sheetName);
    // Sheet样式
    HSSFCellStyle sheetStyle1 = wb.createCellStyle();
    HSSFCellStyle sheetStyle2 = wb.createCellStyle();
    HSSFCellStyle sheetStyle3 = wb.createCellStyle();
    HSSFCellStyle sheetStyle4 = wb.createCellStyle();
    sheetStyle1.setBorderBottom(BorderStyle.THIN); // 下边框
    sheetStyle1.setBorderLeft(BorderStyle.THIN);// 左边框
    sheetStyle1.setBorderTop(BorderStyle.THIN);// 上边框
    sheetStyle1.setBorderRight(BorderStyle.THIN);// 右边框
    sheetStyle1.setAlignment(HorizontalAlignment.CENTER);
    sheetStyle1.setVerticalAlignment(VerticalAlignment.CENTER);

     sheetStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
     sheetStyle2.setBorderLeft(BorderStyle.THIN);// 左边框
     sheetStyle2.setBorderTop(BorderStyle.THIN);// 上边框
     sheetStyle2.setBorderRight(BorderStyle.THIN);// 右边框
     sheetStyle2.setAlignment(HorizontalAlignment.CENTER);
     sheetStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
    
     // 设置字体
     HSSFFont headfont = wb.createFont();
     headfont.setFontName("黑体");
     headfont.setFontHeightInPoints((short) 10);// 字体大小
     headfont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
    

// headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
sheetStyle3.setFont(headfont);
sheetStyle3.setBorderBottom(BorderStyle.THIN); // 下边框
sheetStyle3.setBorderLeft(BorderStyle.THIN);// 左边框
sheetStyle3.setBorderTop(BorderStyle.THIN);// 上边框
sheetStyle3.setBorderRight(BorderStyle.THIN);// 右边框
sheetStyle3.setAlignment(HorizontalAlignment.CENTER);
sheetStyle3.setVerticalAlignment(VerticalAlignment.CENTER);

        sheetStyle4.setBorderBottom(BorderStyle.THIN); // 下边框
        sheetStyle4.setBorderLeft(BorderStyle.THIN);// 左边框
        sheetStyle4.setBorderTop(BorderStyle.THIN);// 上边框
        sheetStyle4.setBorderRight(BorderStyle.THIN);// 右边框
        sheetStyle4.setAlignment(HorizontalAlignment.CENTER);
        sheetStyle4.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFFont font1 = wb.createFont();
        font1.setFontHeightInPoints((short) 10);
        sheetStyle1.setFont(font1);
        sheetStyle2.setFont(font1);
        // 设置列宽（已知为3列）
        for (int i = 0; i < count; i++) {
            sheet.setColumnWidth(i, 5500);
        }
        for (int i = 0; i < list.size(); i++) {
            HSSFRow row = sheet.createRow((short) i);
            row.setHeight((short) 450);
            String[] tmp = list.get(i);
            for (int j = 0; j < tmp.length; j++) {
                HSSFCell cell = row.createCell(j);
                if (tmp[j] == null) {
                    cell.setCellStyle(sheetStyle4);
                } else {
                    if (i == 0) {
                        sheetStyle3
                                .setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
                        sheetStyle3
                                .setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        sheetStyle3.setWrapText(true);
                        cell.setCellStyle(sheetStyle3);
                    }
                    if (i == list.size() - 1) {
                        cell.setCellStyle(sheetStyle2);
                    }
                    if (i >= 1 && i < list.size() - 1) {
                        cell.setCellStyle(sheetStyle2);
                    }
                    cell.setCellValue(tmp[j]);
                }
                if (list.size() == 1) {
                    sheetStyle3
                            .setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
                    sheetStyle3
                            .setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    sheetStyle3.setWrapText(true);
                    cell.setCellStyle(sheetStyle3);
                }
            }

        }
        // 将创建的Excel写到Response的响应输出流中输出
        excelName = URLEncoder.encode(excelName + ".xls", ENCODING_UTF);
        // 设置响应
        response.setCharacterEncoding(ENCODING_UTF);
        response.setContentType("application/ms-excel; charset="
                + ENCODING_UTF);
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "max-age=30");

        if (excelName.length() > 150) {
            // 根据request的LOCALE 得出可能的编码，中文操作系统通常是gb2312
            excelName = new String(excelName.concat(".xls").getBytes(
                    "gb2312"), StandardCharsets.ISO_8859_1);

        }
        response.setHeader("Content-Disposition",
                "attachment; filename=" + excelName);
        // 获取Response响应输出流
        os = response.getOutputStream();
        // 写出响应
        wb.write(os);
        // 清空输出流
        os.flush();
        // 关闭输出流
        os.close();
    } catch (Exception e) {
        log.error("excel导出失败", e);
    } finally {
        if (os != null) {
            try {
                os.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

}