package com.hanyastar.util.file;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import com.hanyastar.exception.HanyaException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
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;

/**
 * Excel操作.
 * 支持读、写操作。
 * @author Belen
 * @since 2013-05-26
 */
public abstract class XLSUtils {

	/**
	 * 生成Excel. 

	 * Excel中columnsName将加粗显示。 
	 * Example:
	 * 
	 * 
	 * 列名数组
	 * String [] columnsName = new String[]{"id","username","password"}
	 * 
	 * 内容List，此List中存放着数组，要求内容为字符串
	 * List sList = new ArrayList();
	 * sList.add(new String[]{"1","u1","p1"});
	 * sList.add(new String[]{"2","u2","p2"});
	 * sList.add(new String[]{"3","u3","p3"});
	 * 
	 * CreateXLS("e:/", "1.xls",columnsName , sList);
	 * 执行后，将指定路径生成Excel.
	 * 
	 * 
	 * @param path
	 *            生成文件的路径 绝对路径
	 * @param fileName
	 *            文件名
	 * @param column
	 *            EXCEL第一行的列名
	 * @param contentList
	 *            EXCEL每行的数据，List中存放字符串型
	 * @throws HanyaException 
	 */
	public static void CreateXLS(String path, String fileName,
			String[] columnsName, List contentList) throws HanyaException{
		WritableWorkbook book = null;
		try {
			// 设置表头格式
			WritableCellFormat wcf = new WritableCellFormat(
					new jxl.write.WritableFont(WritableFont.ARIAL, 10,
							WritableFont.BOLD, false,
							UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
			// 设置对齐方式
			wcf.setAlignment(jxl.format.Alignment.CENTRE);
			// 垂直居中
			// wCF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

			book = Workbook.createWorkbook(new File(path + fileName));
			// 生成名为“第一页”的工作表，参数0表示这是第一页
			WritableSheet sheet = book.createSheet(fileName, 0);
			// 添加第一行的列名
			for (int i = 0; i < columnsName.length; i++) {
				// Label 列 行 内容
				sheet.addCell(new Label(i, 0, columnsName[i], wcf));
				if (columnsName[i].length() > 5) {
					sheet.setColumnView(i, 20);
				}
			}
			
			// 添加每列每行的内容
			for (int j = 0; j < contentList.size(); j++) {
				String[] sArray = (String[]) contentList.get(j);
				for (int i = 0; i < sArray.length; i++) {
					sheet.addCell(new Label(i, j + 1, sArray[i]));
				}
			}
			book.write();
		} catch (IOException e) {
			throw new HanyaException(e);
		} catch (RowsExceededException e1) {
			throw new HanyaException(e1);
		} catch (WriteException e2) {
			throw new HanyaException(e2);
		} finally {
			if (book != null){
				try {
					book.close();
				} catch (WriteException e) {
					throw new HanyaException(e);
				} catch (IOException e) {
					throw new HanyaException(e);
				}
			}
		}
	}

	/**
	 * 根据filename,column,list生在Excel.
	 * @throws HanyaException 
	 * 
	 * @see CreateXLS(String path, String fileName, String column[], List
	 *      contentList)
	 */
	public static void CreateXLS(String fileName, String columnsName[],
			List contentList) throws HanyaException {
		CreateXLS("", fileName, columnsName, contentList);
	}

	/**
	 * 根据filename,list生在Excel. CreateXLS(String fileName,String column[], List
	 * contentList)
	 * @throws HanyaException 
	 */
	public static void CreateXLS(String fileName, List contentList)
			throws HanyaException {
		CreateXLS("", fileName, new String[] {}, contentList);
	}

	/**
	 * 将Excel转化为List，此List中的值均为数组。Excel中一行数据为一个数组。

	 * Example:
	 * 
	 * List cList = readXLS("e:/1.xls");
	 *	for (int i = 0; i < cList.size(); i++) {
	 *		String[] s = cList.get(i);
	 *		int t = 0;
	 *		while (t < s.length) {
	 *			System.out.println(s[t]);
	 *			t++;
	 *		}
	 *	}
	 * ----------------------------
	 * 输出：每行数据
	 * 
	 * @param path EXCEL的路径，必须要为绝对路径。
	 * @return list
	 * @throws HanyaException 
	 */
	public static List readXLS(String path) throws HanyaException {
		Workbook book = null;
		try {
			List cList = new ArrayList();
			book = Workbook.getWorkbook(new File(path));
			Sheet[] sheets = book.getSheets();
			for (int i = 0; i < sheets.length; i++) {
				Sheet sheet = sheets[i];
				int rows = sheet.getRows();
				int columns = sheet.getColumns();
				for (int j = 0; j < rows; j++) {
					String[] contentArray = new String[columns];
					int t = 0;
					while (t < columns) {
						Cell cell = sheet.getCell(t, j);
						contentArray[t] = cell.getContents();
						t++;
					}
					cList.add(contentArray);
				}
			}
			return cList;
		} catch (BiffException e) {
			throw new HanyaException(e);
		} catch (IOException e1) {
			throw new HanyaException(e1);
		} finally {
			if (book != null)
				book.close();
		}
	}
}

创建与读取EXCEL只需一行或很少量代码。

将EXCEL自动转成JAVA对象等均可以。

附件包括源码与架包，下载后放在项目中就可运行，适当去掉我调用的异常处理即可。