Example usage for org.apache.poi.ss.usermodel ClientAnchor setRow2

List of usage examples for org.apache.poi.ss.usermodel ClientAnchor setRow2

Introduction

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

Prototype

public void setRow2(int row2);

Source Link

Document

Returns the row (0 based) of the first cell.

Usage

From source file:com.cms.utils.ExportExcell.java

private Comment getcellComment(FormatExcell item, Cell cell) {
    ExcellHeaderComment headerCommand = item.getHeaderCommand();
    Drawing sSFPatriarch = sh.createDrawingPatriarch();
    CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper();
    ClientAnchor anchor = factory.createClientAnchor();
    Comment comment1 = sSFPatriarch.createCellComment(anchor);

    anchor.setCol1(headerCommand.getRow1());
    anchor.setCol2(headerCommand.getRow2());
    anchor.setRow1(headerCommand.getColumn1());
    anchor.setRow2(headerCommand.getColumn2());
    anchor.setDx1(headerCommand.getDx1());
    anchor.setDx2(headerCommand.getDx2());
    anchor.setDy1(headerCommand.getDy1());
    anchor.setDy2(headerCommand.getDy2());

    RichTextString rtf1 = factory.createRichTextString(headerCommand.getValue());
    Font font = wb.createFont();/*  w  w w .  j  a v  a  2  s  .  co m*/
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    font.setColor(IndexedColors.RED.getIndex());
    rtf1.applyFont(font);
    comment1.setString(rtf1);
    comment1.setAuthor("Logistics");
    //        comment1.setColumn(cell.getColumnIndex());
    //        comment1.setRow(cell.getRowIndex());
    return comment1;
}

From source file:com.cms.utils.ExportExcell.java

private Comment getcellComment(Sheet sh, FormatExcell item, Cell cell) {
    ExcellHeaderComment headerCommand = item.getHeaderCommand();
    Drawing sSFPatriarch = sh.createDrawingPatriarch();
    CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper();
    ClientAnchor anchor = factory.createClientAnchor();
    Comment comment1 = sSFPatriarch.createCellComment(anchor);

    anchor.setCol1(headerCommand.getRow1());
    anchor.setCol2(headerCommand.getRow2());
    anchor.setRow1(headerCommand.getColumn1());
    anchor.setRow2(headerCommand.getColumn2());
    anchor.setDx1(headerCommand.getDx1());
    anchor.setDx2(headerCommand.getDx2());
    anchor.setDy1(headerCommand.getDy1());
    anchor.setDy2(headerCommand.getDy2());

    RichTextString rtf1 = factory.createRichTextString(headerCommand.getValue());
    Font font = wb.createFont();/*from   www .j a v  a 2  s .  c  o  m*/
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    font.setColor(IndexedColors.RED.getIndex());
    rtf1.applyFont(font);
    comment1.setString(rtf1);
    comment1.setAuthor("Logistics");
    //        comment1.setColumn(cell.getColumnIndex());
    //        comment1.setRow(cell.getRowIndex());
    return comment1;
}

From source file:com.docdoku.server.export.ExcelGenerator.java

License:Open Source License

