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

The code for this article is my encapsulated Excel processing class, which includes determining whether Excel exists, whether a 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 the object as an argument, the code automatically resolves the entity class properties and methods, code reuse is high.

The code also has some areas that need to be improved and expanded, and you can make simple changes based on 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 Excel to Excel has a header, the contents of each column of the header correspond to the properties of the entity class * * @author NAGSH * */public class Excelmanage {private Hssfworkboo K Workbook = null;/** * Determine 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;} /** * Determine if the file's sheet 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 adding 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 you do not add Sheet will error when opened) Sheet Sheet1 = Workbook.createsheet (sheetname);    New file FileOutputStream out = null;    try {//Add 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);          Determine if the directory or file exists if (!file.exists ()) {//does not exist returns false return flag;                } else {//To determine if the file is to be called if (File.isfile ()) {//is the file when the Delete file Method File.delete ();            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 file INputstream (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 header int columnCount = Sheet.getrow (0). Getlastcellnum ();     try {Row row = Sheet.createrow (RowCount); The latest line to add//To get the field of object by reflection, the corresponding header insert//Gets 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 execute string data = Method.invoke (object). toString ();   Executes 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 number, such as Sheet1) * @param Object Object */publ IC List readfromexcel (String filedir,string sheetname, Object object) {//create Workbookfile file = new file (filedir); try {work Book = new Hssfworkbook (new FileInputStream (file));} catch (FileNotFoundException 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) {returnResult;} 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 (columnindex ). 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 execute--set method parameter is Stringmethod.invoke (obj, data); Execute the method} else if (fieldtypes[columnindex].equals ("int")) {method = Class_.getdeclaredmethod (Methodname,int.class); Set the method to execute--set method parameter is intdouble data_double = double.parsedouble (data), int data_int = (int) data_double;method.invoke (obj , Data_int); Execute the Method}} catch (Exception e) {e.printstacktrace ();}} Result.add (obj);}} return result;} public static void Main (string[] args) {excelmanage em = new Excelmanage ();//Determine if 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);// Determine 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 contains header information that must correspond to the property values of the Bean class (the number can be different, but the spelling and capitalization must be the same)

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