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

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

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

Introduction

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

Prototype

Row createRow(int rownum);

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:com.toba.bll.admin.ReportsDownloadServlet.java

/**
 * Handles the HTTP <code>GET</code> method.
 *
 * @param request servlet request/* w  ww .  ja  v a 2s.  co m*/
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("Users Registered in the Past Month");
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("User Name");
    row.createCell(1).setCellValue("First Name");
    row.createCell(2).setCellValue("Last Name");
    row.createCell(3).setCellValue("Registration Date");

    Calendar calendar = Calendar.getInstance();
    calendar.add(Calendar.MONTH, -1);
    Date oneMonthAgo = calendar.getTime();

    List<User> usersRegisteredInThePastMonth = UserDB.selectUsersRegisteredAfter(oneMonthAgo);

    HSSFCellStyle dateCellStyle = (HSSFCellStyle) workbook.createCellStyle();
    short dateDataFormat = workbook.createDataFormat().getFormat("dd/MM/yyyy");
    dateCellStyle.setDataFormat(dateDataFormat);

    for (int i = 0; i < usersRegisteredInThePastMonth.size(); i++) {
        User user = usersRegisteredInThePastMonth.get(i);

        row = sheet.createRow(1 + i);
        row.createCell(0).setCellValue(user.getUserName());
        row.createCell(1).setCellValue(user.getFirstName());
        row.createCell(2).setCellValue(user.getLastName());
        row.createCell(3).setCellValue(user.getRegistrationDate());
        row.getCell(3).setCellStyle(dateCellStyle);
    }

    workbook.write(response.getOutputStream());
    workbook.close();

    response.setHeader("content-disposition", "attachment; filename=users.xls");
    response.setHeader("cache-control", "no-cache");
}

From source file:com.toolsverse.etl.connector.excel.ExcelConnector.java

License:Open Source License

@SuppressWarnings("resource")
public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception {
    String fileName = null;//from  www. j a  va2 s . c om

    OutputStream out = null;

    if (params.getOutputStream() == null) {
        fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName(
                dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xls", true));

        params.setRealFileName(fileName);

        out = new FileOutputStream(fileName);

        if (params.getTransactionMonitor() != null)
            params.getTransactionMonitor().addFile(fileName);
    } else
        out = params.getOutputStream();

    params.setOut(out);

    Workbook workbook = new HSSFWorkbook();

    params.setWorkbook(workbook);

    Sheet sheet = workbook
            .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());

    params.setSheet(sheet);

    Font labelFont = workbook.createFont();
    labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle labelCellStyle = workbook.createCellStyle();
    labelCellStyle.setFont(labelFont);

    DataFormat dateTimeFormat = workbook.createDataFormat();
    CellStyle dateTimeCellStyle = workbook.createCellStyle();
    dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));

    params.setDateTimeCellStyle(dateTimeCellStyle);

    DataFormat dateFormat = workbook.createDataFormat();
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));

    params.setDateCellStyle(dateCellStyle);

    DataFormat timeFormat = workbook.createDataFormat();
    CellStyle timeCellStyle = workbook.createCellStyle();
    timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));

    params.setTimeCellStyle(timeCellStyle);

    // column names
    Row excelRow = sheet.createRow(0);

    // metadata
    int col = 0;
    for (FieldDef fieldDef : dataSet.getFields().getList()) {
        if (!fieldDef.isVisible())
            continue;

        Cell labelCell = excelRow.createCell(col++, Cell.CELL_TYPE_STRING);
        labelCell.setCellStyle(labelCellStyle);
        labelCell.setCellValue(fieldDef.getName());
    }

    params.setPrePersistOccured(true);
}

From source file:com.toolsverse.etl.connector.excel.ExcelXlsxConnector.java

License:Open Source License

