Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getRowNum

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos,
        Medico AdministratorUser) throws IOException {

    Guardias[] lGuardias;/* w  w  w .j av  a  2  s .  c o m*/

    Gson gson = new GsonBuilder().create();

    lGuardias = gson.fromJson(JSONContenidos, Guardias[].class);

    boolean xlsx = true;
    int year = calendar.get(Calendar.YEAR);
    int month = calendar.get(Calendar.MONTH);

    DateFormat _format = new SimpleDateFormat("yyyy-MM-dd");

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    calendar.set(Calendar.MONTH, month);
    calendar.set(Calendar.DAY_OF_MONTH, 1);

    calendar.setFirstDayOfWeek(Calendar.MONDAY);
    //create a sheet for each month
    Sheet sheet = wb.createSheet(_format.format(calendar.getTime()));

    CellStyle styleBORDER = wb.createCellStyle();
    styleBORDER.setBorderRight(CellStyle.BORDER_THICK);
    styleBORDER.setBorderBottom(CellStyle.BORDER_THICK);
    styleBORDER.setBorderTop(CellStyle.BORDER_THICK);
    styleBORDER.setBorderLeft(CellStyle.BORDER_THICK);
    styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    //

    //turn off gridlines
    sheet.setDisplayGridlines(true);
    sheet.autoSizeColumn(0);
    sheet.setPrintGridlines(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //header with month titles
    Row monthRow = sheet.createRow(1);
    Font fontH = wb.createFont();
    CellStyle CStyleH = wb.createCellStyle();
    CStyleH.setBorderRight(CellStyle.BORDER_THICK);
    CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    fontH.setBold(true);
    CStyleH.setFont(fontH);
    for (int i = 0; i < days.length; i++) {

        Cell monthCell = monthRow.createCell(i);

        monthCell.setCellStyle(CStyleH);
        monthCell.setCellValue(days[i]);
        sheet.autoSizeColumn(i);

    }

    int cnt = 1, day = 1;
    int rownum = 2;
    for (int j = 0; j < 6; j++) {
        Row row = sheet.createRow(rownum++);
        Row rowGuardias;
        boolean bRowsCreated = false;

        // row.setHeightInPoints(100);
        for (int i = 0; i < days.length; i++) {
            Cell dayCell_1 = row.createCell(i);
            //  Cell dayCell_2 = row.createCell(i*2 + 1);

            int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7;
            //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);  
            if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) {

                Font font = wb.createFont();
                CellStyle CStyle = wb.createCellStyle();
                short colorI = HSSFColor.AQUA.index; // presencia
                //font.set(colorI);
                CStyle.setFillForegroundColor(colorI);
                CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //CStyle.setBorderBottom( colorBorder);
                CStyle.setBorderRight(CellStyle.BORDER_THICK);
                CStyle.setBorderBottom(CellStyle.BORDER_THICK);
                CStyle.setBorderTop(CellStyle.BORDER_THICK);
                CStyle.setBorderLeft(CellStyle.BORDER_THICK);
                CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                //CStyle.setFont(font);

                dayCell_1.setCellValue(day);
                dayCell_1.setCellStyle(CStyle);

                sheet.autoSizeColumn(i);

                String _Dia = _format.format(calendar.getTime());

                int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia
                for (int d = 0; d < lGuardias.length; d++) {

                    Guardias oGuardias = lGuardias[d];
                    if (oGuardias.getDiaGuardia().equals(_Dia)) {

                        if (!bRowsCreated)
                            rowGuardias = sheet.createRow(rownum++);
                        else
                            rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont);

                        Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i);
                        //    Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1);

                        List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(),
                                AdministratorUser.getServicioId());

                        Medico _oMedico = _lMedico.get(0);

                        font = wb.createFont();
                        CStyle = wb.createCellStyle();
                        // PRESENCIA 
                        // LOCALIZADA
                        //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre());
                        colorI = HSSFColor.LIGHT_ORANGE.index; // presencia                        
                        if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase()))
                            colorI = HSSFColor.GREEN.index;
                        else if (oGuardias.getTipo()
                                .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase()))
                            colorI = HSSFColor.BLUE.index;
                        else if (oGuardias.getTipo().equals("")) // residente
                            colorI = HSSFColor.RED.index;

                        font.setColor(colorI);

                        CStyle.setFont(font);
                        //CStyle.setBorderBottom( colorBorder);
                        CStyle.setBorderRight(CellStyle.BORDER_THICK);
                        CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());

                        dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre()
                                + "[" + _oMedico.getIDMEDICO() + "]");
                        dayCell_1_GUARDIAS.setCellStyle(CStyle);

                        DataRowCont++;

                    }

                }
                bRowsCreated = true;

                //      dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString());

                calendar.set(Calendar.DAY_OF_MONTH, ++day);

                /*if(i == 0 || i == days.length-1) {
                    dayCell_1.setCellStyle(styles.get("weekend_left"));
                    dayCell_2.setCellStyle(styles.get("weekend_right"));
                } else {
                    dayCell_1.setCellStyle(styles.get("workday_left"));
                    dayCell_2.setCellStyle(styles.get("workday_right"));
                }
                } else {
                dayCell_1.setCellStyle(styles.get("grey_left"));
                dayCell_2.setCellStyle(styles.get("grey_right"));*/
            }
            cnt++;
        }
        if (calendar.get(Calendar.MONTH) > month)
            break;
    }
    //  }

    // Write the output to a file
    String file = RutaFile;
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    wb.close();

}

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

