List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getColumnIndex
@Override public int getColumnIndex()
From source file:com.centurylink.mdw.drools.Excel2007Parser.java
License:Apache License
@SuppressWarnings("deprecation") private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) { int mergedRegionCount = sheet.getNumMergedRegions(); CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount]; for (int i = 0; i < mergedRegionCount; i++) { mergedRanges[i] = sheet.getMergedRegion(i); }/*from w w w . j a v a 2s .c o m*/ for (int i = 0; i <= sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); if (row != null) { newRow(listeners, i, row.getLastCellNum()); for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) { XSSFCell cell = row.getCell(cellNum); if (cell != null) { CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges); if (merged != null) { XSSFRow topRow = sheet.getRow(merged.getFirstRow()); XSSFCell topLeft = topRow.getCell(merged.getFirstColumn()); String cellValue = topLeft.getCellType() == CellType.NUMERIC.getCode() ? String.valueOf(topLeft.getNumericCellValue()) : topLeft.getStringCellValue(); newCell(listeners, i, cellNum, cellValue, topLeft.getColumnIndex()); } else { String cellValue = null; if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cellValue = String.valueOf(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = String.valueOf(cell.getNumericCellValue()); else cellValue = cell.getStringCellValue(); newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED); } } } } } finishSheet(listeners); }
From source file:com.centurylink.mdw.drools.Excel2007Parser.java
License:Apache License
private CellRangeAddress getRangeIfMerged(XSSFCell cell, CellRangeAddress[] mergedRanges) { for (int i = 0; i < mergedRanges.length; i++) { CellRangeAddress range = mergedRanges[i]; if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) return range; }//from w w w .ja v a2s . co m return null; }
From source file:com.centurylink.mdw.workflow.drools.Excel2007Parser.java
License:Apache License
private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) { int mergedRegionCount = sheet.getNumMergedRegions(); CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount]; for (int i = 0; i < mergedRegionCount; i++) { mergedRanges[i] = sheet.getMergedRegion(i); }/* w ww . j a va 2s .c o m*/ for (int i = 0; i <= sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); if (row != null) { newRow(listeners, i, row.getLastCellNum()); for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) { XSSFCell cell = row.getCell(cellNum); if (cell != null) { CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges); if (merged != null) { XSSFRow topRow = sheet.getRow(merged.getFirstRow()); XSSFCell topLeft = topRow.getCell(merged.getFirstColumn()); newCell(listeners, i, cellNum, topLeft.getStringCellValue(), topLeft.getColumnIndex()); } else { String cellValue = null; if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cellValue = String.valueOf(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = String.valueOf(cell.getNumericCellValue()); else cellValue = cell.getStringCellValue(); newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED); } } } } } finishSheet(listeners); }
From source file:com.pe.nisira.movil.view.action.MultitablaAction.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;/*w w w. j a v a2 s. c o m*/ StreamedContent arch = null; try { String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION"; File ruta = new File(folder); if (!ruta.isDirectory()) { ruta.mkdirs(); } String rutaArchivo = folder + "\\FI_MULTITABLA.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_MULTITABLA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 4; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 4); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Indicar si es es Padre (Usar SI o NO)."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Es Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Opcional \n - Escribir la Abreviatura del campo del cual depende este."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Abreviatura Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Descripcion de la multitabla"); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("DESCRIPCION"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Abreviatura de la multitabla."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("ABREVIATURA"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); libro.write(file); file.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_MULTITABLA.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }
From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;/*www . j av a 2s . co 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:de.escnet.ExcelTable.java
License:Open Source License
public ExcelTable(String excel, String sheetName) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(excel); evaluator = wb.getCreationHelper().createFormulaEvaluator(); theme = wb.getTheme();/* w w w.j av a2 s. c om*/ sheet = sheetName == null ? wb.getSheetAt(0) : wb.getSheet(sheetName); for (Iterator rowIt = sheet.rowIterator(); rowIt.hasNext();) { Row row = (Row) rowIt.next(); for (Iterator cellIt = row.cellIterator(); cellIt.hasNext();) { XSSFCell cell = (XSSFCell) cellIt.next(); int rowIndex = cell.getRowIndex(); rowMin = Math.min(rowMin, rowIndex); rowMax = Math.max(rowMax, rowIndex); int colIndex = cell.getColumnIndex(); colMin = Math.min(colMin, colIndex); colMax = Math.max(colMax, colIndex); } } }
From source file:nl.architolk.ldt.processors.ExcelConverter.java
License:Open Source License
public void generateData(PipelineContext context, ContentHandler contentHandler) throws SAXException { try {/*from w w w .j a v a 2s . c om*/ // Read binary content of Excel file ByteArrayOutputStream os = new ByteArrayOutputStream(); Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os); readInputAsSAX(context, INPUT_DATA, base64ContentHandler); final byte[] fileContent = os.toByteArray(); final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); // Create workbook XSSFWorkbook workbook = new XSSFWorkbook(bais); DataFormatter formatter = new DataFormatter(); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); contentHandler.startDocument(); contentHandler.startElement("", "workbook", "workbook", new AttributesImpl()); for (int s = 0; s < workbook.getNumberOfSheets(); s++) { XSSFSheet sheet = workbook.getSheetAt(s); AttributesImpl sheetAttr = new AttributesImpl(); sheetAttr.addAttribute("", "name", "name", "CDATA", sheet.getSheetName()); contentHandler.startElement("", "sheet", "sheet", sheetAttr); for (int r = 0; r <= sheet.getLastRowNum(); r++) { XSSFRow row = sheet.getRow(r); if (row != null) { AttributesImpl rowAttr = new AttributesImpl(); rowAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(r)); contentHandler.startElement("", "row", "row", rowAttr); for (int c = 0; c < row.getLastCellNum(); c++) { XSSFCell cell = row.getCell(c); if (cell != null) { try { String cellvalue = formatter.formatCellValue(cell, evaluator); if (cellvalue != "") { AttributesImpl columnAttr = new AttributesImpl(); columnAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(cell.getColumnIndex())); contentHandler.startElement("", "column", "column", columnAttr); contentHandler.characters(cellvalue.toCharArray(), 0, cellvalue.length()); contentHandler.endElement("", "column", "column"); } } catch (Exception e) { } } } contentHandler.endElement("", "row", "row"); } } contentHandler.endElement("", "sheet", "sheet"); } contentHandler.endElement("", "workbook", "workbook"); contentHandler.endDocument(); } catch (IOException e) { throw new OXFException(e); } }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
private void copyRow(XSSFRow sourceRow, XSSFRow targetRow, XSSFCreationHelper factory, XSSFDrawing patriarch) { for (int j = 0; j < sourceRow.getPhysicalNumberOfCells(); j++) { XSSFCell cell = sourceRow.getCell(j); if (cell != null) { XSSFCell newCell = targetRow.createCell(j); int cellType = cell.getCellType(); newCell.setCellType(cellType); switch (cellType) { case XSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(cell.getErrorCellValue()); break; case XSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: newCell.setCellValue(cell.getRichStringCellValue()); break; default: newCell.setCellValue(formatter.formatCellValue(cell)); }/*from ww w .ja va 2s .co m*/ if (cell.getCellComment() != null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(newCell.getColumnIndex()); anchor.setCol2(newCell.getColumnIndex() + 4); anchor.setRow1(newCell.getRowIndex()); anchor.setRow2(newCell.getRowIndex() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); XSSFComment comment = patriarch.createCellComment(anchor); comment.setString(cell.getCellComment().getString()); newCell.setCellComment(comment); } newCell.setCellStyle(cell.getCellStyle()); newCell.getSheet().setColumnWidth(newCell.getColumnIndex(), cell.getSheet().getColumnWidth(cell.getColumnIndex())); } } }
From source file:org.openstreetmap.josm.plugins.msf1.XLSX_Reader.java
public static void getIndexes(String arg) throws IOException { try {/*from www . ja va 2s . c o m*/ ExcelFileToRead = new FileInputStream(arg); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFCell cell; XSSFSheet sheet = wb.getSheetAt(0); Iterator cells = sheet.getRow(0).cellIterator(); while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (cell != null) { if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { if (cell.getStringCellValue().equalsIgnoreCase("_Location_Latitude")) { lat_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("_LOCATION_longitude")) { lon_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("Village_name")) { villageName_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("Alt_village_name")) { altVillageName_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("HANDPUMP_WORKING")) { handpump_condition_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("WATERPOINT_NAME")) { waterPointName_index = cell.getColumnIndex(); } if (cell.getStringCellValue().equalsIgnoreCase("BOREHOLE_PROTECTED")) { borehole_access_index = cell.getColumnIndex(); } } } } lon_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(lon_index) == null || row.getCell(lon_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(lon_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(lon_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(lon_index).toString(); lon_array[i] = var; // System.out.println(var); } else { lon_array[i] = "null"; } } lat_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(lat_index) == null || row.getCell(lat_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(lat_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(lat_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(lat_index).toString(); lat_array[i] = var; // System.out.println(var); } else { lat_array[i] = "null"; } } villageName_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(villageName_index) == null || row.getCell(villageName_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(villageName_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(villageName_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(villageName_index).toString(); villageName_array[i] = var; // System.out.println(var); } else { villageName_array[i] = "null"; } } altVilageName_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(altVillageName_index) == null || row.getCell(altVillageName_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(altVillageName_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(altVillageName_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(altVillageName_index).toString(); altVilageName_array[i] = var; // System.out.println(var); } else { altVilageName_array[i] = "null"; } } borehole_access_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(borehole_access_index) == null || row.getCell(borehole_access_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(borehole_access_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(borehole_access_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(borehole_access_index).toString(); borehole_access_array[i] = var; // System.out.println(var); } else { borehole_access_array[i] = "null"; } } handpump_condition_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(handpump_condition_index) == null || row.getCell(handpump_condition_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(handpump_condition_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(handpump_condition_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(handpump_condition_index).toString(); handpump_condition_array[i] = var; //System.out.println(var); } else { handpump_condition_array[i] = "null"; } } waterPointName_array = new String[sheet.getPhysicalNumberOfRows()]; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row.getCell(waterPointName_index) == null || row.getCell(waterPointName_index).getCellType() == Cell.CELL_TYPE_BLANK) { i++; } else if (row.getCell(waterPointName_index).getCellType() == XSSFCell.CELL_TYPE_NUMERIC || row.getCell(waterPointName_index).getCellType() == XSSFCell.CELL_TYPE_STRING) { String var = row.getCell(waterPointName_index).toString(); waterPointName_array[i] = var; // System.out.println(var); } else { waterPointName_array[i] = "null"; } } // public static String[] getLon_array() { // return lon_array; // } // // public static String[] getLat_array() { // return lat_array; // } // public static String[] getVillageName_array() { // return villageName_array; // } // public static String[] getAltVillageName_array() { // return altVilageName_array; // } // public static String[] getBoreholeAccess_array() { // return borehole_access_array; // } // public static String[] getHandPumpCondition_array() { // return handpump_condition_array; // } // public static String[] getWaterPoint_array() { // return waterPointName_array; // } wb.close(); } catch (IOException e) { } }