List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum
short getLastCellNum();
From source file:com.zlfun.framework.excel.ExcelUtils.java
private static <T> void fill(Class<T> clazz, List<T> result, String fileName, InputStream is) { try {// w w w. j a v a 2s . co m Workbook excel = null; if (fileName.indexOf(".xlsx") > 0) { excel = new XSSFWorkbook(is);// Excel2007 } else if (fileName.indexOf(".xls") > 0) { excel = new HSSFWorkbook(is);// Excel2003 } else { return; } FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator(); Sheet sheet = excel.getSheetAt(0);// ?0 // ????1 List<String> header = new ArrayList<String>(); if (sheet.getLastRowNum() >= 0) { Row row = sheet.getRow(0);// ? for (int i = 0; i < row.getLastCellNum(); i++) { Cell cell = row.getCell(i);// ?? if (cell != null) {// ?cellStr header.add(cell.getStringCellValue()); } } } // if (sheet.getLastRowNum() > 1) { for (int i = 1; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i);// ? if (row == null) {// ?? continue; } Map<String, String> map = genRowMap(row, header, evaluator); T t = fill(map, clazz.newInstance()); result.add(t); } } } catch (IOException ex) { Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex); } catch (InstantiationException ex) { Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex); } catch (IllegalAccessException ex) { Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex); } finally {// ? if (is != null) { try { is.close(); } catch (IOException ex) { Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex); } } } return; }
From source file:common.ReadExcelData.java
License:Apache License
public static boolean isRowEmpty(Row row) { for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c);//from w ww .j a va 2s . co m if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) return false; } return true; }
From source file:controller.VisitasController.java
public void makeRowBold(Workbook wb, Row row) { CellStyle style = wb.createCellStyle();//Create style Font font = wb.createFont();//Create font font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(new HSSFColor.WHITE().getIndex());//Make font bold style.setFont(font);//set it to bold style.setAlignment(HorizontalAlignment.CENTER); style.setFillBackgroundColor(new HSSFColor.BLACK().getIndex()); style.setFillForegroundColor(new HSSFColor.BLACK().getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); for (int i = 0; i < row.getLastCellNum(); i++) { if (!row.getCell(i).getStringCellValue().equals("")) { row.getCell(i).setCellStyle(style);//Set the sty;e }//from w w w . j ava2 s. c o m } }
From source file:controller.VisitasController.java
public void centerRow(Workbook wb, Row row) { CellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); for (int i = 0; i < row.getLastCellNum(); i++) { if (row.getCell(i) != null) { row.getCell(i).setCellStyle(style);//Set the sty;e }// w ww . j a v a 2 s. c om } }
From source file:coolmap.application.io.external.ImportCOntologyFromXLS.java
private void importSIF(Sheet sheet, COntology ontology) throws Exception { System.out.println("rowStart + columnStart:" + rowStart + " " + columnStart); int rowCounter = 0; Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (rowCounter < rowStart) { rowCounter++;/*ww w. j a v a 2 s . co m*/ continue; } if (columnStart + 2 > row.getLastCellNum()) { continue; } Cell parentCell = row.getCell(columnStart); Cell childCell = row.getCell(columnStart + 1); System.out.println(parentCell + " " + childCell); if (parentCell == null || childCell == null || parentCell.toString().trim().length() == 0 || childCell.toString().trim().length() == 0) { continue; } ontology.addRelationshipNoUpdateDepth(parentCell.toString().trim(), childCell.toString().trim()); rowCounter++; } }
From source file:coolmap.application.io.external.ImportCOntologyFromXLS.java
private void importGMT(Sheet sheet, COntology ontology) throws Exception { int rowCounter = 0; Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (rowCounter < rowStart) { rowCounter++;//from w w w . jav a2 s . com continue; } if (columnStart + 2 > row.getLastCellNum()) { continue; } Cell parentCell = row.getCell(columnStart); Cell descriptionCell = row.getCell(columnStart + 1); if (descriptionCell != null && descriptionCell.toString().trim().length() != 0 && parentCell == null && parentCell.toString().trim().length() != 0) { COntology.setAttribute(parentCell.toString().trim(), "gmt.Description", descriptionCell.toString().trim()); } if (parentCell == null || parentCell.toString().trim().length() == 0) { continue; } for (int j = columnStart + 2; j < row.getLastCellNum(); j++) { Cell childCell = row.getCell(j); if (childCell == null || childCell.toString().trim().length() == 0) { continue; } ontology.addRelationshipNoUpdateDepth(parentCell.toString().trim(), childCell.toString().trim()); } rowCounter++; } }
From source file:coolmap.application.io.external.ImportCOntologyFromXLS.java
@Override public void configure(File... file) { try {//from w w w.j ava 2s . c o m File inFile = file[0]; String fileNameString = inFile.getName().toLowerCase(); FileInputStream inStream = new FileInputStream(inFile); Workbook workbook = null; if (fileNameString.endsWith("xls")) { workbook = new HSSFWorkbook(inStream); } else if (fileNameString.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(inStream); } int sheetCount = workbook.getNumberOfSheets(); String[] sheetNames = new String[sheetCount]; for (int i = 0; i < sheetNames.length; i++) { String sheetName = workbook.getSheetAt(i).getSheetName(); sheetNames[i] = sheetName == null || sheetName.length() == 0 ? "Untitled" : sheetName; } DefaultTableModel tableModels[] = new DefaultTableModel[sheetCount]; Cell cell; Row row; ArrayList<ArrayList<ArrayList<Object>>> previewData = new ArrayList(); for (int si = 0; si < sheetCount; si++) { //The row iterator automatically skips the blank rows //so only need to figure out how many rows to skip; which is nice //columns, not the same though Sheet sheet = workbook.getSheetAt(si); Iterator<Row> rowIterator = sheet.rowIterator(); int ri = 0; ArrayList<ArrayList<Object>> data = new ArrayList<ArrayList<Object>>(); while (rowIterator.hasNext()) { row = rowIterator.next(); ArrayList<Object> rowData = new ArrayList<>(); for (int j = 0; j < row.getLastCellNum(); j++) { cell = row.getCell(j); try { if (cell == null) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { rowData.add(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { rowData.add(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { rowData.add(cell.getBooleanCellValue()); } else { rowData.add(cell.toString()); } } catch (Exception e) { // CMConsole.logError(" error parsing excel cell: " + cell + ", [" + ri + "," + j + "]"); rowData.add(null); } } data.add(rowData); ri++; if (ri == previewNum) { break; } } //end // System.out.println(data); // now the data is the data // ugh-> this is not a generic importer previewData.add(data); } //end of loop sheets ConfigPanel configPanel = new ConfigPanel(sheetNames, previewData); int returnVal = JOptionPane.showConfirmDialog(CoolMapMaster.getCMainFrame(), configPanel, "Import from Excel: " + inFile.getAbsolutePath(), JOptionPane.OK_CANCEL_OPTION, JOptionPane.PLAIN_MESSAGE, null); if (returnVal == JOptionPane.OK_OPTION) { proceed = true; inStream.close(); workbook = null; //set parameters inFile = file[0]; rowStart = configPanel.getRowStart(); columnStart = configPanel.getColumnStart(); sheetIndex = configPanel.getSheetIndex(); formatIndex = configPanel.getFormatIndex(); } else { //mark operation cancelled proceed = false; } } catch (Exception e) { } }
From source file:coolmap.application.io.external.ImportDataFromXLS.java
@Override public void importFromFile(File inFile) throws Exception { //Ignore the file, choose only a single file //I actually don't know the row count if (!proceed) { throw new Exception("Import from excel was cancelled"); } else {/*from ww w . ja v a2 s . c o m*/ try { String fileNameString = inFile.getName().toLowerCase(); FileInputStream inStream = new FileInputStream(inFile); Workbook workbook = null; if (fileNameString.endsWith("xls")) { workbook = new HSSFWorkbook(inStream); } else if (fileNameString.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(inStream); } Sheet sheet = workbook.getSheetAt(sheetIndex); int rowCounter = 0; //need to first copy the file over ArrayList<ArrayList<Object>> data = new ArrayList<ArrayList<Object>>(); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // if (rowCounter < rowStart) { // rowCounter++; // //import ontology rows // // continue; // // //skip first rows // } ArrayList<Object> rowData = new ArrayList<Object>(); for (int i = 0; i < row.getLastCellNum(); i++) { Cell cell = row.getCell(i); // System.out.print(cell + " "); // now add data try { if (cell == null) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { rowData.add(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { rowData.add(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { rowData.add(cell.getBooleanCellValue()); } else { rowData.add(cell.toString()); } } catch (Exception e) { // CMConsole.logError(" error parsing excel cell: " + cell + ", [" + row + "," + i + "]"); rowData.add(null); } } // System.out.println(""); data.add(rowData); } //now I have row data int rowCount = data.size() - rowStart - 1; int columnCount = data.get(0).size() - columnStart - 1; DoubleCMatrix matrix = new DoubleCMatrix(Tools.removeFileExtension(inFile.getName()), rowCount, columnCount); String[] rowNames = new String[rowCount]; String[] columnNames = new String[columnCount]; for (int i = rowStart; i < data.size(); i++) { ArrayList row = data.get(i); if (i == rowStart) { //first row contains names for (int j = columnStart + 1; j < row.size(); j++) { try { columnNames[j - columnStart - 1] = row.get(j).toString(); } catch (Exception e) { columnNames[j - columnStart - 1] = "Untitled " + Tools.randomID(); } } continue; } for (int j = columnStart; j < row.size(); j++) { Object cell = row.get(j); if (j == columnStart) { try { rowNames[i - rowStart - 1] = cell.toString(); } catch (Exception e) { rowNames[i - rowStart - 1] = "Untitled" + Tools.randomID(); } } else { //set values try { Object value = (Double) row.get(j); if (value == null) { matrix.setValue(i - rowStart - 1, j - columnStart - 1, null); } else if (value instanceof Double) { matrix.setValue(i - rowStart - 1, j - columnStart - 1, (Double) value); } else { matrix.setValue(i - rowStart - 1, j - columnStart - 1, Double.NaN); } } catch (Exception e) { matrix.setValue(i - rowStart - 1, j - columnStart - 1, null); } } } //end of iterating columns } //end of iterating rows // matrix.printMatrix(); // matrix.setRowLabels(rowNames); matrix.setColLabels(columnNames); CoolMapObject object = new CoolMapObject(); object.setName(Tools.removeFileExtension(inFile.getName())); object.addBaseCMatrix(matrix); ArrayList<VNode> nodes = new ArrayList<VNode>(); for (Object label : matrix.getRowLabelsAsList()) { nodes.add(new VNode(label.toString())); } object.insertRowNodes(nodes); nodes.clear(); for (Object label : matrix.getColLabelsAsList()) { nodes.add(new VNode(label.toString())); } object.insertColumnNodes(nodes); object.setAggregator(new DoubleDoubleMean()); object.setSnippetConverter(new DoubleSnippet1_3()); object.setViewRenderer(new NumberToColor(), true); object.getCoolMapView().addColumnMap(new ColumnLabels(object)); object.getCoolMapView().addColumnMap(new ColumnTree(object)); object.getCoolMapView().addRowMap(new RowLabels(object)); object.getCoolMapView().addRowMap(new RowTree(object)); importedCoolMaps.clear(); importedCoolMaps.add(object); //////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////// // //let's add COntologies if (columnStart > 0) { COntology columnOntology = new COntology( Tools.removeFileExtension(inFile.getName()) + " column ontology", null); ArrayList<Object> columnLabels = data.get(rowStart); //these are column labels for (int i = 0; i < rowStart; i++) { ArrayList ontologyColumn = data.get(i); for (int j = columnStart + 1; j < columnLabels.size(); j++) { Object parent = ontologyColumn.get(j); Object child = columnLabels.get(j); if (parent != null && child != null) { columnOntology.addRelationshipNoUpdateDepth(parent.toString(), child.toString()); } //Also need to create presets } } columnOntology.validate(); // COntologyUtils.printOntology(columnOntology); importedOntologies.add(columnOntology); // need to finish the preset } if (rowStart > 0) { COntology rowOntology = new COntology( Tools.removeFileExtension(inFile.getName()) + " row ontology", null); List rowLabels = Arrays.asList(rowNames); for (int j = 0; j < columnStart; j++) { for (int i = rowStart + 1; i < data.size(); i++) { Object parent = data.get(i).get(j); Object child = rowLabels.get(i - rowStart - 1); if (parent != null && child != null) { rowOntology.addRelationshipNoUpdateDepth(parent.toString(), child.toString()); } } } rowOntology.validate(); COntologyUtils.printOntology(rowOntology); importedOntologies.add(rowOntology); } // create row and column complex combinatorial ontology (intersections) } catch (Exception e) { // e.printStackTrace(); throw new Exception("File error"); } } }
From source file:coolmap.application.io.external.ImportDataFromXLS.java
@Override public void configure(File... file) { //need to popup a secondary dialog; this must be done differently try {/*from ww w . j av a 2 s . c o m*/ File inFile = file[0]; String fileNameString = inFile.getName().toLowerCase(); FileInputStream inStream = new FileInputStream(inFile); Workbook workbook = null; if (fileNameString.endsWith("xls")) { workbook = new HSSFWorkbook(inStream); } else if (fileNameString.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(inStream); } int sheetCount = workbook.getNumberOfSheets(); String[] sheetNames = new String[sheetCount]; for (int i = 0; i < sheetNames.length; i++) { String sheetName = workbook.getSheetAt(i).getSheetName(); sheetNames[i] = sheetName == null || sheetName.length() == 0 ? "Untitled" : sheetName; } //also need to get the top 100 rows + all columns DefaultTableModel tableModels[] = new DefaultTableModel[sheetCount]; Cell cell; Row row; ArrayList<ArrayList<ArrayList<Object>>> previewData = new ArrayList(); for (int si = 0; si < sheetCount; si++) { //The row iterator automatically skips the blank rows //so only need to figure out how many rows to skip; which is nice //columns, not the same though Sheet sheet = workbook.getSheetAt(si); Iterator<Row> rowIterator = sheet.rowIterator(); int ri = 0; ArrayList<ArrayList<Object>> data = new ArrayList<ArrayList<Object>>(); while (rowIterator.hasNext()) { row = rowIterator.next(); ArrayList<Object> rowData = new ArrayList<>(); for (int j = 0; j < row.getLastCellNum(); j++) { cell = row.getCell(j); try { if (cell == null) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { rowData.add(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { rowData.add(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { rowData.add(cell.getBooleanCellValue()); } else { rowData.add(cell.toString()); } } catch (Exception e) { // CMConsole.logError(" error parsing excel cell: " + cell + ", [" + ri + "," + j + "]"); rowData.add(null); } } data.add(rowData); ri++; if (ri == previewNum) { break; } } //end // System.out.println(data); // now the data is the data // ugh-> this is not a generic importer previewData.add(data); } //end of iterating all sheets ConfigPanel configPanel = new ConfigPanel(sheetNames, previewData); //int returnVal = JOptionPane.showMessageDialog(CoolMapMaster.getCMainFrame(), configPanel); int returnVal = JOptionPane.showConfirmDialog(CoolMapMaster.getCMainFrame(), configPanel, "Import from Excel: " + inFile.getAbsolutePath(), JOptionPane.OK_CANCEL_OPTION, JOptionPane.PLAIN_MESSAGE, null); if (returnVal == JOptionPane.OK_OPTION) { proceed = true; inStream.close(); workbook = null; //set parameters inFile = file[0]; importOntology = configPanel.getImportOntology(); rowStart = configPanel.getRowStart(); columnStart = configPanel.getColumnStart(); sheetIndex = configPanel.getSheetIndex(); } else { //mark operation cancelled proceed = false; } } catch (Exception e) { CMConsole.logError(" failed to import numeric matrix data from: " + file); e.printStackTrace(); } }
From source file:courtscheduler.persistence.CourtScheduleIO.java
License:Apache License
public List<Team> readXlsx(String filename, CourtScheduleInfo info) throws Exception { File file = new File(filename); if (!file.exists()) { return null; }//from w w w . j av a 2 s. c om FileInputStream fis = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(fis); // Get worksheet by index XSSFSheet sh = wb.getSheetAt(0); rowNumber = 2; Integer rowCount = sh.getLastRowNum(); if (Main.LOG_LEVEL >= 1) { System.out.println(new java.util.Date() + "[INFO] Worksheet Name: " + sh.getSheetName()); System.out.println(new java.util.Date() + "[INFO] Worksheet has " + (rowCount - 1) + " lines of data."); } while (rowNumber <= rowCount) { Row currentRow = sh.getRow(rowNumber); if (currentRow != null && currentRow.getLastCellNum() > 0) { Team nextTeam = processRow(currentRow, info); if (nextTeam != null && nextTeam.getTeamId() != null) { teamList.add(nextTeam); } else break; } rowNumber += 1; } if (Main.LOG_LEVEL >= 1) { /*for (int x = 0; x < teamList.size(); x++) { System.out.println(teamList.get(x)); }*/ System.out.println(new java.util.Date() + " [INFO] Input parsed. Constructing possible matches..."); } return teamList; }