Java Export Excel table

POI Introduction : Jakarta POI is a set of Java APIs for accessing Microsoft format documents. Jakarta POI has a number of components, including HSSF for manipulating Excel-formatted files and HWPF for manipulating word, and only the HSSF that are currently used to manipulate Excel are relatively mature in various components.

Official Homepage http://poi.apache.org/index.html,

API Document Http://poi.apache.org/apidocs/index.html

Now used more is using POI technology to export or import Excel, so we use POI Bar, with POI export Excel we first want to download the required jar package and then import into our project, with Maven's classmates just find the relevant

Dependencies can be added to the pom.xml.

1. Download the jar package:

Official Download: http://poi.apache.org/download.html Here you can download to its latest version and documentation, currently the latest version is 3.7, which uses a more stable version 3.6.

Baidu Network disk Download: Https://pan.baidu.com/s/1mjhoaWK Password: Pkur

2. Add the jar package to the project:

Add the downloaded jar package to the Lib folder in the Webinfo directory, the eclipse user selects the jar package and right-click to select the Build path option, the idea user selects the jar package and then right-clicks the Add as Library option.

If you are using MAVEN to search for POI in the MAVEN central repository and select the corresponding version, you can also copy the following code directly to Pom.xml.

<!--Https://mvnrepository.com/artifact/org.apache.poi/poi--><dependency><groupid> org.apache.poi</groupid><artifactid>poi</artifactid><version>3.6</version></ Dependency>

Tip : using Maven to introduce a dependency jar package might encounter a bug where package references are not available, but Maven dependencies are actually introduced and there are no errors, but as long as a reference to the class below Org.apache.poi.hssf.usermodel

will be error, the error content is: caused By:java.lang.noclassdeffounderror:org/apache/poi/hssf/usermodel/hssfworkbook.

3. Jakarta POI HSSF API component:

HSSF (the component used to manipulate Excel) provides the user with objects in the Rg.apache.poi.hssf.usermodel package, the main parts of which include Excel objects, styles, and formatting, with the following commonly used objects:

Common components:

Hssfworkbook Document objects for Excel

Hssfsheet Excel Forms

Hssfrow lines of Excel

Hssfcell Excel's lattice unit

Hssffont Excel Fonts

Style:

Hssfcellstyle cell style

4. Basic Operation steps:

First of all, we should know that an Excel file corresponds to a workbook, a workbook is composed of multiple sheet, a sheet is composed of multiple rows (row) and columns (cell). So we're going to use POI to export an Excel table

The correct order should be:

1. Open or create an "Excel file Object" with Hssfworkbook

2. Return or create sheet object with Hssfworkbook object

3. Return the Row object with the sheet object and get the cell object with the Row object

4. Read and write to the Cell object.

5, the generated hssfworkbook into the httpservletresponse in response to the front page

5. Export an Excel application instance:

Tool Class Code:

Package Com.yq.util;import Org.apache.poi.hssf.usermodel.hssfcell;import Org.apache.poi.hssf.usermodel.hssfcellstyle;import Org.apache.poi.hssf.usermodel.hssfrow;import    Org.apache.poi.hssf.usermodel.hssfsheet;import Org.apache.poi.hssf.usermodel.hssfworkbook;public class ExcelUtil { /** * Export Excel * @param sheetname sheet name * @param title * @param values * @param WB Hssfworkbook Object * @return */public static Hssfworkbook Gethssfworkbook (String sheetname,string []title,string [][]values, H        Ssfworkbook WB) {///first step, create a hssfworkbook, corresponding to an Excel file if (WB = = NULL) {WB = new Hssfworkbook ();        }//Second step, add a sheet in workbook, corresponding to the sheet hssfsheet sheet = wb.createsheet (sheetname) in the Excel file;        In the third step, add the No. 0 row of the table header to the sheet, noting that the old version of POI has a limit on the number of rows in Excel hssfrow row = sheet.createrow (0);        Fourth step, create the cell, and set the value header to set the table header centered Hssfcellstyle style = Wb.createcellstyle (); Style.setalignment (Hssfcellstyle.align_center); CreateA center format//Declaration Column Object Hssfcell cell = null;            Create a caption for (int i=0;i<title.length;i++) {cell = Row.createcell (i);            Cell.setcellvalue (Title[i]);        Cell.setcellstyle (style);            }//create content for (int i=0;i<values.length;i++) {row = Sheet.createrow (i + 1); for (int j=0;j<values[i].length;j++) {//assigns the content sequentially to the corresponding Column object Row.createcell (j). Setcellvalue (Valu            ES[I][J]);    }} return WB; }}

Controller code:

@Controller @requestmapping (value = "/report") public class Reportformcontroller extends Basecontroller {@Resource (name    = "Reportservice") private Reportmanager Reportservice; /** * Export Report * @return */@RequestMapping (value = "/export") @ResponseBody public void export (HTTPSERVL Etrequest request,httpservletresponse response) throws Exception {//Get data list<pagedata> List =           Reportservice.booklist (page);         　Excel title string[] title = {"Name", "Gender", "age", "School", "class"};  　　　　　Excel filename String filename = "Student Information sheet" +system.currenttimemillis () + ". xls";　　　　　　Sheet name String sheetname = "Student Information table";            for (int i = 0; i < list.size (); i++) {Content[i] = new String[title.length];            Pagedata obj = List.get (i);            Content[i][0] = Obj.get ("Stuname"). ToString ();            CONTENT[I][1] = Obj.get ("Stusex"). ToString ();            CONTENT[I][2] = Obj.get ("Stuage"). ToString (); ContenT[I][3] = Obj.get ("Stuschoolname"). ToString ();　　　　　　CONTENT[I][4] = Obj.get ("Stuclassname"). ToString ();　　　　　　}//Create Hssfworkbook Hssfworkbook WB = Excelutil.gethssfworkbook (sheetname, title, content, NULL);       　　　　Response to client try {this.setresponseheader (response, fileName);       　　　　OutputStream OS = Response.getoutputstream ();       　　　　Wb.write (OS);       　　　　Os.flush (); 　　　　　　Os.close (); 　　　　　　} catch (Exception e) {e.printstacktrace ();            }}//Send response stream method public void Setresponseheader (httpservletresponse response, String fileName) {try {            try {fileName = new String (Filename.getbytes (), "iso8859-1"); } catch (Unsupportedencodingexception e) {//TODO auto-generated catch block e.printstacktr            Ace ();            } response.setcontenttype ("Application/octet-stream;charset=iso8859-1"); Response.setheader ("Content-disposition", "attachment;filename="+ fileName);            Response.AddHeader ("Pargam", "No-cache");        Response.AddHeader ("Cache-control", "No-cache");        } catch (Exception ex) {ex.printstacktrace (); }    }
}

Front page code:

<button id= "Js-export" type= "button" class= "BTN btn-primary" > Export excel</button>

$ (' #js-export '). Click (function () {            window.location.href= "/report/exportbookstable.do;
});

Java Export Excel table