Export Excel Using POI

Many times, a software application needs to generate a report in Microsoft Excel file format. Sometimes, an application might even want to use an Excel file as input data.

Common Java Excel APIs

Java Aspose Cells
Java Aspose Cells is a purely Java-licensed Excel API, developed and released by vendor Aspose. The latest version of this API, released in July 2014, is a rich and heavy API (a combination of common Java classes and AWT classes) designed to read, write, and manipulate the components of the spreadsheet Excel. 8.1.2 Common uses for this API are as follows:
Excel reports, creating dynamic Excel Reports
High-fidelity Excel rendering and printing
Import and export data from an Excel spreadsheet
Generate, edit, convert and spreadsheet

JXL
JXL is a selenium third-party framework that supports data-driven automation based on Web browsers (data is automatically updated with Web browsers). However, it is also used as a common support library for the Jexcel API, because its basic functionality is the ability to create, read and write spreadsheets. The basic features are as follows:
To generate an Excel file
Import data from workbooks and spreadsheets
Get the total number of rows and columns
Note: JXL only supports the XLS file format and cannot handle large data volumes.


Jexcel
Jexcel is a pure licensed API developed by Team Dev. With this, programmers can easily read, write, display, and modify two formats in an Excel workbook:. xls and. XLSX. This API makes it easy to embed the Java swing and AWT. The latest version of this API is Jexcel-2.6.12, released in 2009, with the main features listed below.
Automate Excel applications, workbooks, worksheets, and more
Embedded in a Java swing application as a normal swing component in a workbook
Event listeners added to workbooks and spreadsheets
Add event handlers to handle the behavior of workbooks and spreadsheet events
Add local peer development customization features

Apache POI
Apache POI is the 100% Open Source Library provided by the Apache Software Foundation. Most small and medium-sized application development relies primarily on Apache POI (hssf+ XSSF). It supports all the basic functions of the Excel library; However, rendering and text extraction are the main features of it.

Now commonly used to parse Excel API mainly has JXL and poi, this article mainly on the POI related knowledge.

Introduction to POI Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. This is an open source library developed by the Apache Software Foundation that uses Java distributed design or modifies Microsoft Office files. It contains classes and methods to decode user input data or files into MS Office documents.


Apache POI Component
Apache POI contains classes and methods to compound all of the MS Office OLE 2 documents. The list of this API component is as follows.


Poifs (poor obfuscation technology for file systems): This component is a fundamental factor for all other POI components. It is used to explicitly read different files.


HSSF (spreadsheet format): It is used to read and write the XLS format of the Ms-excel file.


XSSF (XML format): It is used for ms-excel in the xlsx file format.


HPSF (property formatting): It is used to extract Ms-office file property settings.


HWPF (Word Processor format): It is a file used to read and write Ms-word's document extension.


XWPF (XML word Processor format): It is a file used to read and write Ms-word docx extensions.


HSLF (Slide layout format): It is used to read, create, and edit PowerPoint presentations.


HDGF (Chart format): It contains a binary file of classes and methods for Ms-visio.

HPBF (publisher format): It is used to read and write Ms-publisher files.

HSSF profile HSSF is the abbreviation for horrible SpreadSheet format, which allows you to read, write, and modify Excel files with pure Java code. HSSF provides two types of Api:usermodel and Eventusermodel for read operations, the user model and the event-user model.
POI Excel Document Structure class
Hssfworkbook Excel Document Object
Hssfsheet Excel sheet Hssfrow line of Excel
Hssfcell Excel cell Hssffont Excel font
Hssfname name Hssfdataformat date format
Hssfheader Sheet Head
Hssffooter sheet Tail
Hssfcellstyle cell style
Hssfdateutil Date
Hssfprintsetup Printing
Hssferrorconstants error Message Table

Knowledge system

Excel common operation Method 1, Get Excel Common Object

Poifsfilesystem Fs=newpoifsfilesystem (New FileInputStream ("D:/test.xls"));
Get an Excel Workbook object
Hssfworkbook wb = new Hssfworkbook (FS);
Get an Excel sheet object
Hssfsheet sheet = wb.getsheetat (0);
Get the rows of an Excel worksheet
Hssfrow row = Sheet.getrow (i);
Get the cells for the Excel worksheet to specify rows
Hssfcell cell = Row.getcell ((short) j);
CellStyle = Cell.getcellstyle ();//Get cell style


2. Set up Excel Common Object

Hssfworkbook wb = new Hssfworkbook ();//Create an Excel Workbook object
Hssfsheet sheet = wb.createsheet ("new sheet");//Create an Excel sheet object
Hssfrow row = Sheet.createrow ((short) 0); Create a row for an Excel worksheet
CellStyle = Wb.createcellstyle ();//Create cell style
Row.createcell ((short) 0). Setcellstyle (CellStyle); Create a cell in an Excel worksheet that specifies a row
Row.createcell ((short) 0). Setcellvalue (1); Set the value of an Excel worksheet

3, set the column width, row height

[
Sheet.setcolumnwidth (short) column, (short) width);
Row.setheight ((short) height);

4. Save Excel File


FileOutputStream fileout = new FileOutputStream (path);
Wb.write (fileout);

5, commonly used cell border format


Hssfcellstyle style = Wb.createcellstyle ();
Style.setborderbottom (hssfcellstyle.border_dotted);//Bottom Border
Style.setborderleft (hssfcellstyle.border_dotted);//left Border
Style.setborderright (Hssfcellstyle.border_thin);//Right Border
Style.setbordertop (Hssfcellstyle.border_thin);//Top Border

6. Set the Excel gridlines to not display
Sheet.setdisplaygridlines (false), where sheet is the sheet object
7. Set content wrapping in Excel cells
Cellstyle.setwraptext (True) where CellStyle is the CellStyle object created by workbook, then sets CellStyle to the cell object to be wrapped, and finally joins the object to be wrapped (typically a string) "/ r/n ". Such as
Toptile.append ("/r/n" + "cellcontent");


8. Merging of cells
Sheet.addmergedregion (New cellrangeaddress (0, 4, 0, 2)); This example merges 4 rows 2 columns

The sample code uses POI to import the POI-related jar, this example uses Poi-3.10-final-20140208.jar, and now the latest version is Poi-3.15.jar

Package Com.company.test;import Java.io.file;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.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.ss.util.cellrangeaddress;public class mergedcells{public static void Main (string[] args) throws Ioexce    ption {//create workbook Hssfworkbook wb = new Hssfworkbook ();    create worksheet Hssfsheet sheet = wb.createsheet ("new sheet");    for (int i=0;i<3;i++) {//Set column width sheet.setcolumnwidth (i, 3000);    }//Create row Hssfrow row = sheet.createrow (0);    Row.setheightinpoints (30);//Set row height//create cell Hssfcell cell = Row.createcell (0);    Cell.setcellvalue ("User Information Form"); Heading styles//Creating cell styles HssfceLlstyle CellStyle = Wb.createcellstyle ();    Sets the background color of the cell to light blue Cellstyle.setfillforegroundcolor (HSSFColor.PALE_BLUE.index);    Cellstyle.setfillpattern (Hssfcellstyle.solid_foreground);    Set Cell Center alignment cellstyle.setalignment (Hssfcellstyle.align_center);    Sets the cell vertical center alignment cellstyle.setverticalalignment (Hssfcellstyle.vertical_center);    Create cell contents not displayed when the line wraps Cellstyle.setwraptext (true);    Set the cell font style hssffont font = Wb.createfont ();    Set font bold font.setboldweight (Hssffont.boldweight_bold);    Font.setfontname ("The song Body");    Font.setfontheight ((short) 200);    Cellstyle.setfont (font);    Set cell border to Thin line cellstyle.setborderleft (Hssfcellstyle.border_thin);    Cellstyle.setborderbottom (Hssfcellstyle.border_thin);    Cellstyle.setborderright (Hssfcellstyle.border_thin);    Cellstyle.setbordertop (Hssfcellstyle.border_thin);    Set cell style Cell.setcellstyle (CellStyle);        Merge cell sheet.addmergedregion (new cellrangeaddress (0, 0, 0, 2)); Hssfrow row1 = Sheet.createrow (1);  Title Information string[] titles = {"ID", "username", "password"};  for (int i=0;i<3;i++) {Hssfcell cell1 = Row1.createcell (i);  Cell1.setcellvalue (Titles[i]);  Set cell style Cell1.setcellstyle (CellStyle);  }//simulation data, in real case string[] more for entity bean list<string[]> List = new arraylist<string[]> ();  List.add (New string[]{"1", "Zhangsan", "111"});  List.add (New string[]{"2", "Lisi", "222"});  List.add (New string[]{"3", "Wangwu", "333"});    Content style//Create cell style Hssfcellstyle CellStyle2 = Wb.createcellstyle ();    Set Cell Center alignment cellstyle2.setalignment (Hssfcellstyle.align_center);    Sets the cell vertical center alignment cellstyle2.setverticalalignment (Hssfcellstyle.vertical_center);    Create cell contents not displayed when the line wraps Cellstyle2.setwraptext (true);    Set cell font style Hssffont Font2 = Wb.createfont ();    Set font bold font2.setboldweight (Hssffont.boldweight_bold);    Font2.setfontname ("The song Body");    Font2.setfontheight ((short) 200);    Cellstyle2.setfont (Font2);    Set cell border to Thin line cellstyle.setborderleft (Hssfcellstyle.border_thin); CellstYle.setborderbottom (Hssfcellstyle.border_thin);    Cellstyle.setborderright (Hssfcellstyle.border_thin);  Cellstyle.setbordertop (Hssfcellstyle.border_thin);  Loop assignment for (int i=0;i<list.size (); i++) {Hssfrow row2 = Sheet.createrow (i+2);    for (int j=0;j<3;j++) {Hssfcell cell1 = Row2.createcell (j);    Cell1.setcellvalue (List.get (i) [j]);  Set cell style Cell1.setcellstyle (cellStyle2);    }} File File = new file ("D://a.xls"), if (!file.exists ()) {file.createnewfile ();}    FileOutputStream fileout = new FileOutputStream (file);    Wb.write (fileout);  Fileout.close (); }}

Run results

This article briefly introduces the relevant knowledge of POI, and mainly studies the knowledge of using POI to export Excel, the knowledge about using POI to read Excel and using POI to manipulate other Office files, such as Doc, to be researched later.

Export Excel Using POI