ecel

Hentua

2023-06-10 72e7d01b5b249ae1a079d19cdc62ecd2782c325c

ecel

 pom.xml

@@ -191,11 +191,11 @@
        </dependency>

        <!-- Excel导入导出插件 -->
        <dependency>
            <groupId>com.wuwenze</groupId>
            <artifactId>ExcelKit</artifactId>
            <version>2.0.72</version>
        </dependency>
<!--        <dependency>-->
<!--            <groupId>com.wuwenze</groupId>-->
<!--            <artifactId>ExcelKit</artifactId>-->
<!--            <version>2.0.72</version>-->
<!--        </dependency>-->

        <!-- lombok -->
        <dependency>
@@ -312,30 +312,37 @@
            <version>1.5</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>
<!--        <dependency>-->
<!--            <groupId>org.apache.poi</groupId>-->
<!--            <artifactId>poi-ooxml</artifactId>-->
<!--            <version>3.8</version>-->
<!--        </dependency>-->

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.8</version>
        </dependency>
<!--        <dependency>-->
<!--            <groupId>org.apache.poi</groupId>-->
<!--            <artifactId>poi</artifactId>-->
<!--            <version>3.8</version>-->
<!--        </dependency>-->

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.8</version>
        </dependency>
<!--        &lt;!&ndash; https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas &ndash;&gt;-->
<!--        <dependency>-->
<!--            <groupId>org.apache.poi</groupId>-->
<!--            <artifactId>poi-ooxml-schemas</artifactId>-->
<!--            <version>3.8</version>-->
<!--        </dependency>-->

        <dependency>
            <groupId>com.github.binarywang</groupId>
            <artifactId>weixin-java-pay</artifactId>
            <version>4.4.0</version>
        </dependency>


        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>
    </dependencies>

    <build>

 src/main/java/cc/mrbird/febs/common/converter/TimeConverter.java

@@ -1,32 +1,32 @@
package cc.mrbird.febs.common.converter;

import cc.mrbird.febs.common.utils.DateUtil;
import com.wuwenze.poi.convert.WriteConverter;
import com.wuwenze.poi.exception.ExcelKitWriteConverterException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.text.ParseException;

/**
 * Execl导出时间类型字段格式化
 *
 * @author MrBird
 */
@Slf4j
public class TimeConverter implements WriteConverter {
    @Override
    public String convert(Object value) {
        if (value == null) {
            return StringUtils.EMPTY;
        } else {
            try {
                return DateUtil.formatCstTime(value.toString(), DateUtil.FULL_TIME_SPLIT_PATTERN);
            } catch (ParseException e) {
                String message = "时间转换异常";
                log.error(message, e);
                throw new ExcelKitWriteConverterException(message);
            }
        }
    }
}
//package cc.mrbird.febs.common.converter;
//
//import cc.mrbird.febs.common.utils.DateUtil;
//import com.wuwenze.poi.convert.WriteConverter;
//import com.wuwenze.poi.exception.ExcelKitWriteConverterException;
//import lombok.extern.slf4j.Slf4j;
//import org.apache.commons.lang3.StringUtils;
//
//import java.text.ParseException;
//
///**
// * Execl导出时间类型字段格式化
// *
// * @author MrBird
// */
//@Slf4j
//public class TimeConverter implements WriteConverter {
//    @Override
//    public String convert(Object value) {
//        if (value == null) {
//            return StringUtils.EMPTY;
//        } else {
//            try {
//                return DateUtil.formatCstTime(value.toString(), DateUtil.FULL_TIME_SPLIT_PATTERN);
//            } catch (ParseException e) {
//                String message = "时间转换异常";
//                log.error(message, e);
//                throw new ExcelKitWriteConverterException(message);
//            }
//        }
//    }
//}

 src/main/java/cc/mrbird/febs/common/utils/excl/ExcelUtil.java

@@ -135,35 +135,35 @@
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            // 格式化数字
            DecimalFormat nf = new DecimalFormat("0.00");
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    columnValue = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        columnValue = df.format(cell.getNumericCellValue());
                    } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        columnValue = nf.format(cell.getNumericCellValue());
                    } else {
                        columnValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    columnValue = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    columnValue = "";
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    // 格式单元格
                    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
                    evaluator.evaluateFormulaCell(cell);
                    CellValue cellValue = evaluator.evaluate(cell);
                    columnValue = cellValue.getNumberValue();
                    break;
                default:
                    columnValue = cell.toString();
            }
