List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getNumericCellValue
@Override public double getNumericCellValue()
From source file:com.smanempat.controller.ControllerClassification.java
private void showXLSX(JTextField txtFileDirectory, JTable tablePreview) throws FileNotFoundException, IOException { DefaultTableModel tableModel = new DefaultTableModel(); File fileName = new File(txtFileDirectory.getText()); FileInputStream inputStream = new FileInputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheetAt(0); int rowValue = sheet.getLastRowNum() + 1; int colValue = sheet.getRow(0).getLastCellNum(); String[][] data = new String[rowValue][colValue]; String[] colName = new String[colValue]; for (int i = 0; i < rowValue; i++) { XSSFRow row = sheet.getRow(i);/*from w w w .j a v a 2 s .co m*/ for (int j = 0; j < colValue; j++) { XSSFCell cell = row.getCell(j); int type = cell.getCellType(); Object returnCellValue = null; if (type == 0) { returnCellValue = cell.getNumericCellValue(); } else if (type == 1) { returnCellValue = cell.getStringCellValue(); } data[i][j] = returnCellValue.toString(); } } for (int i = 0; i < colValue; i++) { colName[i] = data[0][i]; } tableModel = new DefaultTableModel(data, colName); tablePreview.setModel(tableModel); tableModel.removeRow(0); }
From source file:com.smanempat.view.ReadWorkbook.java
private String nilaiCell(XSSFCell pCell) { int tipe = pCell.getCellType(); Object nilaiBalik = null;/*from w w w . ja v a 2 s . c o m*/ if (tipe == 0) { nilaiBalik = pCell.getNumericCellValue(); } else if (tipe == 1) { nilaiBalik = pCell.getStringCellValue(); } return nilaiBalik.toString(); }
From source file:com.xhsoft.framework.common.file.ExcelHandle.java
License:Open Source License
/** * ????.xlsx?/* ww w . ja v a2 s . c o m*/ * @params {:,:} * @return String * @author lijiangwei * @since 2012-11-12 */ private String getCellValue(XSSFCell xls_cell) { String value = ""; switch (xls_cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: value = ""; break; case XSSFCell.CELL_TYPE_BOOLEAN: value = String.valueOf(xls_cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: break; case XSSFCell.CELL_TYPE_FORMULA: String.valueOf(xls_cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_NUMERIC: value = String.valueOf(xls_cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: value = String.valueOf(xls_cell.getStringCellValue()); break; default: break; } return value; }
From source file:com.yanglb.utilitys.codegen.core.reader.BaseReader.java
License:Apache License
/** * ?Cell???/*from w w w . j a v a2s.com*/ * @param cell * @return */ public String getCellStringValue(XSSFCell cell) { String result = null; int type = cell.getCellType(); if (type == Cell.CELL_TYPE_FORMULA) type = cell.getCachedFormulaResultType(); if (type == Cell.CELL_TYPE_BLANK) return null; if (type == Cell.CELL_TYPE_ERROR) { return "#VALUE!"; } switch (type) { case Cell.CELL_TYPE_BOOLEAN: result = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: { if (cell.getCellStyle().getDataFormat() == DataFormatType.FORMAT_DATE) { Date date = cell.getDateCellValue(); String format = "yyyy/MM/dd";//cell.getCellStyle().getDataFormatString(); if (cell.getCellStyle().getDataFormatString().contains(":")) { // format = "yyyy/MM/dd HH:mm:ss"; } SimpleDateFormat df = null; try { df = new SimpleDateFormat(format); } catch (IllegalArgumentException e) { //df = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss"); } result = df.format(date); } else { // ?? 0 Number number = cell.getNumericCellValue(); result = number.toString(); if (result.indexOf('.') != -1) { result = result.replaceAll("[0]*$", ""); } if (result.endsWith(".")) { result = result.substring(0, result.length() - 1); } } } break; } return result; }
From source file:Creator.TaskManagerPanel.java
private void loadDefaultTasks() { String path = "/Creator/textFiles/tasks.xlsx"; InputStream loc = this.getClass().getResourceAsStream(path); importedTasks = new ArrayList<>(); try {//from w w w . j av a2 s . co 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 + "]"); rowData[j] = "default @ [" + i + "][" + j + "]"; break; } } else { System.out.println("null @ [" + i + "][" + j + "]"); rowData[j] = "nullValue @ [" + i + "][" + j + "]"; } } rowData[5] = "'" + rowData[5] + "'"; importedTasks.add(rowData); } } wb.close(); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
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:data.pkg.ReadWriteExcelFile.java
public static void readXLSXFile(String path) throws IOException { File excel = new File(path); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet ws = wb.getSheet("Sheet1"); int rowNum = ws.getLastRowNum() + 1; int colNum = ws.getRow(0).getLastCellNum(); for (int i = 0; i < rowNum; i++) { XSSFRow row = ws.getRow(i);//from ww w.j av a2 s.c o m for (int j = 0; j < colNum; j++) { XSSFCell cell = row.getCell(j); if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { long l = (new Double(cell.getNumericCellValue())).longValue(); //long value = Long.parseLong(cell.toString()); if (j == 0) { dataOpen.addDeslocamento(l); System.out.println(dataOpen.getDeslocamento(i - 1)); } if (j == 1) { dataOpen.addForca(l); System.out.println(dataOpen.getForca(i - 1)); } } } } }
From source file:database.scanners.DailyWOScanner.java
public WOData[] ScanDatabase(String databaseName) { int woRowNumber = 0; //Licznik wierszy klasy WOData int numericCellInt; //Licznik komrek z wartoci numeryczn WOData[] dataArray = null;/*from w ww . j av a2 s. c om*/ Iterator rows = null; try { dataArray = GetBlankDatabase(databaseName); rows = GetSheetIterator(databaseName); } catch (IOException ex) { System.out.println(ex.getMessage()); } while (rows.hasNext()) { numericCellInt = 0; XSSFRow row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) numericCellInt++; } if (numericCellInt == 0) continue; XSSFCell cell; cell = row.getCell(config.getDailyWOMinTimeCell()); dataArray[woRowNumber].setMinTime((int) cell.getNumericCellValue()); cell = row.getCell(config.getDailyWOMaxTimeCell()); dataArray[woRowNumber].setMaxTime((int) cell.getNumericCellValue()); cell = row.getCell(config.getDailyWOClientNumberCell()); dataArray[woRowNumber].setClientNumber((int) cell.getNumericCellValue()); cell = row.getCell(config.getDailyWOAddressCell()); dataArray[woRowNumber].setClientAddress(cell.getStringCellValue()); woRowNumber++; } return dataArray; }
From source file:database.scanners.ReportScanner.java
public WOData[] ScanDatabase(String databaseName) { int woRowNumber = 0; //Licznik wierszy klasy WOData int numericCellInt; //Licznik komrek z wartoci numeryczn int stringCellInt; //Licznik komrek z wartoci string WOData[] dataArray;//from w w w . ja va2s.c om Iterator rows; try { dataArray = GetBlankDatabase(databaseName); rows = GetSheetIterator(databaseName); } catch (IOException ex) { System.out.println(ex.getMessage()); return null; } while (rows.hasNext()) { numericCellInt = 0; stringCellInt = 0; XSSFRow row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { numericCellInt++; if (numericCellInt == config.getReportMinTimeCell()) { dataArray[woRowNumber].setMinTime((int) cell.getNumericCellValue()); } if (numericCellInt == config.getReportMaxTimeCell()) { dataArray[woRowNumber].setMaxTime((int) cell.getNumericCellValue()); } if (numericCellInt == config.getReportClientNumberCell()) { dataArray[woRowNumber].setClientNumber((int) cell.getNumericCellValue()); } } if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { stringCellInt++; if ((stringCellInt == config.getReportAddressCell()) && (numericCellInt > 0)) { dataArray[woRowNumber].setClientAddress(cell.getStringCellValue()); } } if ((numericCellInt >= config.getReportClientNumberCell()) && (stringCellInt >= config.getReportAddressCell())) { woRowNumber++; break; } } } return dataArray; }
From source file:db.pj.util.excel.ApplicationImporter.java
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null;/* w w w . j av a 2 s .c o m*/ List<String> result = new ArrayList<String>(); result.add(""); try { inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (Exception e) { result.set(0, ""); return result; } int success = 0, fail = 0; // number of tuples UserDao userDao = DaoFactory.getUserDao(); LicenseDao licenseDao = DaoFactory.getLicenseDao(); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = null; XSSFCell cell = null; int rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); String[] values = new String[cells]; // read data to an array of strings for (short j = 0; j < cells; j++) { cell = row.getCell(j); if (cell == null) values[j] = null; else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); Date date = new Date(DateUtil.getJavaDate(d).getTime()); values[j] = date.toString(); } else { values[j] = (int) (cell.getNumericCellValue()) + ""; } break; default: values[j] = cell.getStringCellValue(); break; } } } License license = null; User user = userDao.queryUserByIC(Integer.parseInt(values[0]), values[1]); if (user != null) license = licenseDao.queryLicenseByUserID(user.getUserID()); if (user == null) { user = new User(); user.setUserID(IDGenerator.generateUserID(values[0] + values[1])); user.setUserPwd(StringUtil.MD5(user.getUserID())); user.setUserName(values[2]); user.setUserGender(XMLRepertory.genderStr2Num(values[3])); user.setUserIctype(Integer.parseInt(values[0])); user.setUserIcno(values[1]); user.setUserBdate(MyDateUtil.str2Date(values[4])); user.setUserPhone("00000000"); user.setUserNation(values[5]); user.setUserAddr(values[6]); user.setUserHead("user/default.png"); user.setUserCensor(values[9]); user.setUserHealth(Integer.parseInt(values[7])); user.setUserPdate(MyDateUtil.str2Date(values[8])); // check health and age boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18) && (user.getAge(user.getUserPdate()) <= 70); user.setUserState(ok ? 1 : 0); boolean re = userDao.addUser(user); if (re) success++; else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add("" + ic + ""); } } else if (license == null) { user.setUserCensor(values[9]); user.setUserHealth(Integer.parseInt(values[7])); user.setUserPdate(MyDateUtil.str2Date(values[8])); // check health and age boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18) && (user.getAge(user.getUserPdate()) <= 70); user.setUserState(ok ? 1 : 0); boolean re = userDao.updateUser(user); if (re) success++; else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add("" + ic + ""); } } else { // user has a license if (license.getLicnValid() == 0) { boolean nolimit = license.getLicnVinfo() == 12 && license.getLicnLimit() == null; boolean outlimit = license.getLicnLimit() != null && (license.getLicnLimit().getTime() - MyDateUtil.str2Date(values[8]).getTime() <= 0); if (nolimit || outlimit) { user.setUserCensor(values[9]); user.setUserHealth(Integer.parseInt(values[7])); user.setUserPdate(MyDateUtil.str2Date(values[8])); // check health and age boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18) && (user.getAge(user.getUserPdate()) <= 70); user.setUserState(ok ? 1 : 0); boolean re = userDao.updateUser(user); if (re) success++; else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add("" + ic + ""); } } else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add(ic + ""); } } else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add(ic + ""); } } } result.set(0, "" + success + "" + fail + ""); return result; }