public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
    File excelFile = new File("export_parts.xls");
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Parts Data");

    String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
    String[] columns = header.split(";");

    Map<Integer, String[]> data = new HashMap<>();
    String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
    data.put(1, headerFormatted);/*from   w w w. j  a va  2  s  . c  o m*/

    Map<Integer, String[]> commentsData = new HashMap<>();
    String[] headerComments = createXLSHeaderRowComments(header, columns);
    commentsData.put(1, headerComments);

    List<String> selects = queryResult.getQuery().getSelects();
    int i = 1;
    for (QueryResultRow row : queryResult.getRows()) {
        i++;
        data.put(i, createXLSRow(selects, row, baseURL));
        commentsData.put(i, createXLSRowComments(selects, row));
    }

    //Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;

    for (Integer key : keyset) {

        Row row = sheet.createRow(rownum++);
        String[] objArr = data.get(key);
        int cellnum = 0;
        for (String obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            cell.setCellValue(obj);
        }

        CreationHelper factory = workbook.getCreationHelper();
        Drawing drawing = sheet.createDrawingPatriarch();
        String[] commentsObjArr = commentsData.get(key);
        cellnum = 0;
        for (String commentsObj : commentsObjArr) {
            if (commentsObj.length() > 0) {
                Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);

                // When the comment box is visible, have it show in a 1x3 space
                ClientAnchor anchor = factory.createClientAnchor();
                anchor.setCol1(cell.getColumnIndex());
                anchor.setCol2(cell.getColumnIndex() + 1);
                anchor.setRow1(row.getRowNum());
                anchor.setRow2(row.getRowNum() + 1);

                Comment comment = drawing.createCellComment(anchor);
                RichTextString str = factory.createRichTextString(commentsObj);
                comment.setString(str);

                // Assign the comment to the cell
                cell.setCellComment(comment);
            }
            cellnum++;
        }
    }

    // Define header style
    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setFontName("Courier New");
    headerFont.setItalic(true);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFont(headerFont);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    // Set header style
    for (int j = 0; j < columns.length; j++) {
        Cell cell = sheet.getRow(0).getCell(j);
        cell.setCellStyle(headerStyle);

        if (cell.getCellComment() != null) {
            String comment = cell.getCellComment().getString().toString();

            if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER)
                    || comment.equals(QueryField.PART_MASTER_NUMBER)) {
                for (int k = 0; k < queryResult.getRows().size(); k++) {
                    Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j)
                            : sheet.getRow(k + 1).createCell(j);
                    grayCell.setCellStyle(headerStyle);
                }
            }
        }
    }

    try {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(excelFile);
        workbook.write(out);
        out.close();
    } catch (Exception e) {
        LOGGER.log(Level.FINEST, null, e);
    }
    return excelFile;

}

From source file:com.haulmont.yarg.formatters.impl.inline.AbstractInliner.java

License:Apache License

@Override
public void inlineToXls(HSSFPatriarch patriarch, HSSFCell resultCell, Object paramValue,
        Matcher paramsMatcher) {/*w  w  w.j a v  a  2s  . c  o  m*/
    try {
        Image image = new Image(paramValue, paramsMatcher);
        if (image.isValid()) {
            HSSFSheet sheet = resultCell.getSheet();
            HSSFWorkbook workbook = sheet.getWorkbook();

            int pictureIdx = workbook.addPicture(image.imageContent, Workbook.PICTURE_TYPE_JPEG);

            CreationHelper helper = workbook.getCreationHelper();
            ClientAnchor anchor = helper.createClientAnchor();
            anchor.setCol1(resultCell.getColumnIndex());
            anchor.setRow1(resultCell.getRowIndex());
            anchor.setCol2(resultCell.getColumnIndex());
            anchor.setRow2(resultCell.getRowIndex());
            if (patriarch == null) {
                throw new IllegalArgumentException(String.format(
                        "No HSSFPatriarch object provided. Charts on this sheet could cause this effect. Please check sheet %s",
                        resultCell.getSheet().getSheetName()));
            }
            HSSFPicture picture = patriarch.createPicture(anchor, pictureIdx);
            Dimension size = ImageUtils.getDimensionFromAnchor(picture);
            double actualHeight = size.getHeight() / EMU_PER_PIXEL;
            double actualWidth = size.getWidth() / EMU_PER_PIXEL;
            picture.resize((double) image.width / actualWidth, (double) image.height / actualHeight);
        }
    } catch (IllegalArgumentException e) {
        throw new ReportFormattingException("An error occurred while inserting bitmap to xls file", e);
    }
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public void addImage(File imageFile, String name, boolean originalSize)
        throws FileNotFoundException, IOException {
    Name cname = getName(name);/*  www. ja  v  a2 s  . c o  m*/

    // Get sheet where name is defined in
    Sheet sheet = workbook.getSheet(cname.getSheetName());

    AreaReference aref = new AreaReference(cname.getRefersToFormula());
    // Get name corners (top left, bottom right)
    CellReference topLeft = aref.getFirstCell();
    CellReference bottomRight = aref.getLastCell();

    // Determine image type
    int imageType;
    String filename = imageFile.getName().toLowerCase();
    if (filename.endsWith("jpg") || filename.endsWith("jpeg")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_JPEG;
    } else if (filename.endsWith("png")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_PNG;
    } else if (filename.endsWith("wmf")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_WMF;
    } else if (filename.endsWith("emf")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_EMF;
    } else if (filename.endsWith("bmp") || filename.endsWith("dib")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_DIB;
    } else if (filename.endsWith("pict") || filename.endsWith("pct") || filename.endsWith("pic")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_PICT;
    } else
        throw new IllegalArgumentException(
                "Image type \"" + filename.substring(filename.lastIndexOf('.') + 1) + "\" not supported!");

    InputStream is = new FileInputStream(imageFile);
    byte[] bytes = IOUtils.toByteArray(is);
    int imageIndex = workbook.addPicture(bytes, imageType);
    is.close();

    Drawing drawing;
    if (isHSSF()) {
        drawing = ((HSSFSheet) sheet).getDrawingPatriarch();
        if (drawing == null) {
            drawing = sheet.createDrawingPatriarch();
        }
    } else if (isXSSF()) {
        drawing = ((XSSFSheet) sheet).createDrawingPatriarch();
    } else {
        drawing = sheet.createDrawingPatriarch();
    }

    CreationHelper helper = workbook.getCreationHelper();
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setRow1(topLeft.getRow());
    anchor.setCol1(topLeft.getCol());
    // +1 since we want to include the
    anchor.setRow2(bottomRight.getRow() + 1);
    anchor.setCol2(bottomRight.getCol() + 1);
    anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);

    Picture picture = drawing.createPicture(anchor, imageIndex);
    if (originalSize)
        picture.resize();
}

From source file:com.pe.nisira.movil.view.action.MultitablaAction.java

public StreamedContent downFormatExcel() throws Exception {
    InputStream stream = null;/*from  ww  w.  j a v a2  s . c  o m*/
    StreamedContent arch = null;
    try {
        String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION";
        File ruta = new File(folder);
        if (!ruta.isDirectory()) {
            ruta.mkdirs();
        }
        String rutaArchivo = folder + "\\FI_MULTITABLA.xlsx";
        File fileXls = new File(rutaArchivo);
        if (fileXls.exists()) {
            fileXls.delete();
        }
        fileXls.createNewFile();
        XSSFWorkbook libro = new XSSFWorkbook();
        FileOutputStream file = new FileOutputStream(fileXls);
        XSSFSheet hoja = libro.createSheet("IMPORTAR_MULTITABLA");
        CreationHelper factory = libro.getCreationHelper();
        hoja = libro.getSheetAt(0);
        XSSFCellStyle style = libro.createCellStyle();
        Font font = libro.createFont();
        Font font1 = libro.createFont();
        Drawing drawing = hoja.createDrawingPatriarch();
        ClientAnchor anchor1 = factory.createClientAnchor();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 8);
        font1.setFontHeightInPoints((short) 8);
        font1.setFontName("Arial");
        font.setFontName("Arial");
        style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70)));
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setAlignment(CellStyle.VERTICAL_CENTER);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(font);
        for (int f = 0; f < 1; f++) {
            XSSFRow fila = hoja.createRow(f);
            for (int c = 0; c < 4; c++) {
                XSSFCell celda = fila.createCell(c);
                celda.setCellStyle(style);
                anchor1.setCol1(celda.getColumnIndex());
                anchor1.setCol2(celda.getColumnIndex() + 4);
                anchor1.setRow1(fila.getRowNum());
                anchor1.setRow2(fila.getRowNum() + 3);
                Comment comment = drawing.createCellComment(anchor1);
                if (f == 0 && c == 0) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Indicar si es es Padre (Usar SI o NO).");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("Es Padre");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 1) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Opcional \n - Escribir la Abreviatura del campo del cual depende este.");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("Abreviatura Padre");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 2) {
                    RichTextString str = factory
                            .createRichTextString("ADM:\nCampo Obligatorio \n - Descripcion de la multitabla");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("DESCRIPCION");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 3) {
                    RichTextString str = factory
                            .createRichTextString("ADM:\nCampo Obligatorio \n - Abreviatura de la multitabla.");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("ABREVIATURA");
                    celda.setCellComment(comment);
                }
            }
        }
        hoja.autoSizeColumn((short) 0);
        hoja.autoSizeColumn((short) 1);
        hoja.autoSizeColumn((short) 2);
        libro.write(file);
        file.close();
        stream = new FileInputStream(new File(rutaArchivo));
        arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_MULTITABLA.xlsx");
    } catch (FileNotFoundException ex) {
        System.out.println("Error al Descargar : " + ex.getMessage());
    }
    return arch;
}

From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java

