Java uses POI to export data to an excel report

In the previous article, we briefly introduced how to read the Word, Excel, and PDF documents in Java. However, in actual development, we used the most data export in the database in the form of an Excel report. It is not only simple to read data in the Office, especially in the production management or financial system, because these systems often do some report printing work. The data export format is generally Excel or PDF. So let's take a brief look at how to use Apache poi to export data in the database to an Excel report. In Java, there are many third-party jar packages for exporting data to excel reports. However, after comparison, it seems that poi is relatively easy to use. If you want to learn other export methods, you can study them on your own.

 

First, let's take a look at Apache poi.

 

Apache poi is a free and open-source cross-platform Java API written in Java. Apache poi provides APIs for Java programs to read and write Microsoft Office files. Apache poi is a Java API used to create and maintain various files that comply with office open XML (ooxml) standards and Microsoft's Ole 2 composite document format (ole2. You can use Java to read and create MS Excel files, modify MS Excel files, and use Java to read and create MS Word and mspowerpoint files. Apache poi provides a Java Excel operation solution for Excel97-2008 ).
The following describes the functions of Apache poi:

 

 

Hssf-provides the ability to read and write files in Microsoft Excel XLS format.

Xssf-provides the ability to read and write files in Microsoft Excel ooxml XLSX format.

Hwpf-provides the ability to read and write Microsoft Word doc files.

Hslf-supports reading and writing Microsoft PowerPoint files.

Hdgf-provides the ability to read files in Microsoft Visio format.

Hpbf-supports reading files in Microsoft Publisher format.

HSMF-provides the ability to read files in Microsoft Outlook format.

 

In this section, we want to learn how to use poi to excel. Therefore, we only need to use the hssf content. For other information, please study it on your own. Haha.

 

 

Next, let's take a step-by-step look at how to create an Excel report:

 

1. Create a New Excel worksheet

 

Hssfworkbook workbook = new hssfworkbook ();

Create a worksheet in an Excel worksheet named the default value. Poi also provides other workbook constructor methods. Let's take a look:

 

2. Create a worksheet

To create a worksheet named "payroll", the statement is:

Hssfsheet sheet = Workbook. createsheet ("payroll ");

 

3. Create a row

Create a row at the index 0 (the top row)

Hssfrow ROW = sheet. createrow (0 );

　

4. Create Cells

Create a cell (top left) at the position of index 0)

Hssfcell cell = row. createcell (short) 0 );

Define a cell as a string type. You can also set this string type in the created cell.

Cell. setcelltype (hssfcell. cell_type_string );

Enter some content in the cell

Cell. setcellvalue ("added value ");

 

5. Create an output file stream and output the corresponding Excel Workbook to the local device.

Fileoutputstream fout = new fileoutputstream (outputfile );

Workbook. Write (fout );

Fout. Flush ();

Operation ended. close the file.

Fout. Close ();

 

OK. The data read from the database under cell settings. In this way, we can import the content in the database to excel. Of course, here we just briefly introduce some poi usage. It is not detailed, for example, setting the data format in cells. Cell attributes. These will be used in my example below. Let's give you some examples. Okay. Finally, let's look at an instance:

 

Report Generation class: complexexportexcelclient. Java

