Struts Export WorkBook to Excel Tutorial

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.

view source

print?

01.public class ExcelCreator {

02. 

03.public HSSFWorkbook createWorkbook(ArrayList userList) throws Exception {

04. 

05.HSSFWorkbook wb = new HSSFWorkbook();

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

07. 

08./**

09.* Setting the width of the first three columns.

10.*/

11.sheet.setColumnWidth(0, 3500);

12.sheet.setColumnWidth(1, 7500);

13.sheet.setColumnWidth(2, 5000);

14. 

15./**

16.* Style for the header cells.

17.*/

18.HSSFCellStyle headerCellStyle = wb.createCellStyle();

19.HSSFFont boldFont = wb.createFont();

20.boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

21.headerCellStyle.setFont(boldFont);

22. 

23.HSSFRow row = sheet.createRow(0);

24.HSSFCell cell = row.createCell(0);

25.cell.setCellStyle(headerCellStyle);

26.cell.setCellValue(new HSSFRichTextString("User Name"));

27.cell = row.createCell(1);

28.cell.setCellStyle(headerCellStyle);

29.cell.setCellValue(new HSSFRichTextString("Email Id"));

30.cell = row.createCell(2);

31.cell.setCellStyle(headerCellStyle);

32.cell.setCellValue(new HSSFRichTextString("Location"));

33. 

34.for (int index = 1; index < userList.size(); index++) {

35.row = sheet.createRow(index);

36.cell = row.createCell(0);

37.UserData userData = (UserData) userList.get(index);

38.HSSFRichTextString userName = newHSSFRichTextString(userData.getUserName());

39.cell.setCellValue(userName);

40.cell = row.createCell(1);

41.HSSFRichTextString emailId = newHSSFRichTextString(userData.getEmailId());

42.cell.setCellValue(emailId);

43.cell = row.createCell(2);

44.HSSFRichTextString location = newHSSFRichTextString(userData.getLocation());

45.cell.setCellValue(location);

46.}

47.return wb;

48.}

49.}

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.

view source

print?

01.public class UserAction extends DispatchAction {

02. 

03.private final static String SUCCESS = "success";

04. 

05.public ActionForward populate(ActionMapping mapping, ActionForm form,

06.HttpServletRequest request, HttpServletResponse response)

07.throws Exception {

08.UserForm userForm = (UserForm) form;

09.UserData userData = new UserData();

10.userForm.setUserList(userData.loadData());

11.return mapping.findForward(SUCCESS);

12.}

13. 

14.public ActionForward exportExcel(ActionMapping mapping, ActionForm form,

15.HttpServletRequest request, HttpServletResponse response)

16.throws Exception {

17.UserForm userForm = (UserForm) form;

18.ExcelCreator excelCreator = new ExcelCreator();

19.HSSFWorkbook workbook = excelCreator.createWorkbook(userForm.getUserList());

20.response.setHeader("Content-Disposition", "attachment; filename=UserDetails.xls");

21.ServletOutputStream out = response.getOutputStream();

22.workbook.write(out);

23.out.flush();

24.out.close();

25.return mapping.findForward(SUCCESS);

26.}

27.}

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.