Excel2003操作接口

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * Excel2003公用接口
 * 
 */
public class XlsUtil {

	public File xlsFile;
	public Workbook wb = null;
	public Sheet xlsSheet = null;
	public String summarySheetName = "summary";

	public boolean flag = true;
	public boolean flagAll = true;

	public int R_Index = 0;

	public XlsUtil() {
		//
	}

	public XlsUtil(File file) {
		this.xlsFile = file;
		initXls();
	}

	/**
	 * 初始化EXCEL文件 调用XLS时需检查WORKBOOK是否为空
	 */
	public void initXls() {
		try {
			WorkbookSettings wbs = new WorkbookSettings();
			wbs.setGCDisabled(true);
			wb = Workbook.getWorkbook(xlsFile, wbs);
			// xlsSheet = wb.getSheet(0);
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void close()
	{
		if(wb!=null)
			wb.close();
	}

	public String[] getSheetsName() {
		if (wb == null)
			return null;

		return wb.getSheetNames();
	}
	
	public String[] getSheetHeads(Sheet sheet)
	{
		String[] values = getStartRowValues(sheet,0,CsvConst.Csv_Sheet_Col_Start);
		return values;
	}

	public Sheet getSheet(String sheetname) {
		Sheet sht = null;
		if (wb == null)
			return null;
		try {
			sht = wb.getSheet(sheetname);
		} catch (ArrayIndexOutOfBoundsException e) {
			// TODO: handle exception
			System.out.println(e.getLocalizedMessage());
			int index = getSheetIndexFrmSheetName(sheetname);
			sht = wb.getSheet(index);
		}

		return sht;
	}

	public Sheet getSheet(int index) {
		Sheet sht = null;
		if (wb == null)
			return null;
		try {
			sht = wb.getSheet(index);
		} catch (ArrayIndexOutOfBoundsException e) {
			// TODO: handle exception
			System.out.println(e.getLocalizedMessage());
		}

		return sht;
	}

	/** 根据列头名称获取列头索引号 */
	public int getHeadIndex(Sheet sheet, String headname) {
		int colcount = sheet.getColumns();
		for (int i = 0; i < colcount; i++) {
			String tmphead = sheet.getCell(i, 0).getContents();
			if (tmphead.trim().equalsIgnoreCase(headname))
				return i;
			else
				continue;
		}
		return -1;
	}

	public int getRowNo(Sheet sheet, String rowname, int col) {
		int maxlength = sheet.getRows();
		for (int i = 0; i < maxlength; i++) {
			String cellvalue = getCellValue(sheet, i, col);
			if(cellvalue==null)
				return -1;
			if (cellvalue.equalsIgnoreCase(rowname)
					|| formatHeadStr(cellvalue).equalsIgnoreCase(rowname))
				return i;
			else
				continue;
		}
		return -1;
	}

	private String formatHeadStr(String head) {
		head = head.replaceAll(" ", "");
		head = head.replaceAll("\n", "");
		head = head.replaceAll("\t", "");
		return head;
	}

	public String getCellValue(Sheet sheet, int row, int col) {
		Cell[] cells = sheet.getRow(row);
		if(cells==null||cells.length<col+1)
			return null;
		return cells[col].getContents();
	}

	/** 获取指定行数据 */
	public String[] getRowValues(Sheet sheet, int row) {
		Cell[] cells = sheet.getRow(row);
		int length = cells.length;
		String[] values = new String[length];
		for (int i = 0; i < length; i++) {
			values[i] = cells[i].getContents();
		}
		return values;
	}
	
	public String[] getStartRowValues(Sheet sheet, int row,int startCol) {
		Cell[] cells = sheet.getRow(row);
		int length = cells.length;
		String[] values = new String[length-startCol];
		for (int i = 0; i < length-startCol; i++) {
			values[i] = cells[i+startCol].getContents();
		}
		return values;
	}

	/**
	 * 获取XLS中单元格数据
	 * 
	 * @param sheetindex
	 * @param row
	 * @param col
	 * @return String
	 */
	public String getXlsData(int sheetindex, int row, int col) {
		Sheet tmpSheet = wb.getSheet(sheetindex);

		return tmpSheet.getCell(col, row).getContents();
	}

	/**
	 * 获取指定XLS中单元格数据
	 * 
	 * @param workbook
	 * @param sheetindex
	 * @param row
	 * @param col
	 * @return String
	 */
	public String getXlsData(Workbook workbook, int sheetindex, int row, int col) {
		Sheet tmpSheet = workbook.getSheet(sheetindex);

		return tmpSheet.getCell(col, row).getContents();
	}

	/**
	 * 根据SHEET名称获取SHEET的索引
	 * 
	 * @param sheetname
	 * @return
	 */
	public int getSheetIndexFrmSheetName(String sheetname) {
		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			if (wb.getSheet(i).getName().trim().equalsIgnoreCase(sheetname))
				return i;
			else
				continue;
		}

		return -1;
	}

	/**
	 * 获取指定SHEET页中结束行
	 * 
	 * @param tmpSheet
	 * @param startRow
	 * @return int 结束行号
	 */
	public int getSheetEndRow(Sheet tmpSheet, int startRow) {
		int row = 0, col = 0;
		for (row = startRow; row < tmpSheet.getRows(); row++) {
			boolean rowflag = false;
			for (col = 0; col < 3; col++) {
				String cell = tmpSheet.getCell(col, row).getContents();
				if (!cell.equalsIgnoreCase("")) {
					rowflag = true;
				} else {
					continue;

				}
			}

			if (rowflag)
				continue;
			else
				return row;
		}
		return tmpSheet.getRows();
	}

	/**
	 * 获取指定SHEET，指定列，某两行之间单元格的值数组
	 * 
	 * @param tmpSheet
	 * @param col
	 * @param rowStart
	 * @param rowEnd
	 * @return String[]
	 */
	public String[] getXlsRowsData(Sheet tmpSheet, int col, int rowStart,
			int rowEnd) {
		int length = rowEnd - rowStart + 1;
		String[] colRst = new String[length];

		for (int i = 0; i <= rowEnd - rowStart; i++) {
			colRst[i] = tmpSheet.getCell(col, rowStart - 1 + i).getContents()
					.trim();
		}

		return colRst;
	}

	/**
	 * 根据第0列单元格内容获取当前行号
	 * 
	 * @param tmpSheet
	 *            操作的SHEET
	 * @param contents
	 *            单元格内容
	 * @return int 行号
	 */
	public int getRowByContents(Sheet tmpSheet, String contents) {
		for (int row = 0; row < tmpSheet.getRows(); row++) {
			String tmpContents = tmpSheet.getCell(0, row).getContents();

			if (tmpContents.equalsIgnoreCase(contents))
				return row;
			else
				continue;
		}

		return -1;
	}

	public int getSheetCount() {
		return wb.getNumberOfSheets();
	}

	/**
	 * 根据SHEET名称获取SHEET
	 * 
	 * @param sheetname
	 * @return sheet
	 */
	public Sheet getSheetFrmSheetName(String sheetname) {
		int sheetindex = getSheetIndexFrmSheetName(sheetname);
		if (sheetindex >= 0)
			return wb.getSheet(sheetindex);
		else
			return null;
	}

	/**
	 * 向指定SHEET，指定行列写入值
	 * 
	 * @param WritableSheet
	 *            ws
	 * @param row
	 * @param col
	 * @param Cell
	 *            value
	 */
	public void writeCell(WritableSheet ws, int row, int col, String value) {
		try {
			ws.addCell(new Label(col, row, value));
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public String getCurrentTimeString() {
		return new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date());
	}

	public String[] getAllSheetsName(String startSheetName, int step) {
		int startindex = getSheetIndexFrmSheetName(startSheetName);
		int sheetlength = wb.getNumberOfSheets() - step - startindex;
		String[] rst = new String[sheetlength];

		for (int i = 0; i < sheetlength; i++) {
			rst[i] = wb.getSheet(i + startindex + step).getName().trim();
		}

		return rst;
	}
}