在web开发中,经常会遇到需要把数据导出到EXCEL中,并提供下载。

    那么本文将提供一个通用的导出EXCEL的工具类。。。具体的实现或自定义的样式,可以自己定义。

    请看下文代码

    package com.xuahua.utils;
    
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    import jxl.Workbook;
    import jxl.format.Alignment;
    import jxl.format.Border;
    import jxl.format.BorderLineStyle;
    import jxl.format.Colour;
    import jxl.format.VerticalAlignment;
    import jxl.write.Label;
    import jxl.write.Number;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableFont;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import jxl.write.WriteException;
    
    import org.apache.log4j.Logger;
    
    /**
     * @author wyong 提供公用方法,来生成xls。 具体的数据对象由writeContent实现,标签页的头由getHeaders实现。
     * 
     */
    public class ExportExcelUtils {
    
    	protected Label label;
    	protected Number number;
    
    	protected WritableWorkbook wwb;
    	protected WritableSheet ws;
    	protected int rowIndex = 0;
    	protected int MAXRECORD = 60000;// 每个工作空间最大记录条数。
    
    	protected WritableCellFormat titleFormat1;
    	protected WritableCellFormat headerFormat1;
    	protected WritableCellFormat cellFormat1;
    	protected WritableCellFormat dateCellFormat;
    
    	private static final Logger logger = Logger
    			.getLogger(ExportExcelUtils.class);
    
    	public ExportExcelUtils() {
    		try {
    			headerFormat1 = initHeaderFormat();
    			cellFormat1 = initCellFormat();
    			dateCellFormat = initDateFormat();
    			titleFormat1 = initTitleFormate();
    		} catch (WriteException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    
    	public void xls(OutputStream os, String sheetName, List contentList)
    			throws Exception {
    		wwb = Workbook.createWorkbook(os);
    		try {
    			int maxsize = contentList.size();
    			if (maxsize > MAXRECORD) {
    				int s = maxsize % MAXRECORD == 0 ? maxsize / MAXRECORD
    						: maxsize / MAXRECORD + 1;
    				List tmpList = new ArrayList(MAXRECORD);
    				for (int i = 0; i < s; i++) {
    					rowIndex = 0;
    					tmpList = contentList.subList(i * MAXRECORD, (i + 1)
    							* MAXRECORD > maxsize ? maxsize : (i + 1)
    							* MAXRECORD);
    					ws = wwb.createSheet(sheetName + "-" + i, i);
    					this.writeHeaders();
    					this.writeContent(tmpList);
    				}
    			} else {
    				ws = wwb.createSheet(sheetName, 0);
    				this.writeHeaders();
    				this.writeContent(contentList);
    			}
    		} finally {
    			wwb.write();
    			wwb.close();
    
    			os.flush();
    			os.close();
    		}
    	}
    
    	/**
    	 * @param objlist
    	 *            需要写入xls文件中的对象。做过处理的一个标签页最多60000条。
    	 * @throws Exception
    	 */
    	public void writeContent(List objlist) throws Exception {
    		throw new Exception("u` must implements this method.");
    	};
    
    	/**
    	 * xls文件中的标题头。
    	 * 
    	 * @return
    	 * @throws Exception
    	 */
    	public List<Map<String, Object>> getHeaders() throws Exception {
    		throw new Exception("u` must implements this method.");
    	};
    
    	// 写表头
    	public void writeHeaders() throws Exception {
    		List<Map<String, Object>> headers = this.getHeaders();
    
    		for (int i = 0; i < headers.size(); i++) {
    			Map<String, Object> map = headers.get(i);
    
    			Label label = new Label(i, rowIndex, map.get("value").toString(),
    					headerFormat1);
    			ws.addCell(label);
    
    		}
    		rowIndex++;
    	}
    
    	/**
    	 * 格式化日期单元格
    	 * 
    	 * @return
    	 * @throws WriteException
    	 */
    	protected WritableCellFormat initDateFormat() throws WriteException {
    		jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd HH:mm:ss");
    		WritableCellFormat dateCellFormat = new jxl.write.WritableCellFormat(df);
    		dateCellFormat.setAlignment(Alignment.LEFT);
    		dateCellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    		dateCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
    		dateCellFormat.setAlignment(Alignment.CENTRE); //设置水平对齐
    		dateCellFormat.setWrap(false); //设置自动换行
    		return dateCellFormat;
    	}
    
    	/**
    	 * 格式化单元格样式
    	 * 
    	 * @return
    	 * @throws WriteException
    	 */
    	protected WritableCellFormat initCellFormat() throws WriteException {
    		WritableFont cellFont1 = new WritableFont(WritableFont.TIMES, 12);
    		WritableCellFormat cellFormat1 = new WritableCellFormat(cellFont1);
    		cellFormat1.setAlignment(Alignment.LEFT);
    		cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
    		cellFormat1.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
    		cellFormat1.setAlignment(Alignment.CENTRE); //设置水平对齐
    		cellFormat1.setWrap(false); //设置自动换行
    		return cellFormat1;
    
    	}
    
    	/**
    	 * @return 格式化headerformate
    	 * @throws WriteException
    	 */
    	protected WritableCellFormat initHeaderFormat() throws WriteException {
    		WritableFont headerFont1 = new WritableFont(WritableFont.TIMES, 14,
    				WritableFont.BOLD);
    		headerFont1.setColour(Colour.BLUE);
    		WritableCellFormat headerFormat1 = new WritableCellFormat(headerFont1);
    		headerFormat1.setAlignment(Alignment.CENTRE);
    		headerFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
    		headerFormat1.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
    		headerFormat1.setBackground(Colour.YELLOW);
    		headerFormat1.setAlignment(Alignment.CENTRE); //设置水平对齐
    		headerFormat1.setWrap(false); //设置自动换行
    		return headerFormat1;
    	}
    
    	/**
    	 * 格式化initTitleFormate1
    	 * 
    	 * @return
    	 * @throws WriteException
    	 */
    	protected WritableCellFormat initTitleFormate() throws WriteException {
    		WritableFont titleFont1 = new WritableFont(WritableFont.TIMES, 24,
    				WritableFont.BOLD);
    		titleFont1.setColour(Colour.BLUE);
    		WritableCellFormat titleFormat1 = new WritableCellFormat(titleFont1);
    		titleFormat1.setAlignment(Alignment.CENTRE);
    		titleFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
    		titleFormat1.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
    		titleFormat1.setBackground(Colour.YELLOW);
    		titleFormat1.setAlignment(Alignment.CENTRE); //设置水平对齐
    		titleFormat1.setWrap(false); //设置自动换行
    		return titleFormat1;
    	}
    }

    联系QQ:343886028 --加QQ群 145315854