Java generates complex Excel with Freemarker. (Freemarker)

In the generation of Excel, most of the time is the use of POI,JXL, and so on, but for the complex of Excel, this workload is very large, and, for me so lazy people, it is rather painful one thing, so, I have to find out if there is a simple way to get this thing done. I thought about the last time I successfully generated a Word document with Freemarker. I was wondering if I could build Excel too? Then I'll try. The result is great, however, and there are some differences in the generation of Word documents, okay, no nonsense anymore.

A relatively simple Excel diagram.

This is a structure of Excel, then, save it as "XML Spreadsheet 2003 (*.xml)"

To how to fill the data exactly where it is, the trick to generating a Word document is to add a marker, or write pinyin, where you want to populate the data.

is the generated XML

I think the structure should be clear to everyone, row: Represents a row, the cell represents a cell.

A friend who has used POI,JXL should be very familiar with it.

Some friends may open the time, the XML is very messy, can not find the need to modify the place, the above mentioned small tricks played a role, according to Pinyin or your unique logo to find the location needed to change.

Write the Freemarker module statement where you want to populate the data. For example: To change the type to "${type}", double quotes do not copy the past, hehe.

Also, there may be many lines in this line, all of which require an iterative label, as I mentioned in the previous article.

< #list sequence as item>
...
</#list >

In the syntax format above, sequence is either a collection object or an expression, but the expression returns a collection object.

Item is an arbitrary name, which is a collection element that is iterated over. In addition, when iterating over a collection object, it also contains two special loop variables:
Item_index: Index value of the current variable
Item_has_next: Whether the next object exists
You can also use the < #break > command to jump out of an iteration

In fact, these are all in the http://www.havenliu.com/goodarticle/420.html, there are time friends can see. There are many interesting things.

Back to the point, cut the line you need to loop through to < #list ></#list > Center.

Well, after this step, you can change its suffix to: FTL.

Work to do this step has been almost, the code, you can look at an article. Come on, take the blame, put the code on and write some notes.

[Java]View Plaincopy

2. Package com;
6. Import Java.io.BufferedWriter;
8. Import Java.io.File;
10. Import java.io.FileNotFoundException;
12. Import Java.io.FileOutputStream;
14. Import java.io.IOException;
16. Import Java.io.OutputStreamWriter;
18. Import Java.io.Writer;
20. Import java.util.ArrayList;
22. Import Java.util.HashMap;
24. Import java.util.List;
26. Import Java.util.Map;
30. Import freemarker.template.Configuration;
32. Import Freemarker.template.Template;
34. Import freemarker.template.TemplateException;
38. Public class Wordtest {
42. Private Configuration configuration = null;
46. Public wordtest () {
48. Configuration = new configuration ();
50. Configuration.setdefaultencoding ("UTF-8");
52. }
56. public static void Main (string[] args) {
58. Wordtest test = new Wordtest ();
60. Test.createword ();
62. }
66. public void Createword () {
68. map<string,object> datamap=New hashmap<string,object> ();
70. GetData (DATAMAP);
72. Configuration.setclassfortemplateloading (this.getclass (), "/com"); location where the//FTL file exists
74. Template t=null;
76. try {
78. t = configuration.gettemplate ("WORDMODEL.FTL"); //File name
80. } catch (IOException e) {
82. E.printstacktrace ();
84. }
86. File OutFile = new File ("O:/outfilessa" +math.random () *10000+". xls"); //path to the generated file
88. Writer out = null;
90. try {
92. out = new BufferedWriter (New OutputStreamWriter (NewFileOutputStream (outFile)));
94. } catch (FileNotFoundException E1) {
96. E1.printstacktrace ();
98. }
102. try {
104. T.process (DataMap, out);
106. } catch (Templateexception e) {
108. E.printstacktrace ();
110. } catch (IOException e) {
112. E.printstacktrace ();
114. }
116. }
118. //When assigning a value here, you need to be aware that the data you need in XML must be provided to it, or you will not find an element wrong.
120. private void GetData (map<string, object> dataMap) {
122. Datamap.put ("title", "title");
124. Datamap.put ("year", "2012");
126. Datamap.put ("Month", "2");
128. Datamap.put ("Day", "13");
130. Datamap.put ("auditor", "Xin");
132. Datamap.put ("Phone", "xxxxxxxxxxxxx");
134. Datamap.put ("weave", "Wentao");
136. Datamap.put ("number", 1);
138. Datamap.put ("Content", "contents", +2);
142. list<map<string,object>> list = new arraylist<map<string,object>> ();
144. For (int i = 0; i < i++) {
146. map<string,object> map = new hashmap<string,object> ();
148. Map.put ("number", I);
150. Map.put ("content", "contents", +i);
152. List.add (map);
154. }
160. Datamap.put ("list", list);
162. }
164. }

Well, that's about it.

How the program did not error, then your Excel has been successfully generated, but.

Sometimes you can't open the table?

This is because excel in the generation of XML, it may be to not waste resources, at the time of generation, will specify how many lines of Excel, if beyond this length, it will compile does not pass. Error.

The problem is well solved.

To address these 2 issues, 2 different places need to be modified.

1: The sheet to the settings. It's sheet. OK, open XML, search for "Worksheet", if you have multiple worksheets, find your own.

Modify its property value "Ss:expandedrowcount", see this, you should understand it? Set it up a little bit, or pass a value to it by the program. The problem is solved.

2: Table, if you have patience, you can according to it to your log to see, a look will know. But not very easy to find, hehe.

Search for the last row, and modify its property value "Ss:index".

Java generates complex Excel with Freemarker. (Freemarker)