Meyyappan has posted 101 posts at DZone. View Full User Profile

Struts Export WorkBook to Excel Tutorial

06.15.2012
| 10715 views |
  • submit to reddit

In this example we will see how to export a WorkBook to Excel. ExcelCreator class is used to create the HSSFWorkbook. The ExcelCreator class contains the following code.

public class ExcelCreator {

    public HSSFWorkbook createWorkbook(ArrayList userList) throws Exception {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("User Data");

        /**
         * Setting the width of the first three columns.
         */
        sheet.setColumnWidth(0, 3500);
        sheet.setColumnWidth(1, 7500);
        sheet.setColumnWidth(2, 5000);

        /**
         * Style for the header cells.
         */
        HSSFCellStyle headerCellStyle = wb.createCellStyle();
        HSSFFont boldFont = wb.createFont();
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerCellStyle.setFont(boldFont);

        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(headerCellStyle);
        cell.setCellValue(new HSSFRichTextString("User Name"));
        cell = row.createCell(1);
        cell.setCellStyle(headerCellStyle);
        cell.setCellValue(new HSSFRichTextString("Email Id"));
        cell = row.createCell(2);
        cell.setCellStyle(headerCellStyle);
        cell.setCellValue(new HSSFRichTextString("Location"));

        for (int index = 1; index < userList.size(); index++) {
            row = sheet.createRow(index);
            cell = row.createCell(0);
            UserData userData = (UserData) userList.get(index);
            HSSFRichTextString userName = new HSSFRichTextString(userData.getUserName());
            cell.setCellValue(userName);
            cell = row.createCell(1);
            HSSFRichTextString emailId = new HSSFRichTextString(userData.getEmailId());
            cell.setCellValue(emailId);
            cell = row.createCell(2);
            HSSFRichTextString location = new HSSFRichTextString(userData.getLocation());
            cell.setCellValue(location);
        }
        return wb;
    }
}

The ExcelCreator class contains the createWorkbook method which takes an ArrayList as the argument and returns a HSSFWorkbook .The ArrayList contains a list of UserData. The ArrayList is iterated using a for loop and each row in the excel is created.

In our example the UserAction class extends DispatchAction. The UserAction class contains an exportExcel method, which is used to export the workbook to excel. The UserAction class contains the following code.

public class UserAction extends DispatchAction {

    private final static String SUCCESS = "success";

    public ActionForward populate(ActionMapping mapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        UserForm userForm = (UserForm) form;
        UserData userData = new UserData();
        userForm.setUserList(userData.loadData());
        return mapping.findForward(SUCCESS);
    }

    public ActionForward exportExcel(ActionMapping mapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        UserForm userForm = (UserForm) form;
        ExcelCreator excelCreator = new ExcelCreator();
        HSSFWorkbook workbook = excelCreator.createWorkbook(userForm.getUserList());
        response.setHeader("Content-Disposition", "attachment; filename=UserDetails.xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
        out.close();
        return mapping.findForward(SUCCESS);
    }
}

Run the example. The following user details will be displayed.

On clicking the Excel link the workbook is exported to excel. The following excel sheet will be displayed.

You can download the source code of the export Workbook to Excel example by clicking on the Download link below.

Source: Download

Source + Lib: Download

Published at DZone with permission of its author, Meyyappan Muthuraman.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Tags: