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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. ClientAnchor
  6. setRow1

Introduction

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

Prototype

public void setRow1(int row1);

Source Link

Document

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

Usage

From source file:com.actelion.research.spiritapp.report.StudyDesignReport.java

License:Open Source License

@Override
protected void populateWorkBook() throws Exception {
    CreationHelper helper = wb.getCreationHelper();

    //create sheet
    Sheet sheet = createSheet(wb, "Study Design");
    sheet.setPrintGridlines(false);//from   w w w . j  a  v  a 2s  .  c o m
    createHeadersWithTitle(sheet, study, "Study Design");

    // Create the drawing patriarch.  This is the top level container for all shapes.
    Drawing drawing = sheet.createDrawingPatriarch();

    ByteArrayOutputStream os = new ByteArrayOutputStream();
    //10.5inches*600 = 6300 pixels, 7.5*600=4500 pixels, on 600dpi
    int size = FastFont.getDefaultFontSize();
    FastFont.setDefaultFontSize(36);
    BufferedImage img = StudyDepictor.getImage(study, 6300, 4500, 1);
    ImageIO.write(img, "PNG", os);
    FastFont.setDefaultFontSize(size);

    //add a picture shape
    int pictureIdx = wb.addPicture(os.toByteArray(), Workbook.PICTURE_TYPE_PNG);

    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
    anchor.setCol1(0);
    anchor.setRow1(3);
    Picture pict = drawing.createPicture(anchor, pictureIdx);
    final double scaleDown = .33;
    //Samplings
    int y = (int) (img.getHeight() * scaleDown) / 20 + 5;
    int nSamplings = 0;
    for (NamedSampling ns : study.getNamedSamplings()) {
        nSamplings = Math.max(nSamplings, ns.getAllSamplings().size());
    }
    if (study.getNamedSamplings().size() > 0) {
        set(sheet, y++, 0, "Sampling Templates", Style.S_TITLE14BLUE);
        int count = 0;
        for (NamedSampling ns : study.getNamedSamplings()) {
            set(sheet, y, count, ns.getName(), Style.S_TH_LEFT);
            int line = 0;
            for (Sampling s : ns.getAllSamplings()) {
                set(sheet, y + (++line), count, MiscUtils.removeHtml(s.getDetailsWithMeasurements()),
                        Style.S_TD_LEFT);
            }
            while (line < nSamplings) {
                set(sheet, y + (++line), count, "", Style.S_TD_LEFT);
            }
            count++;
        }
    }

    //Treatments
    y += nSamplings + 3;
    if (study.getNamedTreatments().size() > 0) {
        set(sheet, y++, 0, "Treatments", Style.S_TITLE14BLUE);
        int count = 0;
        for (NamedTreatment nt : study.getNamedTreatments()) {
            set(sheet, y, count, nt.getName(), Style.S_TH_LEFT);
            set(sheet, y + 1, count, nt.getCompoundAndUnit1(), Style.S_TD_LEFT);
            set(sheet, y + 2, count, nt.getCompoundAndUnit2(), Style.S_TD_LEFT);
            count++;
        }
    }

    POIUtils.autoSizeColumns(sheet, -1, true);
    pict.resize();
    pict.resize(scaleDown);

}

From source file:com.automaster.autoview.server.servlet.ExcelServlet.java

protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    /*response.setContentType("application/vnd.ms-excel");
     response.setHeader("Content-Disposition", "attachment; filename=filename.xls");
     HSSFWorkbook workbook = new HSSFWorkbook();
     // ...//from   w w w  . j  a  v  a2  s .  c  o m
     // Now populate workbook the usual way.
     // ...
     OutputStream arqSaida = response.getOutputStream();
     workbook.write(arqSaida); // Write workbook to response.
     arqSaida.flush();
     arqSaida.close();*/
    //getServletContext().getRealPath("/")
    String tempoDecorrido = " 0";
    String url = getServletContext().getRealPath("/");
    String placa = request.getParameter("placa");
    //TimeZone timeZoneMundial = TimeZone.getTimeZone(ZoneId.ofOffset("UTC", ZoneOffset.UTC));
    Timestamp dataInicio = new Timestamp(Long.parseLong(request.getParameter("dataInicio")));
    Timestamp dataFim = new Timestamp(Long.parseLong(request.getParameter("dataFim")));
    //String timeZoneInterface = request.getParameter("timeZone");
    /*String timeZone = "Z";
    if(timeZoneInterface.equalsIgnoreCase("0")){
    timeZone = "Z";
    } else {
    timeZone = String.valueOf((-1) * (Integer.parseInt(timeZoneInterface) / 60));
    }  */
    TimeZone timeZonePadrao = TimeZone.getTimeZone(ZoneId.of("-3"));
    System.out.println("Time zone Cliente : " + timeZonePadrao);
    //System.out.println("timeZoneInterface: "+timeZoneInterface);
    //System.out.println("timeZone: "+timeZone);
    //String ign = request.getParameter("ign");
    ZzzPosPlacaVeiculoDAO zzzPosPlacaVeiculoDAO = new ZzzPosPlacaVeiculoDAO();
    ArrayList<TreeMap<String, String>> posicoes = zzzPosPlacaVeiculoDAO.buscarPosicoesPorIntervaloData(placa,
            dataInicio, dataFim);

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment; filename=Historico-" + placa + ".xlsx");

    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("Histrico - " + placa);
    int pictureIdx;
    try ( //add picture data to this workbook.
            InputStream is = new FileInputStream(url + "/imagens/logo.jpg")) {
        byte[] bytes = IOUtils.toByteArray(is);
        pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
    }

    CreationHelper helper = wb.getCreationHelper();

    // Create the drawing patriarch.  This is the top level container for all shapes. 
    Drawing drawing = sheet.createDrawingPatriarch();

    //add a picture shape
    ClientAnchor anchor = helper.createClientAnchor();
    //set top-left corner of the picture,
    //subsequent call of Picture#resize() will operate relative to it
    anchor.setCol1(1);
    anchor.setRow1(0);
    Picture pict = drawing.createPicture(anchor, pictureIdx);
    //auto-size picture relative to its top-left corner
    pict.resize(3, 3);
    //pict.resize();
    //sheet.setColumnWidth(0, 200);

    Font fonte = wb.createFont();
    fonte.setFontHeightInPoints((short) 24);
    fonte.setFontName("Arial");
    fonte.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fonte.setItalic(true);
    CellStyle estiloTitulo = wb.createCellStyle();
    estiloTitulo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    estiloTitulo.setFont(fonte);

    Font fonteCabecalho = wb.createFont();
    fonteCabecalho.setFontHeightInPoints((short) 14);
    fonteCabecalho.setFontName("Arial");
    fonteCabecalho.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    //fonteCabecalho.setItalic(true);
    CellStyle estiloCabecalho = wb.createCellStyle();
    estiloCabecalho.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    estiloCabecalho.setFont(fonteCabecalho);

    Font fonteTituloTabela = wb.createFont();
    //fonteTituloTabela.setFontHeightInPoints((short) 14);
    fonteTituloTabela.setFontName("Arial");
    fonteTituloTabela.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    CellStyle estilo = wb.createCellStyle();
    estilo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    estilo.setFont(fonteTituloTabela);

    CellStyle estiloCorpo = wb.createCellStyle();
    estiloCorpo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    //estiloCorpo.setFillBackgroundColor(HSSFColor.WHITE.index);
    //estiloCorpo.setFont(fonteTituloTabela);

    XSSFRow linha6 = (XSSFRow) sheet.createRow(6);
    XSSFCell cell046 = linha6.createCell(3);
    cell046.setCellValue("Relatrio de Posies");
    cell046.setCellStyle(estiloTitulo);
    //sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 3));

    XSSFRow linha7 = (XSSFRow) sheet.createRow(7);
    XSSFCell cell047 = linha7.createCell(3);
    cell047.setCellValue("Veculo : " + placa);
    cell047.setCellStyle(estiloCabecalho);
    //sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 3));

    XSSFRow linha8 = (XSSFRow) sheet.createRow(8);
    XSSFCell cell038 = linha8.createCell(3);
    //TimeZone.setDefault(timeZoneMundial);
    //Date dataHoraInicio0 = new Date(Long.parseLong(request.getParameter("dataInicio")));
    //TimeZone.setDefault(timeZoneCliente);
    //Date dataHoraInicio = new Date(dataHoraInicio0.getTime());
    SimpleDateFormat dataFormatadaCabecalho = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
    dataFormatadaCabecalho.setTimeZone(timeZonePadrao);
    cell038.setCellValue("Perodo: De: " + dataFormatadaCabecalho.format(dataInicio) + " at: "
            + dataFormatadaCabecalho.format(dataFim));
    cell038.setCellStyle(estiloCabecalho);
    //sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 3));

    sheet.setColumnWidth(0, 5000);
    sheet.setColumnWidth(1, 3000);
    sheet.setColumnWidth(2, 3500);
    //sheet.setColumnWidth(3, 4000);
    sheet.setColumnWidth(3, 30000);
    //sheet.setColumnWidth(4, 4000);
    //sheet.setColumnWidth(4, 30000);
    sheet.setColumnWidth(5, 3000);
    sheet.setColumnWidth(6, 3000);
    sheet.setColumnWidth(7, 3000);
    sheet.setColumnWidth(8, 3000);
    sheet.setColumnWidth(9, 3000);
    //sheet.setColumnWidth(10, 20000);        
    sheet.setColumnWidth(11, 3000);
    XSSFRow linha9 = (XSSFRow) sheet.createRow(10);
    XSSFCell cell0 = linha9.createCell(0);
    cell0.setCellValue("Data e hora");
    cell0.setCellStyle(estilo);
    XSSFCell cell1 = linha9.createCell(1);
    cell1.setCellValue("Velocidade");
    cell1.setCellStyle(estilo);
    XSSFCell cell2 = linha9.createCell(2);
    cell2.setCellValue("Ignio");
    cell2.setCellStyle(estilo);
    //XSSFCell cell3 = linha9.createCell(3);
    //cell3.setCellValue("Latitude");
    //cell3.setCellStyle(estilo);
    //XSSFCell cell4 = linha9.createCell(4);
    //cell4.setCellValue("Longitude");
    //cell4.setCellStyle(estilo);
    //XSSFCell cell5 = linha9.createCell(5);
    //cell5.setCellValue("Satlite");
    //cell5.setCellStyle(estilo);
    //XSSFCell cell6 = linha9.createCell(6);
    //cell6.setCellValue("GPS");
    //cell6.setCellStyle(estilo);
    //XSSFCell cell7 = linha9.createCell(7);
    //cell7.setCellValue("Entrada");
    //cell7.setCellStyle(estilo);
    //XSSFCell cell8 = linha9.createCell(8);
    //cell8.setCellValue("Sada");
    //cell8.setCellStyle(estilo);
    //XSSFCell cell9 = linha9.createCell(9);
    //cell9.setCellValue("Evento");
    //cell9.setCellStyle(estilo);
    XSSFCell cell10 = linha9.createCell(3);
    cell10.setCellValue("Endereo");
    cell10.setCellStyle(estilo);
    //sheet.addMergedRegion(new CellRangeAddress(11, 11, 4, 8));
    //XSSFCell cell11 = linha9.createCell(11);
    //cell11.setCellValue("Direo");
    //cell11.setCellStyle(estilo);
    int linha = 0;
    int j = 11;
    double latAnt = 0;
    double lonAnt = 0;
    double latAtual = 0;
    double lonAtual = 0;
    double distancia = 0;
    double distanciaTotal = 0;
    for (int i = 0; i < posicoes.size(); i++) {
        XSSFRow row = (XSSFRow) sheet.createRow(j);
        if (i == 0) {
            distancia = 0;
            //System.out.println("linha 00 - EXCEL");
        } else {
            //System.out.println("linha 01 - EXCEL");
            latAnt = Double.parseDouble(posicoes.get(i - 1).get("lat"));
            lonAnt = Double.parseDouble(posicoes.get(i - 1).get("lon"));
            latAtual = Double.parseDouble(posicoes.get(i).get("lat"));
            lonAtual = Double.parseDouble(posicoes.get(i).get("lon"));
            //System.out.println("linha 02 - PDF");
            if (latAnt == latAtual && lonAnt == lonAtual) {
                distancia = 0;
            } else {
                distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual);
                //System.out.println("linha 03 - PDF");
            }

        }
        distanciaTotal = distanciaTotal + distancia;
        /*if(i==0) {
         latAnt = Double.parseDouble(posicoes.get(i).get("lat"));
         lonAnt = Double.parseDouble(posicoes.get(i).get("lon"));
        } else{
            latAnt = Double.parseDouble(posicoes.get(i-1).get("lat"));
            lonAnt = Double.parseDouble(posicoes.get(i-1).get("lon"));
        }
        double latAtual = Double.parseDouble(posicoes.get(i).get("lat"));
        double lonAtual = Double.parseDouble(posicoes.get(i).get("lon"));
        double distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual);
        distanciaTotal = distanciaTotal + distancia;*/
        for (int col = 0; col < posicoes.get(linha).size(); col++) {
            XSSFCell cell = row.createCell(col);
            cell.setCellStyle(estiloCorpo);
            switch (col) {
            case 0:
                Date dataHora0 = new Date(Long.parseLong(posicoes.get(i).get("dataHora")));
                Date dataHora = new Date(dataHora0.getTime());
                SimpleDateFormat dataFormatada = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
                dataFormatada.setTimeZone(timeZonePadrao);
                cell.setCellValue(dataFormatada.format(dataHora));
                break;
            case 1:
                cell.setCellValue(posicoes.get(linha).get("vel"));
                break;
            case 2:
                cell.setCellValue(
                        posicoes.get(linha).get("ign").equalsIgnoreCase("True") ? "Ligada" : "Desligada");
                break;
            /*case 3:
                cell.setCellValue(posicoes.get(linha).get("lat"));
                break;
            case 4:
                cell.setCellValue(posicoes.get(linha).get("lon"));
                break;
            case 5:
                cell.setCellValue(posicoes.get(linha).get("sat"));
                break;
            case 6:
                cell.setCellValue(posicoes.get(linha).get("gps"));
                break;
            case 7:
                cell.setCellValue(posicoes.get(linha).get("entrada"));
                break;
            case 8:
                cell.setCellValue(posicoes.get(linha).get("saida"));
                break;
            case 9:
                cell.setCellValue(posicoes.get(linha).get("evento"));
                break;*/
            case 3:
                cell.setCellValue(posicoes.get(linha).get("endereco") == null ? "Sem endereo"
                        : posicoes.get(linha).get("endereco"));
                break;
            /*case 11:
                cell.setCellValue(posicoes.get(linha).get("direcao"));
                break;*/
            }

        }
        j = j + 1;
        linha = linha + 1;

    }
    tempoDecorrido = calculaDatas(Long.parseLong(posicoes.get(0).get("dataHora")),
            Long.parseLong(posicoes.get(posicoes.size() - 1).get("dataHora")));
    int index = 0;
    String kms = "0";
    String m = "";
    double metros = 0;
    if (distanciaTotal > 0) {
        BigDecimal decimalFormatado = new BigDecimal(distanciaTotal).setScale(2, RoundingMode.HALF_EVEN);
        index = String.valueOf(decimalFormatado).indexOf(".");
        kms = String.valueOf(decimalFormatado).substring(0, index);
        m = "0" + (String.valueOf(decimalFormatado).substring(index));
        metros = Double.parseDouble(m) * 1000;
    }
    //String formatted = NumberFormat.getFormat("000.00").format(metros);
    //System.out.println("Percorridos aproximadamente : "+kms+" KM e "+metros+" metros");
    XSSFRow linhaX = (XSSFRow) sheet.createRow(linha + 12);
    XSSFCell cellX = linhaX.createCell(0);
    sheet.addMergedRegion(new CellRangeAddress(linha + 12, linha + 12, 0, 3));
    cellX.setCellStyle(estilo);
    cellX.setCellValue(
            "Percorridos: " + kms + " KM e " + String.valueOf(metros) + " metros. Tempo: " + tempoDecorrido);
    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
    wb.write(outByteStream);

    byte[] outArray = outByteStream.toByteArray();
    OutputStream outStream = response.getOutputStream();
    outStream.write(outArray);
    outStream.flush();

}

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();//from w  w w  .j  a  v  a2 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.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   w w w.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);//w w w  .ja v  a 2 s .  co 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.github.cutstock.excel.model.SheetBuilder.java

License:Apache License

private void createLogoImage(ICellInfo title) {
    Workbook wb = sheet.getWorkbook();//from  w  ww.  java  2  s .  com
    int pictureIdx = wb.addPicture(title.getImage(), Workbook.PICTURE_TYPE_JPEG);
    CreationHelper helper = wb.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setCol1(title.getRect().getStartCol());
    anchor.setRow1(title.getRect().getStartRow());
    Picture pict = drawing.createPicture(anchor, pictureIdx);
    pict.resize();
}

From source file:com.griffinslogistics.document.excel.BDLGenerator.java

private static void insertPulsioImage(Workbook workbook, Sheet sheet, Pulsiodetails pulsiodetails) {

    Row imageRow = sheet.createRow(0);/*from w w  w .  j a  va  2  s .c  om*/
    imageRow.setHeightInPoints(55);

    byte[] imageBytes = pulsiodetails.getLogo();
    int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);
    CreationHelper helper = workbook.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();

    //set top-left corner for the image
    anchor.setCol1(7);
    anchor.setRow1(0);

    Picture pict = drawing.createPicture(anchor, pictureIdx);
    pict.resize();
}

From source file:com.griffinslogistics.document.excel.CMRGenerator.java

private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow,
        Pulsiodetails pulsiodetails) {//from w  w w. java2s . c  om
    currentRow++;

    String mergeString;

    for (int i = currentRow; i < currentRow + 2; i++) {
        Row row = sheet.createRow(i);
        mergeString = String.format("$B$%s:$I$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

        for (int j = 1; j < 9; j++) {
            row.createCell(j)
                    .setCellStyle(styles.get(i == currentRow ? LABEL_MIDDLE_STYLE : LABEL_BOTTOM_STYLE));
        }
    }

    for (int i = currentRow + 2; i < currentRow + 16; i++) {
        Row row = sheet.createRow(i);
        mergeString = String.format("$D$%s:$I$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));
        for (int j = 1; j < 9; j++) {
            row.createCell(j).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
        }
    }

    currentRow++;
    Row row45 = sheet.getRow(currentRow);
    row45.setHeightInPoints(30);
    row45.getCell(1).setCellValue(LABEL_POINT_20);

    currentRow++;
    Row row46 = sheet.getRow(currentRow);

    Cell establishedInCell = row46.getCell(1);
    establishedInCell.setCellValue(LABEL_ESTABLISHED_IN);

    Cell establishedOnCell = row46.getCell(2);
    establishedOnCell.setCellValue(LABEL_ESTABLISHED_ON);

    Cell goodsDeliveredCell = row46.getCell(3);
    goodsDeliveredCell.setCellValue(LABEL_GOODS_RECEIVED);

    currentRow++;
    Row row47 = sheet.getRow(currentRow);
    row47.getCell(3).setCellValue(LABEL_TIME_OF_ARRIVAL);

    currentRow++;
    Row row48 = sheet.getRow(currentRow);
    Cell cityCell = row48.getCell(1);
    cityCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    cityCell.setCellValue("Sofia, Bulgaria");

    Cell dateCell = row48.getCell(2);
    dateCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    dateCell.setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(new Date()));

    currentRow += 2;
    sheet.getRow(currentRow).getCell(3).setCellValue(LABEL_PLACE_20);

    currentRow++;
    Row row51 = sheet.getRow(currentRow);

    currentRow++;

    row51.getCell(1).setCellValue(TWENTY_TWO);
    row51.getCell(2).setCellValue(TWENTY_THREE);

    // Insert signature picture
    Workbook workbook = sheet.getWorkbook();
    byte[] imageBytes = pulsiodetails.getSignature();
    int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
    CreationHelper helper = workbook.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();

    //set top-left corner for the image
    anchor.setCol1(1);
    anchor.setRow1(currentRow);

    XSSFPicture pict = (XSSFPicture) drawing.createPicture(anchor, pictureIdx);
    pict.resize(1.01, 5);

    currentRow += 4;
    Row row56 = sheet.getRow(currentRow);
    row56.getCell(3).setCellValue(LABEL_SIGNATURE_STAMP);

    currentRow += 2;
    Row row58 = sheet.getRow(currentRow);
    Cell signatureLabelCell1 = row58.getCell(1);
    signatureLabelCell1.setCellValue(LABEL_SENDER_SIGNATURE_BULGARIAN);

    Cell carrierSignatureCell = row58.getCell(2);
    carrierSignatureCell.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN);

    Cell receiverSignatureCell = row58.getCell(3);
    receiverSignatureCell.setCellValue(LABEL_RECEIVER_SIGNATURE_BULGARIAN);

    currentRow++;
    Row row59 = sheet.getRow(currentRow);
    Cell signatureLabelCell2 = row59.getCell(1);
    signatureLabelCell2.setCellValue(LABEL_SENDER_SIGNATURE_ENGLISH);

    Cell carrierSignatureCel2 = row59.getCell(2);
    carrierSignatureCel2.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN);

    Cell receiverSignatureCel2 = row59.getCell(3);
    receiverSignatureCel2.setCellValue(LABEL_RECEIVER_SIGNATURE_ENGLISH);

    currentRow++;
    Row row60 = sheet.createRow(currentRow);
    mergeString = String.format("$B$%s:$I$%s", currentRow + 1, currentRow + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    for (int i = 1; i < 9; i++) {
        row60.createCell(i).setCellStyle(styles.get(LABEL_WHOLE_STYLE));
    }

    Cell additionalSpaceCell = row60.getCell(1);
    additionalSpaceCell.setCellValue(LABEL_ADDITIONAL_SPACE);

    return currentRow;
}