//            switch (cell.getCellType()) {
//                case Cell.CELL_TYPE_STRING:
//                    columnValue = cell.getStringCellValue();
//                    break;
//                case Cell.CELL_TYPE_NUMERIC:
//                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
//                        columnValue = df.format(cell.getNumericCellValue());
//                    } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//                        columnValue = nf.format(cell.getNumericCellValue());
//                    } else {
//                        columnValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
//                    }
//                    break;
//                case Cell.CELL_TYPE_BOOLEAN:
//                    columnValue = cell.getBooleanCellValue();
//                    break;
//                case Cell.CELL_TYPE_BLANK:
//                    columnValue = "";
//                    break;
//                case Cell.CELL_TYPE_FORMULA:
//                    // 格式单元格
//                    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
//                    evaluator.evaluateFormulaCell(cell);
//                    CellValue cellValue = evaluator.evaluate(cell);
//                    columnValue = cellValue.getNumberValue();
//                    break;
//                default:
//                    columnValue = cell.toString();
//            }
        }
        return columnValue;
    }
@@ -285,24 +285,24 @@
//        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setWrapText(true);

        if (STYLE_HEADER == type) {
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 16);
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            style.setFont(font);
        } else if (STYLE_TITLE == type) {
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 18);
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            style.setFont(font);
        } else if (STYLE_DATA == type) {
            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 12);
            style.setFont(font);
        }
//        if (STYLE_HEADER == type) {
//            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//            Font font = wb.createFont();
//            font.setFontHeightInPoints((short) 16);
//            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
//            style.setFont(font);
//        } else if (STYLE_TITLE == type) {
//            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//            Font font = wb.createFont();
//            font.setFontHeightInPoints((short) 18);
//            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
//            style.setFont(font);
//        } else if (STYLE_DATA == type) {
//            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
//            Font font = wb.createFont();
//            font.setFontHeightInPoints((short) 12);
//            style.setFont(font);
//        }
        cellStyleMap.put(type, style);
        return style;
    }

 src/main/java/cc/mrbird/febs/mall/controller/AdminMallMemberController.java

@@ -10,6 +10,10 @@
import cc.mrbird.febs.mall.service.IAdminMallMemberService;
import cc.mrbird.febs.mall.service.IApiMallMemberService;
import cc.mrbird.febs.mall.vo.AdminAgentLevelOptionTreeVo;
import cc.mrbird.febs.mall.vo.ChargeListExportVo;
import cc.mrbird.febs.system.entity.Dept;
import com.alibaba.excel.EasyExcel;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.annotations.Param;
@@ -17,8 +21,12 @@
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

@@ -466,4 +474,15 @@
        return null;
    }

    @GetMapping(value = "/chargeList/excel")
    public void exportChargeList(MoneyChargeListDto moneyChargeListDto, HttpServletResponse response) throws IOException {
        List<ChargeListExportVo> list = this.mallMemberService.findChargeListForExcel(moneyChargeListDto);

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName= URLEncoder.encode("提现列表","UTF-8").replaceAll("\\+","%20");
        response.setHeader("Content-disposition","attachment;filename*=utf-8''"+fileName+".xlsx");
        EasyExcel.write(response.getOutputStream(),ChargeListExportVo.class).sheet("提现列表").doWrite(list);
    }

}

 src/main/java/cc/mrbird/febs/mall/mapper/MallMoneyFlowMapper.java

@@ -1,5 +1,6 @@
package cc.mrbird.febs.mall.mapper;

import cc.mrbird.febs.mall.dto.AdminRoleReleaseDto;
import cc.mrbird.febs.mall.dto.MoneyChargeListDto;
import cc.mrbird.febs.mall.dto.MoneyFlowDto;
import cc.mrbird.febs.mall.dto.MoneyFlowListDto;
@@ -49,4 +50,6 @@
    void updateRemarkAndIsReturnById(@Param("remark")String remarkNum, @Param("id")Long id);

    MallMoneyFlow selectByOrderNoAndMemberId(@Param("orderNo")String orderNo, @Param("memberId")Long memberId, @Param("isReturn")int isReturn);

    List<ChargeListExportVo> selectChargeListForExcel(@Param("record") MoneyChargeListDto moneyChargeListDto);
}

 src/main/java/cc/mrbird/febs/mall/service/IAdminMallMemberService.java

