Using POI to read and write Excel

For a programmer, file operations are frequently encountered, especially for Excel files.

Here is a brief introduction to the tool I used in my project,--poi, for Excel. About the concept of poi, a lot of online, detailed information you can own Baidu, I only do a brief introduction here. POI is an Apache class library that provides Java developers with support for processing Office files (Word, ppt, Excel). I'll give you a few examples to illustrate this.

Preparatory work

The version I use is 3.10.1, and the required jars are:

Dom4j.jar Log4j-1.2.13.jar Poi-3.10.1-20140818.jar Poi-ooxml-3.10.1-20140818.jar Poi-ooxml-schemas-3.10.1-20140818.jar Poi-scratchpad-3.10.1-20140818.jar code Example 1. Reading Excel

public void Testreadexcel () {
  try {
    //Read Excel
    Workbook wb = new Hssfworkbook (New FileInputStream ("D:\\2.xls "));

    Gets the number of Sheet for
    (int t = 0; t < wb.getnumberofsheets (); t++) {
      Sheet Sheet = Wb.getsheetat (t);
      row row = null;
      int lastrownum = Sheet.getlastrownum ();

      Loop read for
      (int i = 0; I <= lastrownum; i++) {
        row = Sheet.getrow (i);
        if (row!= null) {
          //Get the value of each column for
          (int j = 0; J < Row.getlastcellnum (); j + +) {
            cell cell = Row.getcell (j );
            String value = getcellvalue (cell);
            if (!value.equals (")) {
              System.out.print (value +" | ");
            }
          System.out.println ();}}}
  catch (Exception e) {
    e.printstacktrace ();
  }
}

A method was used:

/*** * Read the value of a cell * @Title: Getcellvalue * @Date: 2014-9-11 morning 10:52:07 * @param cell * @return *
  /private String Getcellvalue (cell cell) {Object result = ""; if (cell!= null) {switch (Cell.getcelltype ()) {Case Cell.CELL_TYPE_STRING:result = Cell.getstringcellval
      UE ();
    Break
      Case Cell.CELL_TYPE_NUMERIC:result = Cell.getnumericcellvalue ();
    Break
      Case Cell.CELL_TYPE_BOOLEAN:result = Cell.getbooleancellvalue ();
    Break
      Case Cell.CELL_TYPE_FORMULA:result = Cell.getcellformula ();
    Break
      Case Cell.CELL_TYPE_ERROR:result = Cell.geterrorcellvalue ();
    Break
    Case Cell.CELL_TYPE_BLANK:break;
    Default:break;
} return result.tostring (); }

To explain, first read the file into the workbook workbook, workbook is an interface, he has 2 implementations: Hssfworkbook and Xssfworkbook. The former is used to read version 97-03 of Excel, the extension is XLS, the latter is read 07 and later versions, the extension is xlsx. Read into the workbook, and then loop through all the sheet, all valid rows and valid columns in the sheet loop. where Sheet.getlastrownum () gets the index value (starting from 0) of the last row, and Sheet.getphysicalnumberofrows () is the line number (starting from 1) of the last row obtained. Notice here that the Loop column is not looping through the sheet, but rather loops in the row.
The effect chart is as follows:


code Example 2. Writing to Excel files

public void Testwriteexcel () {String Excelpath = "D:/3.xls";
  Workbook workbook = null; try {//Xssfworkbook used for. XSLX (>= 2007), Hssworkbook to. xsl workbook = new Hssfworkbook ();//XSSFWO
  Rkbook ();//workbookfactory.create (InputStream);
    catch (Exception e) {System.out.println ("Create Excel failed:");
  E.printstacktrace ();
    } if (workbook!= null) {Sheet Sheet = workbook.createsheet ("test data");
    Row row0 = sheet.createrow (0);
      for (int i = 0; i < 6; i++) {Cell cell = Row0.createcell (i, cell.cell_type_string);
      Cell.setcellvalue ("column title" + i); Sheet.autosizecolumn (i);//Auto Adjust width} for (int rownum = 1; rownum < rownum++) {row row = Sheet.create
      Row (rownum);
        for (int i = 0; i < 6; i++) {Cell cell = Row.createcell (i, cell.cell_type_string);
      Cell.setcellvalue ("Cells" + string.valueof (rownum + 1) + string.valueof (i + 1)); } try {FileOutputStream outputstrEAM = new FileOutputStream (Excelpath);
      Workbook.write (OutputStream);
      Outputstream.flush ();
    Outputstream.close ();
      catch (Exception e) {System.out. println ("Write Excel failed:");
    E.printstacktrace ();
 }
  }
}

The effect chart is as follows:
How, very simple. As long as you use the POI, regardless of whether you have Office installed, you can perfect the operation of Office files, small friends, come and try it.