How to read and write excel file

Question

I want to read and write an Excel file from Java with 3 columns and N rows  printing one string in each cell   Can anyone give me simple code snippet for this  Do I need to use any external lib or does Java have built-in support for it   I want to do the following   for i 0  i  lt rows  i           read  i col1    i col2    i col3   for i 0  i lt rows  i          write  i col1    i col2    i col3

User · Answer

For reading a xlsx file we can use Apache POI libs Try this   public static void readXLSXFile   throws IOException               InputStream ExcelFileToRead   new FileInputStream  C  Test xlsx            XSSFWorkbook  wb   new XSSFWorkbook ExcelFileToRead            XSSFWorkbook test   new XSSFWorkbook              XSSFSheet sheet   wb getSheetAt 0           XSSFRow row           XSSFCell cell           Iterator rows   sheet rowIterator             while  rows hasNext                          row  XSSFRow  rows next                Iterator cells   row cellIterator                while  cells hasNext                                  cell  XSSFCell  cells next                     if  cell getCellType      XSSFCell CELL TYPE STRING                                        System out print cell getStringCellValue                                           else if cell getCellType      XSSFCell CELL TYPE NUMERIC                                        System out print cell getNumericCellValue                                           else                                         U Can Handel Boolean  Formula  Errors                                             System out println

User · Answer

There is a new easy and very cool tool  10x to Kfir   xcelite  Write   public class User        Column  name  Firstname     private String firstName      Column  name  Lastname     private String lastName      Column   private long id       Column   private Date birthDate      Xcelite xcelite   new Xcelite        XceliteSheet sheet   xcelite createSheet  users    SheetWriter lt User gt  writer   sheet getBeanWriter User class   List lt User gt  users   new ArrayList lt User gt           fill up users writer write users    xcelite write new File  users doc xlsx       Read   Xcelite xcelite   new Xcelite new File  users doc xlsx     XceliteSheet sheet   xcelite getSheet  users    SheetReader lt User gt  reader   sheet getBeanReader User class   Collection lt User gt  users   reader read

User · Answer

You need Apache POI library  and this code below should help you              import java io File      import java io FileInputStream      import java io FileNotFoundException      import java io FileOutputStream      import java io IOException      import java util ArrayList      import java util List      import java util Iterator                                                                          import org apache poi ss usermodel Sheet      import org apache poi ss usermodel Cell      import org apache poi ss usermodel Row      import org apache poi ss usermodel Workbook      import org apache poi xssf usermodel XSSFSheet      import org apache poi xssf usermodel XSSFWorkbook                                                                          public class AdvUse        private static Workbook wb        private static Sheet sh        private static FileInputStream fis        private static FileOutputStream fos         private static Row row         private static Cell cell        private static String ExcelPath                                                                             public static void setEcxelFile String ExcelPath  String SheetName  throws Exception       try      File f  new File ExcelPath       if  f exists            f createNewFile           System out println  File not Found so created              fis   new FileInputStream    testData xlsx        wb   WorkbookFactory create fis        sh   wb getSheet  SheetName        if sh    null           sh   wb getSheet SheetName               catch Exception e       System out println e getMessage                                                                                              public static void setCellData String text   int rowno   int colno       try          row   sh getRow rowno           if row    null               row   sh createRow rowno                     cell   row getCell colno           if cell  null               cell setCellValue text                      else              cell   row createCell colno               cell setCellValue text                      fos   new FileOutputStream ExcelPath           wb write fos           fos flush            fos close         catch Exception e           System out println e getMessage                                                                                              public static String getCellData int rowno   int colno           try               cell   sh getRow rowno  getCell colno                String CellData   null               switch cell getCellType                 case  STRING                   CellData   cell getStringCellValue                   break                case NUMERIC                    CellData   Double toString cell getNumericCellValue                     if CellData contains   o                         CellData   CellData substring 0 CellData length  -2                                  break                case BLANK                CellData       break                               return CellData           catch Exception e  return                                                                                           public static int getLastRow            return sh getLastRowNum

User · Answer

