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

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

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

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

private List<Map<ColumnIdentifier, String>> readWorksheet(String wsName, int headerRowsNb,
        Map<ColumnIdentifier, Integer> cols) {

    List<Map<ColumnIdentifier, String>> output = new LinkedList<Map<ColumnIdentifier, String>>();

    Sheet sheet = wb.getSheet(wsName);

    for (int i = headerRowsNb; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);//  w  ww.j av  a2  s. co m
        if (row == null) {
            continue;
        }

        Map<ColumnIdentifier, String> outputItem = getWorksheetPointerStringMap(cols, row);
        output.add(outputItem);
    }
    return output;
}

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

private Hashtable<String, String> readNodeAbbrMapping(Workbook wb) {
    Sheet sheet = wb
            .getSheet(getPropertyValue(tpaProps.TRAINPATHSECTION_NODES_TO_SYSTEM_NODES_MAPPING_WS_NAME));
    Hashtable<String, String> hashtable = new Hashtable<String, String>();

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);/* ww w. j  a  v a 2s .  c  o  m*/
        if (row == null) {
            continue;
        }
        Cell cellAbbr = row.getCell(0);
        if (cellAbbr == null || cellAbbr.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            continue;
        }
        Cell cellUnique = row.getCell(1);
        if (cellUnique == null || cellUnique.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            continue;
        }
        hashtable.put(formatter.formatCellValue(cellAbbr), formatter.formatCellValue(cellUnique));
    }
    return hashtable;
}

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

public void generateRequests(MacroscopicTopology macroscopicTopology, int requestsPerHour, int durationMinutes,
        int offset, String from, String to, Periodicity p) {
    String colLayoutString = getPropertyValue(tpaProps.REQUESTS_COL_LAYOUT);

    List<ColumnIdentifier> cols = new ArrayList<ColumnIdentifier>(requestsLayout.values().length);
    for (requestsLayout l : requestsLayout.values()) {
        cols.add(l);//from ww  w .java2  s.  c o  m
    }
    Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols);

    int arrtimeColIndex = colLayoutMapping.get(requestsLayout.ARRTIME);
    int fromColIndex = colLayoutMapping.get(requestsLayout.FROM);
    int toColIndex = colLayoutMapping.get(requestsLayout.TO);
    int deptimeColIndex = colLayoutMapping.get(requestsLayout.DEPTIME);
    int idColIndex = colLayoutMapping.get(requestsLayout.ID);
    String requestedMarker = getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER);

    List<String> linkNames = macroscopicTopology.getLinkNames();
    Sheet sheet = wb.getSheet(getPropertyValue(tpaProps.REQUESTS_WS_NAME));

    // header
    Row headerRow = sheet.createRow(0);
    for (ColumnIdentifier col : colLayoutMapping.keySet()) {
        int i = colLayoutMapping.get(col);
        Cell cell = headerRow.getCell(i, Row.CREATE_NULL_AS_BLANK);
        cell.setCellValue(col.name());
    }
    // train path slots hourly
    int k = 1;
    for (int i = 0; i < 24; i++) {
        for (int j = 0; j < requestsPerHour; j++) {
            Row row = sheet.createRow(sheet.getLastRowNum() + 1);
            int hour = i;
            int minutes = (offset + j * (60 / requestsPerHour)) % 60;
            double deptime = DateUtil
                    .convertTime(String.format("%02d", hour) + ":" + String.format("%02d", minutes));
            double arrtime = (deptime * 24 * 60 + durationMinutes) / (24 * 60);

            Cell cell = row.getCell(deptimeColIndex, Row.CREATE_NULL_AS_BLANK);
            cell.setCellStyle(timestyle);
            cell.setCellValue(deptime);
            cell = row.getCell(arrtimeColIndex, Row.CREATE_NULL_AS_BLANK);
            cell.setCellStyle(timestyle);
            cell.setCellValue(arrtime);

            row.getCell(fromColIndex, Row.CREATE_NULL_AS_BLANK).setCellValue(from);
            row.getCell(toColIndex, Row.CREATE_NULL_AS_BLANK).setCellValue(to);

            row.getCell(idColIndex, Row.CREATE_NULL_AS_BLANK)
                    .setCellValue(from + "_" + to + "_" + String.format("%03d", k));
            k++;

            // peridiocity
            for (Integer integer : p.getWeekDays()) {
                int weekdayColIndex = colLayoutMapping.get(requestsLayout.getWeekDayTrainPathLayout(integer));
                row.getCell(weekdayColIndex, Row.CREATE_NULL_AS_BLANK).setCellValue(requestedMarker);
            }
        }
    }
}

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

private void allocate(SimpleTrainPathApplication simpleTrainPathApplication, SolutionCandidate allocation) {
    String colLayoutString = getPropertyValue(tpaProps.TRAINPATHS_COL_LAYOUT);
    List<ColumnIdentifier> cols = new ArrayList<ColumnIdentifier>(trainPathLayout.values().length);
    for (trainPathLayout l : trainPathLayout.values()) {
        cols.add(l);/*  www . j a  v  a2 s .  com*/
    }

    Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols);
    int rowsFrom = Integer.parseInt(getPropertyValue(tpaProps.TRAINPATHS_WS_HEADER_ROWS));

    for (TrainPathSlot trainPathSlot : allocation.getPath()) {
        Sheet sheet = wb.getSheet(trainPathSlot.getPeriodicalTrainPathSlot().getTrainPathSectionName());

        for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            String slotId = getCellValueString(row.getCell(colLayoutMapping.get(trainPathLayout.ID)));
            if (trainPathSlot.getPeriodicalTrainPathSlot().getName().equals(slotId)) {
                trainPathLayout day = trainPathLayout
                        .getWeekDayTrainPathLayout(trainPathSlot.getStartTime().getDayOfWeek());
                String slotName = trainPathSlot.getName();
                int colNum = colLayoutMapping.get(day);
                Cell cell = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK);
                if (StringUtils.isNotBlank(getCellValueString(cell))) {
                    throw new IllegalStateException("Cell must be empty; trying to allocate " + slotName
                            + " on " + day + " to request " + simpleTrainPathApplication.getName()
                            + "; cell value is " + getCellValueString(cell));
                }
                cell.setCellValue(simpleTrainPathApplication.getName()); // TODO show periodicity here when implementing "non-flat" allocation
                LOGGER.debug("Allocating " + slotName + " on " + day + " by request "
                        + simpleTrainPathApplication.getName() + " of weight " + allocation.getWeight());
            }
        }
    }

}

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

/**
 * Mark the request as allocated on all days of the request.
 *
 * @param request//from   ww  w  .  jav a 2  s  .  c o m
 */
private void markRequestAllocated(TrainPathApplication request) {
    String colLayoutString = getPropertyValue(tpaProps.REQUESTS_COL_LAYOUT);
    List<ColumnIdentifier> cols = new ArrayList<ColumnIdentifier>(requestsLayout.values().length);
    for (requestsLayout l : requestsLayout.values()) {
        cols.add(l);
    }

    Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols);
    int rowsFrom = Integer.parseInt(getPropertyValue(tpaProps.REQUESTS_WS_HEADER_ROWS));
    Sheet sheet = wb.getSheet(getPropertyValue(tpaProps.REQUESTS_WS_NAME));

    for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        if (row != null && row.getCell(colLayoutMapping.get(requestsLayout.ID)) != null) {
            String allocatedRequest = getCellValueString(row.getCell(colLayoutMapping.get(requestsLayout.ID)));
            if (allocatedRequest.equals(request.getName())) {
                for (Integer day : request.getPeriodicity().getWeekDays()) {
                    requestsLayout requestDay = requestsLayout.getWeekDayTrainPathLayout(day);
                    int colNum = colLayoutMapping.get(requestDay);

                    Cell cell = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK);
                    if (!getCellValueString(cell)
                            .equals(getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER))) {
                        throw new IllegalStateException("Application " + request.getName() + " on day " + day
                                + " must have requested flag \""
                                + getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER)
                                + "\" since we're trying to mark it satisfied; found "
                                + getCellValueString(cell));
                    }
                    cell.setCellValue(getPropertyValue(tpaProps.REQUESTS_ALLOCATED_DAY_MARKER));
                    LOGGER.debug("Set allocated flag for  " + request.getName() + " on day " + day);
                }
            }
        }
    }
}

