How one can Learn Write Excel file in Java

On this Java Excel tutorial, you’ll learn to learn and write from Excel recordsdata in Java. You’ll be taught steps to learn/write each XLS and XLSX file format through the use of the Apache POI library. On this instance, we are going to significantly deal with studying and writing String and Date values into Excel file as writing dates are little bit difficult. In our earlier Java Excel tutorial, you’ve got already realized find out how to learn/write Numeric sorts from Excel in Java, however we have not touched date values, that are additionally saved as numeric sorts, we are going to be taught that on this tutorial. There are two elements of this tutorial, in first half we are going to write date and String values into the XLS file and within the second half we are going to learn them from the XLS file. 

It is crucial to grasp which JAR recordsdata you must learn which form of Excel recordsdata in Java, as a result of lessons used to learn completely different Excel file format are completely different e.g. to learn previous Excel file format i.e. XLS recordsdata you want HSSFWorkbook class, which is inside poi-XX.jar, whereas class used to learn present Excel file format i.e. XLSX file  is XSSFWorkbook, which is inside poi-ooxml.jar library.

Apache POI JARs to Learn/Write Excel File in Java

Although there are couple of open supply library accessible to learn and write from Excel file in Java e.g. JXL, essentially the most characteristic wealthy and hottest one is Apache POI library. You’ll be able to learn each forms of Excel file format utilizing this library. With the intention to use this library both you must obtain POI JAR recordsdata and add into your Eclipse’s construct path manually or you’ll be able to use Maven to obtain dependency for you.

In case you are utilizing Maven then embrace following two dependencies to make use of Apache POI in your Java program :

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <model>3.12</model>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <model>3.12</model>
    </dependency>
  </dependencies>

Fundamental benefit of utilizing Maven is that it not solely downloads direct dependency e.g.  poi.jar and poi-ooxml.jar but in addition obtain transitive dependency e.g. JARS on which POI library is internally dependent.  For instance, I’ve simply specified Apache POI JAR recordsdata however Maven will even obtain xmlbeans-2.6.0.jar, stax-api-1.0.1.jar, poi-ooxml-schemas-3.12.jar and commons-codec-1.9.jar. 

JAR Dependencies :

In case you are extra comfy by downloading JAR recordsdata by your self, you’ll be able to obtain Apache POI JARS  from right here . It will obtain entire bundle so that you need not fear, however make certain it incorporates following JAR recordsdata in case your utility goes to help each XLS and XLSX format.

• poi-3.12.jar
• commons-codec-1.9.jar
• poi-ooxml-3.12.jar
• poi-ooxml-schemas-3.12.jar
• xmlbeans-2.6.0.jar
• stax-api-1.0.1.jar

POI is for studying OLE format e.g. XLS, DOC and .PPT format, whereas poi-ooxml.jar is to learn XLSX, DOCX and .PPTX format. Do not obtain simply POI jar, all the time embrace transitive dependency. 

For instance, in case you embrace simply poi-3.12.jar then your program will compile high quality as a result of you aren’t utilizing transitive dependency e.g. xmlbeans straight however it is going to fail at runtime with error like java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject due to lacking xmlbeans.jar dependency.

How one can learn from Excel File in Java? Instance

Suppose you’ve got a cell in your excel file which incorporates a date e.g. birthdate? how do you learn it? Most of you’ll say that you’ll learn that cell by first making a Workbook, then getting a sheet from that workbook, then getting the cell from that sheet which is containing date worth and eventually getting cell worth from that cell. 

With the intention to print the precise date worth you must use technique getDateCellValue(), which can return an object of java.util.Date, if you wish to show a formatted date, then you must format date utilizing SimpleDateFormat or through the use of Joda Date and Time library.

In our instance, we are going to create an excel file which incorporates one row and two columns. The primary column will include a String kind, the place we are going to retailer identify and the second column can be of date kind, the place we are going to date of beginning. Later, we are going to learn the identical excel file in our Java program to show identify and date values into the console. 

With the intention to learn an excel file in Java, it have to be in classpath. With the intention to keep away from points, I’ll use Eclipse IDE to jot down this program and it’ll create an excel file in Eclipse’s venture straight, which all the time stays within the classpath.

How one can learn/write from XLS file in Java

