List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getCreationHelper
@Override
public HSSFCreationHelper getCreationHelper()
From source file:com.tecnosur.util.Excel.java
private static void createCell(HSSFWorkbook wb, Row row, int column, short halign, short valign, String strContenido, boolean booBorde, boolean booCabecera) { CreationHelper ch = wb.getCreationHelper(); Cell cell = row.createCell(column);//from www. j ava 2 s . c o m HSSFFont cellFont = wb.createFont(); cellFont.setFontHeightInPoints((short) 8); cellFont.setFontName(HSSFFont.FONT_ARIAL); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cell.setCellValue(ch.createRichTextString(strContenido)); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cellStyle.setFont(cellFont); if (booBorde) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBottomBorderColor((short) 8); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setLeftBorderColor((short) 8); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setRightBorderColor((short) 8); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setTopBorderColor((short) 8); } if (booCabecera) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBottomBorderColor((short) 8); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setLeftBorderColor((short) 8); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setRightBorderColor((short) 8); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setTopBorderColor((short) 8); cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); } cell.setCellStyle(cellStyle); }
From source file:controller.VisitasController.java
public void exportExcel() { FileChooser fileChooser = new FileChooser(); fileChooser.setTitle("Open Resource File"); fileChooser.setInitialDirectory(new File(System.getProperty("user.home"))); fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("XLS", "*.xls")); File f = fileChooser.showSaveDialog(null); try {// w w w . ja v a2 s . c om String filename = f.getAbsolutePath(); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("FirstSheet"); int bool = 1; InputStream inputStream = getClass().getResourceAsStream("/images/excel-logo.jpg"); byte[] imageBytes = IOUtils.toByteArray(inputStream); int pictureureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG); inputStream.close(); CreationHelper helper = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(1); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureureIdx); HSSFRow rowhead = sheet.createRow(8); rowhead.createCell(0); rowhead.createCell(1).setCellValue("Cedula"); rowhead.createCell(2).setCellValue("Cliente"); rowhead.createCell(3).setCellValue("Contrato"); rowhead.createCell(4).setCellValue("Plan"); rowhead.createCell(5).setCellValue("Fecha"); rowhead.createCell(6).setCellValue("Hora"); rowhead.createCell(7).setCellValue("Invitados"); makeRowBold(workbook, rowhead); for (int i = 0; i < table.getItems().size(); i++) { HSSFRow row = sheet.createRow(i + 9); Asistencia a = (Asistencia) table.getItems().get(i); row.createCell(1).setCellValue(a.getCedula()); row.createCell(2).setCellValue(a.getNombre()); row.createCell(3).setCellValue(a.getContrato()); row.createCell(4).setCellValue(a.getPlan()); row.createCell(5).setCellValue(a.getFecha()); row.createCell(6).setCellValue(a.getHora()); row.createCell(7).setCellValue(Integer.parseInt(a.getInvitados())); centerRow(workbook, row); } autoSizeColumns(workbook); pict.resize(); FileOutputStream fileOut = new FileOutputStream(filename); workbook.write(fileOut); fileOut.close(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java
License:Apache License
private void initStyles(HSSFWorkbook wb) { // cache styles used to write text into cells HSSFCellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont();/* w w w .j a v a 2s. c om*/ font.setBold(true); style.setFont(font); styles.put(STYLE_BOLD, style); style = wb.createCellStyle(); font = wb.createFont(); font.setItalic(true); style.setFont(font); styles.put(STYLE_ITALIC, style); style = wb.createCellStyle(); font = wb.createFont(); font.setItalic(true); font.setColor(Font.COLOR_RED); style.setFont(font); styles.put(STYLE_REVIEW, style); style = wb.createCellStyle(); style.setFillPattern(FillPatternType.FINE_DOTS); style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex()); style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex()); styles.put(STYLE_MISSING, style); style = wb.createCellStyle(); style.setFillPattern(FillPatternType.FINE_DOTS); style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex()); style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex()); style.setFont(font); styles.put(STYLE_MISSING_REVIEW, style); style = wb.createCellStyle(); HSSFCreationHelper createHelper = wb.getCreationHelper(); style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-dd-mm hh:mm")); styles.put(STYLE_DATETIME, style); }
From source file:Documentos.ClaseAlmacenGeneral.java
private static void createTituloCell(HSSFWorkbook wb, Row row, int column, short halign, short valign, String strContenido) {/*from w w w .ja va 2s . c o m*/ CreationHelper ch = wb.getCreationHelper(); Cell cell = row.createCell(column); cell.setCellValue(ch.createRichTextString(strContenido)); HSSFFont cellFont = wb.createFont(); cellFont.setFontHeightInPoints((short) 11); cellFont.setFontName(HSSFFont.FONT_ARIAL); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cellStyle.setFont(cellFont); cell.setCellStyle(cellStyle); }
From source file:eu.squadd.timesheets.eolas.TimeTemplate.java
public String prepareTimesheet(String[] args) { String response = null;//from www. j av a 2 s. c o m try { String[] ym = args[0].split("/"); month = Integer.parseInt(ym[0]); year = Integer.parseInt(ym[1]); Calendar cal = Calendar.getInstance(TimeZone.getDefault()); cal.set(Calendar.YEAR, year); cal.set(Calendar.MONTH, month - 1); int days = cal.getActualMaximum(Calendar.DAY_OF_MONTH); monthName = cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.ENGLISH); String periodName = monthName + "-" + year; cal.set(Calendar.DATE, 1); String dayOfWeek = new SimpleDateFormat("EE").format(cal.getTime()); System.out.println("Month: " + periodName); System.out.println("Days in month: " + days); System.out.println("Month starts in: " + dayOfWeek); Map<String, String> bankHolidays = year == 2016 ? publicHolidays2016 : publicHolidays2017; Map<String, String> holidays = this.extractHolidays(args); HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(template)); HSSFSheet sheet = wb.getSheet("timesheet"); //getSheetAt(0); HSSFRow currentRow; SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); sheet.getRow(4).getCell(1).setCellValue(periodName); int row = 7; int startRow = 0; int i = 1; while (i <= days) { currentRow = sheet.getRow(row); if (currentRow.getRowNum() > 47) break; String day = currentRow.getCell(0).getStringCellValue(); if (day.startsWith("Total")) { evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); row++; continue; } if (startRow == 0) { if (dayOfWeek.equals(day.substring(0, 3))) { startRow = currentRow.getRowNum(); System.out.println("Starting row found: " + startRow + 1); } else { row++; continue; } } cal.set(Calendar.DATE, i); String date = sdf.format(cal.getTime()); if (!day.equals("Saturday") && !day.equals("Sunday") && bankHolidays.get(date) == null && holidays.get(date) == null) { currentRow.getCell(1).setCellValue(date); currentRow.getCell(2).setCellValue(defaultHours); // regular hours //currentRow.getCell(3).setCellValue(defaultHours); // overtime hours currentRow.getCell(4).setCellValue(defaultHours); // total hours } i++; row++; } currentRow = sheet.getRow(46); evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); currentRow = sheet.getRow(47); evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); response = outFilePath.replace("#MONTH#", periodName); wb.write(new FileOutputStream(response)); } catch (IOException ex) { Logger.getLogger(Timesheets.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("Timesheet created."); return response; }
From source file:excel.PoiWriteExcelFile.java
public static int generarReporte() { //Calendar cal=Calendar.getInstance(); Calendar cal = WorkMonitorUI.instante; try {/* www . j a v a2 s. c om*/ FileOutputStream fileOut = new FileOutputStream("HH_" + instante.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()) .toUpperCase() + "_" + persona.getNombre().toUpperCase().charAt(0) + "." + persona.getApellido().toUpperCase() + "_" + instante.get(Calendar.YEAR) + ".xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle diasStyle = workbook.createCellStyle(); diasStyle.setFillForegroundColor(HSSFColor.SEA_GREEN.index); diasStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.WHITE.index); diasStyle.setFont(font); diasStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); diasStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); diasStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle schedStyle = workbook.createCellStyle(); schedStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); schedStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font3 = workbook.createFont(); font3.setFontHeightInPoints((short) 11); font3.setFontName("Calibri"); font3.setItalic(false); font3.setColor(HSSFColor.BLACK.index); schedStyle.setFont(font3); schedStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); schedStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); schedStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle workdayStyle = workbook.createCellStyle(); //workdayStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); workdayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); workdayStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); workdayStyle.setWrapText(true); HSSFFont font2 = workbook.createFont(); font2.setFontHeightInPoints((short) 8); font2.setFontName("Serif"); font2.setItalic(false); //font2.setColor(HSSFColor.YELLOW.index); workdayStyle.setFont(font2); workdayStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle weekendStyle = workbook.createCellStyle(); weekendStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); weekendStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); weekendStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); weekendStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); weekendStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle horarioStyle = workbook.createCellStyle(); horarioStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); horarioStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); horarioStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); horarioStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); horarioStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font4 = workbook.createFont(); font4.setFontHeightInPoints((short) 10); font4.setFontName("Serif"); font4.setItalic(false); font4.setBold(true); //font2.setColor(HSSFColor.YELLOW.index); horarioStyle.setFont(font4); // index from 0,0... cell A1 is cell(0,0) HSSFRow row1 = worksheet.createRow((short) 0); row1.setHeight((short) 500); //System.out.println("cal.get(Calendar.YEAR)="+cal.get(Calendar.YEAR)); HSSFCell cellA1 = row1.createCell((short) 0); cellA1.setCellValue( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); cellA1.setCellStyle(cellStyle); HSSFRow row2 = worksheet.createRow((short) 1); HSSFCell cellA4 = row2.createCell((short) 0); cellA4.setCellValue("Horario"); cellA4.setCellStyle(horarioStyle); //row2.setHeight((short)500); HSSFRow row3 = worksheet.createRow((short) 2); HSSFCell cellA3 = row3.createCell((short) 0); cellA3.setCellValue("Inicio - Trmino"); cellA3.setCellStyle(diasStyle); Calendar hora = Calendar.getInstance(); hora.set(Calendar.HOUR_OF_DAY, 9); hora.set(Calendar.MINUTE, 0); hora.set(Calendar.SECOND, 0); SimpleDateFormat sdf = new SimpleDateFormat("HH:mm"); HSSFCell cellXn; for (int i = 0; i < 29; ++i) { HSSFRow row = worksheet.createRow((short) i + 3); row.setHeight((short) 500); cellXn = row.createCell((short) 0); String horaIni = sdf.format(hora.getTime()); hora.add(Calendar.MINUTE, 30); String horaFin = sdf.format(hora.getTime()); cellXn.setCellValue(horaIni + " - " + horaFin); cellXn.setCellStyle(schedStyle); } System.out.println("cal.get(Calendar.MONTH)1=" + cal.get(Calendar.MONTH)); cal.add(Calendar.DAY_OF_MONTH, -cal.get(Calendar.DAY_OF_MONTH) + 1); int diasMes = cal.getActualMaximum(Calendar.DAY_OF_MONTH); System.out.println("cal.get(Calendar.MONTH)2=" + cal.get(Calendar.MONTH)); sdf = new SimpleDateFormat("EEEE d"); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)1=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); for (int i = 0; i < diasMes; ++i) { cellXn = row2.createCell((short) i + 1); String dia = sdf.format(cal.getTime()); dia = Character.toUpperCase(dia.charAt(0)) + dia.substring(1); cellXn.setCellValue(dia); cellXn.setCellStyle(horarioStyle); //System.out.println("cal.get(Calendar.DAY_OF_MONTH)="+cal.get(Calendar.DAY_OF_MONTH)); cal.add(Calendar.DAY_OF_MONTH, 1); } for (int i = 0; i < diasMes; ++i) { cellXn = row3.createCell((short) i + 1); cellXn.setCellValue("Descripcin"); cellXn.setCellStyle(diasStyle); } System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)2=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); //cal.add(Calendar.DAY_OF_MONTH, -1); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)3=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); HhDao hhDao = new HhDao(); Object[][] hh = new Object[29][cal.getActualMaximum(Calendar.DAY_OF_MONTH)]; hh = hhDao.getByMes(WorkMonitorUI.persona.getId(), cal.getTime()); cal.set(Calendar.DAY_OF_MONTH, 1); Sheet sheet = workbook.getSheetAt(0); sdf = new SimpleDateFormat("EEEE"); HSSFPatriarch _drawing = (HSSFPatriarch) sheet.createDrawingPatriarch(); CreationHelper factory = workbook.getCreationHelper(); for (int i = 0; i < 29; ++i) { Row r = sheet.getRow(i + 3); for (int j = 0; j < diasMes; ++j) { if (hh[i][j].toString() != "") { cellXn = (HSSFCell) r.createCell((short) j + 1); Hh _hh = (Hh) hh[i][j]; cellXn.setCellValue( _hh.getTarea().getNombre().trim() + ": " + _hh.getActividad().getNombre().trim()); HSSFAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5); org.apache.poi.ss.usermodel.Comment comment = _drawing.createComment(anchor); String comentario = _hh.getTarea().getComentario().toLowerCase(); if (_hh.getComentario() != null) comentario = comentario + _hh.getComentario().toLowerCase(); RichTextString str = factory.createRichTextString(comentario); comment.setString(str); cellXn.setCellComment(comment); } else { cellXn = (HSSFCell) r.createCell((short) j + 1); cellXn.setCellValue(""); } //System.out.println("sdf.format(cal.getTime())="+sdf.format(cal.getTime())); if (Arrays.asList("sbado", "domingo").contains(sdf.format(cal.getTime()))) cellXn.setCellStyle(weekendStyle); else cellXn.setCellStyle(workdayStyle); sheet.setColumnWidth(j, 5000); cal.add(Calendar.DAY_OF_MONTH, 1); //sheet.autoSizeColumn(j); } // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); System.out.println("cal.get(Calendar.MONTH)3=" + cal.get(Calendar.MONTH)); cal.set(Calendar.DAY_OF_MONTH, 1); } sheet.setColumnWidth(diasMes, 5000); WorkMonitorUI.instante = Calendar.getInstance(); sheet.setColumnWidth(0, 5000); sheet.createFreezePane(1, 3); // Freeze just one row //sheet.createFreezePane( 0, 1, 0, 1 ); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return -1; } catch (IOException e) { e.printStackTrace(); return -2; } return 1; }
From source file:helpers.Excel.ExcelDataFormat.java
public Object unmarshal(Exchange exchng, InputStream in) throws Exception { logger.info("Unmarshalling XLS"); Object res = exchng.getIn().getBody(); GenericFile genfile = (GenericFile) res; if (genfile.getFileNameOnly().endsWith("xlsx")) { return unmarshalXLSX(exchng, in); }//from ww w . j a v a 2 s. c o m HSSFWorkbook workbook = new HSSFWorkbook(in); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); if (importType != ImportType.FORMATTED) { return marshalAsArray(sheet.iterator()); } else { OneExcel excel = new OneExcel(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { OneExcelSheet onesheet = marshalAsStructure(workbook.getSheetAt(i).iterator(), evaluator); logger.info("Loading sheet:" + i); logger.info("Data:" + onesheet.data.size()); if (onesheet.data.size() > 0) excel.sheets.add(onesheet); } logger.info("Total sheets:" + excel.sheets.size()); ArrayList<HashMap<String, Object>> resu = excel.GenerateResult(); HashMap<String, Object> mappings = excel.GenerateMappings(); exchng.getOut().setHeader("mappings", mappings); exchng.getOut().setHeader("xlsdata", resu); return resu; } }
From source file:ia_app.PastPerformancesPanel.java
public PastPerformancesPanel() throws FileNotFoundException, IOException { initComponents();//w ww . j a v a 2 s .c om FileInputStream fis = new FileInputStream(new File("testing.xls")); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); FormulaEvaluator forEval = wb.getCreationHelper().createFormulaEvaluator(); this.text = ""; for (Row row : sheet) { for (Cell cell : row) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.text = (text + cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: this.text = (text + cell.getStringCellValue() + "\t"); break; } } this.text = (text + "\n"); } this.jTextArea1.setText(text); }
From source file:ia_app.StatsPanel.java
/** * Creates new form StatsPanel/*from ww w. ja va 2 s. com*/ */ public StatsPanel() throws FileNotFoundException, IOException { initComponents(); FileInputStream fis = new FileInputStream(new File("games.xls")); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); FormulaEvaluator forEval = wb.getCreationHelper().createFormulaEvaluator(); Row row1 = sheet.getRow(1); for (Cell cell : row1) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.makes += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextFieldMakes.setText(makes + ""); Row row2 = sheet.getRow(2); for (Cell cell : row2) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.attempts += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextFieldAttempts.setText(attempts + ""); this.jTextFieldFGPct.setText("58.3%"); Row row3 = sheet.getRow(3); for (Cell cell : row3) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.threeMakes += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } Row row4 = sheet.getRow(4); for (Cell cell : row4) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.threeAttempts += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextField3Pct.setText("80%"); /*for(Row column : sheet){ for(Cell cell : column){ switch(forEval.evaluateInCell(cell).getCellType()){ case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: break; } } } */ }
From source file:it.cineca.pst.huborcid.web.rest.ReportatFileResource.java
License:Open Source License
/** * GET /reportat -> get all the relPersonApplications. *///from ww w . j a va2 s. co m @RequestMapping(value = "/reportat/downloadExcel", method = RequestMethod.GET) @Timed public void getExcel(HttpServletResponse response) throws URISyntaxException { String currentLogin = SecurityUtils.getCurrentLogin(); Application application = applicationRepository.findOneByApplicationID(currentLogin); Sort sort = new Sort(Sort.Direction.ASC, "person.localID"); List<RelPersonApplication> listAccessToken = relPersonApplicationRepository .findAllByLastIsTrueAndApplicationIs(application, sort); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Report Access Token"); Object[] headerExcel = new Object[] { "LOCAL ID", "ORCID", "ORCID ASSOCIATION DATE", "ORCID ACCESS TOKEN", "ORCID ACCESS TOKEN RELEASED DATE" }; Row rowHeader = sheet.createRow(0); int cellnumHeader = 0; //header for (Object obj : headerExcel) { Cell cell = rowHeader.createCell(cellnumHeader++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } //data CellStyle cellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy")); int rownum = 1; for (int i = 0; i < listAccessToken.size(); i++) { RelPersonApplication relPerson = listAccessToken.get(i); Row rowData = sheet.createRow(rownum++); int cellnumData = 0; //localid Cell cellLocalId = rowData.createCell(cellnumData++); cellLocalId.setCellValue(relPerson.getPerson().getLocalID()); //orcid Cell cellOrcid = rowData.createCell(cellnumData++); cellOrcid.setCellValue(relPerson.getPerson().getOrcid()); //orcidCreated Cell cellOrcidCreated = rowData.createCell(cellnumData++); if (relPerson.getPerson().getOrcidReleaseDate() != null) { cellOrcidCreated.setCellValue(relPerson.getPerson().getOrcidReleaseDate().toDate()); cellOrcidCreated.setCellStyle(cellStyle); } //orcid access token Cell callAccessToken = rowData.createCell(cellnumData++); if ((relPerson.getDenied() == null) || (relPerson.getDenied() == false)) { callAccessToken.setCellValue(relPerson.getOauthAccessToken()); } else { callAccessToken.setCellValue((String) null); } //access token Created Cell cellAccessTokenCreated = rowData.createCell(cellnumData++); if (relPerson.getDateReleased() != null) { if ((relPerson.getDenied() == null) || (relPerson.getDenied() == false)) { cellAccessTokenCreated.setCellValue(relPerson.getDateReleased().toDate()); cellAccessTokenCreated.setCellStyle(cellStyle); } else { //cellAccessTokenCreated.setCellValue((Date)null); cellAccessTokenCreated.setCellStyle(cellStyle); } } // //FIXME quando verr gestita la revoca // //denied // Cell cellDenied = rowData.createCell(cellnumData++); // if(relPerson.getDenied()!=null) // cellDenied.setCellValue(new Boolean(null)); } //autosize for (int i = 0; i < headerExcel.length; i++) { sheet.autoSizeColumn(i); } try { workbook.write(response.getOutputStream()); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }