With an Easyexcel export Excel actual combat.

Repeat the requirement:

Suppose there is a batch of commercial housing, we need to export it to Excel, maintain the price and format in batches, and then import it back to the system.

id	formats	The name of the room	area
1	shops	Vanke City Lights – Phase I – One Building -101	50
2	shops	Vanke City Lights – Phase I – One Building -102	50
3	shops	Vanke City Lights – Phase I – One Building -103	50
4	residential	Vanke City Lights – Phase I – One Building -201	24
5	residential	Vanke City Lights – Phase I – One Building -202	35
6	residential	Vanke City Lights – Phase I – One Building -203	31

Where, the format is enumerated, and the specific values are as follows:
@AllArgsConstructor
@Getter
public enum HouseTypeEnum {

    RESIDENTIAL("residential"."Home"),
    PARKING("parking"."Parking"),
    SHOP("shop"."Shop"),;private final String code;

    private final String desc;

}
Copy the code
After the total price is input, the unit price is calculated according to the area. After entering the unit price, calculate the total price according to the area. Total price = unit price * area

In the last article, we achieved most of the requirements, but there is one last little tail left: automatic calculation of a unit price from a unit price, or automatic calculation of a unit price from a total price.

The formula

To implement this function in Excel, we don’t need to use formulas, but we can use annotations +Handler to solve the problem

Start by defining a formula enumeration class to carry relevant information

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface ExcelFormula {

    ${GetCellAddress("id",${RowNo})}} to obtain the cell address. The available parameters are * RowNo current row */
    String value(a);

}
Copy the code

Here I have made some formatting definitions for the formula template, adding two prefabricated placeholders

Use the format $env to define environment variables, such as ${rowNo} to identify the current line number

Use the format ${funcName(*args)} to define method calls (note that this is not an Excel function, but an additional method I’ve defined myself), such as ${getCellAddress(”

“,

)} to get the cell address of the corresponding field

Here’s the WriteHandler that parses the formula annotations:

/** * Prerequisites: * 1. Class mode **@author jingwen
 */
@Slf4j
public class FormulaCellWriteHandler extends AbstractCellWriteHandler implements SheetWriteHandler {

    private static final String ENV_ROW_NO = "rowNo";
    private static final Pattern PATTERN_ENV = Pattern.compile("\\$\\{([a-zA-Z]\\w+)}");
    private static final Pattern PATTERN_FUNC = Pattern.compile("\\$\\{([a-zA-Z]\\w+)\\((((('[a-zA-Z]\\w+')|\\d+),?) *) \ \}");
    private static final Pattern PATTERN_ARGS = Pattern.compile("('([a-zA-Z]\\w*)')|(\\d+)");

    /** * easyExcel metadata */
    private Map<String, ExcelContentProperty> contentPropertyMap = null;
    /** * formula map * since no deletion is performed and even updates are overwritten with the same data, you can use HashMap */
    private final Map<String, ExcelFormula> formulaMap = new HashMap<>();

    private final Function<String[], String> getCellAddressFunc = (args) -> getCellAddress(args[0], Integer.parseInt(args[1]));

    private final Map<String, Function<String[], String>> functionMap = ImmutableMap.of(
            "getCellAddress", getCellAddressFunc
    );

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // Skip overinformation, if not class mode, skip too
        if(! isHead && head ! =null&& head.getFieldName() ! =null) {
            ExcelFormula excelFormula;
            if (formulaMap.containsKey(head.getFieldName())) {
                excelFormula = formulaMap.get(head.getFieldName());
            } else {
                ExcelContentProperty excelContentProperty = contentPropertyMap.get(head.getFieldName());
                excelFormula = excelContentProperty.getField().getAnnotation(ExcelFormula.class);
                // Empty also put, so that next time containsKey returns true
                formulaMap.put(head.getFieldName(), excelFormula);
            }
            if (excelFormula == null) {
                return;
            }
            String formulaTemplate = excelFormula.value();
            // Parse formulas and environment variablesString formula= parseFunctionAndExecute(formulaTemplate, cell); cell.setCellFormula(formula); }}private String parseFunctionAndExecute(String formulaTemplate, Cell cell) {

        // Parse the environment variables first
        formulaTemplate = setEnvProperties(formulaTemplate, cell);
        log.info("============= start compiling formula template: {}=============", formulaTemplate);
        Matcher matcher = PATTERN_FUNC.matcher(formulaTemplate);
        StringBuilder stringBuffer = new StringBuilder();
        while (matcher.find()) {
            String methodName = matcher.group(1);
            log.info("-----------methodName:{}-----------", methodName);
            Matcher argsMatcher = PATTERN_ARGS.matcher(matcher.group(2));
            // There are not so many parameters
            List<String> args = new ArrayList<>(5);
            while (argsMatcher.find()) {
                // Can be a string or a number
                String strArgument = argsMatcher.group(2);
                String digitArgument = argsMatcher.group(3); String actualArgument = strArgument ! =null ? strArgument : digitArgument;
                log.info("arg[{}]: {}", args.size(), actualArgument);
                args.add(actualArgument);
            }
            log.info("-----------method resolved -----------");
            Function<String[], String> function = functionMap.get(methodName);
            if (function == null) {
                throw new IllegalArgumentException("No corresponding method can be found :" + methodName);
            }
            String result = function.apply(args.toArray(new String[0]));
            log.info("execute method, result: {}", result);
            matcher.appendReplacement(stringBuffer, result);
        }
        matcher.appendTail(stringBuffer);
        String formula = stringBuffer.toString();
        log.info("============= formula template parsed: {}=============", formula);
        return formula;
    }

