Examples with HSSFRow org.apache.poi.hssf.usermodel.HSSFRow used on opensource projects

Example 81 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class NumberRenderingSpreadsheetGenerator method writeDataRow.

static void writeDataRow(HSSFSheet sheet, int rowIx, long rawLongBits, String expectedExcelRendering) {
    double d = Double.longBitsToDouble(rawLongBits);
    HSSFRow row = sheet.createRow(rowIx);
    int rowNum = rowIx + 1;
    String cel0ref = "A" + rowNum;
    String rawBitsText = formatLongAsHex(rawLongBits);
    String jmExpr = "'ec(" + rawBitsText + ", ''\" & C" + rowNum + " & \"'', ''\" & D" + rowNum + " & \"''),'";
    // The 'Match' column will contain 'OK' if the metadata (from NumberToTextConversionExamples)
    // matches Excel's rendering.
    String matchExpr = "if(D" + rowNum + "=E" + rowNum + ", \"OK\", \"ERROR\")";
    row.createCell(0).setCellValue(d);
    row.createCell(1).setCellValue(new HSSFRichTextString(rawBitsText));
    row.createCell(2).setCellValue(new HSSFRichTextString(Double.toString(d)));
    row.createCell(3).setCellFormula("\"\" & " + cel0ref);
    row.createCell(4).setCellValue(new HSSFRichTextString(expectedExcelRendering));
    row.createCell(5).setCellFormula(matchExpr);
    row.createCell(6).setCellFormula(jmExpr.replaceAll("'", "\""));
//		if (false) {
//			// for observing arithmetic near numeric range boundaries
//			row.createCell(7).setCellFormula(cel0ref + " * 1.0001");
//			row.createCell(8).setCellFormula(cel0ref + " / 1.0001");
//		}
}

Example 82 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class Alignment method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    HSSFRow row = sheet.createRow(2);
    createCell(wb, row, 0, HorizontalAlignment.CENTER);
    createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION);
    createCell(wb, row, 2, HorizontalAlignment.FILL);
    createCell(wb, row, 3, HorizontalAlignment.GENERAL);
    createCell(wb, row, 4, HorizontalAlignment.JUSTIFY);
    createCell(wb, row, 5, HorizontalAlignment.LEFT);
    createCell(wb, row, 6, HorizontalAlignment.RIGHT);
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 83 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class AddDimensionedImage method calculateRowLocation.

/**
     * 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.
     *
     * @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(HSSFSheet sheet, int startingRow, double reqImageHeightMM) {
    ClientAnchorDetail clientAnchorDetail = null;
    HSSFRow row = null;
    double rowHeightMM = 0.0D;
    double totalRowHeightMM = 0.0D;
    double overlapMM = 0.0D;
    double rowCoordinatesPerMM = 0.0D;
    int toRow = startingRow;
    int inset = 0;
    // heights.
    while (totalRowHeightMM < reqImageHeightMM) {
        row = sheet.getRow(toRow);
        // 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--;
    // I do not really like it!!
    if ((int) totalRowHeightMM == (int) reqImageHeightMM) {
        clientAnchorDetail = new ClientAnchorDetail(startingRow, toRow, ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS);
    } 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);
        // the image is very close indeed to the column size.
        if (overlapMM < 0) {
            overlapMM = 0.0D;
        }
        rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM;
        inset = (int) (overlapMM * rowCoordinatesPerMM);
        clientAnchorDetail = new ClientAnchorDetail(startingRow, toRow, inset);
    }
    return (clientAnchorDetail);
}

Example 84 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class AddDimensionedImage method fitImageToRows.

/**
     * Determines whether the sheet's 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
     * an HSSFClientAnchor that will fix the image on the sheet and establish
     * it's size.
     *
     * @param sheet A reference to the sheet that will 'contain' the image.
     * @param rowNumber A primtive int that contains the index number of a
     *                  row on the sheet.
     * @param reqImageHeightMM A primtive double that contains the required
     *                         height of the image in millimetres
     * @param resizeBehaviour A primitve 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 co-ordinate positions.
     */
private ClientAnchorDetail fitImageToRows(HSSFSheet sheet, int rowNumber, double reqImageHeightMM, int resizeBehaviour) {
    HSSFRow 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;
    // 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));
            rowHeightMM = reqImageHeightMM;
            rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM;
            pictureHeightCoordinates = (int) (reqImageHeightMM * rowCoordinatesPerMM);
            rowClientAnchorDetail = new ClientAnchorDetail(rowNumber, rowNumber, pictureHeightCoordinates);
        } else // the image out ver one or more rows.
        if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN) || (resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN)) {
            rowClientAnchorDetail = this.calculateRowLocation(sheet, rowNumber, reqImageHeightMM);
        }
    } else // Else, if the image is smaller than the space available
    {
        rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM;
        pictureHeightCoordinates = (int) (reqImageHeightMM * rowCoordinatesPerMM);
        rowClientAnchorDetail = new ClientAnchorDetail(rowNumber, rowNumber, pictureHeightCoordinates);
    }
    return (rowClientAnchorDetail);
}

Example 85 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class Borders method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFSheet sheet = wb.createSheet("new sheet");
        // Create a row and put some cells in it. Rows are 0 based.
        HSSFRow row = sheet.createRow(1);
        // Create a cell and put a value in it.
        HSSFCell cell = row.createCell(1);
        cell.setCellValue(4);
        // Style the cell with borders all around.
        HSSFCellStyle style = wb.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(HSSFColorPredefined.GREEN.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(HSSFColorPredefined.BLUE.getIndex());
        style.setBorderTop(BorderStyle.MEDIUM_DASHED);
        style.setTopBorderColor(HSSFColorPredefined.ORANGE.getIndex());
        cell.setCellStyle(style);
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    } finally {
        wb.close();
    }
}