Java read and write Excel file example

Java usually has two methods to manipulate Excel:poi and Jexcelapi, both of which are open source. The POI is developed by Apache, is powerful, supports XLS and xlsx two formats, while Jexcelapi is a Korean company developed, easy to get started, but only supported in XLS format.

Poi sample

Official website: http://www.andykhan.com/jexcelapi/index.html

To create an XLS file:

    public static void testjxmwrite (String[] args)  {         WritableWorkbook workbook = null;         try {            //   Create a workbook             workbook =  Workbook.createworkbook (New file ("Example.xls"));             //  Create Worksheets              Writablesheet sheet = workbook.createsheet ("sheet",  0);             //  Add a table header              label name = new label (0, 0,  "user name");              sheet.addcell (name);            label  Amount = new label (1, 0,  "Amount");             sheet.addcell (amount);             label date = new label (2, 0,  "Time");             sheet.addcell (date);             //  Add Data              Sheet.addcell (New label (0, 1,  "admin"));             sheet.addcell (New jxl.write.number (1, 1, 1000));             DateFormat customDateFormat = new  DateFormat (                     "yyyy mm month DD Day   HH:mm:ss ");            writablecellformat  Dateformat = new writablecellformat (                     customdateformat);             sheet.addcell (New datetime (2, 1, new Date ( ),  dateformat));            //  write file             workbook.write ();         }        catch  (ioexception |  writeexception e)  {             Logger.error ("Error creating file",  e);nbsp;       }        finally {             if  (Workbook != null)  {                if   (Workbook != null)  {                     try {                          Workbook.close ();                     }                     catch  (throwable t)  {            &nBsp;            logger.error ("Off workbook error");                      }                }             }         }    }

Reading xls files

    public static void testjxlread (String[] args)  {         Workbook workbook = null;         try {            //  Read the workbook             workbook =  Workbook.getworkbook (New file ("Example.xls"));             //  reading worksheet             sheet  sheet = workbook.getsheet (0);                         for  (int i=0;  i<sheet.getrows ();  i++)  {                 for  (INT&NBsp;j=0; j<sheet.getcolumns (); j + +)  {                     system.out.print (Sheet.getCell (j, i). getcontents ());                     system.out.print ("\ t");                 }                 system.out.println ();             }        }         catch  (ioexception | biffexception e)  {             logger.error ("Error reading file",  e);         }     &Nbsp;  finally {            if   (Workbook != null)  {                 if  (workbook != null)  {                     try {                          workbook.close ();                     }                     catch  (throwable t)  {                          logGer.error ("Close Workbook error");                     }                 }            }         }    }

Poi sample

Official website: http://poi.apache.org/

Create xlsx file

    public static void testpoiwrite ()  {         try  (Outputstream fileout = new fileoutputstream ("example.xlsx"))  {            //  Create gongzuob             try  (workbook wb = new  Xssfworkbook ())  {                 //  Create Worksheets                  sheet sheet = wb.createsheet ("New sheet");                 CreationHelper createHelper =  Wb.getcreationhelper ();                 //  Create header row                 row row  = sheet.createrow (0);                 row.createcell (0). Setcellvalue ("username");                 row.createcell (1). Setcellvalue ("Amount");                 row.createcell (2). Setcellvalue ("Time");                 //  Add data rows                  row =  Sheet.createrow (1);                 row.createcell (0). Setcellvalue ("admin");               &nbsP; row.createcell (1). Setcellvalue (";          ")       cellstyle cellstyle = wb.createcellstyle ();                 cellstyle.setdataformat ( Createhelper.createdataformat ()                          .getformat ("Yyyy-MM-dd HH:mm:ss"));                 cell cell  = row.createcell (2);                 cell.setcellvalue (New date ());                 cell.setcellstyle (CellStyle);            &Nbsp;    wb.write (fileout);             }        }         catch  (ioexception e)  {             Logger.error ("Error creating file",  e);         }    }

Read xlsx file

    public static void testpoiread (String[] args)  {         dateformat sdf = new simpledateformat ("Yyyy-MM-dd  hh:mm:ss ");        try  (workbook wb =  Workbookfactory.create (New file ("example.xlsx")))  {             for  (SHEET SHEET : WB)  {                 for  (Row row : sheet)  {                      for  (Cell cell : row)  {                         switch  ( Cell.getcelltype ())  {                         case Cell.CELL_TYPE_STRING:                              system.out.print (                                      cell.getrichstringcellvalue (). getString ());                              break;                         case Cell.CELL_TYPE_NUMERIC:                              if  (dateutil.iscelldateformatted (cell))  {                                  system.out.print (                                          sdf.format ( Cell.getdatecellvalue ()));                             }                              else {                                  System.out.print (Cell.getnumericcellvalue ());                             }                              break;                         case  Cell.CELL_TYPE_BOOLEAN:                             system.out.print ( Cell.getbooleancellvalue ());                             break;                         case cell.cell_ type_formula:                             system.out.print (Cell.getCellFormula () );                             break;                         default:                       &Nbsp;      system.out.print ("");                              break;                         }                         system.out.print ("\ t ");                     }                     system.out.println ();                 }             }        }        catch  ( encrypteddocumentexception | invalidformatexception                 | ioexception e)  {             logger.error ("Error reading file",  e);         }    }

Problems with not using Chinese in poi time formatting

Is the POI bug, please refer to the article "Solve POI dateutil.iscelldateformatted (cell cell) can not determine the Chinese date problem", the address is http://huiy.iteye.com/blog/1558860

This article from "Do not accumulate kuibu, not even thousands of Miles" blog, please be sure to keep this source http://wangzhichao.blog.51cto.com/2643325/1730100

Java read and write Excel file example