Export Excel with POI

Import Java.lang.reflect.Field;
Import java.lang.reflect.InvocationTargetException;
Import Java.lang.reflect.Method;
Import Java.sql.Date;
Import java.util.ArrayList;
Import java.util.List;

Import Org.apache.poi.ss.usermodel.Cell;
Import Org.apache.poi.ss.usermodel.CellStyle;
Import Org.apache.poi.ss.usermodel.CreationHelper;
Import Org.apache.poi.ss.usermodel.Row;
Import Org.apache.poi.ss.usermodel.Sheet;
Import Org.apache.poi.ss.usermodel.Workbook;
Import org.apache.poi.ss.util.CellRangeAddress;
Import Org.apache.poi.xssf.streaming.SXSSFWorkbook;

Required jar Packages: Poi-3.15.jar, Poi-ooxml-3.15.jar

/**
* 2002, 2003 of a workbook has a maximum of 256 worksheets, by default, three worksheets, the worksheet consists of 65536 rows of *256 columns, each row is crossed into a single unit.
* 2007, regardless of the number of worksheets, the number of worksheet columns, the number of rows, has greatly exceeded this data, Excel default workbook extension ". xlsx",
* Each work page in this format contains 1048576 rows (row) *16384 columns (column). The Excel Help file says that theoretically sheet is unlimited, but is limited by the available memory,
* You can try to insert a new sheet, and then press F4, you will see that the worksheet is constantly increasing, my 1G memory, the worksheet is increased to 10,000, and the basic operation can be done normally.
*/

/**
*
* Excel Export method
* @param title of Excel
* @param headersparams The list<striing>;header that the element is stored as a string "header = param" is a table header, and Param represents the property of the table header
* @param dataList need to export Excel data
* @param sheetmaxcount The maximum number of storage rows per sheet (qty)
* @return Workbook
* @since (which version this method started in) 0.0.3
* @author Yangke
*
*/
public class Nextexcelutil {

/**
* Sub-sheet export Excel case
* @param title of the Excel table, or null if it does not exist
* @param headersparams table header corresponds to an array of attribute values
* @param dataList Export Excel data
* @param sheetmaxcount The maximum amount of data stored in a single sheet
* */
@SuppressWarnings ({"Unchecked", "rawtypes", "unused"})
public static <T> Workbook Getworkbook (String title, list<string> headersparams, list<t> dataList, int Sheetmaxcount) {
Get the total amount of data exported
int count = Datalist.size ();
Get the number of sheet to divide
int page = count%sheetmaxcount > 0? Count/sheetmaxcount + 1:count/sheetmaxcount;
Splitting a large list into smaller lists
list<list> splitlist = getsplitlist (dataList, Sheetmaxcount);
Build table
Workbook wb = new Hssfworkbook ();
for (int i = 0;i < page;i++) {
int m = 0;
Create sheet
Sheet Sheet = Wb.createsheet ();
if (title! = null) {
Wb.setsheetname (i, title+i);
Set Title
for (int j = 0;j < 2;j++) {
Row Titlerow = Sheet.createrow (i);
for (int k = 0;k < Headersparams.size (); k++) {
Cell Titlecell = Titlerow.createcell (k);
CellStyle titlestyle = GetStyle (WB);
Titlecell.setcellstyle (TitleStyle);
The header content is set only once, and the value can be set only in the first row of the header's position, or the merged cell value is overwritten
if (j = = 0 && k = = 0) {
Titlecell.setcellvalue (title);
Titlecell.setcellstyle (TitleStyle);
}
}
}
Merge header cells
Sheet.addmergedregion (New cellrangeaddress (0, 1,0,headersparams.size ()-1));
m = 2;
}

Get the header and its corresponding properties
string[] headers = new string[headersparams.size ()];
string[] params = new string[headersparams.size ()];
for (int j = 0;j < Headersparams.size (); j + +) {
string[] HsPs = Headersparams.get (j). ToString (). Split ("=");
HEADERS[J] = hsps[0];
PARAMS[J] = hsps[1];
}

Set up the table header
Row Headrow = Sheet.createrow (m);
for (int j = 0;j < headers.length;j++) {
Cell Headercell = Headrow.createcell (j);
Headercell.setcellvalue (Headers[j]);
}
Get the data that a single sheet needs to populate
list<t> smalllist = Splitlist.get (i);
Populating a single sheet with data
Get reflection Templates
Class clazz = null;
for (int j = 0;j < Smalllist.size (); j + +) {
Clazz = Smalllist.get (0). GetClass ();
Row Paramrow = Sheet.createrow (j + 1 + m);
for (int k = 0;k < params.length;k++) {
try {
Field field = Clazz.getdeclaredfield (Params[k]);
method = Clazz.getmethod (Getmethodname (params[k));
Object obj = Method.invoke (Smalllist.get (j));

Cell Paramcell = Paramrow.createcell (k);
Determine if it is a time format
if (obj.getclass () = = Date.class) {
if (obj! = null) {
Creationhelper creationhelper = Wb.getcreationhelper ();
CellStyle Datestyle = Wb.createcellstyle ();
Datestyle.setdataformat (
Creationhelper.createdataformat (). GetFormat ("Yyyy-mm-dd HH:mm:ss"));
Paramcell.setcellstyle (Datestyle);
Paramcell.setcellvalue ((Date) obj);
}else{
Paramcell.setcellvalue ("");
}
}else{
if (obj! = null) {
Paramcell.setcellvalue (Obj.tostring ());
}else{
Paramcell.setcellvalue ("");
}
}
} catch (Nosuchfieldexception e) {
E.printstacktrace ();
} catch (SecurityException e) {
E.printstacktrace ();
} catch (Nosuchmethodexception e) {
E.printstacktrace ();
} catch (Illegalaccessexception e) {
E.printstacktrace ();
} catch (IllegalArgumentException e) {
E.printstacktrace ();
} catch (InvocationTargetException e) {
E.printstacktrace ();
}
}
}
}
return WB;
}

/**
* Export a single sheet of Excel
* @param title of the Excel table, or null if it does not exist
* @param headersparams table header corresponds to an array of attribute values
* @param dataList Export Excel data
* */
@SuppressWarnings ({"Rawtypes", "unused", "unchecked"})
public static <T> Workbook Getworkbook (String title,list<string> headersparams,list<t> dataList) {
Build table
Workbook wb = new Hssfworkbook ();
Create a workbook
Sheet Sheet = Wb.createsheet ();

Set start line, default is 0
int m = 0;
Determine if a title exists
if (title! = null) {
Wb.setsheetname (0, title);
Set Title
for (int i = 0;i < 2;i++) {
Row Titlerow = Sheet.createrow (i);
for (int j = 0;j < Headersparams.size (); j + +) {
Cell Titlecell = Titlerow.createcell (j);
CellStyle titlestyle = GetStyle (WB);
Titlecell.setcellstyle (TitleStyle);

The header content is set only once, and the value can be set only in the first row of the header's position, or the merged cell value is overwritten
if (j = = 0 && i = = 0) {
Titlecell.setcellvalue (title);
Titlecell.setcellstyle (TitleStyle);

}

}
}
Merge header cells
Sheet.addmergedregion (New cellrangeaddress (0, 1,0,headersparams.size ()-1));
m = 2;
}

string[] headers = new string[headersparams.size ()];
string[] params = new string[headersparams.size ()];
for (int i = 0;i < Headersparams.size (); i++) {
string[] Headerparam = Headersparams.get (i). Split ("=");
Headers[i] = headerparam[0];
Params[i] = headerparam[1];
}
Set up the table header
Row Headrow = Sheet.createrow (m);
for (int i = 0;i < headers.length;i++) {
Cell Headercell = Headrow.createcell (i);
Headercell.setcellvalue (Headers[i]);
}
Fill in the data
Get reflection Templates
Class clazz = null;
if (dataList! = null && datalist.size () > 0) {
Clazz = Datalist.get (0). GetClass ();
for (int i = 0;i < Datalist.size (); i++) {
Row Paramrow = Sheet.createrow (i + 1 + m);
for (int j = 0;j < params.length;j++) {
try {
Cell Paramcell = Paramrow.createcell (j);
Field field = Clazz.getdeclaredfield (Params[j]);
method = Clazz.getmethod (Getmethodname (params[j));
Object obj = Method.invoke (Datalist.get (i));

Determine if it is a time format
if (obj.getclass () = = Date.class) {
if (obj! = null) {
Creationhelper creationhelper = Wb.getcreationhelper ();
CellStyle Datestyle = Wb.createcellstyle ();
Datestyle.setdataformat (
Creationhelper.createdataformat (). GetFormat ("Yyyy-mm-dd HH:mm:ss"));
Paramcell.setcellstyle (Datestyle);
Paramcell.setcellvalue ((Date) obj);
}else{
Paramcell.setcellvalue ("");
}
}else{
if (obj! = null) {
Paramcell.setcellvalue (Obj.tostring ());
}else{
Paramcell.setcellvalue ("");
}
}
} catch (Nosuchfieldexception e) {
E.printstacktrace ();
} catch (SecurityException e) {
E.printstacktrace ();
} catch (Nosuchmethodexception e) {
E.printstacktrace ();
} catch (Illegalaccessexception e) {
E.printstacktrace ();
} catch (IllegalArgumentException e) {
E.printstacktrace ();
} catch (InvocationTargetException e) {
E.printstacktrace ();
}
}

}
}
return WB;
}

/**
*
* Set caption Border
* @param workbook Excel Object
* @param Titlerow Header Line
* @param headersparams Column Name property name, list of corresponding relationships
* @return CellStyle heading style
* @since (which version this method started in) 0.0.3
* @author YK
*
*/
@SuppressWarnings ("deprecation")
public static CellStyle GetStyle (Workbook WB) {
Create a display style
CellStyle style = Wb.createcellstyle ();
Create a font style
Font font = Wb.createfont ();

Font.setfontheight ((short) 280);
Font.setboldweight (Font.boldweight_bold);

Style.setalignment (HorizontalAlignment.Center);
Set border
Style.setborderbottom (Cellstyle.border_thin);
Style.setborderleft (Cellstyle.border_thin);
Style.setborderright (Cellstyle.border_thin);
Style.setbordertop (Cellstyle.border_thin);
Style.setfont (font);
return style;
}
/*
* Title Style
*/
@SuppressWarnings ("deprecation")
public static <T> CellStyle Gettitlestyle (Workbook Workbook) {

Set font
Font font = Workbook.createfont ();
Set Font size
Font.setfontheightinpoints ((short) 11);
Font Bold
Font.setboldweight (Font.boldweight_bold);
Set the font name
Font.setfontname ("Courier New");
Set style;
CellStyle style = Workbook.createcellstyle ();
Style.setfont (font);
Set up automatic line wrapping;
Style.setwraptext (FALSE);
Sets the horizontal alignment of the style to center-aligned;
Style.setalignment (Cellstyle.align_center);
Sets the vertical alignment of the style to center-aligned;
Style.setverticalalignment (Cellstyle.vertical_center);
return style;

}

/*
* Column header cell style
*/
@SuppressWarnings ("deprecation")
Public CellStyle Getcolumntopstyle (Workbook Workbook) {

Set font
Font font = Workbook.createfont ();
Set Font size
Font.setfontheightinpoints ((short) 11);
Font Bold
Font.setboldweight (Font.boldweight_bold);
Set the font name
Font.setfontname ("Courier New");
Set style;
CellStyle style = Workbook.createcellstyle ();
Set the bottom border;
Style.setborderbottom (Cellstyle.border_thin);
Set the left border;
Style.setborderleft (Cellstyle.border_thin);
Set the right border;
Style.setborderright (Cellstyle.border_thin);
Set the top border;
Style.setbordertop (Cellstyle.border_thin);
Fonts that are set in the style with the app;
Style.setfont (font);
Set up automatic line wrapping;
Style.setwraptext (FALSE);
Sets the horizontal alignment of the style to center-aligned;
Style.setalignment (Cellstyle.align_center);
Sets the vertical alignment of the style to center-aligned;
Style.setverticalalignment (Cellstyle.vertical_center);

return style;

}

/*
* Column data information cell style
*/
@SuppressWarnings ("deprecation")
Public CellStyle Getcellstyle (Workbook Workbook) {
Set font
Font font = Workbook.createfont ();
Set Font size
Font.setfontheightinpoints ((short) 10);
Font Bold
Font.setboldweight (Hssffont.boldweight_bold);
Set the font name
Font.setfontname ("Courier New");
Set style;
CellStyle style = Workbook.createcellstyle ();
Set the bottom border;
Style.setborderbottom (Cellstyle.border_thin);
Set the left border;
Style.setborderleft (Cellstyle.border_thin);
Set the right border;
Style.setborderright (Cellstyle.border_thin);
Set the top border;
Style.setbordertop (Cellstyle.border_thin);
Fonts that are set in the style with the app;
Style.setfont (font);
Set up automatic line wrapping;
Style.setwraptext (FALSE);
Sets the horizontal alignment of the style to center-aligned;
Style.setalignment (Hssfcellstyle.align_center);
Sets the vertical alignment of the style to center-aligned;
Style.setverticalalignment (Hssfcellstyle.vertical_center);

return style;

}

/*
* Get Method Name
* @param property name
* */
private static string Getmethodname (String fieldName) {
Return "get" + fieldname.substring (0,1). toUpperCase () + fieldname.substring (1);
}

}

Export Excel with POI