Read XLSX file in Java

Question

I need to read an Excel 2007 XLSX file in a Java application  Does anyone know of a good API to accomplish this task

User · Answer

Have you looked at the poorly obfuscated API?

Nevermind:

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. It does not support the new Excel 2007 .xlsx OOXML file format, which is not OLE2 based.

You might consider using a JDBC-ODBC bridge instead.

User · Answer

I don t know if it is up to date for Excel 2007  but for earlier versions I use the JExcelAPI

User · Answer

Aspose Cells for Java supports XLSX format  You may find more details and further help in Aspose Cells for Java Documentation  Please see if this helps   Disclosure  I work as developer evangelist at Aspose

User · Answer

Try this    Unzip XLSX file Read XML files Compose and use data   Example code       public Workbook getTemplateData String xlsxFile        Workbook workbook   new Workbook        parseSharedStrings xlsxFile       parseWorkesheet xlsxFile  workbook       parseComments xlsxFile  workbook       for  Worksheet worksheet   workbook sheets            worksheet dimension   manager getDimension worksheet              return workbook     private void parseComments String tmpFile  Workbook workbook        try           FileInputStream fin   new FileInputStream tmpFile           final ZipInputStream zin   new ZipInputStream fin           InputStream in   getInputStream zin           while  true                ZipEntry entry   zin getNextEntry                if  entry    null                  break               String name   entry getName                if  name endsWith   xml         NON-NLS-1                  if  name contains COMMENTS                         parseComments in  workbook                                               zin closeEntry                      in close            zin close            fin close          catch  FileNotFoundException e            System out println e         catch  IOException e            e printStackTrace             private void parseComments InputStream in  Workbook workbook        try           DefaultHandler handler   getCommentHandler workbook           SAXParser saxParser   getSAXParser            saxParser parse in  handler         catch  Exception e            e printStackTrace             private DefaultHandler getCommentHandler Workbook workbook        final Worksheet ws   workbook sheets get 0       return new DefaultHandler             String lastTag               private Cell ccell            Override         public void startElement String uri  String localName                  String qName  Attributes attributes  throws SAXException               lastTag   qName              if  lastTag equals  comment                      String cellName   attributes getValue  ref                    int r   manager getRowIndex cellName                   int c   manager getColumnIndex cellName                   Row row   ws rows get r                   if  row    null                        row   new Row                        row index   r                      ws rows put r  row                                     ccell   row cells get c                   if  ccell    null                        ccell   new Cell                        ccell cellName   cellName                      row cells put c  ccell                                                       Override         public void characters char   ch  int start  int length                  throws SAXException               String val                   if  ccell    null  amp  amp  lastTag equals  t                      for  int i   start  i  lt  start   length  i                          val    ch i                                     if  ccell comment    null                      ccell comment   val                  else                       ccell comment    val                                                      private void parseSharedStrings String tmpFile        try           FileInputStream fin   new FileInputStream tmpFile           final ZipInputStream zin   new ZipInputStream fin           InputStream in   getInputStream zin           while  true                ZipEntry entry   zin getNextEntry                if  entry    null                  break              String name   entry getName                if  name endsWith   xml         NON-NLS-1                  if  name startsWith SHARED STRINGS                         parseStrings in                                               zin closeEntry                      in close            zin close            fin close          catch  FileNotFoundException e            System out println e         catch  IOException e            e printStackTrace             public void parseWorkesheet String tmpFile  Workbook workbook        try           FileInputStream fin   new FileInputStream tmpFile           final ZipInputStream zin   new ZipInputStream fin           InputStream in   getInputStream zin           while  true                ZipEntry entry   zin getNextEntry                if  entry    null                  break               String name   entry getName                if  name endsWith   xml         NON-NLS-1                  if  name contains  worksheets                          Worksheet worksheet   new Worksheet                        worksheet name   name                      parseWorksheet in  worksheet                       workbook sheets add worksheet                                               zin closeEntry                      in close            zin close            fin close          catch  FileNotFoundException e            System out println e         catch  IOException e            e printStackTrace             public void parseWorksheet InputStream in  Worksheet worksheet          throws IOException          read sheet1 sharedStrings        styles  strings  formulas         try           DefaultHandler handler   getDefaultHandler worksheet           SAXParser saxParser   getSAXParser            saxParser parse in  handler         catch  SAXException e            e printStackTrace          catch  ParserConfigurationException e            e printStackTrace              where Workbook class   public class Workbook   Integer id   null  public List lt Worksheet gt  sheets   new ArrayList lt Worksheet gt        and Worksheet class   public class Worksheet   public Integer id   null  public String name   null  public String dimension   null  public Map lt Integer  Row gt  rows   new TreeMap lt Integer  Row gt     public Map lt Integer  Column gt  columns   new TreeMap lt Integer  Column gt     public List lt Span gt  spans   new ArrayList lt Span gt        and Row class   public class Row   public Integer id   null  public Integer index   null  public Row tmpRow   null  public Style style   null  public Double height   null  public Map lt Integer Cell gt  cells   new TreeMap lt Integer  Cell gt     public String spans   null  public Integer customHeight   null     and Cell class   public class Cell   public Integer id   null  public Integer rowIndex   null  public Integer colIndex   null  public String cellName   null  public String text   null  public String formula   null  public String comment   null  public Style style   null  public Object value   null  public Cell tmpCell   null     and Column class   public class Column       public Integer index   null      public Style style   null      public String width   null      public Column tmpColumn   null      and Span class   public class Span       Integer id   null      String topLeft   null      String bottomRight   null

