An Excel-generated tool class that was written using Apache POI

Not much to say, directly on the code, on a class, comments are also written more clearly.

/** * */package com.common.office;import java.io.fileoutputstream;import Java.lang.reflect.field;import Java.util.calendar;import Java.util.list;import Org.apache.commons.collections.collectionutils;import Org.apache.commons.io.ioutils;import Org.apache.commons.lang.arrayutils;import Org.apache.commons.lang.stringutils;import Org.apache.log4j.logger;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.hssfrichtextstring;import Org.apache.poi.hssf.usermodel.hssfrow;import Org.apache.poi.hssf.usermodel.hssfsheet;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Org.apache.poi.ss.usermodel.cellstyle;import Org.apache.poi.ss.usermodel.font;import org.apache.poi.ss.util.cellrangeaddress;/** * Excel Tool class * @author ll */public class Poiexcelutils {/** log */private static Final Logger Logger = Logger.getlogger (poiexcelutils.class);/** column default width */private static final int defaul_column_width = 4000;/** * 1. Create Workbook * * @return {@link Hssfworkbook} */private hssfworkbook Gethssfworkbo Ok () {Logger.info ("" Create Workbook "); return new Hssfworkbook ();} /** * 2. Create sheet * * @param hssfworkbook {@link hssfworkbook} * @param sheetname sheet name * @return {@link Hssfsheet} */p Rivate hssfsheet Gethssfsheet (Hssfworkbook hssfworkbook, String sheetname) {logger.info ("" Create Sheet "sheetname:" + SheetN AME); return Hssfworkbook.createsheet (SheetName);} /** * 3. Write header information * * @param hssfworkbook {@link hssfworkbook} * @param hssfsheet {@link hssfsheet} * @param headers column header, array shape {column header [email protected] @columnWidth, column heading [email protected] @columnWidth, column heading [email protected ] @columnWidth "} * where parameter @columnwidth is optional, columnWidth is integer value * @param title title */private void Writeheader (Hssfworkbook hssfwor Kbook, Hssfsheet Hssfsheet, string[] headers,string title) {logger.info ("Write header Information");//Initialize header and header cell style Hssfcellstyle Titlecellstyle = Createtitlecellstyle (Hssfworkbook);//title bar Hssfrow titlerow = hssfsheet.createrow (0); Titlerow.setheight ((short) 500); Hssfcell Titlecell = Titlerow.createcell (0);//Set Caption text Titlecell.setcellvalue (new hssfrichtextstring (title));// Set cell style Titlecell.setcellstyle (titlecellstyle);//Handle cell merging, four parameters are: Start line, terminating line, starting row, terminating column hssfsheet.addmergedregion (new Cellrangeaddress (0, 0, (short) 0, (short) (headers.length-1)));//sets the style of the merged cell Titlerow.createcell (headers.length-1). Setcellstyle (Titlecellstyle);//table header Hssfrow Headrow = Hssfsheet.createrow (1); Headrow.setheight ((short) 500); Hssfcell Headcell = null; string[] Headinfo = null;//handles Excel header for (int i = 0, len = headers.length; i < Len; i++) {headinfo = Headers[i].split (" @ "); Headcell = Headrow.createcell (i); Headcell.setcellvalue (headinfo[0]); Headcell.setcellstyle (titleCellStyle);// Set column width setcolumnwidth (i, Headinfo, Hssfsheet);}} /** * 4. Write Content section * * @param hssfworkbook {@link hssfworkbook} * @param hssfsheet {@link hssfsheet} * @param headers column header, array shape Type, * such as {column header [email protected] @columnWidTh "," column header [email protected] @columnWidth "," column header [email protected] @columnWidth "} * where parameter @columnwidth is optional, ColumnWidth is an integer value * @param dataList data collection to be exported * @throws Exception */private void Writecontent (Hssfworkbook hssfworkbook, HS Sfsheet Hssfsheet, string[] headers,list<?> dataList) throws Exception {Logger.info ("Write to Excel Content section" "); Hssfrow row = null; Hssfcell cell = null;//value of cells object cellvalue = null;//data written to row index int rownum = 2;//cell style Hssfcellstyle CellStyle = Createcont Entcellstyle (Hssfworkbook);//traversal collection, processing data for (int j = 0, size = Datalist.size (); j < size; J + +) {row = Hssfsheet.createro W (rownum); for (int i = 0, len = headers.length; i < Len; i++) {cell = Row.createcell (i); cellvalue = Getcellvalue (Datali St.get (j), Headers[i].split ("@") [1]); Cellvaluehandler (cell, Cellvalue); Cell.setcellstyle (CellStyle);} rownum++;}}  /** * Set Column width * @param the index number of the i column * @param headinfo header information, which contains the column width that the user needs to set */private void setcolumnwidth (int i, string[] headinfo, Hssfsheet hssfsheet) {if (headinfo.length< 3) {//User not set column width, use default width hssfsheet.setcolumnwidth (i, defaul_column_width); return;} if (Stringutils.isblank (headinfo[2])) {//Use default width hssfsheet.setcolumnwidth (i, defaul_column_width); return;} Set with user-set column widths Hssfsheet.setcolumnwidth (i, Integer.parseint (headinfo[2]));} /** * Cell Write value processor * @param {{@link Hssfcell} * @param cellvalue cell value */private void Cellvaluehandler (Hssfcell cell, Object CE Llvalue) {if (Cellvalue instanceof String) {cell.setcellvalue ((String) cellvalue);} else if (Cellvalue instanceof Boolean ) {Cell.setcellvalue ((Boolean) cellvalue);} else if (cellvalue instanceof Calendar) {cell.setcellvalue ((Calendar) cellvalue);} else if (Cellvalue instanceof Double) { Cell.setcellvalue (Double) cellvalue);} else if (cellvalue instanceof Integer | | cellvalue instanceof long| | cellvalue instanceof Short | | cellvalue instanceof FL Oat) {Cell.setcellvalue ((double.parsedouble (Cellvalue.tostring ()));} else if (Cellvalue instanceof hssfrichtextstring) {cell.setcellvalue (hssfrichtextstring) cellValue);} Cell.setcellvalue (Cellvalue.tostring ());} /** * Get attribute value based on field name * @param object to read the value of * @param fieldName field name * @param ClassType object's class type * @return attribute value * @throws exce Ption */private Object Getcellvalue (Object object, String fieldName) throws Exception {field[] fields = Object.getclass () . Getdeclaredfields (); Object value = Null;for (Field field:fields) {field.setaccessible (true); if (Field.getname (). Equals (FieldName)) {value = Field.get (object); break;}} return value;} /** * Create header and header cell style * @param hssfworkbook {@link Hssfworkbook} * @return {@link Hssfcellstyle} */private hssfcellstyle Crea Tetitlecellstyle (Hssfworkbook hssfworkbook) {logger.info ("Create header and header cell style");//cell style Hssfcellstyle CellStyle = Hssfworkbook.createcellstyle ();//Set the font style, instead of the bold hssffont font = Hssfworkbook.createfont (); font.setfontheightinpoints (short); Font.setboldweight (font.boldweight_bold); Cellstyle.setfont (font);//Set Universal Cell Properties Setcommoncellstyle ( CellStyle); return cellstyle;} /** * Create content cell style * @param hssfworkbook {@link Hssfworkbook} * @return {@link Hssfcellstyle} */private hssfcellstyle Createcontentcellstyle (hssfworkbook Hssfworkbook) {logger.info ("Create Content cell style"),//cell style Hssfcellstyle CellStyle = Hssfworkbook.createcellstyle ();// Set the font style to unchanged hssffont font = Hssfworkbook.createfont (); font.setfontheightinpoints (short); Cellstyle.setfont ( font);//Set Universal Cell Properties Setcommoncellstyle (CellStyle); return cellstyle;} /** * Set Common cell properties * @param cellstyle cells to set properties */private void Setcommoncellstyle (Hssfcellstyle cellstyle) {//center CELLSTYLE.S Etalignment (cellstyle.align_center);//Set Border Cellstyle.setborderbottom (Hssfcellstyle.border_thin); Cellstyle.setborderleft (Hssfcellstyle.border_thin); Cellstyle.setbordertop (Hssfcellstyle.border_thin); Cellstyle.setborderright (Hssfcellstyle.border_thin);} /** * Output generated Excel to the specified directory * @param hssfworkbook {@link hssfworkbook} * @param filePath file output directory, including file name (. xls) */private void writ E2filepath (Hssfworkbook hssfworkbook, String filePath) {logger.info ("" Outputs the generated Excel to the specified directory "FilePath:" + filePath); FileOutputStream fileout = null;try {fileout = new FileOutputStream (FilePath); Hssfworkbook.write (fileout);} catch ( Exception e) {logger.error ("" Output generated Excel to the specified directory failed "", e); throw new RuntimeException ("Failed to output the generated Excel to the specified directory");} finally { ioutils.closequietly (Fileout);}} /** * Generate Excel, store to specified directory * @param sheetname sheet name * @param title * @param filePath file path for Excel to export * @param headers column Header , array form, * such as {"Column header [email protected] @columnWidth", "column header [email protected] @columnWidth", "column heading [email  Protected] @columnWidth "} * where the parameter @columnwidth is optional, columnWidth is an integer value * @param dataList the collection of data to be exported * @throws Exception */public static void Createexcel2filepath (String sheetname, string title, String filepath,string[] headers, list<?> DataList) throws Exception {Logger.info ("" generates Excel and stores it in the folder directory specified "SheetName:" + SheetName + ", Title:" + title+ ", file Path: "+ FilePath +", headers: "+ headers.tostring ())" if (Arrayutils.isempty (headers)) {Logger.warn ("" header is empty "); throw New RuntimeException ("Table header cannot be empty ");} if (Collectionutils.isempty (dataList)) {Logger.warn ("" The data to be exported is empty "); throw new RuntimeException (" The data to be exported is empty ");} Poiexcelutils poiexcelutil = new Poiexcelutils ();//1. Create Workbookhssfworkbook Hssfworkbook = Poiexcelutil.gethssfworkbook ();//2. Create Sheethssfsheet Hssfsheet = Poiexcelutil.gethssfsheet (HssfWorkbook, SheetName) ;//3. Write Headpoiexcelutil.writeheader (Hssfworkbook, Hssfsheet, headers, title);//4. Write Content Poiexcelutil.writecontent ( Hssfworkbook, Hssfsheet, headers, dataList);//5. Save the file to FilePath Poiexcelutil.write2filepath (Hssfworkbook, FilePath);} /** * generates Excel workbook for exporting Excel * @param sheetname sheet name * @param title * @param headers column header, array form, * e.g. {"Column header [email  protected] @columnWidth "," column header [email protected] @columnWidth "," column header [email protected] @columnWidth "} * The parameter @columnwidth is optional, ColumnWidth is the integer value * @param dataList the collection of data to be exported * @throws Exception */public static Hssfworkbook creat Eexcel2export (String sheetname, string title, string[] headers,list<?> dataList) throWS Exception {logger.info ("" "Generate Excel Workbook for exporting Excel" SheetName: "+ SheetName +", Title: "+ title+", headers: "+ headers.tostring ())" if (Arrayutils.isempty (headers)) {Logger.warn ("" header is empty "); throw new RuntimeException (" table header cannot be empty ") ;} if (Collectionutils.isempty (dataList)) {Logger.warn ("" The data to be exported is empty "); throw new RuntimeException (" The data to be exported is empty ");} Poiexcelutils poiexcelutil = new Poiexcelutils ();//1. Create Workbookhssfworkbook Hssfworkbook = Poiexcelutil.gethssfworkbook ();//2. Create Sheethssfsheet Hssfsheet = Poiexcelutil.gethssfsheet (HssfWorkbook, SheetName) ;//3. Write Headpoiexcelutil.writeheader (Hssfworkbook, Hssfsheet, headers, title);//4. Write Content Poiexcelutil.writecontent ( Hssfworkbook, Hssfsheet, headers, dataList); return hssfworkbook;}}

An Excel-generated tool class that was written using Apache POI