Excels.java

package com.bj58.hrg.creative.pingjia.web.utils;

import java.io.ByteArrayOutputStream;
import java.io.IOException;

import org.apache.log4j.Logger;

import jxl.Cell;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * Excel 生成工具
 * @author lzxz1234<lzxz1234@gmail.com>
 */
public class Excels {
    
    private int nextSheetIndex;
    private ByteArrayOutputStream bos;
    private WritableWorkbook wwb;
    
    public static Excels create() throws IOException {
        
        Excels excels = new Excels();
        excels.bos = new ByteArrayOutputStream(1024 * 1024);//初始1M
        excels.wwb = Workbook.createWorkbook(excels.bos);
        excels.nextSheetIndex = 0;
        return excels;
    }
    
    /**
     * 创建工作薄
     * @param sheetNamePrefix
     * @return
     */
    public Sheet addSheets(String sheetNamePrefix) {
        
        return new Sheet(sheetNamePrefix);
    }
    
    public byte[] toByteArray() throws WriteException, IOException {
        
        wwb.write();
        wwb.close();
        return bos.toByteArray();
    }
    
    public String toHtmlTable() throws Exception {
        
        StringBuilder sb = new StringBuilder();
        for(WritableSheet sheet : wwb.getSheets()) 
            if(sheet != null && sheet.getRows() > 0) {
                sb.append(sheet.getName()).append("：<br />");
                sb.append("<table style='font-size:11px;color:#333333;border-width: 1px;border-color: #999999;border-collapse: collapse'>");
                for(int i = 0; i < sheet.getRows(); i ++) {
                    sb.append("<tr style='background-color:#d4e3e5;'>");
                    for(Cell cell : sheet.getRow(i))
                        sb.append("<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #a9c6c9;'>").append(cell.getContents()).append("</td>");
                    sb.append("</tr>");
                }
                sb.append("</table>");
            }
        return sb.toString();
    }
    
    /**
     * 带自动翻页功能的工作薄操作对象
     * @author lzxz1234<lzxz1234@gmail.com>
     */
    public class Sheet {
        
        private Logger log = Logger.getLogger(Sheet.class);
        private final int MAX_LINE_PER_SHEET = 60000;
        private String sheetNamePrefix;
        private WritableSheet sheet;
        private int rowIndex;
        
        public Sheet(String sheetNamePrefix) {
            
            this.sheetNamePrefix = sheetNamePrefix;
            this.rollSheet();
        }
        
        public void occur(String[] columns) {
            
            if(columns == null) return;
            for(int i = 0; i < columns.length; i ++) {
                try {
                    WritableCell wc = new Label(i, rowIndex, columns[i]);
                    sheet.addCell(wc);
                } catch (Exception e) {
                    log.error("单条回调失败", e);
                }
            }
            this.rowIndex ++;
            if(rowIndex % MAX_LINE_PER_SHEET == 0) //单个sheet最大60000行
                this.rollSheet();
        }
        
        private void rollSheet() {
            
            int nextSheetSeq = rowIndex / MAX_LINE_PER_SHEET + 1;
            String sheetName = sheetNamePrefix + (nextSheetSeq == 1 ? "" : "-" + nextSheetSeq);
            this.sheet = wwb.createSheet(sheetName, nextSheetIndex ++);
            this.rowIndex = 0;
        }
    }
}