“This is the 11th day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021”

The introduction

The function of exporting Excel form made by my colleague is done by using Easypoi component. After exporting 💰 fields, text format is displayed in Excel form. The business side needs to manually change the numeric format each time. Add type=10 attribute to @excel annotation, which is invalid in actual measurement, so use other forms to implement, share. Big guy not gush, gush is hydrology, in order to get a ☕️ machine.

Business source

Entity class section

@Builder
@Data
@NoArgsConstructor
@AllArgsConstructor(access = AccessLevel.PRIVATE)
public class ItemExport extends IntegerDomain {

    /** * due date */
    @excel (name = "due date ", orderNum = "0", width = 25)
    private LocalDate payableDate;

    /** * Payment slip no. */
    @excel (name = "order number ", orderNum = "1", width = 25)
    private String statementNo;

    /** * Amount of payment */
    @excel (name = "payment amount ", orderNum = "2", width = 25, type = 10)
    private BigDecimal itemAmountCny;

    /** * Payment amount (yuan) */
    @excel (name = "payment amount ", orderNum = "3", width = 25, type = 10)
    private BigDecimal itemAmount;

    /** ** currency */
    @excel (name = "money ", orderNum = "4", width = 25)
    private String currency;

    /** * Details */
    @excel (name = "detail status ", orderNum = "5", width = 25)
    private String itemStatus;

    /**
     * 财务类型
     */
    @excel (name = "financial type ", orderNum = "6", width = 25)
    private String financeType;

    /** * Business type */
    @excel (name = "business type ", orderNum = "7", width = 25)
    private String businessType;

    /** * Actual mode of payment */
    @excel (name = "actual payment method ", orderNum = "8", width = 25)
    private String bizPayMode;

    /** * import payment method */
    @excel (name = "import payment method ", orderNum = "9", width = 25)
    private String importPayMode;

    /** * Actual mode of payment */
    @excel (name = "money payment method ", orderNum = "10", width = 25)
    private String financePayMode;

    /** * Actual payment time */
    @excel (name = "actual payment time ", orderNum = "11", width = 25)
    private String payCompleteDate;

    /** * Payment items */
    @excel (name = "payment item ", orderNum = "12", width = 25)
    private String itemTitle;

    /** * Supplier */
    @excel (name = "supplier ", orderNum = "13", width = 25)
    private String supplierName;

    /** * Payment subject */
    @excel (name = "paymaster ", orderNum = "14", width = 25)
    private String companyName;

    /** * business */
    @excel (name = "business ", orderNum = "15", width = 25)
    private String businessName;

    /** * Category of expenditure */
    @excel (name = "spending category ", orderNum = "16", width = 25)
    private String expendType;


    /** * create time */
    @excel (name = "create time ", orderNum = "17", width = 25)
    private String createdAt;

    /** * update time */
    @excel (name = "update time ", orderNum = "18", width = 25)
    private String modifiedAt;

    public static List<ItemExport> convertDataList(List<Item> items) {
        List<ItemExport> itemExports = new ArrayList<>();
        if (CollectionUtils.isEmpty(items)) {
            return itemExports;
        }
        items.forEach(
                item -> {
                    String expendTypeEnum = ExcelUtil.convertEnumMessage("ExpendTypeEnum", Integer.valueOf(item.getExpendType()));
                    ItemExport export = ItemExport.builder()
                            .itemStatus(ExcelUtil.convertEnumMessage("ItemStatusEnum", item.getItemStatus()))
                            .bizPayMode(ExcelUtil.convertEnumMessage("BizPayModeEnum", item.getBizPayMode()))
                            .importPayMode(ExcelUtil.convertEnumMessage("BizPayModeEnum", item.getImportPayMode()))
                            .financePayMode(ExcelUtil.convertEnumMessage("PayModeEnum", item.getFinancePayMode()))
                            .createdAt(item.getCreatedAt().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")))
                            .modifiedAt(item.getModifiedAt().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")))
                            .payCompleteDate(item.getPayCompleteDate() == null ? "" : item.getPayCompleteDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))) .build(); item.setExpendType(expendTypeEnum); BeanUtils.copyProperties(item, export); itemExports.add(export); });returnitemExports; }}Copy the code

Export some code:

public void exportPlanItem(HttpServletResponse response, String no){
    Long currentTimeMillis = System.currentTimeMillis();
    String fileName = currentTimeMillis.toString() + ".xlsx";
    try {
        Workbook workbook = null;
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName("sheet1");
        if (StringUtils.isEmpty(no)){
            throw new BizRuntimeException(ErrorCode.COMMON_PARAM_ERROR, "No cannot be empty");
        }
        Example example = new Example(Item.class);
        example.createCriteria().andEqualTo("no", no);

        List<Item> items = itemService.find(example);
        //
        workbook = ExcelExportUtil.exportBigExcel(exportParams, ItemExport.class, ItemExport.convertDataList(items));
        // End the export
        ExcelExportUtil.closeExportBigExcel();
        / / write
        ExcelUtil.downLoadExcel(fileName, workbook, response);
    } catch (Exception e) {
        log.error("Exporting details failed :", e); }}Copy the code

To solve the process

Export the main elements and dependencies used

Exporting Excel using EasyPOI is a simple one. ExcelExportUtil.exportBigExcel(exportParams, ItemExport.class, ItemExport.convertDataList(items)); The main elements are listed below:

The name of the describe
ExportParams Define the file name, Chinese name, file type, export style, etc.
ExcelExportStatisticStyler Inherited from ExcelExportStylerDefaultImpl class, defined style. Through the params. SetStyle (ExcelExportStatisticStyler. Class); Set to ExportParams. If not specified, the default use ExcelExportStylerDefaultImpl
ItemExport Export the entity of the cell, where you can set the data rules

Since ExportParams can set the style of export, that inherit ExcelExportStylerDefaultImpl class, rewrite the style, it can be derived field is set to the format of the business needs.

Realize the Style

public class ExcelExportStatisticStyle extends ExcelExportStylerDefaultImpl {

    private CellStyle numberCellStyle;

    public ExcelExportStatisticStyle(Workbook workbook) {
        super(workbook);
        createNumberCellStyle();
    }

    private void createNumberCellStyle(a) {
        numberCellStyle = workbook.createCellStyle();
        numberCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        numberCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        numberCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("#, # # 0.00"));
        numberCellStyle.setWrapText(true);
    }

    @Override
    public CellStyle getStyles(boolean noneStyler, ExcelExportEntity entity) {
        if(entity ! =null
                && 10==entity.getType()) {
            return numberCellStyle;
        }
        return super.getStyles(noneStyler, entity); }}Copy the code

Set the Style

ExportParams exportParams = new ExportParams();
exportParams.setSheetName("sheet1");
exportParams.setStyle(ExcelExportStatisticStyle.class);
Copy the code

Comparison of derived results

The format of the cells in the 💰 field for the table exported before setting the Style is:

After setting, the exported table field cell format is:

Problem solved, submit the code.

conclusion

  • If you set the text center, you may get an error. Select different code implementation according to version:

To achieve 1:

numberCellStyle.setAlignment(HorizontalAlignment.CENTER);
numberCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Copy the code

Realization of 2:

numberCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); numberCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Copy the code
  • List of formats supported by EasyPOI
static { List<String> m = new ArrayList(); putFormat(m, 0, "General"); putFormat(m, 1, "0"); PutFormat (m, 2, "0.00"); putFormat(m, 3, "#,##0"); PutFormat (m, 4, "# # # 0.00"); putFormat(m, 5, ""$"#,##0_); (" $" #, # # 0) "); putFormat(m, 6, ""$"#,##0_); [Red]("$"#,##0)"); PutFormat (m, 7, "" $" #, # # 0.00 _); (" $" #, # # 0.00) "); PutFormat (m, 8, "" $" #, # # 0.00 _); [Red] (" $" #, # # 0.00) "); putFormat(m, 9, "0%"); PutFormat (m, 10, "0.00%"); 0.00 e+00 putFormat (m, 11, ""); putFormat(m, 12, "# ? /?" ); putFormat(m, 13, "# ?? /??" ); putFormat(m, 14, "m/d/yy"); putFormat(m, 15, "d-mmm-yy"); putFormat(m, 16, "d-mmm"); putFormat(m, 17, "mmm-yy"); putFormat(m, 18, "h:mm AM/PM"); putFormat(m, 19, "h:mm:ss AM/PM"); putFormat(m, 20, "h:mm"); putFormat(m, 21, "h:mm:ss"); putFormat(m, 22, "m/d/yy h:mm"); for(int i = 23; i <= 36; ++i) { putFormat(m, i, "reserved-0x" + Integer.toHexString(i)); } putFormat(m, 37, "#,##0_); (#, # # 0) "); putFormat(m, 38, "#,##0_); [Red](#,##0)"); PutFormat (m, 39, #, # # 0.00 _);" (#, # # 0.00) "); PutFormat (m, 40, "#, # # 0.00 _); [Red] (#, # # 0.00) "); putFormat(m, 41, "_("$"* #,##0_); _ (" $" * (#, # # 0); _ (" $* "-" _ "); _ (@ _) "); putFormat(m, 42, "_(* #,##0_); _ (* (#, # # 0); "-" _ _ (*); _ (@ _) "); PutFormat (m, 43, "_ (0.00 _ * # # #); _ (* (#, # # 0.00); _ (* "-"?? _); _ (@ _) "); PutFormat (m, 44, "_ (" $" * #, # # 0.00 _); _ (" $" * (#, # # 0.00); _ (" $" * "-"?? _); _ (@ _) "); putFormat(m, 45, "mm:ss"); putFormat(m, 46, "[h]:mm:ss"); putFormat(m, 47, "mm:ss.0"); PutFormat (m, 48, "# # 0.0 e+0"); putFormat(m, 49, "@"); String[] ss = new String[m.size()]; m.toArray(ss); _formats = ss; }Copy the code

Can be according to the requirements in numberCellStyle. SetDataFormat ((short) BuiltinFormats. GetBuiltinFormat (” # # # 0.00 “)); To set the required export format.