Java processing Excel file---Excel file creation, delete, write, read

The code for this article is the Excel processing class that I encapsulated, containing the inference that Excel exists, whether the table index exists, creating an Excel file, deleting an Excel file, writing information to Excel, and reading data from Excel.

Especially in the two methods of writing and reading, I use the Java reflection mechanism to implement, with object object as the parameter can be. The code proactively parses the properties and methods of the entity class. Code reusability is high.

Other areas of code that need to be improved and expanded. We can make simple changes according to the actual situation.

On the code, first of all I encapsulated this class (using a POI package):

Package Module.system.common;import Java.io.file;import Java.io.fileinputstream;import Java.io.filenotfoundexception;import Java.io.fileoutputstream;import Java.io.ioexception;import Java.lang.reflect.field;import Java.lang.reflect.method;import Java.util.arraylist;import Java.util.List;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.poifs.filesystem.poifsfilesystem;import Org.apache.poi.ss.usermodel.cell;import Org.apache.poi.ss.usermodel.row;import Org.apache.poi.ss.usermodel.Sheet /** * reading data from Excel/writing to Excel has a table header. The contents of each column of the table header attribute of the corresponding entity class * * @author NAGSH * */public class Excelmanage {private Hssfworkbook workbook = null;/** * Infer if the file exists. * param filedir File path * @return */public boolean fileexist (String filedir) {Boolean flag = false; File File = new file (Filedir); Flag = File.exists (); return flag;} /** * Infer whether the sheet of the file exists. * @param filedir file path * @param sheetname Table index name * @Return */public boolean sheetexist (String filedir,string sheetname) {Boolean flag = false; File File = new file (Filedir); if (file.exists ()) {//File exists//Create workbook try {workbook = new Hssfworkbook (new FileInputStream (file));//Add worksheet (No       The XLS file generated when you join sheet will be opened with an error) Hssfsheet sheet = workbook.getsheet (sheetname); if (sheet!=null) flag = true;}       catch (Exception e) {e.printstacktrace ();}  }else{//file does not exist flag = false;} return flag;} /** * Create new Excel. * @param filedir Excel Path * @param sheetname the table index to create * @param titlerow the first line of Excel, the table header */public void Createexcel (Strin    G filedir,string sheetname,string titlerow[]) {//Create workbook workbook = new Hssfworkbook ();      add worksheet (The XLS file generated when not joined Sheet will be opened with an error) Sheet Sheet1 = Workbook.createsheet (sheetname);    New file FileOutputStream out = null;    try {//Join table header row row = Workbook.getsheet (sheetname). CreateRow (0);    Create the first line for (int i = 0;i < titlerow.length;i++) {cell cell = Row.createcell (i); Cell. Setcellvalue (Titlerow[i]); } out = new FileOutputStream (filedir), workbook.write (out);}      catch (Exception e) {e.printstacktrace ();} finally {try {out.close ();    } catch (IOException e) {e.printstacktrace ();     }}}/** * Delete files.    * @param filedir file path */public boolean deleteexcel (String filedir) {Boolean flag = false;    File File = new file (Filedir);          Infer if the folder or file exists if (!file.exists ()) {//does not exist returns false return flag;                } else {//infer whether the Delete file Method File.delete () is called when the file if (File.isfile ()) {//is a file);            Flag = true;    }} return flag;     }/** * writes to Excel (existing data cannot be written). * @param filedir file path * @param sheetname Table Index * @param object */public void Writetoexcel (String filedi R,string SheetName, Object object) {//create Workbookfile file = new file (filedir); try {workbook = new Hssfworkbook (new fileInputStream (file));} catch (FileNotFoundException e) {e.printstacktrace ();} catch (IOException e) {e.printstacktrace ();} Stream FileOutputStream out = null; Hssfsheet sheet = workbook.getsheet (sheetname);//Gets the total number of rows in the table int rowCount = Sheet.getlastrownum () + 1;    Need to add a//To get the number of columns of the table header int columnCount = Sheet.getrow (0). Getlastcellnum ();     try {Row row = Sheet.createrow (RowCount); The latest line to join//To get the field of object by reflection, the corresponding header insert//Get the object's class object class Class_ = Object.getclass ();//Get header Row object Hssfrow titlerow = sh Eet.getrow (0), if (Titlerow!=null) {for (int columnindex = 0; columnindex < ColumnCount; columnindex++) {//Traverse table header string Ti tle = Titlerow.getcell (columnindex). ToString (). Trim (). ToString (). Trim (); String utitle = character.touppercase (Title.charat (0)) + title.substring (1, Title.length ()); Capitalize the first letter; String methodName = "Get" +utitle; method = Class_.getdeclaredmethod (MethodName); Set the method to run string data = Method.invoke (object). toString ();  Run the Get method, which is the data to be inserted in cell cell = Row.createcell (columnindex);  Cell.setcellvalue (data);}} out = new FileOutputStream (filedir), workbook.write (out);}      catch (Exception e) {e.printstacktrace ();} finally {try {out.close ();    } catch (IOException e) {e.printstacktrace (); }}}/** * Read the data in the Excel table. * * @param filedir file path * @param sheetname table index (EXCEL is a multi-table document, so you need to enter the table index.) As Sheet1) * @param object Object */public List readfromexcel (String filedir,string sheetname, Object object) {//Create WOR Kbookfile file = new file (filedir); try {workbook = new Hssfworkbook (new FileInputStream (file));} catch (Filenotfoundexcep tion e) {e.printstacktrace ();} catch (IOException e) {e.printstacktrace ();} List result = new ArrayList ();//Gets the object's class object class Class_ = Object.getclass ();//Get all the properties of the class field[] fields = class_.getdecl Aredfields ()///Read Excel data//Get the specified Excel table Hssfsheet sheet = workbook.getsheet (sheetname);//Gets the total number of rows in the table int rowCount = Sheet.getlastrownum () + 1; Need to add a System.out.println ("RowCount:" +rowcount); if (RowCount < 1) {return Result;} Gets the number of columns of the header int columnCount = Sheet.getrow (0). Getlastcellnum ();//Read header information, determine the method name to use---set method//For storing method name string[] Methodnames = new String[columncount]; The number of table header columns is the required set method number//For storing the property type string[] fieldtypes = new string[columncount];//Get header Row object Hssfrow titlerow = Sheet.getrow (0) ;//traverse for (int columnindex = 0; columnindex < ColumnCount; columnindex++) {//Traverse header column String data = Titlerow.getcell (Colu Mnindex). toString (); The contents of a column string Udata = Character.touppercase (Data.charat (0)) + data.substring (1, Data.length ()); Capitalize the first letter Methodnames[columnindex] = "Set" + udata;for (int i = 0; i < fields.length; i++) {//Traversal property array if (Data.equals (f Ields[i].getname ())) {//The attribute is equal to the table header Fieldtypes[columnindex] = Fields[i].gettype (). GetName ();//Put the property type in the array}}}//read data line by row Starting from 1 omit header for (int rowIndex = 1; rowIndex < RowCount; rowindex++) {//Get Row object Hssfrow row = Sheet.getrow (RowIndex); if (row ! = null) {Object obj = null;//Instantiates the object of the generic class an object try {obj = Class_.newinstance ();} catch (Exception E1) {e1.printstacktrace (); }// Obtain data for each cell in the bank for (int columnindex = 0; columnindex < ColumnCount; columnindex++) {String data = Row.getcell (Columninde x). toString ();//Gets the method name of the method to invoke string methodName = Methodnames[columnindex]; Method = Null;try {//This section can expand itself if (fieldtypes[columnindex].equals ("java.lang.String")) {method = Class_. Getdeclaredmethod (Methodname,string.class); Set the method to run--set method parameter is Stringmethod.invoke (obj, data); Run the method} else if (fieldtypes[columnindex].equals ("int")) {method = Class_.getdeclaredmethod (Methodname,int.class); Set the method to run--set method is intdouble data_double = double.parsedouble (data), int data_int = (int) data_double;method.invoke (obj , Data_int); Run the Method}} catch (Exception e) {e.printstacktrace ()}} Result.add (obj);}} return result;} public static void Main (string[] args) {excelmanage em = new Excelmanage ();//Infer whether the file exists System.out.println (em.fileexist ("E :/test2.xls "));//Create file string title[] = {" id "," name "," Password "};em.createexcel (" E:/test2.xls "," Sheet1 ", title);// Infer if sheet exists System.out.println (em. Sheetexist ("E:/test2.xls", "Sheet1"));//write to Exceluser user = new User (); User.setid (5); User.setname ("Qwer"); User.setpassword ("ZXCV"); User User3 = New user (), User3.setid (6), User3.setname ("Qwerwww"), User3.setpassword ("zxcvwww"); Em.writetoexcel ("e:/ Test2.xls "," Sheet1 ", user), Em.writetoexcel (" E:/test2.xls "," Sheet1 ", user3);//Read Exceluser user2 = new User (); List List = Em.readfromexcel ("E:/test2.xls", "Sheet1", User2); for (int i = 0; i < list.size (); i++) {User NewUser = (use R) List.get (i);    System.out.println (Newuser.getid () + "" + newuser.getname () + "" + Newuser.getpassword ()); Delete File//system.out.println (Em.deleteexcel ("E:/test2.xls"));}}

The following is a bean class for testing:

Package Module.system.common;public class User {private int id;private string name;private string password;public int Geti D () {return ID;} public void setId (int id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} Public String GetPassword () {return password;} public void SetPassword (String password) {this.password = password;}}

Note: When you create Excel, you need to pass in an array that includes the header information in which the contents of the corresponding Bean class must be of the same value (the number can be different, but the spelling and capitalization must be the same as the lower case)

Java processing Excel file---Excel file creation, delete, write, read