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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. 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:com.bawan.vims.common.util.ExcelHelper.java

/**
 * ?excel/*from   w  ww. j  av  a2 s.  c o m*/
 */
public static Map<String, List<Map<String, Object>>> parserExcel(String excelFilePath) {

    Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();

    InputStream in = null;
    Workbook wb = null;
    try {
        File excelFile = new File(excelFilePath);
        if (excelFile == null || !excelFile.exists()) {
            logger.error("ExcelHelper[parserExcel]  excel file don't exist!");
            return null;
        }
        in = new FileInputStream(excelFile);

        String suffix = excelFilePath.substring(excelFilePath.lastIndexOf("."));
        if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) {
            logger.error("ExcelHelper[parserExcel]  file suffix do'not match[*.xls, *.xlsx]! ");
            return null;
        } /*else if ("xls".equals(suffix)){
           wb = new HSSFWorkbook(in);
          } else if("xlsx".equals(suffix)) {
           wb = new XSSFWorkbook(in);
          }*/

        wb = WorkbookFactory.create(in); // POI 3.8?, ??xls?xlsx?
        int sheetSize = 0;

        while (true) {
            Sheet sheet = wb.getSheetAt(sheetSize);
            if (sheet == null) {
                break;
            }
            String sheetName = sheet.getSheetName();

            List<Map<String, Object>> sheetContent = new ArrayList<Map<String, Object>>();
            for (int rowNum = 521; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                Map<String, Object> rowMap = new HashMap<String, Object>();
                StringBuffer rowContent = new StringBuffer(
                        "insert into vims_car_market_spread_data(ID, MANUFACTURER, BRAND, VEHICEL_LEVEL, VEHICEL, YEAR, MONTH, AREA, SALE_SIZE, PRODUCTION_SIZE, LICENSED_SIZE, TOTAL_FEE, INTERNET_FEE, TV_FEE, MAGAZINE_FEE, NEWSPAPER_FEE, RADIO_FEE, METRO_FEE, OUTDOORS_FEE) values(");
                rowContent.append("'").append(IDGenerator.getID(32)).append("',");
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    //                  if (cell == null) {
                    //                     rowMap.put(rowNum + "_" + cellIndex, null);
                    //                  } else {
                    //                     rowMap.put(rowNum + "_" + cellIndex, cell.toString());
                    //                  }
                    if (cellIndex == 2) {
                        if (cell == null) {
                            rowContent.append(0).append(",");
                        } else if ("mpv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(1).append(",");
                        } else if ("suv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(2).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(3).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(4).append(",");
                        } else if ("?".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(5).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(6).append(",");
                        }
                        continue;
                    }

                    if (cell == null || cell.toString().trim().length() == 0) {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("default").append(",");
                        } else {
                            rowContent.append("0").append(",");
                        }
                    } else {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("'").append(cell.toString()).append("',");
                        } else if (cellIndex == 4 || cellIndex == 5 || cellIndex == 7 || cellIndex == 8
                                || cellIndex == 9) {
                            String value = cell.toString().substring(0, cell.toString().indexOf("."));
                            rowContent.append(Integer.valueOf(value)).append(",");
                        } else {
                            rowContent.append(cell.toString()).append(",");
                        }
                    }
                }
                String sql = rowContent.toString();
                sql = sql.substring(0, sql.length() - 1);
                sql += ");";
                System.out.println(sql);
                sheetContent.add(rowMap);
            }

            result.put(sheetName, sheetContent);
            sheetSize++;
        }

    } catch (Exception e) {
        e.printStackTrace();
        logger.error("ExcelHelper[parserExcel] excel file not found! error:" + e.getMessage(), e);
    } finally {
        try {
            if (wb != null) {
                wb.close();
                wb = null;
            }
        } catch (IOException e1) {
        }

        try {
            if (in != null) {
                in.close();
                in = null;
            }
        } catch (IOException e) {
        }
    }

    return result;
}

From source file:com.ben12.reta.util.RETAAnalysis.java

License:Open Source License

