excelutil.java

管理公司合同

            // 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;
    }

    /**
     * Checks whether the cell has been merged if it has then return true,
     * or, retrun false
     */
    public static boolean isMergedCells(int intCol, int intRow, Range[] ranges) {
        boolean isMerged = false;

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

        for (int i = 0; i < ranges.length; i++) {
            // Get the cell in the top left
            Cell tl = ranges[i].getTopLeft();
            Cell br = ranges[i].getBottomRight();
            if (intCol >= tl.getColumn() && intCol <= br.getColumn() &&
                    intRow >= tl.getRow() && intRow <= br.getRow()) {
                isMerged = true;
                break;
            }
        }
        return isMerged;
    }

	public static boolean isMergedCells(int intCol, int intRow, Sheet sheet) {
        boolean isMerged = false;
		Range[] ranges = getMergedCells(sheet);

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

        for (int i = 0; i < ranges.length; i++) {
            // Get the cell in the top left
            Cell tl = ranges[i].getTopLeft();
            Cell br = ranges[i].getBottomRight();
            if (intCol >= tl.getColumn() && intCol <= br.getColumn() &&
                    intRow >= tl.getRow() && intRow <= br.getRow()) {
                isMerged = true;
                break;
            }
        }
        return isMerged;
    }
    /**
     * Creates a writable workbook with the given file name
     */
    public static WritableWorkbook createWorkbook(String strFileName){
        WritableWorkbook workbook = null;
        try {
            workbook = Workbook.createWorkbook(new File(strFileName));
        } catch (java.io.IOException ioe) {
            System.out.println("createWorkbook IOException occurred(1) : " + ioe.getMessage());
        }
        return workbook;
    }

    /**
     * Creates a writable workbook with the given filename(strDestFileName)
     * as a copy of the sourcefile(strDestFileName) passed in.
     * Once created, the contents of the writable workbook may be modified
     */
    public static WritableWorkbook createWorkbook(String strSourceFileName,
            String strDestFileName) {
        WritableWorkbook copyWorkbook = null;
        try {
            Workbook workbook = getWorkbook(strSourceFileName);
            copyWorkbook = Workbook.createWorkbook(new File(strDestFileName), workbook);
			workbook.close();
        } catch (java.io.IOException ioe) {
            System.out.println("createWorkbook IOException occurred(1) : " + ioe.getMessage());
        }
        return copyWorkbook;
    }

	public static WritableWorkbook createWorkbook(OutputStream  os,
            InputStream  is) {
        WritableWorkbook copyWorkbook = null;
        try {
            Workbook workbook = Workbook.getWorkbook(is);
			System.out.println(((WorkbookParser)workbook).getSettings().getGCDisabled());
            copyWorkbook = Workbook.createWorkbook(os, workbook);
			workbook.close();
        } catch (java.io.IOException ioe) {
            System.out.println("createWorkbook IOException occurred(1) : " + ioe.getMessage());
        }catch(jxl.read.biff.BiffException e){
			e.printStackTrace();
		}
        return copyWorkbook;
    }

    /**
     * Creates, and returns a worksheet at the specified position
     * with the specified name
     */
    public static WritableSheet createSheet(WritableWorkbook workbook,
            String strSheetName, int index){
        WritableSheet sheet = null;
        try {
            sheet = workbook.createSheet(strSheetName, index);
        } catch (Exception e) {
            System.out.println("createSheet Exception occurred(1) : " + e.getMessage());
        }
        return sheet;
    }
	

	public static CellFormat getCellFormat(WritableSheet sheet,int intCol,int intRow){
		WritableCell wcell = sheet.getWritableCell(intCol,intRow);
		return wcell.getCellFormat();
	}
    /**
     * Add a string cell into the sheet with specify position, content
     * and default format.
     */
    public static int addCell(WritableSheet sheet,int intCol,
            int intRow, String strContent, jxl.format.CellFormat format) {
        int intReturn = -1;

        strContent = strContent == null ? "" : strContent.trim();

        try {
            Label label = format != null ?
                    new Label(intCol,intRow, strContent, format) :
                    new Label(intCol,intRow, strContent);
            sheet.addCell(label);
            intReturn = 1;
        } catch (jxl.write.biff.RowsExceededException  ee) {
            System.out.println("addCell RowsExceededException occurred(1): " +
                    "增加一个String单元的行数太大,导致出错! " + ee.getMessage());
        } catch (jxl.write.WriteException  we) {
            System.out.println("addCell WriteException occurred(2): " +
                    "增加一个String单元写入时出错! " + we.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("addCell Exception occurred(3): " +
                    "增加一个String单元时出错! " + e.getMessage());
        }
        return intReturn;
    }
	
	public static int addNumberCell(WritableSheet sheet,int intCol,
            int intRow, double value, jxl.format.CellFormat format) {
        int intReturn = -1;
        try {
            jxl.write.Number number = format != null ?
                    new jxl.write.Number(intCol,intRow,value,format) :
                    new jxl.write.Number(intCol,intRow,value);
            sheet.addCell(number);
            intReturn = 1;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("addCell Exception occurred(3): " +
                    "增加一个Number单元时出错! " + e.getMessage());
        }
        return intReturn;
    }

	public static int setCell(WritableSheet sheet,int intCol,
            int intRow, String strContent) {
        int intReturn = -1;
        strContent = strContent == null ? "" : strContent.trim();
        try {
            WritableCell cell = sheet.getWritableCell(intCol,intRow);
			CellType cellType = cell.getType();
			//System.out.println(cellType);
			if(cellType==CellType.LABEL){
				//System.out.println("CellType.LABEL");
				Label label = (Label)cell;
				label.setString(strContent);
			}else if(cellType==CellType.NUMBER){
				//System.out.println("CellType.NUMBER");
				jxl.write.Number number = (jxl.write.Number)cell;
				number.setValue(Double.parseDouble(strContent));
			}else{
				//System.out.println("CellType.OTHER");
				Label label = new Label(intCol,intRow, strContent);
				sheet.addCell(label);
			}
            intReturn = 1;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("addCell Exception occurred(3): " +
                    "设置一个单元时出错! " + e.getMessage());
        }
        return intReturn;
    }

    /**
     * Add a number cell into the sheet with specify position, content
     * font and default format.
     */
    public static int addCell(WritableSheet sheet,int intCol,
            int intRow, double dValue, jxl.format.CellFormat format) {
        int intReturn = -1;

        try {
            /*
            // Gets a cell format
            Cell cell = sheet.getCell(intCol,intRow);
            jxl.format.CellFormat format = cell.getCellFormat();
            */
            // Create the number, specifying content and format
            jxl.write.Number number = format != null ?
                    new jxl.write.Number(intCol, intRow, dValue, format) :
                    new jxl.write.Number(intCol, intRow, dValue);

            if (number != null) sheet.addCell(number);
            intReturn = 1;
        } catch (jxl.write.biff.RowsExceededException  ee) {
            System.out.println("addCell RowsExceededException occurred(1): " +
                    "增加一个Number单元的行数太大,导致出错! " + ee.getMessage());
        } catch (jxl.write.WriteException  we) {
            System.out.println("addCell WriteException occurred(2): " +
                    "增加一个Number单元写入时出错! " + we.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("addNumberCell Exception occurred(3): " +
                    "增加一个Number单元时出错! " + e.getMessage());
        }
        return intReturn;
    }

    /**
     * Add a date cell into the sheet with specify position, content
     * and  format.
     */
    public static int addCell(WritableSheet sheet,int intCol,
            int intRow, java.util.Date dateValue, jxl.format.CellFormat format) {
        int intReturn = -1;

        try {
            /*
            //Gets a cell format
            Cell cell = sheet.getCell(intCol,intRow);
            jxl.format.CellFormat format = cell.getCellFormat();
            */
            //Create the date cell, specifying date and format
            DateTime dateCell = format != null ?
                    new DateTime(intCol, intRow, dateValue, format) :
                    new DateTime(intCol, intRow, dateValue);

            sheet.addCell(dateCell);
            intReturn = 1;
        } catch (jxl.write.biff.RowsExceededException  ee) {
            System.out.println("addDateCell RowsExceededException occurred(1): " +
                    "增加一个Date单元的行数太大,导致出错! " + ee.getMessage());
        } catch (jxl.write.WriteException  we) {
            System.out.println("addDateCell WriteException occurred(2): " +
                    "增加一个Date单元写入时出错! " + we.getMessage());
        } catch (Exception e) {
            System.out.println("addDateCell Exception occurred(3): " +
                    "增加一个Date单元时出错! " + e.getMessage());
        }
        return intReturn;
    }

    /**
     * Add a formula cell into the sheet with specify position
     * and formula string
     */
    public static int addFormulaCell(WritableSheet sheet,int intCol,
            int intRow, String strFormula, jxl.format.CellFormat format) {
        int intReturn = -1;

        try {
            /*
            //Gets a cell format
            Cell cell = sheet.getCell(intCol,intRow);
            jxl.format.CellFormat format = cell.getCellFormat();
            */
            // Create a cell for specifying position ,formula and format
            Formula f = new Formula(intCol, intRow, strFormula, format);

            sheet.addCell(f);
            intReturn = 1;
        } catch (jxl.write.biff.RowsExceededException  ee) {
            System.out.println("addFormulaCell RowsExceededException occurred(1): " +
                    "增加一个Formula单元的行数太大,导致出错! " + ee.getMessage());
        } catch (jxl.write.WriteException  we) {
            System.out.println("addFormulaCell WriteException occurred(2): " +
                    "增加一个Formula单元写入时出错! " + we.getMessage());
        } catch (Exception e) {
            System.out.println("addFormulaCell Exception occurred(3): " +
                    "增加一个Formula单元时出错! " + e.getMessage());
        }
        return intReturn;
    }



    public static void main(String[] args) {
        try {
        /*
            WritableWorkbook workbook =
                    ExcelUtil.createWorkbook("template11.xls", "stat.xls");
            WritableSheet sheet = ExcelUtil.getWritableSheet(workbook,0);
            ExcelUtil.addCell(sheet, 2, 7, 456);
            ExcelUtil.addCell(sheet, 1, 8, 20);
            ExcelUtil.addFormulaCell(sheet, 1, 9, "B8+B9");
            workbook.write();
            workbook.close();
        */

            Workbook wb = ExcelUtil.getWorkbook("template.xls");
            Sheet sheet2 = ExcelUtil.getSheet(wb, 0);
            Range[] ranges = ExcelUtil.getMergedCells(sheet2);
            Range range = ExcelUtil.getMergedRange(1,8, ranges);
            if (range != null)
                System.out.println("**** "+range.getTopLeft().getRow() +" , " +
                        range.getTopLeft().getColumn());
            //String s = ExcelUtil.getCellContent(sheet2, 1,9);
            //String f = ExcelUtil.getFormula(sheet2, 1, 9);
            System.out.println("--------" + ranges.length);
            //CellType ct = ExcelUtil.getCellType(sheet, 1,9);
            //System.out.println(ct.toString());
            wb.close();

        } catch(Exception e) {
            System.out.println("---err = " + e.getMessage());
        }

    }

}