jxl 做的报表通用方法

ExcelUtilToolTest.java

package com.sobey.pms.util.excel;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import junit.framework.TestCase;

/**
 * <p>Title: TestExcelUtilTool.java</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2009 Sobey,Inc</p>
 * <p>Company: Sobey.Inc</p>
 * @author yaobo 2010-8-9
 * @version 1.0
 */
public class ExcelUtilToolTest extends TestCase{
	public void testCreateExcel(){
		List datas = new ArrayList();
		for (int i = 0; i < 10; i++){
			Data data = new Data("名字" + i, "地址" + i, "公司" + i);
			datas.add(data);
		}
		
		ExcelExportInfo info=null;
	   	String footer[][]=new String[1][2];
	   	footer[0][0]=""; //footer[0][0] 是文本内容,footer[0][1]为文本占的列数
	   	footer[0][1]="3";   
	   	info = new ExcelExportInfo();
	   	info.setColumnHeader("名字, 地址, 公司");
	   	info.setDatas(datas); //list (是po\vo)
	   	info.setFeilds("name,address,company"); // list 集合中对象的feilds
	   	info.setTitle("人员信息"); //设置title,如果为空则excel无标题
	   	info.setFooter(footer); // footer
	   	info.setWidth(new int[]{50, 50, 50}); // int数组为int类型, 为null 取默认宽度20,
	   	
	   	ExcelUtilTool util=new ExcelUtilTool();
	   	ByteArrayOutputStream  tt = util.writeExcelStream(info);
	   	
	   	try{
	   		FileOutputStream pos = new FileOutputStream("c:/test.xls");
	   		pos.write(tt.toByteArray());
	   		tt.close();
	   		pos.close();
	   	}catch (Exception ex){
	   		assert(false);
	   		ex.printStackTrace();
	   	}
	   	assert(true);
	}
}

class Data{
	String name;
	String address;
	String company;
	
	public Data(String name, String address, String company) {
		super();
		this.name = name;
		this.address = address;
		this.company = company;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getCompany() {
		return company;
	}
	public void setCompany(String company) {
		this.company = company;
	}
	
	
}

 

ExcelUtilTool.java

package com.sobey.pms.util.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.log4j.Logger;

import com.sobey.esda.utils.ReflectionUtils;

/**
 * 
 * 通用Excel处理类

 * 
 * 
 */
public class ExcelUtilTool {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(ExcelUtilTool.class);

	/**
	 * 得到 Excel 报表数据的字节

	 * 
	 * @param reportTitle
	 *            报表的标题

	 * 
	 * @param dataInArr
	 *            String[][] 输入的待显示数据数组
	 * @param param_width
	 *            int[] 每列列宽
	 * 
	 * @param columnHeader
	 *            列头标题
	 * 
	 * @param columnFooter
	 *            脚眉 [0]=数据,[1]=表示占有宽度 为 1,2,3,..
	 * 
	 * @param report_format
	 *            String[] 报表显示 格式数组{"","",""}
	 * @throws Exception
	 */
	public byte[] writeExcelReport(String ReportTitle[][],
			String columnHeader[], String dataInArr[][],
			String columnFooter[][], int param_width[]) throws Exception {
		ByteArrayOutputStream bufferOb = null; // 直接输出到流中

		bufferOb = writeExcelStream(ReportTitle, columnHeader, dataInArr,
				columnFooter, param_width);
		if (bufferOb != null)
			return bufferOb.toByteArray();
		else
			return null;
	}