From source file:ch.swissbytes.Service.business.Spreadsheet.ToCSV.java

License:Apache License

/**
 * Called to convert the contents of the currently opened workbook into
 * a CSV file.//from w  w w. ja  v a  2  s . co m
 */
private void convertToCSV() {
    Sheet sheet = null;
    Row row = null;
    int lastRowNum = 0;
    this.csvData = new ArrayList<ArrayList<String>>();

    System.out.println("Converting files contents to CSV format.");

    // Discover how many sheets there are in the workbook....
    int numSheets = this.workbook.getNumberOfSheets();

    // and then iterate through them.
    for (int i = 0; i < numSheets; i++) {

        // Get a reference to a sheet and check to see if it contains
        // any rows.
        sheet = this.workbook.getSheetAt(i);
        if (sheet.getPhysicalNumberOfRows() > 0) {

            // Note down the index number of the bottom-most row and
            // then iterate through all of the rows on the sheet starting
            // from the very first row - number 1 - even if it is missing.
            // Recover a reference to the row and then call another method
            // which will strip the data from the cells and build lines
            // for inclusion in the resylting CSV file.
            lastRowNum = sheet.getLastRowNum();
            for (int j = 0; j <= lastRowNum; j++) {
                row = sheet.getRow(j);
                this.rowToCSV(row);
            }
        }
    }
}

From source file:chronostone.parser.ChronoStoneParser.java

private static int check_created(Sheet hoja) {
    int ret = hoja.getLastRowNum();
    return ret;
}

From source file:cht.Parser.java

License:Apache License

