private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo) throws Exception { Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK); Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType()); nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME)); nCell.setCellStyle(tCell.getCellStyle()); tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK); nCell = newSheet.createRow(1).createCell(0, tCell.getCellType()); nCell.setCellValue( tCell .getStringCellValue() .replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD") .replaceAll( PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom)) .replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo))); nCell.setCellStyle(tCell.getCellStyle()); Row tempRow = tempSheet.getRow(4); Row newRow = newSheet.createRow(4); for (int c = 0; c < tempRow.getLastCellNum(); c++) { tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK); nCell = newRow.createCell(c, tCell.getCellType()); nCell.setCellValue(tCell.getStringCellValue()); nCell.setCellStyle(tCell.getCellStyle()); } for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i); newSheet.addMergedRegion(mergedRegion); } }
private static void verifyNumericCell(Cell cell, String cell_description) throws ParseException { verifyCellNotEmpty(cell, cell_description); if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) { throw new ParseException( cell_description + " in row " + cell.getRow().getRowNum() + " is not numeric type!", 0); } }
private void generateServicesRegion(Workbook wb, StringBuilder sb) { // Get the Services Sheet Sheet sheet = wb.getSheet("Services"); Iterator<Row> rowIt = sheet.rowIterator(); // Ignore the Header row rowIt.next(); while (rowIt.hasNext()) { Row row = rowIt.next(); Cell cellId = row.getCell(0); if (cellId != null) { int id = (int) cellId.getNumericCellValue(); Cell cellName = row.getCell(1); String name = cellName.getStringCellValue().replaceAll("\"", "'"); Cell cellDescription = row.getCell(2); String description = cellDescription.getStringCellValue().replaceAll("\"", "'"); Cell cellPrivateData = row.getCell(3); Cell cellPartOf = row.getCell(4); sb.append("Service S" + id + " {"); sb.append("\n"); sb.append("\tName \"" + name + "\""); sb.append("\n"); sb.append("\tDescription \"" + description + "\""); sb.append("\n"); if (cellPrivateData.getCellType() == Cell.CELL_TYPE_NUMERIC) { int privateData = (int) cellPrivateData.getNumericCellValue(); sb.append("\tRefersTo PrivateData PD" + privateData); sb.append("\n"); } else if (cellPrivateData.getCellType() == Cell.CELL_TYPE_STRING) { String privateData = cellPrivateData.getStringCellValue(); if (privateData.equals("All")) { sb.append("\tRefersTo PrivateData All"); } else { sb.append("\tRefersTo PrivateData "); for (String s : privateData.split(", ")) { sb.append("PD" + s + ","); } // Delete last ',' sb.deleteCharAt(sb.length() - 1); } sb.append("\n"); } if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) { int partOf = (int) cellPartOf.getNumericCellValue(); sb.append("\tService_Part S" + partOf); sb.append("\n"); } sb.append("}"); sb.append("\n\n"); } else break; } }
private void generateRecipientsRegion(Workbook wb, StringBuilder sb) { // Get the Recipients Sheet Sheet sheet = wb.getSheet("Recipients"); Iterator<Row> rowIt = sheet.rowIterator(); // Ignore the Header row rowIt.next(); while (rowIt.hasNext()) { Row row = rowIt.next(); Cell cellId = row.getCell(0); if (cellId != null) { if (cellId.getCellType() == Cell.CELL_TYPE_NUMERIC) { int id = (int) cellId.getNumericCellValue(); Cell cellDescription = row.getCell(1); String description = cellDescription.getStringCellValue().replaceAll("\"", "'"); Cell cellScope = row.getCell(2); String scope = cellScope.getStringCellValue(); if (scope.contains("/")) { scope = "Internal/External"; } else { scope = scope.substring(0, 1).toUpperCase() + scope.substring(1); } Cell cellType = row.getCell(3); String type = cellType.getStringCellValue(); if (type.contains("/")) { type = "Individual/Organization"; } else { type = type.substring(0, 1).toUpperCase() + type.substring(1); } Cell cellPartOf = row.getCell(4); sb.append("Recipient R" + id + " {"); sb.append("\n"); sb.append("\tName \"" + description + "\""); sb.append("\n"); sb.append("\tDescription \"" + description + "\""); sb.append("\n"); if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) { int partOf = (int) cellPartOf.getNumericCellValue(); sb.append("\tRecipient_Part R" + partOf); sb.append("\n"); } sb.append("\tScope " + scope); sb.append("\n"); sb.append("\tType " + type); sb.append("\n}"); sb.append("\n\n"); } } else break; } }
@SuppressWarnings("static-access") private static String getValue(Cell cell) { if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) { return NumberToTextConverter.toText(cell.getNumericCellValue()); } else { return String.valueOf(cell.getStringCellValue()); } }
static String[] getStringArrVall(Cell val, String split) { if (val != null) { if (val.getCellType() == Cell.CELL_TYPE_STRING) { if (val.getStringCellValue().equals(" ")) return null; return val.getStringCellValue().split(split); } else if (val.getCellType() == Cell.CELL_TYPE_NUMERIC) return ("" + val.getNumericCellValue()).split(","); } return null; }
public Object getDataFromCell(final Cell cell) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } return null; }
public static void ParseXLSFormat(String file_name, String output_file_name) throws IOException { FileInputStream fileInputStream = new FileInputStream(file_name); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("T_Auto"); BufferedWriter out = new BufferedWriter(new FileWriter(output_file_name)); boolean first = true; StringBuffer result = new StringBuffer(); Iterator<Row> rowIterator = worksheet.iterator(); rowIterator.next(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); String automaton = row.getCell(AUTOMATA).getStringCellValue(); Cell from_cell = row.getCell(FROM); int from; if (from_cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { from = (int) from_cell.getNumericCellValue(); } else { from = Integer.parseInt(from_cell.getStringCellValue()); } Cell to_cell = row.getCell(TO); int to; if (to_cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { to = (int) to_cell.getNumericCellValue(); } else { to = Integer.parseInt(to_cell.getStringCellValue()); } String events = row.getCell(EVENTS).getStringCellValue(); String condition = row.getCell(CONDITION).getStringCellValue(); String actions = row.getCell(ACTIONS).getStringCellValue(); if (first) { first = false; } else { result.append(NEWLINE); } result.append( automaton + NEWLINE + from + NEWLINE + to + NEWLINE + events + NEWLINE + condition + NEWLINE + actions); } fileInputStream.close(); out.write(result.toString()); out.close(); }
/** 从Excel导入到TableBean */ public void doImport() { int rowNum = sheet.getLastRowNum() + 1; int columnNum = 0; for (int i = 0; i < rowNum; i++) { if (sheet.getRow(i) != null) { int last = sheet.getRow(i).getLastCellNum(); columnNum = last > columnNum ? last : columnNum; } } tableBean = new TableBean(rowNum, columnNum); Collection<CellBean> cellBeans = new ArrayList<CellBean>(); for (int r = startRow; r < rowNum; r++) { Row row = sheet.getRow(r); if (row != null) { for (int c = 0; c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c); if (cell != null) { cell.setCellType(Cell.CELL_TYPE_STRING); Integer type = forceCellType.get(c); if (type != null) { cell.setCellType(type); } String cellValue = null; if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) { cellValue = cell.getBooleanCellValue() + ""; } else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) { try { cellValue = String.valueOf(cell.getNumericCellValue()); } catch (IllegalStateException e) { cellValue = String.valueOf(cell.getRichStringCellValue()).trim(); } } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { if (DateUtil.isCellDateFormatted(cell)) { Date date2 = cell.getDateCellValue(); SimpleDateFormat dff = new SimpleDateFormat(dateFormat); cellValue = dff.format(date2); // 日期转化 } else cellValue = String.valueOf(cell.getNumericCellValue()); } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) { cellValue = cell.getStringCellValue(); } if (cellValue != null && cellValue instanceof String) { cellValue = cellValue.toString().trim(); } CellBean cellBean = new CellBean(cellValue, r, c); cellBean.setCell(cell); cellBeans.add(cellBean); } } } } tableBean.setCellBeans(cellBeans); }
@Override public String getString(Integer rowNums, int index) { Cell cell = getCell(rowNums, index); if (cell == null) { return ""; } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue().trim(); } if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return new DecimalFormat("###").format(cell.getNumericCellValue()); } return ""; }
/* (non-Javadoc) * @see com.his.common.excel.AbstractFileImporter#getInteger(java.lang.Integer, int) */ @Override public Integer getInteger(Integer rowNums, int index) { Cell cell = getCell(rowNums, index); if (cell == null) { return null; } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return Integer.parseInt(cell.getStringCellValue().trim()); } if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return (int) cell.getNumericCellValue(); } return (int) cell.getNumericCellValue(); }
private Object objectFrom(final HSSFWorkbook workbook, final Cell cell) { Object cellValue = null; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellValue = cell.getRichStringCellValue().getString(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { cellValue = getNumericCellValue(cell); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { cellValue = cell.getBooleanCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { cellValue = evaluateCellFormula(workbook, cell); } return cellValue; }
public void parseData( final Sheet sheet, final int startRow, final int endColumn, final List<String> propertyNames, final Class clazzOfTestCase) { int rowCounter = startRow; while (!isBlank(sheet, rowCounter, endColumn)) { Row row = sheet.getRow(rowCounter - 1); for (int i = 0; i < endColumn; i++) { Cell cell = row.getCell(i); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { try { Method method = clazzOfTestCase.getMethod("set" + propertyNames.get(i), Object.class); } catch (NoSuchMethodException e) { e .printStackTrace(); // To change body of catch statement use File | Settings | File // Templates. } } } } }
@Override public String getPriceDate(String fileName) { try { HSSFSheet sheet = getSheet(fileName, 0); String marker = "Дата"; int numOfRows = sheet.getPhysicalNumberOfRows() > 10 ? 10 : sheet.getPhysicalNumberOfRows(); String dirtyDate = null; for (int i = priceConfig.getRowToStart(Brand.STELS); i < numOfRows; i++) { Row row = sheet.getRow(i); if (row.getPhysicalNumberOfCells() > 1) { Cell cell = row.getCell(0); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String stringCellValue = cell.getStringCellValue(); if (stringCellValue.contains(marker)) { dirtyDate = stringCellValue; break; } } } } if (dirtyDate != null) { return dirtyDate.trim(); } } catch (Exception ex) { LOGGER.error("FAIL to get price date - return today"); return new Date().toString(); } return new Date().toString(); }
public String getValue(Row row, int cellNo) { Cell cell = row.getCell(cellNo); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) return cell.getStringCellValue().trim().replaceAll(" ", ""); else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) return Boolean.toString(cell.getBooleanCellValue()) .toUpperCase() .trim() .replaceAll(" ", ""); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) return Integer.toString((int) cell.getNumericCellValue()).trim().replaceAll(" ", ""); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return ""; } return ""; }
public String readCGCell(Row row, int cellIdx, DataDto dto, CellType cellType) { Cell cell = row.getCell(cellIdx); if (null == cell) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getConfidenceGrade() + " cell is null."); return null; } if (cell.getCellType() != Cell.CELL_TYPE_STRING) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getConfidenceGrade() + " CG cell is not of type String."); return null; } RichTextString cg = cell.getRichStringCellValue(); return cg.getString(); }
protected boolean isCellEmpty(Cell cell) { if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { return true; } try { String cellStringValue = cell.getStringCellValue(); if (!cellStringValue.isEmpty()) { return false; } } catch (Exception e) { // Do nothing. Its just not a String cell. } try { Double cellNumericValue = cell.getNumericCellValue(); if (0 != cellNumericValue) { // from the poi documentation 'For blank cells we return a 0.'. Groan! So since a zero is a // valid value, how do I test for an empty numeric cell? return false; } } catch (Exception e) { // Do nothing. Its just not a Numeric cell. } // Its either empty or a not a string or numeric. return true; }
public boolean isCellNumeric(Cell cell) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return true; } else { return false; } }
private String text(Row row, int pos) { Cell cell = row.getCell(pos); if (cell != null) { String s = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (pos == INNLAND_POSTNUMMER) { s = String.format("%04.0f", cell.getNumericCellValue()); } else if (pos == INNLAND_KONTONR) { s = String.format("%011.0f", cell.getNumericCellValue()); } else { s = String.format("%1.0f", cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_FORMULA: s = this.formatter.formatCellValue(cell, this.evaluator); break; default: s = cell.getStringCellValue(); } return s.trim(); } else { return ""; } }
public boolean isCellString(Cell cell) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return true; } else { return false; } }
public static String getValue(Cell cell) { String content = null; if (cell != null) { switch (cell.getCellType()) { case 0: double d = cell.getNumericCellValue(); if (d == (long) d) content = String.valueOf((long) d); else { content = String.valueOf(d); } break; case 1: content = cell.getStringCellValue(); break; case 2: double dd = cell.getNumericCellValue(); if (dd == (long) dd) content = String.valueOf((long) dd); else { content = String.valueOf(dd); } break; case 3: content = ""; break; case 4: content = String.valueOf(cell.getBooleanCellValue()); } } return content; }
public String getCellContent(Cell cell) { DecimalFormat df = new DecimalFormat("0"); if (cell == null) { return ""; } int type = cell.getCellType(); String value = ""; switch (type) { case Cell.CELL_TYPE_FORMULA: try { value = String.valueOf(df.format(cell.getNumericCellValue())).toString().trim(); } catch (Exception e) { log.info("处理公式单元格失败!"); } break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue().toString().trim(); break; case Cell.CELL_TYPE_BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()).toString().trim(); break; case Cell.CELL_TYPE_NUMERIC: value = String.valueOf(df.format(cell.getNumericCellValue())).toString().trim(); if (value.endsWith(".0")) { value = value.substring(0, value.length() - 2).trim(); } break; default: value = cell.toString().trim(); break; } return value; }
/** * 由于Excel当中的单元格Cell存在类型,若获取类型错误就会产生异常, 所以通过此方法将Cell内容全部转换为String类型 * * @param cell * @return */ public static String getCellValue(Cell cell) { String str = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: str = ""; break; case Cell.CELL_TYPE_BOOLEAN: str = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: str = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { str = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(cell.getDateCellValue()); } else { str = String.valueOf((long) cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: str = String.valueOf(cell.getStringCellValue()); break; default: str = null; break; } } return StringUtils.trim(str); }
public static void main(String args[]) throws Exception { File file = new File("d:/delete/createworkbook.xlsx"); FileInputStream fis = new FileInputStream(file); if (file.isFile() && file.exists()) { // Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fis); System.out.println("openworkbook.xlsx file open successfully."); // get worksheet, 0 based XSSFSheet sheet = workbook.getSheetAt(0); // get rows Iterator<Row> it = sheet.iterator(); while (it.hasNext()) { Row row = (XSSFRow) it.next(); // get cells Iterator<Cell> cellIt = row.cellIterator(); while (cellIt.hasNext()) { Cell cell = cellIt.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + " \t\t "); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + " \t\t "); break; } } } } else { System.out.println("Error to open openworkbook.xlsx file."); } }
/** 把单元格内的类型转换至String类型 */ private String ConvertCellStr(Cell cell, String cellStr) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // 读取String cellStr = cell.getStringCellValue().toString(); break; case Cell.CELL_TYPE_BOOLEAN: // 得到Boolean对象的方法 cellStr = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: // 先看是否是日期格式 if (DateUtil.isCellDateFormatted(cell)) { // 读取日期格式 cellStr = cell.getDateCellValue().toString(); } else { // 读取数字 cellStr = String.valueOf(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_FORMULA: // 读取公式 cellStr = cell.getCellFormula().toString(); break; } return cellStr; }
private static void retrieveSheetInformation(Sheet sheet) { // every sheet has rows, iterate over them Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { // Get the row object Row row = rowIterator.next(); // Every row has columns, get the column iterator and // iterate over them Iterator<Cell> cellIterator = row.cellIterator(); StringBuffer sb = new StringBuffer(); while (cellIterator.hasNext()) { // Get the Cell object Cell cell = cellIterator.next(); // check the cell type and process accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: sb.append(cell.getStringCellValue()).append(" $ "); break; case Cell.CELL_TYPE_NUMERIC: sb.append(cell.getNumericCellValue()).append(" "); } } // end of cell iterator System.out.println(sb.toString()); } // end of rows iterator }
public static Object getCellValue(Cell cell) { if (cell == null) return null; int celltype = cell.getCellType(); if (celltype == Cell.CELL_TYPE_NUMERIC) return cell.getNumericCellValue(); if (celltype == Cell.CELL_TYPE_STRING) { String value = cell.getStringCellValue(); if ("null".equals(value)) { value = ""; } if (!StringUtil.isEmpty(value)) { value = value.replaceAll(" ", ""); value = value.replaceAll(" ", ""); } return value; } if (celltype == Cell.CELL_TYPE_FORMULA) { String value = cell.getStringCellValue(); if ("null".equals(value)) { value = ""; } return value; } if (celltype == cell.CELL_TYPE_BLANK) return ""; if (celltype == cell.CELL_TYPE_ERROR) return ""; return ""; }
private Object[] readLineData(Row row) { if (row == null) return null; List<Object> dataList = new ArrayList<Object>(); for (Cell cell : row) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: // ???Boolean???????? dataList.add(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // ????????? dataList.add(cell.getDateCellValue()); } else { // ??????? dataList.add(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: // ???String dataList.add(cell.getRichStringCellValue().toString()); break; case Cell.CELL_TYPE_BLANK: // ??? dataList.add(""); break; } } return dataList.toArray(); }
public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) { if (copyStyle) { newCell.setCellStyle(oldCell.getCellStyle()); } switch (oldCell.getCellType()) { case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: newCell.setCellType(Cell.CELL_TYPE_BLANK); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
@Override public String readValue(String valueCode) { String retVal = null; try { if (headers.containsKey(valueCode)) { Cell cell = dataRow.getCell(headers.get(valueCode), Row.RETURN_BLANK_AS_NULL); if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: retVal = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: retVal = String.valueOf(cell.getNumericCellValue()); break; } } } } catch (Exception e) { logger.error(String.format("Failed to get column data: %s", valueCode)); } return retVal; }