List of usage examples for org.apache.poi.ss.usermodel Sheet createDrawingPatriarch
Drawing<?> createDrawingPatriarch();
From source file:cn.bzvs.excel.export.ExcelExportServer.java
License:Apache License
protected void insertDataToSheet(Workbook workbook, ExportParams entity, List<ExcelExportEntity> entityList, Collection<?> dataSet, Sheet sheet) { try {/*from ww w .j av a 2 s . c o m*/ dataHanlder = entity.getDataHanlder(); if (dataHanlder != null && dataHanlder.getNeedHandlerFields() != null) { needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields()); } // ? setExcelExportStyler( (IExcelExportStyler) entity.getStyle().getConstructor(Workbook.class).newInstance(workbook)); Drawing patriarch = sheet.createDrawingPatriarch(); List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>(); if (entity.isAddIndex()) { excelParams.add(indexExcelEntity(entity)); } excelParams.addAll(entityList); sortAllParams(excelParams); int index = entity.isCreateHeadRows() ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0; int titleHeight = index; setCellWith(excelParams, sheet); short rowHeight = getRowHeight(excelParams); setCurrentIndex(1); Iterator<?> its = dataSet.iterator(); List<Object> tempList = new ArrayList<Object>(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight); tempList.add(t); if (index >= MAX_NUM) break; } if (entity.getFreezeCol() != 0) { sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0); } mergeCells(sheet, excelParams, titleHeight); its = dataSet.iterator(); for (int i = 0, le = tempList.size(); i < le; i++) { its.next(); its.remove(); } // ?list Sheet if (dataSet.size() > 0) { createSheetForMap(workbook, entity, entityList, dataSet); } else { // ?? addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet); } } catch (Exception e) { e.getMessage(); throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause()); } }
From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java
License:Apache License
/** * Sheet ?,?? ,??/*from www . j av a 2 s .com*/ * * @param sheet * @param pojoClass * @param dataSet * @param workbook */ private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception { if (workbook instanceof XSSFWorkbook) { super.type = ExcelType.XSSF; } // ?? Map<String, Integer> titlemap = getTitleMap(sheet); Drawing patriarch = sheet.createDrawingPatriarch(); // Field[] fileds = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); String targetId = null; if (etarget != null) { targetId = etarget.value(); } // ?? List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>(); getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null); // ?? sortAndFilterExportField(excelParams, titlemap); short rowHeight = getRowHeight(excelParams); int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index; //?,? sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true); if (excelParams.size() == 0) { return; } Iterator<?> its = dataSet.iterator(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight); } // ?? mergeCells(sheet, excelParams, titleHeight); }
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);/*ww w . j a va 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 ww .j a va2 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 ww w .j a v a2 s. com 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.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();//www . j av 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.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);//w w w . j a v a 2s . 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);// w w w. j a v a 2 s . 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(7); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(); }
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 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(8); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(); }
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);/*ww w. j a v a2s .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; }