Use poi to export Excel and poi to export excel

Use poi to export Excel and poi to export excel

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;

// The required jar package: poi-3.15.jar, poi-ooxml-3.15.jar

/**
* A Workbook of 2002 and 2003 contains a maximum of 256 worksheets. By default, three worksheets are created. A worksheet consists of 65536 rows and 256 columns. Each row and column is in a single unit.
* No matter the number of worksheets, the number of columns and the number of rows in the worksheet, the data is greatly broken through. xlsx by default ",
* In this format, each work page contains 1048576 rows (Row) * 16384 columns (Column ). In the EXCEL help file, theoretically, sheet is infinite, but restricted by the available memory,
* You can try to insert a new worksheet and press F4. then you will see that the worksheet keeps increasing. I have 1 GB of memory and 10000 worksheets. You can also perform basic operations normally.
*/

/**
*
* Excel export Method
* @ Param title: Excel title
* @ Param headersParams stores the List of elements in the string "header = param" mode <Striing>. header indicates the header, And param indicates the attributes of the header.
* @ Param dataList Excel data needs to be exported
* @ Param sheetMaxCount the maximum number of storage lines per sheet (Quantity)
* @ Return Workbook
* @ Since (version of which this method starts) 0.0.3
* @ Author yangke
*
*/
Public class NextExcelUtil {

/**
* Excel export by sheet
* @ Param title: The title of the Excel table. If it does not exist, null is passed.
* @ Param headersParams array of the ing between the header and the attribute value
* @ Param dataList export Excel Data
* @ Param sheetMaxCount maximum data volume stored in a single sheet
**/
@ SuppressWarnings ({"unchecked", "rawtypes", "unused "})
Public static <T> Workbook getWorkbook (String title, List <String> headersParams, List <T> dataList, int sheetMaxCount ){
// Obtain the total exported data volume
Int count = dataList. size ();
// Obtain the number of sheet shards.
Int page = count % sheetMaxCount> 0? Count/sheetMaxCount + 1: count/sheetMaxCount;
// Split a large List into multiple small lists.
List <List> splitList = getSplitList (dataList, sheetMaxCount );
// Create a table
Workbook wb = new HSSFWorkbook ();
For (int I = 0; I <page; I ++ ){
Int m = 0;
// Create a sheet
Sheet sheet = wb. createSheet ();
If (title! = Null ){
Wb. setSheetName (I, title + I );
// Set the title
For (int j = 0; j <2; j ++ ){
Row titleRow = sheet. createRow (I );
For (int k = 0; k Cell titleCell = titleRow. createCell (k );
CellStyle titleStyle = getStyle (wb );
TitleCell. setCellStyle (titleStyle );
// Set the title content only once, and the value can only be set to the first row and first column in the position occupied by the title; otherwise, the value of the merged cell will be overwritten.
If (j = 0 & k = 0 ){
TitleCell. setCellValue (title );
TitleCell. setCellStyle (titleStyle );
}
}
}
// Merge the title Cells
Sheet. addMergedRegion (new CellRangeAddress (0, 1, 0, headersParams. size ()-1 ));
M = 2;
}

// Obtain the header and its corresponding attributes
String [] headers = new String [headersParams. size ()];
String [] params = new String [headersParams. size ()];
For (int j = 0; j String [] hsPs = headersParams. get (j). toString (). split ("= ");
Headers [j] = hsPs [0];
Params [j] = hsPs [1];
}

// Set the header
Row headRow = sheet. createRow (m );
For (int j = 0; j Cell headerCell = headRow. createCell (j );
HeaderCell. setCellValue (headers [j]);
}
// Obtain the data to be filled in for a single sheet
List <T> smallList = splitList. get (I );
// Fill data with a single sheet
// Obtain the reflection Template
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 method = clazz. getMethod (getMethodName (params [k]);
Object obj = method. invoke (smallList. get (j ));

Cell paramCell = paramRow. createCell (k );
// Determine whether the time format is used
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 an Excel worksheet
* @ Param title: The title of the Excel table. If it does not exist, null is passed.
* @ Param headersParams array of the ing between the header and the attribute value
* @ Param dataList export Excel Data
**/
@ SuppressWarnings ({"rawtypes", "unused", "unchecked "})
Public static <T> Workbook getWorkbook (String title, List <String> headersParams, List <T> dataList ){
// Create a table
Workbook wb = new HSSFWorkbook ();
// Create a workbook
Sheet sheet = wb. createSheet ();

// Set the start line. The default value is 0.
Int m = 0;
// Determine whether a title exists
If (title! = Null ){
Wb. setSheetName (0, title );
// Set the title
For (int I = 0; I <2; I ++ ){
Row titleRow = sheet. createRow (I );
For (int j = 0; j Cell titleCell = titleRow. createCell (j );
CellStyle titleStyle = getStyle (wb );
TitleCell. setCellStyle (titleStyle );

// Set the title content only once, and the value can only be set to the first row and first column in the position occupied by the title; otherwise, the value of the merged cell will be overwritten.
If (j = 0 & I = 0 ){
TitleCell. setCellValue (title );
TitleCell. setCellStyle (titleStyle );

}

}
}
// Merge the title 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 String [] headerParam = headersParams. get (I). split ("= ");
Headers [I] = headerParam [0];
Params [I] = headerParam [1];
}
// Set the header
Row headRow = sheet. createRow (m );
For (int I = 0; I Cell headerCell = headRow. createCell (I );
HeaderCell. setCellValue (headers [I]);
}
// Enter data
// Obtain the reflection Template
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 method = clazz. getMethod (getMethodName (params [j]);
Object obj = method. invoke (dataList. get (I ));

// Determine whether the time format is used
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 the title border
* @ Param workbook Excel Object
* @ Param titleRow header line
* @ Param headersParams: name of the column name, List of corresponding relations
* @ Return CellStyle title Style
* @ Since (version of which this method starts) 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 the 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 the font
Font font = workbook. createFont ();
// Set the font size
Font. setFontHeightInPoints (short) 11 );
// Bold font
Font. setBoldweight (Font. BOLDWEIGHT_BOLD );
// Set the font name
Font. setFontName ("Courier New ");
// Set the style;
CellStyle style = workbook. createCellStyle ();
Style. setFont (font );
// Set automatic line feed;
Style. setWrapText (false );
// Set the horizontal alignment style to center alignment;
Style. setAlignment (CellStyle. ALIGN_CENTER );
// Set the vertical alignment style to center alignment;
Style. setVerticalAlignment (CellStyle. VERTICAL_CENTER );
Return style;

}

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

// Set the font
Font font = workbook. createFont ();
// Set the font size
Font. setFontHeightInPoints (short) 11 );
// Bold font
Font. setBoldweight (Font. BOLDWEIGHT_BOLD );
// Set the font name
Font. setFontName ("Courier New ");
// Set the 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 );
// Apply the font set in the style;
Style. setFont (font );
// Set automatic line feed;
Style. setWrapText (false );
// Set the horizontal alignment style to center alignment;
Style. setAlignment (CellStyle. ALIGN_CENTER );
// Set the vertical alignment style to center alignment;
Style. setVerticalAlignment (CellStyle. VERTICAL_CENTER );

Return style;

}

/*
* Column data information cell style
*/
@ SuppressWarnings ("deprecation ")
Public CellStyle getCellStyle (Workbook workbook ){
// Set the font
Font font = workbook. createFont ();
// Set the font size
// Font. setFontHeightInPoints (short) 10 );
// Bold font
// Font. setBoldweight (HSSFFont. BOLDWEIGHT_BOLD );
// Set the font name
Font. setFontName ("Courier New ");
// Set the 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 );
// Apply the font set in the style;
Style. setFont (font );
// Set automatic line feed;
Style. setWrapText (false );
// Set the horizontal alignment style to center alignment;
Style. setAlignment (HSSFCellStyle. ALIGN_CENTER );
// Set the vertical alignment style to center alignment;
Style. setVerticalAlignment (HSSFCellStyle. VERTICAL_CENTER );

Return style;

}

/*
* Get method name
* @ Param attribute name
**/
Private static String getMethodName (String fieldName ){
Return "get" + fieldName. substring (0, 1). toUpperCase () + fieldName. substring (1 );
}

}