List of usage examples for org.apache.poi.ss.usermodel Workbook addPicture
int addPicture(byte[] pictureData, int format);
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(); // ...// w w w . j a va 2 s .c om // 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.automaster.autoview.server.servlet.TesteImagemPOI.java
public static void main(String[] args) throws FileNotFoundException, IOException { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); byte data[] = new byte[8000]; try {//from w w w .j a va 2 s . c o m new DataInputStream(new FileInputStream( "D:\\Users\\Adriano\\Documents\\NetBeansProjects\\JRGWT\\web\\imagens\\logo.jpg")).read(data); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } int index = wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG); HSSFClientAnchor ancora = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 5, (short) 10, 10); ancora.setAnchorType(2); sheet.createDrawingPatriarch().createPicture(ancora, index); //Write the Excel file FileOutputStream fileOut = null; fileOut = new FileOutputStream("C://myFile.xls"); wb.write(fileOut); fileOut.close(); }
From source file:com.github.cutstock.excel.model.SheetBuilder.java
License:Apache License
private void createLogoImage(ICellInfo title) { Workbook wb = sheet.getWorkbook(); 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 ww w. j a v a 2s . c om }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.OnlineJobsReportForIPTranslatorGenerator.java
License:Apache License
private void addTotalsPerLang(Workbook p_workbook, Sheet p_sheet, final int p_sheetCategory, IntHolder p_row) throws Exception { Font subTotalFont = p_workbook.createFont(); subTotalFont.setBoldweight(Font.BOLDWEIGHT_BOLD); subTotalFont.setColor(IndexedColors.BLACK.getIndex()); subTotalFont.setUnderline(Font.U_NONE); subTotalFont.setFontName("Arial"); subTotalFont.setFontHeightInPoints((short) 10); CellStyle subTotalStyle = p_workbook.createCellStyle(); subTotalStyle.setFont(subTotalFont); String title = m_bundle.getString("lb_total_cost_per_lang"); int row = p_row.getValue() + 4; // skip a row ArrayList<String> locales = new ArrayList<String>(totalCost.keySet()); SortUtil.sort(locales);/*from w w w. j a v a 2 s . c o m*/ int col = m_data.getSumStartCol(); Row theRow = getRow(p_sheet, row); Cell cell_Title = getCell(theRow, col - 3); cell_Title.setCellValue(title); cell_Title.setCellStyle(subTotalStyle); File imgFile = File.createTempFile("GSJobChart", ".png"); JfreeCharUtil.drawPieChart2D("", totalCostDate, imgFile); Drawing patriarch = p_sheet.createDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 15, row - 1, 22, row + 24); ByteArrayOutputStream img_bytes = new ByteArrayOutputStream(); InputStream is = imgFile.toURI().toURL().openStream(); int b; while ((b = is.read()) != -1) { img_bytes.write(b); } is.close(); int i = p_workbook.addPicture(img_bytes.toByteArray(), SXSSFWorkbook.PICTURE_TYPE_PNG); patriarch.createPicture(anchor, i); for (String locale : locales) { List<Integer> indexs = totalCost.get(locale); StringBuffer values = new StringBuffer(); for (Integer index : indexs) { if (values.length() == 0) { values.append("SUM("); } else { values.append("+"); } values.append(totalCol).append(index); } values.append(")"); Row perRow = getRow(p_sheet, row); Cell cell_Locale = getCell(perRow, col - 1); cell_Locale.setCellValue(locale); cell_Locale.setCellStyle(getContentStyle(p_workbook)); Cell cell_Total = getCell(perRow, col); cell_Total.setCellFormula(values.toString()); cell_Total.setCellStyle(getMoneyStyle(p_workbook)); row++; } // Reset total column number for every sheet. totalCol = null; }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertPulsioImage(Workbook workbook, Sheet sheet, Pulsiodetails pulsiodetails) { Row imageRow = sheet.createRow(0);//ww w. j a va2 s . com 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 .ja v a 2s . c o 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_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 av a2s. c o 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) {/* w w w. j ava 2s . com*/ 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; }
From source file:com.ideaspymes.proyecttemplate.stock.web.ProductoConsultaBean.java
@Override public Workbook getWorkBook() { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("My Sample Excel"); List<CatalogoProductos> lista = (List<CatalogoProductos>) getDetalles(); sheet.setDefaultRowHeight((short) (sheet.getDefaultRowHeight() * new Short("6"))); org.apache.poi.ss.usermodel.Font fontTitulo = wb.createFont(); fontTitulo.setFontHeightInPoints((short) 12); fontTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); org.apache.poi.ss.usermodel.Font fontTituloPricipal = wb.createFont(); fontTituloPricipal.setFontHeightInPoints((short) 22); fontTituloPricipal.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); DataFormat format = wb.createDataFormat(); CellStyle styleTituloPrincipal = wb.createCellStyle(); styleTituloPrincipal.setFont(fontTituloPricipal); styleTituloPrincipal.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleTituloPrincipal.setAlignment(CellStyle.ALIGN_CENTER); CellStyle styleTitulo = wb.createCellStyle(); styleTitulo.setFont(fontTitulo);//from www . j a va2 s. c o m styleTitulo.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleTitulo.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); styleTitulo.setFillPattern(CellStyle.SOLID_FOREGROUND); styleTitulo.setWrapText(true); CellStyle styleNumero = wb.createCellStyle(); styleNumero.setDataFormat(format.getFormat("#,##0")); styleNumero.setWrapText(true); styleNumero.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleNumero.setAlignment(CellStyle.ALIGN_CENTER); CellStyle styleFecha = wb.createCellStyle(); styleFecha.setDataFormat(format.getFormat("dd/MM/yyyy")); styleFecha.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleFecha.setAlignment(CellStyle.ALIGN_CENTER); CellStyle style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setWrapText(true); CellStyle styleCenter = wb.createCellStyle(); styleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleCenter.setAlignment(CellStyle.ALIGN_CENTER); styleCenter.setWrapText(true); Row rowTitle = sheet.createRow(0); Cell cellTitle = rowTitle.createCell(1); cellTitle.setCellStyle(styleTituloPrincipal); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 11 //last column (0-based) )); cellTitle.setCellValue("Listado de Activos"); int i = 2; Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell1 = row0.createCell(1); cell1.setCellValue("Foto"); cell1.setCellStyle(styleTitulo); Cell cellFecha = row0.createCell(3); cellFecha.setCellValue("Fecha Ingreso"); cellFecha.setCellStyle(styleTitulo); Cell cellFechaCarga = row0.createCell(4); cellFechaCarga.setCellValue("Fecha Carga"); cellFechaCarga.setCellStyle(styleTitulo); Cell cell3 = row0.createCell(5); cell3.setCellValue("Nombre"); cell3.setCellStyle(styleTitulo); Cell cell4 = row0.createCell(6); cell4.setCellValue("Cdigo"); cell4.setCellStyle(styleTitulo); Cell cell5 = row0.createCell(7); cell5.setCellValue("Descripcin"); cell5.setCellStyle(styleTitulo); Cell cell6 = row0.createCell(8); cell6.setCellValue("Es Regalo?"); cell6.setCellStyle(styleTitulo); Cell cell7 = row0.createCell(9); cell7.setCellValue("Familia"); cell7.setCellStyle(styleTitulo); Cell cell8 = row0.createCell(10); cell8.setCellValue("Ubicaciones"); cell8.setCellStyle(styleTitulo); Cell cell9 = row0.createCell(11); cell9.setCellValue("Stock"); cell9.setCellStyle(styleTitulo); for (CatalogoProductos cp : lista) { int indexFila = i + 1; if (cp.getImagen() != null) { int pictureIdx = wb.addPicture(cp.getImagen(), Workbook.PICTURE_TYPE_PNG); CreationHelper helper = wb.getCreationHelper(); //Creates the top-level drawing patriarch. Drawing drawing = sheet.createDrawingPatriarch(); //Create an anchor that is attached to the worksheet ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(1); anchor.setRow1(indexFila); //Creates a picture Picture pict = drawing.createPicture(anchor, pictureIdx); //Reset the image to the original size pict.resize(0.4); } Row row1 = sheet.createRow(indexFila); row1.setHeightInPoints(80f); Cell cellColFecha = row1.createCell(3); if (cp.getFecha() != null) { cellColFecha.setCellValue(cp.getFecha()); cellColFecha.setCellStyle(styleFecha); } else { cellColFecha.setCellValue(""); cellColFecha.setCellStyle(styleFecha); } Cell cellColFechaCarga = row1.createCell(4); if (cp.getFechaCarga() != null) { cellColFechaCarga.setCellValue(cp.getFechaCarga()); cellColFechaCarga.setCellStyle(styleFecha); } else { cellColFechaCarga.setCellValue(""); cellColFechaCarga.setCellStyle(styleFecha); } Cell cellCol1 = row1.createCell(5); cellCol1.setCellValue(cp.getProducto()); cellCol1.setCellStyle(style); Cell cellCol2 = row1.createCell(6); cellCol2.setCellValue(cp.getCodigo()); cellCol2.setCellStyle(styleNumero); Cell cellCol3 = row1.createCell(7); cellCol3.setCellValue(cp.getDescripcion()); cellCol3.setCellStyle(style); Cell cellCol4 = row1.createCell(8); cellCol4.setCellValue(cp.isEsRegalo() ? "SI" : "NO"); cellCol4.setCellStyle(styleCenter); Cell cellCol5 = row1.createCell(9); cellCol5.setCellValue(cp.getFamilia()); cellCol5.setCellStyle(style); Cell cellCol6 = row1.createCell(10); cellCol6.setCellValue(cp.getUbicaciones()); cellCol6.setCellStyle(style); Cell cellCol7 = row1.createCell(11); cellCol7.setCellValue(cp.getStock()); cellCol7.setCellStyle(styleNumero); i++; } sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 0); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 4000); sheet.setColumnWidth(5, 10000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 10000); sheet.setColumnWidth(8, 3500); sheet.setColumnWidth(9, 6000); sheet.setColumnWidth(10, 10000); sheet.setColumnWidth(11, 2000); return wb; }
From source file:Compras.altaCompras.java
private void b_muestraActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_b_muestraActionPerformed // TODO add your handling code here: javax.swing.JFileChooser jF1= new javax.swing.JFileChooser(); jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); String ruta = null;// ww w.j av a2s .com if(jF1.showSaveDialog(null)==jF1.APPROVE_OPTION) { ruta = jF1.getSelectedFile().getAbsolutePath(); File archivoXLS = new File(ruta+".xls"); try { if(archivoXLS.exists()) archivoXLS.delete(); archivoXLS.createNewFile(); Workbook libro = new HSSFWorkbook(); FileOutputStream archivo = new FileOutputStream(archivoXLS); Sheet hoja = libro.createSheet("Muestras"); org.apache.poi.ss.usermodel.Font font10 = libro.createFont(); font10.setFontHeightInPoints((short)10); font10.setFontName("Arial"); font10.setItalic(false); font10.setBold(false); font10.setColor(new HSSFColor.YELLOW().getIndex()); CellStyle titulo = libro.createCellStyle(); CellStyle contenido = libro.createCellStyle(); CellStyle firma = libro.createCellStyle(); CellStyle costado = libro.createCellStyle(); CellStyle derecha = libro.createCellStyle(); CellStyle derecha_borde = libro.createCellStyle(); titulo.setFont(font10); titulo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titulo.setFillBackgroundColor(new HSSFColor.GREEN().getIndex()); titulo.setAlignment(CellStyle.ALIGN_CENTER); contenido.setBorderBottom(HSSFCellStyle.BORDER_THIN); contenido.setBorderTop(HSSFCellStyle.BORDER_THIN); contenido.setBorderRight(HSSFCellStyle.BORDER_THIN); contenido.setBorderLeft(HSSFCellStyle.BORDER_THIN); derecha_borde.setBorderBottom(HSSFCellStyle.BORDER_THIN); derecha_borde.setBorderTop(HSSFCellStyle.BORDER_THIN); derecha_borde.setBorderRight(HSSFCellStyle.BORDER_THIN); derecha_borde.setBorderLeft(HSSFCellStyle.BORDER_THIN); derecha_borde.setAlignment(CellStyle.ALIGN_RIGHT); derecha.setAlignment(CellStyle.ALIGN_RIGHT); firma.setBorderTop(HSSFCellStyle.BORDER_THIN); firma.setAlignment(CellStyle.ALIGN_CENTER); //costado.setBorderLeft(HSSFCellStyle.BORDER_THIN); hoja.setColumnWidth(0, 3000); hoja.setColumnWidth(2, 3000); hoja.setColumnWidth(3, 8000); hoja.setColumnWidth(4, 5000); try { InputStream is = new FileInputStream("imagenes/grande300115.jpg"); byte[] bytes = IOUtils.toByteArray(is); int pictureIdx = libro.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); is.close(); CreationHelper helper = libro.getCreationHelper(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(0); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(); }catch(Exception e){e.printStackTrace();} Row r7 = hoja.createRow(7); r7.createCell(0).setCellValue("ORDEN:"); r7.createCell(1).setCellValue(String.valueOf(ord.getIdOrden())); r7.createCell(2).setCellValue("Hrs. Entrega:"); r7.createCell(3).setCellValue(""); hoja.addMergedRegion(new CellRangeAddress(7,7,4,7)); r7.createCell(4).setCellValue("ORDEN PARA SURTIR MUESTRAS"); r7.getCell(4).setCellStyle(derecha); Date fecha = new Date(); DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");//YYYY-MM-DD HH:MM:SS String valor=dateFormat.format(fecha); Row r8 = hoja.createRow(8); r8.createCell(0).setCellValue("Supervisor:"); hoja.addMergedRegion(new CellRangeAddress(8,8,1,3)); r8.createCell(1).setCellValue(""); r8.createCell(4).setCellValue("F. Elaboracin:"); r8.createCell(5).setCellValue(valor); Row r9 = hoja.createRow(9); r9.createCell(0).setCellValue("Comprador:"); hoja.addMergedRegion(new CellRangeAddress(9,9,1,3)); r9.createCell(1).setCellValue(""); r9.createCell(4).setCellValue("F. Entrega:"); r9.createCell(5).setCellValue(""); Row r10 = hoja.createRow(10); r10.createCell(0).setCellValue("Cantidad"); r10.getCell(0).setCellStyle(titulo); hoja.addMergedRegion(new CellRangeAddress(10,10,1,7)); r10.createCell(1).setCellValue("Descripcin"); r10.getCell(1).setCellStyle(titulo); int ren=11; for(int r=0;r<(t_datos.getRowCount());r++) { if((boolean)t_titulos.getValueAt(r, 3)==true) { Row fila = hoja.createRow(ren); Cell celda = fila.createCell(0); celda.setCellStyle(derecha_borde); Cell celda1 = fila.createCell(1); celda1.setCellStyle(contenido); fila.createCell(2).setCellStyle(contenido); fila.createCell(3).setCellStyle(contenido); fila.createCell(4).setCellStyle(contenido); fila.createCell(5).setCellStyle(contenido); fila.createCell(6).setCellStyle(contenido); fila.createCell(7).setCellStyle(contenido); //Cell celda8 = fila.createCell(8); //celda8.setCellStyle(costado); try{ celda.setCellValue(t_datos.getValueAt(r, 14).toString()); hoja.addMergedRegion(new CellRangeAddress(ren,ren,1,7)); celda1.setCellValue(t_titulos.getValueAt(r, 2).toString()); //celda8.setCellValue(""); }catch(Exception e){ celda.setCellValue(""); } ren++; } } Row rx = hoja.createRow(ren+5); hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,0,2)); rx.createCell(0).setCellValue("Recibe Muestras"); rx.getCell(0).setCellStyle(firma); rx.createCell(1).setCellStyle(firma); rx.createCell(2).setCellStyle(firma); hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,5,7)); rx.createCell(5).setCellValue("Entrega Muestras"); rx.getCell(5).setCellStyle(firma); rx.createCell(6).setCellStyle(firma); rx.createCell(7).setCellStyle(firma); libro.write(archivo); archivo.close(); Desktop.getDesktop().open(archivoXLS); }catch(Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte si el archivo esta abierto"); } } }