    private String setEnvProperties(String formulaTemplate, Cell cell) {

        CellAddress cellAddress = cell.getAddress();
        int currentRow = cellAddress.getRow() + 1;
        Matcher matcher = PATTERN_ENV.matcher(formulaTemplate);
        StringBuilder stringBuffer = new StringBuilder();
        while (matcher.find()) {
            String envName = matcher.group(1);
            if (ENV_ROW_NO.equals(envName)) {
                matcher.appendReplacement(stringBuffer, String.valueOf(currentRow));
            } else {
                throw new IllegalArgumentException("Unrecognized variable");
            }
        }
        matcher.appendTail(stringBuffer);
        return stringBuffer.toString();
    }


    public String getCellAddress(String fieldName, int rowIndex) {
        ExcelContentProperty excelContentProperty = contentPropertyMap.get(fieldName);
        if (excelContentProperty == null) {
            throw new IllegalArgumentException("Invalid field name :" + fieldName);
        }
        int columnIndex = excelContentProperty.getHead().getColumnIndex();
        String columnStr = CellReference.convertNumToColString(columnIndex);
        return columnStr + rowIndex;
    }


    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (writeSheetHolder.getClazz() == null) {
            throw new UnsupportedOperationException("Only class mode writes are supported");
        }
        initHeadMap(writeSheetHolder.getExcelWriteHeadProperty().getContentPropertyMap());
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/** * Save EasyExcel model data *@param excelContentPropertyMap 
     */
    private synchronized void initHeadMap(Map<Integer, ExcelContentProperty> excelContentPropertyMap) {
        if (this.contentPropertyMap == null) {
            this.contentPropertyMap = excelContentPropertyMap.values() .stream() .collect(Collectors.toMap( e -> e.getHead().getFieldName(), Function.identity() )); }}}Copy the code

Here I did a few things:

Defines a method to obtain the address of a cell, and encapsulates it asFunction<String[],String>Register to method list, easy to call
When creating a worksheet, save EasyExcel’s model information for later use
Use the Map cache formula to annotate the information to avoid repeated calls to reflection. In this case, I used HashMap, which can also cache null values to avoid breakdown
Use regular expressions to resolve environment variables and methods in previously defined formula templates

The environment variable

For ${env} in the form of environment variable, we use regular $\ \ {([a zA – Z] \ w +)} to parse, you can see only a capture group, is straightforward

methods

The analysis of the method is divided into two steps

Parse method body, get method name and parameter block, use re\$\{([a-zA-Z]\w+)\((((('[a-zA-Z]\w+')|\d+),?) *) \]}, this is complicated, mainly because of the need to match properly formatted parameter blocks at the same time

By the parameter block parse each parameter, using regular (‘ ([a zA – Z] \ w *) ‘) | (\ d +), capture groups as shown in figure

To try this out, modify the model class and add a formula annotation:

    /** * Total price */
    @excelProperty (" Total price (YUAN)")
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    @ExcelFormula("=${getCellAddress('unitPrice',${rowNo})} * ${getCellAddress('area',${rowNo})}")
    private BigDecimal totalPrice;
Copy the code