private void initialize(String title, List<String> headerList) {
    this.wb = new SXSSFWorkbook(500);
    this.sheet = wb.createSheet("Export");
    this.styles = createStyles(wb);
    // Create title
    if (StringUtils.isNotBlank(title)) {
        Row titleRow = sheet.createRow(rownum++);
        titleRow.setHeightInPoints(30);/*from ww w .  j a  va  2 s . c  o m*/
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(styles.get("title"));
        titleCell.setCellValue(title);
        sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(),
                titleRow.getRowNum(), headerList.size() - 1));
    }
    // Create header
    if (headerList == null) {
        throw new RuntimeException("headerList not null!");
    }
    Row headerRow = sheet.createRow(rownum++);
    headerRow.setHeightInPoints(16);
    for (int i = 0; i < headerList.size(); i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellStyle(styles.get("header"));
        String[] ss = StringUtils.split(headerList.get(i), "**", 2);
        if (ss.length == 2) {
            cell.setCellValue(ss[0]);
            Comment comment = this.sheet.createDrawingPatriarch()
                    .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
        } else {
            cell.setCellValue(headerList.get(i));
        }
        sheet.autoSizeColumn(i);
    }
    for (int i = 0; i < headerList.size(); i++) {
        int colWidth = sheet.getColumnWidth(i) * 2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
    }
    log.debug("Initialize success.");
}

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?/* www  .j  a v  a 2s  .c  o  m*/
 * 
 * @param row
 *            
 * @param column
 *            ?
 * @param val
 *            
 * @param align
 *            ??1?23??
 * @return ?
 */
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType, String[] constraintValue) {
    Cell cell = row.createCell(column);
    CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
    try {
        if (val == null) {
            cell.setCellValue("");
        } else if (val instanceof String) {
            cell.setCellValue((String) val);
        } else if (val instanceof Integer) {
            cell.setCellValue((Integer) val);
        } else if (val instanceof Long) {
            cell.setCellValue((Long) val);
        } else if (val instanceof Double) {
            cell.setCellValue((Double) val);
        } else if (val instanceof Float) {
            cell.setCellValue((Float) val);
        } else if (val instanceof Date) {
            DataFormat format = wb.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellValue((Date) val);
        } else {
            if (fieldType != Class.class) {
                cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
            } else {
                cell.setCellValue((String) Class
                        .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                "fieldtype." + val.getClass().getSimpleName() + "Type"))
                        .getMethod("setValue", Object.class).invoke(null, val));
            }
        }

        if (constraintValue.length > 0) {
            // ?
            // ???
            CellRangeAddressList regions = new CellRangeAddressList(row.getRowNum(), row.getRowNum(), column,
                    column);
            // ?
            XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(constraintValue);
            DataValidationHelper help = sheet.getDataValidationHelper();

            // 
            DataValidation validation = help.createValidation(constraint, regions);

            validation.createErrorBox("", "");
            validation.setShowErrorBox(true);

            // sheet
            sheet.addValidationData(validation);
        }
    } catch (Exception ex) {
        log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
        cell.setCellValue(val.toString());
    }
    cell.setCellStyle(style);
    return cell;
}

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?annotation.ExportField?/*ww w  . ja  v  a 2s.c om*/
 * 
 * @return list ?
 */
