How to generate an excel file with a variable header to generate a variable excel File

How to generate an excel file with a variable header to generate a variable excel File

1. functions:

Input a header and data to import the data to excel.

To facilitate project expansion, data is passed in through a generic set. when data is obtained, it is obtained through reflection. This way, no matter how many items are in your header, I can easily generate it. In addition, to facilitate data management, a folder is automatically generated every day, and excel is generated in the corresponding folder. The root directory of the file is obtained by reading the properties file in the project (for details, see: http://www.cnblogs.com/0201zcr/p/4700418.html ). Now, go to code development.

2. Required jar packages

Here, we use the poi method to import data to excel.

3. Code Design

1) properties File Content

filePath=E\:/appData

 

2) obtain the root directory for saving the file (from the properties file in the Project)

Import java. io. bufferedInputStream; import java. io. file; import java. io. fileInputStream; import java. io. IOException; import java. io. inputStream; import java. util. properties; public class GetFilePlace {/*** read the file and obtain the root directory saved in excel * @ return the root directory saved in excel */public String getFilePath () {String dir = System. getProperty ("user. dir "); // obtain the working path of tomcat // obtain filedir that stores the file storage location. properties File path String realDir = dir + File. separator + "src" + File. separator + "META-INF" + File. separator + "filedir. properties ";/* String realDir = dir. substring (0, dir. length ()-4) + File. separator + "webapps" + File. separator + "generateExcels" + File. separator + "classes" + File. separator + "META-INF" + File. separator + "config" + File. separator + "filedir. properties "; */return realDir;}/*** get the value of the key in the filePath [properities file, * @ param filePath properities file path [including properities file] * @ param key the key value to be searched * @ return key the value */public String GetValueByKey (String filePath, String key) {Properties pps = new Properties (); try {InputStream in = new BufferedInputStream (new FileInputStream (filePath); pps. load (in); String value = pps. getProperty (key); in. close (); return value;} catch (IOException e) {e. printStackTrace (); return null ;}} /*** query the storage location corresponding to the properities file * @ param key query the storage address corresponding to the primary key * @ return key */public String getFileDirFromProperties (String key) {return GetValueByKey (getFilePath (), key);} public static void main (String [] args) {System. out. println (new GetFilePlace (). getFileDirFromProperties ("filePath "));}}

 

3) generate folders

Import java. io. file; import java. text. simpleDateFormat; import java. util. calendar; public class GenerateFold {/*** query the path of the generated excel file. If yes, it is stored in the corresponding location. Otherwise, a directory is generated and changed, and a folder is generated every day, the folder naming rule is the timestamp * @ param foldName generated excel save path * @ return current excel save path */public String getFold (String foldName) {SimpleDateFormat format = new SimpleDateFormat ("yyyyMMdd"); String todayStr = format. format (Calendar. getInstance (). g EtTime (); String foldPath = foldName + File. separator + todayStr; File file = new File (foldPath); if (! File. exists ()&&! File. isDirectory () {System. out. println ("nonexistent"); file. mkdirs ();} else {System. out. println ("exist");} return foldPath ;}}

 

4) generate an excel file

Import java. io. file; import java. io. fileNotFoundException; import java. io. fileOutputStream; import java. io. IOException; import java. io. outputStream; import java. lang. reflect. invocationTargetException; import java. lang. reflect. method; import java. text. simpleDateFormat; import java. util. arrayList; import java. util. calendar; import java. util. list; import java. util. UUID; import org. apache. poi. hssf. usermodel. HSSFCe Ll; import org. apache. poi. hssf. usermodel. HSSFCellStyle; 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. hssf. util. region; import org. apache. poi. ss. usermodel. cellStyle; import com. zcr. until. getFilePlace; import com. zcr. until. user;/*** generate excel * @ author zcr **/public class GenerateExcel {/*** Query the storage location of the corresponding files in the properties file through the keyword, and save the data to the xls file in the corresponding file path according to the header order, the file naming rule is a timestamp followed by a globally unique code * @ param fileDir // find the root directory of the file storage * @ param head // header * @ param list // data * @ return // file storage path and its name String */public <T> String generateExcels (String fileDir, string [] head, List <T> list) {// obtain the storage path // String savePath = new GetFilePlace (). getFileDirFromProperties (key); // file storage name String saveFileName = ""; SimpleDateFormat format = New SimpleDateFormat ("yyyyMMddHHmmssSS"); saveFileName + = format. format (Calendar. getInstance (). getTime (); UUID uuid = UUID. randomUUID (); // The World's unique code: saveFileName + = "-" + uuid. toString (); HSSFWorkbook workbook = new HSSFWorkbook (); HSSFSheet sheet = workbook. createSheet (); workbook. setSheetName (0, "APP data"); // you can specify HSSFCellStyle cellStyle = workbook. createCellStyle (); cellStyle. setAlignment (Cell Style. ALIGN_CENTER); cellStyle. setverticalignment (CellStyle. VERTICAL_CENTER); HSSFRow titleRow = sheet. createRow (0); sheet. addMergedRegion (new Region (0, (short) 0, 0, (short) (head. length-1); HSSFCell titleCell = titleRow. createCell (0); titleCell. setCellValue ("AAP data ____"); titleCell. setCellStyle (cellStyle); HSSFRow row1 = sheet. createRow (1); // set the header for (int I = 0; I 
 
5) Test Results
Generated file

The file content is as follows:

 
Properties File Read can be viewed: http://www.cnblogs.com/0201zcr/p/4700418.html
Read excel to view: http://www.cnblogs.com/0201zcr/p/4656779.html
Thank you!