List<BusinessTravelSalesDailyData> dataList = new ArrayList<>();
try (BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(file))) {
            EasyExcel.write(out, BusinessTravelSalesDailyData.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet(0, sheetName)
                    .doWrite(dataList);
        } catch (Exception e) {
            log.error("数据生成完毕 写入失败", e);
            throw e;
        }

package com.huoli.ctar.tmc.csa.csm.job.statsRpt.excel.handle;

import com.alibaba.excel.write.handler.WorkbookWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.huoli.ctar.tmc.vo.BusinessTravelSalesDailyEmailVo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * Author:   caijc
 * Date:     2022/2/9 14:29
 * Description: 自定义合计列
 */
public class CustomSumDataHandler implements WorkbookWriteHandler {
    Integer sum;

    public CustomSumDataHandler(Integer sum) {
        this.sum = sum;
    }

    @Override
    public void beforeWorkbookCreate() {

    }

    @Override
    public void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder) {

    }

    @Override
    public void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {
        Sheet sheet = writeWorkbookHolder.getWorkbook().getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        int sumRowIndex = lastRowNum+1;
        Row row = sheet.createRow(sumRowIndex);
        CellStyle cellStyle = writeWorkbookHolder.getWorkbook().createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        Cell cellA1 = row.createCell(0);
        cellA1.setCellValue("总计:" + sum);
        cellA1.setCellStyle(cellStyle);
        CellRangeAddress cra = new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, 1);
        // 合并单元格
        sheet.addMergedRegion(cra);
    }
}

   /**
    * The maximum number of cell styles in a .xls workbook.
    * The 'official' limit is 4,000, but POI allows a slightly larger number.
    * This extra delta takes into account built-in styles that are automatically
    *
    * See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
    */
   private static final int MAX_STYLES = 4030;

/**
    * Create a new Cell style and add it to the workbook's style table.
    * You can define up to 4000 unique styles in a .xls workbook.
    *
    * @return the new Cell Style object
    * @throws IllegalStateException if the number of cell styles exceeded the limit for this type of Workbook.
    */
   @Override
   public HSSFCellStyle createCellStyle()
   {
       if(workbook.getNumExFormats() == MAX_STYLES) {
           throw new IllegalStateException("The maximum number of cell styles was exceeded. " +
                   "You can define up to 4000 styles in a .xls workbook");
       }
       ExtendedFormatRecord xfr = workbook.createCellXF();
       short index = (short) (getNumCellStyles() - 1);
       return new HSSFCellStyle(index, xfr, this);
   }

    /**
     * Excel2007
     *
     * <ul>
     * <li>The total number of available rows is 1M (2^20)</li>
     * <li>The total number of available columns is 16K (2^14)</li>
     * <li>The maximum number of arguments to a function is 255</li>
     * <li>Number of conditional format conditions on a cell is unlimited
     * (actually limited by available memory in Excel)</li>
     * <li>Number of cell styles is 64000</li>
     * <li>Length of text cell contents is 32767</li>
     * <ul>
     */
    EXCEL2007(0x100000, 0x4000, 255, Integer.MAX_VALUE, 64000, 32767);

// Is this right? Number formats (XSSFDataFormat) and cell styles (XSSFCellStyle) are different. What's up with the plus 1?
    private static final int MAXIMUM_STYLE_ID = SpreadsheetVersion.EXCEL2007.getMaxCellStyles();

public XSSFCellStyle createCellStyle() {
        if (this.getNumCellStyles() > MAXIMUM_STYLE_ID) {
            throw new IllegalStateException("The maximum number of Cell Styles was exceeded. You can define up to " + MAXIMUM_STYLE_ID + " style in a .xlsx Workbook");
        } else {
            int xfSize = this.styleXfs.size();
            CTXf xf = org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf.Factory.newInstance();
            xf.setNumFmtId(0L);
            xf.setFontId(0L);
            xf.setFillId(0L);
            xf.setBorderId(0L);
            xf.setXfId(0L);
            int indexXf = this.putCellXf(xf);
            return new XSSFCellStyle(indexXf - 1, xfSize - 1, this, this.theme);
        }
    }

package com.huoli.ctar.cms.poi;

