Exporting Customized Excel Files

Background

A team built a national enterprise credit query system through Informat, which needs to provide a function to export enterprise report information sheets externally.
The enterprise report information sheet requires one-to-one basic information such as business code, as well as complex one-to-many relationships such as main members and major equity changes.

Implementation Steps

• Create website and resource hosting modules
• Upload Excel template
• Add a button in the form settings to call automation
• Create call automation
• Generate the final required file by calling a script

/**
 * Construct跨行的集 (Construct跨行的集)
 * @param list
 * @param key
 */
const buildCellList = (list, key) => {
    // Grouping
    const map = new Map();
    list.forEach(item => {
        let children = map.get(item[key]);
        if (!children) {
            children = [];
            map.set(item[key], children);
        }
        children.push(item);
    });
    // Set跨行 (Set跨行)
    map.forEach((children, k) => {
        if (children.length > 1) {
            // Merge跨行 for the first element of each group
            const item = children[0];
            item[key] = informat.excel.createTemplateCell({
                content: item[key],
                colspan: 1,
                rowspan: children.length,
            });
        }
    });
}

// Start execution...

// Get record
const record = automatic.getVar('record');
// Test data
// const record = {"date":new Date(),"legalRepresentative":"Zhang Shuai","unifiedSocialCreditCode":"SZ00000000001","officialWebsite":"https://informat.cn/","address":"Nanshan District, Shenzhen","registeredCapital":"80 billion yuan","phone":"1304900xxxx","companyName":"Shenzhen XXX Co., Ltd.","logo":{"path":"nzt2qnr0kny3p/jnno0wb5vny65/40b141a9a26a4eabaf04148269cbf668.png","thumbnail":"3e1695e23b9f49c4880ce95d0d99a0e5.png","size":2271,"name":"图片1.png","id":"40b141a9a26a4eabaf04148269cbf668.png","md5":"1338ee9bf691a747e7bbb72b5fa94ab1"},"id":"r7hh35ymsm30s","incorporationDate":"2016-06-01"};

// First download the template file of the static module to the current environment
informat.website.download('website','template.xlsx','template.xlsx');

// Get main members based on association ID
const userList = informat.table.queryList('userList', {
    filter:{
        conditionList:[
            {fieldId:'companyRel',opt:'eq',value: record.id}
        ]
    }
});
// Test data
// const userList = [{"gender":"1","shareholdingRatio":"42.14%","companyRel":"r7hh35ymsm30s","name":"Zhang Shuai","companyRel_name":"Shenzhen XXX Co., Ltd.","position":"Actual controller, ultimate beneficiary, major shareholder","id":"owmn1qa6p5fst","ultimateBeneficialShare":"42.15%","individualResume":"Pioneering leader in the low-code industry, in the industry..."},{"gender":"2","shareholdingRatio":"34.9%","companyRel":"r7hh35ymsm30s","name":"Wang Xiaomei","companyRel_name":"Shenzhen XXX Co., Ltd.","position":"Supervisor\t","id":"foxp35iywuef4","ultimateBeneficialShare":"34.9%","individualResume":"Pioneering leader in the low-code industry, in the industry..."}];


