Java Selenium Read Write Xls Csv File

Learn to read excel, write excel, evaluate formula cells and employ custom formatting to the generated excel files using Apache POI library with examples.

If we are building a software for the 60 minutes or the Finance domain, in that location is usually a requirement for generating the excel reports, usually across management levels. Apart from reports, we can also wait some input information for the applications coming in the form of excel sheets and the application is expected to support this requirement.

Apache POI is a well-trusted library amid many other open-source libraries to handle such usecases involving excel files. Delight note that, in addition, we tin read and write MS Discussion and MS PowerPoint files as well using the Apache POI library.

In this Apache POI tutorial, We volition discuss some common excel operations in real-life applications.

1. 1. Maven Dependency
2. two. Important Classes in POI Library
   • HSSF, XSSF and XSSF classes
   • Row and Jail cell
   • Styling Related Classes
   • FormulaEvaluator
3. iii. Writing an Excel File
4. 4. Reading an Excel File
5. 5. Add and Evaluate Formula Cells
6. 6. Formatting the Cells
   • half dozen.1. Cell value in a certain range
   • 6.2. Highlight Indistinguishable Values
   • 6.3. Color Alternate Rows in Different Colors
   • six.four. Color amounts which are going to expire in next xxx days
7. vii. Download Sourcecode

one. Maven Dependency

If we are working on a maven project, we can include the POI dependency in pom.xml file using this:

          <dependency>     <groupId>org.apache.poi</groupId>     <artifactId>poi</artifactId>     <version>v.0.0</version> </dependency>        

If we are not using maven, and so we can detect the required jar files from the zip file available in this location. Include all the jar files into the application lib binder to run the sample code given in this tutorial.

two. Of import Classes in POI Library

1. HSSF, XSSF and XSSF classes

   Apache POI main classes usually start with either HSSF, XSSF or SXSSF.

   • HSSF – is the POI Project'southward pure Java implementation of the Excel '97(-2007) file format. e.g. HSSFWorkbook, HSSFSheet.
   • XSSF – is the POI Project'southward pure Coffee implementation of the Excel 2007 OOXML (.xlsx) file format. due east.k. XSSFWorkbook, XSSFSheet.
   • SXSSF (since iii.8-beta3) – is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is express. eastward.g. SXSSFWorkbook, SXSSFSheet. SXSSF achieves its low memory footprint past limiting access to the rows that are within a sliding window, while XSSF gives admission to all rows in the document.

2. Row and Cell

   Apart from higher up classes, Row and Cell are used to collaborate with a particular row and a particular cell in excel sail.

3. Styling Related Classes

   A broad range of classes like CellStyle, BuiltinFormats, ComparisonOperator, ConditionalFormattingRule, FontFormatting, IndexedColors, PatternFormatting, SheetConditionalFormatting etc. are used when you accept to add formatting in a sheet, mostly based on some rules.

4. FormulaEvaluator

   Another useful form FormulaEvaluator is used to evaluate the formula cells in excel sheet.

3. Writing an Excel File

I am taking this example starting time and so that we tin reuse the excel sheet created by this code in further examples.

Writing excel using POI is very unproblematic and involve the following steps:

1. Create a workbook
2. Create a canvass in workbook
3. Create a row in sheet
4. Add cells in sheet
5. Repeat step 3 and 4 to write more data

It seems very unproblematic, right? Let's have a expect at the code doing these steps.

Java program to write an excel file using Apache POI library.

          package com.howtodoinjava.demo.poi; //import statements public class WriteExcelDemo  {     public static void main(Cord[] args)      {         //Blank workbook         XSSFWorkbook workbook = new XSSFWorkbook();                    //Create a bare sail         XSSFSheet canvass = workbook.createSheet("Employee Information");                    //This data needs to be written (Object[])         Map<String, Object[]> data = new TreeMap<Cord, Object[]>();         data.put("i", new Object[] {"ID", "NAME", "LASTNAME"});         data.put("2", new Object[] {ane, "Amit", "Shukla"});         data.put("iii", new Object[] {two, "Lokesh", "Gupta"});         data.put("4", new Object[] {3, "John", "Adwards"});         data.put("v", new Object[] {4, "Brian", "Schultz"});                    //Iterate over data and write to sail         Ready<Cord> keyset = data.keySet();         int rownum = 0;         for (String key : keyset)         {             Row row = sheet.createRow(rownum++);             Object [] objArr = data.get(key);             int cellnum = 0;             for (Object obj : objArr)             {                Jail cell prison cell = row.createCell(cellnum++);                if(obj instanceof String)                     jail cell.setCellValue((String)obj);                 else if(obj instanceof Integer)                     jail cell.setCellValue((Integer)obj);             }         }         endeavor         {             //Write the workbook in file organization             FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));             workbook.write(out);             out.close();             System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");         }          catch (Exception e)          {             e.printStackTrace();         }     } }        

four. Reading an Excel File

Reading an excel file using POI is as well very unproblematic if we divide this into steps.

1. Create workbook instance from excel canvas
2. Go to the desired sheet
3. Increment row number
4. iterate over all cells in a row
5. repeat step iii and 4 until all data is read

Let'southward see all the to a higher place steps in code. I am writing the code to read the excel file created in the above example. It will read all the column names and the values in information technology – cell by prison cell.

Java program to read an excel file using Apache POI library.

          package com.howtodoinjava.demo.poi; //import statements public class ReadExcelDemo  {     public static void main(Cord[] args)      {         try         {             FileInputStream file = new FileInputStream(new File("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 past one             Iterator<Row> rowIterator = sail.iterator();             while (rowIterator.hasNext())              {                 Row row = rowIterator.next();                 //For each row, iterate through all the columns                 Iterator<Prison cell> cellIterator = row.cellIterator();                                   while (cellIterator.hasNext())                  {                     Cell cell = cellIterator.next();                     //Cheque the prison cell blazon and format appropriately                     switch (cell.getCellType())                      {                         case Prison cell.CELL_TYPE_NUMERIC:                             System.out.print(cell.getNumericCellValue() + "t");                             break;                         example Cell.CELL_TYPE_STRING:                             System.out.print(cell.getStringCellValue() + "t");                             break;                     }                 }                 System.out.println("");             }             file.close();         }          catch (Exception due east)          {             e.printStackTrace();         }     } }        

Program Output:

          ID      NAME        LASTNAME 1.0     Amit        Shukla   ii.0     Lokesh      Gupta    3.0     John        Adwards  4.0     Brian       Schultz                  

v. Add together and Evaluate Formula Cells

When working on complex excel sheets, we come across many cells which have formulas to calculate their values. These are formula cells. Apache POI has excellent support for adding formula cells and evaluating already present formula cells also.

Let'south see ane example of how to add together formula cells in excel?

In this code, in that location are four cells in a row and the fourth i in the multiplication of all previous 3 rows. Then the formula will be: A2*B2*C2 (in the second row)

Coffee plan to add formula in an excel file using Apache POI library.

          public static void main(String[] args)  {     XSSFWorkbook workbook = new XSSFWorkbook();     XSSFSheet sheet = workbook.createSheet("Calculate Elementary Involvement");        Row header = canvas.createRow(0);     header.createCell(0).setCellValue("Pricipal");     header.createCell(1).setCellValue("RoI");     header.createCell(two).setCellValue("T");     header.createCell(3).setCellValue("Interest (P r t)");            Row dataRow = sheet.createRow(one);     dataRow.createCell(0).setCellValue(14500d);     dataRow.createCell(one).setCellValue(nine.25);     dataRow.createCell(2).setCellValue(3d);     dataRow.createCell(iii).setCellFormula("A2*B2*C2");            try {         FileOutputStream out =  new FileOutputStream(new File("formulaDemo.xlsx"));         workbook.write(out);         out.close();         Organisation.out.println("Excel with foumula cells written successfully");                } catch (FileNotFoundException eastward) {         e.printStackTrace();     } catch (IOException east) {         e.printStackTrace();     } }        

Similarly, nosotros want to read a file with formula cells and use the following logic to evaluate formula cells.