That is our first instance to learn String and date values from Excel recordsdata in Java. On this instance, we’re first creating an previous Excel file format i.e. XLS file birthdays.xls and later we are going to learn from the identical file. As soon as we run our program, you’ll be able to see this excel file created in your Eclipse venture listing, as proven beneath.

Steps to jot down Knowledge into XLS file in Java

• Embrace poi-3.12.jar in your Java program’s classpath
• Create an object of HSSFWorkBook
• Create a Sheet on that workbook by calling createSheet() technique 
• Create a Row on that sheet by calling createRow() technique
• Create a Cell by calling createCell() technique
• Set worth to that cell by calling setCellValue() technique.
• Write workbook content material into File utilizing FileOutputStream object.
• Shut the workbook object by calling shut() technique

These steps are high quality for writing String and Numeric values however in an effort to write date values into Excel file, you must observe following extra steps :

• Create a DataFormat
• Create a CellStyle
• Set format into CellStyle
• Set CellStyle into Cell
• Write java.util.Date into Cell

Step to learn information from XLS file in Java

• Embrace poi-3.12.jar in your Java program’s classpath
• Create an object of HSSFWorkBook by opening excel file utilizing FileInputStream
• Get a Sheet from workbook by calling getSheet() technique, you’ll be able to cross identify or sheet index
• Get a Row from that sheet by calling getRow() technique, you’ll be able to cross index
• Get a Cell by calling getCell() technique
• Get the Cell kind by calling getCellType() technique.
• Relying upon Cell kind, name getStringCellValue(), getNumericCellValue() or getDateCellValue() technique to get worth.
• Shut the workbook object by calling shut() technique

In case you are studying date values then only one thing more to do not forget that there is no such thing as a cell with date kind and Excel shops date as a numeric kind. So all the time examine kind of a cell with date worth to a numeric cell kind.

On this program, studying and writing logic are encapsulated into two static utility strategies readFromExcel() and writeIntoExcel(), so you may also check out them for precise code for studying writing XLS file in Java.

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * Easy Java Program to learn and write dates from Excel file in Java.
 * This instance significantly learn Excel file in OLE format i.e.
 * Excel file with extension .xls, often known as XLS recordsdata.
 * 
 * @creator WINDOWS 8
 *
 */
public class ExcelDateReader {

    public static void major(String[] args) throws FileNotFoundException,
                                          IOException {
        writeIntoExcel("birthdays.xls");
        readFromExcel("birthdays.xls");
    }
    
    /**
     * Java technique to learn dates from Excel file in Java.
     * This technique learn worth from .XLS file, which is an OLE
     * format. 
     * 
     * @param file
     * @throws IOException 
     */
    public static void readFromExcel(String file) throws IOException{
        HSSFWorkbook myExcelBook = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet myExcelSheet = myExcelBook.getSheet("Birthdays");
        HSSFRow row = myExcelSheet.getRow(0);
        
        if(row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_STRING){
            String identify = row.getCell(0).getStringCellValue();
            System.out.println("identify : " + identify);
        }
        
        if(row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
            Date birthdate = row.getCell(1).getDateCellValue();
            System.out.println("birthdate :" + birthdate);
        }
        
        myExcelBook.shut();
        
    }
    
    /**
     * Java technique to jot down dates from Excel file in Java.
     * This technique write worth into .XLS file in Java.
     * @param file, identify of excel file to jot down.
     * @throws IOException 
     * @throws FileNotFoundException 
     */
    @SuppressWarnings("deprecation")
    public static void writeIntoExcel(String file) throws 
                   FileNotFoundException, IOException{
        Workbook e book = new HSSFWorkbook();
        Sheet sheet = e book.createSheet("Birthdays");

        // first row begin with zero
        Row row = sheet.createRow(0); 
        
        // we are going to write identify and birthdates in two columns
        // identify can be String and birthday can be Date
        // formatted as dd.mm.yyyy
        Cell identify = row.createCell(0);
        identify.setCellValue("John");
        
        Cell birthdate = row.createCell(1);
        
        // steps to format a cell to show date worth in Excel
        // 1. Create a DataFormat
        // 2. Create a CellStyle
        // 3. Set format into CellStyle
        // 4. Set CellStyle into Cell
        // 5. Write java.util.Date into Cell
        DataFormat format = e book.createDataFormat();
        CellStyle dateStyle = e book.createCellStyle();
        dateStyle.setDataFormat(format.getFormat("dd.mm.yyyy"));
        birthdate.setCellStyle(dateStyle);
        
        // It is very trick technique, deprecated, do not use
        // yr is from 1900, month begins with zero
        birthdate.setCellValue(new Date(110, 10, 10));
        
        // auto-resizing columns
        sheet.autoSizeColumn(1);
        
        // Now, its time to jot down content material of Excel into File
        e book.write(new FileOutputStream(file));
        e book.shut();
    }
}


