销售订单导出

zouyu

2023-09-24 05981a8e1db6adcb3dc79907fc22453fb2e51230

销售订单导出

 framework/pom.xml

@@ -38,5 +38,19 @@
            <groupId>com.auth0</groupId>
            <artifactId>java-jwt</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
        </dependency>
        <!--poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </dependency>
        <dependency>
            <groupId>com.github.whvcse</groupId>
            <artifactId>easy-captcha</artifactId>
            <version>1.6.2</version>
        </dependency>
    </dependencies>
</project>

 framework/src/main/java/com/yuanchu/mom/exception/ExcelException.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,25 @@
package com.yuanchu.mom.exception;

import com.yuanchu.mom.utils.easyexcel.ResponseResultEnum;
import lombok.Getter;

/**
 * excel全局异常
 * @auther Zou, Yu
 * @create 2023-9-24
 */
@Getter
public class ExcelException extends RuntimeException {

    private Integer status;

    public ExcelException(ResponseResultEnum resultEnum) {
        super(resultEnum.getMessage());
        this.status = resultEnum.getStatus();
    }

    public ExcelException(Integer status, String message) {
        super(message);
        this.status = status;
    }
}

 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/CustemHandler.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,81 @@
package com.yuanchu.mom.utils.easyexcel;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.util.CollectionUtils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
*@author: Zou, Yu
*@description:
*@date: 2023/9/24 0024 12:56
**/
public class CustemHandler extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;
    //因为在自动列宽的过程中，有些设置地方让列宽显得紧凑，所以做出了个判断
    private static final int COLUMN_WIDTH = 20;
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

    public CustemHandler() {
    }

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map) CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                } else {
                    if (columnWidth < COLUMN_WIDTH) {
                        columnWidth = columnWidth * 2;
                    }
                }

                Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData) cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/EasyExcelUtils.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,405 @@
package com.yuanchu.mom.utils.easyexcel;


import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.metadata.TableStyle;
import com.alibaba.excel.metadata.*;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.yuanchu.mom.exception.ExcelException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

/**
*@author: Zou, Yu
*@description:
*@date: 2023/9/24 0024 13:18
**/
public class EasyExcelUtils {


    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // å¤´çš„ç­–ç•¥
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // èƒŒæ™¯è®¾ç½®ä¸ºç°è‰²
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // å­—体样式
        headWriteFont.setFontName("Frozen");
        headWriteCellStyle.setWriteFont(headWriteFont);
        // è‡ªåŠ¨æ¢è¡Œ
        headWriteCellStyle.setWrapped(false);
        // æ°´å¹³å¯¹é½æ–¹å¼
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // åž‚直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // å†…容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // è¿™é‡Œéœ€è¦æŒ‡å®š FillPatternType ä¸ºFillPatternType.SOLID_FOREGROUND ä¸ç„¶æ— æ³•æ˜¾ç¤ºèƒŒæ™¯é¢œè‰².头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // èƒŒæ™¯ç™½è‰²
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // å­—体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        // å­—体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // å†…容左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // è¿™ä¸ªç­–略是 å¤´æ˜¯å¤´çš„样式 å†…容是内容的样式 å…¶ä»–的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    public static HorizontalCellStyleStrategy getStyleFixedStrategy() {
        // å¤´çš„ç­–ç•¥
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // èƒŒæ™¯è®¾ç½®ä¸ºç°è‰²
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // å­—体样式
        headWriteFont.setFontName("Calibri");
        headWriteCellStyle.setWriteFont(headWriteFont);
        // è‡ªåŠ¨æ¢è¡Œ
        headWriteCellStyle.setWrapped(false);
        // æ°´å¹³å¯¹é½æ–¹å¼
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // åž‚直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // å†…容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // è¿™é‡Œéœ€è¦æŒ‡å®š FillPatternType ä¸ºFillPatternType.SOLID_FOREGROUND ä¸ç„¶æ— æ³•æ˜¾ç¤ºèƒŒæ™¯é¢œè‰².头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // èƒŒæ™¯ç™½è‰²
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // å­—体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        // å­—体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // å†…容左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // è¿™ä¸ªç­–略是 å¤´æ˜¯å¤´çš„样式 å†…容是内容的样式 å…¶ä»–的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    public static HorizontalCellStyleStrategy getArveStyleStrategy() {
        // å¤´çš„ç­–ç•¥
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // èƒŒæ™¯è®¾ç½®ä¸º
        headWriteCellStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // å­—体样式
        headWriteFont.setFontName("Calibri");
        headWriteFont.setColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setWriteFont(headWriteFont);

        headWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
        headWriteCellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex());
        // è‡ªåŠ¨æ¢è¡Œ
        headWriteCellStyle.setWrapped(true);
        // æ°´å¹³å¯¹é½æ–¹å¼
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // åž‚直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // å†…容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // è¿™é‡Œéœ€è¦æŒ‡å®š FillPatternType ä¸ºFillPatternType.SOLID_FOREGROUND ä¸ç„¶æ— æ³•æ˜¾ç¤ºèƒŒæ™¯é¢œè‰².头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // èƒŒæ™¯ç™½è‰²
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // å­—体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        // å­—体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // è¿™ä¸ªç­–略是 å¤´æ˜¯å¤´çš„样式 å†…容是内容的样式 å…¶ä»–的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * excel文件读取
     *
     * @param clazz model类型Class对象
     * @param excel excel文件对象
     * @param <T>   model类型泛型
     * @return model类型数据集合
     * @throws IOException IO异常
     */
    public static <T extends BaseRowModel> List<T> readModelExcel(Class<T> clazz, MultipartFile excel) throws IOException {
        ExcelListener<T> excelListener = new ExcelListener<>();
        ExcelReader reader = getReader(excel, excelListener);
        try {
            reader.getSheets().forEach(sheet -> {
                sheet.setClazz(clazz);
                reader.read(sheet);
            });
        } catch (Exception e) {
            throw new IOException("请检查导入模板！");
        }
        return excelListener.getDataList();
    }

    /**
     *
     * @param clazz model类型Class对象
     * @param excel excel文件对象
     * @param sheetNumber æŒ‡å®šsheet页
     * @param <T> model类型泛型
     * @return model类型数据集合
     * @throws IOException
     */
    public static <T extends BaseRowModel> List<T> readModelSheetExcel(Class<T> clazz, MultipartFile excel, int sheetNumber) throws IOException {
        ExcelListener<T> excelListener = new ExcelListener<>();
        ExcelReader reader = getReader(excel, excelListener);
        try {
            Sheet sheet = reader.getSheets().get(sheetNumber);
            sheet.setClazz(clazz);
            reader.read(sheet);
        } catch (Exception e) {
            throw new IOException("请检查导入模板！");
        }
        return excelListener.getDataList();
    }

    /**
     * èŽ·å–excel文件读取对象
     *
     * @param excel         excel文件对象
     * @param excelListener excel事件处理
     * @return excel è¯»å–对象
     * @throws IOException IO异常
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx") && !filename.toLowerCase().endsWith(".xltm") && !filename.toLowerCase().endsWith(".xlsm"))) {
            throw new ExcelException(ResponseResultEnum.EXCEL_FILE_EXT_ERROR);
        }
        InputStream inputStream = new BufferedInputStream(excel.getInputStream());
        return new ExcelReader(inputStream, null, excelListener, false);
    }

    /**
     * å†™å…¥æ•°æ®åˆ°å¯¼å‡ºæ–‡ä»¶
     *
     * @param clazz       model类型Class对象
     * @param sheetNo     sheet序号
     * @param headLineMun head行数
     * @param startRow    å¼€å§‹å†™å…¥è¡Œæ•°
     * @param tableNo     table序号
     * @param modelList   model数据集合
     * @param temp        ä¸´æ—¶æ–‡ä»¶å¯¹è±¡
     * @param export      å¯¼å‡ºæ–‡ä»¶å¯¹è±¡
     * @param <T>         model泛型
     */
    public static <T extends BaseRowModel> void writeModelExport(Class<T> clazz, Integer sheetNo, Integer headLineMun, Integer startRow, Integer tableNo, List<T> modelList, File temp, File export) {
        try {
            InputStream inputStream = new FileInputStream(temp);
            FileOutputStream OutputStream = new FileOutputStream(export);
            ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream, OutputStream, ExcelTypeEnum.XLSX, false);
            writer.write(modelList, initSheet(sheetNo, headLineMun, startRow), initTable(tableNo, clazz));
            writer.finish();
            OutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Sheet初始化
     *
     * @param sheetNo     sheet序号
     * @param headLineMun head行数
     * @param startRow    å¼€å§‹å†™å…¥è¡Œæ•°
     * @return Sheet对象
     */
    public static Sheet initSheet(Integer sheetNo, Integer headLineMun, Integer startRow) {
        Sheet sheet = new Sheet(sheetNo, headLineMun);
        sheet.setStartRow(startRow);
        return sheet;
    }

    /**
     * Table初始化
     *
     * @param tableNo table序号
     * @param clazz   model类型Class对象
     * @param <T>     model泛型
     * @return Table对象
     */
    public static <T extends BaseRowModel> Table initTable(Integer tableNo, Class<T> clazz) {
        Table table = new Table(tableNo);
        table.setClazz(clazz);
        TableStyle tableStyle = new TableStyle();
        tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
        tableStyle.setTableHeadBackGroundColor(IndexedColors.WHITE);
        Font font = new Font();
        font.setBold(false);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        tableStyle.setTableContentFont(font);
        table.setTableStyle(tableStyle);
        return table;
    }

    /**
     * æ›¿æ¢æ¨¡æ¿æ ‡è®°
     *
     * @param export     å¯¼å‡ºæ–‡ä»¶å¯¹è±¡
     * @param index      sheet序号
     * @param rows       å•å…ƒæ ¼è¡Œåº
     * @param columns    å•å…ƒæ ¼åˆ—序
     * @param replaceMap å¤šä¸ªæ ‡è®°é›†åˆ
     */
    public static void editModelWorkBook(File export, Integer index, Integer rows, Integer columns, Map<String, String> replaceMap) {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(export);
//            editModelWorkBook(workBook,index,0,0,replaceMap);
            editModelWorkBook(workBook, index, rows, columns, replaceMap);
            workBook.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * æ›¿æ¢æ¨¡æ¿æ ‡è®°
     *
     * @param workBook   excel工作册对象
     * @param index      sheet序号
     * @param rows       å•å…ƒæ ¼è¡Œåº
     * @param columns    å•å…ƒæ ¼åˆ—序
     * @param replaceMap å¤šä¸ªæ ‡è®°é›†åˆ
     */
    public static void editModelWorkBook(XSSFWorkbook workBook, Integer index, Integer rows, Integer columns, Map<String, String> replaceMap) {
        XSSFSheet sheet = workBook.getSheetAt(index);
        Row row = sheet.getRow(rows);
        Cell cell = row.getCell(columns);
        String replaceString = cell.toString();
        for (Map.Entry<String, String> entry : replaceMap.entrySet()) {
            replaceString = replaceString.replace(entry.getKey(), entry.getValue());
        }
        cell.setCellValue(replaceString);
        workBook.setSheetName(index, replaceString.replace(":", ""));
    }

    /**
     * æ¨¡æ¿sheet克隆及命名
     *
     * @param temp           ä¸´æ—¶æ–‡ä»¶å¯¹è±¡
     * @param export         å¯¼å‡ºæ–‡ä»¶å¯¹è±¡
     * @param cloneSheetNo   å…‹éš†sheet序列
     * @param rows           å•å…ƒæ ¼è¡Œåº
     * @param columns        å•å…ƒæ ¼åˆ—序
     * @param replaceMapList å¤šä¸ªæ ‡è®°é›†åˆ
     */
    public static void writeModelCloneSheet(File temp, File export, Integer cloneSheetNo, Integer rows, Integer columns, List<Map<String, String>> replaceMapList) {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(temp);
            OutputStream outputStream = new FileOutputStream(export);
            /** å¦‚果你需要6份相同模板的sheet é‚£ä¹ˆä½ åªéœ€è¦å…‹éš†5份即可*/
            for (int index = 0; index < replaceMapList.size() - 1; index++) {
                /** å…‹éš†æ¨¡æ¿æ–‡ä»¶ */
                XSSFSheet sheet = workBook.cloneSheet(cloneSheetNo);
            }
            for (int index = 0; index < replaceMapList.size(); index++) {
                editModelWorkBook(workBook, index, rows, columns, replaceMapList.get(index));
            }
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workBook.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * ä¸´æ—¶æ–‡ä»¶å†™å…¥å¯¼å‡ºæ–‡ä»¶
     *
     * @param temp    ä¸´æ—¶æ–‡ä»¶å¯¹è±¡
     * @param export  å¯¼å‡ºæ–‡ä»¶å¯¹è±¡
     * @param index   sheet序号
     * @param rows    å•å…ƒæ ¼è¡Œåº
     * @param columns å•å…ƒæ ¼åˆ—序
     * @param isTime  æ˜¯å¦æœ‰æ—¶é—´æ ‡è®°
     */
    public static void writeModelWorkBook(File temp, File export, Integer index, Integer rows, Integer columns, Boolean isTime) {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(temp);
            OutputStream outputStream = new FileOutputStream(export);
            if (isTime) {
                writeModelWorkBook(workBook, index, rows, columns);
            }
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workBook.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * æ›¿æ¢æ—¶é—´æ ‡è®°ï¼ˆyear,month,day）
     *
     * @param workBook excel工作册对象
     * @param index    sheet序号
     * @param rows     å•å…ƒæ ¼è¡Œåº
     * @param columns  å•å…ƒæ ¼åˆ—序
     */
    public static void writeModelWorkBook(XSSFWorkbook workBook, Integer index, Integer rows, Integer columns) {
        XSSFSheet xssfSheet = workBook.getSheetAt(index);
        Row row = xssfSheet.getRow(rows);
        Cell cell = row.getCell(columns);
        String cellString = cell.toString();
        Calendar calendar = Calendar.getInstance();
        int year = calendar.get(Calendar.YEAR);
        int month = calendar.get(Calendar.MONTH) + 1;
        int day = calendar.get(Calendar.DAY_OF_MONTH);
        String yearString = cellString.replace("year", String.valueOf(year));
        String monthString = yearString.replace("month", String.valueOf(month));
        String dayString = monthString.replace("day", String.valueOf(day));
        cell.setCellValue(dayString);
    }

    /**
     * é’ˆå¯¹UserModel导出方法
     *
     * @param temp
     * @param export
     * @param isTime
     */
    public static void writeModelWorkBook(File temp, File export, Boolean isTime) {
        writeModelWorkBook(temp, export, 0, 1, 9, isTime);
    }


    public static void writeModelCloneSheet(File temp, File export, List<Map<String, String>> replaceMapList) {
        writeModelCloneSheet(temp, export, 0, 0, 0, replaceMapList);
    }

}

 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/ExcelListener.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,79 @@
package com.yuanchu.mom.utils.easyexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * excel数据导入数据处理
 *
 * @param <T>
 * @author Zou, Yu
 */
@Slf4j
@Getter
@Setter
public class ExcelListener<T> extends AnalysisEventListener<T> {

    private List<T> dataList = new ArrayList<>();

    @Override
    public void invoke(T classType, AnalysisContext analysisContext) {
        dataList.add(classType);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        String templateHead = "";

        List<String> headList = new ArrayList<>();
        for (Integer i : headMap.keySet()) {
            headList.add(headMap.get(i));
        }
        String importHead = headMap.values().toString();
        log.info("importHead:{}", importHead);
        String templateName = context.getCurrentSheet().getClazz().getSimpleName();
        log.info("templateName:", templateName);
//        if ("CostModel".equals(templateName)) {
//            templateHead = templateHeadCost;
//        } else if ("StaffInfoTemplate".equals(templateName)) {
//            templateHead = templateHeadStaff;
//        } else if ("OrganizationTemplate".equals(templateName)) {
//            templateHead = templateHeadOrganization;
//        }else if ("StaffDictionaryTemplate".equals(templateName)) {
//            templateHead = templateHeadDictionary;
//        }else if ("StaffCtfTemplate".equals(templateName)){
//            templateHead = templateHeadCtf;
//        }else if ("IndustryLineTemplate".equals(templateName)){
//            templateHead = templateHeadIndustryLine;
//        }else if ("SubServiceLineTemplate".equals(templateName)){
//            templateHead = templateHeadSubServiceLine;
//        }else if ("StaffHealthTemplate".equals(templateName)){
//            templateHead = templateHeadHealth;
//        }else if ("AccountDataTemplate".equals(templateName)){
//            templateHead = templateAccountData;
//        }else if ("PlInfoTemplate".equals(templateName)){
//            templateHead = templatePlInfo;
//        }else if ("CodeDataTemplate".equals(templateName)){
//            templateHead = templateCodeData;
//        }else if ("CodeTbdDataTemplate".equals(templateName)){
//            templateHead = templateCodeTbdData;
//        }

        if (!templateHead.equals(importHead)) {
            throw new RuntimeException(ResponseResultEnum.EXCEL_MODEL_ERROR.getMessage());
        }

    }

}

 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/LocalDateConverter.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,43 @@
package com.yuanchu.mom.utils.easyexcel;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.springframework.stereotype.Component;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

/**
*@author: Zou, Yu
*@description:
*@date: 2023/9/24 0024 13:18
**/
@Component
public class LocalDateConverter implements Converter<LocalDate> {

    @Override
    public Class<LocalDate> supportJavaTypeKey() {
        return LocalDate.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public LocalDate convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
                                       GlobalConfiguration globalConfiguration) {
        return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
    }

    @Override
    public CellData<String> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
        return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
    }

}

 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/ResponseResultEnum.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,28 @@
package com.yuanchu.mom.utils.easyexcel;

import lombok.Getter;

/**
 * å¼‚常信息枚举
 * @auther Zou, Yu
 * @create 2023-09-24
 */
@Getter
public enum ResponseResultEnum {
    EXCEL_FILE_EXT_ERROR(1000,"文件格式错误"),
    EXCEL_FILE_READ_FAIL(1001,"excel文件读取失败,请检查模板!"),
    EXCEL_FILE_IS_EMPTY(1002,"excel文件为空"),
    MODEL_FILE_NOT_EXIT(1003,"模版文件不存在"),
    EXCEL_ACTIVE_DATE_ERROR(1004,"生效时间错误"),
    EXCEL_MODEL_ERROR(1005,"导入模板有误，请检查！"),
    EXCEL_FILE_EXCESS(1006,"批量导入数据量不可超过500条");

    private Integer status;
    private String message;

    ResponseResultEnum(Integer status, String message) {
        this.status = status;
        this.message = message;
    }

}

 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/converter/SaleTypeConverter.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,48 @@
package com.yuanchu.mom.utils.easyexcel.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.springframework.stereotype.Component;

/** è®¢å•çŠ¶æ€è½¬æ¢
 * @Author: Zou, Yu
 * @DATE: 2023/9/24 0024 14:21
 */
@Component
public class SaleTypeConverter implements Converter<String> {

    @Override
    public Class<String> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if("通过".equals(cellData.getStringValue())){
            return "1";
        }else if("不通过".equals(cellData.getStringValue())){
            return "0";
        }else{
            return "2";
        }
    }

    @Override
    public CellData convertToExcelData(String str, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if("1".equals(str)){
            return new CellData<>("通过");
        }else if("0".equals(str)){
            return new CellData<>("不通过");
        }else{
            return new CellData<>("未审核");
        }
    }
}

 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/template/ExportSaleTemplate.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,68 @@
package com.yuanchu.mom.utils.easyexcel.template;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.BaseRowModel;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.yuanchu.mom.utils.easyexcel.converter.SaleTypeConverter;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;
import java.util.Date;

/**
 * @Author: Zou, Yu
 * @DATE: 2023/9/24 0024 11:21
 */
@Data
@EqualsAndHashCode(callSuper = true)
public class ExportSaleTemplate extends BaseRowModel implements Serializable {

    @ColumnWidth(26)
    @ExcelProperty(value="序号", index = 0)
    private Integer id;

    @ColumnWidth(26)
    @ExcelProperty(value="订单编号", index = 1)
    @JsonSerialize
    private String orderNumber;

    @ColumnWidth(26)
    @ExcelProperty(value="订单类型", index = 2)
    @JsonSerialize
    private String orderType;

    @ColumnWidth(26)
    @ExcelProperty(value="合同编号", index = 3)
    @JsonSerialize
    private String code;

    @ColumnWidth(26)
    @ExcelProperty(value="工程名称", index = 4)
    @JsonSerialize
    private String name;

    @ColumnWidth(26)
    @ExcelProperty(value="客户名称", index = 5)
    @JsonSerialize
    private String proname;

    @ColumnWidth(26)
    @ExcelProperty(value="交货日期", index = 6)
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date delTime;

    @ColumnWidth(26)
    @ExcelProperty(value="备注", index = 7)
    @JsonSerialize
    private String note;

    @ColumnWidth(26)
    @ExcelProperty(value="状态", index = 8 ,converter = SaleTypeConverter.class)
    @JsonSerialize
    private String type;

}

 inventory-server/src/main/java/com/yuanchu/mom/controller/SaleController.java

@@ -1,15 +1,22 @@
package com.yuanchu.mom.controller;


import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yuanchu.mom.pojo.dto.ExportSaleDto;
import com.yuanchu.mom.utils.easyexcel.template.ExportSaleTemplate;
import com.yuanchu.mom.pojo.dto.SaleDto;
import com.yuanchu.mom.pojo.dto.SaleMaterialDto;
import com.yuanchu.mom.pojo.vo.SaleVo;
import com.yuanchu.mom.service.RepertoryService;
import com.yuanchu.mom.utils.JackSonUtil;
import com.yuanchu.mom.pojo.dto.SaleDto;
import com.yuanchu.mom.service.SaleService;
import com.yuanchu.mom.utils.JackSonUtil;
import com.yuanchu.mom.utils.Jwt;
import com.yuanchu.mom.utils.easyexcel.CustemHandler;
import com.yuanchu.mom.utils.easyexcel.EasyExcelUtils;
import com.yuanchu.mom.utils.easyexcel.LocalDateConverter;
import com.yuanchu.mom.vo.Result;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
@@ -20,6 +27,10 @@
import springfox.documentation.spring.web.json.Json;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

/**
@@ -148,4 +159,16 @@
        return Result.success(saleService.download(id));
    }

    @ApiOperation(value = "销售订单导出")
    @ApiImplicitParams(value = {
            @ApiImplicitParam(name = "orderNumber", value = "订单编号", dataTypeClass = String.class),
            @ApiImplicitParam(name = "name", value = "产品名称", dataTypeClass = String.class),
            @ApiImplicitParam(name = "type", value = "状态(为空=全部)", dataTypeClass = Integer.class),
            @ApiImplicitParam(name = "delTime", value = "交货日期", dataTypeClass = String.class)
    })
    @PostMapping("/exportSale")
    public void exportSale(@RequestBody ExportSaleDto dto, HttpServletResponse response){
        saleService.exportSale(dto,response);
    }

}

 inventory-server/src/main/java/com/yuanchu/mom/mapper/SaleMapper.java

@@ -4,7 +4,9 @@
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yuanchu.mom.pojo.Sale;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yuanchu.mom.utils.easyexcel.template.ExportSaleTemplate;

import java.util.List;
import java.util.Map;

/**
@@ -25,4 +27,6 @@

    //批量删除
    void delAllSale(String ids);

    List<ExportSaleTemplate> selectSaleListByExport(String orderNumber, String name, Integer type, String delTime);
}

 inventory-server/src/main/java/com/yuanchu/mom/pojo/dto/ExportSaleDto.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,30 @@
package com.yuanchu.mom.pojo.dto;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import lombok.Data;

import java.util.Date;

/**
 * @Author: Zou, Yu
 * @DATE: 2023/9/24 0024 15:45
 */
@Data
@JsonSerialize
public class ExportSaleDto {

    @JsonSerialize
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private String delTime;

    @JsonSerialize
    private String name;

    @JsonSerialize
    private Integer type;

    @JsonSerialize
    private String orderNumber;

}

 inventory-server/src/main/java/com/yuanchu/mom/service/SaleService.java

@@ -5,9 +5,12 @@
import com.yuanchu.mom.pojo.Sale;
import com.baomidou.mybatisplus.extension.service.IService;
import com.yuanchu.mom.pojo.dto.ConsignmentDto1;
import com.yuanchu.mom.pojo.dto.ExportSaleDto;
import com.yuanchu.mom.pojo.dto.SaleDto;
import com.yuanchu.mom.pojo.vo.SaleVo;
import com.yuanchu.mom.utils.easyexcel.template.ExportSaleTemplate;

import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;

@@ -90,4 +93,12 @@
     * @return
     */
    List<String> download(Integer id);

    /**
     * å¯¼å‡ºé”€å”®è®¢å•
     * @param dto
     * @param response
     */
    void exportSale(ExportSaleDto dto, HttpServletResponse response);

}

 inventory-server/src/main/java/com/yuanchu/mom/service/impl/SaleServiceImpl.java

@@ -1,6 +1,8 @@
package com.yuanchu.mom.service.impl;

import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
@@ -10,20 +12,25 @@
import com.yuanchu.mom.pojo.Sale;
import com.yuanchu.mom.mapper.SaleMapper;
import com.yuanchu.mom.pojo.SaleMaterial;
import com.yuanchu.mom.pojo.dto.ConsignmentDto1;
import com.yuanchu.mom.pojo.dto.ConsignmentDto2;
import com.yuanchu.mom.pojo.dto.SaleDto;
import com.yuanchu.mom.pojo.dto.SaleMaterialDto;
import com.yuanchu.mom.pojo.dto.*;
import com.yuanchu.mom.pojo.vo.SaleVo;
import com.yuanchu.mom.service.SaleMaterialService;
import com.yuanchu.mom.service.SaleService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yuanchu.mom.utils.easyexcel.CustemHandler;
import com.yuanchu.mom.utils.easyexcel.EasyExcelUtils;
import com.yuanchu.mom.utils.easyexcel.LocalDateConverter;
import com.yuanchu.mom.utils.easyexcel.template.ExportSaleTemplate;
import com.yuanchu.mom.vo.Result;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
@@ -257,4 +264,40 @@
        }
        return list;
    }

    @Override
    public void exportSale(ExportSaleDto dto, HttpServletResponse response) {
        try{
            List<ExportSaleTemplate> exportSaleTemplates = saleMapper.selectSaleListByExport(dto.getOrderNumber(), dto.getName(), dto.getType(), dto.getDelTime());
            for (int i = 0; i < exportSaleTemplates.size(); i++) {
                exportSaleTemplates.get(i).setId(i+1);
            }
            String fileName = String.valueOf(new StringBuilder()
                    .append("销售订单_")
                    .append(DateUtil.today())
                    .append(ExcelTypeEnum.XLS.getValue()));
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            response.setContentType("application/octet-stream");
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            EasyExcel.write(response.getOutputStream())
                    .head(ExportSaleTemplate.class)
                    .registerConverter(new LocalDateConverter())
                    .autoCloseStream(true)
                    .excelType(ExcelTypeEnum.XLS)
                    .registerWriteHandler(new CustemHandler())
                    .registerWriteHandler(EasyExcelUtils.getStyleStrategy())
                    .sheet("Sheet1")
                    .doWrite(exportSaleTemplates);
        }catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                response.getOutputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

 inventory-server/src/main/resources/mapper/SaleMapper.xml

@@ -33,7 +33,31 @@
        </if>
        order by id desc
    </select>

    <select id="selectSaleListByExport" resultType="com.yuanchu.mom.utils.easyexcel.template.ExportSaleTemplate">
        select order_number AS orderNumber,
        code,
        name,
        proname,
        type,
        DATE_FORMAT(delTime, '%Y-%m-%d') AS delTime,
        note,
        order_type AS orderType
        from mom_ocean.sale
        where state=1
        <if test="orderNumber!=null and orderNumber!=''">
            and order_number like concat('%',#{orderNumber},'%')
        </if>
        <if test="name!=null and name!=''">
            and name LIKE CONCAT('%',#{name},'%')
        </if>
        <if test="type!=null ">
            and type=#{type}
        </if>
        <if test="delTime!=null and delTime!=''">
            and DATE_FORMAT(delTime, '%Y-%m-%d')=#{delTime}
        </if>
        order by id desc
    </select>
    <!--查询订单BOM列表-->
    <select id="selectAllOrder" resultType="java.util.Map">
        select sm.id,

 pom.xml

@@ -39,6 +39,8 @@
        <feign-okhttp.version>11.0</feign-okhttp.version>
        <shiro.version>1.5.3</shiro.version>
        <hutool.version>5.8.16</hutool.version>
        <easyexcel.version>2.2.10</easyexcel.version>
        <poi.version>4.1.2</poi.version>
        <!--提交前-->
        <!-- æ‰“包后导出的路径 -->
        <package.path>${project.build.directory}/BLOG</package.path>
@@ -98,6 +100,24 @@

    <dependencyManagement>
        <dependencies>
            <!--easyexcel-->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>${easyexcel.version}</version>
            </dependency>
            <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>${poi.version}</version>
            </dependency>
            <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>${poi.version}</version>
            </dependency>
            <!--log4j-->
            <dependency>
                <groupId>log4j</groupId>