User · Answer

Apache POI 3 5 have added support to all the OOXML  docx  xlsx  etc    See the XSSF sub project

User · Answer

I m not very happy with any of the options so I ended up requesting the file in Excel 97 formate   The POI works great for that   Thanks everyone for the help

User · Answer

AFAIK there are no xlsx-libraries available yet  But there are some for old xls   One library is jxls which internally uses the already mentioned POI   2 other links  Handle Excel files  Java libraries to read and write Excel XLS document files

User · Answer

If you want to work with xlsx  you ll have to use the org apache poi ss package  This package has class XSSF  which can be used for parsing xlxs file  This Sample code works on Excel 2007 or later   xlsx   OPCPackage pkg   OPCPackage open new ByteArrayInputStream data                Workbook wb   new XSSFWorkbook pkg               Sheet sheet   wb getSheetAt 0               Iterator lt Row gt  rows   sheet rowIterator             while  rows hasNext                  int j   5              Person person  new Person                 Row row   rows next                if  row getRowNum    gt  0                    person setPersonId  int  row getCell 0  getNumericCellValue                      person setFirstName row getCell 1  getStringCellValue                     person setLastName row getCell 2  getStringCellValue                     person setGroupId  int  row getCell 3  getNumericCellValue                      person setUserName row getCell 4  getStringCellValue                     person setCreditId  int  row getCell 5  getNumericCellValue                                Excel 1998-2003 file   xls  - you may use HSSF library    just use    Workbook wb   new HSSFWorkbook pkg

User · Answer

I had to do this in  NET and I couldn t find any API s out there    My solution was to unzip the  xlsx  and dive right into manipulating the XML    It s not so bad once you create your helper classes and such   There are some  gotchas  like the nodes all have to be sorted according to the way excel expects them  that I didn t find in the official docs   Excel has its own date timestamping  so you ll need to make a conversion formula

User · Answer

AFAIK there are no xlsx-libraries available yet  But there are some for old xls   One library is jxls which internally uses the already mentioned POI   2 other links  Handle Excel files  Java libraries to read and write Excel XLS document files

User · Answer

Aspose Cells for Java supports XLSX format  You may find more details and further help in Aspose Cells for Java Documentation  Please see if this helps   Disclosure  I work as developer evangelist at Aspose

User · Answer

docx4j now covers xlsx as well    Why would you use docx4j to do this   I hear you ask   rather than POI  which focuses on xlsx and binary xls    Probably because you like JAXB  as opposed to XML Beans   or you are already using docx4j for docx or pptx  and need to be able to do some stuff with xlsx as well   Another possible reason is that the jar XML Beans generates from the OpenXML schemas is too big for your purposes   To get around this  POI offers a  lite  subset  the  big  ooxml-schemas-1 0 jar is 14 5 MB  But if you need to support arbitrary spreadsheets  you ll probably need the complete jar   In contrast  the whole of docx4j pptx4j xlsx4j weighs in at about the same as POI s lite subset   If you are processing spreadsheets only  ie not docx or pptx   and preceding paragraph is not a concern for you  then you would probably be best off using POI