@@ -116,4 +116,6 @@
    List<MallMember> findParentMemberList(List<String> inviteIds, Integer level, boolean containsParent);

    TreeItemVo findMatrixTree();

    List<ChargeListExportVo> findChargeListForExcel(MoneyChargeListDto moneyChargeListDto);
}

 src/main/java/cc/mrbird/febs/mall/service/impl/AdminMallMemberServiceImpl.java

@@ -807,4 +807,9 @@
    public TreeItemVo findMatrixTree() {
        return null;
    }

    @Override
    public List<ChargeListExportVo> findChargeListForExcel(MoneyChargeListDto moneyChargeListDto) {
        return this.mallMoneyFlowMapper.selectChargeListForExcel(moneyChargeListDto);
    }
}

 src/main/java/cc/mrbird/febs/mall/vo/ChargeListExportVo.java

New file
@@ -0,0 +1,42 @@
package cc.mrbird.febs.mall.vo;


import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@Data
public class ChargeListExportVo {

    @ExcelProperty(value = "会员名称")
    private String memberName;

    @ExcelProperty(value = "会员账号")
    private String phone;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = "提现时间")
    private Date createTime;

    @ExcelProperty(value = "提现金额")
    private BigDecimal amount;

    @ExcelProperty(value = "持卡人姓名")
    private String cardName;

    @ExcelProperty(value = "所属银行")
    private String bankName;

    @ExcelProperty(value = "所属支行")
    private String subbranchName;

    @ExcelProperty(value = "银行卡号")
    private String bankNo;

    @ExcelProperty(value = "预留手机号")
    private String bankPhone;

}

 src/main/java/cc/mrbird/febs/monitor/controller/LogController.java

@@ -7,7 +7,7 @@
import cc.mrbird.febs.monitor.entity.SystemLog;
import cc.mrbird.febs.monitor.service.ILogService;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.wuwenze.poi.ExcelKit;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.shiro.authz.annotation.RequiresPermissions;
@@ -53,6 +53,6 @@
    @ControllerEndpoint(exceptionMessage = "导出Excel失败")
    public void export(QueryRequest request, SystemLog lg, HttpServletResponse response) {
        List<SystemLog> logs = this.logService.findLogs(lg, request).getRecords();
        ExcelKit.$Export(SystemLog.class, response).downXlsx(logs, false);
        // ExcelKit.$Export(SystemLog.class, response).downXlsx(logs, false);
    }
}

 src/main/java/cc/mrbird/febs/monitor/controller/LoginLogController.java

@@ -7,7 +7,7 @@
import cc.mrbird.febs.monitor.entity.LoginLog;
import cc.mrbird.febs.monitor.service.ILoginLogService;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.wuwenze.poi.ExcelKit;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.shiro.authz.annotation.RequiresPermissions;
@@ -53,6 +53,6 @@
    @ControllerEndpoint(exceptionMessage = "导出Excel失败")
    public void export(QueryRequest request, LoginLog loginLog, HttpServletResponse response) {
        List<LoginLog> loginLogs = this.loginLogService.findLoginLogs(loginLog, request).getRecords();
        ExcelKit.$Export(LoginLog.class, response).downXlsx(loginLogs, false);
        // ExcelKit.$Export(LoginLog.class, response).downXlsx(loginLogs, false);
    }
}

 src/main/java/cc/mrbird/febs/monitor/entity/LoginLog.java

@@ -1,13 +1,12 @@
package cc.mrbird.febs.monitor.entity;

import cc.mrbird.febs.common.converter.TimeConverter;

import cc.mrbird.febs.common.utils.HttpContextUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.wuwenze.poi.annotation.Excel;
import com.wuwenze.poi.annotation.ExcelField;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
@@ -22,7 +21,6 @@
@Slf4j
@Data
@TableName("t_login_log")
@Excel("登录日志")
public class LoginLog implements Serializable {

    private static final long serialVersionUID = 921991157363932095L;
@@ -36,39 +34,39 @@
     * 登录用户
     */
    @TableField("USERNAME")
    @ExcelField("登录用户")
    @ExcelProperty("登录用户")
    private String username;