From source file:com.griffinslogistics.excel.BDLGenerator.java

private static void insertPulsioImage(Workbook workbook, Sheet sheet, Pulsiodetails pulsiodetails) {

    Row imageRow = sheet.createRow(0);/*from w w w . j ava 2s.  co m*/
    imageRow.setHeightInPoints(55);

    byte[] imageBytes = pulsiodetails.getLogo();
    int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);
    CreationHelper helper = workbook.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();

    //set top-left corner for the image
    anchor.setCol1(8);
    anchor.setRow1(0);

    Picture pict = drawing.createPicture(anchor, pictureIdx);
    pict.resize();
}

From source file:com.griffinslogistics.excel.CMRGenerator.java

private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow,
        Pulsiodetails pulsiodetails) {// ww w  . j  a v  a  2 s .  co  m
    currentRow++;

    String mergeString;

    for (int i = currentRow; i < currentRow + 2; i++) {
        Row row = sheet.createRow(i);
        mergeString = String.format("$B$%s:$I$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

        for (int j = 1; j < 9; j++) {
            row.createCell(j)
                    .setCellStyle(styles.get(i == currentRow ? LABEL_MIDDLE_STYLE : LABEL_BOTTOM_STYLE));
        }
    }

    for (int i = currentRow + 2; i < currentRow + 16; i++) {
        Row row = sheet.createRow(i);
        mergeString = String.format("$D$%s:$I$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));
        for (int j = 1; j < 9; j++) {
            row.createCell(j).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
        }
    }

    currentRow++;
    Row row45 = sheet.getRow(currentRow);
    row45.setHeightInPoints(30);
    row45.getCell(1).setCellValue(LABEL_POINT_20);

    currentRow++;
    Row row46 = sheet.getRow(currentRow);

    Cell establishedInCell = row46.getCell(1);
    establishedInCell.setCellValue(LABEL_ESTABLISHED_IN);

    Cell establishedOnCell = row46.getCell(2);
    establishedOnCell.setCellValue(LABEL_ESTABLISHED_ON);

    Cell goodsDeliveredCell = row46.getCell(3);
    goodsDeliveredCell.setCellValue(LABEL_GOODS_RECEIVED);

    currentRow++;
    Row row47 = sheet.getRow(currentRow);
    row47.getCell(3).setCellValue(LABEL_TIME_OF_ARRIVAL);

    currentRow++;
    Row row48 = sheet.getRow(currentRow);
    Cell cityCell = row48.getCell(1);
    cityCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    cityCell.setCellValue("Sofia, Bulgaria");

    Cell dateCell = row48.getCell(2);
    dateCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    dateCell.setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(new Date()));

    currentRow += 2;
    sheet.getRow(currentRow).getCell(3).setCellValue(LABEL_PLACE_20);

    currentRow++;
    Row row51 = sheet.getRow(currentRow);

    currentRow++;

    row51.getCell(1).setCellValue(TWENTY_TWO);
    row51.getCell(2).setCellValue(TWENTY_THREE);

    // Insert signature picture
    Workbook workbook = sheet.getWorkbook();
    byte[] imageBytes = pulsiodetails.getSignature();
    int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);
    CreationHelper helper = workbook.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();

    //set top-left corner for the image
    anchor.setCol1(1);
    anchor.setRow1(currentRow);

    Picture pict = drawing.createPicture(anchor, pictureIdx);
    pict.resize();

    currentRow += 4;
    Row row56 = sheet.getRow(currentRow);
    row56.getCell(3).setCellValue(LABEL_SIGNATURE_STAMP);

    currentRow += 2;
    Row row58 = sheet.getRow(currentRow);
    Cell signatureLabelCell1 = row58.getCell(1);
    signatureLabelCell1.setCellValue(LABEL_SENDER_SIGNATURE_BULGARIAN);

    Cell carrierSignatureCell = row58.getCell(2);
    carrierSignatureCell.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN);

    Cell receiverSignatureCell = row58.getCell(3);
    receiverSignatureCell.setCellValue(LABEL_RECEIVER_SIGNATURE_BULGARIAN);

    currentRow++;
    Row row59 = sheet.getRow(currentRow);
    Cell signatureLabelCell2 = row59.getCell(1);
    signatureLabelCell2.setCellValue(LABEL_SENDER_SIGNATURE_ENGLISH);

    Cell carrierSignatureCel2 = row59.getCell(2);
    carrierSignatureCel2.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN);

    Cell receiverSignatureCel2 = row59.getCell(3);
    receiverSignatureCel2.setCellValue(LABEL_RECEIVER_SIGNATURE_ENGLISH);

    currentRow++;
    Row row60 = sheet.createRow(currentRow);
    mergeString = String.format("$B$%s:$I$%s", currentRow + 1, currentRow + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    for (int i = 1; i < 9; i++) {
        row60.createCell(i).setCellStyle(styles.get(LABEL_WHOLE_STYLE));
    }

    Cell additionalSpaceCell = row60.getCell(1);
    additionalSpaceCell.setCellValue(LABEL_ADDITIONAL_SPACE);

    return currentRow;
}