Java read and write Excel file example

Source: Internet
Author: User
Tags dateformat

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&NBSP;SHEET&NBSP;:&NBSP;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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.