    /**
     * 登录时间
     */
    @TableField("LOGIN_TIME")
    @ExcelField(value = "登录时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "登录时间")
    private Date loginTime;

    /**
     * 登录地点
     */
    @TableField("LOCATION")
    @ExcelField(value = "登录地点")
    @ExcelProperty(value = "登录地点")
    private String location;
    /**
     * 登录 IP
     */
    @TableField("IP")
    @ExcelField("登录IP")
    @ExcelProperty("登录IP")
    private String ip;
    /**
     * 操作系统
     */
    @TableField("`SYSTEM`")
    @ExcelField("操作系统")
    @ExcelProperty("操作系统")
    private String system;
    /**
     * 登录浏览器
     */
    @TableField("BROWSER")
    @ExcelField("登录浏览器")
    @ExcelProperty("登录浏览器")
    private String browser;

    private transient String loginTimeFrom;

 src/main/java/cc/mrbird/febs/monitor/entity/SystemLog.java

@@ -1,12 +1,11 @@
package cc.mrbird.febs.monitor.entity;

import cc.mrbird.febs.common.converter.TimeConverter;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.wuwenze.poi.annotation.Excel;
import com.wuwenze.poi.annotation.ExcelField;
import lombok.Data;

import java.io.Serializable;
@@ -17,7 +16,6 @@
 */
@Data
@TableName("t_log")
@Excel("系统日志表")
public class SystemLog implements Serializable {

    /**
@@ -30,56 +28,56 @@
     * 操作用户
     */
    @TableField("USERNAME")
    @ExcelField(value = "操作用户")
    @ExcelProperty(value = "操作用户")
    private String username;

    /**
     * 操作内容
     */
    @TableField("OPERATION")
    @ExcelField(value = "操作内容")
    @ExcelProperty(value = "操作内容")
    private String operation;

    /**
     * 耗时
     */
    @TableField("TIME")
    @ExcelField(value = "耗时（毫秒）")
    @ExcelProperty(value = "耗时（毫秒）")
    private Long time;

    /**
     * 操作方法
     */
    @TableField("METHOD")
    @ExcelField(value = "操作方法")
    @ExcelProperty(value = "操作方法")
    private String method;

    /**
     * 方法参数
     */
    @TableField("PARAMS")
    @ExcelField(value = "方法参数")
    @ExcelProperty(value = "方法参数")
    private String params;

    /**
     * 操作者IP
     */
    @TableField("IP")
    @ExcelField(value = "操作者IP")
    @ExcelProperty(value = "操作者IP")
    private String ip;

    /**
     * 创建时间
     */
    @TableField("CREATE_TIME")
    @ExcelField(value = "操作时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "操作时间")
    private Date createTime;

    /**
     * 操作地点
     */
    @TableField("LOCATION")
    @ExcelField(value = "操作地点")
    @ExcelProperty(value = "操作地点")
    private String location;

    private transient String createTimeFrom;

 src/main/java/cc/mrbird/febs/others/controller/EximportController.java

@@ -10,9 +10,7 @@
import com.google.common.base.Stopwatch;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import com.wuwenze.poi.ExcelKit;
import com.wuwenze.poi.handler.ExcelReadHandler;
import com.wuwenze.poi.pojo.ExcelErrorField;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
@@ -66,7 +64,7 @@
            list.add(eximport);
        });
        // 构建模板
        ExcelKit.$Export(Eximport.class, response).downXlsx(list, true);
        // ExcelKit.$Export(Eximport.class, response).downXlsx(list, true);
    }

