List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet(String sheetname)
From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;//ww w . ja v a2s . c om 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.philips.his.pixiu.cdr.poi.BigGridDemo.java
License:Apache License
public static void main(String[] args) throws Exception { // Step 1. Create a template file. Setup sheets and workbook-level objects such as // cell styles, number formats, etc. XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Big Grid"); Map<String, XSSFCellStyle> styles = createStyles(wb); // name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml String sheetRef = sheet.getPackagePart().getPartName().getName(); // save the template FileOutputStream os = new FileOutputStream("c:/temp/template.xlsx"); wb.write(os);//from ww w .jav a2 s .c om os.close(); // Step 2. Generate XML file. File tmp = File.createTempFile("sheet", ".xml"); Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING); generate(fw, styles); fw.close(); // Step 3. Substitute the template entry with the generated data FileOutputStream out = new FileOutputStream("c:/temp/big-grid.xlsx"); substitute(new File("c:/temp/template.xlsx"), tmp, sheetRef.substring(1), out); out.close(); wb.close(); }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public void addNew(String filePath, String testName, String cellValue[]) throws PhrescoException { try {//w w w . j a va2 s. co m //FileInputStream myInput = new FileInputStream(filePath); int numCol; int cellno = 0; CellStyle tryStyle[] = new CellStyle[20]; String sheetName = testName; //String cellValue[] = {"","",testName,success, fail,"","","",total,testCoverage,"","",""}; Iterator<Row> rowIterator; File testDir = new File(filePath); StringBuilder sb = new StringBuilder(filePath); if (testDir.isDirectory()) { FilenameFilter filter = new PhrescoFileFilter("", "xlsx"); File[] listFiles = testDir.listFiles(filter); if (listFiles.length != 0) { for (File file1 : listFiles) { if (file1.isFile()) { sb.append(File.separator); sb.append(file1.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); XSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); numCol = 13; Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } do { int flag = 0; next = rowIterator.next(); if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index") && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) { for (Cell cell : next) { cell.setCellType(1); if (cell.getStringCellValue().equalsIgnoreCase("total")) { mySheet.shiftRows((mySheet.getLastRowNum() - 1), (mySheet.getPhysicalNumberOfRows() - 1), 1); flag = 1; } if (flag == 1) break; } if (flag == 1) break; } } while (rowIterator.hasNext()); Row r = null; if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { r = mySheet.createRow(next.getRowNum() - 1); } else { r = mySheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); // used only when sheet is 'index' if (i == 2) sheetName = cellValue[i]; cell.setCellStyle(tryStyle[i]); } if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1)); Sheet toSheet = myWorkBook.createSheet(sheetName); int i = 0; Iterator<Row> copyFrom = fromSheet.rowIterator(); Row fromRow, toRow; CellStyle newSheetStyle[] = new CellStyle[20]; Integer newSheetType[] = new Integer[100]; String newSheetValue[] = new String[100]; do { fromRow = copyFrom.next(); if (fromRow.getRowNum() == 24) { break; } toRow = toSheet.createRow(i); int numCell = 0; for (Cell cell : fromRow) { Cell newCell = toRow.createCell(numCell); cell.setCellType(1); newSheetStyle[numCell] = cell.getCellStyle(); newCell.setCellStyle(newSheetStyle[numCell]); newSheetType[numCell] = cell.getCellType(); newCell.setCellType(newSheetType[numCell]); if (fromRow.getCell(0).getStringCellValue().length() != 1 && fromRow.getCell(0).getStringCellValue().length() != 2 && fromRow.getCell(0).getStringCellValue().length() != 3) { newSheetValue[numCell] = cell.getStringCellValue(); newCell.setCellValue(newSheetValue[numCell]); } numCell = numCell + 1; } i = i + 1; } while (copyFrom.hasNext()); } // write to file FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } else { FilenameFilter xlsFilter = new PhrescoFileFilter("", "xls"); File[] xlsListFiles = testDir.listFiles(xlsFilter); if (xlsListFiles.length != 0) { for (File file2 : xlsListFiles) { if (file2.isFile()) { sb.append(File.separator); sb.append(file2.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); HSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); numCol = 13; Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } do { int flag = 0; next = rowIterator.next(); if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index") && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) { for (Cell cell : next) { cell.setCellType(1); if (cell.getStringCellValue().equalsIgnoreCase("total")) { mySheet.shiftRows((mySheet.getLastRowNum() - 1), (mySheet.getPhysicalNumberOfRows() - 1), 1); flag = 1; } if (flag == 1) break; } if (flag == 1) break; } } while (rowIterator.hasNext()); Row r = null; if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { r = mySheet.createRow(mySheet.getLastRowNum() - 2); } else { r = mySheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); // used only when sheet is 'index' if (i == 2) sheetName = cellValue[i]; cell.setCellStyle(tryStyle[i]); } if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1)); Sheet toSheet = myWorkBook.createSheet(sheetName); int i = 0; Iterator<Row> copyFrom = fromSheet.rowIterator(); Row fromRow, toRow; CellStyle newSheetStyle[] = new CellStyle[20]; Integer newSheetType[] = new Integer[100]; String newSheetValue[] = new String[100]; do { fromRow = copyFrom.next(); if (fromRow.getRowNum() == 24) { break; } toRow = toSheet.createRow(i); int numCell = 0; for (Cell cell : fromRow) { Cell newCell = toRow.createCell(numCell); cell.setCellType(1); newSheetStyle[numCell] = cell.getCellStyle(); newCell.setCellStyle(newSheetStyle[numCell]); newSheetType[numCell] = cell.getCellType(); newCell.setCellType(newSheetType[numCell]); if (fromRow.getCell(0).getStringCellValue().length() != 1 && fromRow.getCell(0).getStringCellValue().length() != 2 && fromRow.getCell(0).getStringCellValue().length() != 3) { newSheetValue[numCell] = cell.getStringCellValue(); newCell.setCellValue(newSheetValue[numCell]); } numCell = numCell + 1; if (numCell == 15) { break; } } i = i + 1; } while (copyFrom.hasNext()); } // write to file FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } else { FilenameFilter odsFilter = new PhrescoFileFilter("", "ods"); File[] odsListFiles = testDir.listFiles(odsFilter); for (File file1 : odsListFiles) { if (file1.isFile()) { sb.append(File.separator); sb.append(file1.getName()); break; } } File file = new File(sb.toString()); addTestSuiteToOds(file, cellValue); } } } } catch (Exception e) { // throw new PhrescoException(e); } }
From source file:com.respam.comniq.models.POIexcelExporter.java
License:Open Source License
public void createFile() throws IOException { String path = System.getProperty("user.home") + File.separator + "comniq" + File.separator + "output"; File file = new File(path + File.separator + "POImovieInfo.xlsx"); // Blank Workbook XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Movies"); // Data for Labels Map<String, Object[]> label = new TreeMap<>(); label.put("1", new Object[] { "Poster", "Title", "Release Date", "Metascore", "IMDB Rating", "Plot", "IMDB URL", "Genre", "Director", "Actors", "Rating", "Runtime" }); // Iterate over label and write to sheet Set<String> keyset = label.keySet(); // Setting Style for the Label Row Font font = workbook.createFont(); font.setFontHeight((short) 240); font.setFontName("Courier New"); font.setBold(true);//from w w w . ja v a 2 s .co m XSSFCellStyle labelStyle = workbook.createCellStyle(); labelStyle.setWrapText(true); labelStyle.setFont(font); // Setting column widths sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 8500); sheet.setColumnWidth(2, 4000); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 3500); sheet.setColumnWidth(5, 9500); sheet.setColumnWidth(6, 5000); sheet.setColumnWidth(7, 4000); sheet.setColumnWidth(8, 3500); sheet.setColumnWidth(9, 4000); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 4000); // Freezing the first row sheet.createFreezePane(0, 1); // Filling each cell with Label data for (String key : keyset) { Row row = sheet.createRow(0); Object[] objArr = label.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellStyle(labelStyle); cell.setCellValue((String) obj); } } // Writing the excel file try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); out.close(); System.out.println("Excel File Created"); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.saba.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Map<String, Object> data = new HashMap<String, Object>(); prepareXLSDynamicValues(data);/*w w w . j a v a 2 s . c om*/ XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Awarded Bid & Contact Details"); Map<String, CellStyle> styles = createStyles(workbook); sheet.setPrintGridlines(false); sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); setupColumnWidthForEachFields(sheet); //preparing the contact & details table along with data prepareContactDetailsTableAndData(data, sheet, styles); int contactdetrow = contactDetails.length + 2; //preparing the award bid & details table along with data prepareAwardBidDetailsTableAndData(data, sheet, styles, contactdetrow); int awardDetailsRow = (contactDetails.length + awardedBidDetails.length + 4); //preparing the product & details table prepareProductDetailsTable(workbook, sheet, styles, awardDetailsRow); //preparing the product & details table data prepareProductDetailsTableData(data, sheet, styles, awardDetailsRow); FileOutputStream out = new FileOutputStream("award_bid.xlsx"); workbook.write(out); out.close(); }
From source file:com.safeway.app.appcert.util.smoketester.TestCaseReader.java
public void writetoExcel(List<TestScriptTemplate> testcases) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Smoke Test Result"); DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); //create header first Row rowhead = sheet.createRow(2);/*w w w .jav a 2s.co m*/ rowhead.createCell(0).setCellValue("Application Code"); rowhead.createCell(1).setCellValue("Test Case #1: Login to application"); rowhead.createCell(2).setCellValue("Test Case #2: Verify Home Page title and element"); rowhead.createCell(3).setCellValue("Test Case #3: Verify next page title and element"); rowhead.createCell(4).setCellValue("Date and Time Log"); rowhead.createCell(5).setCellValue("Test Execution Log"); Iterator i = testcases.iterator(); int rownum = 3; while (i.hasNext()) { TestScriptTemplate testresult = (TestScriptTemplate) i.next(); Row row = sheet.createRow(rownum); rownum++; Date date = new Date(); row.createCell(0).setCellValue(testresult.getAppCode()); row.createCell(1).setCellValue(testresult.getTc1_resultSummary()); row.createCell(2).setCellValue(testresult.getTc2_resultSummary()); row.createCell(3).setCellValue(testresult.getTc3_resultSummary()); //row.createCell(4).getCellStyle().setWrapText(true); row.createCell(4).setCellValue(dateFormat.format(date)); row.createCell(5).getCellStyle().setWrapText(true); row.createCell(5).setCellValue(testresult.getLogs()); } try { Date date = new Date(); DateFormat dateFormatFile1 = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss"); FileOutputStream out = new FileOutputStream( new File("C:\\Nino\\SmokeTest_" + dateFormatFile1.format(date) + ".xlsx")); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:com.sec.ose.osi.report.standard.ISheetTemplate.java
License:Open Source License
public ISheetTemplate(XSSFWorkbook wb, String sheetName, int sheetColor, int[] coverWidth) { this.wb = wb; this.sheet = wb.createSheet(sheetName); sheet.setTabColor(sheetColor);/*from www . j a va 2 s . c o m*/ setLayout(coverWidth); createTitle(); createTable(); }
From source file:com.vodafone.poms.ii.helpers.ExportManager.java
public void exportActivityForUser() { List<Activity> activities = activityController.getUserItems(); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Master Track"); Row row = sheet.createRow(0);/* w w w .j a v a 2s .c om*/ for (int i = 0; i < activityHeaders.length; i++) { row.createCell(i).setCellValue(activityHeaders[i]); } for (int i = 0; i < activities.size(); i++) { row = sheet.createRow(i + 1); row.createCell(0).setCellValue(activities.get(i).getSite().getSitePhysical()); row.createCell(1).setCellValue(activities.get(i).getAsp().getSubcontractorName()); row.createCell(2).setCellValue(activities.get(i).getArea().getAreaName()); row.createCell(3).setCellValue(activities.get(i).getVendorOwner().getOwnerName()); if (activities.get(i).getClaimStatus() != null) { row.createCell(4).setCellValue(activities.get(i).getClaimStatus().getClaimName()); } else { row.createCell(4).setCellValue(""); } if (activities.get(i).getApprovalStatus() != null) { row.createCell(5).setCellValue(activities.get(i).getApprovalStatus().getStatusName()); } else { row.createCell(5).setCellValue(""); } row.createCell(6).setCellValue(activities.get(i).getActivityType().getDomainName()); if (activities.get(i).getPhase() != null) { row.createCell(7).setCellValue(activities.get(i).getPhase().getPhaseName()); } else { row.createCell(7).setCellValue(""); } row.createCell(8).setCellValue(activities.get(i).getActivityDate()); row.createCell(9).setCellValue(activities.get(i).getAcMaterialId()); row.createCell(10).setCellValue(activities.get(i).getAcDescription()); row.createCell(11).setCellValue(activities.get(i).getActivityDetails()); row.createCell(12).setCellValue(activities.get(i).getQty()); row.createCell(13).setCellValue(activities.get(i).getAcVendorPrice()); row.createCell(14).setCellValue(activities.get(i).getTotalPriceVendor()); row.createCell(15).setCellValue(activities.get(i).getTotalPriceVendorTaxes()); row.createCell(16).setCellValue(activities.get(i).getAcSubcontractorPrice()); row.createCell(17).setCellValue(activities.get(i).getTotalPriceAsp()); row.createCell(18).setCellValue(activities.get(i).getTotalUm()); row.createCell(19).setCellValue(activities.get(i).getTotalUmPercent()); row.createCell(20).setCellValue(activities.get(i).getActivityComment()); row.createCell(21).setCellValue((activities.get(i).getAspPoCollection().isEmpty() ? "Uncorrelated" : ((AspPo) activities.get(i).getAspPoCollection().toArray()[0]).getPoNumber())); } FacesContext facesContext = FacesContext.getCurrentInstance(); ExternalContext externalContext = facesContext.getExternalContext(); externalContext.setResponseContentType("application/vnd.ms-excel"); externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"G-Cairo Region Extra Work.xlsx\""); try { workbook.write(externalContext.getResponseOutputStream()); externalContext.getResponseOutputStream().close(); } catch (IOException ex) { Logger.getLogger(ExportManager.class.getName()).log(Level.SEVERE, null, ex); } facesContext.responseComplete(); JsfUtil.addSuccessMessage("Activity Report is now exported"); }
From source file:com.vodafone.poms.ii.helpers.ExportManager.java
public void exportActivity() { try {/*from w w w. ja v a 2 s. co m*/ List<Activity> activities = activityController.getExportItems(fromDate, toDate); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Master Track"); int imgPckId = addImageToWorkbook(workbook, "/home/poms/uploaded_data/pkg.png", Workbook.PICTURE_TYPE_PNG); String imgPckRelId = addImageToSheet(sheet, imgPckId, Workbook.PICTURE_TYPE_PNG); Row row = sheet.createRow(0); for (int i = 0; i < activityHeaders.length; i++) { row.createCell(i).setCellValue(activityHeaders[i]); } for (int i = 0; i < activities.size(); i++) { row = sheet.createRow(i + 1); row.createCell(0).setCellValue(activities.get(i).getSite().getSitePhysical()); row.createCell(1).setCellValue(activities.get(i).getAsp().getSubcontractorName()); row.createCell(2).setCellValue(activities.get(i).getArea().getAreaName()); row.createCell(3).setCellValue(activities.get(i).getVendorOwner().getOwnerName()); if (activities.get(i).getClaimStatus() != null) { row.createCell(4).setCellValue(activities.get(i).getClaimStatus().getClaimName()); } else { row.createCell(4).setCellValue(""); } if (activities.get(i).getApprovalStatus() != null) { row.createCell(5).setCellValue(activities.get(i).getApprovalStatus().getStatusName()); } else { row.createCell(5).setCellValue(""); } row.createCell(6).setCellValue(activities.get(i).getActivityType().getDomainName()); if (activities.get(i).getPhase() != null) { row.createCell(7).setCellValue(activities.get(i).getPhase().getPhaseName()); } else { row.createCell(7).setCellValue(""); } row.createCell(8).setCellValue(activities.get(i).getActivityDate()); row.createCell(9).setCellValue(activities.get(i).getAcMaterialId()); row.createCell(10).setCellValue(activities.get(i).getAcDescription()); row.createCell(11).setCellValue(activities.get(i).getActivityDetails()); row.createCell(12).setCellValue(activities.get(i).getQty()); row.createCell(13).setCellValue(activities.get(i).getAcVendorPrice()); row.createCell(14).setCellValue(activities.get(i).getTotalPriceVendor()); row.createCell(15).setCellValue(activities.get(i).getTotalPriceVendorTaxes()); row.createCell(16).setCellValue(activities.get(i).getAcSubcontractorPrice()); row.createCell(17).setCellValue(activities.get(i).getTotalPriceAsp()); row.createCell(18).setCellValue(activities.get(i).getTotalUm()); row.createCell(19).setCellValue(activities.get(i).getTotalUmPercent()); row.createCell(20).setCellValue(activities.get(i).getActivityComment()); row.createCell(21).setCellValue((activities.get(i).getAspPoCollection().isEmpty() ? "Uncorrelated" : ((AspPo) activities.get(i).getAspPoCollection().toArray()[0]).getPoNumber())); if (activities.get(i).getActivityAttachmentsCollection() != null) { if (activities.get(i).getActivityAttachmentsCollection().size() > 0) { Object[] attachments = activities.get(i).getActivityAttachmentsCollection().toArray(); for (int j = 0; j < attachments.length; j++) { XSSFClientAnchor imgAnchor1 = new XSSFClientAnchor(0, 0, 0, 0, (23 + j), row.getRowNum(), (23 + j + 1), row.getRowNum() + 1); String oleRelId1 = addFile(sheet, ((ActivityAttachments) attachments[j]).getAttachmentLocation(), (i + j + activities.get(i).getActivityId().intValue() + Math.random())); int shapeId1 = addImageToShape(sheet, imgAnchor1, imgPckId); addObjectToShape(sheet, imgAnchor1, shapeId1, oleRelId1, imgPckRelId, "Objekt-Manager-Shellobjekt"); } } } } FacesContext facesContext = FacesContext.getCurrentInstance(); ExternalContext externalContext = facesContext.getExternalContext(); externalContext.setResponseContentType("application/vnd.ms-excel"); externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"G-Cairo Region Extra Work.xlsx\""); try { workbook.write(externalContext.getResponseOutputStream()); externalContext.getResponseOutputStream().close(); } catch (IOException ex) { Logger.getLogger(ExportManager.class.getName()).log(Level.SEVERE, null, ex); } facesContext.responseComplete(); JsfUtil.addSuccessMessage("Activity Report is now exported"); } catch (InvalidFormatException ex) { Logger.getLogger(ExportManager.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExportManager.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.vodafone.poms.ii.helpers.ExportManager.java
public void exportCustomerPO() { List<VendorPo> vendorPOs = vendorPOController.getExportItems(fromDate, toDate); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("All POs"); Row row = sheet.createRow(0);//from w w w .j a va 2s . c om for (int i = 0; i < customerPOHeaders.length; i++) { row.createCell(i).setCellValue(customerPOHeaders[i]); } int innerRow = 0; for (int i = 0; i < vendorPOs.size(); i++) { row = sheet.createRow(i + 1 + innerRow); //po# row.createCell(0).setCellValue(vendorPOs.get(i).getPoNumber()); //poDate row.createCell(1).setCellValue(vendorPOs.get(i).getPoDate()); //domain row.createCell(2).setCellValue(vendorPOs.get(i).getDomainName().getDomainName()); //type row.createCell(3).setCellValue(vendorPOs.get(i).getPoType().getTypeName()); //description row.createCell(4).setCellValue(vendorPOs.get(i).getPoDescription()); //factor row.createCell(5).setCellValue(vendorPOs.get(i).getFactor()); //service_value row.createCell(6).setCellValue(vendorPOs.get(i).getServiceValue().toString()); //po_value row.createCell(7).setCellValue(vendorPOs.get(i).getPoValue().toString()); //po_value with taxes row.createCell(8).setCellValue(vendorPOs.get(i).getPoValueTaxes().toString()); //work done row.createCell(9).setCellValue(vendorPOs.get(i).getWorkDone()); //remaining in po row.createCell(10).setCellValue(vendorPOs.get(i).getRemainingInPo().toString()); //taxes row.createCell(11).setCellValue(vendorPOs.get(i).getTaxes()); Object[] mds = vendorPOs.get(i).getVendorMdCollection().toArray(); for (int j = 0; j < mds.length; j++) { if (j > 1) { row = sheet.createRow(i + 1 + innerRow); innerRow++; } //md_deserved row.createCell(12).setCellValue(((VendorMd) mds[j]).getMdDeserved().toString()); //md_value row.createCell(13) .setCellValue(((VendorMd) mds[j]).getMdValue() != null ? ((VendorMd) mds[j]).getMdValue().toString() : ""); //md_date row.createCell(14).setCellValue( ((VendorMd) mds[j]).getMdDate() != null ? ((VendorMd) mds[j]).getMdDate() : null); //md_number row.createCell(15).setCellValue( ((VendorMd) mds[j]).getMdNumber() != null ? ((VendorMd) mds[j]).getMdNumber() : ""); //invoiced row.createCell(16).setCellValue( ((VendorMd) mds[j]).getInvoiced() != null ? ((VendorMd) mds[j]).getInvoiced() : false); //remaining in md row.createCell(17) .setCellValue(((VendorMd) mds[j]).getRemainingInMd() != null ? ((VendorMd) mds[j]).getRemainingInMd().toString() : ""); } } FacesContext facesContext = FacesContext.getCurrentInstance(); ExternalContext externalContext = facesContext.getExternalContext(); externalContext.setResponseContentType("application/vnd.ms-excel"); externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"Customer POs.xlsx\""); try { workbook.write(externalContext.getResponseOutputStream()); externalContext.getResponseOutputStream().close(); } catch (IOException ex) { Logger.getLogger(ExportManager.class.getName()).log(Level.SEVERE, null, ex); } facesContext.responseComplete(); JsfUtil.addSuccessMessage("Customer PO Report is now exported"); }