Example usage for org.apache.poi.ss.usermodel Sheet getSheetName

List of usage examples for org.apache.poi.ss.usermodel Sheet getSheetName

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getSheetName

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getSheetName.

Prototype

String getSheetName();

Source Link

Document

Returns the name of this sheet

Usage

From source file:se.softhouse.garden.oak.ExcelDecisionTableBuilder.java

License:Open Source License

private void loadFromStream(InputStream is, DecisionEngine engine) throws IOException, InvalidFormatException {
    if (is == null) {
        throw new IOException("Unable to find resource.");
    }//from   www .  java2s  .c  o m
    Workbook wb = WorkbookFactory.create(is);
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);

        DecisionTable table = new DecisionTable();
        List<Integer> tableIndexes = locateActionTables(sheet);
        for (int k = 0; k < tableIndexes.size() - 1; k++) {
            table.addTable(create(table, sheet, tableIndexes.get(k), tableIndexes.get(k + 1)));
        }

        engine.addDecisionTable(sheet.getSheetName(), table);

    }
}

From source file:se.softhouse.garden.oak.ExcelDecisionTableBuilder.java

License:Open Source License

private List<Integer> locateActionTables(Sheet sheet) {
    List<Integer> indexes = new ArrayList<Integer>();
    Row tableTypeRow = sheet.getRow(1);// w  w w .  ja v  a2  s  .c  o  m
    Row opRow = sheet.getRow(5);

    for (int i = tableTypeRow.getFirstCellNum(); i < tableTypeRow.getLastCellNum(); i++) {
        Cell typeRowCell = tableTypeRow.getCell(i);
        if (typeRowCell == null) {
            throw new NullPointerException("Sheet[" + sheet.getSheetName() + "] - Row:Col[1:" + i
                    + "] - Cell object is null. (#columns = " + tableTypeRow.getLastCellNum() + ")");
        }
        String cellValue = typeRowCell.getStringCellValue();
        if (cellValue == null) {
            throw new NullPointerException(
                    "Sheet[" + sheet.getSheetName() + "] - Row:Col[1:" + i + "] - Cell value is null.");
        }
        if (!cellValue.isEmpty()) {
            indexes.add(i);
        }
    }
    indexes.add((int) opRow.getLastCellNum());
    return indexes;
}

From source file:step.datapool.excel.ExcelDataPoolImpl.java

License:Open Source License

private int mapHeaderToCellNum(Sheet sheet, String header, boolean createHeaderIfNotExisting) {
    if (configuration.getHeaders().get()) {
        Row row = sheet.getRow(0);//from  www . ja  va2  s  .  co  m
        if (row != null) {
            for (Cell cell : row) {
                String key = ExcelFunctions.getCellValueAsString(cell, workbookSet.getMainFormulaEvaluator());
                if (key != null && key.equals(header)) {
                    return cell.getColumnIndex();
                }
            }
        } else {
            if (createHeaderIfNotExisting) {
                sheet.createRow(0);
            } else {
                throw new ValidationException("The sheet " + sheet.getSheetName() + " contains no headers");
            }
        }
        if (createHeaderIfNotExisting) {
            return addHeader(sheet, header);
        } else {
            throw new ValidationException(
                    "The column " + header + " doesn't exist in sheet " + sheet.getSheetName());
        }
    } else {
        return CellReference.convertColStringToIndex(header);
    }
}

From source file:Tools.excel2champagne.java

