List of usage examples for org.apache.poi.ss.usermodel Cell getRichStringCellValue
RichTextString getRichStringCellValue();
For numeric cells we throw an exception.
From source file:au.com.onegeek.lambda.parser.XslxUtil.java
License:Apache License
public static Object objectFrom(final Workbook workbook, final Cell cell) { Object cellValue = null;/*from ww w . ja va 2s.co m*/ if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellValue = cell.getRichStringCellValue().getString(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { cell.setCellType(Cell.CELL_TYPE_STRING); cellValue = cell.getRichStringCellValue().getString(); // 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; }
From source file:b01.officeLink.excel.ExcelRefiller.java
License:Apache License
public void fillGroupDefinition(FocExcelDocument excel) { setFocExcelDocument(excel);//from w ww . jav a2s. co m Sheet sourceSheet = excel.getSheetAt(1); for (int i = 0; i < 100; i++) { Row sRow = sourceSheet.getRow(i); Cell sCell = sRow != null ? sRow.getCell(0) : null; RichTextString groupColValue = sCell != null ? sCell.getRichStringCellValue() : null; String groupColValueStr = groupColValue != null ? groupColValue.getString() : null; if (groupColValueStr != null) { ExcelGroupDefinition groupDef = groupMap.get(groupColValueStr); if (groupDef == null) { groupDef = new ExcelGroupDefinition(); groupMap.put(groupColValueStr, groupDef); } groupDef.addRow(i); } } }
From source file:b01.officeLink.excel.ExcelRefiller.java
License:Apache License
public void fillGroupContent(String groupStr, FocObject object) { ExcelGroupDefinition grpDef = getGroupDefinition(groupStr); Sheet srcSheet = getSourceSheet();//w w w.j av a 2s . co m Sheet tarSheet = getTargetSheet(); if (grpDef != null) { for (int i = 0; i < grpDef.getRowCount(); i++) { int rowIdx = grpDef.getRowAt(i); Row sRow = srcSheet.getRow(rowIdx); if (sRow != null) { Row tRow = tarSheet.getRow(currentRow); if (tRow == null) { tRow = tarSheet.createRow(currentRow); } if (tRow != null) { tRow.setHeight(sRow.getHeight()); for (int c = 0; c < 20; c++) { Cell sCell = sRow.getCell(c + 1); if (sCell != null) { Cell tCell = tRow.getCell(c); if (tCell == null) { tCell = tRow.createCell(c); } if (tCell != null) { tCell.setCellStyle(sCell.getCellStyle()); String str = ""; if (sCell.getCellType() == Cell.CELL_TYPE_STRING) { RichTextString rts = sCell.getRichStringCellValue(); str = rts.getString(); str = analyseContent(str, object); } else if (sCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { str = String.valueOf(sCell.getNumericCellValue()); } if (str != null && !str.isEmpty()) { int iVal = convertString2Integer(str); double dVal = convertString2Double(str); if (iVal != Integer.MAX_VALUE) { tCell.setCellValue(iVal); } else if (!Double.isNaN(dVal)) { tCell.setCellValue(dVal); } else { if (getFocExcelDocument() != null && getFocExcelDocument().getWorkbook() != null) { tCell.setCellValue(getFocExcelDocument().getWorkbook() .getCreationHelper().createRichTextString(str)); } } } } } } } currentRow++; } } } }
From source file:b01.officeLink.excel.FocExcelDocument.java
License:Apache License
public void exportLocally(FocObject object) { try {//from w ww . j a v a2s . co m Sheet sheet = workbook.getSheetAt(0); // Iterate over each row in the sheet Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = (Row) rows.next(); System.out.println("Row #" + row.getRowNum()); // Iterate over each cell in the row and print out the cell's content Iterator cells = row.cellIterator(); while (cells.hasNext()) { Cell cell = (Cell) cells.next(); System.out.println("Cell #" + cell.getColumnIndex()); /* System.out.println(String.valueOf(cell.getRichStringCellValue())); */ String str = null; try { str = String.valueOf(cell.getRichStringCellValue()); } catch (Exception e) { Globals.logExceptionWithoutPopup(e); str = ""; } String result = analyseContent(str, object); if (result != null) { if (getWorkbook() != null) { cell.setCellValue(getWorkbook().getCreationHelper().createRichTextString(result)); } } /* * switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: * System.out.println(cell.getNumericCellValue()); String result = * analyseContent(String.valueOf(cell.getNumericCellValue()), object); * if (result != null){ cell.setCellValue(Double.valueOf(result)); } * break; case HSSFCell.CELL_TYPE_STRING: * System.out.println(cell.getRichStringCellValue()); result = * analyseContent(String.valueOf(cell.getRichStringCellValue()), * object); if (result != null){ cell.setCellValue(new * HSSFRichTextString(result)); } break; default: * System.out.println("unsuported cell type"); break; } */} } } catch (Exception e) { e.printStackTrace(); } }
From source file:b01.officeLink.excel.FocExcelSheet.java
License:Apache License
public String getCellString(int coord0, int coord1) { String str = null;//from www.j av a 2s. c om try { Row row = sheet.getRow(coord0); Cell cell = row != null ? row.getCell(coord1) : null; if (cell != null) { int type = cell.getCellType(); if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { try { double dVal = cell.getNumericCellValue(); if (Double.isNaN(dVal)) { type = Cell.CELL_TYPE_STRING; } else { type = Cell.CELL_TYPE_NUMERIC; } } catch (Exception e) { Globals.logString("This EXCEPTION is Handles"); Globals.logExceptionWithoutPopup(e); } } if (type == Cell.CELL_TYPE_STRING) { str = cell.getRichStringCellValue().getString(); } else if (type == Cell.CELL_TYPE_NUMERIC) { // str = String.valueOf((int) cell.getNumericCellValue()); str = cell.getNumericCellValue() + ""; } } } catch (Exception e) { Globals.logException(e); Globals.logString("Could Not get Value for cell [" + coord0 + "," + coord1 + "]"); } return str; }
From source file:bad.robot.excel.row.CopyRow.java
License:Apache License
private static void setCellDataValue(Cell oldCell, Cell newCell) { switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break;// ww w . j a va 2s.c om 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; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } }
From source file:blanco.commons.calc.parser.AbstractBlancoCalcParser.java
License:Open Source License
public static String getCellValue(Cell cell) { // 2016.01.20 j.amano // ?jxl to poi ????? //------------------------ //??:\-1,000/*from w w w .ja v a 2 s . co m*/ //jxl:($1,000)?$????????? //poi:-1000 //------------------------ //??:2016/1/20 //jxl:0020, 1 20, 2016 //poi:2016/01/20 00:00:00 //------------------------ //??:#REF!??? //jxl:#REF! //poi:#REF! //------------------------ //??:1,000 //jxl:" "1,000 //poi:-1000 //------------------------ if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_NUMERIC: // ?? if (DateUtil.isCellDateFormatted(cell)) { // ???? Date dt = cell.getDateCellValue(); // ???? DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); String sDate = df.format(dt); return sDate; } // ???.0 DecimalFormat format = new DecimalFormat("0.#"); return format.format(cell.getNumericCellValue()); case Cell.CELL_TYPE_FORMULA: Workbook wb = cell.getSheet().getWorkbook(); CreationHelper crateHelper = wb.getCreationHelper(); FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator(); return getCellValue(evaluator.evaluateInCell(cell)); case Cell.CELL_TYPE_ERROR: byte errorCode = cell.getErrorCellValue(); FormulaError error = FormulaError.forInt(errorCode); String errorText = error.getString(); return errorText; default: return ""; } } return ""; }
From source file:browsermator.com.MyTable.java
MyTable(String csvFile) {
DataFile = csvFile;/*from w w w . ja v a2 s .c o m*/
DataTable = new JTable();
myEntries = new ArrayList<>();
File filecheck = new File(csvFile);
if (filecheck.isAbsolute()) {
String[] left_right_side_of_dot = csvFile.split("\\.");
String file_extension = left_right_side_of_dot[left_right_side_of_dot.length - 1];
switch (file_extension) {
case "xls":
try {
FileInputStream file = new FileInputStream(new File(DataFile));
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int number_of_cells = 0;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int number_of_thesecells = row.getPhysicalNumberOfCells();
if (number_of_thesecells > number_of_cells) {
number_of_cells = number_of_thesecells;
}
}
Iterator<Row> rowIterator2 = sheet.iterator();
while (rowIterator2.hasNext()) {
Row row = rowIterator2.next();
String[] myRow = new String[number_of_cells];
Iterator<Cell> cellIterator = row.cellIterator();
int cell_index = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Boolean boolvalue = cell.getBooleanCellValue();
String cellvalue = "false";
if (boolvalue) {
cellvalue = "true";
} else
myRow[cell_index] = cellvalue;
break;
case Cell.CELL_TYPE_NUMERIC:
myRow[cell_index] = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
myRow[cell_index] = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_ERROR:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_FORMULA:
myRow[cell_index] = cell.getCellFormula();
break;
}
cell_index++;
}
if (cell_index != number_of_cells) {
for (int x = cell_index; x < number_of_cells; x++)
myRow[cell_index] = "";
}
myEntries.add(myRow);
}
file.close();
}
catch (Exception e) {
System.out.println("Error occurred while reading XLS file: " + e.toString());
}
break;
case "xlsx":
try {
FileInputStream file = new FileInputStream(new File(DataFile));
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int number_of_cells = 0;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int number_of_thesecells = row.getPhysicalNumberOfCells();
if (number_of_thesecells > number_of_cells) {
number_of_cells = number_of_thesecells;
}
}
Iterator<Row> rowIterator2 = sheet.iterator();
while (rowIterator2.hasNext()) {
Row row = rowIterator2.next();
String[] myRow = new String[number_of_cells];
Iterator<Cell> cellIterator = row.cellIterator();
int cell_index = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Boolean boolvalue = cell.getBooleanCellValue();
String cellvalue = "false";
if (boolvalue) {
cellvalue = "true";
} else
myRow[cell_index] = cellvalue;
break;
case Cell.CELL_TYPE_NUMERIC:
myRow[cell_index] = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
myRow[cell_index] = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_ERROR:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_FORMULA:
myRow[cell_index] = cell.getCellFormula();
break;
}
cell_index++;
}
if (cell_index != number_of_cells) {
for (int x = cell_index; x < number_of_cells; x++)
myRow[cell_index] = "";
}
myEntries.add(myRow);
}
file.close();
}
catch (Exception ex) {
System.out.print("Exception during XLSX import: " + ex.toString());
}
break;
case "csv":
try {
CSVFileReader = new CSVReader(new FileReader(DataFile), ',', '"', '\0');
myEntries = CSVFileReader.readAll();
} catch (Exception e) {
}
}
columnnames = (String[]) myEntries.get(0);
DefaultTableModel tableModel = new DefaultTableModel(columnnames, myEntries.size() - 1);
rowcount = tableModel.getRowCount();
this.number_of_records = rowcount;
for (int x = 0; x < rowcount + 1; x++) {
int columnnumber = 0;
if (x > 0) {
for (String thiscellvalue : (String[]) myEntries.get(x)) {
tableModel.setValueAt(thiscellvalue, x - 1, columnnumber);
columnnumber++;
}
}
}
DataTable = new JTable(tableModel);
int number_of_rows = DataTable.getRowCount();
if (number_of_rows < 20) {
DataTable.setPreferredScrollableViewportSize(
new Dimension(1200, number_of_rows * DataTable.getRowHeight()));
}
} else {
columnnames[0] = "Stored URL List:" + csvFile;
DefaultTableModel tableModel = new DefaultTableModel(columnnames, 0);
DataTable = new JTable(tableModel);
DataTable.getColumnModel().getColumn(0).setPreferredWidth(200);
DataTable.setPreferredScrollableViewportSize(new Dimension(20, 0));
}
}
From source file:cn.afterturn.easypoi.excel.imports.CellValueService.java
License:Apache License
/** * ??/*w w w. j a v a 2s. co m*/ * * @param cell * @param entity * @return */ private Object getCellValue(String classFullName, Cell cell, ExcelImportEntity entity) { if (cell == null) { return ""; } Object result = null; if ("class java.util.Date".equals(classFullName) || "class java.sql.Date".equals(classFullName) || ("class java.sql.Time").equals(classFullName) || ("class java.time.Instant").equals(classFullName) || ("class java.time.LocalDate").equals(classFullName) || ("class java.time.LocalDateTime").equals(classFullName) || ("class java.sql.Timestamp").equals(classFullName)) { //FIX: ?yyyyMMdd cell.getDateCellValue() ? if (CellType.NUMERIC == cell.getCellType() && DateUtil.isCellDateFormatted(cell)) { result = DateUtil.getJavaDate(cell.getNumericCellValue()); } else { String val = ""; try { val = cell.getStringCellValue(); } catch (Exception e) { cell.setCellType(CellType.STRING); val = cell.getStringCellValue(); } result = getDateData(entity, val); if (result == null) { return null; } } if (("class java.time.Instant").equals(classFullName)) { result = ((Date) result).toInstant(); } else if (("class java.time.LocalDate").equals(classFullName)) { result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDate(); } else if (("class java.time.LocalDateTime").equals(classFullName)) { result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime(); } else if (("class java.sql.Date").equals(classFullName)) { result = new java.sql.Date(((Date) result).getTime()); } else if (("class java.sql.Time").equals(classFullName)) { result = new Time(((Date) result).getTime()); } else if (("class java.sql.Timestamp").equals(classFullName)) { result = new Timestamp(((Date) result).getTime()); } } else { switch (cell.getCellType()) { case STRING: result = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().getString(); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { if ("class java.lang.String".equals(classFullName)) { result = formateDate(entity, cell.getDateCellValue()); } } else { result = readNumericCell(cell); } break; case BOOLEAN: result = Boolean.toString(cell.getBooleanCellValue()); break; case BLANK: break; case ERROR: break; case FORMULA: try { result = readNumericCell(cell); } catch (Exception e1) { try { result = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().getString(); } catch (Exception e2) { throw new RuntimeException("???", e2); } } break; default: break; } } return result; }
From source file:cn.mypandora.util.MyExcelUtil.java
License:Apache License
/** * @param workbook /*from ww w. j a v a2 s . c om*/ * @param fieldNames ?? * @param sheetName ??? * @return */ private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) { String[] strKey = fieldNames.split(","); List<Map<String, String>> listMap = new ArrayList<>(); int i = 1; try { Sheet sheet; if (sheetName.length == 0) { sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName[0]); } while (true) { Row row = sheet.getRow(i); if (row == null) { break; } Map<String, String> map = new HashMap<String, String>(); map.put("rowid", String.valueOf(row.getRowNum())); for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) { Cell cell; cell = row.getCell(keyIndex); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { // ?cell?Date if (DateUtil.isCellDateFormatted(cell)) { // Date?CellDate SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())); } // else { // ??Cell Integer num = new Integer((int) cell.getNumericCellValue()); cellValue = String.valueOf(num); } break; } case Cell.CELL_TYPE_STRING: cellValue = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: cellValue = " "; } } map.put(strKey[keyIndex], cellValue); } listMap.add(map); i++; } } catch (Exception e) { logger.debug("?" + i + "??"); throw new RuntimeException(e); } return listMap; }