How to export data from struts2 to Excel

Question

I am expecting data to export in Excel format. I have a problem in writing data to Excel file and when I download that it is not in Excel format.

In the below first method I am getting data from a database and sending it as a list to a second method that expects a filename. In the second method I am trying to write the database data to an Excel file.

I have done coding for exporting to PDF also. It gets correctly downloaded, but in the URL bar downloadTeacherListINExcel.action. I have given a file path like this: String fileName = "f:\\teachersList.pdf". When it is downloaded in the browser the filename comes over like f-teachersList.

public String exportInExcel() {

        // getting data from data base 
        listOfTeachers = reportService.getlistOfTeachers();
        for (TeacherDTO teacherDTO : listOfTeachers) {
            System.out.println(teacherDTO.getTeacherEmailID());
        }

        // sending a list data export in excel
        String excelFileName = reportService.exportInExcel(listOfTeachers);
        System.out.println(excelFileName);
        try {
            fileInputStream = new FileInputStream(excelFileName);
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("Execl Download Method");
        return SUCCESS;
    }

Code written for Excel

@Override
    public String exportInExcel(List<TeacherDTO> listOfTeachers) {
        String fileName = "f:\\test\\teachersList.xls";
        try {

            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("Products List");

            // create heading
            Row rowHeading = sheet.createRow(0);

            rowHeading.createCell(0).setCellValue("Id");
            rowHeading.createCell(1).setCellValue("Name");

            for (int i = 0; i < 6; i++) {

                CellStyle stylerowHeading = workbook.createCellStyle();

                Font font = workbook.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                font.setFontName(HSSFFont.FONT_ARIAL);
                font.setFontHeightInPoints((short) 11);

                stylerowHeading.setFont(font);
                stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER);

                rowHeading.getCell(i).setCellStyle(stylerowHeading);
            }

            int r = 1;
            for (TeacherDTO teacher : listOfTeachers) {
                Row row = sheet.createRow(r);

                // Id column
                Cell cellID = row.createCell(0);
                cellID.setCellValue(teacher.getTeacherFirstName());// (run after
                                                                    // this line
                                                                    // once)

                // name column
                Cell cellName = row.createCell(1);
                cellName.setCellValue(teacher.getTeacherEmailID());// (run after
                                                                    // this line
                                                                    // once)

                r++;
            }

            // Auto fit
            for (int i = 0; i < 6; i++) {
                sheet.autoSizeColumn(i);
            }

            FileOutputStream fout = new FileOutputStream(new File(fileName));
            workbook.write(fout);
            fout.close();

            System.out.println("Excel Written Success");

        } catch (Exception e) {
        System.out.println("%%%%%%%%%%%%%%%%%%"+e.getMessage());
        }
        return fileName;
    }

Struts config

<action name="downloadTeacherListINExcel" class="com.pradeep.sms.controller.report.StaffReportAction" method="exportInExcel">
            <result  name="success"  type="stream">

                <param name="contentType">application/vnd.ms-excel</param>
                <param name="inputName">fileInputStream</param>
                 <param name="contentDisposition">attachment;filename=${fileName}</param>
                <param name="bufferSize">1024</param>
        </result>
    </action>   

Error

Hibernate: select 
pradeepkumarhe1989@gmail.com
%%%%%%%%%%%%%%%%%%null
f:\test\teachersList.xls
java.io.FileNotFoundException: f:\test\teachersList.xls (The system cannot find the file specified)

Answer 1

package com.download2excel;

import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import com.opensymphony.xwork2.ActionSupport;