public <E> ExportExcel setDataList(List<E> list) {
    for (E e : list) {
        int colunm = 0;
        Row row = this.addRow();
        int num = row.getRowNum();
        StringBuilder sb = new StringBuilder();
        for (Object[] os : annotationList) {
            ExcelField ef = (ExcelField) os[0];
            Object val = null;
            // Get entity value
            try {
                if (StringUtils.isNotBlank(ef.value())) {
                    val = ReflectHelper.invokeGetter(e, ef.value());
                } else {
                    if (os[1] instanceof Field) {
                        val = ReflectHelper.invokeGetter(e, ((Field) os[1]).getName());
                    } else if (os[1] instanceof Method) {
                        val = ReflectHelper.invokeMethod(e, ((Method) os[1]).getName(), new Class[] {},
                                new Object[] {});
                    }
                }
                // If is dict, get dict label
                if (StringUtils.isNotBlank(ef.dictType())) {
                    val = DictUtils.getDictLabel(val == null ? "" : val.toString(), ef.dictType(), "");
                }
            } catch (Exception ex) {
                // Failure to ignore
                log.info(ex.toString());
                val = "";
            }
            this.addCell(row, colunm++, val, ef.align(), ef.fieldType(), ef.constraintValue());
            sb.append(val + ", ");
        }
        log.debug("Write success: [" + row.getRowNum() + "] " + sb.toString());
    }
    return this;
}

From source file:com.hurence.logisland.processor.excel.ExcelExtract.java

License:Apache License

/**
 * Extract every matching sheet from the raw excel input stream.
 *
 * @param inputStream an inputstream that will be closed once consumed.
 * @return a stream of {@link Record} each containing the stream raw data.
 */// w w  w.  j  a  v a  2s . c om
private Stream<Record> handleExcelStream(InputStream inputStream) {
    List<Record> ret = new ArrayList<>();
    try {
        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Iterator<Sheet> iter = workbook.sheetIterator();
            while (iter.hasNext()) {
                String sheetName = "unknown";
                List<String> headerNames = null;

                try {
                    Sheet sheet = iter.next();
                    sheetName = sheet.getSheetName();
                    if (toBeSkipped(sheetName)) {
                        LOGGER.info("Skipped sheet {}", sheetName);
                        continue;
                    }
                    LOGGER.info("Extracting sheet {}", sheetName);
                    int count = 0;
                    for (Row row : sheet) {
                        if (row == null) {
                            continue;
                        }
                        if (configuration.getHeaderRowNumber() != null
                                && configuration.getHeaderRowNumber().equals(row.getRowNum())) {
                            headerNames = extractFieldNamesFromRow(row);

                        }
                        if (count++ < configuration.getRowsToSkip()) {
                            continue;
                        }
                        Record current = handleRow(row, headerNames);
                        current.setField(Fields.rowNumber(row.getRowNum()))
                                .setField(Fields.sheetName(sheetName));
                        ret.add(current);
                    }

                } catch (Exception e) {
                    LOGGER.error("Unrecoverable exception occurred while processing excel sheet", e);
                    ret.add(new StandardRecord().addError(ProcessError.RECORD_CONVERSION_ERROR.getName(),
                            String.format("Unable to parse sheet %s: %s", sheetName, e.getMessage())));
                }
            }
        }
    } catch (InvalidFormatException | NotOfficeXmlFileException ife) {
        LOGGER.error("Wrong or unsupported file format.", ife);
        ret.add(new StandardRecord().addError(ProcessError.INVALID_FILE_FORMAT_ERROR.getName(),
                ife.getMessage()));
    } catch (IOException ioe) {
        LOGGER.error("I/O Exception occurred while processing excel file", ioe);
        ret.add(new StandardRecord().addError(ProcessError.RUNTIME_ERROR.getName(), ioe.getMessage()));

    } finally {
        IOUtils.closeQuietly(inputStream);
    }
    return ret.stream();
}

From source file:com.hurry.excel.html.Excel2Html.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();/*from   ww w  .  java  2  s. co  m*/

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();

        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    // Set the value that is rendered for the cell
                    // also applies the format
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                    if (content.equals(""))
                        content = "&nbsp;";
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java

License:Open Source License

private String createInsert(final String tableName, final List<Entry<String, ExcelType>> types, final Row row) {
    //Iterate//  w  w  w  . j a v  a2  s  . co  m
    final FormulaEvaluator evaluator = row.getSheet().getWorkbook().getCreationHelper()
            .createFormulaEvaluator();
    final Map<String, String> colVals = new HashMap<String, String>();

    int columnCount = 0;
    for (Entry<String, ExcelType> sourceType : types) {
        if (isSet(sourceType)) {
            Cell cell = row.getCell(columnCount);
            if (cell != null) {
                cell = evaluator.evaluateInCell(cell);
                try {
                    final String value;
                    switch (sourceType.getValue()) {
                    case DATE:
                        value = "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(getCellValue(cell)) + "'";
                        colVals.put(sourceType.getKey(), value);
                        break;
                    case NUMERIC:
                        value = String.valueOf(getCellValue(cell));
                        colVals.put(sourceType.getKey(), value);
                        break;
                    case BOOLEAN:
                        value = String.valueOf(cell.getBooleanCellValue());
                        colVals.put(sourceType.getKey(), value);
                        break;
                    case STRING:
                        value = String.valueOf(getCellValue(cell)).replaceAll("'", "\\\\'");
                        if (!value.isEmpty()) {
                            colVals.put(sourceType.getKey(), "'" + value + "'");
                        }
                        break;
                    }
                } catch (Exception ex) {
                    if (strict) {
                        throw new RuntimeException("Failed to process cell value: " + getCellValue(cell)
                                + ", of column:row " + columnCount + ":" + row.getRowNum()
                                + ", expecting type: " + sourceType.getValue().toString(), ex);
                    }
                }
            }
        }
        columnCount++;
    }
    return createInsertStatement(colVals, tableName);
}

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * Initialize the attributes :/*from w  ww. j a  v  a 2  s.  c om*/
 * 
 * - inputStream
 * - workbook
 * - evaluator
 * - sheet
 * - firstRowIsMetaData
 * 
 * - firstColumnIndex
 * - firstRowIndex
 * - lastColumnIndex
 * - lastRowIndex
 * 
 * @param fileName
 * @param spreadsheetName
 * @param firstCellRange
 * @param lastCellRange
 * @param interpretFirstLineAsMetaData
 * @throws SQLException
 */
public void initialize(String fileName, String spreadsheetName, String firstCellRange, String lastCellRange,
        boolean interpretFirstLineAsMetaData) throws SQLException {
    try {
        inputStream = new FileInputStream(fileName);
        workbook = WorkbookFactory.create(inputStream);
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        sheet = findSpreadsheet(workbook, spreadsheetName);
        firstRowIsMetaData = interpretFirstLineAsMetaData;

        if (firstCellRange != null && lastCellRange != null) {
            CellReference firstCell = new CellReference(firstCellRange);

            // Deduce last row number if it was not specified
            if (lastCellRange.matches("[a-zA-Z]+")) {
                lastCellRange += (sheet.getLastRowNum() + 1); //Note: getLastRowNum is 0-based
                stopScanOnFirstEmptyRow = true;
                logger.logInfo("Deduced last row in Excel table: " + lastCellRange
                        + " - but scans will end on first empty row");
            }

            CellReference lastCell = new CellReference(lastCellRange);

            firstColumnIndex = firstCell.getCol();
            firstRowIndex = firstCell.getRow(); // + (firstRowIsMetaData?1:0);
            lastColumnIndex = lastCell.getCol();
            lastRowIndex = lastCell.getRow();
        } else {
            Row firstRow = locateFirstRow(sheet);

            if (firstRow == null) {
                throw new SQLException("Empty spreadsheet !");
            }

            firstRowIndex = firstRow.getRowNum(); // + (firstRowIsMetaData?1:0);
            lastRowIndex = sheet.getLastRowNum();
            firstColumnIndex = firstRow.getFirstCellNum(); //Note: getFirstCellNum is 0-based
            lastColumnIndex = firstRow.getLastCellNum() - 1; //Note: getLastCellNum is 1-based
        }

        //System.out.println("sheet: " + sheet.getSheetName() + ", firstcolindex: " + firstColumnIndex + ", lastcolindex: " + lastColumnIndex + ", firstrowindex: " + firstRowIndex + ", lastrowindex: " + lastRowIndex);
    } catch (Exception e) {
        throw new SQLException(e.getMessage());
    }
}