============= Start compiling formula template: =${getCellAddress('unitPrice',2)} * ${getCellAddress('area',2)}============= -----------methodName:getCellAddress----------- arg[0]: unitPrice arg[1]: 2 -- -- -- -- -- -- -- -- -- -- - finished analytical method -- -- -- -- -- -- -- -- -- -- -- the execute method, result: D2 -- -- -- -- -- -- -- -- -- -- -- methodName: getCellAddress -- -- -- -- -- -- -- -- -- -- - (arg [0] : Area arg [1] : 2 -- -- -- -- -- -- -- -- -- -- - finished analytical method -- -- -- -- -- -- -- -- -- -- -- the execute method, result: C2 = = = = = = = = = = = = = formula template parsing out: D2 * C2 = = = = = = = = = = = = = =Copy the code

Let’s take a look at the excel export

You can see there’s a formula!

Simplified formula

In fact, all we need in this article is to get the values of other cells in the same row and calculate them. We don’t need excel functions, so we can simplify the formula template by using

< operations >.

format, let’s modify the code.

Adjusted formula annotation to add switch of simplified formula

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface ExcelFormula {

    ${GetCellAddress("id",${RowNo})}} to obtain the cell address. The available parameters are * RowNo current row */
    String value(a);

    FormulaType type(a) default FormulaType.SIMPLE;


    enum FormulaType {
        /** * simple mode, does not support calling Excel functions, only supports the addition, subtraction, multiplication and division parentheses * but can directly use the variable name to point to a field in the line */
        SIMPLE,
        /** * Complex mode, support excel function calls, support the introduction of custom environment variables */
        COMPLEX,
    }

}
Copy the code

inFormulaCellWriteHandlerAdded support for simplified versions of formulas in

@Slf4j
public class FormulaCellWriteHandler extends AbstractCellWriteHandler implements SheetWriteHandler {

    private static final String ENV_ROW_NO = "rowNo";
    private static final Pattern PATTERN_ENV = Pattern.compile("\\$\\{([a-zA-Z]\\w+)}");
    private static final Pattern PATTERN_FUNC_COMPLEX = Pattern.compile("\\$\\{([a-zA-Z]\\w+)\\((((('[a-zA-Z]\\w+')|\\d+),?) *) \ \}");
    private static final Pattern PATTERN_FUNC_SIMPLE = Pattern.compile("([a-zA-Z]\\w*)");
    private static final Pattern PATTERN_ARGS = Pattern.compile("('([a-zA-Z]\\w*)')|(\\d+)");

    /** * easyExcel metadata */
    private Map<String, ExcelContentProperty> contentPropertyMap = null;
    /** * formula map * since no deletion is performed and even updates are overwritten with the same data, you can use HashMap */
    private final Map<String, ExcelFormula> formulaMap = new HashMap<>();

    private final Function<String[], String> getCellAddressFunc = (args) -> getCellAddress(args[0], Integer.parseInt(args[1]));

    private final Map<String, Function<String[], String>> functionMap = ImmutableMap.of(
            "getCellAddress", getCellAddressFunc
    );

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // Skip overinformation, if not class mode, skip too
        if(! isHead && head ! =null&& head.getFieldName() ! =null) {
            ExcelFormula excelFormula;
            if (formulaMap.containsKey(head.getFieldName())) {
                excelFormula = formulaMap.get(head.getFieldName());
            } else {
                ExcelContentProperty excelContentProperty = contentPropertyMap.get(head.getFieldName());
                excelFormula = excelContentProperty.getField().getAnnotation(ExcelFormula.class);
                // Empty also put, so that next time containsKey returns true
                formulaMap.put(head.getFieldName(), excelFormula);
            }
            if (excelFormula == null) {
                return;
            }
            String formulaTemplate = excelFormula.value();
            // Replace the environment variable
            String formula;
            if (excelFormula.type() == ExcelFormula.FormulaType.COMPLEX) {
                formula = parseComplexFunctionAndExecute(formulaTemplate, cell);
            } else{ formula = parseSimpleFunctionAndExecute(formulaTemplate, cell); } cell.setCellFormula(formula); }}private String parseSimpleFunctionAndExecute(String formulaTemplate, Cell cell) {
        log.info("============= start compiling formula template: {}=============", formulaTemplate);
        StringBuilder stringBuffer = new StringBuilder();
        Matcher matcher = PATTERN_FUNC_SIMPLE.matcher(formulaTemplate);
        int currentRow = cell.getAddress().getRow() + 1;
        while (matcher.find()) {
            String fieldName = matcher.group(1);
            String fieldCellAddress = getCellAddress(fieldName, currentRow);
            log.info("-----------fieldName:{},fieldCellAddress:{}-----------", fieldName, fieldCellAddress);
            matcher.appendReplacement(stringBuffer, fieldCellAddress);
        }
        matcher.appendTail(stringBuffer);
        String formula = stringBuffer.toString();
        log.info("============= formula template parsed: {}=============", formula);
        return formula;
    }


    private String parseComplexFunctionAndExecute(String formulaTemplate, Cell cell) {

        formulaTemplate = setEnvProperties(formulaTemplate, cell);
        log.info("============= start compiling formula template: {}=============", formulaTemplate);
        Matcher matcher = PATTERN_FUNC_COMPLEX.matcher(formulaTemplate);
        StringBuilder stringBuffer = new StringBuilder();
        while (matcher.find()) {
            String methodName = matcher.group(1);
            log.info("-----------methodName:{}-----------", methodName);
            Matcher argsMatcher = PATTERN_ARGS.matcher(matcher.group(2));
            // There are not so many parameters
            List<String> args = new ArrayList<>(5);
            while (argsMatcher.find()) {
                // Can be a string or a number
                String strArgument = argsMatcher.group(2);
                String digitArgument = argsMatcher.group(3); String actualArgument = strArgument ! =null ? strArgument : digitArgument;
                log.info("arg[{}]: {}", args.size(), actualArgument);
                args.add(actualArgument);
            }
            log.info("-----------method resolved -----------");
            Function<String[], String> function = functionMap.get(methodName);
            if (function == null) {
                throw new IllegalArgumentException("No corresponding method can be found :" + methodName);
            }
            String result = function.apply(args.toArray(new String[0]));
            log.info("execute method, result: {}", result);
            matcher.appendReplacement(stringBuffer, result);
        }
        matcher.appendTail(stringBuffer);
        String formula = stringBuffer.toString();
        log.info("============= formula template parsed: {}=============", formula);
        return formula;
    }

    private String setEnvProperties(String formulaTemplate, Cell cell) {

        CellAddress cellAddress = cell.getAddress();
        int currentRow = cellAddress.getRow() + 1;
        Matcher matcher = PATTERN_ENV.matcher(formulaTemplate);
        StringBuilder stringBuffer = new StringBuilder();
        while (matcher.find()) {
            String envName = matcher.group(1);
            if (ENV_ROW_NO.equals(envName)) {
                matcher.appendReplacement(stringBuffer, String.valueOf(currentRow));
            } else {
                throw new IllegalArgumentException("Unrecognized variable");
            }
        }
        matcher.appendTail(stringBuffer);
        return stringBuffer.toString();
    }


    public String getCellAddress(String fieldName, int rowIndex) {
        ExcelContentProperty excelContentProperty = contentPropertyMap.get(fieldName);
        if (excelContentProperty == null) {
            throw new IllegalArgumentException("Invalid field name :" + fieldName);
        }
        int columnIndex = excelContentProperty.getHead().getColumnIndex();
        String columnStr = CellReference.convertNumToColString(columnIndex);
        return columnStr + rowIndex;
    }


    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (writeSheetHolder.getClazz() == null) {
            throw new UnsupportedOperationException("Only class mode writes are supported");
        }
        initHeadMap(writeSheetHolder.getExcelWriteHeadProperty().getContentPropertyMap());
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}private synchronized void initHeadMap(Map<Integer, ExcelContentProperty> excelContentPropertyMap) {
        if (this.contentPropertyMap == null) {
            this.contentPropertyMap = excelContentPropertyMap.values() .stream() .collect(Collectors.toMap( e -> e.getHead().getFieldName(), e -> e )); }}}Copy the code

Here I have added a judgment. If the formula type is a simplified version, the simplified version of the re is used for parsing. The simplified version of the re is :([a-za-z]\w*), which is really simple = =

Modify the formula template used by the model class

    /** * Total price */
    @excelProperty (" Total price (YUAN)")
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    @ExcelFormula("=unitPrice*area")
    private BigDecimal totalPrice;
Copy the code

Export the log output to see the parsing process:

============= Start compiling formula template: =unitPrice*area============= -----------fieldName:unitPrice,fieldCellAddress:D2----------- -- -- -- -- -- -- -- -- -- -- - fieldName: area, fieldCellAddress: C2 -- -- -- -- -- -- -- -- -- -- - = = = = = = = = = = = = = formula template parsing out: D2 * C2 = = = = = = = = = = = = = =Copy the code

Look at the export effect:

It's the same effect as before.Copy the code

conclusion

This EasyExcel export excel is over here, here I did not design to import, because the current import scenarios are relatively simple, the demo on the official website can solve the problem, no code handling. There’s no need to do this if we’re just trying to meet the requirements in this article, but if we write in a general way, we’ll save ourselves some time next time we meet a similar requirement.

The following lists some pits I have encountered in the process of using, hoping to help you.

Hiding columns, which I mentioned in the article,@ExcelPropertyIn thehiddenArguments do not hide columns. To hide a column, use the@ColumnWidth(0)Set the width of the column to 0
EasyExcel parses the model class and resolves the parent and child fields with the same name, so it requires that the parent and child classes do not have annotations@ExcelPropertyField of the same name
@ContentStyleand@HeadStyleAnnotations can be used on a class or field, but note that class and field annotations are not combined. Instead, they take precedence over fields.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

EasyExcel Export Excel practice (continued)

The formula

The environment variable

methods

Simplified formula

conclusion

EasyExcel Export Excel practice (continued)

The formula

The environment variable

methods

Simplified formula

conclusion

Related Posts

NET Core program debugging Windbg(common command)

Openstack Architecture (2)– Keystone components

Take a look at Pig, the Open source project in Spring Cloud