Output
identify : John
birthdate :Wed Nov 10 00:00:00 GMT+08:00 2010

In our program, we’ve first created an excel file with String and date columns and later learn from the identical file and displayed the values into the console. Now let’s confirm output of this program. It is appropriately show the date worth, although not formatted, which implies the excel file was created efficiently and later Java was in a position to learn from it. 

Should you take a look at your Eclipse venture listing, you can see birthdays.xls file created there, in case you open that with Microsoft Excel or any Open Workplace editor, you will note the next output.

It’s because I have never included sheet.autoSizeColumn(1) technique name in first run and since column width shouldn’t be sufficient to show the date in requested format e.g. dd.mm.yyyy it simply shows ######. 

Should you run this system once more with that code, you’ll be able to see the date values correctly formatted and fitted in requested column, as proven beneath

Apache POI Instance to learn XLSX file in Java

Studying and writing into new excel file format XLSX can also be the identical, all you must do is embrace poi-ooxml.jar and substitute all HSFF lessons with XSSF lessons e.g. as an alternative of utilizing HSSFWorkbook, use XSSFWorkbook, as an alternative of utilizing HSFFSheet use XSSFSheet, as an alternative of utilizing HSSFRow use XSSFRow and as an alternative of utilizing HSSFCell simply use XSSFCell class. 

When you run this program in your Eclipse IDE, you’ll be able to see the birthdays.xlsx file created in your Eclipse Challenge listing, as proven beneath :

right here is our java program to learn XLSX recordsdata utilizing Apache POI library.

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
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.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * This program learn date values from XLSX file in Java utilizing Apache POI.
 * 
 * @creator WINDOWS 8
 *
 */
public class ExcelDateReader {

    public static void major(String[] args) throws FileNotFoundException,
                                        IOException {
        writeIntoExcel("birthdays.xlsx");
        readFromExcel("birthdays.xlsx");
    }
    
    public static void readFromExcel(String file) throws IOException{
        XSSFWorkbook myExcelBook = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet myExcelSheet = myExcelBook.getSheet("Birthdays");
        XSSFRow row = myExcelSheet.getRow(0);
        
        if(row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_STRING){
            String identify = row.getCell(0).getStringCellValue();
            System.out.println("NAME : " + identify);
        }
        
        if(row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
            Date birthdate = row.getCell(1).getDateCellValue();
            System.out.println("DOB :" + birthdate);
        }
        
        myExcelBook.shut();
        
    }

    @SuppressWarnings("deprecation")
    public static void writeIntoExcel(String file) throws 
                      FileNotFoundException, IOException{
        Workbook e book = new XSSFWorkbook();
        Sheet sheet = e book.createSheet("Birthdays");
        Row row = sheet.createRow(0); 

        Cell identify = row.createCell(0);
        identify.setCellValue("Gokul");
        
        Cell birthdate = row.createCell(1);
        DataFormat format = e book.createDataFormat();
        CellStyle dateStyle = e book.createCellStyle();
        dateStyle.setDataFormat(format.getFormat("dd.mm.yyyy"));
        birthdate.setCellStyle(dateStyle);

        birthdate.setCellValue(new Date(115, 10, 10));
        
        sheet.autoSizeColumn(1);
        
        e book.write(new FileOutputStream(file));
        e book.shut();
    }
}

NAME : Gokul
DOB :Tue Nov 10 00:00:00 GMT+08:00 2015

That is all about find out how to learn and write from Excel file in Java. You’ve got now realized find out how to learn and write each String and Date from XLS in addition to XLSX recordsdata in Java. You are able to do much more utilizing the Apache POI library however this information will make it easier to be taught and shortly use this library. As soon as once more I counsel to use Maven for together with POI dependency and in case you are downloading JAR, be sure to obtain transitive dependency e.g. xmlbeans.