freemarker Exports Excel with Complex Styles

Code address:

gitee

https://gitee.com/suveng/demo/tree/master/chapter.002

The code is stored in the chapter.002 directory under demo. Each module is a separate springboot application, which can run the application directly.

Environmental Science

  • springboot 2.1.2
  • Freemarker 2.3.28
  • JDK1.8

step

1. Find the corresponding Excel template

I found a website on the Internet and downloaded an Excel template. address

The downloaded file is Inventory Statement 2018

2.Excel template export to xml format

Export it to xml format; save it as a direct file

Delete redundant data and fill in template variables, which need to conform to freemarker's variable rules.

Specific contents can be referred to. file

3. Replace the freemarker variable

Key modifications:

            <#list products as product>
                <Row>
                    <Cell>
                        <Data ss:Type="String">${product.name!}</Data>
                    </Cell>
                    <Cell>
                        <Data ss:Type="String">${product.number!}</Data>
                    </Cell>
                    <Cell>
                        <Data ss:Type="String">${product.type!}</Data>
                    </Cell>
                    <Cell>
                        <Data ss:Type="String">${product.unit!}</Data>
                    </Cell>
                    <Cell>
                        <Data ss:Type="String">${product.left!}</Data>
                    </Cell>
                    <Cell>
                        <Data ss:Type="String">${product.monthNumber!}</Data>
                    </Cell>
                    <Cell>
                        <Data ss:Type="String">${product.in!}</Data>
                    </Cell>
                    <Cell>
                        <Data ss:Type="String">${product.out!}</Data>
                    </Cell>
                    <Cell ss:StyleID="s54">
                        <Data ss:Type="String">${product.date?string('yyyy/MM/dd')}</Data>
                    </Cell>
                </Row>
            </#list>

I can get the documents myself and compare them.

Specific freemarker grammar, for reference link

4. Coding, variable substitution

Here I use my own scaffolding, which is also a fast-start server program built by spring boot. If you are interested, you can go and see it. link

Write a web interface here: export template Excel

The data here is self-simulated, randomly generated meaningless data, using random Util of the hutool Toolkit

AppController.java

@Controller
public class AppController {
    @Autowired
    private Configuration configuration;

    @RequestMapping("/export")
    public void export(HttpServletResponse response) throws Exception {
        //Self-encapsulated Number Data Entities
        ArrayList<Product> products = new ArrayList<>();

        //Constructing data
        for (int i = 0; i < 100; i++) {
            Product e = new Product();
            e.setName(RandomUtil.randomString(5));
            e.setNumber(RandomUtil.randomString(2));
            e.setOut(RandomUtil.randomString(2));
            e.setIn(RandomUtil.randomString(2));
            e.setType(RandomUtil.randomString(5));
            e.setUnit(RandomUtil.randomString(4));
            e.setMonthNumber(RandomUtil.randomString(1));
            e.setDate(new Date());
            products.add(e);
        }
        HashMap<String, Object> map = new HashMap<>();
        map.put("products", products);

        //Construct output stream
        Template template = configuration.getTemplate("2018 An inventory statement.xml", "UTF-8");
        String fileName = "/data/files/" + DateUtil.now() + ".xlsx";
        File file = new File(fileName);
        FileWriter out = new FileWriter(fileName);
        //Variable substitution
        template.process(map, out);

        //Output the file to response and return it to the client
        FileInputStream in = new FileInputStream(file);
        byte[] buffer = new byte[in.available()];
        in.read(buffer);
        in.close();
        response.reset();
        response.addHeader("Content-Disposition", "attachment;filename=file.xlsx");
        ServletOutputStream outputStream = response.getOutputStream();
        response.setContentType("application/octet-stream");
        outputStream.write(buffer);
        outputStream.flush();
        outputStream.close();
    }
}

5. Result presentation

Existing problems

  1. Variable substitution, CPU and memory consumption have not been tested, which component is better than POI? There is a question here.

This is just an Excel data export for complex style, not suitable for a large number of data export. Both the hutool toolkit and easyExcel have optimized the Excel export for a large number of data, so you can view the corresponding documents if necessary.

Tags: Java Excel FreeMarker xml SpringBoot

Posted on Sun, 25 Aug 2019 05:52:20 -0700 by jimmyhumbled