Recently I have done some work in Java for automation framework development. During the project I have used java APIs for excel operations. Today I am going to give you the short introduction of that API and also we will discuss some of the API which we can use to read Excel data.
First of all let’s go through the available projects/libraries to perform operations on Excel sheets and later we will go through in details.
There are two types of POI projects available, one is POI-HSSF and another is POI-XSSF. The library that I have used in Java is POI-HSSF which is a project implemented in Pure Java for the Excel “97(-2007)” file format (.xls). POI-XSSF project is for Excel 2007+ file format.
HSSF and XSSF both the projects provide ways to perform read/write/create/modify Excel operations.
Let’s now discuss different methods and operations in details using HSSF usermodel.
How we can create new workbook?
Workbook workbook = new HSSFWorkbook();
FileOutputStream oFile = new FileOutputStream(“my_workbook.xls”);
After we create new Workbook we have to create sheets as well inside the workbook which we can do it using simple methods available in POI-HSSF. We have to add one line of code in above (how to create new workbook?) code.
How to create sheet?
Add the below line of code after the 1st line (from above code) and it will create new Sheet under the specified workbook.
Sheet mySheet1 = workbook.createSheet(“mysheet1”);
I hope you all understood well, now let us go to the next step. There is no use if we just create Workbook and sheets in it and not adding data in the cell. We can also create cell and also write in to the cell of your choice.
How to add data in to the cell?
To write in Excel, we have to first create Row object and the using that Row we have to create Column object. Finally using that column object we can write into that cell.
After we create sheet in the workbook, we just have to add below lines of code that will write “1” at row number 1 and column number 2 and in another cell (row,column = 2,2) it will write “my name is parsi”.
Row sheetRow = sheet.createRow((short)0);
Cell oCell = sheetRow.createCell(0);
sheetRow.createCell(2).setCellValue(createHelper.createRichTextString(“my name is parsi “));
How to save Excel?
FileOutputStream oFile = new FileOutputStream(“my_workbook2.xls”);
Similar to these there are so many functions/methods are available in POI-HSSF which we will discuss in some other article. I hope this article will be useful to you all.
Similar Articles related to Excel:
Useful Functions of Microsoft Excel Macro
Basics of Microsoft Excel Macro and Some Useful Tips
VB Script – to Compare Two Excel Sheets