public void writeExcel(Window parent) throws IOException, InvalidFormatException {
    logger.info("Start write excel output");

    Path outputFile = Paths.get(output);
    if (!outputFile.isAbsolute()) {
        Path root = config.getAbsoluteFile().getParentFile().toPath();
        outputFile = root.resolve(outputFile);
    }//from  w  w  w. j a v  a 2 s .c o m

    // test using template
    InputStream is = getClass().getResourceAsStream("/com/ben12/reta/resources/template/template.xlsx");
    ExcelTransformer transformer = new ExcelTransformer();
    List<String> sheetNames = new ArrayList<>();
    List<String> sheetTemplateNames = new ArrayList<>();
    for (InputRequirementSource requirementSource : requirementSources.values()) {
        sheetTemplateNames.add("DOCUMENT");
        sheetTemplateNames.add("COVERAGE");
        sheetNames.add(requirementSource.getName());
        sheetNames.add(requirementSource.getName() + " coverage");
    }

    List<Map<String, Object>> sheetValues = new ArrayList<>();
    for (InputRequirementSource source : requirementSources.values()) {
        Map<String, Object> values = new HashMap<>();
        values.put("source", source);
        values.put("null", null);
        values.put("line", "\n");

        Set<String> attributes = new LinkedHashSet<>();
        attributes.add(Requirement.ATTRIBUTE_ID);
        if (source.getAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) {
            attributes.add(Requirement.ATTRIBUTE_VERSION);
        }
        attributes.addAll(source.getAttributesGroup().keySet());
        attributes.remove(Requirement.ATTRIBUTE_TEXT);
        values.put("attributes", attributes);

        Set<String> refAttributes = new LinkedHashSet<>();
        refAttributes.add(Requirement.ATTRIBUTE_ID);
        if (source.getRefAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) {
            refAttributes.add(Requirement.ATTRIBUTE_VERSION);
        }
        refAttributes.addAll(source.getRefAttributesGroup().keySet());
        refAttributes.remove(Requirement.ATTRIBUTE_TEXT);
        values.put("refAttributes", refAttributes);

        sheetValues.add(values);
        sheetValues.add(values);
    }

    Workbook wb = transformer.transform(is, sheetTemplateNames, sheetNames, sheetValues);
    int sheetCount = wb.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
        Sheet sheet = wb.getSheetAt(i);
        int columns = 0;
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (row != null) {
                row.setHeight((short) -1);
                columns = Math.max(columns, row.getLastCellNum() + 1);
            }
        }
        for (int j = 0; j < columns; j++) {
            sheet.autoSizeColumn(j);
        }
    }

    try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
        wb.write(fos);
    } catch (FileNotFoundException e) {
        int confirm = MessageDialog.showQuestionMessage(null, "Excel output file must be closed.");

        if (confirm == MessageDialog.OK_OPTION) {
            try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
                wb.write(fos);
            } catch (IOException e2) {
                throw e2;
            }
        } else {
            throw e;
        }
    }

    logger.info("End write excel output");
}

From source file:com.benasmussen.maven.plugin.i18n.io.ResourceReader.java

License:Apache License

/**
 * Get all resource definitions and column index
 * //from  w ww.j  a  v a2 s.c o  m
 * <pre>
 * de,
 * de_DE
 * en,
 * en_GB,
 * en_US
 * </pre>
 * 
 * @return
 */
public Map<String, Integer> getLocaleDefinitions(String sheetName) {
    int maxCols = 300;
    Map<String, Integer> localeDefinitions = new HashMap<String, Integer>();

    CellRangeAddress cellRange = CellRangeAddress.valueOf(localeCell);

    Sheet sheet = getSheetByName(sheetName);
    Row row = sheet.getRow(cellRange.getFirstRow());

    int colIndex = cellRange.getFirstColumn();
    for (int idx = colIndex; idx < (maxCols + colIndex); idx++) {
        Cell cell = row.getCell(idx);
        if (cell != null) {
            localeDefinitions.put(cell.getStringCellValue(), idx);
        } else {
            break;
        }
    }

    return localeDefinitions;
}

From source file:com.benasmussen.maven.plugin.i18n.io.ResourceReader.java

License:Apache License

/**
 * Get cell value as string/*from  w  ww  .j  a  va 2s  .  c  o  m*/
 * 
 * @param sheetName
 * @param row
 * @param col
 * @return
 */
