Read/Write .xls and .xlsx with one set of code using POI - WorkbookFactory

As we discussed in earlier post Read/Write excel using Apache POI,
we have to use:
For handling .xls fileswe use HSSF (Horrible SpreadSheet Format) related POI classes.
For handling .xlsx files, we use XSSF (XML SpreadSheet Format) related POI classes.


On this post, how we can perform excel operations with one set code for both excel formats .xls and .xlsx.
For this we have a class provided from Apache POI WorkbookFactory, which auto detects appropriate kind of Workbook (HSSFWorkbook or XSSFWorkbook) depending on the excel formats .xls or .xlsx.
Let's jump to the code implementation


import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelUtil {
 private static Workbook wb;
 private static Sheet s;
 private static FileInputStream fis;
 private static FileOutputStream fos;
 private static Row row;
 private static Cell cell;
public static void main(String[] args) throws Exception {
        fis = new FileInputStream("./TestData.xls"); //same we can use TestData.xlsx as well
        wb = WorkbookFactory.create(fis);
        s = wb.getSheet(Sheet1);
        row  = s.getRow(rownum);
 if(row ==null)
 {
         row = s.createRow(rownum);
        }
        cell = row.getCell(colnum);
        if (cell != null) 
 {
  cell.setCellValue(text);
        } 
 else 
 {
  cell = row.createCell(colnum);
                cell.setCellValue(text);  
        }
        fos = new FileOutputStream(ExcelPath);
        wb.write(fos);
        fos.flush();
 fos.close();
   }
}

As you can see, only line changed from earlier post, code changed for line:
wb = WorkbookFactory.create(fis);
Hope this helps!

7 comments:

  1. There are many interesting information included and i can easily understand all given information.I post something on my blog to post something, or wait to post something worth saying. Keep update more information....
    Logistics Software
    Fleet Management Software
    Logistics management software
    Warehouse Management Software
    CCTV Camera Dealers in Chennai

    ReplyDelete
  2. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well.
    Rainwater Harvesting chennai
    Turnkey Interior Contractors in Chennai
    Interior Contractors in Chennai
    Office Interiors in Chennai
    Best Architects in Chennai

    ReplyDelete
  3. I am not sure the place you are getting your information, however good topic. I needs to spend some time studying more or understanding more. Thank you for wonderful information I was in search of this info for my mission.
    Peer Reviewed Journals
    Journal Of Mathematics
    Conference publication
    Journal Of Physics
    Journal Of Education

    ReplyDelete
  4. Very informative ! Indian cyber Army gives Best Ethical Hacking Training in noida in view of current industry principles that causes participants to anchor positions in their fantasy occupations at MNCs.

    ReplyDelete
  5. Do Follow URL Submission is a social bookmarking submission website that provide you genuine do follow link for backlink of your website
    Do Follow URL Submission

    ReplyDelete

  6. Thanks for sharing the good information and post more information.Talent flames company is one of the best training and placement company in Hyderabad. Providing training on Technologies like Java,Sql,Oracle,..,etc with 100% Placement Assistance. Now Interviews going on if you want to attend just visit our website and drop your resume. for more information visit us http://talentflames.com/
    training and placement company in Hyderabad

    ReplyDelete
  7. It was nice reading your blog. Marvelous work!. Blog is brilliantly written and provides all necessary information I really like this site. Thanks for sharing this useful post.
    https://www.bharattaxi.com

    ReplyDelete