导出excel 并创建多个sheet

工具方法

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtils {  


   /**
    * poi导出excel多个sheet
    * @param wb
    * @param sheetNum  (sheet的位置，0表示第一个表格中的第一个sheet)
    * @param sheetName （sheet的名称）
    * @param headList （表格的标题）
    * @param dataList （表格的数据）
    * @param os （输出流）
    */
   public static void exportExcel(HSSFWorkbook wb,int sheetNum,String sheetName,String[] headList,List<List<String>> dataList,OutputStream os) throws Exception{
      HSSFSheet sheet = wb.createSheet();
      wb.setSheetName(sheetNum, sheetName);
      HSSFRow row = sheet.createRow(0);
      HSSFCellStyle style = wb.createCellStyle();
      HSSFCell cell = row.createCell(0);
      if(headList!=null&& headList.length>0){
         for(int i=0;i<headList.length;i++){
            sheet.setColumnWidth(i, 20 * 256);
            cell = row.createCell(i); // 第一个单元格
            cell.setCellValue(headList[i]);
            cell.setCellStyle(style);
         }
      }
      if(dataList!=null&&dataList.size()>0){
         for(int i=0;i<dataList.size();i++){
            if(dataList.get(i)!=null&&dataList.get(i).size()>0){
               List<String> rowList=dataList.get(i);
               row = sheet.createRow(i + 1);
               if(rowList!=null&&rowList.size()>0){
                  for(int j=0;j<rowList.size();j++){
                     if(rowList.get(j)!=null){
                        row.createCell(j).setCellValue(rowList.get(j));
                     }
                  }
               }
            }
         }
      }

   }
}

调用方法

/**
 * 下载
 * @param request
 * @param respones
 * @throws Exception
 */
@RequestMapping(value = Urls.SSI_BACK_APPOINTMENT_DOWNLOAD)
public void exportAppointmentData(HttpServletRequest request, HttpServletResponse respones,String policyId) throws Exception {
   PolicyDTO policyDTO = policyReportService.queryAppointmentDetail(policyId);

    OutputStream os = respones.getOutputStream();
    try {
        respones.reset(); // 重置response的设置
        respones.setContentType("application/vnd.ms-excel;charset=utf-8");
        respones.setHeader("Content-Disposition", "attachment;filename=" + new String(("保单列表.xls").getBytes(), "iso-8859-1"));
        

         String[] headers0= { "阿", "的","目标" ,"金额","业务线","部门","备注"};
         List<List<String>> data0= new ArrayList<List<String>>();
         String[] headers1= { "客户"};
         List<List<String>> data1= Lists.newArrayList();
         List<String> list = xiaoshouService.getCustomerPool();
         List<String> list1 = xiaoshouService.getCustomerPool();
         for (String s : list) {
            List<String> rowData = new ArrayList<>();
            rowData.add(s);
            data0.add(rowData);
         }

        for (String s : list1) { 
            List<String> rowData = new ArrayList<>(); 
            rowData.add(s); 
            data1.add(rowData); 
        }

        ExcelUtils eeu = new ExcelUtils();
        HSSFWorkbook wb = new HSSFWorkbook();
        eeu.exportExcel(wb,0,"第一张表",headers0, data0, os);
        eeu.exportExcel(wb,1,"第二张表",headers1, data1, os);
       //原理就是将所有的数据一起写入，然后再关闭输入流。
        wb.write(os);
    } catch (IOException e) {
        System.out.println("Excel文件生成失败...");
        e.printStackTrace();
    }finally {
        try {
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    System.out.println("Excel文件生成成功...");