Sure   you will find the code below useful and easy to read and write  This is a util class which you can use in your main method and then you are good to use all methods below        public class ExcelUtils        private static XSSFSheet ExcelWSheet       private static XSSFWorkbook ExcelWBook       private static XSSFCell Cell       private static XSSFRow Row       File fileName   new File  C   Users  satekuma  Pro  Fund xlsx         public void setExcelFile File Path  String SheetName  throws Exception                      try           FileInputStream ExcelFile   new FileInputStream Path           ExcelWBook   new XSSFWorkbook ExcelFile           ExcelWSheet   ExcelWBook getSheet SheetName         catch  Exception e            throw  e                    public static String getCellData int RowNum  int ColNum  throws Exception        try           Cell   ExcelWSheet getRow RowNum  getCell ColNum           String CellData   Cell getStringCellValue            return CellData        catch  Exception e             return               public static void setCellData String Result  int RowNum  int ColNum  File Path  throws Exception        try           Row   ExcelWSheet createRow RowNum - 1           Cell   Row createCell ColNum - 1           Cell setCellValue Result           FileOutputStream fileOut   new FileOutputStream Path           ExcelWBook write fileOut           fileOut flush            fileOut close          catch  Exception e             throw  e

User · Answer

If you go for third party library option  try using Aspose Cells API that enables Java Applications to create  read write  and manage Excel spreadsheets efficiently without requiring Microsoft Excel  e g Sample code  1    Load sample workbook Workbook wb   new Workbook dirPath    quot sample xlsx quot       Access first worksheet Worksheet ws   wb getWorksheets   get 0      Access cells iterator Iterator itrat   ws getCells   iterator       Print cells name in iterator while itrat hasNext          Cell cell    Cell itrat next         System out println cell getName      quot    quot    cell getStringValue   trim           Workbook book   new Workbook  quot sample xlsx quot    Worksheet sheet   book getWorksheets   get 0   Range range   sheet getCells   getMaxDisplayRange     You may also create your desired range  in the worksheet  using  e g sheet getCells   createRange  quot A1 quot    quot J11 quot    Iterator rangeIterator   range iterator    while rangeIterator hasNext      Cell cell    Cell rangeIterator next      your code goes here     Hope  this helps a bit  PS  I am working as Support developer  Evangelist at Aspose

User · Answer

Apache POI can do this for you  Specifically the HSSF module  The quick guide is most useful  Here s how to do what you want - specifically create a sheet and write it out   Workbook wb   new HSSFWorkbook      Workbook wb   new XSSFWorkbook    CreationHelper createHelper   wb getCreationHelper    Sheet sheet   wb createSheet  new sheet        Create a row and put some cells in it  Rows are 0 based  Row row   sheet createRow  short 0      Create a cell and put a value in it  Cell cell   row createCell 0   cell setCellValue 1       Or do it on one line  row createCell 1  setCellValue 1 2   row createCell 2  setCellValue  createHelper createRichTextString  This is a string     row createCell 3  setCellValue true       Write the output to a file FileOutputStream fileOut   new FileOutputStream  workbook xls    wb write fileOut   fileOut close

User · Answer

csv or POI will certainly do it  but you should be aware of Andy Khan s JExcel   I think it s by far the best Java library for working with Excel there is

User · Answer

You can also consider JExcelApi  I find it better designed than POI  There s a tutorial here

User · Answer

I edited the most voted one a little cuz it didn t count blanks columns or rows well not totally  so here is my code i tested it and now can get any cell in any part of an excel file  also now u can have blanks columns between filled column and it will read them    try   POIFSFileSystem fs   new POIFSFileSystem new FileInputStream Dir    HSSFWorkbook wb   new HSSFWorkbook fs   HSSFSheet sheet   wb getSheetAt 0   HSSFRow row  HSSFCell cell   int rows     No of rows rows   sheet getPhysicalNumberOfRows     int cols   0     No of columns int tmp   0  int cblacks 0      This trick ensures that we get the data properly even if it doesn t start from first few rows for int i   0  i  lt   10    i  lt   rows  i          row   sheet getRow i       if row    null            tmp   sheet getRow i  getPhysicalNumberOfCells            if tmp  gt   cols  cols   tmp else rows   cblacks                cols      cols cols cblacks  for int r   0  r  lt  rows  r          row   sheet getRow r       if row    null            for int c   0  c  lt  cols  c                  cell   row getCell c               if cell    null                    System out print cell   n     Your Code here                                  catch Exception ioe    ioe printStackTrace

User · Answer

You can not read  amp  write same file in parallel Read-write lock    But  we can do parallel operations on temporary data i e  Input output stream   Write the data to file only after closing the input stream  Below steps should be followed   Open the file to Input stream Open the same file to an Output Stream Read and do the processing Write contents to output stream  Close the read input stream  close file Close output stream  close file   Apache POI - read write same excel example import java io File  import java io FileInputStream  import java io FileNotFoundException  import java io FileOutputStream  import java io IOException  import java sql Date  import java util HashMap  import java util Iterator  import java util Map  import java util Set   import org apache poi ss usermodel Cell  import org apache poi ss usermodel Row  import org apache poi xssf usermodel XSSFSheet  import org apache poi xssf usermodel XSSFWorkbook    public class XLSXReaderWriter        public static void main String   args             try               File excel   new File  quot D   raju xlsx quot                FileInputStream fis   new FileInputStream excel               XSSFWorkbook book   new XSSFWorkbook fis               XSSFSheet sheet   book getSheetAt 0                Iterator lt Row gt  itr   sheet iterator                    Iterating over Excel file in Java             while  itr hasNext                      Row row   itr next                        Iterating over each column of Excel file                 Iterator lt Cell gt  cellIterator   row cellIterator                    while  cellIterator hasNext                           Cell cell   cellIterator next                         switch  cell getCellType                          case Cell CELL TYPE STRING                          System out print cell getStringCellValue      quot  t quot                            break                      case Cell CELL TYPE NUMERIC                          System out print cell getNumericCellValue      quot  t quot                            break                      case Cell CELL TYPE BOOLEAN                          System out print cell getBooleanCellValue      quot  t quot                            break                      default                                                           System out println  quot  quot                                  writing data into XLSX file             Map lt String  Object   gt  newData   new HashMap lt String  Object   gt                 newData put  quot 1 quot   new Object     1d   quot Raju quot    quot 75K quot    quot dev quot                        quot SGD quot                  newData put  quot 2 quot   new Object     2d   quot Ramesh quot    quot 58K quot    quot test quot                        quot USD quot                  newData put  quot 3 quot   new Object     3d   quot Ravi quot    quot 90K quot    quot PMO quot                        quot INR quot                   Set lt String gt  newRows   newData keySet                int rownum   sheet getLastRowNum                 for  String key   newRows                    Row row   sheet createRow rownum                     Object   objArr   newData get key                   int cellnum   0                  for  Object obj   objArr                        Cell cell   row createCell cellnum                         if  obj instanceof String                            cell setCellValue  String  obj                         else if  obj instanceof Boolean                            cell setCellValue  Boolean  obj                         else if  obj instanceof Date                            cell setCellValue  Date  obj                         else if  obj instanceof Double                            cell setCellValue  Double  obj                                                                         open an OutputStream to save written data into Excel file             FileOutputStream os   new FileOutputStream excel               book write os               System out println  quot Writing on Excel file Finished     quot                    Close workbook  OutputStream and Excel file to prevent leak             os close                book close                fis close               catch  FileNotFoundException fe                fe printStackTrace              catch  IOException ie                ie printStackTrace

User · Answer

Another way to read write Excel files is to use Windmill  It provides a fluent API to process Excel and CSV files   Import data  try  Stream lt Row gt  rowStream   Windmill parse FileSource of new FileInputStream  myFile xlsx          rowStream        skip the header row that contains the column names      skip 1       forEach row - gt          System out println           row n      row rowIndex               column  User login  value       row cell  User login   asString               column n  3 number value       row cell 2  asDouble   value      index is zero-based                      Export data  Windmill    export Arrays asList bean1  bean2  bean3      withHeaderMapping      new ExportHeaderMapping lt Bean gt           add  Name   Bean  getName         add  User login   bean - gt  bean getUser   getLogin           asExcel      writeTo new FileOutputStream  Export xlsx

User · Answer

This will write a JTable to a tab separated file that can be easily imported into Excel  This works   If you save an Excel worksheet as an XML document you could also build the XML file for EXCEL with code  I have done this with word so you do not have to use third-party packages    This could code have the JTable taken out and then just write a tab separated to any text file and then import into Excel  I hope this helps   Code   import java io File  import java io FileWriter  import java io IOException  import javax swing JTable  import javax swing table TableModel   public class excel       String columnNames        Column 1    Column 2    Column 3             Create some data     String dataValues                        12    234    67                -123    43    853                93    89 2    109                279    9033    3092                JTable table       excel             table   new JTable  dataValues  columnNames                public void toExcel JTable table  File file           try              TableModel model   table getModel                FileWriter excel   new FileWriter file                for int i   0  i  lt  model getColumnCount    i                     excel write model getColumnName i      t                               excel write   n                 for int i 0  i lt  model getRowCount    i                      for int j 0  j  lt  model getColumnCount    j                          excel write model getValueAt i j  toString     t                                      excel write   n                               excel close              catch IOException e   System out println e                public static void main String   o            excel cv   new excel            cv toExcel cv table new File  C   Users  itpr13266  Desktop  cs tbv

User · Answer

Try the Apache POI HSSF  Here s an example on how to read an excel file   try       POIFSFileSystem fs   new POIFSFileSystem new FileInputStream file        HSSFWorkbook wb   new HSSFWorkbook fs       HSSFSheet sheet   wb getSheetAt 0       HSSFRow row      HSSFCell cell       int rows     No of rows     rows   sheet getPhysicalNumberOfRows         int cols   0     No of columns     int tmp   0          This trick ensures that we get the data properly even if it doesn t start from first few rows     for int i   0  i  lt  10    i  lt  rows  i              row   sheet getRow i           if row    null                tmp   sheet getRow i  getPhysicalNumberOfCells                if tmp  gt  cols  cols   tmp                       for int r   0  r  lt  rows  r              row   sheet getRow r           if row    null                for int c   0  c  lt  cols  c                      cell   row getCell  short c                   if cell    null                           Your code here                                                   catch Exception ioe        ioe printStackTrace        On the documentation page you also have examples of how to write to excel files

User · Answer

If you need to do anything more with office documents in Java  go for POI as mentioned   For simple reading writing an excel document like you requested  you can use the CSV format  also as mentioned    import java io BufferedReader  import java io FileReader  import java io FileWriter  import java io IOException  import java io PrintWriter  import java util Scanner   public class CsvWriter    public static void main String args    throws IOException      String fileName    test xls      PrintWriter out   new PrintWriter new FileWriter fileName      out println  a b c d      out println  e f g h      out println  i j k l      out close       BufferedReader in   new BufferedReader new FileReader fileName      String line   null    while   line   in readLine       null        Scanner scanner   new Scanner line      String sep          while  scanner hasNext          System out println sep   scanner next         sep                   in close

User · Answer

A simple CSV file should suffice

User · Answer

String path  C   Book2 xlsx   try            File f   new File  path            Workbook wb   WorkbookFactory create f           Sheet mySheet   wb getSheetAt 0           Iterator lt Row gt  rowIter   mySheet rowIterator            for   Iterator lt Row gt  rowIterator   mySheet rowIterator    rowIterator hasNext                            for    Iterator lt Cell gt  cellIterator     Row rowIterator next    cellIterator     cellIterator hasNext                                       System out println      Cell cellIterator next     toString                                System out println                                                                                         catch   Exception e                 System out println   exception             e printStackTrace            and make sure to have added the jars  poi  and poi-ooxml  org apache poi  to your project

User · Answer

If column number are varing you can use this  package com org tests  import org apache poi xssf usermodel    import java io FileInputStream  import java io IOException   public class ExcelSimpleTest           String path      public FileInputStream fis   null      private XSSFWorkbook workbook   null      private XSSFSheet sheet   null      private XSSFRow row    null      private XSSFCell cell   null       public ExcelSimpleTest   throws IOException               path   System getProperty  user dir      resources  Book1 xlsx           fis   new FileInputStream path            workbook   new XSSFWorkbook fis           sheet   workbook getSheetAt 0             public void ExelWorks                 int index   workbook getSheetIndex  Sheet1            sheet   workbook getSheetAt index           int rownumber sheet getLastRowNum   1             for  int i 1  i lt rownumber  i                           row   sheet getRow i               int colnumber   row getLastCellNum                for  int j 0  j lt colnumber  j                                   cell   row getCell j                   System out println cell getStringCellValue                                          public static void main String   args  throws IOException                ExcelSimpleTest excelwork   new ExcelSimpleTest            excelwork ExelWorks              The corresponding mavendependency can be found here

User · Answer

When using the apache poi 4 1 2  The celltype  changes a bit  Below is an example      try           File excel   new File   home name Downloads bb xlsx            FileInputStream fis   new FileInputStream excel           XSSFWorkbook book   new XSSFWorkbook fis           XSSFSheet sheet   book getSheetAt 0            Iterator lt Row gt  itr   sheet iterator                Iterating over Excel file in Java         while  itr hasNext                  Row row   itr next                 Iterator lt Cell gt  cellIterator   row cellIterator                while  cellIterator hasNext                         Cell cell   cellIterator next                       switch  cell getCellType                      case STRING                      System out print cell getStringCellValue       t                        break                  case NUMERIC                      System out print cell getNumericCellValue       t                        break                  case BOOLEAN                      System out print cell getBooleanCellValue       t                        break                  default                                                System out println                catch  Exception e                   TODO  handle exception             e printStackTrace

User · Answer

For reading data from  xlsx workbooks we need to use XSSFworkbook classes   XSSFWorkbook xlsxBook   new XSSFWorkbook fis    XSSFSheet sheet   xlsxBook getSheetAt 0   etc   We need to use Apache-poi 3 9   http   poi apache org    For detailed info with example visit   http   java-recent blogspot in

User · Answer

First add all these jar files in your project class path     poi-scratchpad-3 7-20101029 poi-3 2-FINAL-20081019 poi-3 7-20101029 poi-examples-3 7-20101029 poi-ooxml-3 7-20101029 poi-ooxml-schemas-3 7-20101029 xmlbeans-2 3 0 dom4j-1 6 1   Code for writing in a excel  file   public static void main String   args          Blank workbook     XSSFWorkbook workbook   new XSSFWorkbook           Create a blank sheet     XSSFSheet sheet   workbook createSheet  Employee Data           This data needs to be written  Object        Map lt String  Object   gt  data   new TreeMap lt String  Object   gt         data put  1   new Object    ID    NAME    LASTNAME         data put  2   new Object   1   Amit    Shukla         data put  3   new Object   2   Lokesh    Gupta         data put  4   new Object   3   John    Adwards         data put  5   new Object   4   Brian    Schultz            Iterate over data and write to sheet     Set lt String gt  keyset   data keySet         int rownum   0      for  String key   keyset                   create a row of excelsheet         Row row   sheet createRow rownum                get object array of prerticuler key         Object   objArr   data get key            int cellnum   0           for  Object obj   objArr                         Cell cell   row createCell cellnum                 if  obj instanceof String                                 cell setCellValue  String  obj                             else if  obj instanceof Integer                                 cell setCellValue  Integer  obj                                     try                  Write the workbook in file system         FileOutputStream out   new FileOutputStream new File  C   Documents and Settings  admin  Desktop  imp data  howtodoinjava demo xlsx             workbook write out           out close            System out println  howtodoinjava demo xlsx written successfully on disk                catch  Exception e                e printStackTrace              Code for reading from excel file       To change this template  choose Tools   Templates   and open the template in the editor      public static void main String   args        try           FileInputStream file   new FileInputStream new File  C   Documents and Settings  admin  Desktop  imp data  howtodoinjava demo xlsx                Create Workbook instance holding reference to  xlsx file         XSSFWorkbook workbook   new XSSFWorkbook file              Get first desired sheet from the workbook         XSSFSheet sheet   workbook getSheetAt 0              Iterate through each rows one by one         Iterator lt Row gt  rowIterator   sheet iterator            while  rowIterator hasNext                          Row row   rowIterator next                  For each row  iterate through all the columns             Iterator lt Cell gt  cellIterator   row cellIterator                 while  cellIterator hasNext                                   Cell cell   cellIterator next                      Check the cell type and format accordingly                 switch  cell getCellType                                           case Cell CELL TYPE NUMERIC                          System out print cell getNumericCellValue       t                            break                      case Cell CELL TYPE STRING                          System out print cell getStringCellValue       t                            break                                              System out println                        file close          catch  Exception e            e printStackTrace

User · Answer

Please use Apache POI libs and try this        try               FileInputStream x   new FileInputStream new File   Users rajesh Documents rajesh xls                Create Workbook instance holding reference to  xlsx file         Workbook workbook   new HSSFWorkbook x              Get first desired sheet from the workbook         Sheet sheet   workbook getSheetAt 0              Iterate through each rows one by one         for  Iterator lt Row gt  iterator   sheet iterator    iterator hasNext                   Row row    Row  iterator next                for  Iterator lt Cell gt  iterator2   row iterator    iterator2                      hasNext                       Cell cell    Cell  iterator2 next                    System out println cell getStringCellValue                                                                           x close              catch  Exception e                e printStackTrace

User · Answer

using spring apache poi repo  if  fileName endsWith   xls         File myFile   new File  file location    fileName                   FileInputStream fis   new FileInputStream myFile                    org apache poi ss usermodel Workbook workbook   null                  try                       workbook   WorkbookFactory create fis                     catch  InvalidFormatException e                         e printStackTrace                                        org apache poi ss usermodel Sheet sheet   workbook getSheetAt 0                     Iterator lt Row gt  rowIterator   sheet iterator                      while  rowIterator hasNext                          Row row   rowIterator next                         Iterator lt Cell gt  cellIterator   row cellIterator                        while  cellIterator hasNext                               Cell cell   cellIterator next                            switch  cell getCellType                              case Cell CELL TYPE STRING                              System out print cell getStringCellValue                                 break                          case Cell CELL TYPE BOOLEAN                              System out print cell getBooleanCellValue                                 break                          case Cell CELL TYPE NUMERIC                              System out print cell getNumericCellValue                                 break                                                    System out print   -                                               System out println

[java] How to read and write excel file

Examples related to java

Examples related to excel