Interview Questions

Write to Excel sheet using POI api

In this article, let’s discuss about writing data to Excel sheet using POI api.

While working with applications, you may be often faced with requirement to create a Excel report. It can be done in Java using POI api.

Lets consider an example of creating an Excel sheet containing following data.

EMP_ID EMP_NAME AGE SALARY DEPARTMENT
1001 George 35 50000 IT
1002 Patrick 41 60000 FINANCE
1003 Haggai 32 45000 HR
1004 Jones 29 40000 MARKETING
1005 Miller 38 54000 SALES

Here is the ExcelReportGenerator.java class

ExcelReportGenerator.java

/**
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
 * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */

package in.techdive.java;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Font;

public class ExcelReportGenerator
{

    private FileOutputStream fileOut;

    private HSSFWorkbook     wb   = new HSSFWorkbook();

    private HSSFCellStyle    headerStyle = wb.createCellStyle();

    private List<Employee>   lstEmp      = new ArrayList<Employee>();

    public ExcelReportGenerator()
    {
        Employee emp1 = new Employee(1001, "George", 35, 50000, "IT");
        Employee emp2 = new Employee(1002, "Patrick", 41, 60000, "FINANCE");
        Employee emp3 = new Employee(1003, "Haggai", 32, 45000, "HR");
        Employee emp4 = new Employee(1004, "Jones", 29, 40000, "MARKETING");
        Employee emp5 = new Employee(1005, "Miller", 38, 54000, "SALES");

        lstEmp.add(emp1);
        lstEmp.add(emp2);
        lstEmp.add(emp3);
        lstEmp.add(emp4);
        lstEmp.add(emp5);

        HSSFFont headerFont = wb.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        headerStyle.setFillBackgroundColor(HSSFColor.WHITE.index);
        headerStyle.setFont(headerFont);

        try
        {
            fileOut = new FileOutputStream("C:\\emp.xls");
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
    }

    public void generateSimpleExcelReport()
    {
        try
        {
            HSSFSheet sheet3 = wb.createSheet("EMPLOYEE DETAILS");

            HSSFRow sessionname = sheet3.createRow(0);
            HSSFCell title = sessionname.createCell(0);
            title.setCellStyle(headerStyle);
            title.setCellValue("EMPLOYEE DETAILS");

            HSSFRow row = sheet3.createRow(5);

            HSSFCell cell0 = row.createCell(0);
            cell0.setCellStyle(headerStyle);
            cell0.setCellValue("EMPLOYEE ID");

            HSSFCell cell1 = row.createCell(1);
            cell1.setCellStyle(headerStyle);
            cell1.setCellValue("EMPLOYEE NAME");

            HSSFCell cell2 = row.createCell(2);
            cell2.setCellStyle(headerStyle);
            cell2.setCellValue("AGE");

            HSSFCell cell3 = row.createCell(3);
            cell3.setCellStyle(headerStyle);
            cell3.setCellValue("SALARY");

            HSSFCell cell4 = row.createCell(4);
            cell4.setCellStyle(headerStyle);
            cell4.setCellValue("DEPARTMENT");

            if (!lstEmp.isEmpty())
            {
                int rowNumber = 6;
                String cmptvalue = null;
                for (Employee s : lstEmp)
                {
                    HSSFRow nextrow = sheet3.createRow(rowNumber);
                    nextrow.createCell(0).setCellValue(s.getEmpId());
                    nextrow.createCell(1).setCellValue(s.getEmpName());
                    nextrow.createCell(2).setCellValue(s.getAge());
                    nextrow.createCell(3).setCellValue(s.getSalary());
                    nextrow.createCell(4).setCellValue(s.getDepartment());
                    // nextrow.createCell(2).setCellValue(s.getUdcrAttribute());

                    rowNumber++;
                }
            }
            sheet3.autoSizeColumn(0);
            sheet3.autoSizeColumn(1);
            sheet3.autoSizeColumn(2);
            sheet3.autoSizeColumn(3);
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
        }
        catch (FileNotFoundException fe)
        {
            fe.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                fileOut.flush();
                fileOut.close();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args)
    {
        ExcelReportGenerator eG = new ExcelReportGenerator();
        eG.generateSimpleExcelReport();
    }
}

This class will create an Excel workbook, with a sheet named "EMPLOYEE_DETAILS" with the employee details. It will contain rows as shown in the above table.

The generateSimpleExcelReport() method, first creates a worksheet "EMPLOYEE_DETAILS". It then adds a cell to display the heading in the work sheet. It then creates column headers for the rows to be displayed. Finally it iterates an array list of employee objects to fill the rows with employee details. When ExcelReportGenerator.java is executed, an excel sheet will be created in the given path.

Here is the Employee class for your reference.

Employee.java

package in.techdive.java;

public class Employee
{
    public Employee()
    {
    }

    public Employee(int empId, String empName, int age, int salary,
            String department)
    {
        this.empId = empId;
        this.empName = empName;
        this.age = age;
        this.salary = salary;
        this.department = department;
    }

    private int    empId  = 0;
    private String empName;
    private int    age    = 0;
    private int    salary = 0;
    private String department;

    public int getEmpId()
    {
        return empId;
    }

    public void setEmpId(int empId)
    {
        this.empId = empId;
    }

    public String getEmpName()
    {
        return empName;
    }

    public void setEmpName(String empName)
    {
        this.empName = empName;
    }

    public int getAge()
    {
        return age;
    }

    public void setAge(int age)
    {
        this.age = age;
    }

    public int getSalary()
    {
        return salary;
    }

    public void setSalary(int salary)
    {
        this.salary = salary;
    }

    public String getDepartment()
    {
        return department;
    }

    public void setDepartment(String department)
    {
        this.department = department;
    }
}

In this way we can use POI api to write to an Excel sheet in Java.