List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter
public DataFormatter()
From source file:org.matonto.etl.service.delimited.DelimitedConverterImpl.java
License:Open Source License
@Override public Model convert(ExcelConfig config) throws IOException, MatOntoException { String[] nextRow;/* w w w . java2 s . co m*/ Model convertedRDF = modelFactory.createModel(); ArrayList<ClassMapping> classMappings = parseClassMappings(config.getMapping()); try { Workbook wb = WorkbookFactory.create(config.getData()); Sheet sheet = wb.getSheetAt(0); DataFormatter df = new DataFormatter(); boolean containsHeaders = config.getContainsHeaders(); long offset = config.getOffset(); Optional<Long> limit = config.getLimit(); //Traverse each row and convert column into RDF for (Row row : sheet) { // If headers exist or the row is before the offset point, skip the row if ((containsHeaders && row.getRowNum() == 0) || row.getRowNum() - (containsHeaders ? 1 : 0) < offset || (limit.isPresent() && row.getRowNum() >= limit.get() + offset)) { continue; } nextRow = new String[row.getPhysicalNumberOfCells()]; int cellIndex = 0; for (Cell cell : row) { nextRow[cellIndex] = df.formatCellValue(cell); cellIndex++; } writeClassMappingsToModel(convertedRDF, nextRow, classMappings); } } catch (InvalidFormatException e) { throw new MatOntoException(e); } return convertedRDF; }
From source file:org.opentestsystem.delivery.testreg.upload.ExcelUtils.java
License:Open Source License
private Object[] getCellValues(final Row row, final MissingCellPolicy policy) { final List<Object> excelColumnList = new ArrayList<Object>(); final DataFormatter dataFormat = new DataFormatter(); final int lastCellNo = row.getLastCellNum(); for (int cellNum = 0; cellNum < lastCellNo; cellNum++) { final Cell cell = row.getCell(cellNum, policy); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: excelColumnList.add(cell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_BOOLEAN: cell.setCellType(XSSFCell.CELL_TYPE_STRING); excelColumnList.add(cell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_NUMERIC: excelColumnList.add(dataFormat.formatCellValue(cell)); break; case XSSFCell.CELL_TYPE_BLANK: excelColumnList.add(""); break; case XSSFCell.CELL_TYPE_FORMULA: excelColumnList.add(cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_ERROR: excelColumnList.add(cell.getErrorCellValue()); break; default: excelColumnList.add(cell.toString()); }//from w w w . ja v a2 s .c o m } } return excelColumnList.toArray(new Object[excelColumnList.size()]); }
From source file:org.project.utilities.ExcelRead.java
public ArrayList readexl(File exfile, String exfilename) { ArrayList storvalues = new ArrayList(); try {//w ww .j ava 2 s .co m // File file2 = new File("/home/asl/Desktop/html work/ProgramFile/test_template.xls"); FileInputStream file = new FileInputStream(exfile); //Workbook workbook = null; // String name = file2.getName(); String name = exfilename; Workbook workbook = null; if (name.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(file); } else if (name.toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(file); } // workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL); DataFormatter fmt = new DataFormatter(); for (int sn = 0; sn < workbook.getNumberOfSheets(); sn++) { Sheet sheet = workbook.getSheetAt(sn); for (int rn = sheet.getFirstRowNum() + 1; rn <= sheet.getLastRowNum(); rn++) { Row row = sheet.getRow(rn); if (row == null) { // There is no data in this row, handle as needed } else { // Row "rn" has data ArrayList storeval = new ArrayList(); // System.out.println("size " + row.getLastCellNum()); for (int cn = 0; cn < 17; cn++) { // for (int cn = 0; cn < row.getLastCellNum(); cn++) { Cell cell = row.getCell(cn); // cell.setCellType(Cell.CELL_TYPE_STRING); String val = ""; // String strCellValue = ""; if (cell == null) { // This cell is empty/blank/un-used, handle as needed } else { String cellStr = fmt.formatCellValue(cell); val = cellStr; // Do something with the value } storeval.add(val); } storvalues.add(storeval); } } } } catch (Exception e) { e.printStackTrace(); } return storvalues; }
From source file:org.rakuten.util.XLSUtility.java
License:Open Source License
/** * @param file// w ww . j a v a 2 s. co m * @return map of testData * * This method take file name for test data and convert it into map of testCaseId and Object of testData * Excel file is expected to have at-least 3 columns else that row will be skipped, testCase id is supposed * to be unique other wise test data will be overridden * This method will return null if file does not exists or if file does not contains any data row */ public Map<Integer, TestData> getTestData(String file) { Iterator<Row> rows = getSheetData(file); DataFormatter fmt = new DataFormatter(); if (rows == null) return null; Map<Integer, TestData> testData = new HashMap<>(); while (rows.hasNext()) { Row nextRow = rows.next(); if (nextRow.getPhysicalNumberOfCells() >= 3) { try { Cell testCaseId = nextRow.getCell(0, Row.RETURN_BLANK_AS_NULL); Cell request = nextRow.getCell(1); Cell response = nextRow.getCell(2); TestData testData2 = new TestData(request.getStringCellValue(), response.getStringCellValue()); testData.put(Integer.parseInt(fmt.formatCellValue(testCaseId)), testData2); } catch (Exception e) { e.printStackTrace(); } } } return testData; }
From source file:org.teiid.translator.excel.ExcelExecutionFactory.java
License:Apache License
@Override public ResultSetExecution createResultSetExecution(QueryExpression command, ExecutionContext executionContext, RuntimeMetadata metadata, VirtualFileConnection connection) throws TranslatorException { ExcelExecution ex = new ExcelExecution((Select) command, executionContext, metadata, connection); if (formatStrings) { ex.setDataFormatter(new DataFormatter()); //assume default locale }/*from w ww.j a va 2 s . co m*/ return ex; }
From source file:org.teiid.translator.excel.ExcelExecutionFactory.java
License:Apache License
@Override public ExcelUpdateExecution createUpdateExecution(Command command, ExecutionContext executionContext, RuntimeMetadata metadata, VirtualFileConnection connection) throws TranslatorException { ExcelUpdateExecution ex = new ExcelUpdateExecution(command, executionContext, metadata, connection); if (formatStrings) { ex.setDataFormatter(new DataFormatter()); //assume default locale }/*from w ww.j a v a 2s . c o m*/ return ex; }
From source file:org.testeditor.core.importer.ExcelFileImporter.java
License:Open Source License
/** * gets the testData from a numeric cell. * /*from w ww .j av a 2 s . c o m*/ * @param testDataRow * TestDataRow * @param cell * HSSFCell */ protected void getTestDataNumericCell(TestDataRow testDataRow, HSSFCell cell) { if (HSSFDateUtil.isCellDateFormatted(cell)) { double value = cell.getNumericCellValue(); if (HSSFDateUtil.isValidExcelDate(value)) { Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()); DateFormat format = new SimpleDateFormat(JAVA_TOSTRING); testDataRow.add(format.format(date)); } } else { DataFormatter df = new DataFormatter(); Format cellFormat = df.createFormat(cell); if (cellFormat instanceof DecimalFormat) { String pattern = ((DecimalFormat) cellFormat).toPattern(); DecimalFormat dFormatter = new DecimalFormat(pattern); testDataRow.add(dFormatter.format(cell.getNumericCellValue())); } else { testDataRow.add(String.valueOf(cell.getNumericCellValue())); } } }
From source file:org.wise.portal.presentation.web.controllers.run.MergeSpreadsheetsController.java
License:Open Source License
@RequestMapping(method = RequestMethod.POST) protected ModelAndView onSubmit(@RequestParam("uploadFile") MultipartFile uploadFile, @RequestParam("mergeColumnTitle") String mergeColumnTitle, HttpServletResponse response) throws Exception { // TODO: this line is saving uploadFile to home directory. Can we do without saving to home directory? File file = multipartToFile(uploadFile); String mergedResultFileName = "merged_" + file.getName(); FileInputStream fis = new FileInputStream(file); // Finds the workbook instance of XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fis); DataFormatter objDefaultFormat = new DataFormatter(); FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); // number of sheets in the workbook int numberOfSheets = workbook.getNumberOfSheets(); // contains all values of the merge column across all sheets ArrayList<String> mergeColumnValues = new ArrayList<String>(); // maps mergeColumn value to a Map<SheetIndex, ArrayList<Row>> HashMap<String, HashMap<Integer, ArrayList<Row>>> mergeColumnValueToSheetRows = new HashMap<String, HashMap<Integer, ArrayList<Row>>>(); // maps sheet index to the headers in that sheet HashMap<Integer, ArrayList<String>> sheetIndexToSheetColumnHeaders = new HashMap<Integer, ArrayList<String>>(); // how many copies of headers need to be created for each sheet HashMap<Integer, Integer> sheetIndexToMaxSheetRowCount = new HashMap<Integer, Integer>(); // loop through the sheets in the workbook and populate the variables for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { XSSFSheet sheet = workbook.getSheetAt(sheetIndex); int mergeColumnIndex = -1; // index of the merge column in this sheet int rowIteratorIndex = 0; // index of current row iteration // collect all of the merge column rows in each sheet Iterator<Row> rowIterator = sheet.rowIterator(); int maxSheetRowCountForCurrentSheet = 0; while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (rowIteratorIndex == 0) { // for the very first row in this sheet, go through all the cells in the top row and add to sheetColumnHeaders // and add it to sheetIndexToSheetColumnHeaders ArrayList<String> sheetColumnHeaders = new ArrayList<String>(); int rowCellIteratorIndex = 0; Iterator<Cell> topRowCellIterator = row.cellIterator(); while (topRowCellIterator.hasNext()) { Cell topRowCell = topRowCellIterator.next(); String topRowCellString = topRowCell.toString(); if (!topRowCellString.isEmpty()) { sheetColumnHeaders.add(topRowCellString); }//from ww w . j a v a 2 s .c o m if (!topRowCellString.isEmpty() && topRowCellString.equals(mergeColumnTitle)) { // this is the mergeColumn. Remember the column index if (mergeColumnIndex == -1) { mergeColumnIndex = rowCellIteratorIndex; } else { // there are multiple mergeColumnTitles in this sheet. Let the user know and exit ModelAndView mav = new ModelAndView("/admin/run/mergespreadsheets"); mav.addObject("errorMsg", "You have multiple columns titled \"" + mergeColumnTitle + "\" in worksheet #" + (sheetIndex + 1) + ". You can have only one merge column per worksheet. Please fix and try again."); return mav; } } rowCellIteratorIndex++; } sheetIndexToSheetColumnHeaders.put(sheetIndex, sheetColumnHeaders); } else { // for rows that are not the top row (header) // 1. get all the mergeColumnValues // 2. populate mergeColumnValueToSheetRows // 3. calculate sheetIndexToMaxSheetRowCount Cell mergeColumnValueCell = row.getCell(mergeColumnIndex); if (mergeColumnValueCell != null && !mergeColumnValueCell.toString().isEmpty()) { objFormulaEvaluator.evaluate(mergeColumnValueCell); String mergeColumnValueString = objDefaultFormat.formatCellValue(mergeColumnValueCell, objFormulaEvaluator); HashMap<Integer, ArrayList<Row>> sheetIndexToSheetRows = mergeColumnValueToSheetRows .get(mergeColumnValueString); if (sheetIndexToSheetRows == null) { sheetIndexToSheetRows = new HashMap<Integer, ArrayList<Row>>(); mergeColumnValueToSheetRows.put(mergeColumnValueString, sheetIndexToSheetRows); } ArrayList<Row> sheetRows = sheetIndexToSheetRows.get(sheetIndex); if (sheetRows == null) { sheetRows = new ArrayList<>(); sheetIndexToSheetRows.put(sheetIndex, sheetRows); } sheetRows.add(row); if (sheetRows.size() > maxSheetRowCountForCurrentSheet) { maxSheetRowCountForCurrentSheet = sheetRows.size(); } Iterator<Cell> rowCellIterator = row.cellIterator(); int rowCellIteratorIndex = 0; while (rowCellIterator.hasNext()) { Cell rowCell = rowCellIterator.next(); if (rowCellIteratorIndex == mergeColumnIndex) { // this is a merge column cell, so add its value to mergeColumnValues if (!rowCell.toString().isEmpty()) { objFormulaEvaluator.evaluate(rowCell); String rowCellValueString = objDefaultFormat.formatCellValue(rowCell, objFormulaEvaluator); if (!mergeColumnValues.contains(rowCellValueString)) { mergeColumnValues.add(rowCellValueString); } } } rowCellIteratorIndex++; } } } rowIteratorIndex++; } sheetIndexToMaxSheetRowCount.put(sheetIndex, maxSheetRowCountForCurrentSheet); } // Now we are ready to make the merge sheet. We will be writing one row at a time. Workbook wb = new XSSFWorkbook(); // new output workbook Sheet mergedSheet = wb.createSheet("merged"); // output merged result in "merged" sheet // make the header row Row headerRow = mergedSheet.createRow(0); // (0,0) will be the merge cell header. Column 0 will contain mergeColumnValues. Cell mergeColumnHeaderCell = headerRow.createCell(0); mergeColumnHeaderCell.setCellValue(mergeColumnTitle); // current column index "cursor" where we will be writing to int cellIndexWithoutMergeColumn = 1; // make the header row for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex); ArrayList<String> sheetColumnHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex); XSSFSheet sheet = workbook.getSheetAt(sheetIndex); String sheetName = sheet.getSheetName(); for (int i = 0; i < maxSheetRowCount; i++) { for (int sheetColumnHeaderIndex = 0; sheetColumnHeaderIndex < sheetColumnHeaders .size(); sheetColumnHeaderIndex++) { String sheetColumnHeader = sheetColumnHeaders.get(sheetColumnHeaderIndex); if (!sheetColumnHeader.isEmpty() && !sheetColumnHeader.equals(mergeColumnTitle)) { String newSheetColumnHeader = sheetColumnHeader + " ( " + sheetName + " " + (i + 1) + " ) "; Cell headerCell = headerRow.createCell(cellIndexWithoutMergeColumn); headerCell.setCellValue(newSheetColumnHeader); cellIndexWithoutMergeColumn++; } } } } // now make all the non-header rows for (int mergeColumnValueIndex = 0; mergeColumnValueIndex < mergeColumnValues .size(); mergeColumnValueIndex++) { String mergeColumnValue = mergeColumnValues.get(mergeColumnValueIndex); HashMap<Integer, ArrayList<Row>> mergeColumnValueSheetRow = mergeColumnValueToSheetRows .get(mergeColumnValue); if (mergeColumnValueSheetRow == null) { System.out.println("Null mergeColumnValueSheetRow, continuing. mergeColumnValueIndex: " + mergeColumnValueIndex + " mergeColumnValue: " + mergeColumnValue); continue; } Row row = mergedSheet.createRow(mergeColumnValueIndex + 1); // + 1 is to account for the header row; // reset current cursor as we make each row cellIndexWithoutMergeColumn = 0; // first column will be the merge column value Cell mergeColumnCell = row.createCell(0); mergeColumnCell.setCellValue(mergeColumnValue); cellIndexWithoutMergeColumn++; for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { ArrayList<Row> sheetRows = mergeColumnValueSheetRow.get(sheetIndex); int currentSheetSheetRowIndex = 0; ArrayList<String> sheetHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex); if (sheetRows != null) { for (int sheetRowIndex = 0; sheetRowIndex < sheetRows.size(); sheetRowIndex++) { Row sheetRow = sheetRows.get(sheetRowIndex); for (int sheetHeaderIndex = 0; sheetHeaderIndex < sheetHeaders.size(); sheetHeaderIndex++) { String sheetHeader = sheetHeaders.get(sheetHeaderIndex); if (!sheetHeader.equals(mergeColumnTitle)) { Cell cell = sheetRow.getCell(sheetHeaderIndex); Cell exportCell = row.createCell(cellIndexWithoutMergeColumn); objFormulaEvaluator.evaluate(cell); String cellString = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator); exportCell.setCellValue(cellString); cellIndexWithoutMergeColumn++; } } currentSheetSheetRowIndex++; } } // some columns do not have any values to populate, so populate them with empty cells Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex); while (currentSheetSheetRowIndex < maxSheetRowCount) { for (int i = 0; i < sheetHeaders.size(); i++) { String sheetHeader = sheetHeaders.get(i); if (!sheetHeader.isEmpty() && !sheetHeader.equals(mergeColumnTitle)) { Cell exportCell = row.createCell(cellIndexWithoutMergeColumn); exportCell.setCellValue(""); cellIndexWithoutMergeColumn++; } } currentSheetSheetRowIndex++; } } } // write to response output response.setHeader("Content-Disposition", "attachment; filename=\"" + mergedResultFileName + "\""); ServletOutputStream outputStream = response.getOutputStream(); wb.write(outputStream); fis.close(); return null; }
From source file:org.wso2.security.tool.adapter.ExcelInputAdapter.java
License:Open Source License
/** * Converts the data in the files with .xlsx extension to the JSON format. * A workbook is created from the the excel file (.xlsx) and while iterating through the sheets in the workbook; * the data is read and set in to a JSONObject. The JSONObject returned by the method contains an array of * row objects corresponding to each row in the workbook. A row object contains values of each cell in a given row, * with key values starting from letter 'A'. * * @param dataFilePath The path where the data file uploaded is saved. * @return returns the JSON object that contains all the data in the .xlsx file. * @throws FeedbackToolException If the .xlsx file is not found in the given path or due to an error in * parsing the data in the data file. *//*from w w w .j a v a 2s. c o m*/ @Override public JSONObject convert(String dataFilePath) throws FeedbackToolException { // JSONObject to hold the array of row objects JSONObject dataJSONObject = new JSONObject(); try { Workbook workbook = WorkbookFactory.create(new File(dataFilePath)); logInfo = "Workbook has " + workbook.getNumberOfSheets() + " sheets"; log.info(logInfo); Iterator<Sheet> sheetIterator = workbook.sheetIterator(); // JSONArray to hold all the row objects JSONArray rowsJSONArray = new JSONArray(); while (sheetIterator.hasNext()) { Sheet sheet = sheetIterator.next(); logInfo = "Sheet: " + sheet.getSheetName() + " has " + sheet.getNumMergedRegions() + " merged regions"; log.info(logInfo); DataFormatter dataFormatter = new DataFormatter(); logInfo = "Iterating over Rows and Columns using for-each loop"; log.info(logInfo); for (Row row : sheet) { // JSONObject to hold the data in the cells of a given row JSONObject rowJSONObject = new JSONObject(); char keyLetter = 'A'; for (Cell cell : row) { String cellValue = dataFormatter.formatCellValue(cell); rowJSONObject.put(keyLetter, cellValue); ++keyLetter; } rowsJSONArray.add(rowJSONObject); } } dataJSONObject.put(Constants.JSON_DATA_OBJECT, rowsJSONArray); } catch (InvalidFormatException e) { throw new FeedbackToolException("Error in parsing the data file uploaded", e); } catch (IOException e) { throw new FeedbackToolException("Data file was not found in the specified location", e); } return dataJSONObject; }
From source file:PlacementFormatter.Controller.FileController.java
/** * * @param filepath/*from w w w. j av a2 s .c o m*/ * @throws IOException */ public static void formatFile(PlacementFile filepath) throws IOException { //Creates instance for reading xls, workbook,sheet, FileInputStream InputStream ExcelFileToRead = new FileInputStream(filepath.getFilepath()); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); //Creates instances for writing output to xls format. String sheetName = "Import";//name of sheet HSSFWorkbook outWorkbook = new HSSFWorkbook(); HSSFSheet outSheet = outWorkbook.createSheet(sheetName); //Variables to hold the data without ' and r for the row counter String cellReplace; int r = 0; //Outer and Inner loop for iterating through the workbook for (Row row : sheet) { HSSFRow outRow = outSheet.createRow(r); for (int cn = 0; cn < row.getLastCellNum(); cn++) { // If the cell is missing from the file, generate a blank one // (Works by specifying a MissingCellPolicy) org.apache.poi.ss.usermodel.Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK); //NumberToTextConverter nc = new NumberToTextConverter.toText(); //or DataFormatter df = new DataFormatter(); String dataCell = df.formatCellValue(cell); //Replaces the single dash located in the data cellReplace = dataCell.replace("'", ""); HSSFCell outCell = outRow.createCell(cn); outCell.setCellValue(cellReplace); //System.out.println("CELL: " + cn + " --> " + cellReplace); } //ends inner loop r++; } //ends outer loop FileOutputStream fileOut = new FileOutputStream(filepath.getFilepath().replace("xlsx", "xls")); outWorkbook.write(fileOut); fileOut.flush(); System.out.print("File Exported Correclty"); }