public excel2champagne() {
    try {//from   w  ww. j a  v a 2s.  co  m
        this.wb = new HSSFWorkbook(new FileInputStream("TestData/Donnees.xls"));

    } catch (IOException ex) {
        Logger.getLogger(excel2champagne.class.getName()).log(Level.SEVERE, null, ex);
    }

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet s = wb.getSheetAt(i);
        String name = s.getSheetName();
        String[] items = name.split("-");
        int iPeriode = myDB.addPeriode(new Periode(0, Integer.parseInt(items[0].trim()),
                Integer.parseInt(items[1].trim().substring(10)), "", "", ""));
        System.out.println("iPeriode = " + iPeriode);
        int r = 3;

        Row R = s.getRow(r);
        String v = getCellValue(R, 0);
        String nom, pnom, niv;
        while (r < s.getLastRowNum()) {
            nom = v;
            pnom = getCellValue(R, 1);
            niv = getCellValue(R, 2);
            if (niv.isEmpty()) {
                niv = "1";
            }
            myDB.addEleve(new Eleve(0, nom, pnom, Integer.parseInt(niv), 2015));

            R = s.getRow(++r);
            v = getCellValue(R, 0);
        }

        Row R0 = s.getRow(0);
        Row R1 = s.getRow(1);
        Row R2 = s.getRow(2);
        int c = 3;
        String Tmp, Mat = "", SMat = "", Comp;
        int iComp, iMat = 0, iSmat = 0, iEleve;
        while (!"Fin".equals(getCellValue(R0, c + 1))) {
            Tmp = getCellValue(R0, c);
            if (!Tmp.isEmpty()) {
                Mat = Tmp;
                iMat = myDB.addMat(new Matiere(0, Mat));
                System.out.println("Mat = " + Mat);
            }
            Tmp = getCellValue(R1, c);
            if (!Tmp.isEmpty()) {
                SMat = Tmp;
                iSmat = myDB.addSmat(new SousMatiere(0, iMat, SMat));
                System.out.println("-  SMat = " + SMat);
            }
            Comp = getCellValue(R2, c).replace("", "...");
            System.out.println("----  Comp = " + Comp);
            iComp = myDB.addComp(new Competence(0, iMat, iSmat, Comp));

            for (r = 3; r < s.getLastRowNum(); r++) {
                R = s.getRow(r);
                iEleve = myDB.getEleveId(getCellValue(R, 0), getCellValue(R, 1));
                myDB.addNote(new Note(0, iPeriode, iEleve, iMat, iSmat, iComp, getCellValue(R, c)));
            }
            c++;
        }
    }
}

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 {// www.jav  a  2  s  . c  om
        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);
    }
}

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

License:Apache License

private void printSheet(Sheet sheet) {
    out.format("<Table name=\"%s\">%n", sheet.getSheetName());
    printSheetContent(sheet);/*from   ww w  .j  ava2s  .c om*/
    out.format("</Table>%n");
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.Issue61SheetNameWithGroups.java

License:Open Source License

@Test
public void testIssue61() throws BirtException, IOException {

    debug = false;//  www . j av  a  2s  . c  om
    InputStream inputStream = runAndRenderReport("Issue61SheetNameWithGroups.rptdesign", "xlsx");
    assertNotNull(inputStream);
    try {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(327, workbook.getNumberOfSheets());

        Sheet firstSheet = workbook.getSheetAt(0);
        assertEquals(7, this.firstNullRow(firstSheet));

        assertEquals("10100", firstSheet.getSheetName());

        for (Sheet sheet : workbook) {
            if (!"Sheet326".equals(sheet.getSheetName())) {
                assertEquals(Integer.toString((int) sheet.getRow(1).getCell(0).getNumericCellValue()),
                        sheet.getSheetName());
            }
        }

    } finally {
        inputStream.close();
    }
}

From source file:us.physion.ovation.ui.editor.xls.XLSXReader.java

License:Open Source License

public static void load(File f, LoadHandler handler) throws IOException {
    try (FileInputStream fis = new FileInputStream(f)) {
        XSSFWorkbook workbook = new XSSFWorkbook(fis); // for xls HSSFWorkbook

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            List<String[]> entries = new ArrayList<>();

            Sheet sheet = workbook.getSheetAt(i);
            for (Row row : sheet) {
                List<String> rowData = new ArrayList<>();
                for (Iterator<Cell> cells = row.cellIterator(); cells.hasNext();) {
                    Cell cell = cells.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        rowData.add(cell.getStringCellValue().trim());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        rowData.add(Double.toString(cell.getNumericCellValue()));
                        break;
                    }// w  w  w  . ja va2  s.  co  m
                }
                entries.add(rowData.toArray(new String[0]));
            }

            int columnCount = 0;
            for (String[] row : entries) {
                columnCount = Math.max(columnCount, row.length);
            }

            entries = reallocEntries(entries, columnCount);

            TabularData data = new TabularData(entries, getColumnNames(columnCount), f);

            handler.handle(sheet.getSheetName(), data);
        }
    }
}

