List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getRowNum
@Override public int getRowNum()
From source file:com.axelor.apps.admin.service.ViewDocExportService.java
License:Open Source License
private Integer addDoc(XSSFRow row, String[] values, int count, XSSFCellStyle cellStyle, Integer rowCount) { String name = values[4];/*from ww w .ja v a 2s.c o m*/ if (name == null) { name = values[5]; } String key = null; if (row.getRowNum() == 0) { key = sheet.getSheetName(); } else { key = values[1] + "," + values[2] + "," + values[3] + "," + name; } if (docMap.containsKey(key) && oldSheet != null) { writeCell(row, docMap.get(key), count, cellStyle); } if (commentMap.containsKey(key) && oldSheet != null) { for (Integer rowIndex : commentMap.get(key)) { rowCount = writeRow(rowIndex, style, 0, rowCount); } } return rowCount; }
From source file:com.pe.nisira.movil.view.action.MultitablaAction.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;/* ww w. java 2 s .c o m*/ StreamedContent arch = null; try { String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION"; File ruta = new File(folder); if (!ruta.isDirectory()) { ruta.mkdirs(); } String rutaArchivo = folder + "\\FI_MULTITABLA.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_MULTITABLA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 4; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 4); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Indicar si es es Padre (Usar SI o NO)."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Es Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Opcional \n - Escribir la Abreviatura del campo del cual depende este."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Abreviatura Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Descripcion de la multitabla"); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("DESCRIPCION"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Abreviatura de la multitabla."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("ABREVIATURA"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); libro.write(file); file.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_MULTITABLA.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }
From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;// www . j a v a 2 s.c o m StreamedContent arch = null; try { String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION"; File ruta = new File(folder); if (!ruta.isDirectory()) { ruta.mkdirs(); } String rutaArchivo = folder + "\\FI_REGISTROPALE.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file2 = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_PALETA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); XSSFSheet hoja2 = libro.createSheet("IMPORTAR_DET_PALETA"); CreationHelper factory2 = libro.getCreationHelper(); hoja2 = libro.getSheetAt(1); XSSFCellStyle style2 = libro.createCellStyle(); Font font2 = libro.createFont(); Font font12 = libro.createFont(); Drawing drawing2 = hoja2.createDrawingPatriarch(); ClientAnchor anchor12 = factory2.createClientAnchor(); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); font2.setFontHeightInPoints((short) 8); font12.setFontHeightInPoints((short) 8); font12.setFontName("Arial"); font2.setFontName("Arial"); style2.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); style2.setAlignment(CellStyle.VERTICAL_CENTER); style2.setAlignment(CellStyle.ALIGN_CENTER); style2.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 29; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 5); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString("ADM:\nCampo Obligatorio \n - IDEMPRESA"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMPRESA"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - IDREGISTROPALETA. \n Debe de tener (15) caracteres"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDREGISTROPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del emisor. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMISOR"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - id de la operacion.\n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDOPERACION"); celda.setCellComment(comment); } else if (f == 0 && c == 4) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Operacion.\n -Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NUMOPERACION"); celda.setCellComment(comment); } else if (f == 0 && c == 5) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del motivo de Paleta.\n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDMOTIVOPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 6) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id documento. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDDOCUMENTO"); celda.setCellComment(comment); } else if (f == 0 && c == 7) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Serie del Documento. \n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("SERIE"); celda.setCellComment(comment); } else if (f == 0 && c == 8) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Documento.\n -Debe tener 7 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NUMERO"); celda.setCellComment(comment); } else if (f == 0 && c == 9) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("FECHA"); celda.setCellComment(comment); } else if (f == 0 && c == 10) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Perido del ao \n - fromato YYYYMM."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("PERIODO"); celda.setCellComment(comment); } else if (f == 0 && c == 11) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del estado \n -Debe tener 2 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDESTADO"); celda.setCellComment(comment); } else if (f == 0 && c == 12) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del cliente o proveedor \n -Debe tener 11 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCLIEPROV"); celda.setCellComment(comment); } else if (f == 0 && c == 13) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Paleta \n -Debe tener 20 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NROPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 14) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id de envase \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDENVASE"); celda.setCellComment(comment); } else if (f == 0 && c == 15) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id la sucursal \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDSUCURSAL"); celda.setCellComment(comment); } else if (f == 0 && c == 16) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del almacen. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDALMACEN"); celda.setCellComment(comment); } else if (f == 0 && c == 17) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del embalaje. \n -Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMBALAJE"); celda.setCellComment(comment); } else if (f == 0 && c == 18) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id de cultivo. \n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCULTIVO"); celda.setCellComment(comment); } else if (f == 0 && c == 19) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - id de Variadd. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDVARIEDAD"); celda.setCellComment(comment); } else if (f == 0 && c == 20) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Observaciones sobre la paleta \n -como maximo 240 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("OBSERVACIONES"); celda.setCellComment(comment); } else if (f == 0 && c == 21) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n -Nombre de la venta \n como maximo 50 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("VENTANA"); celda.setCellComment(comment); } else if (f == 0 && c == 22) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Cantidad. \n - 15 numeros y 2 decimales como maximo."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("CANTIDAD"); celda.setCellComment(comment); } else if (f == 0 && c == 23) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Estado de la paleta \n- 1 = cerrado, 0 = Abierto."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("CERRADO"); celda.setCellComment(comment); } else if (f == 0 && c == 24) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Sincroniza \n - N = no , S = si."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("SINCRONIZA"); celda.setCellComment(comment); } else if (f == 0 && c == 25) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("FECHACREACION"); celda.setCellComment(comment); } else if (f == 0 && c == 26) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Manural\n Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NROMANUAL"); celda.setCellComment(comment); } else if (f == 0 && c == 27) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - idcliepro-destino\n debe tener 11 caracteres"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCLIEPROV_DESTINO"); celda.setCellComment(comment); } else if (f == 0 && c == 28) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Tipo de paleta\n debe tener 1 caraccter.."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("TIPO"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); hoja.autoSizeColumn((short) 3); hoja.autoSizeColumn((short) 4); hoja.autoSizeColumn((short) 5); hoja.autoSizeColumn((short) 6); hoja.autoSizeColumn((short) 7); hoja.autoSizeColumn((short) 8); hoja.autoSizeColumn((short) 9); hoja.autoSizeColumn((short) 10); hoja.autoSizeColumn((short) 11); hoja.autoSizeColumn((short) 12); hoja.autoSizeColumn((short) 13); hoja.autoSizeColumn((short) 14); hoja.autoSizeColumn((short) 15); hoja.autoSizeColumn((short) 16); hoja.autoSizeColumn((short) 17); hoja.autoSizeColumn((short) 18); hoja.autoSizeColumn((short) 19); hoja.autoSizeColumn((short) 20); hoja.autoSizeColumn((short) 21); hoja.autoSizeColumn((short) 22); hoja.autoSizeColumn((short) 23); hoja.autoSizeColumn((short) 24); hoja.autoSizeColumn((short) 25); hoja.autoSizeColumn((short) 26); hoja.autoSizeColumn((short) 27); hoja.autoSizeColumn((short) 28); for (int f = 0; f < 2; f++) { XSSFRow fila2 = hoja2.createRow(f); if (f == 0) { for (int c = 0; c < 15; c++) { XSSFCell celda2 = fila2.createCell(c); anchor12.setCol1(celda2.getColumnIndex()); anchor12.setCol2(celda2.getColumnIndex() + 8); anchor12.setRow1(fila2.getRowNum()); anchor12.setRow2(fila2.getRowNum() + 8); Comment comment2 = drawing2.createCellComment(anchor12); RichTextString str; switch (c) { case 0: celda2.setCellStyle(style2); str = factory2.createRichTextString( "ADM:\nCampo Obligatorio \n - El Cdigo debe de ser nico."); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDEMPRESA"); celda2.setCellComment(comment2); break; case 1: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDREGISTROPALETA"); celda2.setCellComment(comment2); break; case 2: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("ITEM"); celda2.setCellComment(comment2); break; case 3: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCLIEPROV"); celda2.setCellComment(comment2); break; case 4: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTE"); celda2.setCellComment(comment2); break; case 5: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCONDICION"); celda2.setCellComment(comment2); break; case 6: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDTALLA"); celda2.setCellComment(comment2); break; case 7: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCOLOR"); celda2.setCellComment(comment2); break; case 8: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("CANTIDAD"); celda2.setCellComment(comment2); break; case 9: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDEMBALAJE"); celda2.setCellComment(comment2); break; case 10: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDPRODUCTO"); celda2.setCellComment(comment2); break; case 11: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTEP"); celda2.setCellComment(comment2); break; case 12: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCONSUMIDOR"); celda2.setCellComment(comment2); break; case 13: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTECAMPO"); celda2.setCellComment(comment2); break; case 14: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDPRESENTACION"); celda2.setCellComment(comment2); break; } } } } hoja2.autoSizeColumn((short) 0); hoja2.autoSizeColumn((short) 1); hoja2.autoSizeColumn((short) 2); hoja2.autoSizeColumn((short) 3); hoja2.autoSizeColumn((short) 4); hoja2.autoSizeColumn((short) 5); hoja2.autoSizeColumn((short) 6); hoja2.autoSizeColumn((short) 7); hoja2.autoSizeColumn((short) 8); hoja2.autoSizeColumn((short) 9); hoja2.autoSizeColumn((short) 10); hoja2.autoSizeColumn((short) 11); hoja2.autoSizeColumn((short) 12); hoja2.autoSizeColumn((short) 13); hoja2.autoSizeColumn((short) 14); hoja2.autoSizeColumn((short) 15); libro.write(file2); file2.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_REGISTROPALE.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }
From source file:com.rknowsys.eapp.DataImportAction.java
/** * This method saves uploaded file into the server folder.And stores the * file data into the database./*from ww w .ja v a 2 s . c o m*/ * * @param actionRequest * @param actionResponse * @throws IOException */ public void saveDataImport(ActionRequest actionRequest, ActionResponse actionResponse) throws IOException { System.out.println("saveDataImport method()..!!!!!!!!!!"); ThemeDisplay themeDisplay = (ThemeDisplay) actionRequest.getAttribute(WebKeys.THEME_DISPLAY); Properties properties = PortalUtil.getPortalProperties(); String uploadDirectory = properties.getProperty("liferay.home") + "/data/uploadedFiles"; UploadPortletRequest uploadRequest = PortalUtil.getUploadPortletRequest(actionRequest); byte[] bytes = null; try { // ==========Saving the uploaded file in server folder with uploaded // date and time as file filename prefix.=========== Date date = new Date(); SimpleDateFormat sd = new SimpleDateFormat("mm-dd-yyyy"); String d = sd.format(date); System.out.println("uploaded date = " + d); File uploadedFile = uploadRequest.getFile("fileName"); bytes = FileUtil.getBytes(uploadedFile); String fileName = uploadRequest.getFileName("fileName"); File newFile = null; File newDirectory = new File(uploadDirectory); if (!newDirectory.exists()) { System.out.println("directory does not exist"); Path directoryPath = Paths.get(uploadDirectory); Files.createDirectory(directoryPath.getParent()); } newFile = new File(uploadDirectory + "/" + d + Calendar.getInstance().getTimeInMillis() + fileName); // ============Creating the New file in server folder=========== if (!newFile.exists()) { System.out.println("file does not exist"); Path pathToFile = Paths .get(uploadDirectory + "/" + d + Calendar.getInstance().getTimeInMillis() + fileName); Files.createFile(pathToFile); } // =========Reading the uploaded file content and writing the // content to newly created file============== FileInputStream fileInputStream = new FileInputStream(uploadedFile); fileInputStream.read(bytes); FileOutputStream fileOutputStream = new FileOutputStream(newFile); fileOutputStream.write(bytes, 0, bytes.length); fileOutputStream.close(); fileInputStream.close(); String filePath = newFile.getAbsolutePath(); System.out.println("filePath = " + filePath); FileInputStream file1 = new FileInputStream(new File(filePath)); // Reading Excel file Rows and cells content using apache poi api // and saving the data in to the database. XSSFWorkbook workbook = new XSSFWorkbook(file1); // Create Workbook // instance // holding // reference to // .xlsx file XSSFSheet sheet = workbook.getSheetAt(0); // Get first/desired sheet // from the workbook @SuppressWarnings("rawtypes") Iterator rows = sheet.rowIterator(); // Iterate through each rows // one by one while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); if (row.getRowNum() != 0) { EmpPersonalDetails empPersonalDetails = EmpPersonalDetailsLocalServiceUtil .createEmpPersonalDetails(CounterLocalServiceUtil.increment()); Employee employee = EmployeeLocalServiceUtil .createEmployee(CounterLocalServiceUtil.increment()); JobTitle jobTitle = JobTitleLocalServiceUtil .createJobTitle(CounterLocalServiceUtil.increment()); SubUnit subUnit = SubUnitLocalServiceUtil.createSubUnit(CounterLocalServiceUtil.increment()); EmploymentStatus employmentStatus = EmploymentStatusLocalServiceUtil .createEmploymentStatus(CounterLocalServiceUtil.increment()); EmpJob empJob = EmpJobLocalServiceUtil.createEmpJob(CounterLocalServiceUtil.increment()); EmpSupervisor empSupervisor = EmpSupervisorLocalServiceUtil .createEmpSupervisor(CounterLocalServiceUtil.increment()); @SuppressWarnings("rawtypes") Iterator cells = row.cellIterator(); while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); if (cell.getColumnIndex() == 0) { empPersonalDetails.setFirstName(cell.toString()); } if (cell.getColumnIndex() == 1) { empPersonalDetails.setMiddleName(cell.toString()); } if (cell.getColumnIndex() == 2) { empPersonalDetails.setLastName(cell.toString()); } if (cell.getColumnIndex() == 3) { empPersonalDetails.setEmployeeNo(cell.getRawValue()); } if (cell.getColumnIndex() == 4) { empPersonalDetails.setLicenseNo(cell.getRawValue()); } if (cell.getColumnIndex() == 5) { jobTitle.setTitle(cell.toString()); } if (cell.getColumnIndex() == 6) { employmentStatus.setEmploymentstatus(cell.toString()); } if (cell.getColumnIndex() == 7) { subUnit.setName(cell.toString()); } } employee.setUserId(themeDisplay.getUserId()); employee.setGroupId(themeDisplay.getCompanyGroupId()); employee.setCompanyId(themeDisplay.getCompanyId()); employee.setCreateDate(date); employee.setModifiedDate(date); employee = EmployeeLocalServiceUtil.addEmployee(employee); empPersonalDetails.setUserId(themeDisplay.getUserId()); empPersonalDetails.setGroupId(themeDisplay.getCompanyGroupId()); empPersonalDetails.setCompanyId(themeDisplay.getCompanyId()); empPersonalDetails.setCreateDate(date); empPersonalDetails.setModifiedDate(date); empPersonalDetails.setEmployeeId(employee.getEmployeeId()); empPersonalDetails = EmpPersonalDetailsLocalServiceUtil .addEmpPersonalDetails(empPersonalDetails); jobTitle.setUserId(themeDisplay.getUserId()); jobTitle.setGroupId(themeDisplay.getCompanyGroupId()); jobTitle.setCompanyId(themeDisplay.getCompanyId()); jobTitle.setCreateDate(date); jobTitle.setModifiedDate(date); jobTitle = JobTitleLocalServiceUtil.addJobTitle(jobTitle); subUnit.setUserId(themeDisplay.getUserId()); subUnit.setGroupId(themeDisplay.getCompanyGroupId()); subUnit.setCompanyId(themeDisplay.getCompanyId()); subUnit.setCreateDate(date); subUnit.setModifiedDate(date); subUnit = SubUnitLocalServiceUtil.addSubUnit(subUnit); employmentStatus.setUserId(themeDisplay.getUserId()); employmentStatus.setGroupId(themeDisplay.getCompanyGroupId()); employmentStatus.setCompanyId(themeDisplay.getCompanyId()); employmentStatus.setCreateDate(date); employmentStatus.setModifiedDate(date); employmentStatus = EmploymentStatusLocalServiceUtil.addEmploymentStatus(employmentStatus); empJob.setJobTitleId(employee.getEmployeeId()); empJob.setEmploymentStatusId(employmentStatus.getEmploymentStatusId()); empJob.setSubUnitId(subUnit.getSubUnitId()); empJob.setUserId(themeDisplay.getUserId()); empJob.setGroupId(themeDisplay.getCompanyGroupId()); empJob.setCompanyId(themeDisplay.getCompanyId()); empJob.setCreateDate(date); empJob.setModifiedDate(date); empJob.setEmployeeId(employee.getEmployeeId()); empJob = EmpJobLocalServiceUtil.addEmpJob(empJob); empSupervisor.setUserId(themeDisplay.getUserId()); empSupervisor.setGroupId(themeDisplay.getCompanyGroupId()); empSupervisor.setCompanyId(themeDisplay.getCompanyId()); empSupervisor.setCreateDate(date); empSupervisor.setModifiedDate(date); empSupervisor.setEmployeeId(employee.getEmployeeId()); empSupervisor.setReporterEmployeeId(empPersonalDetails.getEmployeeId()); empSupervisor = EmpSupervisorLocalServiceUtil.addEmpSupervisor(empSupervisor); } } file1.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java
private void createCellProduct(XSSFSheet sheetBuy, XSSFRow row, Integer index, String value) { XSSFCell cell = row.createCell(index); sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, 3)); cell.setCellStyle(styleProductList); cell.setCellValue(value);// www . j a va2 s .com }
From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java
private void createCellUtilization(XSSFSheet sheetBuy, XSSFRow row, String value) { XSSFCell cell = row.createCell(7);//from w w w. j a va 2 s .co m sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 7, 8)); cell.setCellStyle(styleProductList); cell.setCellValue(value); }
From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java
private void createCellSell(XSSFSheet sheetBuy, XSSFRow row, String value) { XSSFCell cell = row.createCell(9);// w w w . j av a 2s . c o m sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 9, 11)); cell.setCellStyle(styleProductList); cell.setCellValue(value); }
From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java
private void createCellNextTrimester(XSSFSheet sheetBuy, XSSFRow row, String value) { XSSFCell cell = row.createCell(12);// w w w. j a va 2s. c o m sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 12, 13)); cell.setCellStyle(styleProductList); cell.setCellValue(value); }
From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java
private void addBotton(XSSFRow lastRowReference, XSSFSheet sheet) { int colField = 5; int colValue = 6; int intRef = 4; XSSFRow rowTerms = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowTerms.createCell(0).setCellValue("Ateno: O mapa dever ser entregue em 3 vias em at 10 dias"); rowTerms.createCell(colField).setCellValue("O que declaro a verdade, sob as penas da Lei."); XSSFRow rowCityDate = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowCityDate.createCell(0).setCellValue("aps o trmino de cada trimestre."); rowCityDate.createCell(colField).setCellValue("Cidade/data:"); rowCityDate.createCell(colValue).setCellValue("${date.city} - ${date.format}"); XSSFRow rowEmpty = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowEmpty.createCell(0);/* ww w . j a v a2 s . c o m*/ XSSFRow rowSignature = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowSignature.createCell(colField).setCellValue("Assinatura:"); rowSignature.createCell(colValue).setCellValue("____________________________________"); XSSFRow rowResponsable = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowResponsable.createCell(colField).setCellValue("Nome:"); rowResponsable.createCell(colValue).setCellValue("${license.responsable.name}"); XSSFRow rowOffice = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowOffice.createCell(colField).setCellValue("Cargo:"); rowOffice.createCell(colValue).setCellValue("${license.responsable.office}"); XSSFRow rowRG = sheet.createRow(lastRowReference.getRowNum() + intRef++); rowRG.createCell(colField).setCellValue("R.G. N:"); rowRG.createCell(colValue).setCellValue("${license.responsable.rg}"); }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private String fillRow(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, ResultSet rs, XSSFRow row, FormulaEvaluator evaluator, LinkedHashSet<String> de, Boolean isForward, LinkedHashMap<Integer, String> lotDb2Number) throws SQLException { String result = null;//from w w w.j a v a2 s. c o m XSSFCell cell; if (isForward == null || isForward) { cell = row.getCell(1); if (rs.getObject("Produktkatalog.Bezeichnung") != null) cell.setCellValue(rs.getString("Produktkatalog.Bezeichnung")); else cell.setCellValue(""); cell = row.getCell(2); if (rs.getObject("Chargen.ChargenNr") != null) cell.setCellValue(rs.getString("Chargen.ChargenNr")); else cell.setCellValue("(autoLot" + row.getRowNum() + ")"); result = cell.getStringCellValue(); } else { cell = row.getCell(0); if (rs.getObject("Chargen.ChargenNr") != null) cell.setCellValue(rs.getString("Chargen.ChargenNr")); else cell.setCellValue("(autoLot" + row.getRowNum() + ")"); result = cell.getStringCellValue(); } insertCondition(dvHelper, sheetTracing, row.getRowNum(), 3, "1", "31"); cell = row.getCell(3); if (rs.getObject("Lieferungen.dd_day") != null) cell.setCellValue(rs.getInt("Lieferungen.dd_day")); else cell.setCellValue(""); insertCondition(dvHelper, sheetTracing, row.getRowNum(), 4, "1", "12"); cell = row.getCell(4); if (rs.getObject("Lieferungen.dd_month") != null) cell.setCellValue(rs.getInt("Lieferungen.dd_month")); else cell.setCellValue(""); insertCondition(dvHelper, sheetTracing, row.getRowNum(), 5, "1900", "3000"); cell = row.getCell(5); if (rs.getObject("Lieferungen.dd_year") != null) cell.setCellValue(rs.getInt("Lieferungen.dd_year")); else cell.setCellValue(""); insertCondition(dvHelper, sheetTracing, row.getRowNum(), 6, "1", "31"); cell = row.getCell(6); if (rs.getObject("Lieferungen.ad_day") != null) cell.setCellValue(rs.getInt("Lieferungen.ad_day")); else cell.setCellValue(""); insertCondition(dvHelper, sheetTracing, row.getRowNum(), 7, "1", "12"); cell = row.getCell(7); if (rs.getObject("Lieferungen.ad_month") != null) cell.setCellValue(rs.getInt("Lieferungen.ad_month")); else cell.setCellValue(""); insertCondition(dvHelper, sheetTracing, row.getRowNum(), 8, "1900", "3000"); cell = row.getCell(8); if (rs.getObject("Lieferungen.ad_year") != null) cell.setCellValue(rs.getInt("Lieferungen.ad_year")); else cell.setCellValue(""); insertDecCondition(dvHelper, sheetTracing, row.getRowNum(), 9); cell = row.getCell(9); if (rs.getObject("Lieferungen.numPU") != null) cell.setCellValue(rs.getDouble("Lieferungen.numPU")); else cell.setCellValue(""); insertDropBox(dvHelper, sheetTracing, row.getRowNum(), 10, "=Units"); cell = row.getCell(10); if (rs.getObject("Lieferungen.typePU") != null) cell.setCellValue(rs.getString("Lieferungen.typePU")); else cell.setCellValue(""); cell = row.getCell(11); String stationBez = "Lieferungen.Empfnger"; if (isForward == null || isForward) stationBez = "Produktkatalog.Station"; if (rs.getObject(stationBez) != null) cell.setCellValue(getStationLookup(rs.getString(stationBez))); else cell.setCellValue(""); //cell = row.getCell(12); //cell.setCellFormula("INDEX(Companies,MATCH(L" + (row.getRowNum() + 1) + ",StationIDs,0),1)"); //evaluator.evaluateFormulaCell(cell); if (isForward == null) { cell = row.getCell(0); if (rs.getObject("ChargenVerbindungen.Produkt") != null && lotDb2Number != null && lotDb2Number.containsKey(rs.getInt("ChargenVerbindungen.Produkt"))) cell.setCellValue(lotDb2Number.get(rs.getInt("ChargenVerbindungen.Produkt"))); else cell.setCellValue(""); } // DeliveryID cell = row.getCell(12); if (rs.getObject("Lieferungen.Serial") != null) cell.setCellValue(rs.getString("Lieferungen.Serial")); else if (rs.getObject("Lieferungen.ID") != null) cell.setCellValue(rs.getString("Lieferungen.ID")); else cell.setCellValue(""); if (isForward == null || isForward) result = cell.getStringCellValue(); fillExtraFields("Lieferungen", rs.getObject("Lieferungen.ID"), row, de, 13); /* // ExtraFields if (rs.getObject("Lieferungen.ID") != null) { String sql = "Select * from " + MyDBI.delimitL("ExtraFields") + " WHERE " + MyDBI.delimitL("tablename") + "='Lieferungen' AND " + MyDBI.delimitL("id") + "=" + rs.getInt("Lieferungen.ID"); ResultSet rs2 = DBKernel.getResultSet(sql, false); if (rs2 != null && rs2.first()) { do { String s = rs2.getString("attribute"); int j=0; for (String e : de) { if (s.equals(e)) { cell = row.getCell(13+j); if (cell == null) cell = row.createCell(13+j); cell.setCellValue(rs2.getString("value")); break; } j++; } } while (rs2.next()); } } */ return result; }