Use poi to export Excel and poi to export excel

Source: Internet
Author: User

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 );
}

}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.