From source file:utilities.XLSTaskManager.java

License:Open Source License

public ArrayList<Location> convertWorksheetToTagArray(InputStream inputStream, String type) throws Exception {

    Sheet sheet = null;
    Row row = null;//w  w w . j  a  v a2 s.c  o m
    int lastRowNum = 0;
    String group = null;
    ArrayList<Location> tags = new ArrayList<Location>();
    HashMap<String, Integer> header = null;

    if (type != null && type.equals("xls")) {
        wb = new HSSFWorkbook(inputStream);
    } else {
        wb = new XSSFWorkbook(inputStream);
    }

    int numSheets = wb.getNumberOfSheets();

    for (int i = 0; i < numSheets; i++) {
        sheet = wb.getSheetAt(i);
        if (sheet.getPhysicalNumberOfRows() > 0) {

            group = sheet.getSheetName();
            lastRowNum = sheet.getLastRowNum();
            boolean needHeader = true;

            for (int j = 0; j <= lastRowNum; j++) {

                row = sheet.getRow(j);

                if (row != null) {

                    int lastCellNum = row.getLastCellNum();

                    if (needHeader) {
                        header = getHeader(row, lastCellNum);
                        needHeader = false;
                    } else {
                        Location t = new Location();
                        t.group = group;
                        t.type = "nfc";
                        try {
                            t.uid = getColumn(row, "uid", header, lastCellNum, null);
                            t.name = getColumn(row, "name", header, lastCellNum, null);
                            if (t.name == null) {
                                t.name = getColumn(row, "tagname", header, lastCellNum, null); // try legacy name
                            }

                            String lat = getColumn(row, "lat", header, lastCellNum, "0.0");
                            String lon = getColumn(row, "lon", header, lastCellNum, "0.0");
                            try {
                                t.lat = Double.parseDouble(lat);
                                t.lon = Double.parseDouble(lon);
                            } catch (Exception e) {

                            }
                            if (t.name != null && t.name.trim().length() > 0) {
                                tags.add(t);
                            }
                        } catch (Exception e) {
                            log.info("Error getting nfc column" + e.getMessage());
                        }
                    }

                }

            }
        }
    }

    return tags;

}

From source file:workbench.db.exporter.XlsRowDataConverter.java

License:Apache License

private void writeInfoSheet() {
    Sheet info = workbook.getSheet(INFO_SHEETNAME);

    if (info == null) {
        info = workbook.createSheet(INFO_SHEETNAME);
        Row headRow = info.createRow(0);
        Cell cell = headRow.createCell(0);
        setCellValueAndStyle(cell, ResourceMgr.getString("TxtSheet"), true, false, 0);
        cell = headRow.createCell(1);/*w ww .  j  a v a 2  s. co  m*/
        setCellValueAndStyle(cell, "SQL", true, false, 1);
    } else {
        // move the info sheet to the end
        int count = workbook.getNumberOfSheets();
        workbook.setSheetOrder(info.getSheetName(), count - 1);
    }

    int rowNum = info.getLastRowNum() + 1;

    Row infoRow = info.createRow(rowNum);

    Cell name = infoRow.createCell(0);
    CellStyle nameStyle = workbook.createCellStyle();
    nameStyle.setAlignment(CellStyle.ALIGN_LEFT);
    nameStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    nameStyle.setWrapText(false);
    name.setCellValue(sheet.getSheetName());
    name.setCellStyle(nameStyle);
    info.autoSizeColumn(0);

    Cell sqlCell = infoRow.createCell(1);
    CellStyle sqlStyle = workbook.createCellStyle();
    sqlStyle.setAlignment(CellStyle.ALIGN_LEFT);
    sqlStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    sqlStyle.setWrapText(false);

    RichTextString s = workbook.getCreationHelper().createRichTextString(generatingSql);
    sqlCell.setCellValue(s);
    sqlCell.setCellStyle(sqlStyle);
}