excelutil.java

管理公司合同

package cn.com.juneng.system.common;

import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import jxl.Cell;
import jxl.CellType;
import jxl.FormulaCell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.read.biff.WorkbookParser;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelUtil {
		
    public ExcelUtil() {
    }

    /**
     * return a workbook instance.
     */
    public static Workbook getWorkbook(String strFileName){
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(new File(strFileName));
        } catch (java.io.IOException ioe) {
            System.out.println("getWorkbook IOException occurred(1) : " +
                    strFileName + "文件不存在! " + ioe.getMessage());
        } catch (jxl.read.biff.BiffException be) {
            System.out.println("getWorkbook BiffException occurred(2) : " +
                    strFileName + "文件不存在! " + be.getMessage());
        }
        return workbook;
    }
	
	/**
     * return a workbook instance by InputStream.
     */
    public static Workbook getWorkbook(InputStream is){
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(is);
        } catch (java.io.IOException ioe) {
            ioe.printStackTrace();
        } catch (jxl.read.biff.BiffException be) {
			be.printStackTrace();
        }
        return workbook;
    }

    /**
     * Gets the specified sheet within this workbook by the intSheet
     */
    public static Sheet getSheet(Workbook workbook, int intSheet) {
        Sheet sheet = null;
        try {
            sheet = workbook.getSheet(intSheet);
        } catch (IndexOutOfBoundsException obe) {
            System.out.println("getSheet IndexOutOfBoundsException occurred(1) : " +
                    "工作表不存在! " + obe.getMessage());
        }
        return sheet;
    }

    /**
     * Gets the specified sheet within this workbook by specifying sheet name
     */
    public static Sheet getSheet(Workbook workbook, String strSheetName) {
        Sheet sheet = null;
        try {
            sheet = workbook.getSheet(strSheetName);
        } catch (Exception e) {
            System.out.println("getSheet Exception occurred(1) : " +
                    "工作表不存在! " + e.getMessage());
        }
        return sheet;
    }

    /**
     * Gets sheet count of the workbook
     */
    public static int getSheetCount(Workbook workbook) {
        int intCount = 0;
        try {
            intCount = workbook.getSheets().length;
        } catch (Exception e) {
            System.out.println("getSheetCount Exception occurred(1) : " +
                    "工作表不存在! " + e.getMessage());
        }
        return intCount;
    }

    /**
     * Gets the specified writable sheet within
     * this writable workbook by the intSheet
     */
    public static WritableSheet getWritableSheet(WritableWorkbook workbook,
            int intSheet) {
        WritableSheet sheet = null;
        try {
            sheet = workbook.getSheet(intSheet);
        } catch (IndexOutOfBoundsException obe) {
            System.out.println("getWritableSheet IndexOutOfBoundsException occurred(1) : " +
                    "工作表不存在! " + obe.getMessage());
        }
        return sheet;
    }

    /**
     * return the cell type of this cell.
     */
    public static CellType getCellType(Sheet sheet, int intCol, int intRow) {
        CellType cellType = null;
        try {
            Cell cell = sheet.getCell(intCol, intRow);
            cellType = cell.getType();
        } catch (Exception e) {
            System.out.println("getCellType Exception occurred(1) : " + e.getMessage());
        }
        return cellType;
    }

	/**
     * return the cell format of this cell.
     */
    public static CellFormat getCellFormat(Sheet sheet, int intCol, int intRow) {
        CellFormat cellFormat = null;
        try {
            Cell cell = sheet.getCell(intCol, intRow);
            cellFormat = cell.getCellFormat();
        } catch (Exception e) {
            System.out.println("getCellType Exception occurred(1) : " + e.getMessage());
        }
        return cellFormat;
    }

    /**
     * return the contents of this cell as a string.
     */
    public static String getCellContent(Sheet sheet, int intCol, int intRow) {
        String strValue = "";
        try {
            Cell cell = sheet.getCell(intCol, intRow);
            if (cell != null) {
                strValue = cell.getContents();
                strValue = strValue == null ? "" : strValue.trim();
            }
        } catch (Exception e) {
            System.out.println("getCellContent Exception occurred(1) : " + e.getMessage());
        }
        return strValue;
    }


    /**
     * return the contents of this column as a collection,
     * the column is specified area.
     */
    public static ArrayList getColContents(Sheet sheet, int intCol,
            int intStartIndex, int intEndIndex) {
        ArrayList valueList = new ArrayList();

        try {
            Cell[] cells = sheet.getColumn(intCol);
            intStartIndex = intStartIndex < 0 ? 0 : intStartIndex;
            //intEndIndex = intEndIndex > cells.length-1 ?
             //       cells.length-1 : intEndIndex;
			
			String strValue = "";
            for (int i = intStartIndex; i <= intEndIndex; i ++) {
                if(i<cells.length){
					strValue = cells[i].getContents();
					strValue = strValue == null ? "" : strValue.trim();
				}
                valueList.add(strValue);
            }
        } catch (Exception e) {
            System.out.println("getColContent Exception occurred(1) : " + e.getMessage());
            valueList = null;
        }
        return valueList;
    }

    /**
     * return the contents of this row as a collection,
     * the row is specified area.
     */
    public static ArrayList getRowContents(Sheet sheet, int intRow,
            int intStartIndex, int intEndIndex){
        ArrayList valueList = new ArrayList();

        try {
            Cell[] cells = sheet.getRow(intRow);
            intStartIndex = intStartIndex < 0 ? 0 : intStartIndex;
            intEndIndex = intEndIndex > cells.length-1 ?
                    cells.length-1 : intEndIndex;
			
            for (int i = intStartIndex; i <= intEndIndex; i ++) {
                String strValue = cells[i].getContents();
                strValue = strValue == null ? "" : strValue.trim();
                valueList.add(strValue);
            }
        } catch (Exception e) {
            System.out.println("getRowContents Exception occurred(1) : " + e.getMessage());
            valueList = null;
        }
        return valueList;
    }

    /**
     * return the contents of the specified area as a collection,
     * every element of the collection is a collection.
     */
    public static ArrayList getAreaContents(Sheet sheet, int intStartCol,
            int intStartRow, int intEndCol, int intEndRow){
        ArrayList valueList = new ArrayList();
        int intMaxRow = getRows(sheet);
        int intMaxCol = getColumns(sheet);

        intStartCol = intStartCol > intMaxCol-1 ? intMaxCol-1 : intStartCol;
        intEndCol = intEndCol > intMaxCol-1 ? intMaxCol-1 : intEndCol;
        intStartRow = intStartRow > intMaxRow-1 ? intMaxRow-1 : intStartRow;
        intEndRow = intEndRow > intMaxRow-1 ? intMaxRow-1 : intEndRow;

        try {
            for (int i = intStartRow; i <= intEndRow; i ++) {
                ArrayList subList = getRowContents(sheet, i, intStartCol, intEndCol);
                if (subList != null && !subList.isEmpty()) valueList.add(subList);
            }
        } catch (Exception e) {
            System.out.println("getAreaContents Exception occurred(1) : " + e.getMessage());
            valueList = null;
        }
        return valueList;
    }

    /**
     * Returns the number of columns in this sheet
     */
    public static int getColumns(Sheet sheet) {
        int intColumns = -1;
        try {
            intColumns = sheet.getColumns();
        } catch (Exception e) {
            System.out.println("getColumns Exception occurred(1) : " + e.getMessage());
        }
        return intColumns;
    }

    /**
     * Returns the number of rows in this sheet
     */
    public static int getRows(Sheet sheet) {
        int intRows = -1;
        try {
            intRows = sheet.getRows();
        } catch (Exception e) {
            System.out.println("getRows Exception occurred(1) : " + e.getMessage());
        }
        return intRows;
    }

    /**
     * Gets the formula as a string
     */
    public static String getFormula(Sheet sheet, int intCol, int intRow) {
        String strValue = "";
        try {
            Cell cell = sheet.getCell(intCol, intRow);

            if (cell.getType() == CellType.NUMBER_FORMULA ||
                    cell.getType() == CellType.STRING_FORMULA ||
                    cell.getType() == CellType.BOOLEAN_FORMULA ||
                    cell.getType() == CellType.FORMULA_ERROR) {
                System.out.println("------ " + cell.getType().toString());
                FormulaCell fc = (FormulaCell) cell;
                System.out.println("------ 1" );
                strValue = fc.getFormula();
                System.out.println("------ 2" );
                strValue = strValue == null ? "" : strValue.trim();
            }
        } catch (Exception e) {
            System.out.println("getFormula Exception occurred(1) : " + e.getMessage());
        }
        return strValue;
    }

    /**
     * Gets the cells which have been merged on this sheet
     */
    public static Range[] getMergedCells(Sheet sheet) {
        Range[] ranges = sheet.getMergedCells();
        return ranges;
    }

    /**
     * Checks whether the cell has been merged, if it has been merged then return range,
     * or, retrun null
     */
    public static Range getMergedRange(int intCol, int intRow, Range[] ranges) {
        Range range = null;

        if (ranges == null || ranges.length == 0)
            return null;

        for (int i = 0; i < ranges.length; i++) {
            // Get the cell in the top left
            Cell tl = ranges[i].getTopLeft();

            if (tl.getColumn() == intCol &&
                    tl.getRow() == intRow) {
                range = ranges[i];
                break;
            }
        }
        return range;
    }
	
	public static Range getMergedRange(int intCol, int intRow, Sheet sheet) {
        Range range = null;
		Range[] ranges = getMergedCells(sheet);

        if (ranges == null || ranges.length == 0)
            return null;

        for (int i = 0; i < ranges.length; i++) {