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:com.projectswg.tools.ExcelToIff.java

License:Open Source License

private static void convertSheet(String path, String sheetStr) {
    File file = new File(path);
    if (!file.exists()) {
        System.err.println(String.format("Could not convert %s as it doesn't exist!", path));
        return;// w  w w.  java  2 s.c o m
    }

    try {
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheet(sheetStr);
        if (sheet == null)
            sheet = workbook.getSheetAt(Integer.valueOf(sheetStr));
        if (sheet == null) {
            System.err.println(String.format("Could not convert %s as there is no sheet name or id that is %s",
                    path, sheetStr));
        }
        System.out
                .println("Converting sheet " + sheet.getSheetName() + " in workbook " + file.getAbsolutePath());
        convertSheet(new File(file.getAbsolutePath().split("\\.")[0] + "_" + sheet.getSheetName() + ".iff"),
                sheet);
        System.out.println("Conversion for sheet " + sheet.getSheetName() + " in workbook "
                + file.getAbsolutePath() + " completed.");
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

private void excerpt(Workbook wb, List<Sheet> sheetsToKeep, OutputStream output) throws IOException {
    // Make the requested sheets be read only
    Set<String> keepNames = new HashSet<String>();
    for (Sheet s : sheetsToKeep) {
        keepNames.add(s.getSheetName());
        for (Row r : s) {
            for (Cell c : r) {
                if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    switch (c.getCachedFormulaResultType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        double vd = c.getNumericCellValue();
                        c.setCellType(Cell.CELL_TYPE_NUMERIC);
                        c.setCellValue(vd);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        RichTextString vs = c.getRichStringCellValue();
                        c.setCellType(Cell.CELL_TYPE_STRING);
                        c.setCellValue(vs);
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        boolean vb = c.getBooleanCellValue();
                        c.setCellType(Cell.CELL_TYPE_BOOLEAN);
                        c.setCellValue(vb);
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        c.setCellType(Cell.CELL_TYPE_BLANK);
                        break;
                    }//w  ww . j av a 2 s  .c o  m
                }
            }
        }
    }

    // Remove all the other sheets
    // Note - work backwards! Avoids order changing under us
    for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) {
        String name = wb.getSheetName(i);
        if (!keepNames.contains(name)) {
            wb.removeSheetAt(i);
        }
    }

    // Save
    wb.write(output);
}

From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

public WorkbookParser(WorkbookParserSettings settings, Context context, Workbook workbook, String offsetId)
        throws DataParserException {
    this.settings = requireNonNull(settings);
    this.context = requireNonNull(context);
    this.workbook = requireNonNull(workbook);
    this.rowIterator = iterate(this.workbook);
    this.offset = requireNonNull(offsetId);
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    this.currentSheet = null; // default to blank.   Used to figure out when sheet changes and get new field names from header row

    if (!rowIterator.hasNext()) {
        throw new DataParserException(Errors.EXCEL_PARSER_04);
    }/*from  w w  w. ja v a  2 s  .  c o  m*/

    headers = new HashMap<>();

    // If Headers are expected, go through and get them from each sheet
    if (settings.getHeader() == ExcelHeader.WITH_HEADER) {
        Sheet sheet;
        String sheetName;
        Row hdrRow;
        for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
            sheet = workbook.getSheetAt(s);
            sheetName = sheet.getSheetName();
            hdrRow = sheet.rowIterator().next();
            List<Field> sheetHeaders = new ArrayList<>();
            // if the table happens to have blank columns in front of it, loop through and artificially add those as headers
            // This helps in the matching of headers to data later as the indexes will line up properly.
            for (int columnNum = 0; columnNum < hdrRow.getFirstCellNum(); columnNum++) {
                sheetHeaders.add(Field.create(""));
            }
            for (int columnNum = hdrRow.getFirstCellNum(); columnNum < hdrRow.getLastCellNum(); columnNum++) {
                Cell cell = hdrRow.getCell(columnNum);
                try {
                    sheetHeaders.add(Cells.parseCell(cell, this.evaluator));
                } catch (ExcelUnsupportedCellTypeException e) {
                    throw new DataParserException(Errors.EXCEL_PARSER_05, cell.getCellTypeEnum());
                }
            }
            headers.put(sheetName, sheetHeaders);
        }
    }

    Offsets.parse(offsetId).ifPresent(offset -> {
        String startSheetName = offset.getSheetName();
        int startRowNum = offset.getRowNum();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowNum = row.getRowNum();
            String sheetName = row.getSheet().getSheetName();
            // if a sheet has blank rows at the top then the starting row number may be higher than a default offset of zero or one, thus the >= compare
            if (startSheetName.equals(sheetName) && rowNum >= startRowNum) {
                if (rowIterator.hasPrevious()) {
                    row = rowIterator.previous();
                    this.currentSheet = row.getRowNum() == row.getSheet().getFirstRowNum() ? null
                            : row.getSheet().getSheetName(); // used in comparison later to see if we've moved to new sheet
                } else {
                    this.currentSheet = null;
                }
                break;
            }
        }
    });
}

From source file:com.surenpi.autotest.suite.parser.ExcelSuiteParser.java

License:Apache License

/**
 * @param sheet//from  w w  w. ja  va  2s .com
 * @param suitePage
 * @return
 */
