List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter
public DataFormatter()
From source file:CellStyleDetails.java
License:Apache License
public static void main(String[] args) throws Exception { if (args.length == 0) { throw new IllegalArgumentException("Filename must be given"); }/* ww w .j ava 2 s . c om*/ Workbook wb = WorkbookFactory.create(new File(args[0])); DataFormatter formatter = new DataFormatter(); for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { Sheet sheet = wb.getSheetAt(sn); System.out.println("Sheet #" + sn + " : " + sheet.getSheetName()); for (Row row : sheet) { System.out.println(" Row " + row.getRowNum()); for (Cell cell : row) { CellReference ref = new CellReference(cell); System.out.print(" " + ref.formatAsString()); System.out.print(" (" + cell.getColumnIndex() + ") "); CellStyle style = cell.getCellStyle(); System.out.print("Format=" + style.getDataFormatString() + " "); System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " "); System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " "); Font font = wb.getFontAt(style.getFontIndex()); System.out.print("Font=" + font.getFontName() + " "); System.out.print("FontColor="); if (font instanceof HSSFFont) { System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb))); } if (font instanceof XSSFFont) { System.out.print(renderColor(((XSSFFont) font).getXSSFColor())); } System.out.println(); System.out.println(" " + formatter.formatCellValue(cell)); } } System.out.println(); } }
From source file:com.adobe.acs.commons.data.Variant.java
License:Apache License
private void setValue(Cell cell) { int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) { cellType = cell.getCachedFormulaResultType(); }//from ww w .j a v a 2s .co m switch (cellType) { case Cell.CELL_TYPE_BOOLEAN: setValue(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: double number = cell.getNumericCellValue(); if (Math.floor(number) == number) { setValue((long) number); } else { setValue(number); } if (DateUtil.isCellDateFormatted(cell)) { setValue(cell.getDateCellValue()); } DataFormatter dataFormatter = new DataFormatter(); if (cellType == Cell.CELL_TYPE_FORMULA) { setValue(dataFormatter.formatCellValue(cell)); } else { CellStyle cellStyle = cell.getCellStyle(); setValue(dataFormatter.formatRawCellContents(cell.getNumericCellValue(), cellStyle.getDataFormat(), cellStyle.getDataFormatString())); } break; case Cell.CELL_TYPE_STRING: setValue(cell.getStringCellValue().trim()); break; case Cell.CELL_TYPE_BLANK: default: clear(); break; } }
From source file:com.assentisk.controller.OrganizationController.java
private ModelAndView importEmploye(HttpServletRequest req, HttpServletResponse res) throws Exception { System.out.println("hiiii heree......."); MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; MultipartFile multipartFile = multipartRequest.getFile("myexcel"); String fileName = multipartFile.getOriginalFilename(); map = new ModelMap(); String name = req.getParameter("fileName"); int status = 0; java.util.Date dt = new java.util.Date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String currentTime = sdf.format(dt); InetAddress ip = InetAddress.getLocalHost(); String userId = (String) req.getSession().getAttribute("UserRecordID"); byte[] b = multipartFile.getBytes(); FileOutputStream fout = new FileOutputStream(new File(fileName)); fout.write(b);/* ww w. jav a 2 s . co m*/ fout.flush(); fout.close(); try { String DepartmentID = "", LocationID = "", EmpName = "", Email1 = "", Email2 = "", address = ""; String city = "", zip = "", phone1 = "", locCoun = "", locstate = "", loccity = "", locContact = "", locEmail = ""; String phone2 = "", divisions = "", email2 = "", username = "", reporting = "", photo = ""; int isLicense = 0; int isactive = 1; int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; String[] data = null; List<String> dataStatus = new ArrayList<String>(); int val = 0; //Reading .xls files if (fileName.toLowerCase().endsWith(".xls")) { // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { while (rowIterator.hasNext()) { short cellIndex = 0; HSSFRow hsrow = (HSSFRow) rowIterator.next(); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { EmpName = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { EmpName = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); val++; } try { DepartmentID = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { DepartmentID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); val++; } try { LocationID = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { LocationID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Location -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); val++; } try { locCoun = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { locCoun = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Country - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country- " + e.getMessage()); val++; } try { locstate = hsrow.getCell((short) 4).getStringCellValue(); } catch (Exception e) { locstate = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location State - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State- " + e.getMessage()); val++; } try { loccity = hsrow.getCell((short) 5).getStringCellValue(); } catch (Exception e) { loccity = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location City -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City- " + e.getMessage()); val++; } try { locContact = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { locContact = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Contact Name -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name- " + e.getMessage()); val++; } try { locEmail = hsrow.getCell((short) 7).getStringCellValue(); boolean isValid = false; try { // // Create InternetAddress object and validated the supplied // address which is this case is an email address. InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { locEmail = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Email -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); val++; } try { divisions = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { divisions = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); val++; } try { address = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { address = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); val++; } try { city = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); val++; } try { zip = String.valueOf(hsrow.getCell((short) 11).getNumericCellValue()); zip = String.valueOf(zip).split("\\.")[0]; } catch (Exception e) { zip = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone1 = String.valueOf(hsrow.getCell((short) 12).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 12)); phone1 = df2; phone1 = String.valueOf(phone1).split("\\.")[0]; } catch (Exception e) { phone1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone2 = String.valueOf(hsrow.getCell((short) 13).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 13)); phone2 = df2; System.out.println("check phone1" + phone2); phone2 = String.valueOf(phone2).split("\\.")[0]; } catch (Exception e) { phone2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 - " + e.getMessage()); val++; } try { boolean isValid = false; Email1 = hsrow.getCell((short) 14).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); val++; } try { boolean isValid = false; Email2 = hsrow.getCell((short) 15).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); val++; } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + hsrow.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + hsrow.getRowNum() + "Not Inserted Row : " + EmpName); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } } //Reading .xlsx files else if (fileName.toLowerCase().endsWith(".xlsx")) { // Get the workbook instance for XLS file XSSFWorkbook wBook = new XSSFWorkbook(file); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cite = row.cellIterator(); if (row.getRowNum() != 0) { while (cite.hasNext()) { Cell c = cite.next(); if (c.getColumnIndex() == 0) { try { EmpName = c.toString(); } catch (Exception e) { EmpName = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": EmpName - " + e.getMessage()); } } if (c.getColumnIndex() == 1) { try { DepartmentID = c.toString(); } catch (Exception e) { DepartmentID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Department - " + e.getMessage()); } } if (c.getColumnIndex() == 2) { try { LocationID = c.toString(); } catch (Exception e) { LocationID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Location -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { locCoun = c.toString(); } catch (Exception e) { locCoun = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Country - " + e.getMessage()); } } if (c.getColumnIndex() == 4) { try { locstate = c.toString(); } catch (Exception e) { locstate = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location State - " + e.getMessage()); } } if (c.getColumnIndex() == 5) { try { loccity = c.toString(); } catch (Exception e) { loccity = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location City -" + e.getMessage()); } } if (c.getColumnIndex() == 6) { try { locContact = c.toString(); } catch (Exception e) { locContact = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Contact Name -" + e.getMessage()); } } if (c.getColumnIndex() == 7) { try { locEmail = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": locEmail - " + e.getMessage()); } } catch (Exception e) { locEmail = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Email -" + e.getMessage()); } } if (c.getColumnIndex() == 8) { try { divisions = c.toString(); } catch (Exception e) { divisions = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Divisions - " + e.getMessage()); } } if (c.getColumnIndex() == 9) { try { address = c.toString(); } catch (Exception e) { address = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": address - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { city = c.toString(); } catch (Exception e) { city = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": city - " + e.getMessage()); } } if (c.getColumnIndex() == 11) { try { zip = c.toString(); } catch (Exception e) { zip = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": zip - " + e.getMessage()); } } if (c.getColumnIndex() == 12) { try { DataFormatter formatter = new DataFormatter(); phone1 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone1 = df2; } catch (Exception e) { phone1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone1 - " + e.getMessage()); } } if (c.getColumnIndex() == 13) { try { DataFormatter formatter = new DataFormatter(); phone2 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone2 = df2; } catch (Exception e) { phone2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone2 -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { Email1 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { Email2 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + row.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + row.getRowNum() + "Not Inserted Row EmpName : " + EmpName); notcount = notcount + 1; } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { dataStatus.add(notcount + " Rows are not inserted"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } if (dataStatus.size() > 0) { map.addObject("dataStatus", dataStatus); map.addObject("datasize", "true"); } else { map.addObject("datasize", "false"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } catch (FileNotFoundException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (IOException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (Exception e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } return new ModelAndView("organization/employees", map); }
From source file:com.celtris.exparse.parser.ExcelReader.java
License:Apache License
public List<SheetData<T>> readExcel(String absolutePath, Class<T> excelModelClass, boolean headerExtraction) throws IOException, InstantiationException, IllegalAccessException { FileInputStream file = new FileInputStream(new File(absolutePath)); // Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); DataFormatter objDefaultFormat = new DataFormatter(); FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator(workbook); Iterator<Sheet> sheetIterator = workbook.iterator(); List<SheetData<T>> sheetDataList = new ArrayList<SheetData<T>>(workbook.getNumberOfSheets()); int sheetCount = 0; while (sheetIterator.hasNext()) { sheetCount++;/*from w ww. jav a 2 s .com*/ ExcelParser<T> excelParser = new ExcelParser<T>(headerExtraction, excelModelClass); Sheet sheet = sheetIterator.next(); Iterator<Row> rowIterator = sheet.iterator(); int rowCount = 0; // Evaluating header if (headerExtraction) { if (rowIterator.hasNext()) { rowCount++; Field[] fields = excelModelClass.getFields(); List<String> heaaderStr = new ArrayList<String>(fields.length); Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellStrValue = cell.getStringCellValue(); heaaderStr.add(cellStrValue); } excelParser.processFieldAccordingToHeader(heaaderStr, sheet.getSheetName()); } } while (rowIterator.hasNext()) { rowCount++; Row row = rowIterator.next(); // For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); List<String> rowStr = new ArrayList<String>(excelParser.parameterCount()); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellStrValue = ""; switch (cell.getCellTypeEnum()) { case STRING: cellStrValue = cell.getStringCellValue(); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { objFormulaEvaluator.evaluate(cell); cellStrValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator); } else { cellStrValue = Double.toString(cell.getNumericCellValue()); } break; case BOOLEAN: cellStrValue = Boolean.toString(cell.getBooleanCellValue()); break; case FORMULA: cellStrValue = cell.getStringCellValue(); break; case BLANK: default: break; } rowStr.add(cellStrValue); } excelParser.processRow(rowStr, rowCount, sheet.getSheetName()); } SheetData<T> sheetData = new SheetData<T>(excelParser.getParsedObject(), sheet.getSheetName(), sheetCount); sheetDataList.add(sheetData); } file.close(); workbook.close(); return sheetDataList; }
From source file:com.delpac.bean.PreDescargaBean.java
public List<PreDescarga> importData(Workbook workbook, int tabNumber) throws IOException { List<PreDescarga> lista = new ArrayList<>(); String[][] data;/*www. ja v a 2 s . com*/ DataFormatter df = new DataFormatter(); Sheet sheet = workbook.getSheetAt(tabNumber); data = new String[sheet.getLastRowNum() + 1][]; Row[] row = new Row[sheet.getLastRowNum() + 1]; Cell[][] cell = new Cell[row.length][]; for (int i = 1; i < row.length; i++) { row[i] = sheet.getRow(i); cell[i] = new Cell[row[i].getLastCellNum()]; data[i] = new String[row[i].getLastCellNum()]; PreDescarga preDescarga = new PreDescarga(); for (int j = 0; j < cell[i].length; j++) { cell[i][j] = row[i].getCell(j); if (cell[i][j] != null) { switch (j) { case 0: preDescarga.setBl(getDataFromCell(cell, i, j)); break; case 1: preDescarga.setConsignatario(getDataFromCell(cell, i, j)); break; case 2: preDescarga.setPto_origen(getDataFromCell(cell, i, j)); break; case 3: preDescarga.setPto_destino(getDataFromCell(cell, i, j)); break; case 4: preDescarga.setFec_embarque(getDataFromCell(cell, i, j)); break; case 5: preDescarga.setPeso(Double.parseDouble(getDataFromCell(cell, i, j))); break; case 6: preDescarga.setBulto(Double.parseDouble(getDataFromCell(cell, i, j))); break; case 7: preDescarga.setEmbalaje(getDataFromCell(cell, i, j)); break; case 8: preDescarga.setCarga(getDataFromCell(cell, i, j)); break; case 9: preDescarga.setCon_codigo(getDataFromCell(cell, i, j)); break; case 10: preDescarga.setTemperatura(getDataFromCell(cell, i, j)); break; case 11: preDescarga.setVentilacion(getDataFromCell(cell, i, j)); break; case 12: preDescarga.setSello(getDataFromCell(cell, i, j)); break; case 13: preDescarga.setSown(getDataFromCell(cell, i, j)); break; case 14: preDescarga.setClassimo(getDataFromCell(cell, i, j)); break; case 15: preDescarga.setUnnro(getDataFromCell(cell, i, j)); break; case 16: preDescarga.setTip_cont(getDataFromCell(cell, i, j)); break; case 17: preDescarga.setCondicion(getDataFromCell(cell, i, j)); break; case 18: preDescarga.setAlm_codigo(getDataFromCell(cell, i, j)); break; } } else { switch (j) { case 1: preDescarga.setBl(""); break; case 2: preDescarga.setConsignatario(""); break; case 3: preDescarga.setPto_origen(""); break; case 4: preDescarga.setPto_destino(""); break; case 5: preDescarga.setFec_embarque(""); break; case 6: preDescarga.setPeso(0); break; case 7: preDescarga.setBulto(0); break; case 8: preDescarga.setEmbalaje(""); break; case 9: preDescarga.setCarga(""); break; case 10: preDescarga.setCon_codigo(""); break; case 11: preDescarga.setTemperatura(""); break; case 12: preDescarga.setVentilacion(""); break; case 13: preDescarga.setSello(""); break; case 14: preDescarga.setSown(""); break; case 15: preDescarga.setClassimo(""); break; case 16: preDescarga.setUnnro(""); break; case 17: preDescarga.setTip_cont(""); break; case 18: preDescarga.setCondicion(""); break; case 19: preDescarga.setAlm_codigo(""); break; } } } lista.add(preDescarga); } return lista; }
From source file:com.gsecs.GSECSFrame.java
private Object getCellValue(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_NUMERIC: DataFormatter df = new DataFormatter(); String stringCellValue = df.formatCellValue(cell); return stringCellValue; }// ww w . j ava 2 s .c o m return null; }
From source file:com.jkoolcloud.tnt4j.streams.inputs.ExcelSXSSFRowStream.java
License:Apache License
/** * Parses the content of one sheet using the specified styles and shared-strings tables. * * @param styles//w w w .ja v a 2 s .c o m * the table of styles that may be referenced by cells in the sheet * @param strings * the table of strings that may be referenced by cells in the sheet * @param sheetHandler * the sheet handler * @param sheetInputStream * the input stream to read the sheet-data from * * @throws IOException * if sheet input stream read fails * @throws SAXException * if sheet input stream provided XML can't be parsed */ public static void processSXSSFSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler, InputStream sheetInputStream) throws IOException, SAXException { DataFormatter formatter = new DataFormatter(); InputSource sheetSource = new InputSource(sheetInputStream); try { XMLReader sheetParser = SAXHelper.newXMLReader(); ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false); sheetParser.setContentHandler(handler); sheetParser.parse(sheetSource); } catch (ParserConfigurationException exc) { throw new RuntimeException( StreamsResources.getStringFormatted(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME, "ExcelSXSSFRowStream.sax.cfg.error", Utils.getExceptionMessages(exc))); } }
From source file:com.kybelksties.excel.ExcelSheetTableModel.java
License:Open Source License
/** * Retrieve the cell value as String./*from ww w. j a va 2 s.co m*/ * * @param rowIndex row-number of the cell * @param colIndex column-number of the cell * @return the converted cell-value and trimmed cell value if exists, empty * string else */ public String getCellValueAsString(int rowIndex, int colIndex) { DataFormatter formatter = new DataFormatter(); FormulaEvaluator eval = workbook.getCreationHelper().createFormulaEvaluator(); String value; Cell cell = getCellAt(rowIndex, colIndex); if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) { value = ErrorConstants.getText(cell.getErrorCellValue()); } else { value = formatter.formatCellValue(cell, eval); } } else { value = formatter.formatCellValue(cell); } return value.trim(); }
From source file:com.lfwer.common.XLSX2CSV.java
License:Apache License
/** * Parses and shows the content of one sheet using the specified styles and * shared-strings tables./*from w w w . j av a 2s .com*/ * * @param styles * The table of styles that may be referenced by cells in the * sheet * @param strings * The table of strings that may be referenced by cells in the * sheet * @param sheetInputStream * The stream to read the sheet-data from. * * @exception java.io.IOException * An IO exception from the parser, possibly from a byte * stream or character stream supplied by the application. * @throws SAXException * if parsing the XML data fails. */ public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler, InputStream sheetInputStream) throws IOException, SAXException { DataFormatter formatter = new DataFormatter(); InputSource sheetSource = new InputSource(sheetInputStream); try { XMLReader sheetParser = SAXHelper.newXMLReader(); ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false); sheetParser.setContentHandler(handler); sheetParser.parse(sheetSource); } catch (ParserConfigurationException e) { throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage()); } }
From source file:com.mgmtp.jfunk.data.excel.ExcelDataSourceTest.java
License:Apache License
private void testExcelFile(final String path, final DataOrientation dataOrientation) throws InvalidFormatException, IOException { ExcelFile excelFile = new ExcelFile(new File(path), dataOrientation, new DataFormatter()); excelFile.load();/* w w w. j a v a2 s . co m*/ Map<String, List<Map<String, String>>> actualData = excelFile.getData(); assertThat(actualData).isEqualTo(expectedData); }