User · Answer

I don t know if it is up to date for Excel 2007  but for earlier versions I use the JExcelAPI

User · Answer

This one maybe work for you  it can read write Excel 2007 xlsx file      SmartXLS

User · Answer

docx4j now covers xlsx as well    Why would you use docx4j to do this   I hear you ask   rather than POI  which focuses on xlsx and binary xls    Probably because you like JAXB  as opposed to XML Beans   or you are already using docx4j for docx or pptx  and need to be able to do some stuff with xlsx as well   Another possible reason is that the jar XML Beans generates from the OpenXML schemas is too big for your purposes   To get around this  POI offers a  lite  subset  the  big  ooxml-schemas-1 0 jar is 14 5 MB  But if you need to support arbitrary spreadsheets  you ll probably need the complete jar   In contrast  the whole of docx4j pptx4j xlsx4j weighs in at about the same as POI s lite subset   If you are processing spreadsheets only  ie not docx or pptx   and preceding paragraph is not a concern for you  then you would probably be best off using POI

User · Answer

I had to do this in  NET and I couldn t find any API s out there    My solution was to unzip the  xlsx  and dive right into manipulating the XML    It s not so bad once you create your helper classes and such   There are some  gotchas  like the nodes all have to be sorted according to the way excel expects them  that I didn t find in the official docs   Excel has its own date timestamping  so you ll need to make a conversion formula

User · Answer

This one maybe work for you  it can read write Excel 2007 xlsx file      SmartXLS

User · Answer

Try this    Unzip XLSX file Read XML files Compose and use data   Example code       public Workbook getTemplateData String xlsxFile        Workbook workbook   new Workbook        parseSharedStrings xlsxFile       parseWorkesheet xlsxFile  workbook       parseComments xlsxFile  workbook       for  Worksheet worksheet   workbook sheets            worksheet dimension   manager getDimension worksheet              return workbook     private void parseComments String tmpFile  Workbook workbook        try           FileInputStream fin   new FileInputStream tmpFile           final ZipInputStream zin   new ZipInputStream fin           InputStream in   getInputStream zin           while  true                ZipEntry entry   zin getNextEntry                if  entry    null                  break               String name   entry getName                if  name endsWith   xml         NON-NLS-1                  if  name contains COMMENTS                         parseComments in  workbook                                               zin closeEntry                      in close            zin close            fin close          catch  FileNotFoundException e            System out println e         catch  IOException e            e printStackTrace             private void parseComments InputStream in  Workbook workbook        try           DefaultHandler handler   getCommentHandler workbook           SAXParser saxParser   getSAXParser            saxParser parse in  handler         catch  Exception e            e printStackTrace             private DefaultHandler getCommentHandler Workbook workbook        final Worksheet ws   workbook sheets get 0       return new DefaultHandler             String lastTag               private Cell ccell            Override         public void startElement String uri  String localName                  String qName  Attributes attributes  throws SAXException               lastTag   qName              if  lastTag equals  comment                      String cellName   attributes getValue  ref                    int r   manager getRowIndex cellName                   int c   manager getColumnIndex cellName                   Row row   ws rows get r                   if  row    null                        row   new Row                        row index   r                      ws rows put r  row                                     ccell   row cells get c                   if  ccell    null                        ccell   new Cell                        ccell cellName   cellName                      row cells put c  ccell                                                       Override         public void characters char   ch  int start  int length                  throws SAXException               String val                   if  ccell    null  amp  amp  lastTag equals  t                      for  int i   start  i  lt  start   length  i                          val    ch i                                     if  ccell comment    null                      ccell comment   val                  else                       ccell comment    val                                                      private void parseSharedStrings String tmpFile        try           FileInputStream fin   new FileInputStream tmpFile           final ZipInputStream zin   new ZipInputStream fin           InputStream in   getInputStream zin           while  true                ZipEntry entry   zin getNextEntry                if  entry    null                  break              String name   entry getName                if  name endsWith   xml         NON-NLS-1                  if  name startsWith SHARED STRINGS                         parseStrings in                                               zin closeEntry                      in close            zin close            fin close          catch  FileNotFoundException e            System out println e         catch  IOException e            e printStackTrace             public void parseWorkesheet String tmpFile  Workbook workbook        try           FileInputStream fin   new FileInputStream tmpFile           final ZipInputStream zin   new ZipInputStream fin           InputStream in   getInputStream zin           while  true                ZipEntry entry   zin getNextEntry                if  entry    null                  break               String name   entry getName                if  name endsWith   xml         NON-NLS-1                  if  name contains  worksheets                          Worksheet worksheet   new Worksheet                        worksheet name   name                      parseWorksheet in  worksheet                       workbook sheets add worksheet                                               zin closeEntry                      in close            zin close            fin close          catch  FileNotFoundException e            System out println e         catch  IOException e            e printStackTrace             public void parseWorksheet InputStream in  Worksheet worksheet          throws IOException          read sheet1 sharedStrings        styles  strings  formulas         try           DefaultHandler handler   getDefaultHandler worksheet           SAXParser saxParser   getSAXParser            saxParser parse in  handler         catch  SAXException e            e printStackTrace          catch  ParserConfigurationException e            e printStackTrace              where Workbook class   public class Workbook   Integer id   null  public List lt Worksheet gt  sheets   new ArrayList lt Worksheet gt        and Worksheet class   public class Worksheet   public Integer id   null  public String name   null  public String dimension   null  public Map lt Integer  Row gt  rows   new TreeMap lt Integer  Row gt     public Map lt Integer  Column gt  columns   new TreeMap lt Integer  Column gt     public List lt Span gt  spans   new ArrayList lt Span gt        and Row class   public class Row   public Integer id   null  public Integer index   null  public Row tmpRow   null  public Style style   null  public Double height   null  public Map lt Integer Cell gt  cells   new TreeMap lt Integer  Cell gt     public String spans   null  public Integer customHeight   null     and Cell class   public class Cell   public Integer id   null  public Integer rowIndex   null  public Integer colIndex   null  public String cellName   null  public String text   null  public String formula   null  public String comment   null  public Style style   null  public Object value   null  public Cell tmpCell   null     and Column class   public class Column       public Integer index   null      public Style style   null      public String width   null      public Column tmpColumn   null      and Span class   public class Span       Integer id   null      String topLeft   null      String bottomRight   null

