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

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

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);
        if (row == null) {
            continue;
        }// ww  w  .  ja v  a2  s  . c  o m
        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 addCatalogue(MacroscopicTopology macroscopicTopology, TrainPathSlotCatalogue catalogue) {
    String colLayoutString = getPropertyValue(tpaProps.TRAINPATHS_COL_LAYOUT);
    List<ColumnIdentifier> cols = new ArrayList<ColumnIdentifier>(trainPathLayout.values().length);
    for (trainPathLayout l : trainPathLayout.values()) {
        cols.add(l);//from   www.j a  v  a  2  s  . c  om
    }
    Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols);
    int deptimeColIndex = colLayoutMapping.get(trainPathLayout.DEPTIME);
    int arrtimeColIndex = colLayoutMapping.get(trainPathLayout.ARRTIME);
    int idColIndex = colLayoutMapping.get(trainPathLayout.ID);

    for (PeriodicalTrainPathSlot periodicalTrainPathSlot : catalogue.getTrainPathSlots()) {
        String linkName = periodicalTrainPathSlot.getTrainPathSectionName();

        // Create sheet if it does not exist yet
        if (wb.getSheet(linkName) == null) {
            Sheet sheet = wb.createSheet(linkName);
            // 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());
            }
        }

        Sheet sheet = wb.getSheet(linkName);
        int rowNb;
        for (rowNb = 1; rowNb < sheet.getPhysicalNumberOfRows(); rowNb++) {
            if (sheet.getRow(rowNb) == null || StringUtils.isBlank(
                    getCellValueString(sheet.getRow(rowNb).getCell(idColIndex, Row.CREATE_NULL_AS_BLANK)))) {
                break;
            }
        }
        Row row = sheet.createRow(rowNb);

        TrainPathSlot slot = periodicalTrainPathSlot.getSlots().get(0);
        int depHour = slot.getStartTime().getHourOfDay();
        int depMinutes = slot.getStartTime().getMinuteOfHour();
        int arrHour = slot.getEndTime().getHourOfDay();
        int arrMinutes = slot.getEndTime().getMinuteOfHour();
        double deptime = DateUtil
                .convertTime(String.format("%02d", depHour) + ":" + String.format("%02d", depMinutes));
        double arrtime = DateUtil
                .convertTime(String.format("%02d", arrHour) + ":" + String.format("%02d", arrMinutes));

        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);

        cell = row.getCell(idColIndex, Row.CREATE_NULL_AS_BLANK);
        cell.setCellValue(periodicalTrainPathSlot.getName());
    }
}

---

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);// w w  w  .jav a2  s  .c  om
    }

    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//w ww.  j  a  v  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  a2  s .c o  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:cherry.goods.excel.ExcelWriterTest.java

License:Apache License

@Test
public void testSetCurrentSheet() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // // w  w w. j  av a 2s  .  com
        Sheet sheet0 = workbook.createSheet("CREATED 0");
        Sheet sheet1 = workbook.createSheet("CREATED 1");

        // 
        try (ExcelWriter writer = new ExcelWriter(workbook)) {
            writer.setCurrentSheet(0);
            writer.write("CELL IN 0");
            writer.setCurrentSheet(1);
            writer.write("CELL IN 1");

            assertEquals("CELL IN 0", sheet0.getRow(0).getCell(0).getStringCellValue());
            assertEquals("CELL IN 1", sheet1.getRow(0).getCell(0).getStringCellValue());
        }
    }
}

---

From source file:cherry.goods.excel.ExcelWriterTest.java

License:Apache License

@Test
public void testWrite_2_COLS_2_ROWS() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // //from   w  w  w  .  j a v a2  s .  com
        Sheet sheet = workbook.createSheet("CREATED 0");

        // 
        try (ExcelWriter writer = new ExcelWriter(workbook)) {
            writer.write("CELL 00", "CELL 01");
            writer.write("CELL 10", "CELL 11");

            assertEquals("CELL 00", sheet.getRow(0).getCell(0).getStringCellValue());
            assertEquals("CELL 01", sheet.getRow(0).getCell(1).getStringCellValue());
            assertEquals("CELL 10", sheet.getRow(1).getCell(0).getStringCellValue());
            assertEquals("CELL 11", sheet.getRow(1).getCell(1).getStringCellValue());
        }
    }
}

---

From source file:cherry.goods.excel.ExcelWriterTest.java

License:Apache License

@Test
public void testWrite_2_COLS_2_ROWS_WITH_NULL() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // /*from www  .j ava 2  s . c  om*/
        Sheet sheet = workbook.createSheet("CREATED 0");
        Row row0 = sheet.createRow(0);
        row0.createCell(0);
        row0.createCell(1);

        // 
        try (ExcelWriter writer = new ExcelWriter(workbook)) {
            writer.write("CELL 00", null);
            writer.write(null, "CELL 11");

            assertEquals("CELL 00", sheet.getRow(0).getCell(0).getStringCellValue());
            assertNull(sheet.getRow(0).getCell(1));
            assertNull(sheet.getRow(1).getCell(0));
            assertEquals("CELL 11", sheet.getRow(1).getCell(1).getStringCellValue());
        }
    }
}

---

From source file:cherry.goods.excel.ExcelWriterTest.java

License:Apache License

@Test
public void testWrite_WITH_OFFSET() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // /*ww w  .j  a v  a 2s.  com*/
        Sheet sheet = workbook.createSheet("CREATED 0");

        // 
        try (ExcelWriter writer = new ExcelWriter(workbook)) {
            writer.write(2, "CELL 00", "CELL 01");

            assertEquals("CELL 00", sheet.getRow(0).getCell(2).getStringCellValue());
            assertEquals("CELL 01", sheet.getRow(0).getCell(3).getStringCellValue());
        }
    }
}

---

From source file:cherry.goods.excel.ExcelWriterTest.java

License:Apache License

@Test
public void testSkipRows() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // /*from w ww  .j a v  a2s  .com*/
        Sheet sheet = workbook.createSheet("CREATED 0");

        // 
        try (ExcelWriter writer = new ExcelWriter(workbook)) {
            writer.write("CELL 00", "CELL 01");
            writer.skipRows(1);
            writer.write("CELL 20", "CELL 21");

            assertEquals("CELL 00", sheet.getRow(0).getCell(0).getStringCellValue());
            assertEquals("CELL 01", sheet.getRow(0).getCell(1).getStringCellValue());
            assertNull(sheet.getRow(1));
            assertEquals("CELL 20", sheet.getRow(2).getCell(0).getStringCellValue());
            assertEquals("CELL 21", sheet.getRow(2).getCell(1).getStringCellValue());
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »