List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum
short getLastCellNum();
From source file:users.registration.CreateBtechStudentUsers.java
private boolean isEmptyRow(Row row) { boolean isRowEmpty = true; if (row == null) { return true; }//from www . ja va2 s . c om for (int j = 0; j < row.getLastCellNum(); j++) { if (row.getCell(j) == null) { continue; } if (!row.getCell(j).toString().trim().equals("")) { isRowEmpty = false; break; } } return isRowEmpty; }
From source file:util.excel.SheetOfStudentInfo.java
License:Open Source License
private static int countNonBlankCells(Row row) { int count = 0; int cellCount = row.getLastCellNum(); for (int i = 0; i < cellCount; i++) { Cell cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL); if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING && !cell.getStringCellValue().trim().isEmpty()) { count++;/*from w w w . j a v a 2 s. com*/ } } return count; }
From source file:util.POIUtils.java
License:LGPL
/** * Returns column values in specified row. Indexes are 0-based. * * @param sheet Excel spreadsheet//from www . j a v a2 s .c o m * @param rowNum row number * @return list of cell values in row **/ public static List<String> getStringCellValues(Sheet sheet, int rowNum) { Row row = sheet.getRow(rowNum); if (row != null) { return getStringCellValues(sheet, rowNum, 0, (int) row.getLastCellNum() - 1); } return new ArrayList<String>(); }
From source file:util.ToCSV.java
License:Apache License
private void rowToCSV(Row row) { Cell cell = null;/*from w ww . j a va 2 s . c o m*/ int lastCellNum = 0; ArrayList<String> csvLine = new ArrayList<String>(); if (row != null) { 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)); } } } if (lastCellNum > this.maxRowWidth) { this.maxRowWidth = lastCellNum; } } this.csvData.add(csvLine); }
From source file:utilities.TableReader.java
private static int[] getExcelColumns(XSSFSheet sheet, ArrayList<TableMetaData> meta) throws UploadException { int[] columns = Tools.defaultIntArray(meta.size() + 1); Row row = sheet.getRow(0); for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { Cell cell = row.getCell(i);//from w ww. ja v a2 s .c o m String colName = cell.getRichStringCellValue().toString(); checkColumn(columns, colName, i, meta); } if (Tools.contains(columns, -1)) { throw new UploadException(UploadException.MISSING_COLUMNS); } return columns; }
From source file:utilities.XLSCustomReportsManager.java
License:Open Source License
public ReportConfig getOversightDefinition(Connection sd, int oId, String type, InputStream inputStream, ResourceBundle localisation, boolean isSecurityManager) throws Exception { ReportConfig config = new ReportConfig(); config.columns = new ArrayList<TableColumn>(); Sheet sheet = null;/*from www .j a va 2s. c o m*/ Sheet settingsSheet = null; Row row = null; int lastRowNum = 0; HashMap<String, Integer> header = null; if (type != null && type.equals("xls")) { wb = new HSSFWorkbook(inputStream); } else { wb = new XSSFWorkbook(inputStream); } /* * Get the task sheet settings */ settingsSheet = wb.getSheet("settings"); if (settingsSheet != null && settingsSheet.getPhysicalNumberOfRows() > 0) { int lastSettingsRow = settingsSheet.getLastRowNum(); for (int j = 0; j <= lastSettingsRow; j++) { row = settingsSheet.getRow(j); if (row != null) { int lastCellNum = row.getLastCellNum(); if (lastCellNum > 0) { Cell c = row.getCell(0); String k = c.getStringCellValue(); if (k != null && k.trim().toLowerCase().equals("time zone:")) { c = row.getCell(1); break; } } } } } sheet = wb.getSheet("definition"); if (sheet == null) { if (wb.getNumberOfSheets() == 1) { sheet = wb.getSheetAt(0); } else { throw new Exception("A worksheet called 'definition' not found"); } } if (sheet.getPhysicalNumberOfRows() > 0) { lastRowNum = sheet.getLastRowNum(); boolean needHeader = true; boolean foundData = false; TableColumn currentCol = null; boolean processingConditions = false; PreparedStatement pstmtGetRoleId = null; String sqlGetRoleId = "select id from role where o_id = ? and name = ?"; // Get ready to process roles pstmtGetRoleId = sd.prepareStatement(sqlGetRoleId); pstmtGetRoleId.setInt(1, oId); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); if (row != null) { int lastCellNum = row.getLastCellNum(); if (needHeader) { header = XLSUtilities.getHeader(row, localisation, j, "definition"); needHeader = false; } else { String rowType = XLSUtilities.getColumn(row, "row type", header, lastCellNum, null); if (rowType != null && rowType.trim().length() > 0) { foundData = true; rowType = rowType.trim().toLowerCase(); // Close of any condition type calculations if (processingConditions && !rowType.equals("condition")) { processingConditions = false; currentCol.calculation.add("END"); } // Process the row if (rowType.equals("column")) { currentCol = new TableColumn(); config.columns.add(currentCol); // Get data type String dataType = XLSUtilities.getColumn(row, "data type", header, lastCellNum, null); if (dataType != null) { if (!dataType.equals("text") && !dataType.equals("date") && !dataType.equals("calculate") && !dataType.equals("decimal") && !dataType.equals("integer") && !dataType.equals("select_one")) { throw new Exception(localisation.getString("mf_idt") + ": " + dataType + " " + localisation.getString("mf_or") + ": " + (j + 1)); } currentCol.type = dataType; } else { throw new Exception(localisation.getString("mf_mdt") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } // Get column name String colName = XLSUtilities.getColumn(row, "name", header, lastCellNum, null); if (colName != null) { colName = colName.trim().toLowerCase(); String modColName = colName.replaceAll("[^a-z0-9_]", ""); modColName = GeneralUtilityMethods.cleanName(modColName, true, true, true); if (colName.length() != modColName.length()) { throw new Exception(localisation.getString("mf_in") + ": " + colName + " " + localisation.getString("mf_or") + ": " + (j + 1)); } else if (colName.length() > 60) { throw new Exception(localisation.getString("mf_ntl") + ": " + colName + " " + localisation.getString("mf_or") + ": " + (j + 1)); } currentCol.column_name = colName; } else { throw new Exception(localisation.getString("mf_mn") + localisation.getString("mf_or") + ": " + (j + 1)); } // Get display name String dispName = XLSUtilities.getColumn(row, "display name", header, lastCellNum, null); if (dispName != null) { currentCol.displayName = dispName; } else { throw new Exception(localisation.getString("mf_mdn") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } // Get hide state String hide = XLSUtilities.getColumn(row, "hide", header, lastCellNum, null); currentCol.hide = false; if (hide != null) { hide = hide.toLowerCase().trim(); if (hide.equals("yes") || hide.equals("true")) { currentCol.hide = true; } } // Get readonly state String readonly = XLSUtilities.getColumn(row, "readonly", header, lastCellNum, null); currentCol.readonly = false; if (readonly != null) { readonly = readonly.toLowerCase().trim(); if (readonly.equals("yes") || readonly.equals("true")) { currentCol.readonly = true; } } // Get filter state String filter = XLSUtilities.getColumn(row, "filter", header, lastCellNum, null); currentCol.filter = false; if (filter != null) { filter = filter.toLowerCase().trim(); if (filter.equals("yes") || filter.equals("true")) { currentCol.filter = true; } } // Get parameters try { currentCol.parameters = getParamObj( XLSUtilities.getColumn(row, "parameters", header, lastCellNum, null)); } catch (Exception e) { // Ignore errors if parameters are not found } // Get calculation state if (currentCol.type.equals("calculate")) { String calculation = XLSUtilities.getColumn(row, "calculation", header, lastCellNum, null); if (calculation != null && calculation.length() > 0) { calculation = calculation.trim(); if (calculation.equals("condition")) { // Calculation set by condition rows currentCol.isCondition = true; } else if (calculation.length() > 0) { currentCol.calculation = new SqlFrag(); currentCol.calculation.addSqlFragment(calculation, true, localisation); } } else { throw new Exception(localisation.getString("mf_mc") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } } else if (rowType.equals("choice")) { if (currentCol != null && currentCol.type.equals("select_one")) { String name = XLSUtilities.getColumn(row, "name", header, lastCellNum, null); String dispName = XLSUtilities.getColumn(row, "display name", header, lastCellNum, null); if (name == null) { name = XLSUtilities.getColumn(row, "value", header, lastCellNum, null); // Legacy implementation dispName = name; } if (name != null && dispName != null) { if (currentCol.choices == null) { currentCol.choices = new ArrayList<KeyValue>(); currentCol.choices.add(new KeyValue("", "")); // Add the not selected choice automatically as this has to be the default } currentCol.choices.add(new KeyValue(name, dispName)); currentCol.filter = true; // Add conditional color String appearance = XLSUtilities.getColumn(row, "appearance", header, lastCellNum, null); if (appearance != null) { if (currentCol.markup == null) { currentCol.markup = new ArrayList<TableColumnMarkup>(); } currentCol.markup .add(new TableColumnMarkup(name, getMarkup(appearance))); } } else { throw new Exception(localisation.getString("mf_mv") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } else { throw new Exception(localisation.getString("mf_un_c") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } else if (rowType.equals("user_role")) { if (currentCol != null && currentCol.type.equals("select_one")) { String role = XLSUtilities.getColumn(row, "name", header, lastCellNum, null); if (role != null) { if (currentCol.choices == null) { currentCol.choices = new ArrayList<KeyValue>(); currentCol.choices.add(new KeyValue("", "")); // Add the not selected choice automatically as this has to be the default } currentCol.choices.add(new KeyValue(role, role, true)); currentCol.filter = true; } else { throw new Exception(localisation.getString("mf_mv_o") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } else { throw new Exception(localisation.getString("mf_un_u") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } else if (rowType.equals("action")) { if (currentCol != null) { String action = XLSUtilities.getColumn(row, "action", header, lastCellNum, null); if (action != null) { if (action.equals("respond")) { if (currentCol.actions == null) { currentCol.actions = new ArrayList<Action>(); } } else { throw new Exception(localisation.getString("mf_ia") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } // Get the action details Action todo = new Action(action); todo.notify_type = XLSUtilities.getColumn(row, "notify type", header, lastCellNum, null); todo.notify_person = XLSUtilities.getColumn(row, "notify person", header, lastCellNum, null); if (isSecurityManager) { String roles = XLSUtilities.getColumn(row, "roles", header, lastCellNum, null); if (roles != null) { todo.roles = new ArrayList<Role>(); String rArray[] = roles.split(","); for (int i = 0; i < rArray.length; i++) { pstmtGetRoleId.setString(2, rArray[i].trim()); ResultSet rs = pstmtGetRoleId.executeQuery(); if (rs.next()) { todo.roles.add(new Role(rs.getInt(1), rArray[i].trim())); } } } } // Checks for a valid notification if (action.equals("respond") && todo.notify_type == null) { throw new Exception(localisation.getString("mf_mnt") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } currentCol.actions.add(todo); } else { throw new Exception(localisation.getString("mf_mv_a") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } else { throw new Exception(localisation.getString("mf_un_a") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } else if (rowType.equals("condition")) { if (currentCol != null && currentCol.type.equals("calculate")) { processingConditions = true; String condition = XLSUtilities.getColumn(row, "condition", header, lastCellNum, null); String value = XLSUtilities.getColumn(row, "value", header, lastCellNum, null); if (condition == null) { throw new Exception("Missing \"condition\" on row: " + (j + 1)); } else { if (currentCol.calculation == null) { currentCol.calculation = new SqlFrag(); currentCol.calculation.add("CASE"); } if (condition.toLowerCase().trim().equals("all")) { currentCol.calculation.add("ELSE"); currentCol.calculation.addSqlFragment(value, false, localisation); //currentCol.calculation.addText(value); } else { currentCol.calculation.add("WHEN"); currentCol.calculation.addSqlFragment(condition, true, localisation); currentCol.calculation.add("THEN"); //currentCol.calculation.addText(value); currentCol.calculation.addSqlFragment(value, false, localisation); } } // Add conditional markup and save value for use in export of form String appearance = XLSUtilities.getColumn(row, "appearance", header, lastCellNum, null); if (currentCol.markup == null) { currentCol.markup = new ArrayList<TableColumnMarkup>(); } currentCol.markup.add(new TableColumnMarkup(value, getMarkup(appearance))); } else { throw new Exception(localisation.getString("mf_uc") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } else if (rowType.equals("settings")) { config.settings = getParamObj( XLSUtilities.getColumn(row, "parameters", header, lastCellNum, null)); } else { throw new Exception(localisation.getString("mf_ur") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } } } } if (!foundData) { throw new Exception(localisation.getString("mf_nd")); } // Close of any condition type calculations if (processingConditions) { processingConditions = false; currentCol.calculation.add("END"); } } // Final Validatation for (TableColumn col : config.columns) { // 1. Check for condition calculations without any corresponding contion entries if (col.isCondition && col.calculation == null) { throw new Exception(localisation.getString("mf_ncr") + ": " + col.column_name); } } return config; }
From source file:utilities.XLSCustomReportsManager.java
License:Open Source License
public LQAS getLQASReport(Connection sd, int oId, String type, InputStream inputStream, ResourceBundle localisation) throws Exception { LQAS lqas = null;/*from w w w . j a va 2 s . c om*/ String lot = null; ArrayList<TableColumn> defn = new ArrayList<TableColumn>(); Sheet sheet = null; Sheet settingsSheet = null; Row row = null; int lastRowNum = 0; HashMap<String, Integer> header = null; System.out.println("Getting LQAS report"); if (type != null && type.equals("xls")) { wb = new HSSFWorkbook(inputStream); } else { wb = new XSSFWorkbook(inputStream); } /* * Get the settings */ settingsSheet = wb.getSheet("settings"); if (settingsSheet != null && settingsSheet.getPhysicalNumberOfRows() > 0) { int lastSettingsRow = settingsSheet.getLastRowNum(); for (int j = 0; j <= lastSettingsRow; j++) { row = settingsSheet.getRow(j); if (row != null) { int lastCellNum = row.getLastCellNum(); if (lastCellNum > 0) { Cell c = row.getCell(0); String k = c.getStringCellValue(); if (k != null && k.trim().toLowerCase().equals("lot")) { c = row.getCell(1); lot = c.getStringCellValue(); } } } } } if (lot != null) { lqas = new LQAS(lot); } else { throw new Exception("Lot value not specified in settings"); } sheet = wb.getSheet("definition"); if (sheet.getPhysicalNumberOfRows() > 0) { lastRowNum = sheet.getLastRowNum(); boolean processingConditions = false; boolean needHeader = true; LQASGroup currentGroup = null; LQASdataItem currentDataItem = null; LQASItem currentItem = null; TableColumn tc = new TableColumn(); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); if (row != null) { int lastCellNum = row.getLastCellNum(); if (needHeader) { header = XLSUtilities.getHeader(row, localisation, j, "definition"); needHeader = false; } else { String rowType = XLSUtilities.getColumn(row, "row type", header, lastCellNum, null); if (rowType != null) { rowType = rowType.trim().toLowerCase(); String name = XLSUtilities.getColumn(row, "name", header, lastCellNum, null); // Close of any condition type calculations if (processingConditions && !rowType.equals("condition")) { processingConditions = false; currentDataItem.select.add("END"); } // Process the row if (rowType.equals("group")) { String groupName = XLSUtilities.getColumn(row, "display name", header, lastCellNum, null); currentGroup = new LQASGroup(groupName); lqas.groups.add(currentGroup); } else if (rowType.equals("data")) { SqlFrag select = null; String[] sources = null; // Get data type String dataType = XLSUtilities.getColumn(row, "data type", header, lastCellNum, null); if (dataType != null) { if (!dataType.equals("text") && !dataType.equals("date") && !dataType.equals("calculate") && !dataType.equals("decimal") && !dataType.equals("integer") && !dataType.equals("select_one")) { throw new Exception(localisation.getString("mf_idt") + ": " + dataType + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } else { throw new Exception(localisation.getString("mf_mdt") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } // Get calculation state String calculation = XLSUtilities.getColumn(row, "calculation", header, lastCellNum, null); if (calculation != null && calculation.length() > 0) { calculation = calculation.trim(); if (calculation.equals("condition")) { // Calculation set by condition rows } else if (calculation.length() > 0) { select = new SqlFrag(); select.addSqlFragment(calculation, false, localisation); } } else { throw new Exception(localisation.getString("mf_mc") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } currentDataItem = new LQASdataItem(name, select, dataType.equals("text")); lqas.dataItems.add(currentDataItem); } else if (rowType.equals("item")) { // Get data type String dataType = XLSUtilities.getColumn(row, "data type", header, lastCellNum, null); if (dataType != null) { if (!dataType.equals("text") && !dataType.equals("date") && !dataType.equals("calculate") && !dataType.equals("decimal") && !dataType.equals("integer") && !dataType.equals("select_one")) { throw new Exception(localisation.getString("mf_idt") + ": " + dataType + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } else { throw new Exception(localisation.getString("mf_mdt") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } String targetResponseText = XLSUtilities.getColumn(row, "target response text", header, lastCellNum, null); String displayName = XLSUtilities.getColumn(row, "display name", header, lastCellNum, null); // Get calculation String calculation = XLSUtilities.getColumn(row, "calculation", header, lastCellNum, null); if (calculation != null && calculation.length() > 0) { calculation = calculation.trim(); } else { throw new Exception(localisation.getString("mf_mc") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } ArrayList<String> sources = getSourcesFromItem(calculation); currentItem = new LQASItem(name, displayName, calculation, targetResponseText, sources); currentGroup.items.add(currentItem); } else if (rowType.equals("footer")) { String displayName = XLSUtilities.getColumn(row, "display name", header, lastCellNum, null); lqas.footer = new LQASItem("footer", displayName, null, null, null); } else if (rowType.equals("condition")) { if (currentDataItem != null) { processingConditions = true; String condition = XLSUtilities.getColumn(row, "condition", header, lastCellNum, null); String value = XLSUtilities.getColumn(row, "value", header, lastCellNum, null); if (condition == null) { throw new Exception("Missing \"condition\" on row: " + (j + 1)); } else { if (currentDataItem.select == null) { currentDataItem.select = new SqlFrag(); currentDataItem.select.add("CASE"); } if (condition.toLowerCase().trim().equals("all")) { currentDataItem.select.add("ELSE"); currentDataItem.select.addText(value); } else { currentDataItem.select.add("WHEN"); currentDataItem.select.addSqlFragment(condition, true, localisation); currentDataItem.select.add("THEN"); currentDataItem.select.addText(value); } } } else { throw new Exception(localisation.getString("mf_uc") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } else { throw new Exception(localisation.getString("mf_ur") + " " + localisation.getString("mf_or") + ": " + (j + 1)); } } } } } // Close of any condition type calculations if (processingConditions) { processingConditions = false; currentDataItem.select.add("END"); } } return lqas; }
From source file:utilities.XLSTaskManager.java
License:Open Source License
public ArrayList<TaskServerDefn> getXLSTaskList(String type, InputStream inputStream, ResourceBundle localisation, String tz) throws Exception { Sheet sheet = null;/* ww w . j a v a2 s .c om*/ Sheet settingsSheet = null; Row row = null; int lastRowNum = 0; ArrayList<TaskServerDefn> tl = new ArrayList<TaskServerDefn>(); HashMap<String, Integer> header = null; if (type != null && type.equals("xls")) { wb = new HSSFWorkbook(inputStream); } else { wb = new XSSFWorkbook(inputStream); } /* * Get the task sheet settings */ settingsSheet = wb.getSheet("settings"); if (settingsSheet.getPhysicalNumberOfRows() > 0) { int lastSettingsRow = settingsSheet.getLastRowNum(); for (int j = 0; j <= lastSettingsRow; j++) { row = settingsSheet.getRow(j); if (row != null) { int lastCellNum = row.getLastCellNum(); if (lastCellNum > 0) { Cell c = row.getCell(0); String k = c.getStringCellValue(); if (k != null && k.trim().toLowerCase().equals("time zone:")) { c = row.getCell(1); tz = c.getStringCellValue(); break; } } } } } ZoneId timeZoneId = ZoneId.of(tz); ZoneId gmtZoneId = ZoneId.of("GMT"); sheet = wb.getSheet("tasks"); if (sheet.getPhysicalNumberOfRows() > 0) { lastRowNum = sheet.getLastRowNum(); boolean needHeader = true; TaskServerDefn currentTask = null; for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); if (row != null) { int lastCellNum = row.getLastCellNum(); if (needHeader) { header = getHeader(row, lastCellNum); needHeader = false; } else { String tg_name = getColumn(row, "tg_name", header, lastCellNum, null); String form_name = getColumn(row, "form", header, lastCellNum, null); String assignee_ident = getColumn(row, "assignee_ident", header, lastCellNum, null); String email = getColumn(row, "email", header, lastCellNum, null); if (form_name != null && form_name.trim().length() > 0) { currentTask = new TaskServerDefn(); currentTask.tg_name = tg_name; currentTask.survey_name = form_name; currentTask.name = getColumn(row, "name", header, lastCellNum, ""); currentTask.location_trigger = getColumn(row, "location_trigger", header, lastCellNum, null); currentTask.location_group = getColumn(row, "location_group", header, lastCellNum, null); currentTask.location_name = getColumn(row, "location_name", header, lastCellNum, null); currentTask.lat = Double.valueOf(getColumn(row, "lat", header, lastCellNum, "0")); currentTask.lon = Double.valueOf(getColumn(row, "lon", header, lastCellNum, "0")); currentTask.guidance = getColumn(row, "guidance", header, lastCellNum, null); currentTask.from = getGmtDate(row, "from", header, lastCellNum, timeZoneId, gmtZoneId); currentTask.to = getGmtDate(row, "to", header, lastCellNum, timeZoneId, gmtZoneId); // Get from value String repValue = getColumn(row, "repeat", header, lastCellNum, null); if (repValue != null && repValue.equals("true")) { currentTask.repeat = true; } else { currentTask.repeat = false; } // Add assignment in same row as task AssignmentServerDefn currentAssignment = new AssignmentServerDefn(); currentAssignment.assignee_ident = assignee_ident; currentAssignment.email = email; currentAssignment.assignee_name = getColumn(row, "assignee_name", header, lastCellNum, null); currentAssignment.status = getColumn(row, "status", header, lastCellNum, null); currentTask.assignments.add(currentAssignment); tl.add(currentTask); } else if ((assignee_ident != null && assignee_ident.trim().length() > 0) || (email != null && email.trim().length() > 0)) { AssignmentServerDefn currentAssignment = new AssignmentServerDefn(); currentAssignment.assignee_ident = assignee_ident; currentAssignment.email = email; currentAssignment.assignee_name = getColumn(row, "assignee_name", header, lastCellNum, null); currentAssignment.status = getColumn(row, "status", header, lastCellNum, null); if (currentTask == null) { String msg = localisation.getString("t_no_task"); msg = msg.replaceAll("%s1", String.valueOf(j)); throw new Exception(msg); } currentTask.assignments.add(currentAssignment); } } } } } return tl; }
From source file:utilities.XLSTaskManager.java
License:Open Source License
public ArrayList<Location> convertWorksheetToTagArray(InputStream inputStream, String type) throws Exception { Sheet sheet = null;//from ww w . j av a2 s . c om Row row = null; int lastRowNum = 0; String group = null; ArrayList<Location> tags = new ArrayList<Location>(); HashMap<String, Integer> header = null; if (type != null && type.equals("xls")) { wb = new HSSFWorkbook(inputStream); } else { wb = new XSSFWorkbook(inputStream); } int numSheets = wb.getNumberOfSheets(); for (int i = 0; i < numSheets; i++) { sheet = wb.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { group = sheet.getSheetName(); lastRowNum = sheet.getLastRowNum(); boolean needHeader = true; for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); if (row != null) { int lastCellNum = row.getLastCellNum(); if (needHeader) { header = getHeader(row, lastCellNum); needHeader = false; } else { Location t = new Location(); t.group = group; t.type = "nfc"; try { t.uid = getColumn(row, "uid", header, lastCellNum, null); t.name = getColumn(row, "name", header, lastCellNum, null); if (t.name == null) { t.name = getColumn(row, "tagname", header, lastCellNum, null); // try legacy name } String lat = getColumn(row, "lat", header, lastCellNum, "0.0"); String lon = getColumn(row, "lon", header, lastCellNum, "0.0"); try { t.lat = Double.parseDouble(lat); t.lon = Double.parseDouble(lon); } catch (Exception e) { } if (t.name != null && t.name.trim().length() > 0) { tags.add(t); } } catch (Exception e) { log.info("Error getting nfc column" + e.getMessage()); } } } } } } return tags; }
From source file:utilities.XLSTemplateUploadManager.java
License:Open Source License
public Survey getSurvey(Connection sd, int oId, String type, InputStream inputStream, String displayName, int p_id, HashMap<String, String> questionNames, HashMap<String, String> optionNames, boolean merge, int existingVersion) throws Exception { this.questionNames = questionNames; this.optionNames = optionNames; this.merge = merge; wb = WorkbookFactory.create(inputStream); // Create survey and set defaults survey = new Survey(); survey.displayName = displayName;//from w w w . j a v a2 s . c o m survey.o_id = oId; survey.p_id = p_id; survey.version = merge ? existingVersion + 1 : 1; survey.loadedFromXLS = true; survey.deleted = false; survey.blocked = false; survey.meta .add(new MetaItem(metaId--, "string", "instanceID", null, "instanceid", null, false, null, null)); survey.meta.add( new MetaItem(metaId--, "string", "instanceName", null, "instancename", null, false, null, null)); surveySheet = wb.getSheet("survey"); choicesSheet = wb.getSheet("choices"); settingsSheet = wb.getSheet("settings"); if (surveySheet == null) { throw XLSUtilities.getApplicationException(localisation, "tu_nw", -1, "survey", null, null, null); } else if (surveySheet.getPhysicalNumberOfRows() == 0) { throw XLSUtilities.getApplicationException(localisation, "tu_ew", -1, "survey", null, null, null); } else { lastRowNumSurvey = surveySheet.getLastRowNum(); if (choicesSheet != null) { lastRowNumChoices = choicesSheet.getLastRowNum(); } if (settingsSheet != null) { lastRowNumSettings = settingsSheet.getLastRowNum(); } getHeaders(); // get headers and set the languages from them /* * 1. Process the choices sheet */ if (choicesSheet != null) { while (rowNumChoices <= lastRowNumChoices) { Row row = choicesSheet.getRow(rowNumChoices++); if (row != null) { int lastCellNum = row.getLastCellNum(); String listName = XLSUtilities.getTextColumn(row, "list name", choicesHeader, lastCellNum, null); if (listName == null) { listName = XLSUtilities.getTextColumn(row, "list_name", choicesHeader, lastCellNum, null); } if (listName != null) { OptionList ol = survey.optionLists.get(listName); if (ol == null) { ol = new OptionList(); survey.optionLists.put(listName, ol); } ol.options.add(getOption(row, listName)); } } } } /* * 2. Process the survey sheet */ Form f = getForm("main", -1, -1, null); // Validate the top level form if (survey.forms.get(0).questions.size() == 0) { throw new ApplicationException(localisation.getString("tu_nq")); } validateForm(1, f); /* * 3, Process the settings sheet */ if (settingsSheet != null && settingsHeader != null) { Row row = settingsSheet.getRow(rowNumSettings++); if (row != null) { int lastCellNum = row.getLastCellNum(); // Default language survey.def_lang = XLSUtilities.getTextColumn(row, "default_language", settingsHeader, lastCellNum, null); if (survey.def_lang != null) { boolean validLanguage = false; for (Language l : survey.languages) { if (l.name.equals(survey.def_lang)) { validLanguage = true; break; } } if (!validLanguage) { throw new ApplicationException(localisation.getString("tu_idl")); } } survey.instanceNameDefn = XLSUtilities.getTextColumn(row, "instance_name", settingsHeader, lastCellNum, null); survey.surveyClass = XLSUtilities.getTextColumn(row, "style", settingsHeader, lastCellNum, null); survey.task_file = getBooleanColumn(row, "allow_import", settingsHeader, lastCellNum); survey.setHideOnDevice(getBooleanColumn(row, "hide_on_device", settingsHeader, lastCellNum)); survey.timing_data = getBooleanColumn(row, "timing_data", settingsHeader, lastCellNum); survey.hrk = XLSUtilities.getTextColumn(row, "key", settingsHeader, lastCellNum, null); String pdRepeats = XLSUtilities.getTextColumn(row, "pulldata_repeat", settingsHeader, lastCellNum, null); if (pdRepeats != null) { String[] pdArray = pdRepeats.split(":"); if (pdArray.length > 0) { for (String pd : pdArray) { pd = pd.trim(); int idx = pd.indexOf("("); if (idx > 0) { String sName = pd.substring(0, idx); String key = pd.substring(idx + 1, pd.length() - 1); if (survey.pulldata == null) { survey.pulldata = new ArrayList<Pulldata>(); } survey.pulldata.add(new Pulldata(sName, key)); } } } } survey.key_policy = XLSUtilities.getTextColumn(row, "key_policy", settingsHeader, lastCellNum, null); // Add row filters if (rowRoleHeader != null && rowRoleHeader.size() > 0) { for (String h : rowRoleHeader.keySet()) { String filter = XLSUtilities.getTextColumn(row, h, settingsHeader, lastCellNum, null); if (filter != null) { Role r = survey.roles.get(h); if (r != null) { SqlFrag sq = new SqlFrag(); sq.addSqlFragment(filter, false, localisation); settingsQuestionInSurvey(sq.humanNames, h); // validate question names r.row_filter = filter; } } } } } } } /* * Add default preloads */ if (!hasMeta("start")) { survey.meta.add(new MetaItem(metaId--, "dateTime", "_start", "start", "_start", "timestamp", true, "start", null)); } if (!hasMeta("end")) { survey.meta .add(new MetaItem(metaId--, "dateTime", "_end", "end", "_end", "timestamp", true, "end", null)); } if (!hasMeta("deviceid")) { survey.meta.add(new MetaItem(metaId--, "string", "_device", "deviceid", "_device", "property", true, "device", null)); } validateSurvey(); // 4. Final Validation return survey; }