List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
From source file:com.xhsoft.framework.common.file.ExcelHandle.java
License:Open Source License
/** * ????.xls?/* w w w . j a va 2 s.c o m*/ * @params {:,:} * @return String * @author lijiangwei * @since 2012-11-12 */ private String getCellValue(HSSFCell xls_cell) { String value = ""; switch (xls_cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: value = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: value = String.valueOf(xls_cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: break; case HSSFCell.CELL_TYPE_FORMULA: String.valueOf(xls_cell.getCellFormula()); break; case HSSFCell.CELL_TYPE_NUMERIC: value = String.valueOf(xls_cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: value = String.valueOf(xls_cell.getStringCellValue()); break; default: break; } return value; }
From source file:com.xpn.xwiki.plugin.lucene.textextraction.MSExcelTextExtractor.java
License:Open Source License
/** * Extracts all text from each cell of the sheet *//* ww w.j av a 2 s. com*/ private void parseCell(HSSFCell cell, StringBuffer cleanBuffer) { String cellValue = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { cellValue = cell.getRichStringCellValue().getString(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { HSSFCellStyle style = cell.getCellStyle(); short formatId = style.getDataFormat(); String formatPattern = mDataFormat.getFormat(formatId); formatPattern = replace(formatPattern, "\\ ", " "); if (isCellDateFormatted(cell)) { // This is a date formatPattern = replace(formatPattern, "mmmm", "MMMM"); formatPattern = replace(formatPattern, "/", "."); SimpleDateFormat format; try { format = new SimpleDateFormat(formatPattern); } catch (Throwable thr) { if (LOG.isDebugEnabled()) { LOG.debug("Creating date format failed: '" + formatPattern + "'", thr); } format = new SimpleDateFormat(); } double numberValue = cell.getNumericCellValue(); Date date = HSSFDateUtil.getJavaDate(numberValue); cellValue = format.format(date); } else { // This is a Number DecimalFormat format; try { format = new DecimalFormat(formatPattern); } catch (Throwable thr) { if (LOG.isDebugEnabled()) { LOG.debug("Creating number format failed: '" + formatPattern + "'", thr); } format = new DecimalFormat(); } double numberValue = cell.getNumericCellValue(); cellValue = format.format(numberValue); } } if (cellValue != null) { cellValue = cellValue.trim(); if (cellValue.length() != 0) { cleanBuffer.append(cellValue); cleanBuffer.append(" "); } } }
From source file:com.xpn.xwiki.plugin.lucene.textextraction.MSExcelTextExtractor.java
License:Open Source License
/** * Checks cell is date formatted or not. * //from w ww . j ava 2s . c o m * @return boolean */ private boolean isCellDateFormatted(HSSFCell cell) { short format = cell.getCellStyle().getDataFormat(); if (HSSFDateUtil.isValidExcelDate(cell.getNumericCellValue())) { if (HSSFDateUtil.isCellDateFormatted(cell)) { return true; } else { String fmtText = mDataFormat.getFormat(format); if (fmtText != null) { fmtText = fmtText.toLowerCase(); if (fmtText.indexOf("d") >= 0 || fmtText.indexOf("m") >= 0 || fmtText.indexOf("y") >= 0 || fmtText.indexOf("h") >= 0 || fmtText.indexOf("s") >= 0) { return true; } } } } return false; }
From source file:com.xx.platform.util.tools.ms.ExcelExtractor.java
License:Apache License
public String extractText(POIFSFileSystem poifs) throws Exception { StringBuffer resultText = new StringBuffer(); HSSFWorkbook wb = new HSSFWorkbook(poifs, true); if (wb == null) { return ""; }/* w ww . ja va 2 s. c o m*/ HSSFSheet sheet; HSSFRow row; HSSFCell cell; int sNum = 0; int rNum = 0; int cNum = 0; sNum = wb.getNumberOfSheets(); for (int i = 0; i < sNum; i++) { if ((sheet = wb.getSheetAt(i)) == null) { continue; } rNum = sheet.getLastRowNum(); for (int j = 0; j <= rNum; j++) { if ((row = sheet.getRow(j)) == null) { continue; } cNum = row.getLastCellNum(); for (int k = 0; k < cNum; k++) { if ((cell = row.getCell((short) k)) != null) { /*if(HSSFDateUtil.isCellDateFormatted(cell) == true) { resultText += cell.getDateCellValue().toString() + " "; } else */ if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { resultText.append(cell.getStringCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double d = new Double(cell.getNumericCellValue()); resultText.append(d.toString()); } /* else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){ resultText += cell.getCellFormula() + " "; } */ } } } } return resultText.toString(); }
From source file:com.xx.platform.util.tools.ms.ExcelExtrator.java
License:Apache License
public String extractText(POIFSFileSystem poifs) throws Exception { List<Map<String, String>> list = new ArrayList<Map<String, String>>(); String text = null;//w w w. jav a2 s. c o m try { HSSFWorkbook wb = new HSSFWorkbook(poifs, true); if (wb == null) { return null; } HSSFSheet sheet; HSSFRow row; HSSFCell cell; int sNum = 0; int rNum = 0; int cNum = 0; sNum = wb.getNumberOfSheets(); for (int i = 0; i < sNum; i++) { if ((sheet = wb.getSheetAt(i)) == null) { continue; } String[] key = null; //field boolean init = false; //key rNum = sheet.getLastRowNum(); for (int j = 0; j <= rNum; j++) { if ((row = sheet.getRow(j)) == null) { continue; } Map<String, String> rowdata = new HashMap<String, String>(); cNum = row.getLastCellNum(); if (!init) key = new String[cNum]; String value = ""; StringBuffer content = new StringBuffer(); for (int k = 0; k < cNum; k++) { if ((cell = row.getCell((short) k)) != null) { if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { value = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double d = new Double(cell.getNumericCellValue()); value = d.toString(); } else value = ""; if (init) { content.append(value); } else { key[k] = value; } } } } } } catch (Exception e) { text = ""; } return text; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
/** * ?cell/*from ww w. jav a 2 s . co m*/ * @param cell * @return */ private static String getCellValue(HSSFCell cell) { if (cell == null) { return ""; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { DecimalFormat df = new DecimalFormat("0"); return df.format(cell.getNumericCellValue()); } default: return String.valueOf(cell.getStringCellValue()); } }
From source file:Compras.ComparaCotizacion.java
void importaDatos(Workbook wb, int col) { Sheet hoja = wb.getSheetAt(0);/* w w w . j av a2 s . c o m*/ Iterator rowIterator = hoja.rowIterator(); List renglones = new ArrayList(); while (rowIterator.hasNext()) { HSSFRow hssfRow = (HSSFRow) rowIterator.next(); List celdas = new ArrayList(); Iterator iterator = hssfRow.cellIterator(); while (iterator.hasNext()) { HSSFCell hssfCell = (HSSFCell) iterator.next(); celdas.add(hssfCell); } renglones.add(celdas); } for (int r = 8; r < renglones.size(); r++) { List aux = (List) renglones.get(r); HSSFCell auxCell = (HSSFCell) aux.get(0); HSSFCell auxCell1 = (HSSFCell) aux.get(1); int res = busca(auxCell.getNumericCellValue(), auxCell1.getNumericCellValue()); if (res != -1) { for (int c = 0; c < aux.size(); c++) { HSSFCell valor = (HSSFCell) aux.get(c); switch (valor.getColumnIndex()) { case 4: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt((int) valor.getNumericCellValue(), res, col); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: t_datos.setValueAt("", res, col); break; default: t_datos.setValueAt("", res, col); break; } break; case 7: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt(valor.getNumericCellValue(), res, col + 1); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col + 1); break; default: t_datos.setValueAt("", res, col + 1); break; } if (t_datos.getValueAt(res, col + 1).toString().compareTo("") != 0) { model.setCeldaEditable(res, col + 3, true); model.setCeldaEditable(res, col + 4, true); } break; case 10: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt((int) valor.getNumericCellValue(), res, col + 2); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col + 2); break; default: t_datos.setValueAt("", res, col + 2); break; } break; } t_datos.setValueAt(false, res, col + 3); t_datos.setValueAt(false, res, col + 4); } } } }
From source file:Creator.TaskManagerPanel.java
/** * Reads a file and returns a list of strings which contain all the variable * names// ww w .j a v a2 s .c o m * * @param filename */ public void readXFile(String filename) { try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; int idCol = -1, idName = -1; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 1; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } if (!sheet.getRow(i).getCell(0).toString().equals("io_id")) { for (int c = 1; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_id")) { idCol = c; break; } } } else { idCol = 0; } if (!sheet.getRow(i).getCell(1).toString().equals("io_name")) { for (int c = 0; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_name")) { idName = c; break; } } } else { idName = 1; } if (!sheet.getRow(i).getCell(2).toString().equals("io_station_id")) { for (int c = 0; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_station_id")) { stationID = (int) sheet.getRow(1).getCell(c).getNumericCellValue(); break; } } } else { stationID = (int) sheet.getRow(1).getCell(2).getNumericCellValue(); } } if (idName == -1 || idCol == -1) { System.out.println("Could not locate io_name or io_id in excel header"); return; } if (stationID == -1) { System.out.println("Couldnt locate station id"); return; } importedIOVariables = new TreeMap<>(); int io_id; String io_name; for (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { cell = row.getCell(idCol); if (cell != null) { io_id = (int) cell.getNumericCellValue(); cell = row.getCell(idName); if (cell != null) { io_name = cell.toString().replace("\"", ""); // Read both name and id importedIOVariables.put(io_name, io_id); } } } } fs.close(); mf.loadImportedIos(importedIOVariables, 2, stationID); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
From source file:Creator.WidgetPanel.java
/** * Reads a file and returns a list of strings which contain all the variable * names//from w w w. j a v a 2 s . com * * @param filename */ public void readXFile(String filename) { try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; int idCol = -1, idName = -1; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 1; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } if (!sheet.getRow(i).getCell(0).toString().equals("io_id")) { for (int c = 1; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_id")) { idCol = c; break; } } } else { idCol = 0; } if (!sheet.getRow(i).getCell(1).toString().equals("io_name")) { for (int c = 1; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_name")) { idName = c; break; } } } else { idName = 1; } if (!sheet.getRow(i).getCell(2).toString().equals("io_station_id")) { for (int c = 0; c < cols; c++) { if (sheet.getRow(i).getCell(c).equals("io_station_id")) { stationID = (int) sheet.getRow(1).getCell(c).getNumericCellValue(); break; } } } else { stationID = (int) sheet.getRow(1).getCell(2).getNumericCellValue(); } } if (idName == -1 || idCol == -1) { System.out.println("Could not locate io_name or io_id in excel header"); return; } if (stationID == -1) { System.out.println("Couldnt locate station id"); return; } importedIOVariables = new TreeMap<>(); int io_id; String io_name; for (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { cell = row.getCell(idCol); if (cell != null) { io_id = (int) cell.getNumericCellValue(); cell = row.getCell(idName); if (cell != null) { io_name = cell.toString().replace("\"", ""); // Read both name and id importedIOVariables.put(io_name, io_id); } } } } fs.close(); mf.loadImportedIos(importedIOVariables, 1, stationID); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
From source file:database_lab1.ExcelToMySQL.java
public void ExcelFileReader(String filename) throws IOException, SQLException { FileInputStream fis = null;//from ww w . j a v a 2 s. c o m Scanner sc = new Scanner(System.in); try { fis = new FileInputStream(filename); String queryValue = (new File(filename).getName().replaceAll("(.xls)", "")); System.out.print("Please input the schema name:"); String schemaName = sc.next(); System.out.print("Please input the database username:"); String userName = sc.next(); System.out.print("Please input the database password:"); String password = sc.next(); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rowIter = sheet.rowIterator(); //variable that will use to find the first row int firstrow = 0, maxColumn = 0; //iterator for the row values while (rowIter.hasNext()) { HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); Vector<String> cellStoreVector = new Vector<String>(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); String cellvalue; //determines if cell value is string or numaric if (myCell.getCellType() == 1) cellvalue = myCell.getStringCellValue(); else cellvalue = (int) myCell.getNumericCellValue() + ""; //adds the value in the vector cellStoreVector.addElement(cellvalue); //prints out the cell valuse System.out.print(cellvalue + " "); } System.out.println(); //if the row is not the first row then, //insert the data in database if (firstrow != 0) { dbConnection db = new dbConnection("jdbc:mysql://localhost:3306/" + schemaName, userName, password); //sql comment String insert = "INSERT INTO " + queryValue + " VALUES (?,?,?,?,?,?);"; PreparedStatement ps = db.getConnection().prepareStatement(insert);//createStatement().executeUpdate(insert); for (int i = 1; i <= cellStoreVector.size(); i++) { ps.setString(i, cellStoreVector.get(i - 1)); } //executing the sql command ps.execute(); } firstrow++; } } catch (IOException e) { e.printStackTrace(); } finally { if (fis != null) { fis.close(); } } }