public StreamedContent downFormatExcel() throws Exception {
    InputStream stream = null;//from w  w  w  .  ja  v  a2  s . c  o  m
    StreamedContent arch = null;
    try {
        String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION";
        File ruta = new File(folder);
        if (!ruta.isDirectory()) {
            ruta.mkdirs();
        }
        String rutaArchivo = folder + "\\FI_REGISTROPALE.xlsx";
        File fileXls = new File(rutaArchivo);
        if (fileXls.exists()) {
            fileXls.delete();
        }
        fileXls.createNewFile();
        XSSFWorkbook libro = new XSSFWorkbook();
        FileOutputStream file2 = new FileOutputStream(fileXls);
        XSSFSheet hoja = libro.createSheet("IMPORTAR_PALETA");
        CreationHelper factory = libro.getCreationHelper();
        hoja = libro.getSheetAt(0);
        XSSFCellStyle style = libro.createCellStyle();
        Font font = libro.createFont();
        Font font1 = libro.createFont();
        Drawing drawing = hoja.createDrawingPatriarch();
        ClientAnchor anchor1 = factory.createClientAnchor();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 8);
        font1.setFontHeightInPoints((short) 8);
        font1.setFontName("Arial");
        font.setFontName("Arial");
        style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70)));
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setAlignment(CellStyle.VERTICAL_CENTER);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(font);

        XSSFSheet hoja2 = libro.createSheet("IMPORTAR_DET_PALETA");
        CreationHelper factory2 = libro.getCreationHelper();
        hoja2 = libro.getSheetAt(1);
        XSSFCellStyle style2 = libro.createCellStyle();
        Font font2 = libro.createFont();
        Font font12 = libro.createFont();
        Drawing drawing2 = hoja2.createDrawingPatriarch();
        ClientAnchor anchor12 = factory2.createClientAnchor();
        font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font2.setFontHeightInPoints((short) 8);
        font12.setFontHeightInPoints((short) 8);
        font12.setFontName("Arial");
        font2.setFontName("Arial");
        style2.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70)));
        style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style2.setAlignment(CellStyle.VERTICAL_CENTER);
        style2.setAlignment(CellStyle.ALIGN_CENTER);
        style2.setFont(font);
        for (int f = 0; f < 1; f++) {
            XSSFRow fila = hoja.createRow(f);
            for (int c = 0; c < 29; c++) {
                XSSFCell celda = fila.createCell(c);
                celda.setCellStyle(style);
                anchor1.setCol1(celda.getColumnIndex());
                anchor1.setCol2(celda.getColumnIndex() + 5);
                anchor1.setRow1(fila.getRowNum());
                anchor1.setRow2(fila.getRowNum() + 3);
                Comment comment = drawing.createCellComment(anchor1);
                if (f == 0 && c == 0) {
                    RichTextString str = factory.createRichTextString("ADM:\nCampo Obligatorio \n - IDEMPRESA");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDEMPRESA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 1) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - IDREGISTROPALETA. \n Debe de tener (15) caracteres");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDREGISTROPALETA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 2) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del emisor. \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDEMISOR");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 3) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - id de la operacion.\n -Debe tener 4 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDOPERACION");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 4) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Numero de Operacion.\n -Debe tener 10 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("NUMOPERACION");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 5) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del motivo de Paleta.\n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDMOTIVOPALETA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 6) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id documento. \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDDOCUMENTO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 7) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Serie del Documento. \n -Debe tener 4 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("SERIE");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 8) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Numero de Documento.\n -Debe tener 7 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("NUMERO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 9) {
                    RichTextString str = factory
                            .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("FECHA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 10) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Perido del ao \n - fromato YYYYMM.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("PERIODO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 11) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del estado \n -Debe tener 2 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDESTADO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 12) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del cliente o proveedor \n -Debe tener 11 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDCLIEPROV");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 13) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Numero de Paleta \n -Debe tener 20 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("NROPALETA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 14) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id de envase \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDENVASE");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 15) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id la sucursal \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDSUCURSAL");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 16) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del almacen. \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDALMACEN");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 17) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del embalaje. \n -Debe tener 10 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDEMBALAJE");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 18) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id de cultivo. \n -Debe tener 4 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDCULTIVO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 19) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - id de Variadd. \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDVARIEDAD");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 20) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Observaciones sobre la paleta \n -como maximo 240 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("OBSERVACIONES");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 21) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n -Nombre de la venta \n como maximo 50 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("VENTANA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 22) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Cantidad. \n - 15 numeros y 2 decimales como maximo.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("CANTIDAD");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 23) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Estado de la paleta \n- 1 = cerrado, 0 = Abierto.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("CERRADO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 24) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Sincroniza \n - N = no , S = si.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("SINCRONIZA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 25) {
                    RichTextString str = factory
                            .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("FECHACREACION");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 26) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Numero de Manural\n Debe tener 10 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("NROMANUAL");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 27) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - idcliepro-destino\n debe tener 11 caracteres");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDCLIEPROV_DESTINO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 28) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Tipo de paleta\n debe tener 1 caraccter..");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("TIPO");
                    celda.setCellComment(comment);
                }
            }
        }
        hoja.autoSizeColumn((short) 0);
        hoja.autoSizeColumn((short) 1);
        hoja.autoSizeColumn((short) 2);
        hoja.autoSizeColumn((short) 3);
        hoja.autoSizeColumn((short) 4);
        hoja.autoSizeColumn((short) 5);
        hoja.autoSizeColumn((short) 6);
        hoja.autoSizeColumn((short) 7);
        hoja.autoSizeColumn((short) 8);
        hoja.autoSizeColumn((short) 9);
        hoja.autoSizeColumn((short) 10);
        hoja.autoSizeColumn((short) 11);
        hoja.autoSizeColumn((short) 12);
        hoja.autoSizeColumn((short) 13);
        hoja.autoSizeColumn((short) 14);
        hoja.autoSizeColumn((short) 15);
        hoja.autoSizeColumn((short) 16);
        hoja.autoSizeColumn((short) 17);
        hoja.autoSizeColumn((short) 18);
        hoja.autoSizeColumn((short) 19);
        hoja.autoSizeColumn((short) 20);
        hoja.autoSizeColumn((short) 21);
        hoja.autoSizeColumn((short) 22);
        hoja.autoSizeColumn((short) 23);
        hoja.autoSizeColumn((short) 24);
        hoja.autoSizeColumn((short) 25);
        hoja.autoSizeColumn((short) 26);
        hoja.autoSizeColumn((short) 27);
        hoja.autoSizeColumn((short) 28);
        for (int f = 0; f < 2; f++) {
            XSSFRow fila2 = hoja2.createRow(f);
            if (f == 0) {
                for (int c = 0; c < 15; c++) {
                    XSSFCell celda2 = fila2.createCell(c);
                    anchor12.setCol1(celda2.getColumnIndex());
                    anchor12.setCol2(celda2.getColumnIndex() + 8);
                    anchor12.setRow1(fila2.getRowNum());
                    anchor12.setRow2(fila2.getRowNum() + 8);
                    Comment comment2 = drawing2.createCellComment(anchor12);
                    RichTextString str;
                    switch (c) {
                    case 0:
                        celda2.setCellStyle(style2);
                        str = factory2.createRichTextString(
                                "ADM:\nCampo Obligatorio \n - El Cdigo debe de ser nico.");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDEMPRESA");
                        celda2.setCellComment(comment2);
                        break;
                    case 1:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio ");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDREGISTROPALETA");
                        celda2.setCellComment(comment2);
                        break;
                    case 2:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio ");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("ITEM");
                        celda2.setCellComment(comment2);
                        break;
                    case 3:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio ");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDCLIEPROV");
                        celda2.setCellComment(comment2);
                        break;
                    case 4:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDLOTE");
                        celda2.setCellComment(comment2);
                        break;
                    case 5:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDCONDICION");
                        celda2.setCellComment(comment2);
                        break;
                    case 6:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDTALLA");
                        celda2.setCellComment(comment2);
                        break;
                    case 7:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDCOLOR");
                        celda2.setCellComment(comment2);
                        break;
                    case 8:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("CANTIDAD");
                        celda2.setCellComment(comment2);
                        break;
                    case 9:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDEMBALAJE");
                        celda2.setCellComment(comment2);
                        break;
                    case 10:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDPRODUCTO");
                        celda2.setCellComment(comment2);
                        break;
                    case 11:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDLOTEP");
                        celda2.setCellComment(comment2);
                        break;
                    case 12:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDCONSUMIDOR");
                        celda2.setCellComment(comment2);
                        break;
                    case 13:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDLOTECAMPO");
                        celda2.setCellComment(comment2);
                        break;
                    case 14:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDPRESENTACION");
                        celda2.setCellComment(comment2);
                        break;
                    }
                }
            }
        }
        hoja2.autoSizeColumn((short) 0);
        hoja2.autoSizeColumn((short) 1);
        hoja2.autoSizeColumn((short) 2);
        hoja2.autoSizeColumn((short) 3);
        hoja2.autoSizeColumn((short) 4);
        hoja2.autoSizeColumn((short) 5);
        hoja2.autoSizeColumn((short) 6);
        hoja2.autoSizeColumn((short) 7);
        hoja2.autoSizeColumn((short) 8);
        hoja2.autoSizeColumn((short) 9);
        hoja2.autoSizeColumn((short) 10);
        hoja2.autoSizeColumn((short) 11);
        hoja2.autoSizeColumn((short) 12);
        hoja2.autoSizeColumn((short) 13);
        hoja2.autoSizeColumn((short) 14);
        hoja2.autoSizeColumn((short) 15);
        libro.write(file2);
        file2.close();
        stream = new FileInputStream(new File(rutaArchivo));
        arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_REGISTROPALE.xlsx");
    } catch (FileNotFoundException ex) {
        System.out.println("Error al Descargar : " + ex.getMessage());
    }
    return arch;
}

