List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet autoSizeColumn
@Override public void autoSizeColumn(int column)
From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;/*from ww w . j a v a2s. 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.rdg.export.util.ExportXLS.java
License:Apache License
/** * set array list of data for rows in xls * * @param alData alData/*from ww w. j a v a 2 s . c o m*/ * @param spreadsheet spreadsheet */ private static void setDataForRow(ArrayList<Object> alData, XSSFSheet spreadsheet) { if (alData != null && !alData.isEmpty()) { for (int i = Const.ZERO; i < alData.size(); i++) { Object object = alData.get(i); rowNumber = Const.ZERO; setDataForRowObject(object, spreadsheet, Boolean.FALSE, i); } for (int k = Const.ZERO; k < rowNumber; k++) { spreadsheet.autoSizeColumn(k); } } }
From source file:controller.application.employee.ViewEmployeController.java
private void toExcel(ArrayList<ToExcelEmployee> lst, ArrayList<RFIDTimestamp> times, File file, Timestamp from, Timestamp to) throws IOException { try {/* w ww .ja va 2 s . c om*/ XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("Total arbetad tid " + file.getName()); XSSFRow row = spreadsheet.createRow(0); XSSFCell cell; cell = row.createCell(0); cell.setCellValue("Frnamn:"); cell = row.createCell(1); cell.setCellValue("Efternamn:"); cell = row.createCell(2); cell.setCellValue("Anstllningsnummer:"); cell = row.createCell(3); cell.setCellValue("Individuell tid (h) from.: " + (from.toString() + " tom.: " + to.toString()) .replaceAll("(\\s)((\\p{Digit}{2}:){2}\\p{Digit}{2})\\..", "")); Double totAllTime = 0.0; for (int i = 1; i <= lst.size(); i++) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(lst.get(i - 1).surname); cell = row.createCell(1); cell.setCellValue(lst.get(i - 1).lastname); cell = row.createCell(2); cell.setCellValue(lst.get(i - 1).id); cell = row.createCell(3); cell.setCellValue(lst.get(i - 1).time); totAllTime += lst.get(i - 1).time; if (i == lst.size()) { row = spreadsheet.createRow(i + 1); cell = row.createCell(3); cell.setCellValue("Sammanstllning:"); row = spreadsheet.createRow(i + 2); cell = row.createCell(3); cell.setCellValue(totAllTime); } } XSSFSheet spreadsheetTimes = workbook.createSheet("Tider"); row = spreadsheetTimes.createRow(0); cell = row.createCell(0); cell.setCellValue("RFID"); cell = row.createCell(1); cell.setCellValue("IN/UT"); cell = row.createCell(2); cell.setCellValue("Datum/Tid"); for (int i = 1; i <= times.size(); i++) { row = spreadsheetTimes.createRow(i); cell = row.createCell(0); cell.setCellValue(times.get(i - 1).getRFID().toString()); cell = row.createCell(1); cell.setCellValue(times.get(i - 1).getInOut()); cell = row.createCell(2); cell.setCellValue(times.get(i - 1).getTime()); } for (int k = 0; k < spreadsheet.getRow(0).getLastCellNum(); k++) { spreadsheet.autoSizeColumn(k); } for (int j = 0; j < spreadsheetTimes.getRow(0).getLastCellNum(); j++) { spreadsheetTimes.autoSizeColumn(j); } try (FileOutputStream out = new FileOutputStream(file)) { workbook.write(out); } System.out.println(file.getName() + " written successfully"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:coverageqc.functions.MyExcelEditor.java
public static void excelFormator(XSSFSheet currentSheet, File variantTsvFile, String tsvHeadingLine) throws IOException { String[] headingsArray = tsvHeadingLine.split("\t"); HashMap<String, Integer> headings = new HashMap<String, Integer>(); for (int x = 0; x < headingsArray.length; x++) { headings.put(headingsArray[x].substring(0, headingsArray[x].indexOf("_")), x); }//from www . ja v a 2 s. c om XSSFPrintSetup printSetup = (XSSFPrintSetup) currentSheet.getPrintSetup(); File xslxTempFile = new File(variantTsvFile.getCanonicalPath() + ".coverage_qc.xlsx"); currentSheet.getHeader().setLeft(xslxTempFile.getName()); currentSheet.getHeader().setRight("DO NOT DISCARD!!! Keep with patient folder."); //in Dr. Carter's VBA was set at points 18 which is .25 inches currentSheet.setMargin(Sheet.RightMargin, .25); currentSheet.setMargin(Sheet.LeftMargin, .25); printSetup.setOrientation(PrintOrientation.LANDSCAPE); //NOTE: setFitWidth doesn't work for columns, ie can't setFitToPageColumns, this //is the best workaround I can do, it will only looked cramped for those with a lot of calls printSetup.setFitWidth((short) 1); printSetup.setFitHeight((short) 3); currentSheet.setRepeatingRows(CellRangeAddress.valueOf("1")); currentSheet.setFitToPage(true); //making it by default not print the fellow's interp currentSheet.getWorkbook().setPrintArea(0, 1, currentSheet.getRow(0).getPhysicalNumberOfCells(), 0, currentSheet.getLastRowNum()); for (int x = 0; x < currentSheet.getRow(0).getPhysicalNumberOfCells(); x++) { currentSheet.autoSizeColumn(x); } currentSheet.setColumnWidth(0, 10000); currentSheet.setColumnWidth(1, 10000); currentSheet.setColumnWidth(headings.get("Consequence").intValue() + 2, 3500); currentSheet.setColumnHidden(headings.get("Classification").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Inherited From").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Allelic Depths").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Custom Annotation").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Custom Gene Annotation").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Num Transcripts").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Transcript").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("cDNA Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("CDS Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Protein Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Amino Acids").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Codons").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGNC").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Transcript HGNC").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Canonical").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Sift").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("PolyPhen").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("ENSP").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGVSc").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGVSp").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("dbSNP ID").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Ancestral Allele").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Allele Freq").intValue() + 2, true); //everything beyond this is hidden for (int x = headings.get("Global Minor Allele").intValue() + 2; x < currentSheet.getRow(0) .getPhysicalNumberOfCells(); x++) { currentSheet.setColumnHidden(x, true); } }
From source file:coverageqc.functions.MyExcelGenerator.java
public void excelFormator(String sheetName, File variantTsvFile) throws IOException { // String[] headingsArray = tsvHeadingLine.split("\t"); // HashMap<String, Integer> headings = new HashMap<String, Integer>(); // for(int x = 0; x < headingsArray.length; x++) { // headings.put(headingsArray[x].substring(0, headingsArray[x].indexOf("_")), x); // }//from w w w .j a va2 s .c o m XSSFSheet currentSheet = this.workbookcopy.getSheet(sheetName); XSSFPrintSetup printSetup = (XSSFPrintSetup) currentSheet.getPrintSetup(); File xslxTempFile = new File(variantTsvFile.getCanonicalPath() + ".coverage_qc.xlsx"); currentSheet.getHeader().setLeft(xslxTempFile.getName()); currentSheet.getHeader().setRight("DO NOT DISCARD!!! Keep with patient folder."); //in Dr. Carter's VBA was set at points 18 which is .25 inches currentSheet.setMargin(Sheet.RightMargin, .25); currentSheet.setMargin(Sheet.LeftMargin, .25); printSetup.setOrientation(PrintOrientation.LANDSCAPE); //NOTE: setFitWidth doesn't work for columns, ie can't setFitToPageColumns, this //is the best workaround I can do, it will only looked cramped for those with a lot of calls printSetup.setFitWidth((short) 1); printSetup.setFitHeight((short) 3); currentSheet.setRepeatingRows(CellRangeAddress.valueOf("1")); currentSheet.setFitToPage(true); //making it by default not print the fellow's interp currentSheet.getWorkbook().setPrintArea(0, 2, 20, 0, currentSheet.getLastRowNum()); for (int x = 0; x < currentSheet.getRow(0).getPhysicalNumberOfCells(); x++) { currentSheet.autoSizeColumn(x); if (x > 33) { currentSheet.setColumnHidden(x, true); } } currentSheet.setColumnWidth(0, 10000); currentSheet.setColumnWidth(1, 10000); currentSheet.setColumnWidth(2, 10000); // currentSheet.setColumnWidth(this.tsvRearrangeConversion.get(this.originalHeadings.get("Consequence"))+3, 3500); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Gene"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Variant"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Chr"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Coordinate"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Type"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Genotype"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Exonic"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Filters"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Quality"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("GQX"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alt Variant Freq"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Read Depth"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alt Read Depth"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Consequence"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Sift"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PolyPhen"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Global Minor"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Classification"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Inherited From"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allelic Depths"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Custom Annotation"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Custom Gene Annotation"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Num Transcripts"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Transcript"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("cDNA Position"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("CDS Position"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Protein Position"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Amino Acids"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Codons"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGNC"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Transcript HGNC"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Canonical"))+3, true); // //currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Sift"))+3, false); // //currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PolyPhen"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ENSP"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGVSc"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGVSp"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("dbSNP ID"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Ancestral Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Global Minor Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Amr"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Asn"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Af"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Eur"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Evs"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("EVS Coverage"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("EVS Samples"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Conserved Sequence"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC ID"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Wildtype"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Gene"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Primary Site"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Histology"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Accession"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Ref"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Alleles"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Allele Type"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Significance"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alternate Alleles"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Google Scholar"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PubMed"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("UCSC Browser"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar RS"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Disease Name"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar MedGen"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar OMIM"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Orphanet"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar GeneReviews"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar SnoMedCt ID"))+3, true); }
From source file:dataaccess.WriteResultToFile.java
public static boolean writeDataToXLSXFile(String path, Map<String, ArrayList<String>> result) { try {/* w w w . ja v a 2 s. c om*/ XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet spreadsheet = wb.createSheet("result"); XSSFRow row0 = spreadsheet.createRow(0); row0.createCell(0).setCellValue("????"); row0.createCell(1).setCellValue(""); row0.createCell(2).setCellValue("?"); row0.createCell(3).setCellValue(""); row0.createCell(4).setCellValue("?"); row0.createCell(5).setCellValue(""); row0.createCell(6).setCellValue(""); row0.createCell(7).setCellValue(""); row0.createCell(8).setCellValue("()"); row0.createCell(9).setCellValue("(::)"); Iterator<Map.Entry<String, ArrayList<String>>> resultIterator = result.entrySet().iterator(); XSSFRow row; int rowNum = 1; while (resultIterator.hasNext()) { Map.Entry<String, ArrayList<String>> resultEntry = resultIterator.next(); String supplierName = resultEntry.getKey(); ArrayList<String> info = resultEntry.getValue(); row = spreadsheet.createRow(rowNum); row.createCell(0).setCellValue(supplierName); for (int i = 0; i < info.size(); i++) { row.createCell(i + 1).setCellValue(info.get(i)); } rowNum = rowNum + 1; } for (int i = 0; i < 10; i++) { spreadsheet.autoSizeColumn(i); } OutputStream outputStream = new FileOutputStream(path + "\\" + "result.xlsx"); wb.write(outputStream); outputStream.close(); return true; } catch (Exception e) { e.printStackTrace(); return false; } }
From source file:dataaccess.WriteResultToFile.java
public static boolean writeRecommendationInfoToFile(String path, Map<String, ArrayList<String>> result) { try {/*from ww w . j a v a 2 s . com*/ XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet spreadsheet = wb.createSheet("result"); XSSFRow row0 = spreadsheet.createRow(0); row0.createCell(0).setCellValue("???"); row0.createCell(1).setCellValue("?(?)"); row0.createCell(2).setCellValue(""); row0.createCell(3).setCellValue("?"); row0.createCell(4).setCellValue(""); row0.createCell(5).setCellValue("?"); row0.createCell(6).setCellValue(""); row0.createCell(7).setCellValue(""); row0.createCell(8).setCellValue(""); row0.createCell(9).setCellValue("()"); row0.createCell(10).setCellValue("(::)"); Iterator<Map.Entry<String, ArrayList<String>>> resultIterator = result.entrySet().iterator(); XSSFRow row; int rowNum = 1; while (resultIterator.hasNext()) { Map.Entry<String, ArrayList<String>> resultEntry = resultIterator.next(); String supplierName = resultEntry.getKey(); ArrayList<String> info = resultEntry.getValue(); row = spreadsheet.createRow(rowNum); row.createCell(0).setCellValue(supplierName); for (int i = 0; i < info.size(); i++) { row.createCell(i + 1).setCellValue(info.get(i)); } rowNum = rowNum + 1; } for (int i = 0; i < 10; i++) { spreadsheet.autoSizeColumn(i); } OutputStream outputStream = new FileOutputStream(path + "\\" + "result.xlsx"); wb.write(outputStream); outputStream.close(); return true; } catch (Exception e) { e.printStackTrace(); return false; } }
From source file:de.contentcreation.pplive.controller.ReportingController.java
public void postProcessXLSX(Object document) { XSSFWorkbook wb = (XSSFWorkbook) document; XSSFSheet sheet = wb.getSheetAt(0); XSSFRow header = sheet.getRow(0);// www . j a v a2 s. c o m XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < 11; i++) { // XSSFCell cell = header.getCell(i); sheet.autoSizeColumn(i); // cell.setCellStyle(cellStyle); } }
From source file:ec.sirec.web.impuestos.GestionAlcabalasControlador.java
public void postProcessXLS(Object document) { XSSFWorkbook wb = (XSSFWorkbook) document; XSSFSheet sheet = wb.getSheetAt(0); //Creo variable hoja ()contiene los atributos para la hoja de calculo List<String> encabezadoColumna = new ArrayList<String>(); for (Row row : sheet) { //Recorre los valores de la fila 1 (encabezado) pero en dataTable=0 if (row.getRowNum() == 0) { for (Cell cell : row) { encabezadoColumna.add(cell.getStringCellValue() + " "); }/*w ww . j av a 2s.c om*/ } else { break; } } //----inicio crea estilo XSSFCellStyle style = wb.createCellStyle(); //Se crea el estilo XSSFFont font = wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.WHITE.getIndex()); style.setFont(font); byte[] rgb = new byte[3]; rgb[0] = (byte) 076; rgb[1] = (byte) 145; rgb[2] = (byte) 065; XSSFColor myColor = new XSSFColor(rgb); style.setFillForegroundColor(myColor); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); XSSFRow row0 = sheet.createRow((short) 0); //Creo una fila en la posicion 0 //----fin crea estilo for (int i = 0; i <= encabezadoColumna.size() - 1; i++) { createCell(row0, i, encabezadoColumna.get(i), style); //agrego celdas en la posicion indicada con los valores de los encabezados } //Ajusta el ancho de las columnas for (int i = 0; i < 20; i++) { sheet.autoSizeColumn((short) i); } }
From source file:ec.sirec.web.impuestos.GestionImpuestoPredialControlador.java
public void postProcessXLS(Object document) throws IOException { XSSFWorkbook wb = (XSSFWorkbook) document; XSSFSheet hoja = wb.getSheetAt(0); CellStyle style = wb.createCellStyle(); style.setFillPattern(CellStyle.NO_FILL); org.apache.poi.ss.usermodel.Font font = wb.createFont(); font.setFontName("Times Roman"); font.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.BLACK.getIndex()); style.setFont(font);//from w ww . j a v a2 s . c o m /** * ** Configuracin del estilo de la celda header de la tabla. ***** */ CellStyle styleHeaderTable = wb.createCellStyle(); styleHeaderTable.setFillPattern(CellStyle.NO_FILL); org.apache.poi.ss.usermodel.Font fontHeaderTable = wb.createFont(); fontHeaderTable.setFontName("Times Roman"); fontHeaderTable.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fontHeaderTable.setColor(IndexedColors.BLACK.getIndex()); styleHeaderTable.setFont(fontHeaderTable); Sheet sheet = wb.getSheetAt(0); sheet.autoSizeColumn((short) 0); //ajusta el ancho de la primera columna sheet.autoSizeColumn((short) 1); sheet.autoSizeColumn((short) 2); for (int i = 0; i < 20; i++) { hoja.autoSizeColumn((short) i); } }