List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook
public XSSFWorkbook(PackagePart part) throws IOException
From source file:annualleave.PersonelTara.java
static public void Detection(int ilk, int son, String URL) throws FileNotFoundException, IOException { char gun[] = new char[100]; int gunler[] = new int[32]; for (int j = ilk; j <= son; j++) { gunler[j] = j;//from www . j a v a2s . c om } String oncekiAd; // C:\\Users\\talha\\Documents\\NetBeansProjects\\AnnualLeave\\src\\annualleave\\Mays 23.xlsx String excelFilePath = URL; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(0); int sonuncuIndex = firstSheet.getLastRowNum(); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); if (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getRowIndex() >= 6) { oncekiAd = ad; cell = cellIterator.next(); cell = cellIterator.next(); cell = cellIterator.next(); ad = cell.getStringCellValue(); cell = cellIterator.next(); cell = cellIterator.next(); tarih = cell.getStringCellValue(); if (ad != oncekiAd && i != 0 && !(oncekiAd.equals("Personel Ad Soyad")) && !(oncekiAd.isEmpty()) && !(ad.isEmpty()) && !(ad.equals("Personel Ad Soyad")) || cell.getRowIndex() == sonuncuIndex) { for (int j = ilk; j <= son; j++) { if (gunler[j] != 0) { GETIR[z] = oncekiAd + " " + gunler[j]; z++; test = 1; } if (isBuilt) { int left = tarih.indexOf("."); int right = tarih.lastIndexOf("."); String sub = tarih.substring(left + 1, right); ay = Integer.parseInt(sub); int left2 = tarih.lastIndexOf("."); int right2 = tarih.lastIndexOf(""); String sub2 = tarih.substring(left2 + 1, right2); yil = Integer.parseInt(sub2); } Build(); } if (test == 1) { yeniAd[c] = oncekiAd; for (int j = ilk; j <= son; j++) { if (gunler[j] != 0) { Calendar date = Calendar.getInstance(); date.set(yil, ay - 1, gunler[j]); if (date.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY || date.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) { } else { yeniAd[c] += " " + gunler[j]; } } } c++; test = 0; } for (int j = ilk; j <= son; j++) { gunler[j] = j; } } if (!(cell.getStringCellValue().isEmpty()) && !(ad.equals("Personel Ad Soyad"))) { int left = tarih.indexOf(0); int right = tarih.indexOf("."); String sub = tarih.substring(left + 1, right); gunler[Integer.parseInt(sub)] = 0; i++; } } } } }
From source file:apm.common.utils.excel.ImportExcel.java
License:Open Source License
/** * //from w w w.j a v a2 s . c om * @param path * @param headerNum ???=?+1 * @param sheetIndex ? * @throws InvalidFormatException * @throws IOException */ public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { if (StringUtils.isBlank(fileName)) { throw new RuntimeException("?"); } else if (fileName.toLowerCase().endsWith("xls")) { this.wb = new HSSFWorkbook(is); } else if (fileName.toLowerCase().endsWith("xlsx")) { this.wb = new XSSFWorkbook(is); } else { throw new RuntimeException("??!"); } if (this.wb.getNumberOfSheets() < sheetIndex) { throw new RuntimeException("!"); } this.sheet = this.wb.getSheetAt(sheetIndex); this.headerNum = headerNum; log.debug("Initialize success."); }
From source file:appform.Complaint.java
public static void updateComplaint(String complaint) { try {// ww w.j ava 2 s . c o m FileInputStream file = new FileInputStream(new File("Complaint.xls")); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); HashMap data; data = new HashMap<>(); data.put("7", new Object[] { complaint }); Set keyset = data.keySet(); int rownum = sheet.getLastRowNum() + 1; for (Object key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = (Object[]) data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } FileOutputStream out = new FileOutputStream(new File("Complaint.xls")); workbook.write(out); out.close(); System.out.println("Complaint written successfully.."); //</string></string,></string,> } catch (Exception e) { } }
From source file:at.htlpinkafeld.beans.BenutzerkontoBean.java
/** * Method used to load stuff from predefined Excel not currently in use *///from ww w.j a v a 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:at.metalab.m68k.dmnimport.DmnHelper.java
License:Apache License
public static DmnDecision parseXlsx(String resourceName) throws IOException { InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream(resourceName); XSSFWorkbook workBook = null;/*from w w w.j a v a 2 s .com*/ try { workBook = new XSSFWorkbook(in); XSSFSheet sheet = workBook.getSheetAt(0); String tableName = DmnXslx.getTableName(sheet); String tableHitPolicy = DmnXslx.getTableHitpolicy(sheet); List<Column> columns = DmnHelper.buildColumns(sheet); List<RowData> rows = DmnHelper.buildRows(columns, sheet); return buildDmnDecision(tableName, tableHitPolicy, columns, rows); } finally { if (workBook != null) { workBook.close(); } } }
From source file:at.mukprojects.exclycore.dao.ExclyCoreReaderTest.java
License:Open Source License
@Before public void setUp() throws FileNotFoundException, IOException { log.debug("SetUp ExclyCoreTest."); File inputFile = new File(RESFILE); inputStream = new FileInputStream(inputFile); inputWorkbook = new XSSFWorkbook(inputStream); XSSFSheet inputSheet = inputWorkbook.getSheet("Test"); inputRowIterator = inputSheet.iterator(); }
From source file:at.mukprojects.exclycore.dao.ExclyCoreWriterTest.java
License:Open Source License
/** * Tests the XLSXWriter functions./* w ww .j a v a2 s . c o m*/ */ @Test public void testXLSXWriter() throws Exception { XSSFWorkbook workbook = writer.createWorkbook(outputFile, false); workbook.createSheet("Sheet New"); writer.closeWorkbook(); assertTrue(outputTestFile.exists()); workbook = writer.createWorkbook(outputFile, true); workbook.createSheet("Sheet Add"); writer.closeWorkbook(); assertTrue(outputTestFile.exists()); FileInputStream inputStream = new FileInputStream(outputTestFile); workbook = new XSSFWorkbook(inputStream); assertTrue(workbook.getNumberOfSheets() == 2); workbook.close(); }
From source file:at.nhmwien.schema_mapping_tool.fileProcessors.XlsxProcessor.java
License:Apache License
public void prepareFileRead() throws Exception { this.fieldNames = new HashMap<String, Integer>(); this.readBook = new XSSFWorkbook(new FileInputStream(this.operateFile)); this.readSheet = this.readBook.getSheetAt(0); Iterator<Cell> cellIt = this.readSheet.getRow(0).cellIterator(); while (cellIt.hasNext()) { Cell currCell = cellIt.next();//from ww w . jav a2 s . c o m if (currCell.getCellType() != Cell.CELL_TYPE_STRING) continue; this.fieldNames.put(currCell.getStringCellValue(), currCell.getColumnIndex()); } this.currRow = 1; }
From source file:at.nhmwien.schema_mapping_tool.schemaReaders.XlsxConverter.java
License:Apache License
public LinkedHashMap<String, LinkedHashMap> parseFile(InputStream inputFile) { LinkedHashMap<String, LinkedHashMap> fields = null; try {/*from w w w. j a v a2 s . c om*/ XSSFWorkbook workbook = new XSSFWorkbook(inputFile); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = sheet.getRow(0); Iterator<Cell> cellIt = row.cellIterator(); fields = new LinkedHashMap(); while (cellIt.hasNext()) { Cell currCell = cellIt.next(); // Ignore any non-string cells (because they are not suitable as headers) if (currCell.getCellType() != Cell.CELL_TYPE_STRING) continue; LinkedHashMap fieldInfo = new LinkedHashMap(); fieldInfo.put("name", currCell.getStringCellValue()); fieldInfo.put("subfields", null); fields.put(currCell.getStringCellValue(), fieldInfo); } } catch (Exception e) { e.printStackTrace(); } return fields; }
From source file:athena.Controller.java
License:Open Source License
private void convertExceltoCSV(String inputFile, String outputFilePath) { InputStream inp = null;/* w w w.ja va2 s . co m*/ try { inp = new FileInputStream(inputFile); wb = new XSSFWorkbook(inp); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet thisSheet = wb.getSheetAt(i); int rowEnd = Math.max(1400, thisSheet.getLastRowNum()); view.setOutput("Writting.. " + thisSheet.getSheetName()); String csvRawString = ""; String outputFileName = outputFilePath + thisSheet.getSheetName() + ".csv"; try { OutputStream os; File testFile = new File(outputFileName); if (testFile.exists() && !testFile.isDirectory()) { os = new FileOutputStream(outputFilePath + thisSheet.getSheetName() + "(1).csv"); } else { os = new FileOutputStream(outputFileName); } PrintWriter w = new PrintWriter(new OutputStreamWriter(os, "UTF-8")); for (int j = 0; j < rowEnd; j++) { Row row = thisSheet.getRow(j); if (row != null) { for (int k = 0; k < row.getLastCellNum(); k++) { if (k == (row.getLastCellNum() - 1)) { if (row.getCell(k) != null) { csvRawString = csvRawString + row.getCell(k); } } else { if (row.getCell(k) == null) { csvRawString = csvRawString + ","; } else { csvRawString = csvRawString + row.getCell(k) + ","; } } } } else { csvRawString = csvRawString + ","; } csvRawString = csvRawString + "\n"; w.print(csvRawString); w.flush(); csvRawString = ""; } w.close(); view.setOutput("Done.. " + thisSheet.getSheetName()); } catch (FileNotFoundException e) { view.setOutput("I'm confused.. File not found!"); } catch (UnsupportedEncodingException e) { view.setOutput("Call 911.. or Jake"); } } } catch (IOException e) { view.setOutput("Uh oh.. Fail to read file!"); } finally { try { inp.close(); view.setOutput("Done conversion.. " + model.getInputFilePath() + "\n"); model.setInputFilePath(null); model.setOutputFilePath(null); view.refreshIntputPath(); view.refreshOutputPath(); } catch (IOException e) { view.setOutput("Damn input stream.."); } } }