List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getStringCellValue
@Override
public String getStringCellValue()
For numeric cells we throw an exception.
From source file:algoritmos.Recorte.java
private static void buscarImagenes(String dni) { ImagenHuella = null;/* ww w . ja v a2s .c om*/ ImagenFirma = null; String dn = null; double d = -1; if (tryParseInt(dni)) { int nd = Integer.parseInt(dni); try { // InputStream ExcelFileToRead = new FileInputStream("C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/registro.nacional.v.1.xlsx"); InputStream ExcelFileToRead = new FileInputStream(Recorte.rutaGeneral); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Iterator rows = sheet.rowIterator(); row = (XSSFRow) rows.next(); int salir = 0; while (rows.hasNext()) { row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); int i = 0; while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (i == 2) { d = cell.getNumericCellValue(); int num = (int) d; dn = num + ""; // System.out.print("dni: "+num+" "); } if (dn != null) { if (d == nd) { if (i == 4) { String dh = cell.getStringCellValue(); String dr = null; dr = Recorte.rutaHuella + "/" + dh + ".jpg"; // dr=Recorte.rutaHuella+"/"+dh; File file = new File(dr); if (!file.exists()) { dr = Recorte.rutaHuella + "/" + dh + ".png"; file = new File(dr); if (!file.exists()) { dr = Recorte.rutaHuella + "/" + dh + ".jpg.png"; file = new File(dr); } ImagenHuella = ImageIO.read(file); } else { ImagenHuella = Algoritmo_Huellas.readImage(file); } // ImagenHuella= ImageIO.read(file); salir = 1; } /* if(i==4){ double dh=cell.getNumericCellValue(); int num=(int) dh; String dr=null; if(num<10){ // dr="C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/huellas.jpg/00"+num+".jpg"; dr=Recorte.rutaHuella+"/00"+num+".jpg"; } else if(num>9 && num<100){ // dr="C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/huellas.jpg/0"+num+".jpg"; dr=Recorte.rutaHuella+"/0"+num+".jpg"; } else{ System.out.println("c"); // dr="C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/huellas.jpg/"+num+".jpg"; dr=Recorte.rutaHuella+"/"+num+".jpg"; } File file = new File(dr); ImagenHuella= ImageIO.read(file); // cell=(XSSFCell) cells.next(); // i++; }*/ if (i == 5) { String dh = cell.getStringCellValue(); String dr = null; // dr="C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/firmas.jpg/"+dh+".jpg"; dr = Recorte.rutaFirma + "/" + dh + ".jpg"; // dr=Recorte.rutaFirma+"/"+dh; File file = new File(dr); if (!file.exists()) { dr = Recorte.rutaFirma + "/" + dh + ".png"; file = new File(dr); if (!file.exists()) { dr = Recorte.rutaFirma + "/" + dh + ".jpg.png"; file = new File(dr); } ImagenFirma = ImageIO.read(file); } else { ImagenFirma = Algoritmo_Huellas.readImage(file); } // ImagenFirma= ImageIO.read(file); salir = 1; } } } i++; } i = 0; if (salir == 1) break; } } catch (FileNotFoundException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:CDatos.Excel.java
/** * Lee una hoja de un excel y devuelve una matriz con los datos * @return Matriz con los datos del excel *///from w w w .j a v a 2s . c om public ArrayList getDatosHoja() { ArrayList<ArrayList> filas = new ArrayList(); XSSFSheet sheet = workbook.getSheetAt(hoja); int numColumnas = -1; // Recorremos fila a fila for (int r = 0; r <= sheet.getLastRowNum(); r++) { ArrayList<String> celdas = new ArrayList(); XSSFRow row = sheet.getRow(r); if (row == null) break; else { // En la primera fila se leen las cabeceras, por lo que aprovechamos para // guardar el nmero de columnas porque cuando una fila tiene celdas vacas el tamao // de la lista es menor if (numColumnas == -1) numColumnas = row.getLastCellNum(); // Recorremos celda a celda for (int c = 0; c < numColumnas; c++) { XSSFCell cell = row.getCell(c); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat formateador = new SimpleDateFormat("yyyy-MM-dd"); //cellValue = cell.getDateCellValue().toString(); cellValue = formateador.format(cell.getDateCellValue()); } else { cellValue = (Integer.toString((int) cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } } celdas.add(cellValue); } filas.add(celdas); } } return filas; }
From source file:com.accounting.mbeans.SmsController.java
public void setupSmsInfoData(Iterator rows) { // System.out.println("setupSmsInfoData called."); exceluploadSmsList = new ArrayList<SmsInfo>(); // col1 = mobile number, col2 = send on date optionalCOLumn int rowcount = 0; //skipping first row while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); if (rowcount > 0) { int cellCount = 0; SmsInfo smsinfo = new SmsInfo(); smsinfo.setSmsText(smsMessage); smsinfo.setOrgId(orgid);/* ww w. j av a2s . c o m*/ while (cells.hasNext()) { try { XSSFCell cell = (XSSFCell) cells.next(); cell.setCellType(Cell.CELL_TYPE_STRING); System.out.println("mobile " + cell.getStringCellValue()); if (cellCount == 0) { smsinfo.setMobileNumber(String.valueOf(cell.getStringCellValue())); } if (cellCount == 1) { try { smsinfo.setSentDate(cell.getDateCellValue()); } catch (Exception e) { } } cellCount++; } catch (Exception e) { e.printStackTrace(); } } exceluploadSmsList.add(smsinfo); } rowcount++; } }
From source file:com.appdynamics.jrbronet.projectplan.ExcelManager.java
private static void copyRow(XSSFWorkbook workbook, XSSFSheet sourceWorksheet, int sourceRowNum, XSSFSheet destinationWorksheet, int destinationRowNum) { // Get the source / new row XSSFRow newRow = destinationWorksheet.getRow(destinationRowNum); XSSFRow sourceRow = sourceWorksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { destinationWorksheet.shiftRows(destinationRowNum, destinationWorksheet.getLastRowNum(), 1); } else {//from ww w.j a va2s.c o m newRow = destinationWorksheet.createRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); //newCell.setCellValue(oldCell.getRawValue()); //newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); 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; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row /* for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() )), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } */ }
From source file:com.axelor.apps.admin.service.ViewDocExportService.java
License:Open Source License
private void writeCell(XSSFRow row, Integer oldRowIndex, int count, XSSFCellStyle cellStyle) { XSSFRow oldRow = oldSheet.getRow(oldRowIndex); while (count < oldRow.getLastCellNum()) { XSSFCell oldCell = oldRow.getCell(count); Cell cell = row.createCell(count); cell.setCellStyle(cellStyle);//from w w w . j av a2s .co m cell.setCellValue(oldCell.getStringCellValue()); count++; } }
From source file:com.centurylink.mdw.drools.Excel2007Parser.java
License:Apache License
@SuppressWarnings("deprecation") private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) { int mergedRegionCount = sheet.getNumMergedRegions(); CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount]; for (int i = 0; i < mergedRegionCount; i++) { mergedRanges[i] = sheet.getMergedRegion(i); }/* www . j av a 2 s . c o m*/ for (int i = 0; i <= sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); if (row != null) { newRow(listeners, i, row.getLastCellNum()); for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) { XSSFCell cell = row.getCell(cellNum); if (cell != null) { CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges); if (merged != null) { XSSFRow topRow = sheet.getRow(merged.getFirstRow()); XSSFCell topLeft = topRow.getCell(merged.getFirstColumn()); String cellValue = topLeft.getCellType() == CellType.NUMERIC.getCode() ? String.valueOf(topLeft.getNumericCellValue()) : topLeft.getStringCellValue(); newCell(listeners, i, cellNum, cellValue, topLeft.getColumnIndex()); } else { String cellValue = null; if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cellValue = String.valueOf(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = String.valueOf(cell.getNumericCellValue()); else cellValue = cell.getStringCellValue(); newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED); } } } } } finishSheet(listeners); }
From source file:com.centurylink.mdw.workflow.drools.Excel2007Parser.java
License:Apache License
private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) { int mergedRegionCount = sheet.getNumMergedRegions(); CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount]; for (int i = 0; i < mergedRegionCount; i++) { mergedRanges[i] = sheet.getMergedRegion(i); }//from w ww . jav a 2s . com for (int i = 0; i <= sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); if (row != null) { newRow(listeners, i, row.getLastCellNum()); for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) { XSSFCell cell = row.getCell(cellNum); if (cell != null) { CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges); if (merged != null) { XSSFRow topRow = sheet.getRow(merged.getFirstRow()); XSSFCell topLeft = topRow.getCell(merged.getFirstColumn()); newCell(listeners, i, cellNum, topLeft.getStringCellValue(), topLeft.getColumnIndex()); } else { String cellValue = null; if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cellValue = String.valueOf(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = String.valueOf(cell.getNumericCellValue()); else cellValue = cell.getStringCellValue(); newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED); } } } } } finishSheet(listeners); }
From source file:com.cn.util.ExcelImport.java
/** * ?2007excel/* w w w .j ava 2 s . c om*/ * * @param file * @return */ private static List<List<Object>> read2007Excel(InputStream inputStream) throws IOException { List<List<Object>> dataList = new ArrayList<>(); XSSFWorkbook xwb = new XSSFWorkbook(inputStream); XSSFSheet sheet = xwb.getSheetAt(0); XSSFRow row = null; XSSFCell cell = null; Object val = null; DecimalFormat df = new DecimalFormat("0");// ? SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ? for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); if (row == null) { continue; } List<Object> objList = new ArrayList<>(); for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null) { val = null; objList.add(val); continue; } switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { val = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { val = df.format(cell.getNumericCellValue()); } else { val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } break; case XSSFCell.CELL_TYPE_BOOLEAN: val = cell.getBooleanCellValue(); break; case XSSFCell.CELL_TYPE_BLANK: val = ""; break; default: val = cell.toString(); break; } objList.add(val); } dataList.add(objList); } return dataList; }
From source file:com.excel.javafx.frames.MainFrame.java
public boolean compareTwoRows(XSSFRow row1, XSSFRow row2) { expectedValue = new ArrayList<String>(); actualValue = new ArrayList<String>(); if ((row1 == null) && (row2 == null)) { return true; } else if ((row1 == null) || (row2 == null)) { return false; }/*from w ww . ja v a 2 s.c om*/ boolean equalRows = true; // Compare all cells in a row //for(int i=firstCell1; i <= lastCell1; i++) { XSSFCell cell1 = row1.getCell(sourceColumnList.getSelectedIndex()); XSSFCell cell2 = row2.getCell(destColumnList.getSelectedIndex()); if (!compareTwoCells(cell1, cell2)) { equalRows = false; cell1.setCellType(HSSFCell.CELL_TYPE_STRING); cell2.setCellType(HSSFCell.CELL_TYPE_STRING); System.err.println(" Cell " + cell1.getStringCellValue() + " - NOt Equal"); System.err.println(" Cell " + cell2.getStringCellValue() + " - NOt Equal"); expectedValue.add(cell1.getStringCellValue()); actualValue.add(cell2.getStringCellValue()); } else { System.out.println(" Cell " + cell2 + " - Equal"); } // } return equalRows; }
From source file:com.excel.javafx.frames.MainFrame.java
public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) { if ((cell1 == null) && (cell2 == null)) { return true; } else if ((cell1 == null) || (cell2 == null)) { return false; }//w w w . jav a 2 s. c om boolean equalCells = false; int type1 = cell1.getCellType(); int type2 = cell2.getCellType(); if (type1 == type2) { if (cell1.getCellStyle().equals(cell2.getCellStyle())) { // Compare cells based on its type switch (cell1.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: if (cell1.getCellFormula().equals(cell2.getCellFormula())) { equalCells = true; } break; case HSSFCell.CELL_TYPE_NUMERIC: if (cell1.getNumericCellValue() == cell2.getNumericCellValue()) { equalCells = true; } break; case HSSFCell.CELL_TYPE_STRING: if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) { equalCells = true; } break; case HSSFCell.CELL_TYPE_BLANK: if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) { equalCells = true; } break; case HSSFCell.CELL_TYPE_BOOLEAN: if (cell1.getBooleanCellValue() == cell2.getBooleanCellValue()) { equalCells = true; } break; case HSSFCell.CELL_TYPE_ERROR: if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) { equalCells = true; } break; default: if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) { equalCells = true; } break; } } else { return false; } } else { return false; } return equalCells; }