public String getCellValue(String sheetName, int row, int col) {
    Sheet sheet = getSheetByName(sheetName);
    Row r = sheet.getRow(row);
    if (r != null) {
        Cell c = r.getCell(col);
        if (c != null) {
            return c.getStringCellValue();
        }
    }

    return null;
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public Object[][] readTableContent() throws IOException, InvalidFormatException, Exception {
    try {/*w w  w.  j  a v a2 s . c  o m*/
        //OPCPackage pkg = OPCPackage.open(file);
        //            InputStream m_InputStream = new FileInputStream(m_File);
        Sheet sheet = null;
        //            if (!m_InputStream.markSupported()) {
        //                m_InputStream = new PushbackInputStream(m_InputStream, 8);
        //            } 
        //            if (POIFSFileSystem.hasPOIFSHeader(m_InputStream)) {
        //                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(m_InputStream);
        //                 sheet  = (Sheet)hSSFWorkbook.getSheetAt(0);
        //            
        //             } else if (POIXMLDocument.hasOOXMLHeader(m_InputStream)) {
        //                XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(OPCPackage.open(m_File));
        //               sheet  = (Sheet)xSSFWorkbook.getSheetAt(0);
        //             }
        //             else {
        //                throw new IllegalArgumentException("excel?poi??");
        //            }
        sheet = getSheet();
        if (sheet != null) {
            if (sheet.getLastRowNum() == 0) {
                throw new Exception("Excel");
            }
            //?
            m_RowNum = sheet.getLastRowNum() + 1;

            //                m_ColumnNum = sheet.getRow(0).getPhysicalNumberOfCells();
            m_ColumnNum = sheet.getRow(0).getLastCellNum();
            m_TableStr = new Object[m_RowNum][m_ColumnNum];

            for (int rindex = 0; rindex < m_RowNum; rindex++) {
                Row row = sheet.getRow(rindex);
                for (int cindex = 0; cindex < m_ColumnNum; cindex++) {
                    Cell cell = row.getCell(cindex);

                    if (cell == null) {
                        m_TableStr[rindex][cindex] = "";
                    } else {
                        String value = "";
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            //                          System.out.println(cell.getRichStringCellValue().getString());                          
                            value = cell.getRichStringCellValue().getString().replace("\n", "");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                //                                System.out.println(cell.getDateCellValue());
                                value = cell.getDateCellValue().toString();
                            } else {

                                DecimalFormat df = new DecimalFormat("#");
                                value = String.valueOf(cell.getNumericCellValue());
                                double d = cell.getNumericCellValue();
                                int dInt = (int) d;
                                BigDecimal b1 = new BigDecimal(value);
                                BigDecimal b2 = new BigDecimal(Integer.toString(dInt));
                                double dPoint = b1.subtract(b2).doubleValue();
                                if (dPoint == 0) {
                                    //?
                                    value = df.format(cell.getNumericCellValue());
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            //                            System.out.println(cell.getBooleanCellValue());
                            value = cell.getBooleanCellValue() + "";
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            //                            System.out.println(cell.getCellFormula());
                            value = cell.getCellFormula();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            value = "";
                        default:
                            //                            System.out.println();
                            value = "";
                        }
                        m_TableStr[row.getRowNum()][cell.getColumnIndex()] = value;
                    }
                }
            }
        }

    } catch (IOException | InvalidFormatException e) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", e);
        throw e;

    } catch (Exception ex) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", ex);

        throw ex;
    } finally {
        m_InputStream.close();
    }

    return m_TableStr;
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public boolean deleteColumn(int[] columnIndex)
        throws FileNotFoundException, IOException, InvalidFormatException {
    boolean flag = true;
    Sheet sheet = null;
    try {/*from w w  w .  j a va 2 s . co  m*/
        sheet = getSheet();
        if (sheet == null) {
            return false;
        }

        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            //?
            Row tmpRow = sheet.getRow(i);

            for (int j = columnIndex.length - 1; j > -1; j--) {
                //????
                for (int k = columnIndex[j]; k < tmpRow.getLastCellNum(); k++) {
                    Cell tmpCell = tmpRow.getCell(k);
                    if (null != tmpCell) {
                        tmpRow.removeCell(tmpCell);
                    }
                    Cell rightCell = tmpRow.getCell(k + 1);
                    if (null != rightCell) {
                        HSSFRow hr = (HSSFRow) tmpRow;
                        hr.moveCell((HSSFCell) rightCell, (short) k);
                    }
                }

            }
        }
        m_InputStream.close();
        try ( // Write the output to a file
                final FileOutputStream fileOut = new FileOutputStream(m_File)) {
            m_Workerbook.write(fileOut);
        }
    } catch (FileNotFoundException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;
    } catch (IOException | InvalidFormatException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;
    }
    return flag;
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public boolean addColumn(Object[] columnName, Object[][] columnData)
        throws FileNotFoundException, IOException, InvalidFormatException {
    boolean flag = true;
    Row rowCaption;//from  w ww.  j  av a2s.co  m
    Sheet sheet = null;

    try {
        sheet = getSheet();
        if (sheet == null) {
            return false;
        }
        //
        rowCaption = sheet.getRow(0);
        if (rowCaption != null) {
            int columnsCount = rowCaption.getLastCellNum();
            for (int i = 0; i < columnName.length; i++) {
                Cell cell = rowCaption.createCell(columnsCount + i);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(String.valueOf(columnName[i]));
            }

            for (int i = 0; i < sheet.getLastRowNum(); i++) {
                //?
                Row tmpRow = sheet.getRow(i + 1);

                for (int cIndex = 0; cIndex < columnName.length; cIndex++) {
                    Cell cell = tmpRow.getCell(columnsCount + cIndex);
                    if (cell == null) {
                        cell = tmpRow.createCell(columnsCount + cIndex);
                    }
                    //?
                    Object obj = columnData[i][cIndex];
                    if (obj.getClass().getName().equals(Double.class.getName())) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    } else if (obj.getClass().getName().equals(String.class.getName())) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    } else {
                        //?
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    }
                    setCellValue(cell, obj);
                }

            }
        }

        m_InputStream.close();
        try ( // Write the output to a file
                FileOutputStream fileOut = new FileOutputStream(m_File)) {
            m_Workerbook.write(fileOut);
        }
    } catch (FileNotFoundException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;

    } catch (IOException | InvalidFormatException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;
    }

    return flag;
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public boolean updateColumn(int[] columnIndexs, Object[][] columnData)
        throws FileNotFoundException, IOException, InvalidFormatException {
    boolean flag = true;
    Sheet sheet = null;
    try {//w  w w .j av  a  2s.c  o m
        sheet = getSheet();
        if (sheet == null) {
            return false;
        }
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            //?
            Row tmpRow = sheet.getRow(i + 1);
            for (int j = 0; j < columnIndexs.length; j++) {
                Cell cell = tmpRow.getCell(columnIndexs[j]);
                if (cell != null) {
                    setCellValue(cell, columnData[i][j]);
                }
            }
        }
        m_InputStream.close();
        try ( // Write the output to a file
                final FileOutputStream fileOut = new FileOutputStream(m_File)) {
            m_Workerbook.write(fileOut);
        }

    } catch (FileNotFoundException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "?", ex);
        throw ex;
    } catch (IOException | InvalidFormatException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "?", ex);
        throw ex;
    }
    return flag;

}

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataTableWriterExcelManual.java