Coffee programme to evaluate formula in an excel file using Apache POI library.

          public static void readSheetWithFormula() {     try     {         FileInputStream file = new FileInputStream(new File("formulaDemo.xlsx"));           //Create Workbook instance belongings reference to .xlsx file         XSSFWorkbook workbook = new XSSFWorkbook(file);           FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();                   //Get showtime/desired sheet from the workbook         XSSFSheet canvass = workbook.getSheetAt(0);           //Iterate through each rows one by i         Iterator<Row> rowIterator = canvas.iterator();         while (rowIterator.hasNext())          {             Row row = rowIterator.next();             //For each row, iterate through all the columns             Iterator<Cell> cellIterator = row.cellIterator();                           while (cellIterator.hasNext())              {                 Cell prison cell = cellIterator.adjacent();                 //Check the jail cell type later on eveluating formulae                 //If it is formula cell, it volition be evaluated otherwise no change will happen                 switch (evaluator.evaluateInCell(cell).getCellType())                  {                     case Cell.CELL_TYPE_NUMERIC:                         System.out.print(cell.getNumericCellValue() + "tt");                         break;                     case Cell.CELL_TYPE_STRING:                         Organisation.out.print(prison cell.getStringCellValue() + "tt");                         break;                     instance Jail cell.CELL_TYPE_FORMULA:                         //Non over again                         break;                 }             }             Arrangement.out.println("");         }         file.close();     }      take hold of (Exception e)      {         e.printStackTrace();     } }        

Program Output:

          Pricipal        RoI         T       Interest (P r t)         14500.0         nine.25        3.0     402375.0                  

half-dozen. Formatting the Cells

And then for we have seen the examples of reading/writing and excel files using Apache POI. Merely, when creating a report in an excel file, information technology becomes of utmost importance to add formatting on cells that fit into whatsoever pre-determined criteria.

This formatting tin be a different coloring based on a specific value range, based on expiry date limit etc.

In the below examples, nosotros are taking a couple of such cell formatting examples for various purposes.

6.ane. Prison cell value in a sure range

This piece of code volition color any cell in a range whose value is between a configured range. [e.thousand., between 50 and lxx]

          static void basedOnValue(Sheet canvas)  {     //Creating some random values     sheet.createRow(0).createCell(0).setCellValue(84);     sheet.createRow(one).createCell(0).setCellValue(74);     sail.createRow(2).createCell(0).setCellValue(fifty);     sheet.createRow(iii).createCell(0).setCellValue(51);     canvass.createRow(four).createCell(0).setCellValue(49);     sheet.createRow(five).createCell(0).setCellValue(41);       SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();       //Condition ane: Jail cell Value Is   greater than  70   (Blueish Fill)     ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "seventy");     PatternFormatting fill1 = rule1.createPatternFormatting();     fill1.setFillBackgroundColor(IndexedColors.Blue.alphabetize);     fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);       //Condition 2: Prison cell Value Is  less than      50   (Green Fill)     ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "fifty");     PatternFormatting fill2 = rule2.createPatternFormatting();     fill2.setFillBackgroundColor(IndexedColors.GREEN.index);     fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);       CellRangeAddress[] regions = {             CellRangeAddress.valueOf("A1:A6")     };       sheetCF.addConditionalFormatting(regions, rule1, rule2); }        

vi.2. Highlight Duplicate Values

Highlight all cells which accept duplicate values in observed cells.

          static void formatDuplicates(Sheet canvas) {     sheet.createRow(0).createCell(0).setCellValue("Code");     sheet.createRow(one).createCell(0).setCellValue(4);     sheet.createRow(two).createCell(0).setCellValue(three);     sheet.createRow(3).createCell(0).setCellValue(half dozen);     sheet.createRow(four).createCell(0).setCellValue(iii);     canvass.createRow(5).createCell(0).setCellValue(5);     sail.createRow(half-dozen).createCell(0).setCellValue(8);     sheet.createRow(vii).createCell(0).setCellValue(0);     sail.createRow(8).createCell(0).setCellValue(2);     sheet.createRow(9).createCell(0).setCellValue(8);     sheet.createRow(10).createCell(0).setCellValue(half-dozen);       SheetConditionalFormatting sheetCF = canvass.getSheetConditionalFormatting();       // Condition ane: Formula Is   =A2=A1   (White Font)     ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");     FontFormatting font = rule1.createFontFormatting();     font.setFontStyle(false, true);     font.setFontColorIndex(IndexedColors.Blueish.index);       CellRangeAddress[] regions = {             CellRangeAddress.valueOf("A2:A11")     };       sheetCF.addConditionalFormatting(regions, rule1);       sail.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  " +             "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)"); }        

half dozen.3. Color Alternate Rows in Unlike Colors

A simple code to color each alternate row in a different color.

          static void shadeAlt(Canvas sheet) {     SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();       // Condition i: Formula Is   =A2=A1   (White Font)     ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("Modern(ROW(),2)");     PatternFormatting fill1 = rule1.createPatternFormatting();     fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);     fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);       CellRangeAddress[] regions = {             CellRangeAddress.valueOf("A1:Z100")     };       sheetCF.addConditionalFormatting(regions, rule1);       canvass.createRow(0).createCell(1).setCellValue("Shade Alternate Rows");     canvas.createRow(1).createCell(i).setCellValue("Condition: Formula Is  =Mod(ROW(),two)   (Light Greenish Fill)"); }        

six.4. Color amounts which are going to expire in next 30 days

A handy code for financial projects which keeps rails of deadlines.

          static void expiryInNext30Days(Sail canvas)  {     CellStyle style = sheet.getWorkbook().createCellStyle();     way.setDataFormat((short)BuiltinFormats.getBuiltinFormat("d-mmm"));       sheet.createRow(0).createCell(0).setCellValue("Date");     sheet.createRow(one).createCell(0).setCellFormula("TODAY()+29");     canvass.createRow(2).createCell(0).setCellFormula("A2+1");     sheet.createRow(three).createCell(0).setCellFormula("A3+1");       for(int rownum = 1; rownum <= 3; rownum++) canvass.getRow(rownum).getCell(0).setCellStyle(fashion);       SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();       // Condition 1: Formula Is   =A2=A1   (White Font)     ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");     FontFormatting font = rule1.createFontFormatting();     font.setFontStyle(false, truthful);     font.setFontColorIndex(IndexedColors.Blueish.index);       CellRangeAddress[] regions = {             CellRangeAddress.valueOf("A2:A4")     };       sheetCF.addConditionalFormatting(regions, rule1);       sheet.getRow(0).createCell(i).setCellValue("Dates within the adjacent 30 days are highlighted"); }        

I am catastrophe this apache poi tutorial here for keeping the post in limit.

7. Download Sourcecode

Click on the given link to download the source code of the above examples.

In this tutorial, we learned to read excel, write excel, set and evaluate formula cells, and format the cells with colour codings using the Apache POI library.

Happy Learning !!

References:

Apache POI How-to Guide
Apache POI API docs

olsoncoving.blogspot.com

Source: https://howtodoinjava.com/java/library/readingwriting-excel-files-in-java-poi-tutorial/

Share this post