public static void main(String[] args) throws IOException {

    // TODO get from google drive
    boolean isUnicode = false;
    boolean isRemoveInputFileOnComplete = false;
    int rowNum;/*from  w w  w .j av  a 2 s . c o m*/
    int colNum;
    Gson gson = new GsonBuilder().setPrettyPrinting().create();

    Properties prop = new Properties();

    try {
        prop.load(new FileInputStream("config.txt"));
    } catch (IOException ex) {
        ex.printStackTrace();
    }

    String inputFilePath = prop.getProperty("inputFile");
    String outputDirectory = prop.getProperty("outputDirectory");
    System.out.println(outputDirectory);
    // optional
    String unicode = prop.getProperty("unicode");
    String removeInputFileOnComplete = prop.getProperty("removeInputFileOnComplete");

    inputFilePath = inputFilePath.trim();
    outputDirectory = outputDirectory.trim();

    if (unicode != null) {
        isUnicode = Boolean.parseBoolean(unicode.trim());
    }
    if (removeInputFileOnComplete != null) {
        isRemoveInputFileOnComplete = Boolean.parseBoolean(removeInputFileOnComplete.trim());
    }

    Writer out = null;
    FileInputStream in = null;
    final String newLine = System.getProperty("line.separator").toString();
    final String separator = File.separator;
    try {
        in = new FileInputStream(inputFilePath);

        Workbook workbook = new XSSFWorkbook(in);

        Sheet sheet = workbook.getSheetAt(0);

        rowNum = sheet.getLastRowNum() + 1;
        colNum = sheet.getRow(0).getPhysicalNumberOfCells();

        for (int j = 1; j < colNum; ++j) {
            String outputFilename = sheet.getRow(0).getCell(j).getStringCellValue();
            // guess directory
            int slash = outputFilename.indexOf('/');
            if (slash != -1) { // has directory
                outputFilename = outputFilename.substring(0, slash) + separator
                        + outputFilename.substring(slash + 1);
            }

            String outputPath = FilenameUtils.concat(outputDirectory, outputFilename);
            System.out.println("--Writing " + outputPath);
            out = new OutputStreamWriter(new FileOutputStream(outputPath), "UTF-8");
            TreeMap<String, Object> map = new TreeMap<String, Object>();
            for (int i = 1; i < rowNum; i++) {
                try {
                    String key = sheet.getRow(i).getCell(0).getStringCellValue();
                    //String value = "";
                    Cell tmp = sheet.getRow(i).getCell(j);
                    if (tmp != null) {
                        // not empty string!
                        value = sheet.getRow(i).getCell(j).getStringCellValue();
                    }
                    if (!key.equals("") && !key.startsWith("#") && !key.startsWith(".")) {
                        value = isUnicode ? StringEscapeUtils.escapeJava(value) : value;

                        int firstdot = key.indexOf(".");
                        String keyName, keyAttribute;
                        if (firstdot > 0) {// a.b.c.d 
                            keyName = key.substring(0, firstdot); // a
                            keyAttribute = key.substring(firstdot + 1); // b.c.d
                            TreeMap oldhash = null;
                            Object old = null;
                            if (map.get(keyName) != null) {
                                old = map.get(keyName);
                                if (old instanceof TreeMap == false) {
                                    System.out.println("different type of key:" + key);
                                    continue;
                                }
                                oldhash = (TreeMap) old;
                            } else {
                                oldhash = new TreeMap();
                            }

                            int firstdot2 = keyAttribute.indexOf(".");
                            String rootName, childName;
                            if (firstdot2 > 0) {// c, d.f --> d, f
                                rootName = keyAttribute.substring(0, firstdot2);
                                childName = keyAttribute.substring(firstdot2 + 1);
                            } else {// c, d  -> d, null
                                rootName = keyAttribute;
                                childName = null;
                            }

                            TreeMap<String, Object> object = myPut(oldhash, rootName, childName);
                            map.put(keyName, object);

                        } else {// c, d  -> d, null
                            keyName = key;
                            keyAttribute = null;
                            // simple string mode
                            map.put(key, value);
                        }

                    }

                } catch (Exception e) {
                    // just ingore empty rows
                }

            }
            String json = gson.toJson(map);
            // output json
            out.write(json + newLine);
            out.close();
        }
        in.close();

        System.out.println("\n---Complete!---");
        System.out.println("Read input file from " + inputFilePath);
        System.out.println(colNum - 1 + " output files ate generated at " + outputDirectory);
        System.out.println(rowNum + " records are generated for each output file.");
        System.out.println("output file is ecoded as unicode? " + (isUnicode ? "yes" : "no"));
        if (isRemoveInputFileOnComplete) {
            File input = new File(inputFilePath);
            input.deleteOnExit();
            System.out.println("Deleted " + inputFilePath);
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (in != null) {
            in.close();
        }
    }

}

From source file:cn.afterturn.easypoi.excel.export.base.BaseExportService.java

License:Apache License

/**
 * /*from w w w.j a v  a2  s.c  om*/
 */
public void addStatisticsRow(CellStyle styles, Sheet sheet) {
    if (statistics.size() > 0) {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("add statistics data ,size is {}", statistics.size());
        }
        Row row = sheet.createRow(sheet.getLastRowNum() + 1);
        Set<Integer> keys = statistics.keySet();
        createStringCell(row, 0, "?", styles, null);
        for (Integer key : keys) {
            createStringCell(row, key, DOUBLE_FORMAT.format(statistics.get(key)), styles, null);
        }
        statistics.clear();
    }

}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * Sheet ?,?? ,??//from www . j  a  v  a2  s  . c o m
 *
 * @param sheet
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook)
        throws Exception {

    // ??
    Map<String, Integer> titlemap = getTitleMap(sheet);
    Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet);
    // 
    Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
    ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
    String targetId = null;
    if (etarget != null) {
        targetId = etarget.value();
    }
    // ??
    List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
    getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null, null);
    // ??
    sortAndFilterExportField(excelParams, titlemap);
    short rowHeight = getRowHeight(excelParams);
    int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
    int shiftRows = getShiftRows(dataSet, excelParams);
    //?,?
    sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(),
            shiftRows, true, true);
    mergedRegionHelper.shiftRows(sheet, teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(),
            shiftRows);
    templateSumHandler.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(),
            shiftRows);
    PoiExcelTempUtil.reset(sheet, teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(),
            sheet.getLastRowNum());
    if (excelParams.size() == 0) {
        return;
    }
    Iterator<?> its = dataSet.iterator();
    while (its.hasNext()) {
        Object t = its.next();
        index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];
    }
    // ??
    mergeCells(sheet, excelParams, titleHeight);
}