Returning multiple Excel Files in one call with Apache POI

Question

I am attempting to download multiple excel files with one call. I have a stored procedure that returns data broken down by company, and for each company I need a separate excel file. Some of the values of the excel files are interdependent (so what is on the first excel file will go on to the second.

Except for a few functions which I have verified are working correctly, I've included the entirety of the code below. As it stands now the code runs exactly as it should, if I did not check the outputs I would think it was perfect, however where I expect to see two files downloaded I instead only get the first one. The second call to write to the ServletOutputStream does fire, but no file is downloaded.

Can anyone see what is wrong here? It isn't the file size, there should be a max of five records in each file, and I've downloaded single excel files before without issue.

package export;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import main.getFund;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 *
 * @author mmarino
 */
public class NavExport extends HttpServlet {
    String dateStr = "";
    String ERDate = "";
    Double absoluteTotal = 0.0;
    Double iTotal = 0.0;
    getFund g = new getFund();

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String str = "";

        Workbook wb = new HSSFWorkbook();
        Workbook pWB = new HSSFWorkbook();
        //Workbook wb = new XSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet oSheet = wb.createSheet("Recon");
        Sheet pSheet = pWB.createSheet("Recon");
        String prevCompany = "";
        dateStr = new Date().toString();
        dateStr = new SimpleDateFormat("MM/dd/yyy").format(new Date());
        ERDate = "ER" + dateStr.substring(6, 10) + dateStr.substring(0,2) + dateStr.substring(3,5);
        try{

            Connection conn = null;
            ResultSet rs = null;
            PreparedStatement prst = null;
            String s = request.getParameter("ids");

            String roll = request.getParameter("roll");
            String[] ids = s.split("\\|");

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            String connUrl = "My Connection URL";
            conn = DriverManager.getConnection(connUrl);           
            String sql = "My SQL Query";                                 
            prst = conn.prepareStatement(sql);
            rs = prst.executeQuery();

            int pInt = 1;
            int oInt = 1;

            g.main();
            while(rs.next()){
                if(rs.getString("Company").equals("10")){
                    addRow(pSheet, rs, createHelper, pInt);
                    pInt++;
                }else{
                  if(!prevCompany.equals(rs.getString("Company")) && !prevCompany.equals("") && !prevCompany.equals("10")){
                      writeWorkBook(wb, prevCompany, response);
                      oInt = 1;
                      wb = new HSSFWorkbook();
                      oSheet = wb.createSheet("Recon");
                      iTotal = 0.0;
                  }
                prevCompany = rs.getString("Company");
                addRow(oSheet, rs, createHelper, oInt);
                iTotal += rs.getDouble("Approved");
                oInt++;  
                }
                absoluteTotal += rs.getDouble("Approved");
            }// end rs.next();
            if(!prevCompany.equals("10")){
                addTopRow(oSheet, createHelper, prevCompany, iTotal);
                writeWorkBook(wb, prevCompany, response);
            }
            addTopRow(pSheet, createHelper, "10", absoluteTotal);
            writeWorkBook(pWB, "10", response);

        }catch(Exception e){
            str="Got exception: " + e.getMessage();
        }

    }

     private void addTopRow(Sheet sheet, CreationHelper createHelper, String Company, Double amount){
         try{
                Row row = sheet.createRow((short)0);
                row.createCell(0).setCellValue(createHelper.createRichTextString(dateStr));
                row.createCell(1).setCellValue(createHelper.createRichTextString(""));
                row.createCell(2).setCellValue(createHelper.createRichTextString(ERDate));
                row.createCell(3).setCellValue(createHelper.createRichTextString("G/L Account"));
                row.createCell(4).setCellValue(passSwitch(Company));
                row.createCell(5).setCellValue(createHelper.createRichTextString(""));
                row.createCell(6).setCellValue(createHelper.createRichTextString("1000-0000"));
                row.createCell(7).setCellValue(createHelper.createRichTextString(""));
                row.createCell(8).setCellValue(createHelper.createRichTextString(""));
                row.createCell(9).setCellValue(createHelper.createRichTextString(""));
                row.createCell(10).setCellValue(createHelper.createRichTextString(""));
                row.createCell(11).setCellValue(createHelper.createRichTextString("Null"));
                row.createCell(12).setCellValue(createHelper.createRichTextString(""));
                row.createCell(13).setCellValue(createHelper.createRichTextString(""));
                row.createCell(14).setCellValue(createHelper.createRichTextString(""));
                row.createCell(15).setCellValue(createHelper.createRichTextString(""));
                row.createCell(16).setCellValue(createHelper.createRichTextString(""));
                row.createCell(17).setCellValue(amount);
                row.createCell(18).setCellValue(createHelper.createRichTextString(""));
                row.createCell(19).setCellValue(createHelper.createRichTextString(""));
                row.createCell(20).setCellValue(createHelper.createRichTextString(""));
                row.createCell(21).setCellValue(createHelper.createRichTextString("G/L Account"));
         }catch(Exception e){
             String error = e.toString();
         }
     }

    private void addRow(Sheet sheet, ResultSet rs, CreationHelper createHelper, int i){
        try{
            Row row = sheet.createRow((short)i);
            row.createCell(0).setCellValue(createHelper.createRichTextString(dateStr));
            row.createCell(1).setCellValue(createHelper.createRichTextString(""));
            row.createCell(2).setCellValue(createHelper.createRichTextString(ERDate));
            row.createCell(3).setCellValue(createHelper.createRichTextString("G"));
            row.createCell(4).setCellValue(rs.getString("Code"));
            row.createCell(5).setCellValue(createHelper.createRichTextString(""));
            row.createCell(6).setCellValue(createHelper.createRichTextString(g.getFundVar(rs.getString("Company") + ":" + rs.getString("Dept"))));
            row.createCell(7).setCellValue(createHelper.createRichTextString(""));
            row.createCell(8).setCellValue(createHelper.createRichTextString(""));
            row.createCell(9).setCellValue(createHelper.createRichTextString(""));
            row.createCell(10).setCellValue(createHelper.createRichTextString(""));

            String emNum = rs.getString("EM");
            while(emNum.length() != 9){
                emNum = "0" + emNum;
            }
            emNum = "E" + emNum;
            row.createCell(11).setCellValue(createHelper.createRichTextString(emNum));
            row.createCell(12).setCellValue(createHelper.createRichTextString(""));
            row.createCell(13).setCellValue(createHelper.createRichTextString(""));
            row.createCell(14).setCellValue(createHelper.createRichTextString(""));
            row.createCell(15).setCellValue(createHelper.createRichTextString(""));
            row.createCell(16).setCellValue(createHelper.createRichTextString(""));
            row.createCell(17).setCellValue(rs.getDouble("Approved"));
            row.createCell(18).setCellValue(createHelper.createRichTextString(""));
            row.createCell(19).setCellValue(createHelper.createRichTextString(""));
            row.createCell(20).setCellValue(createHelper.createRichTextString(""));
            row.createCell(21).setCellValue(createHelper.createRichTextString("G/L Account"));

        }catch(Exception e){
            String error = e.toString();
        }
    }
    private void writeWorkBook(Workbook wb, String Company, HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Expires", "0");
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        response.setHeader("Content-Disposition", "attachment; filename=" + Company +".xls");
    try {                         
        ServletOutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();         
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Answer 1

Either you have to zip the Excel files and send as response OR you have it as a single excel file and use multiple sheets instead files