Example usage for org.apache.poi.hssf.usermodel HSSFSheet getSheetConditionalFormatting

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet getSheetConditionalFormatting.

Prototype

@Override
    public HSSFSheetConditionalFormatting getSheetConditionalFormatting() 

Source Link

Usage

From source file:uk.ac.liverpool.spreadsheet.ExcelFeatureAnalysis.java

License:Apache License

private static void analyseSheet(Sheet ss, Element s, Namespace n, ExcelFeatureAnalysis efa) {
    // generic part
    boolean costumFormatting = false;
    boolean formulae = false;
    boolean UDF = false;
    boolean hasComments = false;

    Set<String> udfs = new HashSet<String>();
    FormulaEvaluator evaluator = ss.getWorkbook().getCreationHelper().createFormulaEvaluator();

    s.setAttribute("name", ss.getSheetName());
    s.setAttribute("firstRow", "" + ss.getFirstRowNum());
    s.setAttribute("lastRow", "" + ss.getLastRowNum());
    try {/*from   w  w w .  j a va 2s. c o  m*/
        s.setAttribute("forceFormulaRecalc", "" + ss.getForceFormulaRecalculation());
    } catch (Throwable x) {
        //x.printStackTrace();
    }

    // shapes in detail? 
    Footer footer = ss.getFooter();
    if (footer != null) {
        s.setAttribute("footer", "true");
    }
    Header header = ss.getHeader();
    if (header != null) {
        s.setAttribute("header", "true");
    }
    PaneInformation paneInformation = ss.getPaneInformation();
    if (paneInformation != null) {
        s.setAttribute("panels", "true");
    }

    HSSFSheet hs = null;
    XSSFSheet xs = null;
    if (ss instanceof HSSFSheet) {
        hs = (HSSFSheet) ss;
        try {
            if (hs.getDrawingPatriarch() != null) {
                if (hs.getDrawingPatriarch().containsChart())
                    s.addContent(new Element("charts", sn));
                if (hs.getDrawingPatriarch().countOfAllChildren() > 0)
                    s.addContent(new Element("shapes", sn));
            }
        } catch (Exception x) {
            x.printStackTrace();
        }

        if (hs.getSheetConditionalFormatting().getNumConditionalFormattings() > 0) {
            s.setAttribute("conditionalFormatting", "true");
        }
    }
    if (ss instanceof XSSFSheet) {
        xs = (XSSFSheet) ss;

    }
    Iterator<Row> rows = ss.rowIterator();

    int firstColumn = (rows.hasNext() ? Integer.MAX_VALUE : 0);
    int endColumn = 0;
    while (rows.hasNext()) {
        Row row = rows.next();
        short firstCell = row.getFirstCellNum();
        if (firstCell >= 0) {
            firstColumn = Math.min(firstColumn, firstCell);
            endColumn = Math.max(endColumn, row.getLastCellNum());
        }
    }
    s.setAttribute("firstColumn", "" + firstColumn);
    s.setAttribute("lastColumn", "" + endColumn);
    rows = ss.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();
        for (Cell cell : row)
            if (cell != null) {
                try {
                    if (!cell.getCellStyle().getDataFormatString().equals("GENERAL"))
                        costumFormatting = true;
                } catch (Throwable t) {
                }

                if (cell.getCellComment() != null)
                    hasComments = true;
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    // System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //                        if (DateUtil.isCellDateFormatted(cell)) {
                    //                            // System.out.println(cell.getDateCellValue());
                    //                        } else {
                    //                            // System.out.println(cell.getNumericCellValue());
                    //                        }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    // System.out.println(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    // System.out.println(cell.getCellFormula());
                    formulae = true;
                    if (!UDF)
                        try {
                            evaluator.evaluate(cell);
                        } catch (Exception x) {
                            if (x instanceof NotImplementedException) {
                                Throwable e = x;

                                //e.printStackTrace();
                                while (e != null) {
                                    for (StackTraceElement c : e.getStackTrace()) {
                                        if (c.getClassName().contains("UserDefinedFunction")) {
                                            UDF = true;
                                            System.out.println("UDF " + e.getMessage());
                                            udfs.add(e.getMessage());
                                        }
                                    }
                                    e = e.getCause();
                                }

                            }
                        }
                    break;
                default:
                }

            }
    }
    if (costumFormatting) {
        Element cf = new Element("customisedFormatting", sn);
        s.addContent(cf);
    }
    if (formulae) {
        Element cf = new Element("formulae", sn);
        s.addContent(cf);
    }
    if (UDF) {
        Element cf = new Element("userDefinedFunctions", sn);
        for (String sss : udfs)
            cf.addContent(new Element("userDefinedFunction", sn).setAttribute("functionName", sss));
        s.addContent(cf);
    }
    if (hasComments) {
        Element cf = new Element("cellComments", sn);
        s.addContent(cf);
    }
}

---