List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getRawValue
public String getRawValue()
If the cell contains a string, then this value is an index into the shared string table, pointing to the actual string value.
From source file:com.rknowsys.eapp.DataImportAction.java
/** * This method saves uploaded file into the server folder.And stores the * file data into the database.//from w ww .ja v a 2 s .com * * @param actionRequest * @param actionResponse * @throws IOException */ public void saveDataImport(ActionRequest actionRequest, ActionResponse actionResponse) throws IOException { System.out.println("saveDataImport method()..!!!!!!!!!!"); ThemeDisplay themeDisplay = (ThemeDisplay) actionRequest.getAttribute(WebKeys.THEME_DISPLAY); Properties properties = PortalUtil.getPortalProperties(); String uploadDirectory = properties.getProperty("liferay.home") + "/data/uploadedFiles"; UploadPortletRequest uploadRequest = PortalUtil.getUploadPortletRequest(actionRequest); byte[] bytes = null; try { // ==========Saving the uploaded file in server folder with uploaded // date and time as file filename prefix.=========== Date date = new Date(); SimpleDateFormat sd = new SimpleDateFormat("mm-dd-yyyy"); String d = sd.format(date); System.out.println("uploaded date = " + d); File uploadedFile = uploadRequest.getFile("fileName"); bytes = FileUtil.getBytes(uploadedFile); String fileName = uploadRequest.getFileName("fileName"); File newFile = null; File newDirectory = new File(uploadDirectory); if (!newDirectory.exists()) { System.out.println("directory does not exist"); Path directoryPath = Paths.get(uploadDirectory); Files.createDirectory(directoryPath.getParent()); } newFile = new File(uploadDirectory + "/" + d + Calendar.getInstance().getTimeInMillis() + fileName); // ============Creating the New file in server folder=========== if (!newFile.exists()) { System.out.println("file does not exist"); Path pathToFile = Paths .get(uploadDirectory + "/" + d + Calendar.getInstance().getTimeInMillis() + fileName); Files.createFile(pathToFile); } // =========Reading the uploaded file content and writing the // content to newly created file============== FileInputStream fileInputStream = new FileInputStream(uploadedFile); fileInputStream.read(bytes); FileOutputStream fileOutputStream = new FileOutputStream(newFile); fileOutputStream.write(bytes, 0, bytes.length); fileOutputStream.close(); fileInputStream.close(); String filePath = newFile.getAbsolutePath(); System.out.println("filePath = " + filePath); FileInputStream file1 = new FileInputStream(new File(filePath)); // Reading Excel file Rows and cells content using apache poi api // and saving the data in to the database. XSSFWorkbook workbook = new XSSFWorkbook(file1); // Create Workbook // instance // holding // reference to // .xlsx file XSSFSheet sheet = workbook.getSheetAt(0); // Get first/desired sheet // from the workbook @SuppressWarnings("rawtypes") Iterator rows = sheet.rowIterator(); // Iterate through each rows // one by one while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); if (row.getRowNum() != 0) { EmpPersonalDetails empPersonalDetails = EmpPersonalDetailsLocalServiceUtil .createEmpPersonalDetails(CounterLocalServiceUtil.increment()); Employee employee = EmployeeLocalServiceUtil .createEmployee(CounterLocalServiceUtil.increment()); JobTitle jobTitle = JobTitleLocalServiceUtil .createJobTitle(CounterLocalServiceUtil.increment()); SubUnit subUnit = SubUnitLocalServiceUtil.createSubUnit(CounterLocalServiceUtil.increment()); EmploymentStatus employmentStatus = EmploymentStatusLocalServiceUtil .createEmploymentStatus(CounterLocalServiceUtil.increment()); EmpJob empJob = EmpJobLocalServiceUtil.createEmpJob(CounterLocalServiceUtil.increment()); EmpSupervisor empSupervisor = EmpSupervisorLocalServiceUtil .createEmpSupervisor(CounterLocalServiceUtil.increment()); @SuppressWarnings("rawtypes") Iterator cells = row.cellIterator(); while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); if (cell.getColumnIndex() == 0) { empPersonalDetails.setFirstName(cell.toString()); } if (cell.getColumnIndex() == 1) { empPersonalDetails.setMiddleName(cell.toString()); } if (cell.getColumnIndex() == 2) { empPersonalDetails.setLastName(cell.toString()); } if (cell.getColumnIndex() == 3) { empPersonalDetails.setEmployeeNo(cell.getRawValue()); } if (cell.getColumnIndex() == 4) { empPersonalDetails.setLicenseNo(cell.getRawValue()); } if (cell.getColumnIndex() == 5) { jobTitle.setTitle(cell.toString()); } if (cell.getColumnIndex() == 6) { employmentStatus.setEmploymentstatus(cell.toString()); } if (cell.getColumnIndex() == 7) { subUnit.setName(cell.toString()); } } employee.setUserId(themeDisplay.getUserId()); employee.setGroupId(themeDisplay.getCompanyGroupId()); employee.setCompanyId(themeDisplay.getCompanyId()); employee.setCreateDate(date); employee.setModifiedDate(date); employee = EmployeeLocalServiceUtil.addEmployee(employee); empPersonalDetails.setUserId(themeDisplay.getUserId()); empPersonalDetails.setGroupId(themeDisplay.getCompanyGroupId()); empPersonalDetails.setCompanyId(themeDisplay.getCompanyId()); empPersonalDetails.setCreateDate(date); empPersonalDetails.setModifiedDate(date); empPersonalDetails.setEmployeeId(employee.getEmployeeId()); empPersonalDetails = EmpPersonalDetailsLocalServiceUtil .addEmpPersonalDetails(empPersonalDetails); jobTitle.setUserId(themeDisplay.getUserId()); jobTitle.setGroupId(themeDisplay.getCompanyGroupId()); jobTitle.setCompanyId(themeDisplay.getCompanyId()); jobTitle.setCreateDate(date); jobTitle.setModifiedDate(date); jobTitle = JobTitleLocalServiceUtil.addJobTitle(jobTitle); subUnit.setUserId(themeDisplay.getUserId()); subUnit.setGroupId(themeDisplay.getCompanyGroupId()); subUnit.setCompanyId(themeDisplay.getCompanyId()); subUnit.setCreateDate(date); subUnit.setModifiedDate(date); subUnit = SubUnitLocalServiceUtil.addSubUnit(subUnit); employmentStatus.setUserId(themeDisplay.getUserId()); employmentStatus.setGroupId(themeDisplay.getCompanyGroupId()); employmentStatus.setCompanyId(themeDisplay.getCompanyId()); employmentStatus.setCreateDate(date); employmentStatus.setModifiedDate(date); employmentStatus = EmploymentStatusLocalServiceUtil.addEmploymentStatus(employmentStatus); empJob.setJobTitleId(employee.getEmployeeId()); empJob.setEmploymentStatusId(employmentStatus.getEmploymentStatusId()); empJob.setSubUnitId(subUnit.getSubUnitId()); empJob.setUserId(themeDisplay.getUserId()); empJob.setGroupId(themeDisplay.getCompanyGroupId()); empJob.setCompanyId(themeDisplay.getCompanyId()); empJob.setCreateDate(date); empJob.setModifiedDate(date); empJob.setEmployeeId(employee.getEmployeeId()); empJob = EmpJobLocalServiceUtil.addEmpJob(empJob); empSupervisor.setUserId(themeDisplay.getUserId()); empSupervisor.setGroupId(themeDisplay.getCompanyGroupId()); empSupervisor.setCompanyId(themeDisplay.getCompanyId()); empSupervisor.setCreateDate(date); empSupervisor.setModifiedDate(date); empSupervisor.setEmployeeId(employee.getEmployeeId()); empSupervisor.setReporterEmployeeId(empPersonalDetails.getEmployeeId()); empSupervisor = EmpSupervisorLocalServiceUtil.addEmpSupervisor(empSupervisor); } } file1.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:Creator.TaskManagerPanel.java
private void loadDefaultAlerts() { String path = "/Creator/textFiles/alerts.xlsx"; InputStream loc = this.getClass().getResourceAsStream(path); importedAlerts = new ArrayList<>(); try {//from w w w. j a v a 2 s . c o m XSSFWorkbook wb = new XSSFWorkbook(loc); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; String[] rowData; int rows, cols; // No of rows rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row != null) { cols = row.getPhysicalNumberOfCells(); rowData = new String[cols]; for (int j = 0; j < cols; j++) { cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case 1: // string rowData[j] = cell.getStringCellValue(); break; case 2: // int rowData[j] = String.valueOf(cell.getNumericCellValue()); break; case 3: // blank System.out.println("Blank data @ [" + i + "][" + j + "]"); rowData[j] = "no data @ [" + i + "][" + j + "]"; break; case 4: // boolean rowData[j] = String.valueOf(cell.getBooleanCellValue()); break; case 5: // error rowData[j] = String.valueOf(cell.getErrorCellString()); break; default: //System.out.println("default @ [" + i + "][" + j + "] = " + String.valueOf(cell.getRawValue())); rowData[j] = String.valueOf(cell.getRawValue()); break; } } else { System.out.println("null @ [" + i + "][" + j + "]"); rowData[j] = "nullValue @ [" + i + "][" + j + "]"; } } importedAlerts.add(rowData); } } wb.close(); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
From source file:fr.unice.i3s.rockflows.experiments.automatictest.ExcelUtils.java
public static String getStringValue(final XSSFCell cell) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); }/*from www.java 2s . co m*/ return cell.getRawValue(); }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetImporter.java
License:Open Source License
private static void traiterLigne(int ligne, XSSFSheet worksheet, Service service, StockService stockService) throws ImportExcelException { XSSFCell referenceCell = worksheet.getRow(ligne).getCell(1); if (StringUtils.isBlank(referenceCell.getRawValue())) { throw new ImportExcelException(ligne + 1, "La rfrence est introuvable"); }/*w w w . j a v a2s .c om*/ String reference = referenceCell.getStringCellValue(); XSSFCell stockReelCell = worksheet.getRow(ligne).getCell(4); if (StringUtils.isBlank(stockReelCell.getRawValue())) { throw new ImportExcelException(ligne + 1, reference, "La quantit relle n'est pas renseigne"); } if (stockReelCell.getCellTypeEnum() != CellType.NUMERIC) { throw new ImportExcelException(ligne + 1, reference, "La cellule 'Stock rl' n'est pas un entier"); } double value = stockReelCell.getNumericCellValue(); int stockReel = (int) value; if (value != stockReel) { throw new ImportExcelException(ligne + 1, reference, "La cellule 'Stock rl' n'est pas un entier"); } updateStock(service, reference, stockReel, stockService, ligne); }
From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadXlsxExcelJob.java
License:Open Source License
private void loadExcel(final String file) { final File fil = new File(file); if (fil.exists()) { canRead = true;// w w w . j a va 2s . c o m if (grid != null) { try { InputStream inp = new FileInputStream(file); try { wb = new XSSFWorkbook(inp); } catch (Exception e) { MsgDialog.message("Wrong format!\nOnly Excel *.xlsx (2007-2010) is supported!"); canRead = false; e.printStackTrace(); } // wb = new HSSFWorkbook(inp); } catch (IOException ex) { ex.printStackTrace(); } if (canRead) { for (s = 0; s < wb.getNumberOfSheets(); s++) { Display display = PlatformUI.getWorkbench().getDisplay(); display.syncExec(new Runnable() { public void run() { String name = fil.getName(); grid = new Spread().spread(SampleView.getTabFolder(), 0, 0, name); SampleView.setGrid(grid); XSSFSheet sheet = wb.getSheetAt(s); int colCount = grid.getColumnCount(); int rowCount = grid.getItemCount(); int exelRow = endOfRow(sheet); int exelColumn = endOfColumn(sheet); // System.out.println(exelRow + " " + exelColumn // + "---" + sheet.getPhysicalNumberOfRows() + // " " + // sheet.getRow(0).getPhysicalNumberOfCells()); if (colCount < exelColumn) { int diff = exelColumn - colCount; for (int i = 0; i < diff; i++) { GridColumn column = new GridColumn(grid, SWT.NONE); column.setText("C " + (i + 1 + colCount)); column.setWidth(50); } } if (rowCount < exelRow) { int diff = exelRow - rowCount; for (int i = 0; i < diff; i++) { new GridItem(grid, SWT.NONE).setHeight(16); } } // Iterate over each row in the sheet int rows = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < exelRow; i++) { XSSFRow row = sheet.getRow(i); if (row == null) { for (int u = 0; u < exelColumn; u++) { grid.getItem(i).setText(u, " "); } } else { for (int u = 0; u < exelColumn; u++) { XSSFCell cell = row.getCell(u); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: String val = String.valueOf(cell.getNumericCellValue()); grid.getItem(i).setText(u, val); break; case XSSFCell.CELL_TYPE_STRING: XSSFRichTextString st = cell.getRichStringCellValue(); String val2 = st.getString(); grid.getItem(i).setText(u, val2); break; case XSSFCell.CELL_TYPE_FORMULA: try { String val3 = String.valueOf(cell.getRawValue()); grid.getItem(i).setText(u, val3); } catch (Exception e) { // System.out.println(e.getMessage()); String s2 = cell.getCellFormula(); grid.getItem(i).setText(u, s2); } break; case XSSFCell.CELL_TYPE_BLANK: grid.getItem(i).setText(u, " "); break; case XSSFCell.CELL_TYPE_BOOLEAN: boolean s4 = cell.getBooleanCellValue(); if (s4) { grid.getItem(i).setText(u, "TRUE"); } else { grid.getItem(i).setText(u, "FALSE"); } break; default: break; } } else { grid.getItem(i).setText(u, " "); } } } } } }); } wb = null; } } } else { MsgDialog.message("File not found!"); } }
From source file:no.sintef.ict.splcatool.XLSXLib.java
License:Open Source License
public static String getCSV(File file) throws IOException { String filename = file.getAbsoluteFile().toString(); FileInputStream fis = new FileInputStream(filename); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(0); //System.out.println(" number of rows"+ sheet.getLastRowNum()); String csv = ""; int w = Integer.MAX_VALUE; outerloop: for (Row r : sheet) { int x = 0; XSSFRow row = (XSSFRow) r;/*from w w w. j a v a 2s .co m*/ for (Cell c : row) { if (x >= w) break; XSSFCell cell = (XSSFCell) c; String v = ""; if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { v = cell.getStringCellValue(); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { v = "" + (int) cell.getNumericCellValue(); } else { System.out.println("Unknown type " + cell.getCellType() + " " + cell.getRawValue()); System.exit(-1); } //System.out.println(v + ", " + x + ", " + w); if (x == 0 && v.equals("#end")) break outerloop; if (v.equals("#end")) { w = x; break; } csv += v + ";"; x++; } csv += "\n"; } //csv = csv.substring(0, csv.length()-1); //System.out.println(csv); return csv; }
From source file:org.dhatim.fastexcel.reader.FastExcelReaderTest.java
License:Apache License
private static String getRawValue(org.apache.poi.ss.usermodel.Cell cell) { XSSFCell xcell = ((XSSFCell) cell); return xcell == null ? null : xcell.getRawValue(); }
From source file:org.olat.search.service.document.file.ExcelOOXMLDocument.java
License:Apache License
private void extractContent(final StringBuilder buffy, final XSSFWorkbook document) { for (int i = 0; i < document.getNumberOfSheets(); i++) { final XSSFSheet sheet = document.getSheetAt(i); buffy.append(document.getSheetName(i)).append(' '); // Header(s), if present extractHeaderFooter(buffy, sheet.getFirstHeader()); extractHeaderFooter(buffy, sheet.getOddHeader()); extractHeaderFooter(buffy, sheet.getEvenHeader()); // Rows and cells for (final Object rawR : sheet) { final Row row = (Row) rawR; for (final Iterator<Cell> ri = row.cellIterator(); ri.hasNext();) { final Cell cell = ri.next(); if (cell.getCellType() == Cell.CELL_TYPE_FORMULA || cell.getCellType() == Cell.CELL_TYPE_STRING) { buffy.append(cell.getRichStringCellValue().getString()).append(' '); } else { final XSSFCell xc = (XSSFCell) cell; final String rawValue = xc.getRawValue(); if (rawValue != null) { buffy.append(rawValue).append(' '); }//from w w w.ja v a 2 s . co m } // Output the comment in the same cell as the content final Comment comment = cell.getCellComment(); if (comment != null) { buffy.append(comment.getString().getString()).append(' '); } } } // Finally footer(s), if present extractHeaderFooter(buffy, sheet.getFirstFooter()); extractHeaderFooter(buffy, sheet.getOddFooter()); extractHeaderFooter(buffy, sheet.getEvenFooter()); } }
From source file:uk.ac.bbsrc.tgac.miso.core.util.FormUtils.java
License:Open Source License
private static String getCellValueAsString(XSSFCell cell) { if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: return null; case XSSFCell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case XSSFCell.CELL_TYPE_ERROR: return cell.getErrorCellString(); case XSSFCell.CELL_TYPE_FORMULA: return cell.getRawValue(); case XSSFCell.CELL_TYPE_NUMERIC: return String.valueOf(cell.getNumericCellValue()); case XSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue(); default:/*from w w w . jav a 2s . c o m*/ return null; } } return null; }