@SuppressWarnings("resource")
public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception {
    String fileName = null;//  w  w w.  j av a2  s. co m

    OutputStream out = null;

    if (params.getOutputStream() == null) {
        fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName(
                dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xlsx", true));

        params.setRealFileName(fileName);

        out = new FileOutputStream(fileName);

        if (params.getTransactionMonitor() != null)
            params.getTransactionMonitor().addFile(fileName);
    } else
        out = params.getOutputStream();

    params.setOut(out);

    Workbook workbook = new SXSSFWorkbook(100);

    params.setWorkbook(workbook);

    Sheet sheet = workbook
            .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());

    params.setSheet(sheet);

    Font labelFont = workbook.createFont();
    labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle labelCellStyle = workbook.createCellStyle();
    labelCellStyle.setFont(labelFont);

    DataFormat dateTimeFormat = workbook.createDataFormat();
    CellStyle dateTimeCellStyle = workbook.createCellStyle();
    dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));

    params.setDateTimeCellStyle(dateTimeCellStyle);

    DataFormat dateFormat = workbook.createDataFormat();
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));

    params.setDateCellStyle(dateCellStyle);

    DataFormat timeFormat = workbook.createDataFormat();
    CellStyle timeCellStyle = workbook.createCellStyle();
    timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));

    params.setTimeCellStyle(timeCellStyle);

    // column names
    Row excelRow = sheet.createRow(0);

    // metadata
    int col = 0;
    for (FieldDef fieldDef : dataSet.getFields().getList()) {
        if (!fieldDef.isVisible())
            continue;

        Cell labelCell = excelRow.createCell(col++);
        labelCell.setCellStyle(labelCellStyle);
        labelCell.setCellValue(fieldDef.getName());
    }

    params.setPrePersistOccured(true);
}

From source file:com.tremolosecurity.scale.ui.reports.GenerateSpreadsheet.java

License:Apache License

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

    resp.setHeader("Cache-Control", "private, no-store, no-cache, must-revalidate");
    resp.setHeader("Pragma", "no-cache");

    ReportViewer scaleReport = (ReportViewer) req.getSession().getAttribute("scaleReportCached");

    Workbook wb = new XSSFWorkbook();

    Font font = wb.createFont();/*from   w  w  w .ja  va 2  s  . com*/
    font.setBold(true);

    Font titleFont = wb.createFont();
    titleFont.setBold(true);
    titleFont.setFontHeightInPoints((short) 16);

    Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(scaleReport.getReportInfo().getName()));

    //Create a header
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);

    RichTextString title = new XSSFRichTextString(scaleReport.getReportInfo().getName());
    title.applyFont(titleFont);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

    cell.setCellValue(title);

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue(scaleReport.getReportInfo().getDescription());

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

    row = sheet.createRow(2);
    cell = row.createCell(0);
    cell.setCellValue(scaleReport.getRunDateTime());

    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));

    row = sheet.createRow(3);

    int rowNum = 4;

    if (scaleReport.getResults().getGrouping().isEmpty()) {
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("There is no data for this report");
    } else {

        for (ReportGrouping group : scaleReport.getResults().getGrouping()) {
            for (String colHeader : scaleReport.getResults().getHeaderFields()) {
                row = sheet.createRow(rowNum);
                cell = row.createCell(0);

                RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                rcolHeader.applyFont(font);

                cell.setCellValue(rcolHeader);
                cell = row.createCell(1);
                cell.setCellValue(group.getHeader().get(colHeader));

                rowNum++;
            }

            row = sheet.createRow(rowNum);

            int cellNum = 0;
            for (String colHeader : scaleReport.getResults().getDataFields()) {
                cell = row.createCell(cellNum);

                RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                rcolHeader.applyFont(font);
                cell.setCellValue(rcolHeader);
                cellNum++;
            }

            rowNum++;

            for (Map<String, String> dataRow : group.getData()) {
                cellNum = 0;
                row = sheet.createRow(rowNum);
                for (String colHeader : scaleReport.getResults().getDataFields()) {
                    cell = row.createCell(cellNum);
                    cell.setCellValue(dataRow.get(colHeader));
                    cellNum++;
                }
                rowNum++;
            }

            row = sheet.createRow(rowNum);
            rowNum++;
        }

    }

    resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    wb.write(resp.getOutputStream());

}

From source file:com.tremolosecurity.scalejs.ws.ScaleMain.java

License:Apache License