From source file:com.quanticate.opensource.datalistdownload.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body.//from  www . j  a v a  2  s .  c  om
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");

    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else if ("odf".equals(format) || "ods".equals(format)) {
        try {
            SpreadsheetDocument odf = SpreadsheetDocument.newSpreadsheetDocument();

            // Add the header row
            Table sheet = odf.appendSheet("Export");
            org.odftoolkit.simple.table.Row hr = sheet.appendRow();

            // TODO

            // Have the contents populated
            // TODO

            // Save it for the template
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            odf.save(baos);
            model.put(MODEL_ODF, baos.toByteArray());
        } catch (Exception e) {
            throw new WebScriptException("Error creating ODF file", e);
        }
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:com.runwaysdk.dataaccess.io.ExcelExportSheet.java

License:Open Source License

protected void writeHeader(Sheet sheet, Drawing drawing, Row nameRow, Row labelRow, int i, ExcelColumn column,
        CellStyle boldStyle) {//w  w w  .j a v  a2 s  .c  om
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();

    // Notify the listeners
    for (ExcelExportListener listener : listeners) {
        listener.preHeader(column);
    }

    nameRow.createCell(i).setCellValue(helper.createRichTextString(column.getAttributeName()));

    Cell cell = labelRow.createCell(i);
    cell.setCellValue(helper.createRichTextString(column.getDisplayLabel()));

    if (column.isRequired() && boldStyle != null) {
        cell.setCellStyle(boldStyle);
    }

    if (column.getDescription() != null && column.getDescription().length() > 0) {
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setDx1(0);
        anchor.setDy1(0);
        anchor.setDx2(0);
        anchor.setDy2(0);
        anchor.setCol1(0);
        anchor.setRow1(0);
        anchor.setCol2(0);
        anchor.setRow2(4);

        Comment comment = drawing.createCellComment(anchor);
        comment.setString(helper.createRichTextString(column.getDescription()));

        cell.setCellComment(comment);
    }

    sheet.autoSizeColumn((short) i);
}

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

License:Apache License

/**
 * Add an image to a worksheet./*  www . j  a  v  a 2 s. c  o m*/
 *
 * @param colNumber A primitive int that contains the index number of a
 *                  column on the worksheet; POI column indices are zero
 *                  based. Together with the rowNumber parameter's value,
 *                  this parameter identifies a cell on the worksheet. The
 *                  images top left hand corner will be aligned with the
 *                  top left hand corner of this cell.
 * @param rowNumber A primitive int that contains the index number of a row
 *                  on the worksheet; POI row indices are zero based.
 *                  Together with the rowNumber parameter's value, this
 *                  parameter identifies a cell on the worksheet. The
 *                  images top left hand corner will be aligned with the
 *                  top left hand corner of this cell.
 * @param sheet A reference to the sheet that contains the cell identified
 *              by the two parameters above.
 * @param drawing An instance of the DrawingPatriarch class. This is now
 *                passed into the method where it was, previously, recovered
 *                from the sheet in order to allow multiple pictures be
 *                inserted. If the patriarch was not 'cached in this manner
 *                each time it was created any previously positioned images
 *                would be simply over-written.
 * @param imageFile An instance of the URL class that encapsulates the name
 *                  of and path to the image that is to be 'inserted into'
 *                  the sheet.
 * @param reqImageWidthMM A primitive double that contains the required
 *                        width of the image in millimetres.
 * @param reqImageHeightMM A primitive double that contains the required
 *                         height of the image in millimetres.
 * @param resizeBehaviour A primitive int whose value will determine how
 *                        the code should react if the image is larger than
 *                        the cell referenced by the colNumber and
 *                        rowNumber parameters. Four constants are provided
 *                        to determine what should happen;
 *                          AddDimensionedImage.EXPAND_ROW
 *                          AddDimensionedImage.EXPAND_COLUMN
 *                          AddDimensionedImage.EXPAND_ROW_AND_COLUMN
 *                          AddDimensionedImage.OVERLAY_ROW_AND_COLUMN
 * @throws java.io.FileNotFoundException If the file containing the image
 *                                       cannot be located.
 * @throws java.io.IOException If a problem occurs whilst reading the file
 *                             of image data.
 * @throws java.lang.IllegalArgumentException If an invalid value is passed
 *                                            to the resizeBehaviour
 *                                            parameter or if the extension
 *                                            of the image file indicates that
 *                                            it is of a type that cannot
 *                                            currently be added to the worksheet.
 */
public void addImageToSheet(int colNumber, int rowNumber, Sheet sheet, Drawing drawing, URL imageFile,
        double reqImageWidthMM, double reqImageHeightMM, int resizeBehaviour)
        throws IOException, IllegalArgumentException {
    ClientAnchor anchor = null;
    ClientAnchorDetail rowClientAnchorDetail = null;
    ClientAnchorDetail colClientAnchorDetail = null;
    int imageType = 0;

    // Validate the resizeBehaviour parameter.
    if ((resizeBehaviour != AddDimensionedImage.EXPAND_COLUMN)
            && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW)
            && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW_AND_COLUMN)
            && (resizeBehaviour != AddDimensionedImage.OVERLAY_ROW_AND_COLUMN)) {
        throw new IllegalArgumentException("Invalid value passed to the "
                + "resizeBehaviour parameter of AddDimensionedImage.addImageToSheet()");
    }

    // Call methods to calculate how the image and sheet should be
    // manipulated to accomodate the image; columns and then rows.
    colClientAnchorDetail = this.fitImageToColumns(sheet, colNumber, reqImageWidthMM, resizeBehaviour);
    rowClientAnchorDetail = this.fitImageToRows(sheet, rowNumber, reqImageHeightMM, resizeBehaviour);

    // Having determined if and how to resize the rows, columns and/or the
    // image, create the ClientAnchor object to position the image on
    // the worksheet. Note how the two ClientAnchorDetail records are
    // interrogated to recover the row/column co-ordinates and any insets.
    // The first two parameters are not used currently but could be if the
    // need arose to extend the functionality of this code by adding the
    // ability to specify that a clear 'border' be placed around the image.
    anchor = sheet.getWorkbook().getCreationHelper().createClientAnchor();

    anchor.setDx1(0);
    anchor.setDy1(0);
    anchor.setDx2(colClientAnchorDetail.getInset());
    anchor.setDy2(rowClientAnchorDetail.getInset());
    anchor.setCol1(colClientAnchorDetail.getFromIndex());
    anchor.setRow1(rowClientAnchorDetail.getFromIndex());
    anchor.setCol2(colClientAnchorDetail.getToIndex());
    anchor.setRow2(rowClientAnchorDetail.getToIndex());

    // For now, set the anchor type to do not move or resize the
    // image as the size of the row/column is adjusted. This could easilly
    // become another parameter passed to the method. Please read the note
    // above regarding the behaviour of image resizing.
    anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);

    // Now, add the picture to the workbook. Note that unlike the similar
    // method in the HSSF Examples section, the image type is checked. First,
    // the image files location is identified by interrogating the URL passed
    // to the method, the images type is identified before it is added to the
    // sheet.
    String sURL = imageFile.toString().toLowerCase();
    if (sURL.endsWith(".png")) {
        imageType = Workbook.PICTURE_TYPE_PNG;
    } else if (sURL.endsWith("jpg") || sURL.endsWith(".jpeg")) {
        imageType = Workbook.PICTURE_TYPE_JPEG;
    } else {
        throw new IllegalArgumentException("Invalid Image file : " + sURL);
    }
    int index = sheet.getWorkbook().addPicture(IOUtils.toByteArray(imageFile.openStream()), imageType);
    drawing.createPicture(anchor, index);
}