import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Author:   caijc
 * Date:     2022/1/5 10:28
 * Description:
 */
@Data
public class POICopyHandler {

    // 缓存单元格的样式索引，key是拷贝原表格的样式索引id,value是拷贝目标表格的样式id
    Map<Short,Short> styleMap = new HashMap<>();
    private Workbook fromWorkbook;
    private Workbook toWorkbook;
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    // 构造方法，将原表格的所有样式一并拷贝到目标表格，并将表格样式索引缓存
    public POICopyHandler(Workbook fromWorkbook, Workbook toWorkbook) {
        this.fromWorkbook = fromWorkbook;
        this.toWorkbook = toWorkbook;
        Sheet fromSheet = fromWorkbook.getSheetAt(0);
        Sheet toSheet = fromWorkbook.getSheetAt(0);
        if (toSheet == null){
            toWorkbook.createSheet();
        }
        for (int i = fromSheet.getFirstRowNum(); i <= fromSheet.getLastRowNum(); i++) {
            Row fromRow = fromSheet.getRow(i);
            for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) {
                Cell fromCell = fromRow.getCell(j);
                CellStyle fromStyle = fromCell.getCellStyle();
                if(!styleMap.containsKey(fromStyle.getIndex())){
                    CellStyle toStyle = toWorkbook.createCellStyle();
                    toStyle.cloneStyleFrom(fromStyle);
                    styleMap.put(fromStyle.getIndex(), toStyle.getIndex());
                }
            }
        }

    }

    /**
     * 复制行
     * 此方法主要用于复制2个不同Workbook间的行
     */
    public void copySingleRows(int fromSheetIndex, int toSheetIndex, int fromRowIndex, int toRowIndex) {
        Sheet fromSheet = fromWorkbook.getSheetAt(fromSheetIndex);
        Sheet toSheet = toWorkbook.getSheetAt(toSheetIndex);

        if ((fromRowIndex == -1) || (fromRowIndex == -1)) {
            return;
        }

        // 设置列宽
        Row fromRow = fromSheet.getRow(fromRowIndex);
        if (fromRow == null) {
            return;
        }
        for (int i = fromRow.getLastCellNum(); i >= fromRow.getFirstCellNum(); i--) {
            toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
            toSheet.setColumnHidden(i, false);
        }
        // 拷贝行并填充数据
        Row toRow = toSheet.createRow(toRowIndex);
        toRow.setHeight(fromRow.getHeight());
        for (int j = fromRow.getFirstCellNum(); j <= fromRow.getPhysicalNumberOfCells(); j++) {
            Cell fromCell = fromRow.getCell(j);
            Cell toCell = toRow.createCell(j);
            if (fromCell != null) {
                CellStyle fromStyle = fromCell.getCellStyle();
                CellStyle toStyle = toWorkbook.getCellStyleAt(styleMap.get(fromStyle.getIndex()));
                if (toStyle != null){
                    toCell.setCellStyle(toStyle);
                }
                switch (fromCell.getCellTypeEnum()) {
                    case BOOLEAN:
                        toCell.setCellValue(fromCell.getBooleanCellValue());
                        break;
                    case ERROR:
                        toCell.setCellErrorValue(fromCell.getErrorCellValue());
                        break;
                    case FORMULA:
                        toCell.setCellFormula(parseFormula(fromCell.getCellFormula()));
                        break;
                    case NUMERIC:
                        if(DateUtil.isCellDateFormatted(fromCell)){
                            toCell.setCellValue(sdf.format(fromCell.getDateCellValue()));
                        } else{
                            toCell.setCellValue(fromCell.getNumericCellValue());
                        }
                        break;
                    case STRING:
                        toCell.setCellValue(fromCell.getRichStringCellValue());
                        break;
                }
            }

        }
    }

    private String parseFormula(String pPOIFormula) {
        final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
        StringBuffer result;
        int index;

        result = new StringBuffer();
        index = pPOIFormula.indexOf(cstReplaceString);
        if (index >= 0) {
            result.append(pPOIFormula, 0, index);
            result.append(pPOIFormula.substring(index + cstReplaceString.length()));
        } else {
            result.append(pPOIFormula);
        }

        return result.toString();
    }
}

