Upload Excel File to Database in Java Jersey

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

If we are edifice a software for the HR or the Finance domain, there 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 course of excel sheets and the application is expected to support this requirement.

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

In this Apache POI tutorial, We volition talk over some mutual excel operations in real-life applications.

1. 1. Maven Dependency
2. two. Of import Classes in POI Library
   • HSSF, XSSF and XSSF classes
   • Row and Cell
   • Styling Related Classes
   • FormulaEvaluator
3. 3. Writing an Excel File
4. 4. Reading an Excel File
5. five. Add and Evaluate Formula Cells
6. 6. Formatting the Cells
   • 6.1. Jail cell value in a certain range
   • 6.2. Highlight Duplicate Values
   • 6.3. Color Alternate Rows in Different Colors
   • 6.iv. Colour amounts which are going to expire in next 30 days
7. 7. 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>5.0.0</version> </dependency>        

If we are non using maven, then nosotros tin can discover 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 lawmaking given in this tutorial.

2. Important Classes in POI Library

1. HSSF, XSSF and XSSF classes

   Apache POI principal classes ordinarily commencement with either HSSF, XSSF or SXSSF.

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

2. Row and Cell

   Autonomously from to a higher place classes, Row and Prison cell are used to interact with a detail row and a detail cell in excel sheet.

3. Styling Related Classes

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

4. FormulaEvaluator

   Another useful class FormulaEvaluator is used to evaluate the formula cells in excel sail.

iii. Writing an Excel File

I am taking this example commencement so that we can reuse the excel sheet created by this code in farther examples.

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

1. Create a workbook
2. Create a sheet in workbook
3. Create a row in canvas
4. Add together cells in sheet
5. Repeat stride three and 4 to write more information

Information technology seems very elementary, correct? Permit's have a wait at the lawmaking doing these steps.

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

          package com.howtodoinjava.demo.poi; //import statements public course WriteExcelDemo  {     public static void principal(String[] args)      {         //Blank workbook         XSSFWorkbook workbook = new XSSFWorkbook();                    //Create a bare canvas         XSSFSheet sheet = workbook.createSheet("Employee Data");                    //This data needs to be written (Object[])         Map<String, Object[]> data = new TreeMap<String, Object[]>();         data.put("1", new Object[] {"ID", "Name", "LASTNAME"});         data.put("2", new Object[] {one, "Amit", "Shukla"});         data.put("3", new Object[] {2, "Lokesh", "Gupta"});         data.put("4", new Object[] {three, "John", "Adwards"});         data.put("5", new Object[] {iv, "Brian", "Schultz"});                    //Iterate over data and write to canvass         Set<String> keyset = data.keySet();         int rownum = 0;         for (String key : keyset)         {             Row row = canvass.createRow(rownum++);             Object [] objArr = information.go(key);             int cellnum = 0;             for (Object obj : objArr)             {                Cell prison cell = row.createCell(cellnum++);                if(obj instanceof String)                     prison cell.setCellValue((Cord)obj);                 else if(obj instanceof Integer)                     cell.setCellValue((Integer)obj);             }         }         try         {             //Write the workbook in file system             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)          {             due east.printStackTrace();         }     } }        

iv. Reading an Excel File

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

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

Permit's come across all the to a higher place steps in lawmaking. I am writing the code to read the excel file created in the to a higher place example. It will read all the column names and the values in information technology – jail cell by cell.

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

          bundle com.howtodoinjava.demo.poi; //import statements public grade ReadExcelDemo  {     public static void main(String[] args)      {         attempt         {             FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx"));               //Create Workbook example property 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 ane             Iterator<Row> rowIterator = sheet.iterator();             while (rowIterator.hasNext())              {                 Row row = rowIterator.next();                 //For each row, iterate through all the columns                 Iterator<Cell> cellIterator = row.cellIterator();                                   while (cellIterator.hasNext())                  {                     Prison cell cell = cellIterator.next();                     //Check the prison cell type and format accordingly                     switch (cell.getCellType())                      {                         case Prison cell.CELL_TYPE_NUMERIC:                             Organization.out.print(prison cell.getNumericCellValue() + "t");                             break;                         example Cell.CELL_TYPE_STRING:                             System.out.impress(cell.getStringCellValue() + "t");                             break;                     }                 }                 System.out.println("");             }             file.close();         }          catch (Exception e)          {             eastward.printStackTrace();         }     } }        

Program Output:

          ID      Proper name        LASTNAME 1.0     Amit        Shukla   2.0     Lokesh      Gupta    3.0     John        Adwards  4.0     Brian       Schultz                  

five. Add and Evaluate Formula Cells

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

Let's run into 1 instance of how to add together formula cells in excel?

In this code, there are 4 cells in a row and the quaternary 1 in the multiplication of all previous iii rows. So the formula will be: A2*B2*C2 (in the second row)

Java program to add formula in an excel file using Apache POI library.

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

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

Java plan 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 holding reference to .xlsx file         XSSFWorkbook workbook = new XSSFWorkbook(file);           FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();                   //Go first/desired sheet from the workbook         XSSFSheet canvass = workbook.getSheetAt(0);           //Iterate through each rows one past one         Iterator<Row> rowIterator = canvas.iterator();         while (rowIterator.hasNext())          {             Row row = rowIterator.side by side();             //For each row, iterate through all the columns             Iterator<Cell> cellIterator = row.cellIterator();                           while (cellIterator.hasNext())              {                 Cell cell = cellIterator.next();                 //Check the cell blazon subsequently eveluating formulae                 //If it is formula cell, it will exist evaluated otherwise no change volition happen                 switch (evaluator.evaluateInCell(cell).getCellType())                  {                     case Cell.CELL_TYPE_NUMERIC:                         System.out.print(cell.getNumericCellValue() + "tt");                         break;                     case Cell.CELL_TYPE_STRING:                         Organization.out.print(cell.getStringCellValue() + "tt");                         break;                     case Cell.CELL_TYPE_FORMULA:                         //Non again                         break;                 }             }             System.out.println("");         }         file.shut();     }      catch (Exception east)      {         e.printStackTrace();     } }        

Program Output:

          Pricipal        RoI         T       Involvement (P r t)         14500.0         ix.25        three.0     402375.0                  

half-dozen. Formatting the Cells

So for we accept seen the examples of reading/writing and excel files using Apache POI. Just, when creating a report in an excel file, it becomes of utmost importance to add together formatting on cells that fit into any pre-determined criteria.

This formatting can exist a different coloring based on a specific value range, based on death date limit etc.

In the beneath examples, we are taking a couple of such prison cell formatting examples for various purposes.

6.ane. Cell value in a sure range

This slice of code will color any prison cell in a range whose value is between a configured range. [east.g., between fifty and 70]

          static void basedOnValue(Sail sheet)  {     //Creating some random values     canvas.createRow(0).createCell(0).setCellValue(84);     sheet.createRow(one).createCell(0).setCellValue(74);     sheet.createRow(ii).createCell(0).setCellValue(50);     sheet.createRow(3).createCell(0).setCellValue(51);     sheet.createRow(4).createCell(0).setCellValue(49);     sheet.createRow(5).createCell(0).setCellValue(41);       SheetConditionalFormatting sheetCF = canvass.getSheetConditionalFormatting();       //Condition 1: Cell Value Is   greater than  seventy   (Blue Fill up)     ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");     PatternFormatting fill1 = rule1.createPatternFormatting();     fill1.setFillBackgroundColor(IndexedColors.BLUE.alphabetize);     fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);       //Status ii: Cell Value Is  less than      50   (Greenish Fill)     ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "l");     PatternFormatting fill2 = rule2.createPatternFormatting();     fill2.setFillBackgroundColor(IndexedColors.GREEN.alphabetize);     fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);       CellRangeAddress[] regions = {             CellRangeAddress.valueOf("A1:A6")     };       sheetCF.addConditionalFormatting(regions, rule1, rule2); }        

6.2. Highlight Duplicate Values

Highlight all cells which accept duplicate values in observed cells.

          static void formatDuplicates(Sheet sheet) {     sheet.createRow(0).createCell(0).setCellValue("Lawmaking");     sheet.createRow(1).createCell(0).setCellValue(4);     sheet.createRow(2).createCell(0).setCellValue(iii);     canvass.createRow(3).createCell(0).setCellValue(vi);     sheet.createRow(4).createCell(0).setCellValue(3);     sail.createRow(5).createCell(0).setCellValue(5);     sheet.createRow(vi).createCell(0).setCellValue(eight);     canvas.createRow(vii).createCell(0).setCellValue(0);     sheet.createRow(8).createCell(0).setCellValue(2);     sheet.createRow(ix).createCell(0).setCellValue(eight);     sheet.createRow(10).createCell(0).setCellValue(6);       SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();       // Condition 1: Formula Is   =A2=A1   (White Font)     ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$xi,A2)>1");     FontFormatting font = rule1.createFontFormatting();     font.setFontStyle(fake, truthful);     font.setFontColorIndex(IndexedColors.Bluish.index);       CellRangeAddress[] regions = {             CellRangeAddress.valueOf("A2:A11")     };       sheetCF.addConditionalFormatting(regions, rule1);       sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  " +             "Condition: Formula Is =COUNTIF($A$ii:$A$11,A2)>one   (Bluish Font)"); }        

half-dozen.3. Color Alternate Rows in Different Colors

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

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

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

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

          static void expiryInNext30Days(Sheet sheet)  {     CellStyle style = sheet.getWorkbook().createCellStyle();     style.setDataFormat((curt)BuiltinFormats.getBuiltinFormat("d-mmm"));       sheet.createRow(0).createCell(0).setCellValue("Engagement");     sail.createRow(1).createCell(0).setCellFormula("TODAY()+29");     sheet.createRow(2).createCell(0).setCellFormula("A2+i");     sheet.createRow(3).createCell(0).setCellFormula("A3+ane");       for(int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(mode);       SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();       // Condition i: Formula Is   =A2=A1   (White Font)     ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");     FontFormatting font = rule1.createFontFormatting();     font.setFontStyle(imitation, truthful);     font.setFontColorIndex(IndexedColors.BLUE.alphabetize);       CellRangeAddress[] regions = {             CellRangeAddress.valueOf("A2:A4")     };       sheetCF.addConditionalFormatting(regions, rule1);       sheet.getRow(0).createCell(1).setCellValue("Dates within the side by side thirty days are highlighted"); }        

I am ending 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, gear up and evaluate formula cells, and format the cells with color codings using the Apache POI library.

Happy Learning !!

References:

Apache POI How-to Guide
Apache POI API docs

mickensspersen.blogspot.com

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

Share this post