	/**
	 * 
	 * @param ops
	 * @return
	 */
	private WritableWorkbook createWorkBook(OutputStream ops) {
		try {
			return Workbook.createWorkbook(ops);
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 
	 * @param workbook
	 * @param sheetName
	 * @param index
	 * @return
	 */
	private WritableSheet createSheet(WritableWorkbook workbook,
			String sheetName, int index) {
		if (workbook == null)
			return null;
		return workbook.createSheet(sheetName, index);
	}

	/**
	 * 
	 * @param ws
	 * @param reportTitle
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	private void createExcelTitle(WritableSheet ws, String reportTitle[][],
			int len) throws RowsExceededException, WriteException {
		if (ws == null || reportTitle == null)
			return;
		WritableFont boldBigWTF = new WritableFont(WritableFont.ARIAL, 30,
				WritableFont.BOLD);
		WritableFont boldNormalWTF = new WritableFont(WritableFont.ARIAL,
				WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);
		// 填充报表标题,一维是字串，二维是格式(对齐方式):ReportTitle[x][2],对齐方式：left,center,right
		for (int i = 0; i < reportTitle.length; i++) {
			ws.mergeCells(0, i, (len - 1), i); // 合并标题单元格

			WritableCellFormat reportheader = null;
			if (i == 0) {
				reportheader = new WritableCellFormat(boldBigWTF);
			} else {
				reportheader = new WritableCellFormat(boldNormalWTF);
			}

			reportheader.setBorder(Border.NONE, BorderLineStyle.NONE);
			if (reportTitle[i][1].equalsIgnoreCase("left")) {
				reportheader.setAlignment(Alignment.LEFT);
			} else if (reportTitle[i][1].equalsIgnoreCase("center")) {
				reportheader.setAlignment(Alignment.CENTRE);
			} else if (reportTitle[i][1].equalsIgnoreCase("right")) {
				reportheader.setAlignment(Alignment.RIGHT);
			}

			Label lable = new Label(0, i, reportTitle[i][0], reportheader);
			ws.addCell(lable);
			lable = null;
		}
	}

	/**
	 * 
	 * @param ws
	 * @param columnHeader
	 * @param list_start
	 * @throws WriteException
	 */
	private void createColumnHeader(WritableSheet ws, String columnHeader[],
			int list_start) throws WriteException {
		if (ws == null || columnHeader == null)
			return;
		WritableFont wfc = new WritableFont(WritableFont.ARIAL); // 设置字体对象
		WritableCellFormat cellheader = new WritableCellFormat(wfc);
		cellheader.setBackground(Colour.GRAY_25);
		cellheader.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		// 填充columnHeader字段及样式

		for (int i = 0; i < columnHeader.length; i++) {
			Label lable = new Label(i, list_start, columnHeader[i], cellheader);
			ws.addCell(lable);
			lable = null;
		}
	}

	/**
	 * 
	 * @param ws
	 * @param data
	 * @param list_start
	 * @throws WriteException
	 */
	private void fillInData(WritableSheet ws, String data[][], int list_start)
			throws WriteException {
		if (ws == null || data == null)
			return;

		int dataInArr_row = data.length;
		int dataInArr_col = data[0].length;
		WritableFont wfc = new WritableFont(WritableFont.ARIAL); // 设置字体对象
		WritableCellFormat wcfFC = new WritableCellFormat(wfc);
		for (int i = 0; i < dataInArr_row; i++) {
			for (int j = 0; j < dataInArr_col; j++) {
				Label lable = new jxl.write.Label(j, (i + list_start + 1),
						data[i][j], wcfFC);
				ws.addCell(lable);
				lable = null;
			}
		}
	}

	/**
	 * 
	 * @param ws
	 * @param columnFooter
	 * @param rowStart
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	private void createFooter(WritableSheet ws, String columnFooter[][],
			int rowStart) throws RowsExceededException, WriteException {
		if (ws == null || columnFooter == null)
			return;
		// 填充columnFooter字段及统计,一维是字串，二维是格式(跨度):columnFooter[x][2]
		WritableFont wfc = new WritableFont(WritableFont.ARIAL); // 设置字体对象
		WritableCellFormat wcfFC = new WritableCellFormat(wfc);
		if (columnFooter != null) {
			int columnFooterWidth = columnFooter.length;
			int tmpInt = 0, summergeNum = 0;
			for (int i = 0; i < columnFooterWidth; i++) {
				tmpInt = Integer.parseInt(columnFooter[i][1]);
				summergeNum += tmpInt;
				if (tmpInt > 1) {
					ws.mergeCells((summergeNum - tmpInt), (rowStart + 1),
							(summergeNum - 1), (rowStart + 1)); // 合并单元格

				}
				Label lable = new Label((summergeNum - tmpInt), (rowStart + 1),
						columnFooter[i][0], wcfFC);
				ws.addCell(lable);
				lable = null;
			}
		}
	}

	/**
	 * 
	 * @param info
	 * @return
	 * @throws Exception
	 */
	public ByteArrayOutputStream writeExcelStream(ExcelExportInfo info) {
		if (logger.isDebugEnabled()) {
			logger.debug("writeExcelStream(ExcelExportInfo) - start");
		}

		if (info == null || info.getDatas() == null
				|| info.getColumnHeader() == null || info.getFeilds() == null) {
			if (logger.isDebugEnabled()) {
				logger.debug("info is null");
				logger.debug("writeExcelStream(ExcelExportInfo) - end");
			}
			return null;
		}
		String title[][] = null;
		String datas[][] = null;
		String feilds[] = info.getFeilds();
		List list = info.getDatas();

		int rows = list.size();
		int columns = info.getFeilds().length;

		if (logger.isDebugEnabled()) {
			logger.debug(" rows : " + rows + " columns:" + columns);
		}
		if (rows == 0 || columns == 0)
			return null;

		if (info.getTitle() != null) {
			title = new String[1][2];
			title[0][0] = info.getTitle();
			title[0][1] = "center";
			if (title[0][0] == null)
				title[0][0] = "Excel报表打印";
		}

		datas = new String[rows][];

		for (int i = 0; i < rows; i++) {
			Object obj = list.get(i);
			if (obj == null)
				continue;
			datas[i] = new String[columns];
			for (int j = 0; j < columns; j++) {
				datas[i][j] = getParameter(obj, feilds[j]);
			}
		}

		ByteArrayOutputStream returnByteArrayOutputStream = null;

		try {
			returnByteArrayOutputStream = this.writeExcelStream(title, info
					.getColumnHeader(), datas, info.getFooter(), info
					.getWidth());
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (logger.isDebugEnabled()) {
			logger.debug("writeExcelStream(ExcelExportInfo) - end");
		}
		return returnByteArrayOutputStream;
	}

	/**
	 * 
	 * @param obj
	 * @param attr
	 * @return
	 */
	private String getParameter(Object obj, String attr) {
		String value = null;
		Object valuObj = ReflectionUtils.invokeGetterMethod(obj, attr);
		value = (valuObj == null) ? null : valuObj.toString();
		return value;
	}

	/**
	 * 
	 * 得到excel报表数据的字节流
	 * 
	 * @param ReportTitle
	 * @param columnHeader
	 * @param dataInArr
	 * @param columnFooter
	 * @param param_width
	 * @throws Exception
	 */
	public ByteArrayOutputStream writeExcelStream(String reportTitle[][],
			String columnHeader[], String datas[][], String columnFooter[][],
			int paramWidth[]) throws Exception {
		ByteArrayOutputStream bufferOb = new ByteArrayOutputStream(); // 直接输出到字节流中(内存中)
		int i = 0;
		int list_start = 3; // 列表数据从第4行开始写(默认)
		int default_column_width = 20; // 表格默认宽度
		int dataInArr_row = (datas == null) ? 0 : datas.length; // 得到数组行数
		int dataInArr_col = (datas == null) ? 0 : datas[0].length; // 得到数组列数

		if (dataInArr_col == 0 && paramWidth != null) {
			dataInArr_col = paramWidth.length;
		} else if (dataInArr_col == 0)
			dataInArr_col = 1;

		if (paramWidth == null) {
			paramWidth = new int[dataInArr_col];
			for (i = 0; i < dataInArr_col; i++) {
				paramWidth[i] = default_column_width;
			}
		}
		WritableWorkbook wwb = createWorkBook(bufferOb); // 建立excel工作空间
		WritableSheet ws = createSheet(wwb, "报表显示(打印)", 0); // 建立报表表单

		// 设置表格宽度
		for (i = 0; i < dataInArr_col; i++) {
			ws.setColumnView(i, paramWidth[i]);
		}
		// 填充报表标题,一维是字串，二维是格式(对齐方式):ReportTitle[x][2],对齐方式：left,center,right
		int reportTitleLen = reportTitle == null ? 0 : reportTitle.length;
		list_start = reportTitleLen;

		this.createExcelTitle(ws, reportTitle, dataInArr_col);
		this.createColumnHeader(ws, columnHeader, list_start); // list_start从多少行开始写数据
		this.fillInData(ws, datas, list_start);
		this.createFooter(ws, columnFooter, dataInArr_row + list_start);

		wwb.write();
		wwb.close();
		reportTitle = null;
		columnHeader = null;
		datas = null;
		columnFooter = null;
		paramWidth = null;
		return bufferOb;
	}

	/**
	 * 
	 * @param info
	 * @return
	 * @throws Exception
	 */
	public void writeExcel(OutputStream stream, ExcelExportInfo info) {
		if (logger.isDebugEnabled()) {
			logger.debug("writeExcelStream(ExcelExportInfo) - start");
		}

		if (info == null || info.getDatas() == null
				|| info.getColumnHeader() == null || info.getFeilds() == null) {
			if (logger.isDebugEnabled()) {
				logger.debug("info is null");
				logger.debug("writeExcelStream(ExcelExportInfo) - end");
			}
			return;
		}
		String title[][] = null;
		String datas[][] = null;
		String feilds[] = info.getFeilds();
		List list = info.getDatas();

		int rows = list.size();
		int columns = info.getFeilds().length;

		if (logger.isDebugEnabled()) {
			logger.debug(" rows : " + rows + " columns:" + columns);
		}
		if (rows == 0 || columns == 0)
			return;

		if (info.getTitle() != null) {
			title = new String[1][2];
			title[0][0] = info.getTitle();
			title[0][1] = "center";
			if (title[0][0] == null)
				title[0][0] = "Excel报表打印";
		}

		datas = new String[rows][];

		for (int i = 0; i < rows; i++) {
			Object obj = list.get(i);
			if (obj == null)
				continue;
			datas[i] = new String[columns];
			for (int j = 0; j < columns; j++) {
				datas[i][j] = getParameter(obj, feilds[j]);
			}
		}

		try {
			this.writeExcel(stream, title, info.getColumnHeader(), datas, info
					.getFooter(), info.getWidth());
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (logger.isDebugEnabled()) {
			logger.debug("writeExcelStream(ExcelExportInfo) - end");
		}
	}

	/**
	 * 
	 * 得到excel报表数据的字节流
	 * 
	 * @param ReportTitle
	 * @param columnHeader
	 * @param dataInArr
	 * @param columnFooter
	 * @param param_width
	 * @throws Exception
	 */
	public void writeExcel(OutputStream stream, String reportTitle[][],
			String columnHeader[], String datas[][], String columnFooter[][],
			int paramWidth[]) throws Exception {
		int i = 0;
		int list_start = 3; // 列表数据从第4行开始写(默认)
		int default_column_width = 20; // 表格默认宽度
		int dataInArr_row = (datas == null) ? 0 : datas.length; // 得到数组行数
		int dataInArr_col = (datas == null) ? 0 : datas[0].length; // 得到数组列数

		if (dataInArr_col == 0 && paramWidth != null) {
			dataInArr_col = paramWidth.length;
		} else if (dataInArr_col == 0)
			dataInArr_col = 1;

		if (paramWidth == null) {
			paramWidth = new int[dataInArr_col];
			for (i = 0; i < dataInArr_col; i++) {
				paramWidth[i] = default_column_width;
			}
		}
		WritableWorkbook wwb = createWorkBook(stream); // 建立excel工作空间
		WritableSheet ws = createSheet(wwb, "报表显示(打印)", 0); // 建立报表表单

		// 设置表格宽度
		for (i = 0; i < dataInArr_col; i++) {
			ws.setColumnView(i, paramWidth[i]);
		}
		// 填充报表标题,一维是字串，二维是格式(对齐方式):ReportTitle[x][2],对齐方式：left,center,right
		int reportTitleLen = reportTitle == null ? 0 : reportTitle.length;
		list_start = reportTitleLen;

		this.createExcelTitle(ws, reportTitle, dataInArr_col);
		this.createColumnHeader(ws, columnHeader, list_start); // list_start从多少行开始写数据
		this.fillInData(ws, datas, list_start);
		this.createFooter(ws, columnFooter, dataInArr_row + list_start);

		wwb.write();
		wwb.close();
		reportTitle = null;
		columnHeader = null;
		datas = null;
		columnFooter = null;
		paramWidth = null;
	}

	/**
	 * 读excel表格中文字

	 * 
	 * @param fileName
	 * @param x
	 * @param y
	 * @throws Exception
	 */
	public String readExcelCell(String fileName, int x, int y) throws Exception {
		File f = new File(fileName);
		InputStream os = new FileInputStream(f);
		jxl.Workbook book = Workbook.getWorkbook(os);
		jxl.Sheet sheet = book.getSheet(0);
		Cell cell1 = sheet.getCell(x, y);
		String result = cell1.getContents();
		book.close();
		return result;
	}

	/**
	 * 读excel表格中文字

	 * 
	 * @param book
	 * @param x
	 * @param y
	 * @throws Exception
	 */
	public String readExcelCell(Workbook book, int x, int y) throws Exception {
		jxl.Sheet sheet = book.getSheet(0);
		Cell cell1 = sheet.getCell(x, y);
		String result = cell1.getContents();
		return result;
	}

	/**
	 * 把excel数据读入到数组中
	 * 
	 * @param fileName
	 * @param sheetNo
	 *            工作表顺序号,默认为0
	 * @param x_start
	 *            左面开始的列数
	 * @param y_start
	 *            头开始的行数
	 * @param width
	 *            如果宽度为0(默认),就读出所有列
	 * @param height
	 *            如果高度为0(默认),就读出所有行
	 * @throws Exception
	 */
	public String[][] readExcel(String fileName, int sheetNo, int x_start,
			int y_start, int columns, int rows) throws Exception {
		// 数据校正
		if (sheetNo < 0)
			sheetNo = 0;
		if (x_start < 0)
			x_start = 0;
		if (y_start < 0)
			y_start = 0;

		Workbook book = null;
		Sheet sheet = null; // 获得第一个工作表对象
		int tmpInt = 0;

		book = this.getWorkBook(fileName);
		if (book == null)
			return null;
		sheet = book.getSheet(sheetNo);
		tmpInt = sheet.getColumns();

		if (columns == 0 || columns > tmpInt)
			columns = tmpInt;
		tmpInt = sheet.getRows();

		if (rows == 0 || rows > tmpInt)
			rows = tmpInt;
		String[][] reArr = new String[rows][columns];
		for (int y = 0; y < rows; y++) {
			for (int x = 0; x < columns; x++) {
				int cel_x = x_start + x;
				int cel_y = y_start + y;
				reArr[y][x] = sheet.getCell(cel_x, cel_y).getContents(); // cel_x,列

																			// cel_y
																			// 行

			}
		}
		book.close();
		return reArr;
	}

	/**
	 * 把excel数据读入到数组中
	 * 
	 * @param inputstream
	 * @param sheetNo
	 *            工作表顺序号,默认为0
	 * @param x_start
	 *            左面开始的列数
	 * @param y_start
	 *            头开始的行数
	 * @param width
	 *            如果宽度为0(默认),就读出所有列
	 * @param height
	 *            如果高度为0(默认),就读出所有行
	 * @throws Exception
	 */
	public String[][] readExcel(InputStream ins, int sheetNo, int x_start,
			int y_start, int width, int height) throws Exception {
		// 数据校正
		if (sheetNo < 0)
			sheetNo = 0;
		if (x_start < 0)
			x_start = 0;
		if (y_start < 0)
			y_start = 0;
		Workbook book = null;
		Sheet sheet = null; // 获得第一个工作表对象
		int tmpInt = 0;

		book = this.getWorkBook(ins);
		if (book == null)
			return null;
		sheet = book.getSheet(sheetNo);
		tmpInt = sheet.getColumns();
		if (width == 0 || width > tmpInt)
			width = tmpInt;
		tmpInt = sheet.getRows();
		if (height == 0 || height > tmpInt)
			height = tmpInt;
		String[][] reArr = new String[width][height];
		int x = 0, y = 0;
		for (x = x_start; x < (x_start + width); x++) {
			for (y = y_start; y < (y_start + height); y++) {
				reArr[x][y] = sheet.getCell(x, y).getContents();
			}
		}
		book.close();
		return reArr;
	}

	/**
	 * 
	 * @param ins
	 * @return
	 */
	public Workbook getWorkBook(InputStream ins) {
		Workbook book = null;
		try {
			book = Workbook.getWorkbook(ins);
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return book;
	}

	/**
	 * 
	 * @param ins
	 * @return
	 */
	public Workbook getWorkBook(String fileName) {
		File file = null;
		InputStream is = null;

		if (fileName == null)
			return null;

		file = new File(fileName);

		if (!file.exists())
			return null;
		try {
			is = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return this.getWorkBook(is);
	}

	/**
	 * 写数组数据到excel
	 * 
	 * @param fileName
	 *            写入的excel文件明

	 * @param sheetNo
	 *            工作表顺序号,默认为0
	 * @param sheetName
	 *            工作表名
	 * @param x_start
	 *            左面开始的列数
	 * @param y_start
	 *            头开始的行数
	 * @param columnW
	 *            列宽
	 * @param inputArr
	 *            输入的数组

	 * @param maxPerPage
	 *            每页最大记录数(最大记录数为10000)
	 * @throws Exception
	 */
	public void writeExcel(String fileName, int sheetNo, String sheetName,
			int x_start, int y_start, int columnW, String[][] inputArr,
			int maxPerPage) throws Exception {
		// 数据校正
		if (sheetNo < 0)
			sheetNo = 0;
		if (sheetName == null)
			sheetName = "数据列表";
		if (x_start < 0)
			x_start = 0;
		if (y_start < 0)
			y_start = 0;
		if (columnW < 0)
			columnW = 10;
		if (maxPerPage < 0 || maxPerPage > 10000) {
			maxPerPage = 10000;
			System.out.println("每页最大记录数超出最大数10000,改为10000输出");
		}

		// 数据写入excel
		int dataStart = 0; // 数据行开始

		int dataEnd = 0; // 数据行结束

		if (inputArr != null && inputArr.length > 0) {
			int inputArrLength = inputArr[0].length; // 数据行数
			int loop = 0;
			if (inputArrLength % maxPerPage == 0) {
				loop = inputArrLength / maxPerPage;
			} else {
				loop = inputArrLength / maxPerPage + 1;
			}
			int baseYstart = y_start;
			String fileExtName = fileName.substring(fileName.lastIndexOf("."));
			String filePathName = fileName.replaceAll(fileExtName, "");
			for (int i = 0; i < loop; i++) {
				dataStart = i * maxPerPage;
				y_start = baseYstart + dataStart;
				dataEnd = dataStart + maxPerPage;
				if (dataEnd >= inputArrLength) {
					dataEnd = inputArrLength;
				}
				if (i > 0) {
					fileName = filePathName + " (" + i + ")" + fileExtName;
				}
				writeExcel(fileName, sheetNo, sheetName, x_start, baseYstart,
						columnW, inputArr, dataStart, dataEnd);
			}
		}
		inputArr = null;
	}

	/**
	 * 写数组数据到excel
	 * 
	 * @param fileName
	 * @param sheetNo
	 * @param sheetName
	 * @param x_start
	 * @param y_base
	 * @param columnW
	 * @param inputArr
	 * @param dataStart
	 * @param dataEnd
	 * @throws Exception
	 */
	private void writeExcel(String fileName, int sheetNo, String sheetName,
			int x_start, int y_base, int columnW, String[][] inputArr,
			int dataStart, int dataEnd) throws Exception {
		File os = new File(fileName);
		if (os.exists()) {
			if (!os.delete()) {
				throw new Exception("文件已经存在,并且不能删除");
			}
		}
		WritableWorkbook book = null;
		WritableSheet ws = null;
		if (os.createNewFile()) {
			book = Workbook.createWorkbook(os);
			ws = book.createSheet(sheetName, sheetNo);
		} else {
			throw new Exception("文件创建失败");
		}
		if (inputArr != null && inputArr.length > 0) {
			int inputArrWidth = inputArr.length;
			int x = 0, y = 0;
			for (x = 0; x < inputArrWidth; x++) {
				ws.setColumnView((x + x_start), columnW); // 设置列宽
				for (y = dataStart; y < dataEnd; y++) {
					Label lb = new Label((x + x_start),
							(y - dataStart + y_base), inputArr[x][y]);
					ws.addCell(lb);
				}
			}
		}
		book.write();
		book.close();
		inputArr = null;
	}

	/**
	 * 写数组数据到excel文件中

	 * 
	 * @param sheetNo
	 * @param sheetName
	 * @param x_start
	 * @param y_base
	 * @param columnW
	 * @param inputArr
	 * @throws Exception
	 */
	public void writeExcel(String fileName, int sheetNo, String sheetName,
			int x_start, int y_base, int columnW, String[][] inputArr)
			throws Exception {
		File os = new File(fileName);
		if (os.exists()) {
			if (!os.delete()) {
				throw new Exception("文件已经存在,并且不能删除");
			}
		}
		WritableWorkbook book = null;
		WritableSheet ws = null;
		if (os.createNewFile()) {
			book = Workbook.createWorkbook(os);
			ws = book.createSheet(sheetName, sheetNo);
		} else {
			throw new Exception("文件创建失败");
		}
		if (inputArr != null && inputArr.length > 0) {
			int inputArrWidth = inputArr.length;
			int inputArrHeight = inputArr[0].length;
			int x = 0, y = 0;
			for (x = 0; x < inputArrWidth; x++) {
				ws.setColumnView((x + x_start), columnW); // 设置列宽
				for (y = 0; y < inputArrHeight; y++) {
					Label lb = new Label((x + x_start), (y + y_base),
							inputArr[x][y]);
					ws.addCell(lb);
				}
			}
		}
		book.write();
		book.close();
		inputArr = null;
	}

	/**
	 * 写数组数据到excel流中
	 * 
	 * @param sheetNo
	 * @param sheetName
	 * @param x_start
	 * @param y_base
	 * @param columnW
	 * @param inputArr
	 * @throws Exception
	 */
	public ByteArrayOutputStream writeExcelStream(int sheetNo,
			String sheetName, int x_start, int y_base, int columnW,
			String[][] inputArr) throws Exception {
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		WritableWorkbook book = null;
		WritableSheet ws = null;
		book = Workbook.createWorkbook(os);
		ws = book.createSheet(sheetName, sheetNo);
		if (inputArr != null && inputArr.length > 0) {
			int inputArrWidth = inputArr.length;
			int inputArrHeight = inputArr[0].length;
			int x = 0, y = 0;
			for (x = 0; x < inputArrWidth; x++) {
				ws.setColumnView((x + x_start), columnW); // 设置列宽
				for (y = 0; y < inputArrHeight; y++) {
					Label lb = new Label((x + x_start), (y + y_base),
							inputArr[x][y]);
					ws.addCell(lb);
				}
			}
		}
		book.write();
		book.close();
		inputArr = null;
		return os;
	}

	public byte[] writeExcel(int sheetNo, String sheetName, int x_start,
			int y_base, int columnW, String[][] inputArr) throws Exception {
		return writeExcelStream(sheetNo, sheetName, x_start, y_base, columnW,
				inputArr).toByteArray();
	}
}

 ExcelExportInfo.java

 

package com.sobey.pms.util.excel;

import java.util.List;

public class ExcelExportInfo {
	private List datas;

	private String columnHeader[];

	private String feilds[];

	private String footer[][];

	private String title;

	private int width[];

	private String fileName = "ExcelExport.xls";

	public String[] getColumnHeader() {
		return columnHeader;
	}

	public void setColumnHeader(String[] columnHeader) {
		this.columnHeader = columnHeader;
	}

	public void setColumnHeader(String columnHeaders) {
		if (columnHeaders != null)
			this.columnHeader = columnHeaders.split(",");
		else
			this.columnHeader = null;
	}

	public List getDatas() {
		return datas;
	}

	public void setDatas(List datas) {
		this.datas = datas;
	}

	public String[] getFeilds() {
		return feilds;
	}

	public void setFeilds(String[] feilds) {
		this.feilds = feilds;
	}

	public void setFeilds(String feilds) {
		if (feilds != null)
			this.feilds = feilds.split(",");
		else
			this.feilds = null;
	}

	public String[][] getFooter() {
		return footer;
	}

	public void setFooter(String[][] footer) {
		this.footer = footer;
	}

	public int[] getWidth() {
		return width;
	}

	public void setWidth(int[] width) {
		this.width = width;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getFileName() {
		return fileName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}
}