POICopyHandler poiCopyHandler = new POICopyHandler(fromWorkbook, toWorkbook);
poiCopyHandler.copySingleRows(0,0,0,0);// 四个参数分别是：源Workbook的sheet索引，目标Workbook的sheet索引，源Workbook的行索引，目标Workbook的行索引

@Test
public void test1(){
    LocalDateTime ldt = LocalDateTime.now();
    System.out.println(ldt);// 2020-12-10T11:48:43.387

    LocalDateTime ld2 = LocalDateTime.of(2020, 12, 10, 11, 11, 10);
    System.out.println(ld2);// 2020-12-10T11:11:10

    LocalDateTime ldt3 = ld2.plusYears(20);
    System.out.println(ldt3);// 2040-12-10T11:11:10

    LocalDateTime ldt4 = ld2.minusMonths(2);
    System.out.println(ldt4);// 2020-10-10T11:11:10

    System.out.println(ldt.getYear());// 2020
    System.out.println(ldt.getMonthValue());// 12
    System.out.println(ldt.getDayOfMonth());// 10
    System.out.println(ldt.getHour());// 11
    System.out.println(ldt.getMinute());// 48
    System.out.println(ldt.getSecond());// 43
}

//2. Instant : 时间戳。 （使用 Unix 元年  1970年1月1日 00:00:00 所经历的毫秒值）
@Test
public void test2(){
    Instant ins = Instant.now();  //默认使用 UTC 时区
    System.out.println(ins);// 2020-12-10T03:50:25.814Z

    OffsetDateTime odt = ins.atOffset(ZoneOffset.ofHours(8));
    System.out.println(odt);// 2020-12-10T11:50:25.814+08:00

    System.out.println(ins.getNano());// 814000000

    Instant ins2 = Instant.ofEpochSecond(5);
    System.out.println(ins2);// 1970-01-01T00:00:05Z
}

//Duration : 用于计算两个“时间”间隔
//Period : 用于计算两个“日期”间隔
@Test
public void test3(){
    Instant ins1 = Instant.now();

    System.out.println("--------------------");
    try {
        Thread.sleep(1000);
    } catch (InterruptedException e) {
    }

    Instant ins2 = Instant.now();
    // 获取两个时间间隔
    Duration duration = Duration.between(ins1, ins2);
    System.out.println("所耗费时间为(单位毫秒)：" + duration.toMillis());// 所耗费时间为(单位秒)：1001

    System.out.println("----------------------------------");

    LocalDate ld1 = LocalDate.now();
    System.out.println("现在日期为："+ ld1);// 现在日期为：2020-12-10
    LocalDate ld2 = LocalDate.of(2011, 1, 1);
    // 获取两个日期间隔
    Period pe = Period.between(ld2, ld1);
    System.out.println("年间隔" + pe.getYears());// 9
    System.out.println("月间隔" + pe.getMonths());// 11
    System.out.println("天间隔" + pe.getDays());// 9
}

@FunctionalInterface
public interface TemporalAdjuster {

    Temporal adjustInto(Temporal temporal);

}

@Test
public void test4(){
    // 当前日期
	LocalDateTime ldt = LocalDateTime.now();
	System.out.println(ldt);
    // 本月第12天
	LocalDateTime ldt2 = ldt.withDayOfMonth(12);
	System.out.println(ldt2);
	// 下一个周日
	LocalDateTime ldt3 = ldt.with(TemporalAdjusters.next(DayOfWeek.SUNDAY));
	System.out.println(ldt3);
	
	//自定义：下一个工作日
	LocalDateTime ldt5 = ldt.with((l) -> {
		LocalDateTime ldt4 = (LocalDateTime) l;
		
		DayOfWeek dow = ldt4.getDayOfWeek();
		
		if(dow.equals(DayOfWeek.FRIDAY)){
			return ldt4.plusDays(3);
		}else if(dow.equals(DayOfWeek.SATURDAY)){
			return ldt4.plusDays(2);
		}else{
			return ldt4.plusDays(1);
		}
	});
	
	System.out.println(ldt5);
	
}

