excel导出报表，生成多个sheet，压缩excel文件夹，导出压缩文件

public void doExport(OutputStream out) throws JspException
    {
        try
        {
        out = toExcel(out);
            HSSFWorkbook wb = new HSSFWorkbook();//下面注释为单个sheet，就是只有一个excel文件及一个sheet，小数据量则可以直接使用下面注释代码。
//            sheet = wb.createSheet("sheet1");
//
//            int rowNum = 0;
//            int colNum = 0;
//
//            if (this.header)
//            {
//                // Create an header row
//                HSSFRow xlsRow = sheet.createRow(rowNum++);
//
//                HSSFCellStyle headerStyle = wb.createCellStyle();
//                headerStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
//                headerStyle.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
//                HSSFFont bold = wb.createFont();
//                bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//                bold.setColor(HSSFColor.WHITE.index);
//                headerStyle.setFont(bold);
//
//                Iterator iterator = this.model.getHeaderCellList().iterator();
//
//                while (iterator.hasNext())
//                {
//                    HeaderCell headerCell = (HeaderCell) iterator.next();
//
//                    String columnHeader = headerCell.getTitle();
//
//                    if (columnHeader == null){
//                        columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
//                    }
//                    if(StringUtils.isNotEmpty(columnHeader)){
//                        HSSFCell cell = xlsRow.createCell((short) colNum++);
//                        //set a string value for the cell. Please note that if you are using full 16 bit unicode you should call setEncoding() first.
//                        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
//                        cell.setCellValue(escapeColumnValue(columnHeader));
//                        cell.setCellStyle(headerStyle);
//                    }
//                }
//            }
//
//            // get the correct iterator (full or partial list according to the exportFull field)
//            RowIterator rowIterator = this.model.getRowIterator(this.exportFull);
//            // iterator on rows
//
//            while (rowIterator.hasNext())
//            {
//                Row row = rowIterator.next();
//                HSSFRow xlsRow = sheet.createRow(rowNum++);
//                colNum = 0;
//
//                // iterator on columns
//                ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());
//
//                while (columnIterator.hasNext())
//                {
//                    Column column = columnIterator.nextColumn();
//                    // Get the value to be displayed for the column
//                    Object value = column.getValue(this.decorated);
//                    String tile=column.getHeaderCell().getTitle();
//                    if(StringUtils.isNotEmpty(tile)){
//                        HSSFCell cell = xlsRow.createCell((short) colNum++);
//                        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
//                        cell.setCellValue(escapeColumnValue(value));
//                    }
//                }
//            }
            wb.write(out);
            out.close();
            Thread.sleep(3000);
           File files =  new File("D:/exportData");
           //压缩后，删除原有文件
           deleteFile(files);
        }
        catch (Exception e)
        {
            throw new ExcelGenerationException(e);
        }
    }
   
    private void deleteFile(File file){
       if(file.exists()){
        if(file.isFile()){
         file.delete();
        }else if(file.isDirectory()){
         File files[] = file.listFiles();
         for(int i=0;i<files.length;i++){
          this.deleteFile(files[i]);
         }
        }
        file.delete();
       }else{
        System.out.println("所删除的文件不存在！"+'\n');
       }
    }

   
    public OutputStream toExcel(OutputStream out) throws Exception {  
       WritableWorkbook wwb = null;  
       BufferedOutputStream output = new BufferedOutputStream(out);
        String fileName = DateFormatUtils.format(System.currentTimeMillis(), "yyyy-MM-dd HH_mm_ss_sss")+".xls";
        fileName = new String(fileName.getBytes("gb2312"), "ISO-8859-1");
        String os = System.getProperty("os.name").toLowerCase();
       String fileDir = "";
       if(os.startsWith("windows")){  
              //windows system 
            fileDir ="D:/exportData";
          }else{  
              //unix
          fileDir = "/exportData";
          }  
        File directory = new File(fileDir);
        if(directory.exists())
        {
        }
        else
        {
       directory.mkdirs();
        }
        File file = new File(directory.getPath()+"/" + fileName);
        if(!file.exists())
        {
       file.createNewFile();      
        }
            // 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象  
             wwb = Workbook.createWorkbook(file);  
            int s = 0;
        if (wwb != null) {  
     RowIterator rowIterator = this.model.getRowIterator(this.exportFull);
     while(s++<sheetTotal && rowIterator.hasNext() )
     {
            // 创建一个可写入的工作表  
            // Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置  
            WritableSheet ws = wwb.createSheet("sheet"+s, s);  
           if (this.header)
              {

                  Iterator iterator = this.model.getHeaderCellList().iterator();
                  int j = 0;
                  while (iterator.hasNext())
                  {
                      HeaderCell headerCell = (HeaderCell) iterator.next();
                      String columnHeader = headerCell.getTitle();
                      if (columnHeader == null)
                      {
                          columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
                      }
                      if(StringUtils.isNotEmpty(columnHeader))
                      {
                          ws.addCell(new Label(j, 0, escapeColumnValue(columnHeader)));  
                      }
                      j++;
                  }
              }
            int i = 1;  
            while (rowIterator.hasNext())
            {  
           Row row = rowIterator.next();
         int k = 0;//下面则是迭代报表数据，具体数据获取则根据自己项目框架，此处压缩类为ant.jar下面的类，360压缩不可以，会导致路径不正确。
         ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());
         while (columnIterator.hasNext())
                 {
                     Column column = columnIterator.nextColumn();
                     // Get the value to be displayed for the column
                     Object value = column.getValue(this.decorated);
                     String tile=column.getHeaderCell().getTitle();
                     if(StringUtils.isNotEmpty(tile))
                     {
                    ws.addCell(new Label(k++, i, escapeColumnValue(value)));  
                     }
                 }
         i++;
         if(i>9)
         {
         break;
         }
           
            }
         }
     }
                 // 从内存中写入文件中  
                wwb.write();  
                // 关闭资源，释放内存  
                wwb.close();
                //TODO zip
               zip(directory.getPath()+".zip",new File(directory.getPath()));
                //to page
                BufferedInputStream in = new BufferedInputStream(new FileInputStream(new File(directory.getPath()+".zip")));
//                FileUtils.;
                byte[] b = new byte[30080];
                int ii;
                while((ii = in.read(b)) != -1)
                {
                output.write(b, 0, ii);
                }
               in.close();
              new File(directory.getPath()+".zip").delete();
             return output;
    } 

   
    private void zip(String zipFileName,File inputFile)throws Exception
{
ZipOutputStream zipOut = new ZipOutputStream(new FileOutputStream(zipFileName));
zip(zipOut,inputFile,"");
zipOut.close();
}

private void zip(ZipOutputStream out ,File f,String base)throws Exception
{
if(f.isDirectory())
{
File[] fl = f.listFiles();
out.putNextEntry(new ZipEntry(base + "/"));
base = base.length() == 0 ?"":base+"/";
for(int i = 0 ; i < fl.length; i++)
{
zip(out,fl[i],base+fl[i]);
}
}else
{
out.putNextEntry(new ZipEntry(base));
FileInputStream in = new FileInputStream(f);
int b ;
System.out.println(base);
while((b = in.read())!=-1)
{
out.write(b);
}
in.close();
}
}

评论

1 楼 1927105 2011-10-17  

多写文字，少贴代码哈