// Get enterprise changes based on association ID
let changeList = informat.table.queryList('changeList', {
    filter:{
        conditionList:[
            {fieldId:'companyRel',opt:'eq',value: record.id}
        ]
    },
});
// Test data
// let changeList = [{"date":"2022-11-07","companyRel":"r7hh35ymsm30s","companyRel_name":"Shenzhen XXX Co., Ltd.","afterChange":"2022-10-10","id":"msq11e046za3g","changeProject":"Charter or charter amendment adoption date","beforeChange":"2022-05-24"},{"date":"2022-11-07","companyRel":"r7hh35ymsm30s","companyRel_name":"Shenzhen XXX Co., Ltd.","afterChange":"Zhang Shuai 800.0 (billion yuan) 42.14%\nWang Xiaomei* 600.0 (billion yuan) 38.70%\nA District Longwang Group Co., Ltd. 5.2 (billion yuan) 18.00%","id":"jd67yjxhrni18","changeProject":"Investors (including capital contribution amount, capital contribution method, capital contribution date, investor name, etc.)","beforeChange":"Zhang Shuai 800.0 (billion yuan) 42.14%\nWang Xiaomei* 600.0 (billion yuan) 38.70%\nShenzhen Xiaokalami Enterprise Management Partnership (Limited Partnership) 6.9 million yuan 0.01% [Exit]\nA District Longwang Group Co., Ltd. 5.2 (billion yuan) 18.00%"},{"date":"2022-06-09","companyRel":"r7hh35ymsm30s","companyRel_name":"Shenzhen XXX Co., Ltd.","afterChange":"-","id":"qx0tv0m7bfxd9","changeProject":"Equity and notary certificate","beforeChange":"-"}];

// Get enterprise changes based on association ID
let taxRevenueList = informat.table.queryList('taxRevenueList', {
    filter:{
        conditionList:[
            {fieldId:'companyRel',opt:'eq',value: record.id}
        ]
    },
});
// Test data
// let taxRevenueList = [{"date":1640966400000,"note":"January 2022","amount":10000.0,"taxPeriod":1672502400000,"companyRel":"r7hh35ymsm30s","companyRel_name":"Shenzhen XXX Co., Ltd.","id":"t5vdqirf4fig9"},{"date":1643644800000,"note":"February 2022","amount":20000.0,"taxPeriod":1675180800000,"companyRel":"r7hh35ymsm30s","companyRel_name":"Shenzhen XXX Co., Ltd.","id":"xqkic6tnoimd4"}]


// Sort by date in descending order
// ...
// Group changeList by date and merge cells with the same date
buildCellList(changeList,'date');

const workbook = informat.excel.openWithTemplate('example.xlsx','template.xlsx',{
    companyName: record.companyName,
    date: informat.Date.now(),
    legalRepresentative: record.legalRepresentative,
    registeredCapital: record.registeredCapital,
    unifiedSocialCreditCode: record.unifiedSocialCreditCode,
    officialWebsite: record.officialWebsite,
    incorporationDate: record.incorporationDate,
    address: record.address,
    phone: record.phone,
    // Company logo with column span and row span
    logo: informat.excel.createTemplatePicture({
        type:'storage',
        content:record.logo.path,
        colspan: 2,
        rowspan: 4
    }),
    userList: userList,
    changeList: changeList,
    taxRevenueList: taxRevenueList,
});
// Write to file
workbook.write();

Automation Flow Chart

Click the export button to view the final result

Template Directive Introduction

Templates are a simple way to handle complex Excel files. Complex Excel styles can be edited directly with Excel, perfectly avoiding the pitfalls of writing styles in code. At the same time, the support for directives also improves the effectiveness of templates. Here are the directives supported by EasyPoi and their functions, the most important of which are the various uses of fe.

• Space separation
• Ternary operation {{test ? obj:obj2}} For example: value == '1' (Operators must be separated by spaces, available operators: > < == !=)
• n: Indicates that this cell is of numeric type {{n:}}
• le: Represents length {{le:()}} Used in if/else {{le:() > 8 ? obj1 : obj2}}
• fd: Format time {{fd:(obj;yyyy-MM-dd)}}
• fn: Format number {{fn:(obj;###.00)}}
• fe: Traverse data, create row
• !fe: Traverse data without creating row
• $fe: Insert by moving down, move all rows below the current row down by .size() rows, then insert
• #fe: Traverse horizontally
• v_fe: Traverse values horizontally
• !if: Delete current column {{!if:(test)}}
• Single quotes indicate constant values '' For example '1' will output 1
• &NULL& Space
• ]] Line break for multi-line traversal export
• sum: Statistical data

The writing method used is {{}} to represent expressions, and then values are taken based on the data in the expressions.

For more examples, check easypoi