License:Apache License

@Test
public void testMultiSheet() throws Exception {
    RecordDef recordDef = createSimpleRecordDef();
    DataTable dataSet = new DataTable(recordDef);

    for (int i = 0; i < DataSetWriterExcel.EXCEL_MAX_ROWS; i++) {
        Record record = new Record(recordDef);
        for (FieldDef fieldDef : recordDef) {
            record.setFieldValue(fieldDef.getName(), fieldDef.getName() + " test value " + i);
        }//w  w  w .jav  a  2s  .  c om
        dataSet.add(record);
    }

    DataSetWriterExcel writer = new DataSetWriterExcel(); // Pass a filename
    // if you want an
    // output file
    writer.write(dataSet);
    Workbook wb = writer.getWorkbook();
    assertEquals(2, wb.getNumberOfSheets());

    // Second sheet
    Sheet sheet = wb.getSheetAt(1);
    assertEquals(2, sheet.getLastRowNum());

    // Last row
    Row row = sheet.getRow(2);
    assertEquals("applicationVersion test value 1048575", row.getCell(1).getStringCellValue());
}

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateReader.java

License:Apache License

/**
 * Called by the TemplateReader./*from ww  w  .j a v  a 2s  .  c o  m*/
 *
 * @param sheet
 *            the sheet
 * @param templateSheet
 *            the template sheet
 * @throws Exception
 *             the exception
 */
private void populateColumns(Sheet sheet, TemplateSheet templateSheet) throws Exception {
    Map<String, TemplateColumn> columnMap = templateSheet.getColumnMap();
    Row headerRow = sheet.getRow(0);
    if (headerRow == null) {
        throw new Exception("No header row found! Please create one.");
    }

    Row styleRow = sheet.getRow(1);
    if (styleRow == null) {
        throw new Exception(
                "Sheet name " + templateSheet.getSheetName() + ": No style row found! Please create one.");
    }

    for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
        TemplateColumn column = new TemplateColumn();
        Cell headerCell = headerRow.getCell(i);
        Cell styleCell = styleRow.getCell(i);

        if (headerCell == null) {
            throw new Exception("The following column appears to be empty: " + i);
        }

        if (styleCell == null) {
            throw new Exception("The following style position is not defined: " + i);
        }

        String columnName = headerCell.getStringCellValue();
        // We want to use the style cell (row below) as the header will
        // always be text.
        Integer cellType = styleCell.getCellType();

        column.setColumnPos(i);
        column.setCellStyle(styleCell.getCellStyle());
        column.setColumnName(columnName);
        column.setCellType(cellType);
        if (cellType == Cell.CELL_TYPE_FORMULA) {
            column.setCellFormula(styleCell.getCellFormula());
        }

        columnMap.put(columnName, column);
    }

    populateColumnMappings(columnMap);
}