Poi under Java Export template-based Excel

recently did a cumbersome feature, export a complex format of Excel, because the use of POI has been a long time, the export is very convenient, the import of the format is not very supportive, so think of using template-based, Java exported Excel How to read the Excel template and then write the dynamic Data to the template to generate a specific format of Excel, the same idea, the first consideration will be related to those objects, and the previous article read compared to a write step, so JXL will inevitably provide an object to support the write, This is Writableworkbook. So how to get to an instance of this class, view Apidoc Discover Workbook class provides several static Createworkbook methods to return Writableworkbook instances, You can see that many of the Createworkbook methods are divided into two main categories: one parameter and two parameters. Simple analysis can tell that the former is simply used to generate Excel files directly, which first reads the template and then writes the data to the template and then generates Excel. (There is also a three-parameter method plus a parameter to set workbook) now follow the process described in the previous article to see what steps are required to read the template and write it.



First step: Select the template file:

Workbook wb = Workbook.getworkbook (New File (Realpath));


Step two: Get a writable workbook from the template:
Writableworkbook WWB = Workbook.createworkbook (targetfile, WB);
The first parameter is an output stream object, such as the ability to
Bytearrayoutputstream targetfile = new Bytearrayoutputstream ();
This defines the output stream object. The second parameter represents the template to read.

Step Three: Select sheet with the name Stateresult in the template:
Writablesheet WWS = Wwb.getsheet ("Stateresult");
You can also create sheet if needed
Writablesheet WWS = wwb.createsheet ("sheet name", i);

Fourth Step: Select a cell, write a dynamic value, and convert to a cell of the appropriate type according to the different types of cells:
Label A1 = (label) Wws.getwritablecell (0,0);
a1.setstring ("cell contents")
or
Number A2 = (number) Wws.getwritablecell (0,1);//number is Jxl.write.Number
A2.setvalue (3.3);
You can also create new cells and add them to the sheet
Label C1 = new label (2,0, "cell contents");
Wws.addcell (C1);
or
Number C1 = new number (2,0,3.3);
Wws.addcell (C1);

when you generate an Excel report, you also encounter a requirement that is the format of the data, and we may want the numeric cells to appear in some format, while the string cells are

a format display. These can be achieved through Writablefont, NumberFormat, Writablecellformat, and the following example adds different formats to cell A1 and A2.



Java Code
Writablefont font= New Writablefont (Writablefont.createfont ("Song Body"), 10,writablefont.no_bold);
numberformat format = new NumberFormat ("###,# #0. xx");//numberformat is Jxl.write.NumberFormat
Writablecellformat cellFormat1 = new Writablecellformat (font,format);
Writablecellformat cellFormat2 = new Writablecellformat (font);
Cellformat1.setborder (Border.all, Jxl.format.BorderLineStyle.THIN);//border is Jxl.format.Border
Cellformat2.setborder (Border.all, Jxl.format.BorderLineStyle.THIN);//border is Jxl.format.Border
A2.setcellformat (CELLFORMAT1);
A1.setcellformat (CELLFORMAT2);
And don't forget to close Writableworkbook and workbook to free up resources:
wwb.close ();
wb.close ();

Finally, you can get Excel from the output stream targetfile in the way you want, such as directly generating files locally, outputting to the client browser, and so on.

If there are other needs, follow this line of thought, and then refer to Apidoc believe it can be easily solved.

at this point, the Java operation of the understanding of Excel Jxl finished.

Poi under Java Export template-based Excel