Java uses POI to export front-end tabular data in the form of an Excel file

Knowledge points: Front-end tabular data, invoke background interface, export Excel file data, use to Apache POI interface

POI provides the ability for Java programs to read and write to Microsoft Office format files.

(1) Introduction of POI and file read/write related packages in Pom.xml

<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
</dependency>

(2) Controller layer, table export interface

/**
* Export Data
*/
@RequestMapping ("/export")
public void Export (HttpServletRequest request,httpservletresponse response) {
export<equipment> ee = new export<equipment> ();//equipment entity class
List<equipment>equiplist= Equipmentservice.getequipmentlist (); //Equiplist is the data collection of the query
String []Headers= {"e_id", "E_code", "E_location", "E_ip", "E_attend_code", "Cruser", "Crtime", "Upuser", "uptime"};//table header data set
String fileName = "Device list data";//excel Table name
Response.setcontenttype ("Application/x-msdownload");
ServletoutputstreamOutputStream= NULL; //OutputStreamfile output stream
Try
{
filename = filename + ". xls";//define file format 
if (Request.getheader ("User-agent"). toLowerCase (). Contains ("Firefox")//Firefox
{
Response.setheader ("Content-disposition",
"Attachment;filename=" + New String (Filename.getbytes ("UTF-8"), "iso-8859-1");
}
Else
{
Response.setheader ("Content-disposition",
"Attachment;filename=" + urlencoder.Encode (FileName, "UTF-8"));
}
OutputStream = Response.getoutputstream ();
try {
Ee.Export(Headers,FileName,equiplist,OutputStream);//Table Export tool class
} catch (InvocationTargetException e) {
E.printstacktrace ();
}
}
catch (Unsupportedencodingexception e)
{
throw new Businessexception ("Export failed!");
}
catch (IOException E)
{
throw new Businessexception ("Export failed!");
}
catch (Nosuchmethodexception e)
{
        Logger.error (E.getmessage ());
}
catch (Illegalaccessexception e)
{
 Logger.error (E.getmessage ());
} finally
{
Ioutils. closequietly (OutputStream); //
}
}

  (3) Export.java Table Export Tool class

Package Com.agesun.attendance.common;
Import Java.io.OutputStream;
Import Java.lang.reflect.Field;
Import Java.lang.reflect.InvocationTargetException;
Import Java.lang.reflect.Method;
Import Java.util.Iterator;
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;

/**
*< A word function description > < function details >
 *
*@authorNishuai
*@version[Version number, June 14, 2018]
*@see[Related classes/methods]
*@since[Product/Module Version]
*/
public class export<T>
{
 public voidExport (string[] Headers, String FileName, list<t> DataSet, OutputStream OutputStream)
 Throws Nosuchmethodexception, InvocationTargetException, illegalaccessexception
{
 Declaring a working book
Hssfworkbook WB =New Hssfworkbook ();
 Declare a list and name it
Hssfsheet sheet = wb.createsheet (fileName);
 
 /****************** Head Style ***********************/
Hssffont font = Wb.createfont ();
Font.setfontname ("Microsoft Ya Black");
Font.setfontheightinpoints ((Short14);Set Font size
Font.setboldweight (Hssffont.Boldweight_bold);
 Give the name of a list a length
Sheet.setdefaultcolumnwidth ((Short16);
 Generate a Style
Hssfcellstyle style = Wb.createcellstyle ();
Style.setalignment (Hssfcellstyle.Align_center);Center
Style.setfont (font);
 /********************* Data cell style **************************/
Hssffont RowFont = Wb.createfont ();
Rowfont.setfontname ("Song Body");
Rowfont.setfontheightinpoints ((Short14);Set Font size
Give the name of a list a length
Sheet.setdefaultcolumnwidth ((Short16);
 Generate a Style
Hssfcellstyle RowStyle = Wb.createcellstyle ();
Rowstyle.setfont (RowFont);
 Style font Centered
Rowstyle.setalignment (Hssfcellstyle.Align_center);
 
 Create the first row (also known as a table header)
Hssfrow row = Sheet.createrow (0);
Row.setheight ((Short500);
 Create a cell once for the first row of the table header
 for (Short i =0; I < headers.Length; i++)
{
Hssfcell cell = Row.createcell (i);
Cell.setcellvalue (Headers[i]);
Cell.setcellstyle (Style);
}

Iterating through the collection data, producing rows of data
iterator<T> it = Dataset.iterator ();
int index =0;
while (It.hasnext ())
{
index++;
row = Sheet.createrow (index);
Row.setheight ((Short500);
 T t = It.next ();
 Using reflection, the GetXXX () method is dynamically called to get the attribute value according to the order of the JavaBean property. 
field[] fields = T.getclass (). Getdeclaredfields ();
for (Short i =0; I < headers.Length; i++)
{
Field field = Fields[i];
String fieldName = Field.getname ();
String Getmethodname ="Get" + fieldname.substring (0,1). toUpperCase () + fieldname.substring (1);
Class TCls = T.getclass ();
Method GetMethod = Tcls.getmethod (getmethodname, new class[] {});
Object value = Getmethod.invoke (t, new object[] {});
 //determines the type of the value after casting 
 String TextValue = null; 
  //Other data types are treated as strings simple 
  Span style= "COLOR: #cc7832" >if (value! = null &&! {
 TextValue = value.tostring ()   "
 if (TextValue! = null 
 {
 Hssfcell cell = Row.createcell (i) ; 
  Cell.setcellstyle (rowstyle)   Cell.setcellvalue (TextValue)  } 
} 
}            /span>      
 

Java uses POI to export front-end tabular data in the form of an Excel file