Import java. util. arrayList; import java. util. list; import org. apache. poi. hssf. usermodel. HSSFCell; import org. apache. poi. hssf. usermodel. HSSFCellStyle; import org. apache. poi. hssf. usermodel. HSSFFont; import org. apache. poi. hssf. usermodel. HSSFRichTextString; import org. apache. poi. hssf. usermodel. HSSFRow; import org. apache. poi. hssf. usermodel. HSSFSheet; import org. apache. poi. hssf. usermodel. HSSFWorkbook; import org. apache. poi. hssf. util. region;/*** reject report generation class. ** @ author caoyb * @ version $ Revision: $ */public class ComplexExportExcelClient {private static HSSFWorkbook wb = new HSSFWorkbook (); private static HSSFSheet sheet = wb. createSheet (); public static void main (String [] args) {ExportExcel exportExcel = new ExportExcel (wb, sheet); // create a column header LISTList fialList = new ArrayList (); fialList. add ("the applicant has not provided any contact information"); fialList. add ("no work unit information and no income source information is provided"); fialList. add ("there is a work unit but no address or phone number provided"); fialList. add ("Missing Home Address"); fialList. add ("customer identification information missing"); fialList. add ("missing signature or invalid signature"); fialList. add ("other"); List errorList = new ArrayList (); errorList. add ("canceled by the customer"); errorList. add ("poor personal credit"); errorList. add ("fraud application"); errorList. add ("Applicant's basic conditions do not match"); errorList. add ("non-compliant application materials"); errorList. add ("unable to complete credit investigation"); errorList. add ("repeated application"); errorList. add ("other"); // calculate the number of columns in the Report. int number = 2 + fialList. size () * 2 + errorList. size () * 2; // defines the column width for the Working Table column (the number of columns changed by yourself) for (int I = 0; I <number; I ++) {sheet. setColumnWidth (I, 3000);} // create the cell style HSSFCellStyle cellStyle = wb. createCellStyle (); // specify the cell center and cellStyle. setAlignment (HSSFCellStyle. ALIGN_CENTER); // specifies the vertical center alignment of cells in cellStyle. setverticalignment (HSSFCellStyle. VERTICAL_CENTER); // specifies the cellStyle to be automatically wrapped when the cell content cannot be displayed. setWrapText (true); // set the cell font HSSFFont font = wb. createFont (); font. setBoldweight (HSSFFont. BOLDWEIGHT_BOLD); font. setFontName (""); font. setFontHeight (short) 200); cellStyle. setFont (font); // create the report header exportExcel. createNormalHead ("Nanjing application rejection Analysis Statistics", number); // set the second line String [] params = new String [] {" ", "YYYY"}; exportExcel. createNormalTwoRow (params, number); // you can specify HSSFRow row2 = sheet. createRow (2); HSSFCell cell0 = row2.createCell (0); cell0.setCellStyle (cellStyle); cell0.setCellValue (new HSSFRichTextString ("body Code"); HSSFCell 1 = row2.createCell (1 ); cell1.setCellStyle (cellStyle); cell1.setCellValue (new HSSFRichTextString ("branch name"); HSSFCell cell2 = row2.createCell (2); cell2.setCellStyle (cellStyle ); cell2.setCellValue (new HSSFRichTextString ("invalid part"); HSSFCell cell3 = row2.createCell (2 * fialList. size () + 2); cell3.setCellStyle (cellStyle); cell3.setCellValue (new HSSFRichTextString ("reject"); HSSFRow row3 = sheet. createRow (3); // set the Row Height row3.setHeight (short) 800); HSSFCell row3Cell = null; int m = 0; int n = 0; // create a column title for different lists (int I = 2; I <number; I = I + 2) {if (I <2 * fialList. size () + 2) {row3Cell = row3.createCell (I); row3Cell. setCellStyle (cellStyle); row3Cell. setCellValue (new HSSFRichTextString (fialList. get (m ). toString (); m ++;} else {row3Cell = row3.createCell (I); row3Cell. setCellStyle (cellStyle); row3Cell. setCellValue (new HSSFRichTextString (errorList. get (n ). toString (); n ++ ;}// create the total column row3Cell = row3.createCell (number); row3Cell. setCellStyle (cellStyle); row3Cell. setCellValue (new HSSFRichTextString ("Total"); // merge the cell HSSFRow row4 = sheet. createRow (4); // merge the first sheet column from the third row to the fifth row. addMergedRegion (new Region (2, (short) 0, 4, (short) 0); // merge the second sheet column from the third row to the fifth row. addMergedRegion (new Region (2, (short) 1, 4, (short) 1 )); // merge the third column of the third row to the column specified by AA int aa = 2 * fialList. size () + 1; sheet. addMergedRegion (new Region (2, (short) 2, 2, (short) aa); int start = aa + 1; sheet. addMergedRegion (new Region (2, (short) start, 2, (short) (number-1); // combine the rows of the fourth row cyclically, in addition, each two columns are merged into a column for (int I = 2; I <number; I = I + 2) {sheet. addMergedRegion (new Region (3, (short) I, 3, (short) (I + 1 )));} // create different column headers for (int I = 2; I <number; I ++) {if (I <2 * fialList. size () + 2) {if (I % 2 = 0) {HSSFCell cell = row4.createCell (I); cell. setCellStyle (cellStyle); cell. setCellValue (new HSSFRichTextString ("invalid quantity");} else {HSSFCell cell = row4.createCell (I); cell. setCellStyle (cellStyle); cell. setCellValue (new HSSFRichTextString ("proportion") ;}} else {if (I % 2 = 0) {HSSFCell cell = row4.createCell (I); cell. setCellStyle (cellStyle); cell. setCellValue (new HSSFRichTextString ("reject");} else {HSSFCell cell = row4.createCell (I); cell. setCellStyle (cellStyle); cell. setCellValue (new HSSFRichTextString ("proportion") ;}}// the values of the cells in the center of the loop creation for (int I = 5; I <number; I ++) {HSSFRow row = sheet. createRow (short) I); for (int j = 0; j <= number; j ++) {exportExcel. cteateCell (wb, row, (short) j, HSSFCellStyle. ALIGN_CENTER_SELECTION, String. valueOf (j) ;}/// create the total line of the last row String [] cellValue = new String [number-1]; for (int I = 0; I <number-1; I ++) {cellValue [I] = String. valueOf (I);} exportExcel. createLastSumRow (1, cellValue); exportExcel. outputExcel ("c :\\ rejection statistics .xls ");}}

Excel report tool: exportexcel. Java

 

Import Java. io. file; import Java. io. filenotfoundexception; import Java. io. fileoutputstream; import Java. io. ioexception; import Java. util. arraylist; import Java. util. list; import Org. apache. poi. hssf. usermodel. hssfcell; import Org. apache. poi. hssf. usermodel. hssfcellstyle; import Org. apache. poi. hssf. usermodel. hssffont; import Org. apache. poi. hssf. usermodel. hssfrichtextstring; import Org. apache. poi. hssf. usermodel. hssfrow; import Org. apache. poi. hssf. usermodel. hssfsheet; import Org. apache. poi. hssf. usermodel. hssfworkbook; import Org. apache. poi. hssf. util. hssfcolor; import Org. apache. poi. hssf. util. region; public class exportexcel {private hssfworkbook WB = NULL; private hssfsheet sheet = NULL;/*** @ Param WB * @ Param sheet */Public exportexcel (hssfworkbook WB, hssfsheet sheet) {super (); this. WB = WB; this. sheet = sheet;}/*** @ return the sheet */Public hssfsheet getsheet () {return sheet ;} /*** @ Param sheet * the sheet to set */Public void setsheet (hssfsheet sheet) {This. sheet = sheet;}/*** @ return the WB */Public hssfworkbook getwb () {return WB ;} /*** @ Param WB * the WB to set */Public void setwb (hssfworkbook WB) {This. WB = WB ;} /*** create a general Excel header ** @ Param headstring * The character displayed in the header * @ Param colsum * The number of columns in the report */Public void createnormalhead (string headstring, int colsum) {hssfrow ROW = sheet. createrow (0); // set the first hssfcell cell = row. createcell (0); row. setheight (short) 400); // defines the cell as a string type. setcelltype (hssfcell. encoding_utf_16); cell. setcellvalue (New hssfrichtextstring ("import statistics of various outlets in Nanjing City"); // specify the merged area sheet. addmergedregion (new region (0, (short) 0, 0, (short) colsum); hssfcellstyle cellstyle = WB. createcellstyle (); cellstyle. setalignment (hssfcellstyle. align_center); // specify the cell center and cellstyle. setverticalignment (hssfcellstyle. vertical_center); // specifies the vertical center alignment of cells in cellstyle. setwraptext (true); // specify the automatic line feed of cells // set the cell font hssffont font = WB. createfont (); font. setboldweight (hssffont. boldweight_bold); font. setfontname (""); font. setfontheight (short) 300); cellstyle. setfont (font); cell. setcellstyle (cellstyle );} /*** create the second row of a general report ** @ Param Params * array of statistical conditions * @ Param colsum * The column index to be merged */Public void createnormaltworow (string [] Params, int colsum) {hssfrow row1 = sheet. createrow (1); row1.setheight (short) 300); hssfcell cell2 = row1.createcell (0); cell2.setcelltype (hssfcell. encoding_utf_16); cell2.setcellvalue (New hssfrichtextstring ("statistical time:" + Params [0] + "to" + Params [1]); // specify the sheet of the merged area. addmergedregion (new region (1, (short) 0, 1, (short) colsum); hssfcellstyle cellstyle = WB. createcellstyle (); cellstyle. setalignment (hssfcellstyle. align_center); // specify the cell center and cellstyle. setverticalignment (hssfcellstyle. vertical_center); // specifies the vertical center alignment of cells in cellstyle. setwraptext (true); // specify the automatic line feed of cells // set the cell font hssffont font = WB. createfont (); font. setboldweight (hssffont. boldweight_bold); font. setfontname (""); font. setfontheight (short) 250); cellstyle. setfont (font); cell2.setcellstyle (cellstyle);}/*** set report title ** @ Param columheader * Title String Array */Public void createcolumheader (string [] columheader) {// set the column header hssfrow row2 = sheet. createrow (2); // specify the Row Height row2.setheight (short) 600); hssfcellstyle cellstyle = WB. createcellstyle (); cellstyle. setalignment (hssfcellstyle. align_center); // specify the cell center and cellstyle. setverticalignment (hssfcellstyle. vertical_center); // specifies the vertical center alignment of cells in cellstyle. setwraptext (true); // specify the automatic line feed of cells // cell font hssffont font = WB. createfont (); font. setboldweight (hssffont. boldweight_bold); font. setfontname (""); font. setfontheight (short) 250); cellstyle. setfont (font);/** cellstyle. setborderbottom (hssfcellstyle. border_thin); // set the border of a single non-grid to bold * cellstyle. setbottombordercolor (hssfcolor. black. index); // set the cell border color. * cellstyle. setborderleft (hssfcellstyle. border_thin); * cellstyle. setleftbordercolor (hssfcolor. black. index); * cellstyle. setborderright (hssfcellstyle. border_thin); * cellstyle. setrightbordercolor (hssfcolor. black. index); * cellstyle. setbordertop (hssfcellstyle. border_thin); * cellstyle. settopbordercolor (hssfcolor. black. index); * // set the cell background color cellstyle. setfillforegroundcolor (hssfcolor. grey_25_percent.index); cellstyle. setfillpattern (hssfcellstyle. solid_foreground); hssfcell cell3 = NULL; For (INT I = 0; I <columheader. length; I ++) {cell3 = row2.createcell (I); cell3.setcelltype (hssfcell. encoding_utf_16); cell3.setcellstyle (cellstyle); cell3.setcellvalue (New hssfrichtextstring (columheader [I]);} /*** create a content cell ** @ Param WB * hssfworkbook * @ Param row * hssfrow * @ Param Col * Short-type column Index * @ Param align * alignment mode * @ Param val * column value */Public void cteatecell (hssfworkbook WB, hssfrow row, int Col, short align, string Val) {hssfcell cell = row. createcell (COL); cell. setcelltype (hssfcell. encoding_utf_16); cell. setcellvalue (New hssfrichtextstring (VAL); hssfcellstyle cellstyle = WB. createcellstyle (); cellstyle. setalignment (align); cell. setcellstyle (cellstyle);}/*** create a total row ** @ Param colsum * The column index to be merged * @ Param cellvalue */Public void createlastsumrow (INT colsum, string [] cellvalue) {hssfcellstyle cellstyle = WB. createcellstyle (); cellstyle. setalignment (hssfcellstyle. align_center); // specify the cell center and cellstyle. setverticalignment (hssfcellstyle. vertical_center); // specifies the vertical center alignment of cells in cellstyle. setwraptext (true); // specify the automatic line feed of cells // cell font hssffont font = WB. createfont (); font. setboldweight (hssffont. boldweight_bold); font. setfontname (""); font. setfontheight (short) 250); cellstyle. setfont (font); hssfrow lastrow = sheet. createrow (short) (sheet. getlastrownum () + 1); hssfcell sumcell = lastrow. createcell (0); sumcell. setcellvalue (New hssfrichtextstring ("Total"); sumcell. setcellstyle (cellstyle); sheet. addmergedregion (new region (sheet. getlastrownum (), (short) 0, sheet. getlastrownum (), (short) colsum); // specify the merging region for (INT I = 2; I <(cellvalue. length + 2); I ++) {sumcell = lastrow. createcell (I); sumcell. setcellstyle (cellstyle); sumcell. setcellvalue (New hssfrichtextstring (cellvalue [I-2]);}/*** enter the Excel file ** @ Param filename * file name */Public void outputexcel (string filename) {fileoutputstream Fos = NULL; try {Fos = new fileoutputstream (new file (filename); WB. write (FOS); FOS. close ();} catch (filenotfoundexception e) {e. printstacktrace ();} catch (ioexception e) {e. printstacktrace ();}}}