    /**
@@ -87,20 +85,20 @@
        Stopwatch stopwatch = Stopwatch.createStarted();
        final List<Eximport> data = Lists.newArrayList();
        final List<Map<String, Object>> error = Lists.newArrayList();
        ExcelKit.$Import(Eximport.class).readXlsx(file.getInputStream(), new ExcelReadHandler<Eximport>() {
            @Override
            public void onSuccess(int sheet, int row, Eximport eximport) {
                // 数据校验成功时，加入集合
                eximport.setCreateTime(new Date());
                data.add(eximport);
            }

            @Override
            public void onError(int sheet, int row, List<ExcelErrorField> errorFields) {
                // 数据校验失败时，记录到 error集合
                error.add(ImmutableMap.of("row", row, "errorFields", errorFields));
            }
        });
//        ExcelKit.$Import(Eximport.class).readXlsx(file.getInputStream(), new ExcelReadHandler<Eximport>() {
//            @Override
//            public void onSuccess(int sheet, int row, Eximport eximport) {
//                // 数据校验成功时，加入集合
//                eximport.setCreateTime(new Date());
//                data.add(eximport);
//            }
//
//            @Override
//            public void onError(int sheet, int row, List<ExcelErrorField> errorFields) {
//                // 数据校验失败时，记录到 error集合
//                error.add(ImmutableMap.of("row", row, "errorFields", errorFields));
//            }
//        });
        if (CollectionUtils.isNotEmpty(data)) {
            // 将合法的记录批量入库
            this.eximportService.batchInsert(data);
@@ -118,6 +116,6 @@
    @ControllerEndpoint(exceptionMessage = "导出Excel失败")
    public void export(QueryRequest queryRequest, Eximport eximport, HttpServletResponse response) {
        List<Eximport> eximports = this.eximportService.findEximports(queryRequest, eximport).getRecords();
        ExcelKit.$Export(Eximport.class, response).downXlsx(eximports, false);
        // ExcelKit.$Export(Eximport.class, response).downXlsx(eximports, false);
    }
}

 src/main/java/cc/mrbird/febs/others/entity/Eximport.java

@@ -1,9 +1,7 @@
package cc.mrbird.febs.others.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableName;
import com.wuwenze.poi.annotation.Excel;
import com.wuwenze.poi.annotation.ExcelField;
import com.wuwenze.poi.validator.EmailValidator;
import lombok.Data;

import java.util.Date;
@@ -15,28 +13,24 @@
 */
@Data
@TableName("t_eximport")
@Excel("测试导入导出数据")
public class Eximport {

    /**
     * 字段1
     */
    @ExcelField(value = "字段1", required = true, maxLength = 20,
            comment = "提示：必填，长度不能超过20个字符")
    @ExcelProperty(value = "字段1")
    private String field1;

    /**
     * 字段2
     */
    @ExcelField(value = "字段2", required = true, maxLength = 11, regularExp = "[0-9]+",
            regularExpMessage = "必须是数字", comment = "提示: 必填，只能填写数字，并且长度不能超过11位")
    @ExcelProperty(value = "字段2")
    private Integer field2;

    /**
     * 字段3
     */
    @ExcelField(value = "字段3", required = true, maxLength = 50,
            comment = "提示：必填，只能填写邮箱，长度不能超过50个字符", validator = EmailValidator.class)
    @ExcelProperty(value = "字段3")
    private String field3;

    /**

 src/main/java/cc/mrbird/febs/system/controller/DeptController.java

@@ -9,7 +9,7 @@
import cc.mrbird.febs.system.entity.Dept;
import cc.mrbird.febs.system.service.IDeptService;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.wuwenze.poi.ExcelKit;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.shiro.authz.annotation.RequiresPermissions;
@@ -74,6 +74,6 @@
    @ControllerEndpoint(exceptionMessage = "导出Excel失败")
    public void export(Dept dept, QueryRequest request, HttpServletResponse response) throws FebsException {
        List<Dept> depts = this.deptService.findDepts(dept, request);
        ExcelKit.$Export(Dept.class, response).downXlsx(depts, false);
//        // ExcelKit.$Export(Dept.class, response).downXlsx(depts, false);
    }
}

 src/main/java/cc/mrbird/febs/system/controller/MenuController.java

@@ -9,7 +9,7 @@
import cc.mrbird.febs.system.entity.Menu;
import cc.mrbird.febs.system.entity.User;
import cc.mrbird.febs.system.service.IMenuService;
import com.wuwenze.poi.ExcelKit;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
@@ -78,6 +78,6 @@
    @ControllerEndpoint(exceptionMessage = "导出Excel失败")
    public void export(Menu menu, HttpServletResponse response) {
        List<Menu> menus = this.menuService.findMenuList(menu);
        ExcelKit.$Export(Menu.class, response).downXlsx(menus, false);
        // ExcelKit.$Export(Menu.class, response).downXlsx(menus, false);
    }
}

 src/main/java/cc/mrbird/febs/system/controller/RoleController.java

@@ -8,7 +8,7 @@
import cc.mrbird.febs.common.exception.FebsException;
import cc.mrbird.febs.system.entity.Role;
import cc.mrbird.febs.system.service.IRoleService;
import com.wuwenze.poi.ExcelKit;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.shiro.authz.annotation.RequiresPermissions;
@@ -72,7 +72,7 @@
    @ControllerEndpoint(exceptionMessage = "导出Excel失败")
    public void export(QueryRequest queryRequest, Role role, HttpServletResponse response) throws FebsException {
        List<Role> roles = this.roleService.findRoles(role, queryRequest).getRecords();
        ExcelKit.$Export(Role.class, response).downXlsx(roles, false);
        // ExcelKit.$Export(Role.class, response).downXlsx(roles, false);
    }

}

 src/main/java/cc/mrbird/febs/system/controller/UserController.java

@@ -9,7 +9,7 @@
import cc.mrbird.febs.system.entity.User;
import cc.mrbird.febs.system.service.IUserService;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.wuwenze.poi.ExcelKit;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
@@ -132,6 +132,6 @@
    @ControllerEndpoint(exceptionMessage = "导出Excel失败")
    public void export(QueryRequest queryRequest, User user, HttpServletResponse response) {
        List<User> users = this.userService.findUserDetailList(user, queryRequest).getRecords();
        ExcelKit.$Export(User.class, response).downXlsx(users, false);
        // ExcelKit.$Export(User.class, response).downXlsx(users, false);
    }
}

 src/main/java/cc/mrbird/febs/system/entity/Dept.java

@@ -1,12 +1,11 @@
package cc.mrbird.febs.system.entity;

import cc.mrbird.febs.common.converter.TimeConverter;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.wuwenze.poi.annotation.Excel;
import com.wuwenze.poi.annotation.ExcelField;
import lombok.Data;

import javax.validation.constraints.NotBlank;
@@ -19,7 +18,6 @@
 */
@Data
@TableName("t_dept")
@Excel("部门信息表")
public class Dept implements Serializable {

