Using Freemarker to generate Excel tables in Javaweb development

recently, a request was made to export an Excel table for a purchase contract, which is more of a table style. Due to the contract, there are a lot of formatting requirements in this Excel table, such as the sign at the end of the section there are formatting requirements. Here's a scenario that uses Freemarker APIs to generate Excel filesOne, the comparison of the schemeIn response to this demand, I think of 2 scenarios, which are described below                Scenario One: Make a form template for the contract, and then populate the variables with the same type of El expression. The template is then read into memory through the POI-related class, replacing the value of the variable inside, and then generating the downloaded file.                 Scenario Two: First make a contract form template, then convert it to an XML file, and then change it to the Freemarker FTL file. Populate the template with data through the Freemarker API and generate the download file.
        A simple analysis of the above 2 scenarios:Scenario one is more suitable for a scenario where the number of rows in a template does not change, if the head and tail of the contract are fixed, the number of rows in the middle is not fixed, then the scheme is not appropriate.
Scenario Two is more flexible than scheme one, because a lot of tags can be used in freemarker template files, such as (< #List ></#List >); the scenario in which this intermediate number of rows is not fixed is small case. Therefore, this kind of exported Excel has the format request, the use Freemarker to generate Excel is the best implementation.

Second, introduce the implementation process of Freemarker program(1) format Excel template, save as XML file
(2) make an FTL template, copy the contents of the XML file, and replace the variable with freemarker interpolation.(3) for some dynamic rows and columns, the use of Freemarker tags to achieve
(4) The processing in the code, mainly calls the Freemarker label, the template and the data together, generated a file

The following is a Java code in the background

   public void Exportexcel () {try {/** 1. Data is isolated from the database */list<user> uList = Userservice.findall ();/** 2. Encapsulating Data */map<str ing, object> dataMap = new hashmap<string, object> (); list<map<string, string>> userlist = new arraylist<> (Ulist.size ()); for (User u:ulist) {Map<String , string> rowdata = new hashmap<> (), Rowdata.put ("name", U.getname ()), Rowdata.put ("Account", U.getaccount ()); Rowdata.put ("dept", U.getdept ()); Rowdata.put ("Gender", U.isgender ()? "Male": "female"); Rowdata.put ("Email", u.getemail ()); Userlist.add (RowData); Datamap.put ("UserList", userlist);/** 3. Call the Freemarker API to generate Excel */configuration cfg = new Configuration ( CONFIGURATION.VERSION_2_3_22);//Set the location where the template file is loaded cfg.setservletcontextfortemplateloading ( Servletactioncontext.getservletcontext () (), "/FTL");//Gets the template, templates template = Cfg.gettemplate ("EXPORTEXCEL.FTL");// Set response header HttpServletResponse response = Servletactioncontext.getresponse ();//Prevent garbled, Set the next coded response.setcharacterencoding ("Utf-8"); rEsponse.setheader ("Content-disposition", "attachment; Filename=testexcel.xls ");//Generate Exceltemplate.process (DataMap, Response.getwriter ());}  catch (Exception e) {e.printstacktrace ();}}

<textarea spellcheck="false" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none" tabindex="0" readonly=""> </textarea>1

 Public void Exportexcel (){

2

        Try

3

            /** 1. Isolate data from Database */

4

            List < User > uList = UserService. FindAll ();

5

6

            /** 2. Package Data */

7

            Map < String Object > DataMap = New HashMap < String Object > ();

8

            List < Map < String String >> userlist = New ArrayList <> (uList. size ());

9

             for (UseruuList) {

10

                Map < String String > RowData = New HashMap <> ();

11

                RowData. put ("Name"u.) GetName ());

12

                RowData. put ("Account"u.) Getaccount ());

13

                RowData. put ("Dept"u. getdept ());

14

                RowData. put ("Gender"u. Isgender ? "Male" "female");

15

                RowData. put ("Email"u.) Getemail ());

16

                userlist. Add (rowdata);

17

            }

18

            DataMap. put ("userlist"userlist);

19

20

            /** 3. Call the Freemarker API to generate Excel */

21st

            Configuration CFG = New Configuration (Configuration. version_2_3_22);

22

            Set the location where the template files are loaded

23

            cfg. setservletcontextfortemplateloading (servletactioncontext. Getservletcontext "/FTL");

24

            Get template

25

            Template Template = cfg. gettemplate ("EXPORTEXCEL.FTL");

26

27

            HttpServletResponse Response = Servletactioncontext. GetResponse ();

28

            Prevent garbled, set the next code

29

            response. setcharacterencoding ("Utf-8");

30

            response. SetHeader ("content-disposition""attachment; Filename=testexcel.xls ");

31

            Build Excel

32

            template. Process (dataMapresponse. getwriter ());

33

        Catch (Exceptione) {

34

            e. Printstacktrace ();

35

        }

36

Using Freemarker to generate Excel tables in Javaweb development