Java poi Excel encapsulation example

Recently, due to work requirements, a tool class for Excel operations is based on poi. Share with you.

Package COM. shine. framework. core. office; import Java. io. fileinputstream; import Java. io. filenotfoundexception; import Java. io. fileoutputstream; import Java. io. ioexception; import Java. util. arraylist; import Java. util. list; 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. hssfr Ow; import Org. apache. poi. hssf. usermodel. hssfsheet; import Org. apache. poi. hssf. usermodel. hssfworkbook; import COM. shine. framework. core. util. fileutil; /*** Java Excel encapsulation *** @ author ken * @ blog http://blog.csdn.net/arjick/article/details/8182484 **/public class excelutils {/*** create excelfile *** @ Param excelpath * @ return */ public static Boolean createexcelfile (string excelpath) {hssfworkbook workbook = New hssfworkbook (); Return outputhssfworkbook (workbook, excelpath );} /*** Insert a new worksheet ** @ Param excelpath * @ Param sheetname * @ return */public static Boolean insertsheet (string excelpath, string sheetname) {try {If (fileutil. checkfile (excelpath) {hssfworkbook workbook = excelutils. gethssfworkbook (excelpath); hssfsheet sheet = workbook. createsheet (sheetname); Return outputhssfworkbook (workbook, exce Lpath);} else {system. out. println (excelpath + ": the file does not exist ..... ");} return false;} catch (exception e) {e. printstacktrace ();} return false ;} /*** copy a worksheet ** @ Param excelpath * @ Param sheetname * @ Param sheetnum * @ return */public static Boolean copysheet (string excelpath, string sheetname, int formsheetnum) {try {If (fileutil. checkfile (excelpath) {hssfworkbook workbook = excelutils. gethssfworkbook (excelpa Th); If (! Excelutils. checksheet (workbook, sheetname) {workbook. clonesheet (formsheetnum); workbook. setsheetname (workbook. getnumberofsheets ()-1, sheetname); Return outputhssfworkbook (workbook, excelpath);} else {system. out. println (excelpath + ": a worksheet with the same name exists" + sheetname + "..... ") ;}} else {system. out. println (excelpath + ": the file does not exist ..... ");} return false;} catch (exception e) {e. printstacktrace ();} return false;}/*** insert or Update the cell ** @ Param excelpath * @ Param sheetname * @ Param rownum * @ Param cellnum * @ Param value * @ return */public static Boolean insertorupdatecell (string excelpath, string sheetname, int rownum, int cellnum, string value) {try {If (fileutil. checkfile (excelpath) {hssfworkbook WB = gethssfworkbook (excelpath); If (excelutils. checksheet (WB, sheetname) {hssfsheet sheet = WB. getsheet (sheetname); hssfrow Row = NULL; If (sheet. getlastrownum () <rownum) {ROW = sheet. createrow (rownum);} else {ROW = sheet. getrow (rownum);} hssfcell cell = row. getcell (cellnum); cell. setcelltype (hssfcell. cell_type_string); cell. setcellvalue (value); Return outputhssfworkbook (WB, excelpath);} else {system. out. println (excelpath + "" + sheetname + ": the worksheet does not exist ..... ") ;}} else {system. out. println (excelpath + ": the file does not exist ..... ");} return Fals E;} catch (exception e) {e. printstacktrace ();} return false ;} /*** insert or update a cell ** @ Param excelpath * @ Param sheetindex * @ Param rownum * @ Param cellnum * @ Param value * @ return */public static Boolean insertorupdatecell (string excelpath, int sheetindex, int rownum, int cellnum, string value) {try {If (fileutil. checkfile (excelpath) {hssfworkbook WB = gethssfworkbook (excelpath); If (excelutils. checkshe Et (WB, sheetindex) {hssfsheet sheet = WB. getsheetat (sheetindex); hssfrow ROW = NULL; If (sheet. getlastrownum () <rownum) {ROW = sheet. createrow (rownum);} else {ROW = sheet. getrow (rownum);} hssfcell cell = row. getcell (cellnum); cell. setcelltype (hssfcell. cell_type_string); cell. setcellvalue (value); Return outputhssfworkbook (WB, excelpath);} else {system. out. println (excelpath + "" + sheetindex + ": the worksheet does not exist. .... ") ;}} Else {system. out. println (excelpath + ": the file does not exist ..... ");} return false;} catch (exception e) {e. printstacktrace ();} return false ;} /***** @ Param excelpath * @ Param sheetindex * @ Param rownum * @ Param cellnum * @ Param value * @ Param style * @ return */public static Boolean insertorupdatecell (string excelpath, int sheetindex, int rownum, int cellnum, string value, hssfcellstyle style) {try {If (Fi Leutil. checkfile (excelpath) {hssfworkbook WB = gethssfworkbook (excelpath); If (excelutils. checksheet (WB, sheetindex) {hssfsheet sheet = WB. getsheetat (sheetindex); hssfrow ROW = NULL; If (sheet. getlastrownum () <rownum) {ROW = sheet. createrow (rownum);} else {ROW = sheet. getrow (rownum);} hssfcell cell = row. createcell (cellnum); // cell. setcelltype (hssfcell. cell_type_string); hssffont font = WB. createfon T (); font. setfontheight (short) 18); hssfcellstyle style1 = WB. createcellstyle (); style1.setfont (font); // cell. setcellstyle (style1); cell. setcellvalue (value); system. out. println (value); Return outputhssfworkbook (WB, excelpath);} else {system. out. println ("insertorupdatecell:" + excelpath + "" + sheetindex + ": the worksheet does not exist ..... ") ;}} else {system. out. println (excelpath + ": the file does not exist ..... ");} return false;} catch (exce Ption e) {e. printstacktrace ();} return false ;} /*** insert specified row data ** @ Param excelpath * @ Param sheetname * @ Param rownum * @ Param values * @ return */public static Boolean insertorupadaterowdatas (string excelpath, string sheetname, int rownum, string... values) {try {for (INT I = 0; I <values. length; I ++) {insertorupdatecell (excelpath, sheetname, rownum, I, values [I]);} return false;} catch (exception E) {E. printstacktrace ();} return false;}/*** read Excel ** @ Param excelpath * @ return * @ throws exception */public static hssfworkbook gethssfworkbook (string excelpath) throws exception {fileinputstream FS = NULL; try {FS = new fileinputstream (excelpath); hssfworkbook WB = new hssfworkbook (FS); Return WB;} catch (exception E) {Throw E;} finally {try {If (FS! = NULL) FS. close ();} catch (exception e) {e. printstacktrace () ;}}/ *** input Excel ** @ Param WB * @ Param excelpath * @ return */Private Static Boolean outputhssfworkbook (hssfworkbook WB, string excelpath) {fileoutputstream fout = NULL; try {fout = new fileoutputstream (excelpath); WB. write (fout); fout. flush (); system. out. println (excelpath + ": file generation... "); Return true;} catch (filenotfoundexception e) {// todo from Generate Catch Block E. printstacktrace ();} catch (ioexception e) {// todo automatically generate Catch Block E. printstacktrace ();} finally {try {If (fout! = NULL) fout. close ();} catch (exception e) {e. printstacktrace () ;}} return false;}/*** check whether a worksheet exists ** @ Param excelpath * @ Param sheetname * @ return */public static Boolean checksheet (hssfworkbook WB, string sheetname) {try {for (INT numsheets = 0; numsheets <WB. getnumberofsheets (); numsheets ++) {hssfsheet sheet = WB. getsheetat (numsheets); If (sheetname. equals (sheet. getsheetname () {return true ;}} retu Rn false;} catch (exception e) {e. printstacktrace ();} return false;}/*** check whether the number of tables exists ** @ Param WB * @ Param sheetindex * @ return */public static Boolean checksheet (hssfworkbook WB, int sheetindex) {try {If (WB. getnumberofsheets ()> sheetindex) return true; return false;} catch (exception e) {e. printstacktrace ();} return false;}/*** clear all content in the worksheet of the specified Excel worksheet ** @ Param excelpath * @ Param sheet * @ return */publ IC static Boolean cleanexcelfile1 (string excelpath, string sheetname) {try {If (fileutil. checkfile (excelpath) {hssfworkbook WB = gethssfworkbook (excelpath); If (excelutils. checksheet (WB, sheetname) {hssfsheet sheet = WB. getsheet (sheetname); For (INT I = 0; I <sheet. getlastrownum () + 1; I ++) {If (sheet. getrow (I )! = NULL) sheet. removerow (sheet. getrow (I);} return outputhssfworkbook (WB, excelpath);} else {system. out. println ("cleanexcelfile:" + excelpath + "" + sheetname + ": the worksheet does not exist ..... ") ;}} else {system. out. println (excelpath + ": the file does not exist ..... ");} return false;} catch (exception e) {e. printstacktrace ();} return false;}/*** obtain the number of rows in the specified workbook ** @ Param excelpath * @ Param sheetname * @ return */public static int getexcelsheetr Ownum (string excelpath, string sheetname) {try {If (fileutil. checkfile (excelpath) {hssfworkbook WB = gethssfworkbook (excelpath); If (excelutils. checksheet (WB, sheetname) {hssfsheet sheet = WB. getsheet (sheetname); Return sheet. getlastrownum () + 1;} else {system. out. println ("getexcelsheetrownum:" + excelpath + "" + sheetname + ": the worksheet does not exist ..... ") ;}} else {system. out. println (excelpath + ": the file does not exist ..... ");} r Eturn 0;} catch (exception e) {e. printstacktrace ();} return 0;}/*** Delete row ** @ excelpath * @ Param sheetname * @ Param row * @ return */public static Boolean deleterow (string excelpath, string sheetname, int row) {If (getexcelsheetrownum (excelpath, sheetname)> row) {try {If (fileutil. checkfile (excelpath) {hssfworkbook WB = gethssfworkbook (excelpath); If (excelutils. checksheet (WB, sheetname) {hssfshee T sheet = WB. getsheet (sheetname); If (sheet. getrow (ROW )! = NULL) sheet. removerow (sheet. getrow (ROW); Return outputhssfworkbook (WB, excelpath);} else {system. out. println ("deleterow:" + excelpath + "" + sheetname + ": the worksheet does not exist ..... ") ;}} else {system. out. println (excelpath + ": the file does not exist ..... ");} return false;} catch (exception e) {e. printstacktrace () ;}} else {system. out. println ("the specified row does not exist"); Return true;} return false;}/*** get all data ** @ excelpath * @ Param sheetname * @ retu Rn */public static list <list> getalldata (string excelpath, string sheetname) {try {list <list> List = new arraylist <list> (); If (fileutil. checkfile (excelpath) {hssfworkbook WB = gethssfworkbook (excelpath); If (excelutils. checksheet (WB, sheetname) {hssfsheet sheet = WB. getsheet (sheetname); For (INT I = 0; I <sheet. getlastrownum () + 1; I ++) {If (sheet. getrow (I )! = NULL) {list <string> rowlist = new arraylist <string> (); hssfrow Arow = sheet. getrow (I); For (INT cellnumofrow = 0; cellnumofrow <Arow. getlastcellnum (); cellnumofrow ++) {If (null! = Arow. getcell (cellnumofrow) {hssfcell acell = Arow. getcell (cellnumofrow); // obtain the column value if (acell. getcelltype () = hssfcell. cell_type_string) {rowlist. add (acell. getstringcellvalue ();} else if (acell. getcelltype () = hssfcell. cell_type_numeric) {rowlist. add (string. valueof (acell. getnumericcellvalue ()). replace (". 0 "," ") ;}} else {rowlist. add ("") ;}} list. add (rowlist) ;}} return list;} else {system. out. println ("Getalldata:" + excelpath + "" + sheetname + ": the worksheet does not exist ..... ") ;}} else {system. out. println (excelpath + ": the file does not exist ..... ") ;}} catch (exception e) {e. printstacktrace ();} return NULL ;} /*** read some data ** @ Param excelpath * @ Param sheetname * @ Param start * @ Param end * @ return */public static list <list> getdatas (string excelpath, string sheetname, int start, int end) {try {list <list> List = new arraylist <list> (); If (fileutil. checkfile (excelpath) {hssfworkbook WB = gethssfworkbook (excelpath); If (excelutils. checksheet (WB, sheetname) {hssfsheet sheet = WB. getsheet (sheetname); For (INT I = start-1; I <end; I ++) {If (sheet. getrow (I )! = NULL) {list <string> rowlist = new arraylist <string> (); hssfrow Arow = sheet. getrow (I); For (INT cellnumofrow = 0; cellnumofrow <Arow. getlastcellnum (); cellnumofrow ++) {If (null! = Arow. getcell (cellnumofrow) {hssfcell acell = Arow. getcell (cellnumofrow); // obtain the column value if (acell. getcelltype () = hssfcell. cell_type_string) {rowlist. add (acell. getstringcellvalue ();} else if (acell. getcelltype () = hssfcell. cell_type_numeric) {rowlist. add (string. valueof (acell. getnumericcellvalue ()). replace (". 0 "," ") ;}} else {rowlist. add ("") ;}} list. add (rowlist) ;}} return list;} else {system. out. println ("Getdatas:" + excelpath + "" + sheetname + ": the worksheet does not exist ..... ") ;}} else {system. out. println (excelpath + ": the file does not exist ..... ") ;}} catch (exception e) {e. printstacktrace ();} return NULL ;} /*** get specified row and column data ** @ Param excelpath * @ Param sheetname * @ Param row * @ Param cellnum * @ return */public static string getdata (string excelpath, string sheetname, int row, int cellnum) {If (getexcelsheetrownum (excelpath, sheetname )> Row) {try {If (fileutil. checkfile (excelpath) {hssfworkbook WB = gethssfworkbook (excelpath); If (excelutils. checksheet (WB, sheetname) {hssfsheet sheet = WB. getsheet (sheetname); hssfcell acell = sheet. getrow (ROW ). getcell (cellnum); If (acell! = NULL) return acell. getstringcellvalue ();} else {system. out. println ("getdata" + excelpath + "" + sheetname + ": the worksheet does not exist ..... ") ;}} else {system. out. println (excelpath + ": the file does not exist ..... ") ;}} catch (exception e) {e. printstacktrace () ;}} return NULL;}/*** @ Param ARGs */public static void main (string [] ARGs) {// create a new excle // excelutils. createexcelfile ("D: \ test.xls"); // Insert a new worksheet // excelutils. insertsheet ("D: \ test.xls", "333"); // check whether a worksheet exists // hssfworkbook WB; // try {// WB = gethssfworkbook ("D: \ test.xls "); // system. out. println (excelutils. checksheet (WB, "3334"); //} catch (exception e) {// todo auto-generated Catch Block // E. printstacktrace (); // insert a row of Data // excelutils. insertorupadaterowdatas ("D: \ test.xls", "333", 5, "123", // "0000"); // insert cell data // excelutils. insertorupdatecell ("D: \ test.xls", "123", 5, 1, "0000"); // delete all row data of the specified worksheet, // excelutils. cleanexcelfile ("D: \ test.xls", "123"); // delete a specified row // excelutils. deleterow ("D: \ test.xls", "123", 0); // obtain all data // list <list> List = excelutils. getalldata ("D: \ test.xls", "333"); // For (INT I = 0; I <list. size (); I ++) {// list <string> rowlist = List. get (I); // For (Int J = 0; j <rowlist. size (); j ++) {// system. out. println (rowlist. get (j); //} // obtain partial data // list <list> List = excelutils. getdatas (// "C: \ Users \ Ken \ Desktop \ test name list .xls", "sheet1", 5,374); // For (INT I = 0; I <list. size (); I ++) {// list <string> rowlist = List. get (I); // For (Int J = 0; j <rowlist. size (); j ++) {// system. out. print (rowlist. get (j) + ""); //} // system. out. println (); // obtain the specified row and column data // system. out. println (excelutils. getdata ("d :\\ test.xls", "333", 5, 1); // copy the specified worksheet excelutils. copysheet ("d :\\ test room template 2.xls"," 333 ", 0 );}}