User · Answer

AFAIK there are no xlsx-libraries available yet  But there are some for old xls   One library is jxls which internally uses the already mentioned POI   2 other links  Handle Excel files  Java libraries to read and write Excel XLS document files

User · Answer

This one maybe work for you  it can read write Excel 2007 xlsx file      SmartXLS

User · Answer

Have you looked at the poorly obfuscated API?

Nevermind:

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. It does not support the new Excel 2007 .xlsx OOXML file format, which is not OLE2 based.

You might consider using a JDBC-ODBC bridge instead.

User · Answer

This one maybe work for you  it can read write Excel 2007 xlsx file      SmartXLS

User · Answer

Have you looked at the poorly obfuscated API?

Nevermind:

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. It does not support the new Excel 2007 .xlsx OOXML file format, which is not OLE2 based.

You might consider using a JDBC-ODBC bridge instead.

User · Answer

I had to do this in  NET and I couldn t find any API s out there    My solution was to unzip the  xlsx  and dive right into manipulating the XML    It s not so bad once you create your helper classes and such   There are some  gotchas  like the nodes all have to be sorted according to the way excel expects them  that I didn t find in the official docs   Excel has its own date timestamping  so you ll need to make a conversion formula

User · Answer

I don t know if it is up to date for Excel 2007  but for earlier versions I use the JExcelAPI

User · Answer

You can use Apache Tika for that   String parse File xlsxFile        return new Tika   parseToString xlsxFile       Tika uses Apache POI for parsing XLSX files   Here are some usage examples for Tiki   Alternatively  if you want to handle each cell of the spreadsheet individually  here s one way to do this with POI    void parse File xlsx        try  XSSFWorkbook workbook   new XSSFWorkbook xlsx                Handle each cell in each sheet         workbook forEach sheet - gt  sheet forEach row - gt  row forEach this  handle               catch  InvalidFormatException   IOException e            System out println  Can t parse file     xlsx            void handle Cell cell        final String cellContent      switch  cell getCellType              case Cell CELL TYPE STRING              cellContent   cell getStringCellValue                break          case Cell CELL TYPE NUMERIC              cellContent   String valueOf cell getNumericCellValue                 break          case Cell CELL TYPE BOOLEAN              cellContent   String valueOf cell getBooleanCellValue                 break          default              cellContent    Don t know how to handle cell     cell            System out println cellContent