public class Excelwritinginjava extends ActionSupport { private InputStream fileInputStream;

public String createExcelFile() throws Exception {      HSSFWorkbook workbook = new HSSFWorkbook();         HSSFSheet sheet = workbook.createSheet("January");      HSSFRow rowhead = sheet.createRow((short) 0);       rowhead.createCell(0).setCellValue("S.No.");        rowhead.createCell(1).setCellValue("Customer Name");        rowhead.createCell(2).setCellValue("Account Number");       rowhead.createCell(3).setCellValue("e-mail");       rowhead.createCell(4).setCellValue("Balance");      HSSFRow row = sheet.createRow((short) 1);       row.createCell(0).setCellValue("1");        row.createCell(1).setCellValue("subodh");       row.createCell(2).setCellValue("7644864977");       row.createCell(3).setCellValue("subodh.orcl@gmail.com");        row.createCell(4).setCellValue("700000.00");        HSSFRow row1 = sheet.createRow((short) 2);      row1.createCell(0).setCellValue("2");       row1.createCell(1).setCellValue("subodh sharma");       row1.createCell(2).setCellValue("22222222");        row1.createCell(3).setCellValue("subodh.orcl@gmail.com");       row1.createCell(4).setCellValue("200000.00");       ByteArrayOutputStream baos = new ByteArrayOutputStream();       workbook.write(baos);       setFileInputStream(new ByteArrayInputStream(baos.toByteArray()));       return SUCCESS;     }

public InputStream getFileInputStream() {       return fileInputStream;     }

public void setFileInputStream(InputStream fileInputStream) {       this.fileInputStream = fileInputStream;     }

}

======================struts.xml ====

application/octet-stream fileInputStream attachment;filename="Download.xls" 1024

Answer 2

In action class

 public String exportInExcel() {

                //getting List of teachers
                listOfTeachers = reportServiceExcel.getlistOfTeachers();

                // sending list data to write in excel sheet
                HSSFWorkbook workbook = reportServiceExcel.exportInExcel(listOfTeachers);

                // code to download
                try {
                    ByteArrayOutputStream boas = new ByteArrayOutputStream();
                    workbook.write(boas);
                    setInputStream(new ByteArrayInputStream(boas.toByteArray()));
                } catch (IOException e) {
                    e.printStackTrace();
                }

                return SUCCESS;
            }

Excel class

 public HSSFWorkbook exportInExcel(List<TeacherDTO> listOfTeachers) {

            HSSFWorkbook workbook = null;
            try {
                workbook = new HSSFWorkbook();
                HSSFSheet sheet = workbook.createSheet("Products List");

                // create heading
                Row rowHeading = sheet.createRow(0);

                rowHeading.createCell(0).setCellValue("Name");
                rowHeading.createCell(1).setCellValue("Mobile Number");
                rowHeading.createCell(2).setCellValue("Email ID");
                rowHeading.createCell(3).setCellValue("Designation");

                for (int i = 0; i < 4; i++) {
                    CellStyle stylerowHeading = workbook.createCellStyle();
                    Font font = workbook.createFont();
                    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    font.setFontName(HSSFFont.FONT_ARIAL);
                    font.setFontHeightInPoints((short) 11);
                    stylerowHeading.setFont(font);
                    stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER);
                    rowHeading.getCell(i).setCellStyle(stylerowHeading);
                }

                int r = 1;
                for (TeacherDTO t : listOfTeachers) {

                    String teacherName = t.getTeacherFirstName() + "" + t.getTeacherMiddleName() + ""
                            + t.getTeacherLastName();
                    Row row = sheet.createRow(r);

                    // Name column
                    Cell cellName = row.createCell(0);
                    cellName.setCellValue(teacherName);// (run after this line once)

                    // Mobile Number column
                    Cell cellMobileNumber = row.createCell(1);
                    cellMobileNumber.setCellValue(t.getTeacherMobileNumber());

                    // Email column
                    Cell cellEmail = row.createCell(2);
                    cellEmail.setCellValue(t.getTeacherEmailID());

                    // Designation column
                    Cell cellDesignation = row.createCell(3);
                    cellDesignation.setCellValue(t.getTeacherDesignation());

                    r++;
                }

                // Auto fit columns in excel sheet
                for (int i = 0; i < 4; i++) {
                    sheet.autoSizeColumn(i);
                }


                System.out.println("Excel Written Success");

            } catch (Exception e) {
                e.printStackTrace();
            }
            return workbook;
        }

Struts configuration

<action name="downloadTeacherListExcel" class="com.pradeep.sms.controller.report.StaffReportAction" method="exportInExcel">
    <result  name="success"  type="stream">           
        <param name="contentType">application/vnd.ms-excel</param>
        <param name="inputName">inputStream</param>
        <param name="contentDisposition">attachment;filename="teachersList.xls"</param>
        <param name="bufferSize">4096</param>
    </result>
</action>