    private static final long serialVersionUID = 5702271568363798328L;
@@ -44,7 +42,7 @@
    @TableField("DEPT_NAME")
    @NotBlank(message = "{required}")
    @Size(max = 10, message = "{noMoreThan}")
    @ExcelField(value = "部门名称")
    @ExcelProperty(value = "部门名称")
    private String deptName;

    /**
@@ -57,11 +55,11 @@
     * 创建时间
     */
    @TableField("CREATE_TIME")
    @ExcelField(value = "创建时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "创建时间")
    private Date createTime;

    @TableField("MODIFY_TIME")
    @ExcelField(value = "修改时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "修改时间")
    private Date modifyTime;

}

 src/main/java/cc/mrbird/febs/system/entity/Menu.java

@@ -1,12 +1,11 @@
package cc.mrbird.febs.system.entity;

import cc.mrbird.febs.common.converter.TimeConverter;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.wuwenze.poi.annotation.Excel;
import com.wuwenze.poi.annotation.ExcelField;
import lombok.Data;

import javax.validation.constraints.NotBlank;
@@ -19,7 +18,6 @@
 */
@Data
@TableName("t_menu")
@Excel("菜单信息表")
public class Menu implements Serializable {

    private static final long serialVersionUID = 8571011372410167901L;
@@ -53,7 +51,7 @@
    @TableField("MENU_NAME")
    @NotBlank(message = "{required}")
    @Size(max = 10, message = "{noMoreThan}")
    @ExcelField(value = "名称")
    @ExcelProperty(value = "名称")
    private String menuName;

    /**
@@ -61,7 +59,7 @@
     */
    @TableField("URL")
    @Size(max = 50, message = "{noMoreThan}")
    @ExcelField(value = "URL")
    @ExcelProperty(value = "URL")
    private String url;

    /**
@@ -69,7 +67,7 @@
     */
    @TableField("PERMS")
    @Size(max = 50, message = "{noMoreThan}")
    @ExcelField(value = "权限")
    @ExcelProperty(value = "权限")
    private String perms;

    /**
@@ -77,7 +75,7 @@
     */
    @TableField("ICON")
    @Size(max = 50, message = "{noMoreThan}")
    @ExcelField(value = "图标")
    @ExcelProperty(value = "图标")
    private String icon;

    /**
@@ -85,7 +83,7 @@
     */
    @TableField("TYPE")
    @NotBlank(message = "{required}")
    @ExcelField(value = "类型", writeConverterExp = "0=按钮,1=菜单")
    @ExcelProperty(value = "类型")
    private String type;

    /**
@@ -98,14 +96,14 @@
     * 创建时间
     */
    @TableField("CREATE_TIME")
    @ExcelField(value = "创建时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "创建时间")
    private Date createTime;

    /**
     * 修改时间
     */
    @TableField("MODIFY_TIME")
    @ExcelField(value = "修改时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "修改时间")
    private Date modifyTime;



 src/main/java/cc/mrbird/febs/system/entity/Role.java

@@ -1,12 +1,11 @@
package cc.mrbird.febs.system.entity;

import cc.mrbird.febs.common.converter.TimeConverter;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.wuwenze.poi.annotation.Excel;
import com.wuwenze.poi.annotation.ExcelField;
import lombok.Data;

import javax.validation.constraints.NotBlank;
@@ -19,7 +18,6 @@
 */
@Data
@TableName("t_role")
@Excel("角色信息表")
public class Role implements Serializable {

    private static final long serialVersionUID = -4493960686192269860L;
@@ -33,7 +31,7 @@
     * 角色名称
     */
    @TableField("ROLE_NAME")
    @ExcelField(value = "角色名称")
    @ExcelProperty(value = "角色名称")
    @NotBlank(message = "{required}")
    @Size(max = 10, message = "{noMoreThan}")
    private String roleName;
@@ -42,7 +40,7 @@
     * 角色描述
     */
    @TableField("REMARK")
    @ExcelField(value = "角色描述")
    @ExcelProperty(value = "角色描述")
    @Size(max = 50, message = "{noMoreThan}")
    private String remark;

@@ -50,14 +48,14 @@
     * 创建时间
     */
    @TableField("CREATE_TIME")
    @ExcelField(value = "创建时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "创建时间")
    private Date createTime;

    /**
     * 修改时间
     */
    @TableField("MODIFY_TIME")
    @ExcelField(value = "修改时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "修改时间")
    private Date modifyTime;

    /**

 src/main/java/cc/mrbird/febs/system/entity/User.java

@@ -2,15 +2,14 @@

import cc.mrbird.febs.common.annotation.Desensitization;
import cc.mrbird.febs.common.annotation.IsMobile;
import cc.mrbird.febs.common.converter.TimeConverter;

import cc.mrbird.febs.common.entity.DesensitizationType;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.wuwenze.poi.annotation.Excel;
import com.wuwenze.poi.annotation.ExcelField;
import lombok.Data;

import javax.validation.constraints.Email;
@@ -24,7 +23,6 @@
 */
@Data
@TableName("t_user")
@Excel("用户信息表")
public class User implements Serializable {

    private static final long serialVersionUID = -4352868070794165001L;
@@ -86,7 +84,7 @@
     */
    @TableField("USERNAME")
    @Size(min = 4, max = 10, message = "{range}")
    @ExcelField(value = "用户名")
    @ExcelProperty(value = "用户名")
    private String username;

    /**
@@ -107,7 +105,7 @@
    @TableField("EMAIL")
    @Size(max = 50, message = "{noMoreThan}")
    @Email(message = "{email}")
    @ExcelField(value = "邮箱")
    @ExcelProperty(value = "邮箱")
    private String email;

    /**
@@ -115,7 +113,7 @@
     */
    @TableField("MOBILE")
    @IsMobile(message = "{mobile}")
    @ExcelField(value = "联系电话")
    @ExcelProperty(value = "联系电话")
    @Desensitization(type = DesensitizationType.PHONE)
    private String mobile;

@@ -124,28 +122,28 @@
     */
    @TableField("STATUS")
    @NotBlank(message = "{required}")
    @ExcelField(value = "状态", writeConverterExp = "0=锁定,1=有效")
    @ExcelProperty(value = "状态")
    private String status;

    /**
     * 创建时间
     */
    @TableField("CREATE_TIME")
    @ExcelField(value = "创建时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "创建时间")
    private Date createTime;

    /**
     * 修改时间
     */
    @TableField("MODIFY_TIME")
    @ExcelField(value = "修改时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "修改时间")
    private Date modifyTime;

    /**
     * 最近访问时间
     */
    @TableField("LAST_LOGIN_TIME")
    @ExcelField(value = "最近访问时间", writeConverter = TimeConverter.class)
    @ExcelProperty(value = "最近访问时间")
    @JsonFormat(pattern = "yyyy年MM月dd日 HH时mm分ss秒", timezone = "GMT+8")
    private Date lastLoginTime;

@@ -154,7 +152,7 @@
     */
    @TableField("SSEX")
    @NotBlank(message = "{required}")
    @ExcelField(value = "性别", writeConverterExp = "0=男,1=女,2=保密")
    @ExcelProperty(value = "性别")
    private String sex;

    /**
@@ -180,13 +178,13 @@
     */
    @TableField("DESCRIPTION")
    @Size(max = 100, message = "{noMoreThan}")
    @ExcelField(value = "个人描述")
    @ExcelProperty(value = "个人描述")
    private String description;

    /**
     * 部门名称
     */
    @ExcelField(value = "部门")
    @ExcelProperty(value = "部门")
    @TableField(exist = false)
    private String deptName;

@@ -201,7 +199,7 @@
    @TableField(exist = false)
    private String roleId;

    @ExcelField(value = "角色")
    @ExcelProperty(value = "角色")
    @TableField(exist = false)
    private String roleName;


 src/main/resources/generator/templates/controller.ftl

@@ -8,7 +8,7 @@
import cc.mrbird.febs.common.entity.QueryRequest;
import ${basePackage}.${entityPackage}.${className};
import ${basePackage}.${servicePackage}.I${className}Service;
import com.wuwenze.poi.ExcelKit;

import lombok.extern.slf4j.Slf4j;
import lombok.RequiredArgsConstructor;

@@ -91,6 +91,6 @@
    @RequiresPermissions("${className?uncap_first}:export")
    public void export(QueryRequest queryRequest, ${className} ${className?uncap_first}, HttpServletResponse response) {
        List<${className}> ${className?uncap_first}s = this.${className?uncap_first}Service.find${className}s(queryRequest, ${className?uncap_first}).getRecords();
        ExcelKit.$Export(${className}.class, response).downXlsx(${className?uncap_first}s, false);
        // ExcelKit.$Export(${className}.class, response).downXlsx(${className?uncap_first}s, false);
    }
}

 src/main/resources/mapper/modules/MallMemberMapper.xml

@@ -496,10 +496,10 @@
        select IFNULL(count(1),0)
        from mall_member x where x.referrer_id=#{inviteId}
        and x.level != 'ZERO_LEVEL'
        <if test="accountLevel == 1  or accountLevel == 2">
        <if test="accountLevel == 1">
            and x.account_level = #{accountLevel}
        </if>
        <if test="accountLevel == 3">
        <if test="accountLevel == 3 or accountLevel == 2">
            and x.account_level in (2, 3)
        </if>
    </select>

 src/main/resources/mapper/modules/MallMoneyFlowMapper.xml

@@ -241,4 +241,34 @@
        ORDER BY
            a.created_time ASC
    </select>

    <select id="selectChargeListForExcel" resultType="cc.mrbird.febs.mall.vo.ChargeListExportVo">
        select
            b.name memberName
            ,b.phone phone
            ,a.amount
            ,c.CREATED_TIME createTime
            ,c.bank_name bankName
            ,c.bank_no bankNo
            ,c.name cardName
            ,c.subbranch_name subbranchName
            ,c.phone bankPhone
        from mall_member_withdraw a
            inner join mall_member b on a.member_id=b.id
            inner join mall_member_bank c on b.id=c.member_id
        <where>
            <if test="record != null" >
                <if test="record.name!=null and record.name!=''">
                    and b.name like concat('%',  #{record.name},'%')
                </if>
                <if test="record.phone!=null and record.phone!=''">
                    and b.phone like concat('%',  #{record.phone},'%')
                </if>
                <if test="record.status!=null and record.status!=''">
                    and a.status = #{record.status}
                </if>
            </if>
        </where>
        order by a.created_time desc
    </select>
</mapper>

 src/main/resources/templates/febs/views/modules/mallMember/chargeFlowList.html

@@ -39,6 +39,9 @@
                                <div class="layui-btn layui-btn-sm layui-btn-primary febs-button-green-plain table-action" id="reset">
                                    <i class="layui-icon">&#xe79b;</i>
                                </div>
                                <div class="layui-btn layui-btn-sm layui-btn-primary febs-button-green-plain table-action" id="exportExcel">
                                    导出
                                </div>
                            </div>
                        </div>
                    </form>
@@ -68,6 +71,7 @@
            $view = $('#febs-money-flow-charge'),
            $query = $view.find('#query'),
            $reset = $view.find('#reset'),
            $exportExcel = $view.find('#exportExcel'),
            $searchForm = $view.find('form'),
            sortObject = {field: 'phone', type: null},
            tableIns;
@@ -127,6 +131,10 @@
            tableIns.reload({where: getQueryParams(), page: {curr: currPageGoods}, initSort: sortObject});
        });

        $exportExcel.on('click', function() {
            febs.download(ctx + 'admin/mallMember/chargeList/excel', getQueryParams(), '提现列表.xlsx');
        });

        function initTable() {
            tableIns = febs.table.init({
                elem: $view.find('table'),