private boolean sheetParse(Sheet sheet, SuitePage suitePage) {
    if (sheet.getSheetName().equals("SuiteConfig")) {
        for (int i = 0; i < maxRows; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }

            Cell keyCell = row.getCell(0);
            Cell valCell = row.getCell(1);
            if (keyCell == null || valCell == null) {
                continue;
            }

            String keyCellVal = keyCell.getStringCellValue();
            String valCellVal = valCell.getStringCellValue();
            if (keyCellVal.equals("PageConfig")) {
                suite.setXmlConfPath(valCellVal);
            } else if (keyCellVal.equals("PagePackage")) {
                suite.setPagePackage(valCellVal);
            } else if (keyCellVal.equals("AfterSleep")) {
                try {
                    suite.setAfterSleep(Long.parseLong(valCellVal));
                } catch (NumberFormatException e) {
                    e.printStackTrace();
                }
            }
        }

        return false;
    }

    suitePage.setPage(sheet.getSheetName());
    suitePage.setActionList(new ArrayList<SuiteAction>());

    for (int i = 0; i < maxRows; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            break;
        }

        SuiteAction suiteAction = new SuiteAction();
        suiteAction.setRepeat(1);
        rowParse(row, suiteAction);
        suitePage.getActionList().add(suiteAction);
    }

    return true;
}

From source file:com.ucuenca.dao.BaseExcelDao.java

/**
 * This method gets sheets of file excel
 *
 * @param workbook// w  w  w  .  j ava  2s . com
 * @Author pablo and adrian
 * @return list table
 */
public ArrayList<Table> getSheet(Workbook workbook) {
    int numberOfSheets = workbook.getNumberOfSheets();
    ArrayList<Table> listTable = new ArrayList<Table>();
    for (int i = 0; i < numberOfSheets; i++) {
        Table table = new Table_Excel();
        Sheet sheet = workbook.getSheetAt(i);
        table.setTitle(sheet.getSheetName());
        getColumn(sheet);
        listTable.add(table);

    }
    return listTable;
}

From source file:com.yqboots.initializer.core.builder.excel.AbstractSheetBuilder.java

License:Apache License

@Override
public boolean supports(final Sheet sheet) {
    final String name = StringUtils.trim(sheet.getSheetName());
    return StringUtils.startsWithIgnoreCase(name, this.sheetName);
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?//  w w  w  .  jav  a  2s .  com
 * @param FileName ???????excel
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2jsonWithHeaders(InputStream inputStream, String FileName)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (FileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // jsonkey
        String[] cellNames;
        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);
        // ???
        cellNames = new String[curCellNum];
        // ????JSONkey
        for (int m = 0; m < curCellNum; m++) {
            Cell cell = fisrtRow.getCell(m);
            // ?
            cell.setCellStyle(cellStyle);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            // ?
            cellNames[m] = getCellValue(cell);
        }
        for (String s : cellNames) {
            System.out.print("" + i + " sheet " + s + ",");
        }
        System.out.println();

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);

                cell.setCellStyle(cellStyle);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                // ???
                rowMap.put(cellNames[k], getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?//  ww  w. j a  va  2 s . c o m
 * @param FileName ???????excel
 * @param headers list,String-->Arrays.asList();
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2json(InputStream inputStream, String fileName, List<String> headers)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (fileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);
                // ???
                rowMap.put(headers.get(k), getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

public List<CellDefinition> evaluateWorkbook() {
    final List<CellDefinition> rv = new ArrayList<CellDefinition>();

    boolean reevaluate = false;
    if (workbook instanceof HSSFWorkbook) {
        try {//w  w  w . j a va  2s.  c  om
            workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
        } catch (Exception e) {
            reevaluate = true;
        }
    }

    final FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook,
            IStabilityClassifier.TOTALLY_IMMUTABLE);
    formulaEvaluator.clearAllCachedResultValues();

    for (int i = 0; i < workbook.getNumberOfSheets(); ++i) {
        final Sheet sheet = workbook.getSheetAt(i);
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (reevaluate && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    try {
                        formulaEvaluator.evaluateFormulaCell(cell);
                    } catch (Exception e) {
                        ReportEngine.logger.log(Level.WARNING,
                                String.format("Could not evaluate formula '%s' in cell %s on sheet '%s': %s",
                                        cell.getCellFormula(), CellReferenceHelper
                                                .getCellReference(cell.getColumnIndex(), row.getRowNum()),
                                        sheet.getSheetName(), e.getMessage()));
                    }
                }

                final CellDefinition cellDefinition = IMPORTABLE_CELL_TYPES.containsKey(
                        new Integer(cell.getCellType())) ? new CellDefinition(sheet.getSheetName(), cell)
                                : null;
                if (cellDefinition != null)
                    rv.add(cellDefinition);
            }
        }
    }

    return rv;
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.excelimport.ExcelImporter.java

License:Open Source License

/**
 * import meta model (i.e.: data schema)
 *///from  ww  w .  j  ava2 s.  c  om
private void importMetamodel() {
    LOGGER.info("    ==== First pass =====");
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        try {
            importMetamodelTypes(sheet);
        } catch (ElasticeamException e) {
            logError("Sheet {0}: Import error: {1}", sheet.getSheetName(), e.getMessage());
        }
    }
    for (SheetContext sheetContext : wbContext.getSheetContexts()) {
        importEnumSheet(sheetContext);
    }

    LOGGER.info("    ==== Second pass =====");
    for (SheetContext sheetContext : wbContext.getSheetContexts()) {
        importMetamodelFeatures(sheetContext);
    }
    for (Sheet sheet : relationshipSheets) {
        importRelationshipSheet(sheet);
    }
}