private void exportToExcel(HttpFilterRequest request, HttpFilterResponse response, Gson gson)
        throws IOException {
    int lastslash = request.getRequestURI().lastIndexOf('/');
    int secondlastslash = request.getRequestURI().lastIndexOf('/', lastslash - 1);

    String id = request.getRequestURI().substring(secondlastslash + 1, lastslash);

    ReportResults res = (ReportResults) request.getSession().getAttribute(id);

    if (res == null) {
        response.setStatus(404);/* w  w w  .j  ava2 s  .c o  m*/
        ScaleError error = new ScaleError();
        error.getErrors().add("Report no longer available");
        ScaleJSUtils.addCacheHeaders(response);
        response.getWriter().print(gson.toJson(error).trim());
        response.getWriter().flush();
    } else {

        response.setHeader("Cache-Control", "private, no-store, no-cache, must-revalidate");
        response.setHeader("Pragma", "no-cache");

        Workbook wb = new XSSFWorkbook();

        Font font = wb.createFont();
        font.setBold(true);

        Font titleFont = wb.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 16);

        Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(res.getName()));

        //Create a header
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);

        RichTextString title = new XSSFRichTextString(res.getName());
        title.applyFont(titleFont);

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

        cell.setCellValue(title);

        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue(res.getDescription());

        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

        row = sheet.createRow(2);
        cell = row.createCell(0);
        //cell.setCellValue(new DateTime().toString("MMMM Do, YYYY h:mm:ss a"));

        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));

        row = sheet.createRow(3);

        int rowNum = 4;

        if (res.getGrouping().isEmpty()) {
            row = sheet.createRow(rowNum);
            cell = row.createCell(0);
            cell.setCellValue("There is no data for this report");
        } else {

            for (ReportGrouping group : res.getGrouping()) {
                for (String colHeader : res.getHeaderFields()) {
                    row = sheet.createRow(rowNum);
                    cell = row.createCell(0);

                    RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                    rcolHeader.applyFont(font);

                    cell.setCellValue(rcolHeader);
                    cell = row.createCell(1);
                    cell.setCellValue(group.getHeader().get(colHeader));

                    rowNum++;
                }

                row = sheet.createRow(rowNum);

                int cellNum = 0;
                for (String colHeader : res.getDataFields()) {
                    cell = row.createCell(cellNum);

                    RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                    rcolHeader.applyFont(font);
                    cell.setCellValue(rcolHeader);
                    cellNum++;
                }

                rowNum++;

                for (Map<String, String> dataRow : group.getData()) {
                    cellNum = 0;
                    row = sheet.createRow(rowNum);
                    for (String colHeader : res.getDataFields()) {
                        cell = row.createCell(cellNum);
                        cell.setCellValue(dataRow.get(colHeader));
                        cellNum++;
                    }
                    rowNum++;
                }

                row = sheet.createRow(rowNum);
                rowNum++;
            }

        }

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        wb.write(response.getOutputStream());
    }
}

From source file:com.upbest.utils.AddDimensionedImage.java

License:Apache License

/**
 * Determines whether the sheets row should be re-sized to accomodate
 * the image, adjusts the rows height if necessary and creates then
 * returns a ClientAnchorDetail object that facilitates construction of
 * a ClientAnchor that will fix the image on the sheet and establish
 * it's size.//w ww .  jav a2 s.c  om
 *
 * @param sheet A reference to the sheet that will 'contain' the image.
 * @param rowNumber A primitive int that contains the index number of a
 *                  row on the sheet.
 * @param reqImageHeightMM A primitive double that contains the required
 *                         height of the image in millimetres
 * @param resizeBehaviour A primitive int whose value will indicate how the
 *                        height of the row should be adjusted if the
 *                        required height of the image is greater than the
 *                        height of the row.
 * @return An instance of the ClientAnchorDetail class that will contain
 *         the index number of the row containing the cell whose top
 *         left hand corner also defines the top left hand corner of the
 *         image, the index number of the row containing the cell whose
 *         top left hand corner also defines the bottom right hand
 *         corner of the image and an inset that determines how far the
 *         bottom edge of the image can protrude into the next (lower)
 *         row - expressed as a specific number of coordinate positions.
 */
private ClientAnchorDetail fitImageToRows(Sheet sheet, int rowNumber, double reqImageHeightMM,
        int resizeBehaviour) {
    Row row = null;
    double rowHeightMM = 0.0D;
    double rowCoordinatesPerMM = 0.0D;
    int pictureHeightCoordinates = 0;
    ClientAnchorDetail rowClientAnchorDetail = null;

    // Get the row and it's height
    row = sheet.getRow(rowNumber);
    if (row == null) {
        // Create row if it does not exist.
        row = sheet.createRow(rowNumber);
    }

    // Get the row's height in millimetres
    rowHeightMM = row.getHeightInPoints() / ConvertImageUnits.POINTS_PER_MILLIMETRE;

    // Check that the row's height will accomodate the image at the required
    // dimensions. If the height of the row is LESS than the required height
    // of the image, decide how the application should respond - resize the
    // row or overlay the image across a series of rows.
    if (rowHeightMM < reqImageHeightMM) {
        if ((resizeBehaviour == AddDimensionedImage.EXPAND_ROW)
                || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW_AND_COLUMN)) {
            row.setHeightInPoints((float) (reqImageHeightMM * ConvertImageUnits.POINTS_PER_MILLIMETRE));
            if (sheet instanceof HSSFSheet) {
                rowHeightMM = reqImageHeightMM;
                rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM;
                pictureHeightCoordinates = (int) (reqImageHeightMM * rowCoordinatesPerMM);
            } else {
                pictureHeightCoordinates = (int) (reqImageHeightMM * AddDimensionedImage.EMU_PER_MM);
            }
            rowClientAnchorDetail = new ClientAnchorDetail(rowNumber, rowNumber, pictureHeightCoordinates);
        }
        // If the user has chosen to overlay both rows and columns or just
        // to expand ONLY the size of the columns, then calculate how to lay
        // the image out ver one or more rows.
        else if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN)
                || (resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN)) {
            rowClientAnchorDetail = this.calculateRowLocation(sheet, rowNumber, reqImageHeightMM);
        }
    }
    // Else, if the image is smaller than the space available
    else {
        if (sheet instanceof HSSFSheet) {
            rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM;
            pictureHeightCoordinates = (int) (reqImageHeightMM * rowCoordinatesPerMM);
        } else {
            pictureHeightCoordinates = (int) (reqImageHeightMM * AddDimensionedImage.EMU_PER_MM);
        }
        rowClientAnchorDetail = new ClientAnchorDetail(rowNumber, rowNumber, pictureHeightCoordinates);
    }
    return (rowClientAnchorDetail);
}

From source file:com.upbest.utils.AddDimensionedImage.java

License:Apache License

/**
 * If the image is to overlie more than one rows, calculations need to be
 * performed to determine how many rows and whether the image will
 * overlie just a part of one row in order to be presented at the
 * required size.// w  w  w  .j  a v  a2  s .  c om
 *
 * @param sheet The sheet that will 'contain' the image.
 * @param startingRow A primitive int whose value is the index of the row
 *                    that contains the cell whose top left hand corner
 *                    should be aligned with the top left hand corner of
 *                    the image.
 * @param reqImageHeightMM A primitive double whose value will indicate the
 *                         required height of the image in millimetres.
 * @return An instance of the ClientAnchorDetail class that will contain
 *         the index number of the row containing the cell whose top
 *         left hand corner also defines the top left hand corner of the
 *         image, the index number of the row containing the cell whose top
 *         left hand corner also defines the bottom right hand corner of
 *         the image and an inset that determines how far the bottom edge
 *         can protrude into the next (lower) row - expressed as a specific
 *         number of co-ordinate positions.
 */
private ClientAnchorDetail calculateRowLocation(Sheet sheet, int startingRow, double reqImageHeightMM) {
    ClientAnchorDetail clientAnchorDetail = null;
    Row row = null;
    double rowHeightMM = 0.0D;
    double totalRowHeightMM = 0.0D;
    double overlapMM = 0.0D;
    double rowCoordinatesPerMM = 0.0D;
    int toRow = startingRow;
    int inset = 0;

    // Step through the rows in the sheet and accumulate a total of their
    // heights.
    while (totalRowHeightMM < reqImageHeightMM) {
        row = sheet.getRow(toRow);
        // Note, if the row does not already exist on the sheet then create
        // it here.
        if (row == null) {
            row = sheet.createRow(toRow);
        }
        // Get the row's height in millimetres and add to the running total.
        rowHeightMM = row.getHeightInPoints() / ConvertImageUnits.POINTS_PER_MILLIMETRE;
        totalRowHeightMM += rowHeightMM;
        toRow++;
    }
    // Owing to the way the loop above works, the rowNumber will have been
    // incremented one row too far. Undo that here.
    toRow--;
    // Check to see whether the image should occupy an exact number of
    // rows. If so, build the ClientAnchorDetail record to point
    // to those rows and with an inset of the total number of co-ordinate
    // position in the row.
    //
    // To overcome problems that can occur with comparing double values for
    // equality, cast both to int(s) to truncate the value; VERY crude and
    // I do not really like it!!
    if ((int) totalRowHeightMM == (int) reqImageHeightMM) {
        if (sheet instanceof HSSFSheet) {
            clientAnchorDetail = new ClientAnchorDetail(startingRow, toRow,
                    ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS);
        } else {
            clientAnchorDetail = new ClientAnchorDetail(startingRow, toRow,
                    (int) reqImageHeightMM * AddDimensionedImage.EMU_PER_MM);
        }
    } else {
        // Calculate how far the image will project into the next row. Note
        // that the height of the last row assessed is subtracted from the
        // total height of all rows assessed so far.
        overlapMM = reqImageHeightMM - (totalRowHeightMM - rowHeightMM);

        // To prevent an exception being thrown when the required width of
        // the image is very close indeed to the column size.
        if (overlapMM < 0) {
            overlapMM = 0.0D;
        }

        if (sheet instanceof HSSFSheet) {
            rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM;
            inset = (int) (overlapMM * rowCoordinatesPerMM);
        } else {
            inset = (int) overlapMM * AddDimensionedImage.EMU_PER_MM;
        }
        clientAnchorDetail = new ClientAnchorDetail(startingRow, toRow, inset);
    }
    return (clientAnchorDetail);
}

From source file:com.vaadin.addon.spreadsheet.action.InsertDeleteCellCommentAction.java

License:Open Source License

@Override
public void executeActionOnSelection(Spreadsheet spreadsheet, SelectionChangeEvent event) {
    Sheet sheet = spreadsheet.getActiveSheet();
    CellReference cr = event.getSelectedCellReference();
    boolean cellCreated = false, rowCreated = false, commentEdited = false;
    Row row = sheet.getRow(cr.getRow());
    if (row == null) {
        row = sheet.createRow(cr.getRow());
        rowCreated = true;/*w ww. j  ava 2 s  .c  o  m*/
    }
    Cell cell = spreadsheet.getCell(cr);
    if (cell == null) {
        cell = row.createCell(cr.getCol());
        cellCreated = true;
    }
    if (cell.getCellComment() == null) {
        createCellComment(spreadsheet, sheet, cell, cr);
        commentEdited = true;
    } else {
        cell.removeCellComment();
        if (cellCreated) {
            sheet.getRow(cr.getRow()).removeCell(cell);
        }
        if (rowCreated) {
            sheet.removeRow(sheet.getRow(cr.getRow()));
        }
    }
    if (cell != null) {
        spreadsheet.refreshCells(cell);
    }
    if (commentEdited) {
        spreadsheet.editCellComment(cr);
    }
}

From source file:com.vaadin.addon.spreadsheet.action.ShowHideCellCommentAction.java

License:Open Source License

@Override
public void executeActionOnSelection(Spreadsheet spreadsheet, SelectionChangeEvent event) {
    CellReference cr = event.getSelectedCellReference();
    Comment cellComment = spreadsheet.getActiveSheet().getCellComment(cr.getRow(), cr.getCol());
    cellComment.setVisible(!cellComment.isVisible());
    Sheet sheet = spreadsheet.getActiveSheet();
    Row row = sheet.getRow(cr.getRow());
    if (row == null) {
        row = sheet.createRow(cr.getRow());
    }/*from ww w. ja  v a2s. com*/
    Cell cell = spreadsheet.getCell(cr);
    if (cell == null) {
        cell = row.createCell(cr.getCol());
    }
    spreadsheet.refreshCells(cell);
}

From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java

License:Open Source License

private void shiftRowsDownInSelection(int newLastRow) {
    CellRangeAddress paintedCellRange = spreadsheet.getCellSelectionManager().getSelectedCellRange();
    int r1 = paintedCellRange.getFirstRow() + 1;
    int r2 = paintedCellRange.getLastRow() + 1;
    int c1 = paintedCellRange.getFirstColumn() + 1;
    int c2 = paintedCellRange.getLastColumn() + 1;
    Workbook workbook = spreadsheet.getWorkbook();
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    for (int shiftedRowIndex = r1; shiftedRowIndex <= r2; shiftedRowIndex++) {
        final Row shiftedRow = activeSheet.getRow(shiftedRowIndex - 1);
        int newRowIndex = r2 + 1 + (shiftedRowIndex - r1);
        while (newRowIndex <= newLastRow) {
            if (shiftedRow != null) {
                Row newRow = activeSheet.getRow(newRowIndex - 1);
                if (newRow == null) {
                    newRow = activeSheet.createRow(newRowIndex - 1);
                }//from w ww.  jav  a2 s . c o m
                for (int c = c1; c <= c2; c++) {
                    Double sequenceIncrement = getColumnSequenceIncrement(c, r1, r2);
                    Cell shiftedCell = shiftedRow.getCell(c - 1);
                    Cell newCell = newRow.getCell(c - 1);
                    if (shiftedCell != null) {
                        if (newCell == null) {
                            newCell = newRow.createCell(c - 1);
                        }
                        shiftCellValue(shiftedCell, newCell, false, sequenceIncrement);
                    } else if (newCell != null) {
                        // update style to 0
                        newCell.setCellStyle(null);
                        spreadsheet.getSpreadsheetStyleFactory().cellStyleUpdated(newCell, true);
                        newCell.setCellValue((String) null);
                        getCellValueManager().cellDeleted(newCell);
                    }
                }
            } else {
                getCellValueManager().removeCells(newRowIndex, c1, newRowIndex, c2, true);
            }
            newRowIndex += r2 - r1 + 1;
        }
    }
}