List of usage examples for org.apache.poi.ss.usermodel Cell getRichStringCellValue
RichTextString getRichStringCellValue();
For numeric cells we throw an exception.
From source file:gov.nih.nci.cananolab.util.ExcelParser.java
License:BSD License
public void printSheet(Sheet sheet) { for (Row row : sheet) { for (Cell cell : row) { CellReference cellRef = new CellReference(cell.getRowIndex(), cell.getColumnIndex()); System.out.print(cellRef.formatAsString()); System.out.print(" - "); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.println(cell.getNumericCellValue()); }//from www . j a v a2 s.c o m break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: System.out.println(); } } } }
From source file:graphbuilder.ExcelParser.java
private static Object loadCellData(Cell cell) { Object result = null;/*w ww . jav a 2s. c om*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; } return result; }
From source file:hjow.hgtable.util.XLSXUtil.java
License:Apache License
/** * <p>XLSX ? ? ?? . ? ? ?? ?? , ? ? ?? ? ? ?? ?.</p> * /*from w w w . j a va 2 s . co m*/ * @param file : XLSX ? * @return ? ? */ public static List<TableSet> toTableSets(File file) { List<TableSet> tableSets = new Vector<TableSet>(); org.apache.poi.ss.usermodel.Workbook workbook = null; if (file == null) throw new NullPointerException(Manager.applyStringTable("Please select file !!")); if (!file.exists()) throw new NullPointerException(Manager.applyStringTable("File") + " " + file.getAbsolutePath() + " " + Manager.applyStringTable("is not exist")); boolean isHead = true; int rowNum = 0; int cellNum = 0; int cellCount = 0; FileInputStream fileStream = null; try { if (file.getAbsolutePath().endsWith(".xlsx") || file.getAbsolutePath().endsWith(".XLSX")) { workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook(file); } else if (file.getAbsolutePath().endsWith(".xls") || file.getAbsolutePath().endsWith(".XLS")) { fileStream = new FileInputStream(file); workbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(fileStream); } org.apache.poi.ss.usermodel.FormulaEvaluator evals = workbook.getCreationHelper() .createFormulaEvaluator(); org.apache.poi.ss.usermodel.Sheet sheet = null; for (int x = 0; x < workbook.getNumberOfSheets(); x++) { TableSet newTableSet = new DefaultTableSet(); newTableSet.setColumns(new Vector<Column>()); sheet = workbook.getSheetAt(x); newTableSet.setName(sheet.getSheetName()); rowNum = 0; isHead = true; String targetData = null; for (org.apache.poi.ss.usermodel.Row row : sheet) { cellNum = 0; for (org.apache.poi.ss.usermodel.Cell cell : row) { try { if (cellNum >= cellCount) { throw new IndexOutOfBoundsException( Manager.applyStringTable("There are some cells not have their heads") + ", " + Manager.applyStringTable("Head count") + " : " + cellCount + ", " + Manager.applyStringTable("Cell Number") + " : " + cellNum); } switch (cell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: if (isHead) { newTableSet.getColumns().add(new Column( cell.getRichStringCellValue().getString(), Column.TYPE_STRING)); } else { targetData = cell.getRichStringCellValue().getString(); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_DATE)); } else { targetData = String.valueOf(cell.getDateCellValue()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } else { if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC)); } else { double values = cell.getNumericCellValue(); double intPart = values - ((double) ((int) values)); if (intPart == 0.0) { targetData = String.valueOf(((int) values)); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_INTEGER); } else { targetData = String.valueOf(values); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_BOOLEAN)); } else { targetData = String.valueOf(cell.getBooleanCellValue()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC)); } else { if (evals.evaluateFormulaCell(cell) == 0) { targetData = String.valueOf(cell.getNumericCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_NUMERIC); } else if (evals.evaluateFormulaCell(cell) == 1) { targetData = String.valueOf(cell.getStringCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_STRING); } else if (evals.evaluateFormulaCell(cell) == 4) { targetData = String.valueOf(cell.getBooleanCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BOOLEAN); } else { targetData = String.valueOf(cell.getCellFormula()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: if (isHead) { newTableSet.getColumns().add(new Column("", Column.TYPE_STRING)); } else { targetData = ""; newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BLANK); } break; default: if (isHead) { newTableSet.getColumns().add(new Column("", Column.TYPE_STRING)); } else { try { targetData = cell.getStringCellValue(); } catch (Exception e1) { e1.printStackTrace(); } newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; } if (isHead) { cellCount++; } else { while (rowNum > 0 && newTableSet.getColumns().get(cellNum).getData().size() < rowNum) { newTableSet.getColumns().get(cellNum).getData().add(""); } if (targetData != null) newTableSet.getColumns().get(cellNum).getData().add(targetData); else { newTableSet.getColumns().get(cellNum).getData().add(""); } } } catch (ArrayIndexOutOfBoundsException e1) { StringBuffer err = new StringBuffer(""); for (StackTraceElement errEl : e1.getStackTrace()) { err = err.append("\t " + errEl + "\n"); } String cellObject = null; try { cellObject = cell.getStringCellValue(); } catch (Exception e2) { } throw new ArrayIndexOutOfBoundsException( Manager.applyStringTable("Array index out of range") + " <- " + Manager.applyStringTable("Reading xlsx file") + " : " + file.getName() + ", " + sheet.getSheetName() + "\n" + Manager.applyStringTable("On") + " " + Manager.applyStringTable("Row") + " " + rowNum + ", " + Manager.applyStringTable("Cell") + " " + cellNum + ", " + Manager.applyStringTable("Value") + " : " + String.valueOf(cellObject) + "\n " + Manager.applyStringTable("<-\n") + err + "\n " + Manager.applyStringTable("Original Message") + "...\n" + e1.getMessage() + "\n" + Manager.applyStringTable("End")); } cellNum++; } isHead = false; rowNum++; } fillTableSet(newTableSet); newTableSet.removeEmptyColumn(true); tableSets.add(newTableSet); } return tableSets; } catch (Throwable e) { if (Main.MODE >= DebuggingUtil.DEBUG) e.printStackTrace(); Main.logError(e, Manager.applyStringTable("On reading xlsx") + " : " + file + "\n" + Manager.applyStringTable("At rownum") + " " + rowNum + ", " + Manager.applyStringTable("cellnum") + " " + cellNum); return null; } finally { try { workbook.close(); } catch (Throwable e) { } try { if (fileStream != null) fileStream.close(); } catch (Throwable e) { } } }
From source file:jasco.Jasco.java
private List<List<String>> convertSheetToArrayList(int y, int height, int x, int width, Sheet sheet1) { List<List<String>> rows = new ArrayList<>(); for (int row = y; row < y + height; row++) { ArrayList<String> rowData = new ArrayList<>(); for (int col = x; col < x + width; col++) { Cell cell = sheet1.getRow(row).getCell(col); switch (cell.getCellType()) { case (Cell.CELL_TYPE_STRING): RichTextString str = cell.getRichStringCellValue(); rowData.add(str.toString()); break; case (Cell.CELL_TYPE_NUMERIC): rowData.add("" + cell.getNumericCellValue()); break; case (Cell.CELL_TYPE_BOOLEAN): rowData.add("" + cell.getBooleanCellValue()); break; case (Cell.CELL_TYPE_FORMULA): switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: rowData.add("" + cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: rowData.add("" + cell.getRichStringCellValue()); break; }//from w w w.j a va 2s.com break; case (Cell.CELL_TYPE_BLANK): rowData.add(""); break; default: System.out.println("unknown cell type" + cell.getCellType()); } rows.add(rowData); } } return rows; }
From source file:jp.ryoyamamoto.poiutils.Cells.java
License:Apache License
private static void copyCellValue(Cell source, Cell target) { switch (source.getCellType()) { case Cell.CELL_TYPE_NUMERIC: target.setCellValue(source.getNumericCellValue()); break;//from w w w . java 2s . com case Cell.CELL_TYPE_STRING: target.setCellValue(source.getRichStringCellValue()); break; case Cell.CELL_TYPE_FORMULA: target.setCellFormula(source.getCellFormula()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: target.setCellValue(source.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: target.setCellErrorValue(source.getErrorCellValue()); break; } }
From source file:ket_noi_DB.ket_noi_kh.java
public void loadFile(Workbook workbook, String tenCSDL) { Sheet sheet = workbook.getSheetAt(0); int num = 0;//from w w w .j av a 2 s. com for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) { String sql = "INSERT INTO " + tenCSDL + " (makh, tenkh, sdt, diachi, tichdiem) VALUES ("; Row row = rit.next(); for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) { Cell cell = cit.next(); cell.setCellType(Cell.CELL_TYPE_STRING); sql = sql + "'" + cell.getRichStringCellValue().toString() + "'" + ","; } sql = sql.substring(0, sql.length() - 1); sql = sql + ")"; try { statement.executeUpdate(sql); } catch (SQLException ex) { // li lnh truy vn, do trng lp m khch hng num++; } } JOptionPane.showMessageDialog(null, "? thm d liu, c " + num + " m khch hng b trng khng c thm"); }
From source file:ket_noi_excel.ket_noi_excel_cd.java
public void loadFile(String path, boolean isXLS) { PreparedStatement pst = null; System.out.println("? ch?n"); try {/*from w ww . j a v a 2 s.c o m*/ org.apache.poi.ss.usermodel.Sheet sheet = null; if (isXLS == true) { org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook(new FileInputStream(path)); sheet = workbook.getSheetAt(0); } else { org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(new FileInputStream(path)); sheet = workbook.getSheetAt(0); } for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) { Row row = rit.next(); int i = 1; String query = "INSERT INTO " + tableCD + " (`Tn SP`, `M SP`, `Tn Ca S`, `Tn Nhc S`, " + "`Gi Bn`, `S Lng`, `Th Loi`, `Nm Pht Hnh`, `Ngy Nhp`, `S Phiu`, `Chit Khu`)" + " VALUES (?,?,?,?,?,?,?,?,?,?,?);"; pst = conn.prepareStatement(query); for (Iterator<org.apache.poi.ss.usermodel.Cell> cit = row.cellIterator(); cit.hasNext();) { org.apache.poi.ss.usermodel.Cell cell = cit.next(); cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING); if (i == 5) pst.setDouble(i, Double.parseDouble(cell.getRichStringCellValue().toString())); else if (i == 6 || i == 11) pst.setInt(i, Integer.parseInt(cell.getRichStringCellValue().toString())); else pst.setString(i, cell.getRichStringCellValue().toString()); i++; if (i >= 12) break; } if (pst.executeUpdate() > 0) { System.out.println("Thm thnh cng"); } else { System.out.println("Li khi thm sn phm\n"); } } } catch (IOException ex) { Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex); } JOptionPane.showMessageDialog(null, "? thm d liu thnh cng"); }
From source file:ket_noi_excel.ket_noi_excel_dvd.java
public void loadFile(String path, boolean isXLS) { PreparedStatement pst = null; System.out.println("? ch?n"); try {/*from w w w . ja v a 2s . c om*/ org.apache.poi.ss.usermodel.Sheet sheet = null; if (isXLS == true) { org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook(new FileInputStream(path)); sheet = workbook.getSheetAt(0); } else { org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(new FileInputStream(path)); sheet = workbook.getSheetAt(0); } for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) { Row row = rit.next(); int i = 1; String query = "INSERT INTO " + tableDVD + " (`Tn SP`, `M SP`, `Tn Din Vin`, `Tn ?o Din`, " + "`Gi Bn`, `S Lng`, `Th Loi`, `Nm Pht Hnh`, `Ngy Nhp`, `S Phiu`, `Chit Khu`)" + " VALUES (?,?,?,?,?,?,?,?,?,?,?);"; pst = conn.prepareStatement(query); for (Iterator<org.apache.poi.ss.usermodel.Cell> cit = row.cellIterator(); cit.hasNext();) { org.apache.poi.ss.usermodel.Cell cell = cit.next(); cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING); if (i == 5) pst.setDouble(i, Double.parseDouble(cell.getRichStringCellValue().toString())); else if (i == 6 || i == 11) pst.setInt(i, Integer.parseInt(cell.getRichStringCellValue().toString())); else pst.setString(i, cell.getRichStringCellValue().toString()); i++; if (i >= 12) break; } if (pst.executeUpdate() > 0) { System.out.println("Thm thnh cng"); } else { System.out.println("Li khi thm sn phm\n"); } } } catch (IOException ex) { Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex); } JOptionPane.showMessageDialog(null, "? thm d liu thnh cng"); }
From source file:ket_noi_excel.ket_noi_excel_sach.java
public void loadFile(String path, boolean isXLS) { PreparedStatement pst = null; System.out.println("? ch?n"); try {/*from w w w.j a v a2 s .c om*/ org.apache.poi.ss.usermodel.Sheet sheet = null; if (isXLS == true) { org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook(new FileInputStream(path)); sheet = workbook.getSheetAt(0); } else { org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(new FileInputStream(path)); sheet = workbook.getSheetAt(0); } for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) { Row row = rit.next(); int i = 1; String query = "INSERT INTO " + tableSach + " (`Tn SP`, `M SP`," + " `Tn T/G`, `Th Loi`,`Gi Bn`, `S Lng`, `Ngy Nhp`, " + "`S Phiu`, `Chit Khu`) VALUES (?,?,?,?,?,?,?,?,?);"; pst = conn.prepareStatement(query); for (Iterator<org.apache.poi.ss.usermodel.Cell> cit = row.cellIterator(); cit.hasNext();) { org.apache.poi.ss.usermodel.Cell cell = cit.next(); cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING); if (i == 6 || i == 9) pst.setInt(i, Integer.parseInt(cell.getRichStringCellValue().toString())); else if (i == 5) pst.setDouble(i, Double.parseDouble(cell.getRichStringCellValue().toString())); else pst.setString(i, cell.getRichStringCellValue().toString()); i++; if (i >= 12) break; } if (pst.executeUpdate() > 0) { System.out.println("Thm thnh cng"); } else { System.out.println("Li khi thm sn phm\n"); } } } catch (IOException ex) { Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex); } JOptionPane.showMessageDialog(null, "? thm d liu thnh cng"); }
From source file:LogicModel.excel_Manage.java
public static void showExelData(List sheetsData) { ///*from ww w. ja v a 2s . c om*/ // Recorre la lista que contiene las hojas del libro de excel // for (int i = 0; i < sheetsData.size(); i++) { List list = (List) sheetsData.get(i); for (int j = 0; j < list.size(); j++) { Cell cell = (Cell) list.get(j); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.print(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { System.out.print(cell.getRichStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { System.out.print(cell.getBooleanCellValue()); } if (j < list.size() - 1) { System.out.print(", "); } } System.out.println(""); } }