List of usage examples for org.apache.poi.ss.usermodel Row getCell
Cell getCell(int cellnum);
From source file:ch.oakmountain.tpa.parser.TpaParser.java
License:Apache License
private Map<ColumnIdentifier, String> getWorksheetPointerStringMap(Map<ColumnIdentifier, Integer> cols, Row row) { Map<ColumnIdentifier, String> outputItem = new HashMap<ColumnIdentifier, String>(cols.size()); for (ColumnIdentifier col : cols.keySet()) { int colIndex = cols.get(col); Cell cell;/* w ww . j av a 2 s .c o m*/ if (colIndex == lastNonEmptyColNb) { colIndex = row.getLastCellNum(); cell = row.getCell(colIndex); for (; colIndex > 0; colIndex--) { cell = row.getCell(colIndex); if (StringUtils.isNotBlank(formatter.formatCellValue(cell))) { String test = mapping.get(formatter.formatCellValue(cell)); if (test == null) { LOGGER.error("Could not find mapping for " + formatter.formatCellValue(cell)); } break; } } } else { cell = row.getCell(colIndex); } outputItem.put(col, getCellValueString(cell)); } return outputItem; }
From source file:ch.oakmountain.tpa.parser.TpaParser.java
License:Apache License
private Hashtable<String, String> readNodeAbbrMapping(Workbook wb) { Sheet sheet = wb/*from w w w .j av a 2 s. c o m*/ .getSheet(getPropertyValue(tpaProps.TRAINPATHSECTION_NODES_TO_SYSTEM_NODES_MAPPING_WS_NAME)); Hashtable<String, String> hashtable = new Hashtable<String, String>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if (row == null) { continue; } Cell cellAbbr = row.getCell(0); if (cellAbbr == null || cellAbbr.getCellType() == HSSFCell.CELL_TYPE_BLANK) { continue; } Cell cellUnique = row.getCell(1); if (cellUnique == null || cellUnique.getCellType() == HSSFCell.CELL_TYPE_BLANK) { continue; } hashtable.put(formatter.formatCellValue(cellAbbr), formatter.formatCellValue(cellUnique)); } return hashtable; }
From source file:ch.oakmountain.tpa.parser.TpaParser.java
License:Apache License
private void allocate(SimpleTrainPathApplication simpleTrainPathApplication, SolutionCandidate allocation) { String colLayoutString = getPropertyValue(tpaProps.TRAINPATHS_COL_LAYOUT); List<ColumnIdentifier> cols = new ArrayList<ColumnIdentifier>(trainPathLayout.values().length); for (trainPathLayout l : trainPathLayout.values()) { cols.add(l);/* w ww .ja v a 2 s . c o m*/ } Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols); int rowsFrom = Integer.parseInt(getPropertyValue(tpaProps.TRAINPATHS_WS_HEADER_ROWS)); for (TrainPathSlot trainPathSlot : allocation.getPath()) { Sheet sheet = wb.getSheet(trainPathSlot.getPeriodicalTrainPathSlot().getTrainPathSectionName()); for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); String slotId = getCellValueString(row.getCell(colLayoutMapping.get(trainPathLayout.ID))); if (trainPathSlot.getPeriodicalTrainPathSlot().getName().equals(slotId)) { trainPathLayout day = trainPathLayout .getWeekDayTrainPathLayout(trainPathSlot.getStartTime().getDayOfWeek()); String slotName = trainPathSlot.getName(); int colNum = colLayoutMapping.get(day); Cell cell = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK); if (StringUtils.isNotBlank(getCellValueString(cell))) { throw new IllegalStateException("Cell must be empty; trying to allocate " + slotName + " on " + day + " to request " + simpleTrainPathApplication.getName() + "; cell value is " + getCellValueString(cell)); } cell.setCellValue(simpleTrainPathApplication.getName()); // TODO show periodicity here when implementing "non-flat" allocation LOGGER.debug("Allocating " + slotName + " on " + day + " by request " + simpleTrainPathApplication.getName() + " of weight " + allocation.getWeight()); } } } }
From source file:ch.oakmountain.tpa.parser.TpaParser.java
License:Apache License
/** * Mark the request as allocated on all days of the request. * * @param request/* w w w.ja v a 2s . c om*/ */ private void markRequestAllocated(TrainPathApplication request) { String colLayoutString = getPropertyValue(tpaProps.REQUESTS_COL_LAYOUT); List<ColumnIdentifier> cols = new ArrayList<ColumnIdentifier>(requestsLayout.values().length); for (requestsLayout l : requestsLayout.values()) { cols.add(l); } Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols); int rowsFrom = Integer.parseInt(getPropertyValue(tpaProps.REQUESTS_WS_HEADER_ROWS)); Sheet sheet = wb.getSheet(getPropertyValue(tpaProps.REQUESTS_WS_NAME)); for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if (row != null && row.getCell(colLayoutMapping.get(requestsLayout.ID)) != null) { String allocatedRequest = getCellValueString(row.getCell(colLayoutMapping.get(requestsLayout.ID))); if (allocatedRequest.equals(request.getName())) { for (Integer day : request.getPeriodicity().getWeekDays()) { requestsLayout requestDay = requestsLayout.getWeekDayTrainPathLayout(day); int colNum = colLayoutMapping.get(requestDay); Cell cell = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK); if (!getCellValueString(cell) .equals(getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER))) { throw new IllegalStateException("Application " + request.getName() + " on day " + day + " must have requested flag \"" + getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER) + "\" since we're trying to mark it satisfied; found " + getCellValueString(cell)); } cell.setCellValue(getPropertyValue(tpaProps.REQUESTS_ALLOCATED_DAY_MARKER)); LOGGER.debug("Set allocated flag for " + request.getName() + " on day " + day); } } } } }
From source file:ch.ReadData.java
public void read_data_only(String path) { try (FileInputStream file = new FileInputStream(new File(path))) { update_halls = con.prepareStatement(JDBCConnection.Update_halls_colleges); PreparedStatement prepstm = con.prepareStatement(JDBCConnection.Insert_Into_temptable); PreparedStatement student_insert = con.prepareStatement(JDBCConnection.Insert_Into_studentcourse); con.setAutoCommit(false);/*from w w w.jav a 2 s . c o m*/ Workbook workbook = WorkbookFactory.create(file); Sheet sheet = workbook.getSheetAt(0); String college_name, student_name, course_name, lecturer; int student_id, course_id, course_sec; for (org.apache.poi.ss.usermodel.Row row : sheet) { course_name = row.getCell(6).getStringCellValue(); if (course_name.contains("")) { student_id = (int) row.getCell(0).getNumericCellValue(); student_name = row.getCell(1).getStringCellValue(); college_name = row.getCell(2).getStringCellValue(); course_id = (int) row.getCell(3).getNumericCellValue(); course_sec = (int) row.getCell(5).getNumericCellValue(); course_name = row.getCell(4).getStringCellValue(); lecturer = row.getCell(7).getStringCellValue(); prepstm.setInt(1, student_id); prepstm.setString(2, student_name); prepstm.setString(3, college_name); prepstm.setInt(4, course_id); prepstm.setInt(5, course_sec); prepstm.setString(6, course_name); prepstm.setString(7, lecturer); student_insert.setInt(1, student_id); student_insert.setInt(2, course_id); student_insert.setInt(3, course_sec); student_insert.addBatch(); prepstm.addBatch(); } } prepstm.executeBatch(); student_insert.executeBatch(); PreparedStatement prep = con.prepareStatement(JDBCConnection.Insert_new_college); Statement stm = con.createStatement(); ResultSet r = stm.executeQuery(JDBCConnection.Select_collegs); int college_id = 1; PreparedStatement update_college_name = con.prepareStatement(JDBCConnection.Update_college_name); while (r.next()) { prep.setInt(1, college_id * 100); prep.setString(2, r.getString("college_name")); update_college_name.setInt(1, college_id * 100); update_college_name.setString(2, r.getString("college_name")); update_college_name.addBatch(); update_halls.setInt(1, college_id * 100); update_halls.setString(2, r.getString("college_name")); update_halls.addBatch(); prep.addBatch(); college_id++; } prep.executeBatch(); update_college_name.executeBatch(); ResultSet courses_result = stm.executeQuery(JDBCConnection.Select_courses); PreparedStatement add_courses = con.prepareStatement(JDBCConnection.Insert_courses); while (courses_result.next()) { add_courses.setInt(1, courses_result.getInt("course_id")); add_courses.setString(2, courses_result.getString("course_name")); add_courses.setInt(3, courses_result.getInt("total_students")); add_courses.addBatch(); } add_courses.executeBatch(); ResultSet lecturer_result = stm.executeQuery(JDBCConnection.Select_luecturers); PreparedStatement add_lecturer = con.prepareStatement(JDBCConnection.Insert_Lecturers); int lecturer_id = 1000; PreparedStatement update_lecturers = con.prepareStatement(JDBCConnection.Update_lecturer_name); while (lecturer_result.next()) { add_lecturer.setInt(1, lecturer_id); add_lecturer.setString(2, lecturer_result.getString("lecturer")); add_lecturer.setInt(3, lecturer_result.getInt("college_name")); add_lecturer.addBatch(); update_lecturers.setInt(1, lecturer_id); update_lecturers.setString(2, lecturer_result.getString("lecturer")); update_lecturers.addBatch(); lecturer_id += 1; } add_lecturer.executeBatch(); update_lecturers.executeBatch(); ResultSet courses_sections_set = stm.executeQuery(JDBCConnection.Select_Courses_Sections); PreparedStatement add_courses_sections = con.prepareStatement(JDBCConnection.Insert_Course_Section); while (courses_sections_set.next()) { add_courses_sections.setInt(1, courses_sections_set.getInt("course_id")); add_courses_sections.setInt(2, courses_sections_set.getInt("course_sec")); add_courses_sections.setInt(3, courses_sections_set.getInt("lecturer")); add_courses_sections.setInt(4, courses_sections_set.getInt("number_of_students")); add_courses_sections.setInt(5, courses_sections_set.getInt("college_name")); add_courses_sections.addBatch(); } add_courses_sections.executeBatch(); PreparedStatement update_students = con.prepareStatement(JDBCConnection.Insert_Students); ResultSet get_students = stm.executeQuery(JDBCConnection.Select_Students); while (get_students.next()) { update_students.setInt(1, get_students.getInt("student_id")); update_students.setString(2, get_students.getString("student_name")); update_students.setInt(3, get_students.getInt("college_name")); update_students.addBatch(); } update_students.executeBatch(); Statement drop = con.createStatement(); drop.executeUpdate(JDBCConnection.Drop_temptable); con.commit(); } catch (IOException | SQLException | org.apache.poi.openxml4j.exceptions.InvalidFormatException | EncryptedDocumentException ex) { Logger.getLogger(ReadData.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:ch.ReadData.java
public void readHalls(String path) { try (FileInputStream file = new FileInputStream(new File(path))) { PreparedStatement prepstm = con.prepareStatement(JDBCConnection.insert_halls); con.setAutoCommit(false);//w w w . j ava 2 s.c om //Get the workbook instance for XLS file Workbook workbook = WorkbookFactory.create(file); Sheet sheet = workbook.getSheetAt(0); String college_name; int hall_number, capacity; for (org.apache.poi.ss.usermodel.Row row : sheet) { hall_number = (int) row.getCell(0).getNumericCellValue(); college_name = row.getCell(1).getStringCellValue(); capacity = (int) row.getCell(2).getNumericCellValue(); prepstm.setInt(1, hall_number); prepstm.setString(2, college_name); prepstm.setInt(3, capacity); prepstm.addBatch(); } prepstm.executeBatch(); update_halls.executeBatch(); //con.prepareStatement(colleges_update_query).executeUpdate(); con.commit(); } catch (IOException | SQLException | org.apache.poi.openxml4j.exceptions.InvalidFormatException | EncryptedDocumentException ex) { Logger.getLogger(ReadData.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:ch.swissbytes.Service.business.Spreadsheet.ToCSV.java
License:Apache License
/** * Called to convert a row of cells into a line of data that can later be * output to the CSV file.//from w w w . j a v a 2 s . c o m * * @param row An instance of either the HSSFRow or XSSFRow classes that * encapsulates information about a row of cells recovered from * an Excel workbook. */ private void rowToCSV(Row row) { Cell cell = null; int lastCellNum = 0; ArrayList<String> csvLine = new ArrayList<String>(); // Check to ensure that a row was recovered from the sheet as it is // possible that one or more rows between other populated rows could be // missing - blank. If the row does contain cells then... if (row != null) { // Get the index for the right most cell on the row and then // step along the row from left to right recovering the contents // of each cell, converting that into a formatted String and // then storing the String into the csvLine ArrayList. lastCellNum = row.getLastCellNum(); for (int i = 0; i <= lastCellNum; i++) { cell = row.getCell(i); if (cell == null) { csvLine.add(""); } else { if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) { csvLine.add(this.formatter.formatCellValue(cell)); } else { csvLine.add(this.formatter.formatCellValue(cell, this.evaluator)); } } } // Make a note of the index number of the right most cell. This value // will later be used to ensure that the matrix of data in the CSV file // is square. if (lastCellNum > this.maxRowWidth) { this.maxRowWidth = lastCellNum; } } this.csvData.add(csvLine); }
From source file:cherry.goods.excel.ExcelWriter.java
License:Apache License
/** * ?<br />/*w w w.j av a2s . co m*/ * ???1(1) * * @param offsetCols ????? (???) * @param record 1(1) */ public void write(int offsetCols, String... record) { Row row = currentSheet.getRow(rownum); if (row == null) { row = currentSheet.createRow(rownum); } rownum += 1; for (int i = 0; i < record.length; i++) { int colnum = i + offsetCols; Cell cell = row.getCell(colnum); if (record[i] == null) { if (cell != null) { row.removeCell(cell); } } else { if (cell == null) { cell = row.createCell(colnum); } cell.setCellValue(record[i]); } } }
From source file:cherry.goods.telno.SoumuExcelParser.java
License:Apache License
/** * ? () ??/* www .j av a 2 s . c om*/ * * @param row * @param col ? * @return ? () */ private String getCellValue(Row row, int col) { Cell cell = row.getCell(col); if (cell == null) { return null; } return cell.getStringCellValue(); }
From source file:cherry.parser.worksheet.RowBasedParser.java
License:Apache License
private List<TypeDef> parseSheet(Sheet sheet) { boolean configured = false; int coldefFirstCellNum = -1; Map<Integer, String> coldef = new TreeMap<Integer, String>(); Map<String, TypeDef> map = new LinkedHashMap<String, TypeDef>(); TypeDef typeDef = null;// www.j a v a2s . c o m for (Row row : sheet) { int firstCellNum = row.getFirstCellNum(); if (firstCellNum < 0) { continue; } if (!configured) { String directive = getCellValueAsString(row.getCell(firstCellNum)); if ("##COLDEF".equals(directive)) { for (Cell cell : row) { if (cell.getColumnIndex() == firstCellNum) { continue; } if (cell.getCellType() != Cell.CELL_TYPE_STRING) { continue; } coldef.put(cell.getColumnIndex(), getCellValueAsString(cell)); } coldefFirstCellNum = firstCellNum; configured = true; } else { // IGNORE UNKNOWN DIRECTIVES } } else { ItemDef item = null; for (Cell cell : row) { if (cell.getColumnIndex() == coldefFirstCellNum) { item = new ItemDef(); continue; } if (item == null) { continue; } String key = coldef.get(cell.getColumnIndex()); if (key == null) { continue; } String value = getCellValueAsString(cell); if (value != null) { item.put(key, value); } } if (item != null) { String fqcn = item.get(TypeDef.FULLY_QUALIFIED_CLASS_NAME); if (fqcn != null) { TypeDef td = map.get(fqcn); if (td != null) { typeDef = td; } else { typeDef = new TypeDef(); typeDef.setSheetName(sheet.getSheetName()); map.put(fqcn, typeDef); } } if (typeDef != null) { typeDef.getItemDef().add(item); } } } } return new ArrayList<TypeDef>(map.values()); }