List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getCreationHelper
@Override
public XSSFCreationHelper getCreationHelper()
From source file:ExcelConverter.java
public List<ScheduleClass> Converter() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0);/*from w ww .j a v a 2s .co m*/ Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add; int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(j); if (cell.getStringCellValue().contains("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); break outerloop; } } } outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { row = sheet.getRow(i); outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } lt = LocalTime.parse(arrJam[0]); } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 5)) { subject = cell.getStringCellValue(); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colNoIdx + 6 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } } } } } for (int j = 0; j < dosen.size(); j++) { scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return Mergering(scheduleList); }
From source file:ExampleClass.java
public static void main(String[] args) throws Exception { File src = new File( "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx"); //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx"); FileInputStream fis = new FileInputStream(src); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet1 = wb.getSheetAt(0); // Iterator< Row> rowIterator = sheet1.iterator(); int colIndex = 0; int ex = 0;//w w w . ja va2s . com int lastCol = sheet1.getLastRowNum(); int i = 0; int idx = 0; CellRangeAddress add; // while (rowIterator.hasNext()) { // row = (XSSFRow) rowIterator.next(); // Iterator< Cell> cellIterator = row.cellIterator(); // //System.out.println("i = "+i+", ex:"+ex); // // if (row.getRowNum() > 53) { // break; // } //// if(lastCol-(ex+1) == i) break; // while (cellIterator.hasNext()) { // Cell cell = cellIterator.next(); // for (int f = 0; f < sheet1.getNumMergedRegions(); f++) { // add = sheet1.getMergedRegion(f); // // int col = add.getFirstColumn(); // int rowNum = add.getFirstRow(); // if (rowNum != 0 && rowNum == cell.getRowIndex() && colIndex == cell.getColumnIndex()) { // System.out.println("col:"+col+" "+",row :"+rowNum); // String b = String.valueOf(sheet1.getRow(rowNum).getCell(col)); // System.out.println(b); // // } // // } // switch (cell.getCellType()) // { // case Cell.CELL_TYPE_FORMULA: // ex++; // switch (cell.getCachedFormulaResultType()) // { // case Cell.CELL_TYPE_NUMERIC: // i = (int)cell.getNumericCellValue(); // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // // // break; // } // break; // case Cell.CELL_TYPE_NUMERIC: // if (cell.getColumnIndex() >= 6) // { // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // } // break; // case Cell.CELL_TYPE_STRING: // add = sheet1.getMergedRegion(cell.getRowIndex()); // // if (cell.getStringCellValue().contentEquals("No.")) // { // colIndex = cell.getColumnIndex(); // } // if (cell.getColumnIndex() == 1) // { // System.out.print( // cell.getStringCellValue() + " \t\t " ); // } // break; // // } // } // // System.out.println(); // } for (int j = 0; j < sheet1.getLastRowNum(); j++) { row = sheet1.getRow(j); for (int k = 0; k < row.getLastCellNum(); k++) { Cell cell = row.getCell(k); // if (cell.getColumnIndex() == 1) // { // System.out.println(cell.getStringCellValue()); // } FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == 0 && j > 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { System.exit(k); } if (cell.getColumnIndex() >= 6 && cell.getColumnIndex() <= 11) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.print((int) cell.getNumericCellValue() + " "); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); System.out.print( cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " "); } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet1.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { System.out.print( c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " "); } } } } System.out.println(""); } System.out.println(colIndex); System.out.println(idx); fis.close(); }
From source file:at.htlpinkafeld.beans.BenutzerkontoBean.java
/** * Method used to load stuff from predefined Excel not currently in use *//*from www .j a va 2 s . c om*/ public void loadFromExcel(ActionEvent event) throws FileNotFoundException, IOException, ParserException { if (excel != null) { FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Successful", excel.getFileName() + " successfully uploaded!")); XSSFWorkbook workbook = new XSSFWorkbook(excel.getInputstream()); for (int i = 1; i <= 12; i++) { int min = 5; LocalDate date = LocalDate.of(2016, i, 1); int max = min + date.lengthOfMonth() - 1; XSSFSheet sheet = workbook.getSheetAt(i); for (int j = min; j <= max; j++) { Row row = sheet.getRow(j); LocalDateTime start = null; LocalDateTime end = null; LocalDate day = date.withDayOfMonth((int) row.getCell(1).getNumericCellValue()); // DataFormatter formatter = new DataFormatter(); // System.out.println(formatter.formatCellValue(row.getCell(2))); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Cell soll = row.getCell(5); Cell ist = row.getCell(6); CellValue sollValue = null; CellValue istValue = null; if (soll != null && ist != null) { sollValue = evaluator.evaluate(soll); istValue = evaluator.evaluate(ist); } if (sollValue != null && istValue != null) { double dif = sollValue.getNumberValue() - istValue.getNumberValue(); if (istValue.getNumberValue() != 0.0) { Cell urlaub = row.getCell(10); if (urlaub != null && urlaub.getCellType() != Cell.CELL_TYPE_BLANK && urlaub.getNumericCellValue() != 1.0) { Cell cell = row.getCell(2); //for endtime = row 2 if (cell != null) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue != null) { double time = cellValue.getNumberValue() * 24; String time2; DecimalFormat df = new DecimalFormat("00.00"); time2 = df.format(time); time2 = time2.replace(',', ':'); LocalTime localtime = LocalTime.parse(time2); start = LocalDateTime.of(day, localtime); } } cell = row.getCell(3); //for endtime = row 3 if (cell != null) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue != null) { double time = cellValue.getNumberValue() * 24; String time2; DecimalFormat df = new DecimalFormat("00.00"); time2 = df.format(time); time2 = time2.replace(',', ':'); LocalTime localtime = LocalTime.parse(time2); end = LocalDateTime.of(day, localtime); } } int breaktime = 0; cell = row.getCell(4); if (cell != null) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue != null) { double tempbreaktime = cellValue.getNumberValue() * 24 * 60; breaktime = (int) tempbreaktime; } } String bemerkung = ""; Cell comment = row.getCell(11); if (comment != null) { CellValue value = evaluator.evaluate(comment); if (value != null) { bemerkung = value.formatAsString(); double d; try { d = Double.valueOf(bemerkung); if (BigDecimal.valueOf(d).scale() > 2) { d = d * 24 * 60; LocalTime lt = LocalTime.MIN.plusMinutes((int) (d + 0.5)); bemerkung = lt.format(DateTimeFormatter.ofPattern("HH:mm")); } } catch (NumberFormatException e) { //Value is not castable to double and will be ignored -> best case scenario } } } if (start != null && end != null) { WorkTime worktime = new WorkTime(user, start, end, breaktime, bemerkung, ""); IstZeitService.addIstTime(worktime); if (dif > 0.0) { LocalDateTime absenceend = end.plusMinutes((int) ((dif * 24 * 60) + 0.5)); Absence a = new Absence(user, AbsenceTypeNew.TIME_COMPENSATION, end, absenceend, bemerkung); a.setAcknowledged(true); AbsenceService.insertAbsence(a); } } } else if (urlaub != null && urlaub.getCellType() != Cell.CELL_TYPE_BLANK && urlaub.getNumericCellValue() == 1.0) { start = LocalDateTime.of(day, LocalTime.MIN); end = start; Absence a = new Absence(user, AbsenceTypeNew.HOLIDAY, start, end); a.setAcknowledged(true); AbsenceService.insertAbsence(a); } } else { Cell cell = row.getCell(2); //for endtime = row 2 if (cell != null) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue != null) { double time = cellValue.getNumberValue() * 24; String time2; DecimalFormat df = new DecimalFormat("00.00"); time2 = df.format(time); time2 = time2.replace(',', ':'); LocalTime localtime = LocalTime.parse(time2); start = LocalDateTime.of(day, localtime); } } cell = row.getCell(3); //for endtime = row 3 if (cell != null) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue != null) { double time = cellValue.getNumberValue() * 24; String time2; DecimalFormat df = new DecimalFormat("00.00"); time2 = df.format(time); time2 = time2.replace(',', ':'); LocalTime localtime = LocalTime.parse(time2); end = LocalDateTime.of(day, localtime); } } String bemerkung = ""; Cell comment = row.getCell(11); if (comment != null) { CellValue value = evaluator.evaluate(comment); if (value != null) { bemerkung = value.formatAsString(); } } Absence a = new Absence(user, AbsenceTypeNew.TIME_COMPENSATION, start, end, bemerkung); a.setAcknowledged(true); AbsenceService.insertAbsence(a); } } } } } }
From source file:com.anritsu.mcrepositorymanager.utils.GenerateRSS.java
public String getRSS() { FileInputStream file = null;//from w w w .j a v a2 s . c o m String rssFileName = rssTemplateFileName.replaceAll("template", mcVersion); try { file = new FileInputStream( new File(Configuration.getInstance().getRssTemplatePath() + rssTemplateFileName)); XSSFWorkbook workbook = new XSSFWorkbook(file); workbook.setSheetName(workbook.getSheetIndex("MC X.X.X"), "MC " + mcVersion); XSSFSheet sheet = workbook.getSheet("MC " + mcVersion); CreationHelper createHelper = workbook.getCreationHelper(); Cell cell = null; // Update the sheet title cell = sheet.getRow(0).getCell(0); cell.setCellValue(cell.getStringCellValue().replaceAll("template", mcVersion)); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); XSSFCellStyle hlinkstyle = workbook.createCellStyle(); XSSFFont hlinkfont = workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(HSSFColor.BLUE.index); hlinkstyle.setFont(hlinkfont); hlinkstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderTop(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderRight(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); XSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MMMM-yyyy")); dateCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); // Populate the table int rowCount = 1; for (RecommendedMcPackage rmcp : sortedMcPackages) { if (rmcp.getRecommendedVersion() != null && rmcp.isShowInTable()) { Row row = sheet.createRow(rowCount + 1); rowCount++; cell = row.createCell(0); cell.setCellValue(rmcp.getTier().replaceAll("Anritsu/MasterClaw/", "")); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(rmcp.getGroup()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(rmcp.getPackageName()); UrlValidator defaultValidator = new UrlValidator(UrlValidator.ALLOW_LOCAL_URLS); if (rmcp.getRecommendedVersion().getReleaseNote() != null && defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())) { XSSFHyperlink releaseNotelink = (XSSFHyperlink) createHelper .createHyperlink(Hyperlink.LINK_URL); releaseNotelink.setAddress(rmcp.getRecommendedVersion().getReleaseNote()); //System.out.println("Inside(if) RN: " + rmcp.getRecommendedVersion().getReleaseNote() + " Valid: " + defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())); cell.setHyperlink(releaseNotelink); } cell.setCellStyle(hlinkstyle); cell = row.createCell(3); cell.setCellValue(rmcp.getRecommendedVersion().getPackageVersion()); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellValue(rmcp.getAvailability()); cell.setCellStyle(cellStyle); cell = row.createCell(5); String customers = Arrays.asList(rmcp.getRecommendedVersion().getCustomerList().toArray()) .toString(); if (customers.equalsIgnoreCase("[All]")) { customers = ""; } cell.setCellValue(customers); cell.setCellStyle(cellStyle); cell = row.createCell(6); cell.setCellValue(rmcp.getRecommendedVersion().getRisk()); cell.setCellStyle(cellStyle); cell = row.createCell(7); cell.setCellValue(rmcp.getPackageName()); XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress(rmcp.getRecommendedVersion().getDownloadLinks().iterator().next()); cell.setHyperlink((XSSFHyperlink) link); cell.setCellStyle(hlinkstyle); cell = row.createCell(8); cell.setCellValue((rmcp.getRecommendedVersion() != null && rmcp.getRecommendedVersion().isLessRecommended()) ? "#" : ""); cell.setCellStyle(cellStyle); cell = row.createCell(9); cell.setCellValue(rmcp.getRecommendedVersion().getNotes()); cell.setCellStyle(cellStyle); StringBuilder newFeatures = new StringBuilder(); for (MCPackageActivities mcpa : rmcp.getRecommendedVersion().getActivities()) { if (!mcpa.getActivityType().equalsIgnoreCase("epr")) { newFeatures.append(mcpa.getActivityType() + " " + mcpa.getActivityId() + "; "); } } cell = row.createCell(10); cell.setCellValue(newFeatures.toString()); cell.setCellStyle(cellStyle); cell = row.createCell(11); cell.setCellValue(rmcp.getRecommendedVersion().getReleaseDate()); cell.setCellStyle(dateCellStyle); } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); sheet.autoSizeColumn(11); } FileOutputStream outFile = new FileOutputStream( new File(Configuration.getInstance().getRssTemplatePath() + rssFileName)); workbook.write(outFile); outFile.close(); return Configuration.getInstance().getRssTemplatePath() + rssFileName; } catch (FileNotFoundException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } finally { try { file.close(); } catch (IOException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } } return ""; }
From source file:com.docdoku.server.export.ExcelGenerator.java
License:Open Source License
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) { File excelFile = new File("export_parts.xls"); //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Parts Data"); String header = StringUtils.join(queryResult.getQuery().getSelects(), ";"); String[] columns = header.split(";"); Map<Integer, String[]> data = new HashMap<>(); String[] headerFormatted = createXLSHeaderRow(header, columns, locale); data.put(1, headerFormatted);//from w ww . j av a 2 s . c om Map<Integer, String[]> commentsData = new HashMap<>(); String[] headerComments = createXLSHeaderRowComments(header, columns); commentsData.put(1, headerComments); List<String> selects = queryResult.getQuery().getSelects(); int i = 1; for (QueryResultRow row : queryResult.getRows()) { i++; data.put(i, createXLSRow(selects, row, baseURL)); commentsData.put(i, createXLSRowComments(selects, row)); } //Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = sheet.createRow(rownum++); String[] objArr = data.get(key); int cellnum = 0; for (String obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellValue(obj); } CreationHelper factory = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); String[] commentsObjArr = commentsData.get(key); cellnum = 0; for (String commentsObj : commentsObjArr) { if (commentsObj.length() > 0) { Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 1); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(commentsObj); comment.setString(str); // Assign the comment to the cell cell.setCellComment(comment); } cellnum++; } } // Define header style Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 10); headerFont.setFontName("Courier New"); headerFont.setItalic(true); headerFont.setColor(IndexedColors.WHITE.getIndex()); CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); // Set header style for (int j = 0; j < columns.length; j++) { Cell cell = sheet.getRow(0).getCell(j); cell.setCellStyle(headerStyle); if (cell.getCellComment() != null) { String comment = cell.getCellComment().getString().toString(); if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER) || comment.equals(QueryField.PART_MASTER_NUMBER)) { for (int k = 0; k < queryResult.getRows().size(); k++) { Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j) : sheet.getRow(k + 1).createCell(j); grayCell.setCellStyle(headerStyle); } } } } try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(excelFile); workbook.write(out); out.close(); } catch (Exception e) { LOGGER.log(Level.FINEST, null, e); } return excelFile; }
From source file:com.endro.belajar.controller.InvoiceProdukController.java
private void clickedbuttonExportDialog() { dialogExport.getButtonExport().addActionListener(new ActionListener() { @Override/*from ww w. j a v a 2s. com*/ public void actionPerformed(ActionEvent e) { try { LocalDate tanggalAwal = dialogExport.getTanggalAwalChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); LocalDate tanggalAkhir = dialogExport.getTanggalAkhirChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); List<InvoiceOrder> daftar = invoiceDao.findAllByTanggal(tanggalAwal, tanggalAkhir); processConvertExcel(daftar); } catch (SQLException | IOException ex) { Logger.getLogger(InvoiceProdukController.class.getName()).log(Level.SEVERE, null, ex); } catch (NullPointerException ex) { JOptionPane.showMessageDialog(dialogExport, "Form tanggal diisi dengan lengkap!"); } finally { dialogExport.dispose(); dialogExport = null; } } private void processConvertExcel(List<InvoiceOrder> daftarInvoice) throws FileNotFoundException, IOException { Integer returnVal = dialogExport.getChooserSaveFile().showOpenDialog(dialogExport); if (returnVal == dialogExport.getChooserSaveFile().APPROVE_OPTION) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Just Example"); List<InvoiceOrder> list = daftarInvoice; Integer rowTable = 0; Integer cellTable = 0; CellStyle cellStyleTanggal = workbook.createCellStyle(); CellStyle cellStyleHeader = workbook.createCellStyle(); CellStyle cellStyleDouble = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); XSSFFont font = workbook.createFont(); cellStyleTanggal.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy")); cellStyleDouble.setDataFormat( createHelper.createDataFormat().getFormat("[$Rp-421]#,##0.0000;-[$Rp-421]#,##0.0000")); font.setBold(true); cellStyleHeader.setFont(font); cellStyleHeader.setWrapText(true); //cellStyleHeader.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); cellStyleHeader.setFillPattern(FillPatternType.DIAMONDS); for (InvoiceOrder order : list) { Row row = sheet.createRow(rowTable); if (rowTable == 0) { sheet.setColumnWidth(0, 2000); Cell cellHeader = row.createCell(0); cellHeader.setCellValue("ID"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(1, 5000); cellHeader = row.createCell(1); cellHeader.setCellValue("Nomor Transaksi"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(2, 4000); cellHeader = row.createCell(2); cellHeader.setCellValue("Tanggal"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(3, 6000 * 3); cellHeader = row.createCell(3); cellHeader.setCellValue("Informasi Posting"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(4, 4850); cellHeader = row.createCell(4); cellHeader.setCellValue("Total Sebelum Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(5, 5000); cellHeader = row.createCell(5); cellHeader.setCellValue("Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(6, 4500); cellHeader = row.createCell(6); cellHeader.setCellValue("Total Setelah Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(7, 5000 * 2); cellHeader = row.createCell(7); cellHeader.setCellValue("Alamat Pengiriman"); cellHeader.setCellStyle(cellStyleHeader); } else { row.createCell(0).setCellValue((Integer) order.getPk()); row.createCell(1).setCellValue((String) order.getNomortransaksi()); Cell cellTanggal = row.createCell(2); cellTanggal.setCellValue((Date) order.getTanggal()); cellTanggal.setCellStyle(cellStyleTanggal); row.createCell(3).setCellValue((String) order.getInformasiposting()); Cell cellDouble = row.createCell(4); cellDouble.setCellValue(order.getTotalbelumdiskon()); cellDouble.setCellStyle(cellStyleDouble); cellDouble = row.createCell(5); cellDouble.setCellValue(order.getDiskonfaktur()); cellDouble.setCellStyle(cellStyleDouble); cellDouble = row.createCell(6); cellDouble.setCellValue(order.getTotalsetelahdiskon()); cellDouble.setCellStyle(cellStyleDouble); row.createCell(7).setCellValue((String) order.getAlamatpengiriman() == null ? "Null" : order.getAlamatpengiriman()); } rowTable++; } File file = dialogExport.getChooserSaveFile().getSelectedFile(); FileOutputStream outputStream = new FileOutputStream(file + File.separator + "Penjualan.xlsx"); workbook.write(outputStream); int pesan = JOptionPane.showConfirmDialog(dialogExport, "Telah tersimpan di " + file + File.separator + "Penjualan.xlsx \n Apakah anda ingin membuka file tersebut?", "Notification", JOptionPane.OK_CANCEL_OPTION); if (pesan == JOptionPane.YES_OPTION) { if ("Linux".equals(System.getProperty("os.name"))) { String runPenjualan = "xdg-open " + file + File.separator + "Penjualan.xlsx"; Runtime.getRuntime().exec(runPenjualan); } else if ("Windows".equals(System.getProperty("os.name"))) { String runPenjualan = "excel.exe /r" + file + File.separator + "Penjualan.xlsx"; Runtime.getRuntime().exec(runPenjualan); } } } else { dialogExport.getChooserSaveFile().cancelSelection(); } } }); }
From source file:com.endro.belajar.controller.MainController.java
private void clickedExport() { exportPenjualan.getButtonExport().addActionListener(new ActionListener() { @Override//from www .j a va 2 s . c om public void actionPerformed(ActionEvent e) { try { LocalDate tanggalAwal = exportPenjualan.getTanggalAwalChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); LocalDate tanggalAkhir = exportPenjualan.getTanggalAkhirChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); List<InvoiceOrder> daftar = invoiceDao.findAllByTanggal(tanggalAwal, tanggalAkhir); processConvertExcel(daftar); } catch (SQLException ex) { Logger.getLogger(MainController.class.getName()).log(Level.SEVERE, null, ex); } catch (NullPointerException ex) { JOptionPane.showMessageDialog(exportPenjualan, "Form tanggal diisi dengan lengkap!"); } catch (IOException ex) { Logger.getLogger(MainController.class.getName()).log(Level.SEVERE, null, ex); } finally { exportPenjualan.dispose(); exportPenjualan = null; } } private void processConvertExcel(List<InvoiceOrder> daftarInvoice) throws FileNotFoundException, IOException { Integer returnVal = exportPenjualan.getChooserSaveFile().showOpenDialog(exportPenjualan); if (returnVal == exportPenjualan.getChooserSaveFile().APPROVE_OPTION) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Just Example"); List<InvoiceOrder> list = daftarInvoice; Integer rowTable = 0; Integer cellTable = 0; CellStyle cellStyleTanggal = workbook.createCellStyle(); CellStyle cellStyleHeader = workbook.createCellStyle(); CellStyle cellStyleDouble = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); XSSFFont font = workbook.createFont(); cellStyleTanggal.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy")); cellStyleDouble.setDataFormat( createHelper.createDataFormat().getFormat("[$Rp-421]#,##0.0000;-[$Rp-421]#,##0.0000")); font.setBold(true); cellStyleHeader.setFont(font); cellStyleHeader.setWrapText(true); //cellStyleHeader.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); cellStyleHeader.setFillPattern(FillPatternType.DIAMONDS); for (InvoiceOrder order : list) { Row row = sheet.createRow(rowTable); if (rowTable == 0) { sheet.setColumnWidth(0, 2000); Cell cellHeader = row.createCell(0); cellHeader.setCellValue("ID"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(1, 5000); cellHeader = row.createCell(1); cellHeader.setCellValue("Nomor Transaksi"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(2, 4000); cellHeader = row.createCell(2); cellHeader.setCellValue("Tanggal"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(3, 6000 * 3); cellHeader = row.createCell(3); cellHeader.setCellValue("Informasi Posting"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(4, 4850); cellHeader = row.createCell(4); cellHeader.setCellValue("Total Sebelum Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(5, 5000); cellHeader = row.createCell(5); cellHeader.setCellValue("Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(6, 4500); cellHeader = row.createCell(6); cellHeader.setCellValue("Total Setelah Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(7, 5000 * 2); cellHeader = row.createCell(7); cellHeader.setCellValue("Alamat Pengiriman"); cellHeader.setCellStyle(cellStyleHeader); } else { row.createCell(0).setCellValue((Integer) order.getPk()); row.createCell(1).setCellValue((String) order.getNomortransaksi()); Cell cellTanggal = row.createCell(2); cellTanggal.setCellValue((Date) order.getTanggal()); cellTanggal.setCellStyle(cellStyleTanggal); row.createCell(3).setCellValue((String) order.getInformasiposting()); Cell cellDouble = row.createCell(4); cellDouble.setCellValue(order.getTotalbelumdiskon()); cellDouble.setCellStyle(cellStyleDouble); cellDouble = row.createCell(5); cellDouble.setCellValue(order.getDiskonfaktur()); cellDouble.setCellStyle(cellStyleDouble); cellDouble = row.createCell(6); cellDouble.setCellValue(order.getTotalsetelahdiskon()); cellDouble.setCellStyle(cellStyleDouble); row.createCell(7).setCellValue((String) order.getAlamatpengiriman() == null ? "Null" : order.getAlamatpengiriman()); } rowTable++; } File file = exportPenjualan.getChooserSaveFile().getSelectedFile(); FileOutputStream outputStream = new FileOutputStream(file + File.separator + "Penjualan.xlsx"); workbook.write(outputStream); int pesan = JOptionPane.showConfirmDialog(exportPenjualan, "Telah tersimpan di " + file + File.separator + "Penjualan.xlsx \n Apakah anda ingin membuka file tersebut?", "Notification", JOptionPane.OK_CANCEL_OPTION); if (pesan == JOptionPane.YES_OPTION) { if ("Linux".equals(System.getProperty("os.name"))) { String runPenjualan = "xdg-open " + file + File.separator + "Penjualan.xlsx"; Runtime.getRuntime().exec(runPenjualan); } else if ("Windows".equals(System.getProperty("os.name"))) { String runPenjualan = "excel.exe /r" + file + File.separator + "Penjualan.xlsx"; Runtime.getRuntime().exec(runPenjualan); } } } else { exportPenjualan.getChooserSaveFile().cancelSelection(); } } }); }
From source file:com.joalgoca.validatorLayout.layoutDefinition.XLSXDocumentLayout.java
@Override public ResponseValidator validateDocument(InputStream inputStream) { ResponseValidator response;// w ww .j a v a 2 s . c o m StringBuilder stringBuilder = new StringBuilder(); if (isReadyToValidate() && inputStream != null) { HashMap rowsType = new HashMap(); for (int i = 0; i < documentValidator.getListRowValidator().size(); i++) { rowsType.put(documentValidator.getListRowValidator().get(i).getName(), i); } try { int rownum = 0; int wrong = 0; int right = 0; int skip = 0; OPCPackage pkg = OPCPackage.open(inputStream); XSSFWorkbook workBook = new XSSFWorkbook(pkg); FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator(); XSSFSheet xssfSheet = workBook.getSheetAt(0); for (Row row : xssfSheet) { String rowType = row.getCell(0).getStringCellValue(); if (rowsType.containsKey(rowType)) { RowValidator rowValidator = documentValidator.getListRowValidator() .get((int) rowsType.get(rowType)); int columnNum = rowValidator.getListItemValidator().size(); if (row.getLastCellNum() == columnNum) { String[] values = new String[columnNum]; int i = 0; for (Cell cell : row) { switch (evaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: values[i] = cell.getNumericCellValue() + ""; break; case Cell.CELL_TYPE_STRING: values[i] = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: values[i] = ""; break; case Cell.CELL_TYPE_BLANK: values[i] = ""; break; } i++; } ResponseValidator responseValidator = rowValidator.validate(values); if (!responseValidator.isSuccess()) { wrong++; stringBuilder.append("{\"row\":").append(rownum).append(",\"message\":") .append(responseValidator.getMessage()).append(","); } else right++; } else { wrong++; stringBuilder.append("{\"row\":").append(rownum) .append(",\"success\":false,\"message\":\"Line wrong size\"},").toString(); } } else { skip++; stringBuilder.append("{\"row\":").append(rownum) .append(",\"success\":false,\"message\":\"Unknow row type\"},").toString(); } rownum++; } response = new ResponseValidator(wrong == 0, "{\"skip\":" + skip + ",\"wrong\":" + wrong + ",\"right\":" + right + ",\"count\":" + rownum + ",\"errorMessages\":[" + (stringBuilder.toString().length() > 0 ? stringBuilder.substring(0, stringBuilder.toString().length() - 1) : "") + "]}"); } catch (Exception ex) { Logger.getLogger(FlatFixedDocumentLayout.class.getName()).log(Level.SEVERE, null, ex); response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"") .append(ex.getMessage()).append("\"}").toString()); } finally { try { inputStream.close(); } catch (IOException ex) { response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"") .append(ex.getMessage()).append("\"}").toString()); } } } else { response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"No configuration loaded\"}").toString()); } return response; }
From source file:com.larasolution.serverlts.FileUploadHandler.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // tablename=request.getParameter(tablename) //process only if its multipart content FileOutputStream fos = new FileOutputStream("C:\\uploads\\data.csv"); String list = ""; List<List> allData = new ArrayList<List>(); List<String> parameters = new ArrayList<String>(); if (ServletFileUpload.isMultipartContent(request)) { try {/* w w w .j av a 2s. c o m*/ StringBuilder data = new StringBuilder(); List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request); System.out.println(multiparts); for (FileItem item : multiparts) { if (item.isFormField()) { parameters.add(item.getFieldName()); System.out.println(parameters); } if (!item.isFormField()) { String name = new File(item.getName()).getName(); item.write(new File(UPLOAD_DIRECTORY + File.separator + name)); //System.out.println(File.separator); // Get the workbook object for XLSX file XSSFWorkbook wBook = new XSSFWorkbook( new FileInputStream(UPLOAD_DIRECTORY + File.separator + name)); XSSFSheet zz = wBook.getSheetAt(0); FormulaEvaluator formulaEval = wBook.getCreationHelper().createFormulaEvaluator(); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = zz.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: data.append(cell.getBooleanCellValue()).append(","); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { data.append( com.larasolution.modle.getDate.getDate5(cell.getDateCellValue())) .append(","); } else { data.append(cell.getNumericCellValue()).append(","); } break; case Cell.CELL_TYPE_STRING: data.append(cell.getStringCellValue()).append(","); break; case Cell.CELL_TYPE_BLANK: data.append("" + ","); break; case Cell.CELL_TYPE_FORMULA: Double value = Double.parseDouble(formulaEval.evaluate(cell).formatAsString()); data.append(String.format("%.2f", value)).append(","); break; default: data.append(cell).append(""); } } data.append("\r\n"); //String k = data.substring(0, data.length() - 3); //ls.add(k); // data.setLength(0); } fos.write(data.toString().getBytes()); fos.close(); // } } savetosql(); request.setAttribute("message", "successfully uploaded "); } catch (Exception ex) { request.setAttribute("message", "File Upload Failed due to " + ex); } } else { request.setAttribute("message", "Sorry this Servlet only handles file upload request"); } request.setAttribute("arrayfile", allData); request.setAttribute("names", parameters); RequestDispatcher disp = getServletContext().getRequestDispatcher("/FileUploadResult.jsp"); disp.forward(request, response); // System.out.println(allData.size()); // response.sendRedirect("send.jsp?arrayfile=" + list + ""); //request.getRequestDispatcher("/send.jsp?arrayfile='"+ls+"'").forward(request, response); }
From source file:com.pe.nisira.movil.view.action.MultitablaAction.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;//from w w w.ja 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_MULTITABLA.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_MULTITABLA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 4; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 4); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Indicar si es es Padre (Usar SI o NO)."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Es Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Opcional \n - Escribir la Abreviatura del campo del cual depende este."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Abreviatura Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Descripcion de la multitabla"); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("DESCRIPCION"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Abreviatura de la multitabla."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("ABREVIATURA"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); libro.write(file); file.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_MULTITABLA.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }