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:com.compomics.cell_coord.parser.impl.XLSFileParser.java

@Override
public Sample parseTrackFile(File trackFile) throws FileParserException {
    List<Track> list = new ArrayList<>();
    // create a new sample object -- watch out to set the relationships!
    Sample sample = new Sample(trackFile.getName());
    try {//from  w  ww .j  a  va  2  s  .  c o  m
        FileInputStream fileInputStream = new FileInputStream(trackFile);
        Workbook workbook = null;
        // xls extension
        if (trackFile.getName().endsWith("xls")) {
            workbook = new HSSFWorkbook(fileInputStream);
        } else if (trackFile.getName().endsWith("xlsx")) { // xlsx extension
            workbook = new XSSFWorkbook(fileInputStream);
        }
        if (workbook != null) {
            // check that at least one sheet is present
            if (workbook.getNumberOfSheets() > 0) {
                Track currentTrack = null;
                List<TrackSpot> currentTrackPointList = new ArrayList<>();
                Long currentId = 0L;
                Sheet sheet = workbook.getSheetAt(0);
                // iterate through all the rows, starting from the second one to skip the header
                for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                    // get the row
                    Row row = sheet.getRow(i);
                    // check the track id
                    Long trackid = (long) row.getCell(0).getNumericCellValue();
                    if (!Objects.equals(currentId, trackid)) {
                        currentTrack = new Track();
                        currentTrack.setTrackid(trackid);
                        list.add(currentTrack);
                        currentId = trackid;
                        currentTrackPointList = new ArrayList<>();
                    }
                    // create new Track Spot object
                    Long spotid = (long) row.getCell(1).getNumericCellValue();
                    double x = row.getCell(2).getNumericCellValue();
                    double y = row.getCell(3).getNumericCellValue();
                    double time = row.getCell(4).getNumericCellValue();
                    TrackSpot trackSpot = new TrackSpot(spotid, x, y, time, currentTrack);
                    currentTrackPointList.add(trackSpot);
                    currentTrack.setTrackSpots(currentTrackPointList);
                    currentTrack.setSample(sample);
                }
            } else {
                throw new FileParserException(
                        "It seems an Excel file does not have any sheets!\nPlease check your files!");
            }
        } else {
            throw new FileParserException("The parser did not find a single workbook!\nCheck your files!!");
        }
    } catch (IOException ex) {
        LOG.error(ex.getMessage(), ex);
    } catch (NumberFormatException ex) {
        LOG.error(ex.getMessage(), ex);
        throw new FileParserException(
                "It seems like a line does not contain a number!\nPlease check your files!");
    }
    sample.setTracks(list);
    return sample;
}

From source file:com.compomics.pepshell.controllers.dataexport.ExcelExport.java

License:Apache License

private static void excelProteinInfoExportOfProtein(PepshellProtein pepshellProteinToExport,
        Sheet proteinSheet) {
    int column = 0;
    Row proteinRow = proteinSheet.createRow(proteinSheet.getLastRowNum() + 1);
    proteinRow.createCell(column).setCellValue(pepshellProteinToExport.getVisibleAccession());
    column++;//from www.j a va 2s. c om
    for (PeptideGroup peptideGroup : pepshellProteinToExport.getPeptideGroups()) {
        proteinRow.createCell(column).setCellValue(peptideGroup.getRepresentativePeptide().getSequence());
        if (peptideGroup instanceof QuantedPeptideGroup) {
            proteinRow.createCell(column + 1)
                    .setCellValue(Integer.toString(((QuantedPeptideGroup) peptideGroup).getRatio()));
        }
        proteinRow = proteinSheet.createRow(proteinSheet.getLastRowNum() + 1);
    }
}

From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java

License:Apache License

/**
 * Validates the reader-config.xml with the Excel file
 *
 * @param vcConfig The validator configuration object.
 * @param filename Name of the Excel file.
 * @param dDoc Document conatins the request.
 * @param iResultNode The record XML structure root node, or zero, if only validation is needed.
 * @param sheetno Sheet index of the Excel file.
 * @param startrow row index from which data to be read.
 * @param endrow   row index upto which data to be read.
 * @param lErrorList LinkedList contains all the errors.
 *//*from  w ww. ja  v a 2 s . c o  m*/
public static void validate(ValidatorConfig vcConfig, String filename, Document dDoc, int iResultNode,
        int sheetno, int startrow, int endrow, List<FileException> lErrorList) {
    try {

        setRecordsread(0);
        setEndoffile(false);

        Workbook book = null;
        Sheet sheet = null;
        Row row;
        FileInputStream fileinp = null;
        //String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName;
        int sheetindex;
        int noofsheets;
        if (filename == null) {
            throw new FileException(LogMessages.PLEASE_PROVIDE_FILE_NAME);
        }
        File file = new File(filename);
        fileinp = new FileInputStream(filename);
        if (file.exists()) {
            if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) {
                try {
                    book = (Workbook) new HSSFWorkbook(fileinp);
                } catch (IOException ex) {
                    Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex);
                }
            } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) {
                try {
                    book = new XSSFWorkbook(fileinp);
                } catch (IOException ex) {
                    Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex);
                }
            } else {
                //ERROR
                fileinp.close();
                throw new FileException(LogMessages.INPUT_FILE_NOT_SUPPORTED);
            }
        } else {
            //ERROR
            fileinp.close();
            throw new FileException(LogMessages.FILE_NOT_FOUND);
        }
        if (sheetno != -1) {
            sheetindex = sheetno;
            noofsheets = sheetindex + 1;
        } else {
            sheetindex = 0;
            noofsheets = book.getNumberOfSheets();
        }
        //check whether the sheetindex exists or not
        for (; sheetindex < noofsheets; sheetindex++) {
            if (sheetindex >= book.getNumberOfSheets()) {
                //no sheet
                throw new FileException(LogMessages.NO_SHEET_FOUND, sheetindex);
            }
            sheet = book.getSheetAt(sheetindex);
            if (sheet == null) {
                throw new FileException(LogMessages.NO_SHEET_FOUND, sheetindex);
            }
        }

        //validate columns

        //get last column index
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (maxcol < row.getLastCellNum()) {
                maxcol = row.getLastCellNum();
            }
        }
        //check column index in reader-config
        ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList.listIterator();
        while (fieldslist.hasNext()) {
            FieldType excelfields = (FieldType) fieldslist.next();
            try {
                if (Short.parseShort(excelfields.sColumnIndex) < 0
                        || Short.parseShort(excelfields.sColumnIndex) >= maxcol) {
                    throw new FileException(LogMessages.COLUMN_INDEX_NOT_FOUND, excelfields.sColumnIndex,
                            (maxcol - 1));
                }
            } catch (NumberFormatException ex) {
                throw new FileException(ex, LogMessages.COLUMN_INDEX_NOT_VALID, excelfields.sColumnIndex);
            }
        }

        if (endrow == -1) {
            endrow = sheet.getLastRowNum();
            if (startrow == -1) {
                startrow = 0;
            }
        } else {
            endrow = startrow + endrow - 1;
            if (endrow > sheet.getLastRowNum()) {
                endrow = sheet.getLastRowNum();
            }
        }

        setRecordsread(endrow - startrow + 1);

    } catch (IOException ex) {
        lErrorList.add(new FileException(ex, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename));
    } catch (FileException ex) {
        lErrorList.add(ex);
    }

}

From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java

License:Apache License

/**
 * Read records from Excel file/*from   ww w  . j  av a 2s . c  om*/
 *
 * @param vcConfig The validator configuration object.
 * @param bUseTupleOld
 * @param filename Name of the Excel file.
 * @param doc Document conatins the request.
 * @param iResponsenode The record XML structure root node, or zero, if only validation is needed.
 * @param sheetno Sheet index of the Excel file.
 * @param startrow row index from which data to be read.
 * @param endrow   row index upto which data to be read.
 * @param startcolumn column index from which data to be read.
 * @param endcolumn column index upto which data to be read.
 */
public static void readall(ValidatorConfig vcConfig, Boolean bUseTupleOld, String filename, Document doc,
        int iResponsenode, int sheetno, int startrow, int endrow, int startcolumn, int endcolumn)
        throws FileException {

    Workbook book = null;
    Sheet sheet;
    Cell cell;
    Row row;
    FileInputStream fileinp = null;
    String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName;
    try {
        int iRow, iCol, sheetindex, noofsheets;
        File file = new File(filename);
        fileinp = new FileInputStream(filename);
        if (file.exists()) {
            if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) {
                book = (Workbook) new HSSFWorkbook(fileinp);
            } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) {
                book = new XSSFWorkbook(fileinp);
            } else {
                //ERROR
                fileinp.close();
            }
        } else {
            //ERROR
            fileinp.close();
        }

        if (sheetno != -1) {
            sheetindex = sheetno;
            noofsheets = sheetindex + 1;
        } else {
            sheetindex = 0;
            noofsheets = book.getNumberOfSheets();
        }
        for (; sheetindex < noofsheets; sheetindex++) {
            sheet = book.getSheetAt(sheetindex);

            if (endrow == -1) {
                endrow = sheet.getLastRowNum();
                if (startrow == -1) {
                    startrow = 0;
                }
            } else {
                endrow = startrow + endrow - 1;
                if (endrow > sheet.getLastRowNum()) {
                    endrow = sheet.getLastRowNum();
                }
            }

            if (endcolumn == -1) {
                endcolumn = 30;
                if (startcolumn == -1) {
                    startcolumn = 0;
                }
            }
            for (int i = startrow; i <= endrow; i++) {

                row = sheet.getRow(i);

                if (row == null) {
                    int iTup = doc.createElement("tuple", iResponsenode);

                    if (bUseTupleOld) {
                        iTup = doc.createElement("old", iTup);
                    }
                    iRow = doc.createElement(sRecordName, iTup);
                    //Node.setAttribute(iRow, "id", "" + i);
                    ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList
                            .listIterator();
                    while (fieldslist.hasNext()) {
                        FieldType excelfields = (FieldType) fieldslist.next();
                        String sColumnName = excelfields.sFieldName;

                        iCol = doc.createTextElement(sColumnName, "", iRow);
                    }
                    continue;
                }
                int iTup = doc.createElement("tuple", iResponsenode);
                if (bUseTupleOld) {
                    iTup = doc.createElement("old", iTup);
                }
                iRow = doc.createElement(sRecordName, iTup);
                ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList
                        .listIterator();
                while (fieldslist.hasNext()) {
                    FieldType excelfields = (FieldType) fieldslist.next();
                    int iColumnIndex = Integer.parseInt(excelfields.sColumnIndex);
                    cell = row.getCell(iColumnIndex);
                    String sColumnName = excelfields.sFieldName;
                    if (cell == null) {
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        continue;
                    }
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getBooleanCellValue(), iRow);

                        break;
                    case Cell.CELL_TYPE_ERROR:
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getCellFormula(), iRow);

                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat simpledateformat = new SimpleDateFormat(
                                    "yyyy-MM-dd 'T' HH:mm:ss.S");
                            iCol = doc.createTextElement(sColumnName,
                                    "" + simpledateformat.format(cell.getDateCellValue()), iRow);

                        } else {
                            iCol = doc.createTextElement(sColumnName, "" + cell.getNumericCellValue(), iRow);
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getStringCellValue(), iRow);
                        break;
                    default:
                        System.out.println("default");
                    }
                }
            }
        }
    } catch (FileNotFoundException e) {
        throw new FileException(e, LogMessages.FILE_NOT_FOUND);
    } catch (IOException e) {
        throw new FileException(e, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename);
    } finally {
        try {
            fileinp.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.cpjd.roblu.csv.ToCSV.java

License:Apache License

/**
 * Called to convert the contents of the currently opened workbook into
 * a CSV file./* w ww. j  av a 2 s .c  o  m*/
 */
private void convertToCSV() {
    Sheet sheet;
    Row row;
    int lastRowNum;
    this.csvData = new ArrayList<>();

    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:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

@Override
public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle,
        boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, boolean subTable) throws IOException {

    wb = new XSSFWorkbook();
    String safeName = WorkbookUtil.createSafeSheetName(filename);
    Sheet sheet = wb.createSheet(safeName);

    cellStyle = wb.createCellStyle();// w ww  . j a v a 2  s  .c  o m
    facetStyle = wb.createCellStyle();
    titleStyle = wb.createCellStyle();
    createCustomFonts();

    int maxColumns = 0;
    StringTokenizer st = new StringTokenizer(tableId, ",");
    while (st.hasMoreElements()) {
        String tableName = (String) st.nextElement();
        UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(),
                tableName);
        if (component == null) {
            throw new FacesException("Cannot find component \"" + tableName + "\" in view.");
        }
        if (!(component instanceof DataTable || component instanceof DataList)) {
            throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName()
                    + "\", exporter must target a PrimeFaces DataTable/DataList.");
        }

        DataList list = null;
        DataTable table = null;
        int cols = 0;
        if (preProcessor != null) {
            preProcessor.invoke(context.getELContext(), new Object[] { wb });
        }
        if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) {
            Row titleRow = sheet.createRow(sheet.getLastRowNum());
            int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum();
            Cell cell = titleRow.createCell(cellIndex);
            cell.setCellValue(new XSSFRichTextString(tableTitle));
            Font titleFont = wb.createFont();
            titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            titleStyle.setFont(titleFont);
            cell.setCellStyle(titleStyle);
            sheet.createRow(sheet.getLastRowNum() + 3);

        }
        if (component instanceof DataList) {
            list = (DataList) component;

            if (list.getHeader() != null) {
                tableFacet(context, sheet, list, "header");
            }
            if (pageOnly) {
                exportPageOnly(context, list, sheet);
            } else {
                exportAll(context, list, sheet);
            }
            cols = list.getRowCount();
        } else {

            table = (DataTable) component;
            int columnsCount = getColumnsCount(table);

            if (table.getHeader() != null && !subTable) {
                tableFacet(context, sheet, table, columnsCount, "header");

            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "header");
            }

            addColumnFacets(table, sheet, ColumnType.HEADER);

            if (pageOnly) {
                exportPageOnly(context, table, sheet);
            } else if (selectionOnly) {
                exportSelectionOnly(context, table, sheet);
            } else {
                exportAll(context, table, sheet, subTable);
            }

            if (table.hasFooterColumn() && !subTable) {
                addColumnFacets(table, sheet, ColumnType.FOOTER);
            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "footer");
            }
            table.setRowIndex(-1);
            if (postProcessor != null) {
                postProcessor.invoke(context.getELContext(), new Object[] { wb });
            }
            cols = table.getColumnsCount();

            if (maxColumns < cols) {
                maxColumns = cols;
            }
        }
        sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding));
    }

    if (!subTable)
        for (int i = 0; i < maxColumns; i++) {
            sheet.autoSizeColumn((short) i);
        }

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);

    writeExcelToResponse(context.getExternalContext(), wb, filename);

}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, DataTable table, int columnCount,
        String facetType) {//from  w  ww  .  j av  a  2s .  c o  m
    Map<String, UIComponent> map = table.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else if (component instanceof UIPanel) {
            String header = "";
            for (UIComponent child : component.getChildren()) {
                headerValue = exportValue(context, child);
                header = header + headerValue;
            }
            headerValue = header;
        } else {
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                sheetRowIndex, //last row  (0-based)
                0, //first column (0-based)
                columnCount + 1 //last column  (0-based)
        ));

    }
}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, SubTable table, int columnCount,
        String facetType) {/*  w ww .j ava2  s  .  co m*/
    Map<String, UIComponent> map = table.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else if (component instanceof UIPanel) {
            String header = "";
            for (UIComponent child : component.getChildren()) {
                headerValue = exportValue(context, child);
                header = header + headerValue;
            }
            headerValue = header;
        } else {
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                sheetRowIndex, //last row  (0-based)
                0, //first column (0-based)
                columnCount //last column  (0-based)
        ));

    }
}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, DataList list, String facetType) {
    Map<String, UIComponent> map = list.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else {/*from   w ww.ja va2 s.c o  m*/
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                sheetRowIndex, //last row  (0-based)
                0, //first column (0-based)
                1 //last column  (0-based)
        ));

    }
}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, DataTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }/*from ww w  . j  a  v  a2 s.c o  m*/
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {
                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellValue(value);
                                cell.setCellStyle(facetStyle);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                        sheetRowIndex + (rowSpan - 1), //last row  (0-based)
                                        i, //first column (0-based)
                                        i //last column  (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);

                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellValue(value);
                            cell.setCellStyle(facetStyle);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                    sheetRowIndex, //last row  (0-based)
                                    i, //first column (0-based)
                                    i + (colSpan - 1) //last column  (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);
                    }

                    i++;
                }
            }

        }

    }
}