@Test
	public void test5(){
//		DateTimeFormatter dtf = DateTimeFormatter.ISO_LOCAL_DATE;	
	DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy年MM月dd日 HH:mm:ss E");
	
	LocalDateTime ldt = LocalDateTime.now();
	String strDate = ldt.format(dtf);
	
	System.out.println(strDate);
	
	LocalDateTime newLdt = ldt.parse(strDate, dtf);
	System.out.println(newLdt);
}

 
import java.time.*;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
 
/**
 * @author xiaobu
 * @version JDK1.8.0_171
 * @date on  2019/1/11 11:52
 * @description V1.0 LocalDateTime工具类
 * SQL -> Java
 * date -> LocalDate
 * time -> LocalTime
 * timestamp -> LocalDateTime
 */
public class DateTimeUtils {
 
    public static final DateTimeFormatter TIME_FORMATTER = DateTimeFormatter.ofPattern("HHmmss");
    public static final DateTimeFormatter YEAR_MONTH_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM");
    public static final DateTimeFormatter SHORT_DATE_FORMATTER = DateTimeFormatter.ofPattern("yyMMdd");
    public static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    public static final DateTimeFormatter SHORT_DATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyMMddHHmmss");
    public static final DateTimeFormatter DATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    public static final DateTimeFormatter LONG_DATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss SSS");
 
    /**
     * 返回当前的日期
     */
    public static LocalDate getCurrentLocalDate() {
        return LocalDate.now();
    }
 
    /**
     * 返回当前时间
     */
    public static LocalTime getCurrentLocalTime() {
        return LocalTime.now();
    }
 
    /**
     * 返回当前日期时间
     */
    public static LocalDateTime getCurrentLocalDateTime() {
        return LocalDateTime.now();
    }
 
    /**
     * yyyy-MM-dd
     */
    public static String getCurrentDateStr() {
        return LocalDate.now().format(DATE_FORMATTER);
    }
 
    /**
     * yyMMdd
     */
    public static String getCurrentShortDateStr() {
        return LocalDate.now().format(SHORT_DATE_FORMATTER);
    }
 
    public static String getCurrentMonthStr() {
        return LocalDate.now().format(YEAR_MONTH_FORMATTER);
    }
 
    /**
     * yyyy-MM-dd HH:mm:ss
     */
    public static String getCurrentDateTimeStr() {
        return LocalDateTime.now().format(DATETIME_FORMATTER);
    }
 
 
    public static String getCurrentLongDateTimeStr(){
        return LocalDateTime.now().format(LONG_DATETIME_FORMATTER);
    }
 
    /**
     * yyMMddHHmmss
     */
    public static String getCurrentShortDateTimeStr() {
        return LocalDateTime.now().format(SHORT_DATETIME_FORMATTER);
    }
 
    /**
     * HHmmss
     */
    public static String getCurrentTimeStr() {
        return LocalTime.now().format(TIME_FORMATTER);
    }
 
    public static String getCurrentDateStr(String pattern) {
        return LocalDate.now().format(DateTimeFormatter.ofPattern(pattern));
    }
 
    public static String getCurrentDateTimeStr(String pattern) {
        return LocalDateTime.now().format(DateTimeFormatter.ofPattern(pattern));
    }
 
    public static String getCurrentTimeStr(String pattern) {
        return LocalTime.now().format(DateTimeFormatter.ofPattern(pattern));
    }
 
    public static LocalDate parseLocalDate(String dateStr, String pattern) {
        return LocalDate.parse(dateStr, DateTimeFormatter.ofPattern(pattern));
    }
 
    public static LocalDateTime parseLocalDateTime(String dateTimeStr, String pattern) {
        return LocalDateTime.parse(dateTimeStr, DateTimeFormatter.ofPattern(pattern));
    }
 
    public static LocalTime parseLocalTime(String timeStr, String pattern) {
        return LocalTime.parse(timeStr, DateTimeFormatter.ofPattern(pattern));
    }
 
    public static String formatLocalDate(LocalDate date, String pattern) {
        return date.format(DateTimeFormatter.ofPattern(pattern));
    }
 
    public static String formatLocalDateTime(LocalDateTime datetime, String pattern) {
        return datetime.format(DateTimeFormatter.ofPattern(pattern));
    }
 
    public static String formatLocalTime(LocalTime time, String pattern) {
        return time.format(DateTimeFormatter.ofPattern(pattern));
    }
 
