POI operations Excel explained in detail, HSSF and XSSF two ways

HSSF Road:

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.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.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) {try {getexcelasfile ("AAA");} catch (FileNotFoundException e) {e.printstacktrace ();} catch (IOException e) {e.printstacktrace ();} try {//createexceldemo1 ();//} catch (ParseException e) {//e.printstacktRace ();//}}/** * get Excel and Parse content * @param file * @throws filenotfoundexception * @throws ioexception */public static void ge Texcelasfile (String file) throws FileNotFoundException, ioexception{//1. Get Excel frequently using objects//poifsfilesystem FS = new Poifsfilesystem (New FileInputStream ("D:/ftp/test.xls")); Poifsfilesystem fs = new Poifsfilesystem (New FileInputStream ("D:/ftp/new1.xls")),//2. Get Excel Workbook object Hssfworkbook WB = new Hssfworkbook (fs);//3. Gets the Excel sheet object Hssfsheet sheet = wb.getsheetat (0);//Total row int trlength = Sheet.getlastrownum ();//4. Get the Excel worksheet row Hssfrow row = sheet.getrow (0);//total number of columns int tdlength = Row.getlastcellnum ()//5. Gets the cells of the specified row in the Excel worksheet Hssfcell cell = Row.getcell ((short) 1);//6. Get cell style CellStyle CellStyle = Cell.getcellstyle (); for (int i=0;i<trlength;i++) {// Get the Excel worksheet row Hssfrow row1 = Sheet.getrow (i); for (int j=0;j<tdlength;j++) {//Get the Excel worksheet to specify the row cell Hssfcell cell1 =          Row1.getcell (j);/** * In order to handle: Excel exception cannot get a text value from a numeric cell * All columns are formatted with the contents of string type */if (cell1!=null) { cell1.seTcelltype (cell.cell_type_string); }//gets the value in each column System.out.print (cell1.getstringcellvalue () + "\t\t\t");} System.out.println ();}} /** * Create Excel. and write the contents */public static void Createexcel () {//1. Create an Excel Workbook object Hssfworkbook WB = new Hssfworkbook ();//2. Create an Excel sheet object Hssfshe  ET sheet = wb.createsheet ("new sheet");//3. Create a row for an Excel worksheet Hssfrow row = Sheet.createrow (6); Create a cell style//4 CellStyle      =wb.createcellstyle (); Set these styles Cellstyle.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 that specifies the row of cells Row.createcell (0). Setcellstyle (CellStyle);//6. Sets the value of the Excel worksheet Row.createcell (0). Setcellvalue ( "AAAA"); Row.createcell (1). Setcellstyle (CellStyle); Row.createcell (1). SetcellvaLue ("bbbb");//set sheet name and cell contents wb.setsheetname (0, "first sheet");//Set cell contents Cell.setcellvalue ("cell contents");//Last step. 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 ();}} /** * Create an instance of Excel * @throws parseexception */public static void CreateExcelDemo1 () throws parseexception{list List = new A Rraylist (); SimpleDateFormat df = new SimpleDateFormat ("Yyyy-mm-dd"); Student user1 = new Student (1, "Zhang San", 16,true, Df.parse ("1997-03-12")); Student user2 = new Student (2, "John Doe", 17,true, Df.parse ("1996-08-12")); Student User3 = new Student (3, "Harry", 26,false, Df.parse ("1985-11-12")), List.add (user1); List.add (User2); List.add (User3 );//First step. Create a webbook, corresponding Excel file Hssfworkbook wb = new Hssfworkbook ();//The second step, add a sheet in the WebBook, the corresponding Excel file Sheethssfsheet Sheet = wb.createsheet ("Student form One");//The third step, add the table No. 0 row in the sheet, note that the old version number POI has a limit on the number of rows in Excel shorthssfrow row = Sheet.createrow ((int) 0);//Fourth step, create a cell, and set the value header to set the table header centered Hssfcellstyle style = Wb.createcellstYLE (); style.setalignment (Hssfcellstyle.align_center); Create a center format Hssfcell cell = Row.createcell ((short) 0), Cell.setcellvalue ("study number"), 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.createcell ((short) 4); Cell.setcellvalue ("Birthday"); Cell.setcellstyle (style);//Fifth step, Write entity data in the actual application of this 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);//Fourth step. Create a cell and set the value Row.createcell ((short) 0). Setcellvalue ((double) Stu.getid ()); Row.createcell ((short) 1). Setcellvalue ( 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 ()));}Sixth, 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 Way:

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 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.hssfcolor;import Org.apache.poi.openxml4j.exceptions.invalidformatexception;import Org.apache.poi.poifs.filesystem.poifsfilesystem;import Org.apache.poi.ss.usermodel.cell;import Org.apache.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");} catch ( FileNotFoundException e) {e.printstacktrace (),} catch (IOException e) {e.printstacktrace ();} catch ( Invalidformatexception e) {e.printstacktrace ();} try {//createexceldemo1 (),//} catch (ParseException e) {//e.printstacktrace ();//}}/** * get Excel and parse content to 2007 and above version number Use XSSF to parse * @param file * @throws filenotfoundexception * @throws ioexception * @throws invalidformatexception */public St atic void Getexcelasfile (String file) throws FileNotFoundException, IOException, INVALIDFORMATEXCEPTION{////1. Get Excel frequently using objects//poifsfilesystem fs = new Poifsfilesystem (New FileInputStream ("D:/ftp/new1.xls")); Get Excel Workbook objects/           /hssfworkbook wb = new Hssfworkbook (fs); InputStream ins = null;               Workbook wb = null;               Ins=new fileinputstream (file); ins= Excelservice. Class.getclassloader (). getResourceAsStream (FilePath);               WB = workbookfactory.create (INS);   Ins.close (); 3. Get the Excel sheet object Sheet sheet = wb.getsheetat (0);//total number of int trlength = Sheet.getlastrownum ()//4. Get the Excel worksheet rows Row row = Sheet.getrow (0);///Total number of columns int tdlength = Row.getlastcellnum ();//5. Cells that get an Excel worksheet that specifies the row cell cell = Row.getcell ((short) 1);//6. Get cell style CellStyle CellStyle = Cell.getcellstyle (); for (int i=5;i<trlength;i++) {//Get Excel worksheet rows Row Row1 = Sheet.getrow (i); for (int j=0;j<tdlength;j++) {//To get the cells of the Excel worksheet specified row cell cell1 = Row1.getcell (j);/** * In order to handle: Excel exception cannot get a text     Value from a numeric cell * Sets the contents of all columns to the STRING type format */if (cell1!=null) {cell1.setcelltype (cell.cell_type_string); }if (j==5&&i<=10) {Cell1.setcellvalue ("1000");} Get the value in each column System.out.print (cell1+ "");} System.out.println ();}                Save the modified data outputstream out = new FileOutputStream (file); Wb.write (out);} /** * Create Excel and write content */public static void Createexcel ({//1. Create an Excel Workbook object Hssfworkbook WB = new Hssfworkbook ();//2. Create an Excel sheet object Hssfsheet sheet = wb.createsheet ("new sheet");      3. Create an Excel worksheet row Hssfrow row = Sheet.createrow (6);//4. Create a cell style CellStyle cellstyle =wb.createcellstyle (); Set these styles Cellstyle.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 that specifies the row of cells Row.createcell (0). Setcellstyle (CellStyle);//6. Sets the value of the Excel worksheet Row.createcell (0). Setcellvalue ( "AAAA"); Row.createcell (1). Setcellstyle (CellStyle); Row.createcell (1). Setcellvalue ("bbbb");// Set the sheet name and cell contents wb.setsheetname (0, "first sheet");//Set the cell contents Cell.setcellvalue ("cell contents");//The last step, 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 ();}} /** * Create an instance of Excel * @throws parseexception */public static void CreateExcelDemo1 () throws parseexception{list List = new A Rraylist (); SimpleDateFormat df = new SimpleDateFormat ("Yyyy-mm-dd"); Student user1 = new Student (1, "Zhang San", 16,true, Df.parse ("1997-03-12")); Student user2 = new Student (2, "John Doe", 17,true, Df.parse ("1996-08-12")); Student User3 = new Student (3, "Harry", 26,false, Df.parse ("1985-11-12")), List.add (user1); List.add (User2); List.add (User3 );//First step, create a webbook, corresponding an Excel file Hssfworkbook wb = new Hssfworkbook ();//second step, add a sheet in WebBook, Sheethssfsheet sheet = wb.createsheet ("Student Table One") in the corresponding Excel file;//Third step, add the No. 0 row of the table header in the sheet, note that the old version number POI has a limit on the number of rows in Excel Shorthssfrow row = Sheet.createrow ((int) 0);//Fourth step, create cell, and set the value header to center Hssfcellstyle style = Wb.createcellstyle (); style.setalignment (Hssfcellstyle.align_center); Create a center format Hssfcell cell = Row.createcell ((short) 0), Cell.setcellvalue ("study number"), 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.createcell ((short) 4); Cell.setcellvalue ("Birthday"); Cell.setcellstyle (style);///fifth, write the data from the database in the actual application of the Entity data. for (int i = 0; i < list.size (); i++) {row = Sheet.createrow ((int) i + 1); Student stu = (Student) list.get (i);//Fourth step. Create a cell and set the value Row.createcell ((short) 0). Setcellvalue ((double) Stu.getid ()); Row.createcell ((short) 1). Setcellvalue ( 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 ()));} Sixth, 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 ();}}}

Attention:

To change something in Excel:

Cell1.setcellvalue ("1000");

Save the modified Excel file:

OutputStream out = new FileOutputStream (file), Wb.write (out);

Copyright notice: This article blog original article. Blogs, without consent, may not be reproduced.

POI operations Excel explained in detail, HSSF and XSSF two ways