EasyExcel 做模板的导出-----锁表

最新推荐文章于 2024-06-18 11:41:52 发布

石山搭建者

最新推荐文章于 2024-06-18 11:41:52 发布

阅读量873

点赞数

文章标签： java 开发语言后端

本文链接：https://blog.csdn.net/qq_36443319/article/details/121802330

版权

EasyExcel 做模板的导出

摘要：防止以后用到。做excle导出的。需求是锁表。这个是用到了easyExcel 的类似一个监听器，实现CellWriteHandler接口。会监听数据excle导出，可以在时对每个单元格做操作。话不多说，贴代码

1.exclel的模板类

ColumnWidth：单元格宽、ExcelProperty 文件名称、index 对应的单元格信息

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import java.io.Serializable;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * 
 *
 *
 * 模板
 */

@Data
@EqualsAndHashCode()
public class FlowCardTemplateVo implements Serializable {

  /**
   * ICCID号 (不支持编辑)
   */
  @ColumnWidth(32)
  @ExcelProperty(value = "ICCID号",index = 0)
  private String iccid;

  /**
   * MSISDN (不支持编辑)
   */
  @ColumnWidth(32)
  @ExcelProperty(value = "MSISDN",index = 1)
  private String msisdn;

  /**
   * 卡下单单号
   */
  @ColumnWidth(32)
  @ExcelProperty(value = "卡下单单号",index = 2)
  private String buyNo;

  /**
   * 卡下单时间(支持用户编辑)
   */
  @ColumnWidth(32)
  @ExcelProperty(value = "卡下单时间(yyyy-mm-dd)",index = 3)
  private String buyTime;

  /**
   * 卡下单项目(支持用户编辑)(项目名称+项目code)
   */
  @ColumnWidth(32)
  @ExcelProperty(value = "卡下单项目(小区名称+项目编号)",index = 4)
  private String buyProject;

  /**
   * 卡续费是否收费(是/否支持用户编辑)(支持用户编辑)
   */
  @ColumnWidth(32)
  @ExcelProperty(value = "*卡续费是否收费(必填项,是/否)",index = 5)
  private String renewCharge;

  /**
   * 卡续费单号
   */
  @ColumnWidth(32)
  @ExcelProperty(value = "卡续费单号",index = 6)
  private String renewNo;

  /**
   * 卡续费时间
   */
  @ColumnWidth(32)
  @ExcelProperty(value = "卡续费时间(yyyy-mm-dd)",index = 7)
  private String renewTime;
}

2.导出的方法的

先去获取你要的数据，list对象。在放到对应方法上

EasyExcelUtils
        .writeExcel(response, list, "文件名称", "sheet名称", FlowCardTemplateVo.class);

具体导出的方法。看自己需求去在 EasyExcelFactory.write() 方法后面点方法去加




import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import lombok.SneakyThrows;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;


/**
 *导出的方法
 */

public class EasyExcelUtils {
  /**
   * 导出excel
   *
   * @param response
   * @param data      要导出的数据list
   * @param fileName  文件名字
   * @param sheetName 模板名字
   * @param model     导出类
   */
  @SneakyThrows
  public static void writeExcel(HttpServletResponse response, List<? extends Object> data,
                                String fileName, String sheetName, Class model){
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		
	ExcelStyleConfig excelStyleConfig = new ExcelStyleConfig(Lists.newArrayList(0));

    EasyExcelFactory.write(
        getOutputStream(fileName + sdf.format(new Date()) + (new Date()).getTime(), response),
        model)

        //锁表头
        .registerWriteHandler(new SheetWriteHandler() {
          @Override
          public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder,
                                        WriteSheetHolder writeSheetHolder) {
            // TODO document why this method is empty
          }
          @Override
          public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder,
                                       WriteSheetHolder writeSheetHolder) {
            // 冻结首行（锁住第一行头部）
            writeSheetHolder.getSheet().createFreezePane(0, 1, 0, 1);

          }
        })
        .excelType(ExcelTypeEnum.XLSX)
        .sheet(sheetName)
        //引用监听器的方法
         .registerWriteHandler(excelStyleConfig)
        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        .doWrite(data);
  }

  /**
   * 设置服务器信息
   *
   * @param fileName 文件名称
   * @param response
   * @return
   * @throws Exception
   */
  private static OutputStream getOutputStream(String fileName,
                                              HttpServletResponse response) throws Exception {
    try {
      fileName = URLEncoder.encode(fileName, "UTF-8");
      response.setContentType("application/vnd.ms-excel");
      response.setCharacterEncoding("utf8");
      response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
      response.setHeader("Pragma", "public");
      response.setHeader("Cache-Control", "no-store");
      response.addHeader("Cache-Control", "max-age=0");
      return response.getOutputStream();
    } catch (IOException e) {
      throw new Exception("导出excel表格失败!", e);
    }
  }

}

EasyExcel 的监听器

这个也是要自己加参数，不懂去官网看例子，或者自己去百度

package com.vanrui.iccid.manage.utils;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import groovy.util.logging.Slf4j;
import java.util.List;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Component;

/**
 * 
 */
@Component
public class ExcelStyleConfig implements CellWriteHandler {

  private static Logger log = LogManager.getLogger(ExcelStyleConfig.class);



  /**
   * 需要锁定的列集合
   */
  private List<Integer> columnList;

  public ExcelStyleConfig(List<Integer> columnList) {
    this.columnList = columnList;
  }

  /**
   * 样式类
   */
  private CellStyle cellStyle;

  @Override
  public void beforeCellCreate(WriteSheetHolder writeSheetHolder,
      WriteTableHolder writeTableHolder,
      Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
  }

  @Override
  public void afterCellCreate(WriteSheetHolder writeSheetHolder,
      WriteTableHolder writeTableHolder,
      Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

  }

  @Override
  public void afterCellDispose(WriteSheetHolder writeSheetHolder,
      WriteTableHolder writeTableHolder,
      List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex,
      Boolean isHead) {

//    // 下边框
//    cellStyle.setBorderBottom(BorderStyle.THIN);
//    // 左边框
//    cellStyle.setBorderLeft(BorderStyle.THIN);
//    // 上边框
//    cellStyle.setBorderTop(BorderStyle.THIN);
//    // 右边框
//    cellStyle.setBorderRight(BorderStyle.THIN);
//    // 水平对齐方式
//    cellStyle.setAlignment(HorizontalAlignment.CENTER);
//    // 垂直对齐方式
//    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);


   //默认不加锁
    cellStyle.setLocked(false);

    if (!isHead && !CollectionUtils.isEmpty(columnList) && columnList.contains(cell.getColumnIndex())) {
      //设置背景颜色
//      cellStyle.setFillBackgroundColor(IndexedColors.ROYAL_BLUE.getIndex());
//      cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	//加锁（可以判断是哪个单元格再去单个加锁）
      writeSheetHolder.getSheet().protectSheet("123456");
      cellStyle.setLocked(true);
    }
    // 填充单元格样式
    cell.setCellStyle(cellStyle);
  }
}