    public static LocalDate parseLocalDate(String dateStr) {
        return LocalDate.parse(dateStr, DATE_FORMATTER);
    }
 
    public static LocalDateTime parseLocalDateTime(String dateTimeStr) {
        return LocalDateTime.parse(dateTimeStr, DATETIME_FORMATTER);
    }
 
    public static LocalDateTime parseLongLocalDateTime(String longDateTimeStr){
        return LocalDateTime.parse(longDateTimeStr, LONG_DATETIME_FORMATTER);
    }
 
    public static LocalTime parseLocalTime(String timeStr) {
        return LocalTime.parse(timeStr, TIME_FORMATTER);
    }
 
    public static String formatLocalDate(LocalDate date) {
        return date.format(DATE_FORMATTER);
    }
 
    public static String formatLocalDateTime(LocalDateTime datetime) {
        return datetime.format(DATETIME_FORMATTER);
    }
 
    public static String formatLocalTime(LocalTime time) {
        return time.format(TIME_FORMATTER);
    }
 
    /**
     * 日期相隔秒
     */
    public static long periodHours(LocalDateTime startDateTime,LocalDateTime endDateTime){
       return Duration.between(startDateTime, endDateTime).get(ChronoUnit.SECONDS);
    }
 
    /**
     * 日期相隔天数
     */
    public static long periodDays(LocalDate startDate, LocalDate endDate) {
        return startDate.until(endDate, ChronoUnit.DAYS);
    }
 
    /**
     * 日期相隔周数
     */
    public static long periodWeeks(LocalDate startDate, LocalDate endDate) {
        return startDate.until(endDate, ChronoUnit.WEEKS);
    }
 
    /**
     * 日期相隔月数
     */
    public static long periodMonths(LocalDate startDate, LocalDate endDate) {
        return startDate.until(endDate, ChronoUnit.MONTHS);
    }
 
    /**
     * 日期相隔年数
     */
    public static long periodYears(LocalDate startDate, LocalDate endDate) {
        return startDate.until(endDate, ChronoUnit.YEARS);
    }
 
    /**
     * 是否当天
     */
    public static boolean isToday(LocalDate date) {
        return getCurrentLocalDate().equals(date);
    }
    /**
     * 获取当前毫秒数
     */
    public static Long toEpochMilli(LocalDateTime dateTime) {
        return dateTime.atZone(ZoneId.systemDefault()).toInstant().toEpochMilli();
    }
 
    /**
     * 判断是否为闰年
     */
    public static boolean isLeapYear(LocalDate localDate){
        return localDate.isLeapYear();
    }
}

Set<String> set = ZoneId.getAvailableZoneIds();

// ZonedDate、ZonedTime、ZonedDateTime ： 带时区的时间或日期
@Test
public void test7(){
    LocalDateTime ldt = LocalDateTime.now(ZoneId.of("Asia/Shanghai"));
    System.out.println(ldt);

    ZonedDateTime zdt = ZonedDateTime.now(ZoneId.of("US/Pacific"));
    System.out.println(zdt);
}

@Test
public void test1(){
	Optional<Employee> op = Optional.of(new Employee());
	Employee emp = op.get();
	System.out.println(emp);
}

@Test
public void test2(){
    Optional<Employee> op = Optional.ofNullable(null);
    if(op.isPresent()){
        System.out.println(op.get());
    }
    
    System.out.println(op.get());

    Optional<Employee> op = Optional.empty();
    System.out.println(op.get());
}

@Test
public void test3(){
    Optional<Employee> op = Optional.ofNullable(new Employee());

    if(op.isPresent()){
        System.out.println(op.get());
    }

    Employee emp = op.orElse(new Employee("张三"));
    System.out.println(emp);

    Employee emp2 = op.orElseGet(() -> new Employee());
    System.out.println(emp2);
}

@Test
public void test4(){
    Optional<Employee> op = Optional.of(new Employee(101, "张三", 18, 9999.99));

    Optional<String> op2 = op.map(Employee::getName);
    System.out.println(op2.get());

    Optional<String> op3 = op.flatMap((e) -> Optional.of(e.getName()));
    System.out.println(op3.get());
}