From source file:com.impetus.kvapps.runner.UserBroker.java

License:Apache License

private String extractCellData(Row row, int iCurrent) throws Exception {
    Cell cell = (Cell) row.getCell(iCurrent);
    if (cell == null) {
        return "";
    } else {/*ww  w. j a v  a2  s  .  c o m*/
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            double value = cell.getNumericCellValue();
            if (HSSFDateUtil.isCellDateFormatted(cell))

            {
                if (HSSFDateUtil.isValidExcelDate(value)) {
                    Date date = HSSFDateUtil.getJavaDate(value);
                    SimpleDateFormat dateFormat = new SimpleDateFormat(JAVA_TOSTRING);
                    return dateFormat.format(date);
                } else {
                    throw new Exception("Invalid Date value found at row number " + row.getRowNum()
                            + " and column number " + cell.getColumnIndex());
                }
            } else {
                return value + "";
            }
        case HSSFCell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case HSSFCell.CELL_TYPE_BLANK:
            return null;
        default:
            return null;
        }
    }
}

From source file:com.infovity.iep.loader.util.SupplierLoaderUtil.java

public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) {
    ArrayList<String[]> data = new ArrayList<String[]>();
    File selectedFile = null;//from  w w  w.  j  a va2 s  .c  o m
    FileInputStream fis = null;
    ;
    XSSFWorkbook workbook = null;
    //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx");
    boolean sheetFound = false;
    XSSFSheet sheet = null;
    try {

        int sheetIndex = -1;
        fis = new FileInputStream(inputFile);
        workbook = new XSSFWorkbook(fis);

        int noOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < noOfSheets; i++) {
            sheet = workbook.getSheetAt(i);
            if (sheet.getSheetName().equals(sheetName)) {
                sheetFound = true;
                sheetIndex = i;
                selectedFile = inputFile;
                break;
            }
        }
        XSSFWorkbook myWorkBook;

        try {
            myWorkBook = new XSSFWorkbook(selectedFile);
            // Return first sheet from the XLSX workbook
            // XSSFSheet mySheet = myWorkBook.getSheetAt(0);
            // Get iterator to all the rows in current sheet
            Iterator<Row> rowIterator = sheet.iterator();
            String query;
            String[] values = null;
            // Traversing over each row of XLSX file
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (rowHasData(row) && (row.getRowNum() >= 9)) {
                    int endColumn = row.getLastCellNum();
                    int startColumn = row.getFirstCellNum();
                    // For each row, iterate through each columns
                    values = new String[endColumn + 2];
                    for (int i = startColumn; i < endColumn; i++) {
                        String cellValue;
                        Cell cell = row.getCell(i);
                        values[0] = Integer.toString(row.getRowNum() + 1);
                        if (cell != null) {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC
                                    && DateUtil.isCellDateFormatted(cell)) {
                                DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
                                Date cellDateValue = cell.getDateCellValue();
                                cellValue = df.format(cellDateValue);
                            } else {
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                cellValue = cell.getStringCellValue().replaceAll("'", "");
                            }
                            if (!"".equals(cellValue) && cellValue != null) {
                                values[i + 1] = cellValue;
                            } else if (cellValue.isEmpty() || "".equals(cellValue)) {
                                values[i + 1] = "";
                            }
                        } else {
                            values[i + 1] = "";
                        }
                    }
                    data.add(values);
                }

            }
        } catch (InvalidFormatException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    } catch (IOException ex) {
        Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
    } finally {
        try {
            fis.close();
            workbook.close();
        } catch (IOException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    }

    return data;
}