jxl导出超过65535数据的通用功能

/**
 * 
 */

import jxl.JXLException;
import jxl.Workbook;
import jxl.write.*;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

/** 
 * @ClassName: ExcelUtil 
 * @Description: TODO(这里用一句话描述这个类的作用) 
 * @author zhoushun 
 * @date 2013年12月13日 下午3:35:27 
 *  
 */
public class ExcelUtil {

    private static org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(ExcelUtil.class);

	public static void createXls(OutputStream os, String title, List head, List data) throws IOException, JXLException {
		WritableWorkbook wwb = Workbook.createWorkbook(os);
		WritableFont wfTitle = new WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD);
		WritableFont wfCell = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD);
        WritableFont wfHead = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
		WritableCellFormat wcfTitle = new WritableCellFormat(wfTitle);
		WritableCellFormat wcfCell = new WritableCellFormat(wfCell);
        WritableCellFormat wcfHead = new WritableCellFormat(wfHead);
		wcfCell.setWrap(true);
		wcfTitle.setAlignment(Alignment.CENTRE);

		int iMaxLines = 60000;
		int totalLines = data.size();
		int page = totalLines / iMaxLines;
		System.out.println("page="+page);
		int lastLines = totalLines % iMaxLines;//尾页要写的行数
		if (totalLines == 0) {
            return;
        }
		if (page >= 1 && lastLines > 0) {
			page = page + 1;
        }
		if(page == 0){
			page = 1;
		}
	 
		// 合并标题单元格

		//sheet.mergeCells(0, 0, head.size()-1, 0);
		// 设置标题行高
		//sheet.setRowView(0, 1000);
		//sheet.setRowView(1, 1000);
		
		// title
		for(int p =0;p<page;p++){
			
			WritableSheet sheet = wwb.createSheet("sheet"+p, p);
			
			int iStartRow = 0;
			int iEndRow = 0;
			if (p == 0) {
                iStartRow = 0;
                if (page == 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;

                }
            } else {
                iStartRow = p * iMaxLines;
                if (p == page - 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }

            }
			
			 List listPageValue = new ArrayList();//保存每页的数据
             for (int i = iStartRow; i <= iEndRow; i++) {
                 if (data.get(i) == null) {
                     continue;
                 }
                 System.out.println(data.get(i));
                 listPageValue.add(data.get(i));
             }
             
			//title
			if(title != null) {
                Label labTitle = new Label(0, 0, title, wcfTitle);
                sheet.addCell(labTitle);
            }

			// head
            if(head != null && head.size() > 0) {

                for (int i = 0; i < head.size(); i++) {
                    Label labHead = new Label(i, 1, (String) head.get(i), wcfHead);
                    sheet.addCell(labHead);
                    sheet.setColumnView(i, 30);
                }
            }
			
			// data
			if (data != null && data.size() > 0) {
				if (data.get(0) instanceof Map) {
					// list中是Map
					for (int i=0; i<listPageValue.size(); i++) {
						System.out.println(i);
						Map m = (Map) listPageValue.get(i);
						Set s = m.keySet();
						Iterator itr = s.iterator();
						for (int j=0; itr.hasNext(); j++) {
							Object key = itr.next();
							try {
								Label labData = new Label(j, i+2, m.get(key).toString(),wcfCell);
								sheet.addCell(labData);
							} catch (NullPointerException e){
                                logger.info("" + e);
                            }
						}
					}
				} else if (data.get(0) instanceof List) {
					// list中是list
					for (int i=0; i<listPageValue.size(); i++) {
						List lst = (List) listPageValue.get(i);
						for (int j=0; j<lst.size(); j++) {
							try {
								Label labData = new Label(j, i+2, lst.get(j).toString(),wcfCell);
								sheet.addCell(labData);
							} catch (NullPointerException e){
                                logger.info("" + e);
                            }
						}
					}
				} else if (data.get(0) instanceof Object[]) {
					// list中是数组
					for (int i=0; i<listPageValue.size(); i++) {
						Object[] obj = (Object[]) listPageValue.get(i);
						for (int j=0; j<obj.length; j++) {
							try {
								Label labData = new Label(j, i+2, obj[j].toString(),wcfCell);
								//System.out.println(obj[j]);
								sheet.addCell(labData);
							} catch (NullPointerException e){
                                logger.info("" + e);
                            }
						}
					}
				} else {
					//一般对象
                    Class clazz = data.get(0).getClass();
                    Field[] fields = clazz.getDeclaredFields();
                    int j = 0;
                    //title
                    for (int i = 0; i < fields.length; i++) {
                        if (!fields[i].getName().equalsIgnoreCase("serialVersionUID")) {
                            Label lable = new Label(j++, 0, fields[i].getName(), wcfHead);
                            sheet.addCell(lable);
                        }
                    }

                    //getmethods get
                    Method[] methods = clazz.getMethods();
                    List<Method> getMethods = new ArrayList<Method>();
                    for (j = 0; j < fields.length; j++) {
                        for (int i = 0; i < methods.length; i++) {
                            if (methods[i].getName().equalsIgnoreCase(
                                    "get" + fields[j].getName())) {
                                getMethods.add(methods[i]);
                            }
                        }
                    }
                    int i = 0;
                    for (Object obj : listPageValue) {
                        int r = 0;
                        i++;
                        for (Method method : getMethods) {
                            try {
                                Object result = method.invoke(obj);
                                if (result == null) {
                                    result = "";
                                }
                                if (result instanceof Date) {
                                    result = DateUtils.format((Date) result, DateUtils
                                            .getSecondFormatter());
                                }
                                Label lable = new Label(r++, i, result == null ? "" : result
                                        .toString(), wcfCell);
                                sheet.addCell(lable);
                            } catch (IllegalArgumentException e) {
                                logger.info("excel" + e);
                            } catch (IllegalAccessException e) {
                                logger.info("excel" + e);
                            } catch (InvocationTargetException e) {
                                logger.info("excel" + e);
                            }
                        }
                    }
				}
	
			}
			
			
			
		}
		wwb.write();
		wwb.close();
	}
}