Poi Excel operation details, hssf and xssf

Hssf mode:

Package COM. tools. poi. lesson1; import Java. io. fileinputstream; import Java. io. filenotfoundexception; import Java. io. fileoutputstream; import Java. io. ioexception; import Java. text. parseexception; import Java. text. simpledateformat; 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. userm Odel. hssfrow; import Org. apache. poi. hssf. usermodel. hssfsheet; import Org. apache. poi. hssf. usermodel. hssfworkbook; import Org. apache. poi. hssf. util. hssfcolor; import Org. apache. poi. poifs. filesystem. poifsfilesystem; import Org. apache. poi. SS. usermodel. cell; import Org. apache. poi. SS. usermodel. cellstyle; import COM. tools. poi. bean. student; public class excelutilwithhssf {public static void main (string [] ARGs) {tr Y {getexcelasfile ("AAA");} catch (filenotfoundexception e) {e. printstacktrace ();} catch (ioexception e) {e. printstacktrace ();} // try {// createexceldemo1 (); //} catch (parseexception e) {// E. printstacktrace (); //}/*** get the Excel file and parse the content * @ Param file * @ throws filenotfoundexception * @ throws ioexception */public static void getexcelasfile (string file) throws filenotfoundexception, ioexception {// 1. get Excel Common Object // poifsfilesystem FS = new poifsfilesystem (New fileinputstream ("D:/FTP/test.xls"); poifsfilesystem FS = new poifsfilesystem (New fileinputstream ("D: /FTP/new1.xls "); // 2. obtain the Excel Workbook object hssfworkbook WB = new hssfworkbook (FS); // 3. obtain the Excel worksheet object hssfsheet sheet = WB. getsheetat (0); // The total number of rows int trlength = sheet. getlastrownum (); // 4. obtain the row hssfrow ROW = sheet in the Excel worksheet. getrow (0); // The total number of columns int tdlength = row. getlastcellnum (); // 5. the cell hssfcell cell = row of the row specified in the Excel worksheet is obtained. getcell (short) 1); // 6. the cell style is cellstyle = cell. getcellstyle (); For (INT I = 0; I <trlength; I ++) {// obtain the row hssfrow row1 = sheet in the Excel worksheet. getrow (I); For (Int J = 0; j <tdlength; j ++) {// get the cell hssfcell cell1 = row1.getcell (j) of the row specified in the Excel worksheet ); /*** to handle: Excel exception cannot get a text value from a numeric cell * set the content in all columns to the string format */If (cell1! = NULL) {cell1.setcelltype (cell. cell_type_string);} // obtain the value system in each column. out. print (cell1.getstringcellvalue () + "\ t");} system. out. println () ;}}/*** create an Excel file and write the content */public static void createexcel () {// 1. create an Excel worksheet object hssfworkbook WB = new hssfworkbook (); // 2. create an Excel worksheet object hssfsheet sheet = WB. createsheet ("new sheet"); // 3. create an Excel worksheet row hssfrow ROW = sheet. createrow (6); // 4. create cell style cellstyle = WB. create Cellstyle (); // set these styles. setfillforegroundcolor (hssfcolor. sky_blue.index); cellstyle. setfillpattern (hssfcellstyle. solid_foreground); cellstyle. setborderbottom (hssfcellstyle. border_thin); cellstyle. setborderleft (hssfcellstyle. border_thin); cellstyle. setborderright (hssfcellstyle. border_thin); cellstyle. setbordertop (hssfcellstyle. border_thin); cellstyle. setalignment (hssfcellstyle. align_center); // 5. Create an Excel worksheet to specify the row cell row. createcell (0 ). setcellstyle (cellstyle); // 6. sets the value row of an Excel worksheet. createcell (0 ). setcellvalue ("aaaa"); row. createcell (1 ). setcellstyle (cellstyle); row. createcell (1 ). setcellvalue ("BBBB"); // set the sheet name and cell content WB. setsheetname (0, "first worksheet"); // set the cell content. setcellvalue ("cell content"); // save the file to the specified location. Try {fileoutputstream fout = new fileoutputstream ("E:/students.xls"); WB. write (fout); fout. close ();} catch (EXC Eption e) {e. printstacktrace () ;}}/*** create an Excel instance * @ throws parseexception */public static void createexceldemo1 () throws parseexception {list = new arraylist (); simpledateformat df = new simpledateformat ("yyyy-mm-dd"); Student user1 = new student (1, "James", 16, true, DF. parse ("1997-03-12"); Student user2 = new student (2, "Li Si", 17, true, DF. parse ("1996-08-12"); Student user3 = new student (3, "Wang Wu", 26, False, DF. parse ("1985-11-12"); list. add (user1); list. add (user2); list. add (user3); // Step 1: Create a webbook, corresponding to an Excel file hssfworkbook WB = new hssfworkbook (); // Step 2: Add a sheet to webbook, corresponding to the sheet in the Excel file. createsheet ("Student table 1"); // Step 3: Add row 0th in the table header. Note that the old version of POI has a limit on the number of rows in the Excel worksheet. createrow (INT) 0); // Step 4: Create a cell and set the value header to center the header with hssfcellstyle = WB. createcellstyle (); style. setalig Nment (hssfcellstyle. align_center); // create a center format hssfcell cell = row. createcell (short) 0); cell. setcellvalue ("student ID"); cell. setcellstyle (style); cell = row. createcell (short) 1); cell. setcellvalue ("name"); cell. setcellstyle (style); cell = row. createcell (short) 2); cell. setcellvalue ("Age"); cell. setcellstyle (style); cell = row. createcell (short) 3); cell. setcellvalue ("gender"); cell. setcellstyle (style); cell = row. create Cell (short) 4); cell. setcellvalue ("Birthday"); cell. setcellstyle (style); // Step 5: Write the data into the object data. In actual application, the data is obtained from the database. For (INT I = 0; I <list. size (); I ++) {ROW = sheet. createrow (INT) I + 1); Student Stu = (student) list. get (I); // Step 4: Create a cell and set the row value. createcell (short) 0 ). setcellvalue (double) Stu. GETID (); row. createcell (short) 1 ). setcellvalue (Stu. getname (); row. createcell (short) 2 ). setcellvalue (double) Stu. getage (); RO W. createcell (short) 3). setcellvalue (STU. getsex () = true? "Male": "female"); cell = row. createcell (short) 4); cell. setcellvalue (New simpledateformat ("yyyy-mm-dd "). format (Stu. getbirthday ();} // Step 6: Save the file to the specified location. Try {fileoutputstream fout = new fileoutputstream ("E:/students.xls"); WB. write (fout); fout. close ();} catch (exception e) {e. printstacktrace ();}}}

Xssf method:

Package COM. tools. poi. lesson1; import Java. io. file; import Java. io. fileinputstream; import Java. io. filenotfoundexception; import Java. io. fileoutputstream; import Java. io. ioexception; import Java. io. inputstream; import Java. io. outputstream; import Java. text. parseexception; import Java. text. simpledateformat; import Java. util. arraylist; import Java. util. list; import Org. apache. poi. hssf. usermodel. hssfcell; import o RG. 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. hssfcolor; import Org. apache. poi. openxml4j. exceptions. invalidformatexception; import Org. apache. poi. poifs. filesystem. poifsfilesystem; import Org. apache. poi. SS. usermodel. cell; import Org. A Pache. poi. SS. usermodel. cellstyle; import Org. apache. poi. SS. usermodel. row; import Org. apache. poi. SS. usermodel. sheet; import Org. apache. poi. SS. usermodel. workbook; import Org. apache. poi. SS. usermodel. workbookfactory; import Org. apache. poi. SS. util. workbookutil; import COM. tools. poi. bean. student; public class excelutilwithxssf {public static void main (string [] ARGs) {try {getexcelasfile ("D:/FTP/system report .xls");} Ca Tch (filenotfoundexception e) {e. printstacktrace ();} catch (ioexception e) {e. printstacktrace ();} catch (invalidformatexception e) {e. printstacktrace ();} // try {// createexceldemo1 (); //} catch (parseexception e) {// E. printstacktrace (); //}/*** get excel, use xssf to parse the content of version 2007 and later * @ Param file * @ throws filenotfoundexception * @ throws ioexception * @ throws invalidformatexception */public static void g Etexcelasfile (string file) throws filenotfoundexception, ioexception, invalidformatexception {/// 1. get common Excel objects // poifsfilesystem FS = new poifsfilesystem (New fileinputstream ("D:/FTP/new1.xls"); // 2. obtain the Excel Workbook object // hssfworkbook WB = new hssfworkbook (FS); inputstream ins = NULL; workbook WB = NULL; ins = new fileinputstream (new file (File )); // ins = excelservice. class. getclassloader (). getresourceasstream (Filepath); WB = workbookfactory. create (INS); ins. close (); // 3. obtain the Excel worksheet object sheet = WB. getsheetat (0); // The total number of rows int trlength = sheet. getlastrownum (); // 4. the row = sheet in the Excel worksheet is obtained. getrow (0); // The total number of columns int tdlength = row. getlastcellnum (); // 5. the cell = row of the row specified in the Excel worksheet is obtained. getcell (short) 1); // 6. the cell style is cellstyle = cell. getcellstyle (); For (INT I = 5; I <trlength; I ++) {// obtain the row row1 = sheet in the Excel worksheet. get Row (I); For (Int J = 0; j <tdlength; j ++) {// obtain the cell cell1 = row1.getcell (j) of the row specified in the Excel worksheet ); /*** to handle: Excel exception cannot get a text value from a numeric cell * set the content in all columns to the string format */If (cell1! = NULL) {cell1.setcelltype (cell. cell_type_string);} If (j = 5 & I <= 10) {cell1.setcellvalue ("1000");} // obtain the value system in each column. out. print (cell1 + "");} system. out. println () ;}// Save the modified data outputstream out = new fileoutputstream (File); WB. write (out);}/*** create an Excel file and write the content */public static void createexcel () {// 1. create an Excel worksheet object hssfworkbook WB = new hssfworkbook (); // 2. create an Excel worksheet object hssfsheet sheet = WB. createsheet ("new sheet ");/ /3. create an Excel worksheet row hssfrow ROW = sheet. createrow (6); // 4. create cell style cellstyle = WB. createcellstyle (); // set these styles. setfillforegroundcolor (hssfcolor. sky_blue.index); cellstyle. setfillpattern (hssfcellstyle. solid_foreground); cellstyle. setborderbottom (hssfcellstyle. border_thin); cellstyle. setborderleft (hssfcellstyle. border_thin); cellstyle. setborderright (hssfcellstyle. border_thin); cellstyle. setbor Dertop (hssfcellstyle. border_thin); cellstyle. setalignment (hssfcellstyle. align_center); // 5. create an Excel worksheet to specify the row cell row. createcell (0 ). setcellstyle (cellstyle); // 6. sets the value row of an Excel worksheet. createcell (0 ). setcellvalue ("aaaa"); row. createcell (1 ). setcellstyle (cellstyle); row. createcell (1 ). setcellvalue ("BBBB"); // set the sheet name and cell content WB. setsheetname (0, "first worksheet"); // set the cell content. setcellvalue ("cell content"); // last step, save the file to the specified location try {fileoutputstr Foeam ut = new fileoutputstream ("E:/students.xls"); WB. write (fout); fout. close ();} catch (exception e) {e. printstacktrace () ;}}/*** create an Excel instance * @ throws parseexception */public static void createexceldemo1 () throws parseexception {list = new arraylist (); simpledateformat df = new simpledateformat ("yyyy-mm-dd"); Student user1 = new student (1, "James", 16, true, DF. parse ("1997-03-12"); Student user2 = new Student (2, "Li Si", 17, true, DF. parse (""); Student user3 = new student (3, "Wang Wu", 26, false, DF. parse ("1985-11-12"); list. add (user1); list. add (user2); list. add (user3); // Step 1: Create a webbook, corresponding to an Excel file hssfworkbook WB = new hssfworkbook (); // Step 2: Add a sheet to webbook, corresponding to the sheet in the Excel file. createsheet ("Student table 1"); // Step 3: Add row 0th in the table header. Note that the old version of POI has a limit on the number of rows in the Excel worksheet. createrow (( INT) 0); // Step 4: Create a cell and set the value header to set hssfcellstyle = WB. createcellstyle (); style. setalignment (hssfcellstyle. align_center); // create a center format hssfcell cell = row. createcell (short) 0); cell. setcellvalue ("student ID"); cell. setcellstyle (style); cell = row. createcell (short) 1); cell. setcellvalue ("name"); cell. setcellstyle (style); cell = row. createcell (short) 2); cell. setcellvalue ("Age"); cell. setcellstyle (style); cell = Ro W. createcell (short) 3); cell. setcellvalue ("gender"); cell. setcellstyle (style); cell = row. createcell (short) 4); cell. setcellvalue ("Birthday"); cell. setcellstyle (style); // Step 5: Write the data into the object data. In actual application, the data is obtained from the database. For (INT I = 0; I <list. size (); I ++) {ROW = sheet. createrow (INT) I + 1); Student Stu = (student) list. get (I); // Step 4: Create a cell and set the row value. createcell (short) 0 ). setcellvalue (double) Stu. GETID (); row. createcell (short) 1 ). se Tcellvalue (Stu. getname (); row. createcell (short) 2 ). setcellvalue (double) Stu. getage (); row. createcell (short) 3 ). setcellvalue (Stu. getsex () = true? "Male": "female"); cell = row. createcell (short) 4); cell. setcellvalue (New simpledateformat ("yyyy-mm-dd "). format (Stu. getbirthday ();} // Step 6: Save the file to the specified location. Try {fileoutputstream fout = new fileoutputstream ("E:/students.xls"); WB. write (fout); fout. close ();} catch (exception e) {e. printstacktrace ();}}}

Note:

Modify a content in Excel:

cell1.setCellValue("1000");

Save the modified Excel file:

OutputStream out = new FileOutputStream(file);wb.write(out);