User · Answer

If you want to work with xlsx  you ll have to use the org apache poi ss package  This package has class XSSF  which can be used for parsing xlxs file  This Sample code works on Excel 2007 or later   xlsx   OPCPackage pkg   OPCPackage open new ByteArrayInputStream data                Workbook wb   new XSSFWorkbook pkg               Sheet sheet   wb getSheetAt 0               Iterator lt Row gt  rows   sheet rowIterator             while  rows hasNext                  int j   5              Person person  new Person                 Row row   rows next                if  row getRowNum    gt  0                    person setPersonId  int  row getCell 0  getNumericCellValue                      person setFirstName row getCell 1  getStringCellValue                     person setLastName row getCell 2  getStringCellValue                     person setGroupId  int  row getCell 3  getNumericCellValue                      person setUserName row getCell 4  getStringCellValue                     person setCreditId  int  row getCell 5  getNumericCellValue                                Excel 1998-2003 file   xls  - you may use HSSF library    just use    Workbook wb   new HSSFWorkbook pkg

User · Answer

I don t know if it is up to date for Excel 2007  but for earlier versions I use the JExcelAPI

User · Answer

AFAIK there are no xlsx-libraries available yet  But there are some for old xls   One library is jxls which internally uses the already mentioned POI   2 other links  Handle Excel files  Java libraries to read and write Excel XLS document files

User · Answer

I m not very happy with any of the options so I ended up requesting the file in Excel 97 formate   The POI works great for that   Thanks everyone for the help

User · Answer

Might be a little late  but the beta POI now supports xlsx

User · Answer

I m not very happy with any of the options so I ended up requesting the file in Excel 97 formate   The POI works great for that   Thanks everyone for the help

User · Answer

Apache POI 3 5 have added support to all the OOXML  docx  xlsx  etc    See the XSSF sub project

User · Answer

Have you looked at the poorly obfuscated API?

Nevermind:

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. It does not support the new Excel 2007 .xlsx OOXML file format, which is not OLE2 based.

You might consider using a JDBC-ODBC bridge instead.

User · Answer

Might be a little late  but the beta POI now supports xlsx

User · Answer

I m not very happy with any of the options so I ended up requesting the file in Excel 97 formate   The POI works great for that   Thanks everyone for the help

User · Answer

You can use Apache Tika for that   String parse File xlsxFile        return new Tika   parseToString xlsxFile       Tika uses Apache POI for parsing XLSX files   Here are some usage examples for Tiki   Alternatively  if you want to handle each cell of the spreadsheet individually  here s one way to do this with POI    void parse File xlsx        try  XSSFWorkbook workbook   new XSSFWorkbook xlsx                Handle each cell in each sheet         workbook forEach sheet - gt  sheet forEach row - gt  row forEach this  handle               catch  InvalidFormatException   IOException e            System out println  Can t parse file     xlsx            void handle Cell cell        final String cellContent      switch  cell getCellType              case Cell CELL TYPE STRING              cellContent   cell getStringCellValue                break          case Cell CELL TYPE NUMERIC              cellContent   String valueOf cell getNumericCellValue                 break          case Cell CELL TYPE BOOLEAN              cellContent   String valueOf cell getBooleanCellValue                 break          default              cellContent    Don t know how to handle cell     cell            System out println cellContent

User · Answer

I had to do this in  NET and I couldn t find any API s out there    My solution was to unzip the  xlsx  and dive right into manipulating the XML    It s not so bad once you create your helper classes and such   There are some  gotchas  like the nodes all have to be sorted according to the way excel expects them  that I didn t find in the official docs   Excel has its own date timestamping  so you ll need to make a conversion formula

[java] Read XLSX file in Java

Examples related to java

Examples related to excel

Examples related to parsing