List of usage examples for org.apache.poi.ss.usermodel Sheet getPhysicalNumberOfRows
int getPhysicalNumberOfRows();
From source file:test.XExcel.java
public static void main(String[] args) throws FileNotFoundException, IOException { Properties bundle = new Properties(); bundle.load(new FileInputStream(new File("configuration.properties"))); Integer counter = Integer.parseInt(bundle.getProperty("loop.counter")); String titles[] = { "Firstname", "Lastname", "Country", "Language" }; String data[][] = { { "noman ali", "abbasi", "PK", "EN" }, { "ahsan", "shaikh", "PK", "EN" }, { "abdul jalil", "ahmed", "PK", "EN" }, { "umair", "khan", "PK", "EN" }, { "abdul rahim", "khan", "PK", "EN" } }; int rowCount = 0; try {/*from w w w.ja v a 2s . c o m*/ File xlsxFile = new File("C:/workbook.xlsx"); Workbook wb = null; Sheet sheet = null; Row row = null; if (xlsxFile.exists()) { FileInputStream fileInputStream = new FileInputStream(xlsxFile); wb = new XSSFWorkbook(fileInputStream); sheet = wb.getSheet("Test"); rowCount = sheet.getPhysicalNumberOfRows(); System.err.println("Writingxisting file ...."); for (int i = 0; i < counter; i++) { row = sheet.createRow(rowCount++); int rndNumber = new Random().nextInt(3); System.out.println(rndNumber); for (int c = 0; c < titles.length; c++) { Cell cell = row.createCell(c); cell.setCellValue(data[rndNumber][c]); } } System.err.println(xlsxFile.delete()); } else { System.err.println("Creatingl file ...."); wb = new XSSFWorkbook(); sheet = wb.createSheet("Test"); row = sheet.createRow(rowCount++); CellStyle cellStyle = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(font); for (int i = 0; i < titles.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(cellStyle); } for (int i = 0; i < counter; i++) { row = sheet.createRow(rowCount++); int rndNumber = new Random().nextInt(3); for (int c = 0; c < titles.length; c++) { Cell cell = row.createCell(c); cell.setCellValue(data[rndNumber][c]); } } } FileOutputStream fileOut; try { fileOut = new FileOutputStream("C:/workbook.xlsx", true); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } System.out.println("Last " + sheet.getLastRowNum() + ", " + sheet.getPhysicalNumberOfRows()); } catch (java.lang.IllegalArgumentException illegalArgumentException) { System.err.println(illegalArgumentException.getMessage()); } }
From source file:uk.ac.leeds.ccg.andyt.projects.pfi.XSLX2CSV.java
/** * Called to convert the contents of the currently opened workbook into a * CSV file.//from w w w .jav a 2 s . c o m */ private void convertToCSV(File excelFile, File destination) { Sheet sheet = null; Row row = null; int lastRowNum = 0; int firstRowNum = 0; this.csvData = new ArrayList<ArrayList<ArrayList<String>>>(); System.out.println("Converting files contents to CSV format."); // Discover how many sheets there are in the workbook.... int numSheets = this.workbook.getNumberOfSheets(); // and then iterate through them. for (int sheetid = 0; sheetid < numSheets; sheetid++) { this.csvData.add(new ArrayList<ArrayList<String>>()); // Get a reference to a sheet and check to see if it contains // any rows. sheet = this.workbook.getSheetAt(sheetid); if (sheet.getPhysicalNumberOfRows() > 0) { // Note down the index number of the bottom-most row and // then iterate through all of the rows on the sheet starting // from the very first row - number 1 - even if it is missing. // Recover a reference to the row and then call another method // which will strip the data from the cells and build lines // for inclusion in the resylting CSV file. lastRowNum = sheet.getLastRowNum(); firstRowNum = sheet.getFirstRowNum(); for (int j = firstRowNum; j <= lastRowNum; j++) { row = sheet.getRow(j); this.rowToCSV(row, sheetid); } } try { // Save the CSV file away using the newly constricted file name // and to the specified directory. this.saveCSVFile(destination); } catch (IOException ex) { Logger.getLogger(XSLX2CSV.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:util.ToCSV.java
License:Apache License
private void convertToCSV() { Sheet sheet = null; Row row = null;/*from w ww. j a v a2 s. c o m*/ int lastRowNum = 0; this.csvData = new ArrayList<ArrayList>(); int numSheets = this.workbook.getNumberOfSheets(); for (int i = 0; i < numSheets; i++) { sheet = this.workbook.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { lastRowNum = sheet.getLastRowNum(); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); this.rowToCSV(row); } } } }
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;// ww w . j a va2s . 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;// w w w. j a va 2 s .co m 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;//from ww w . j ava 2s. co m 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; Row row = null;/* w w w . j a v a2s . c om*/ 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:xlsParser.hffsimpl.SSReader.java
private XLSSheet createSheet(String name, Sheet sheet) { List<Row> rows = new ArrayList(sheet.getPhysicalNumberOfRows()); for (int i = 0; i < sheet.getPhysicalNumberOfRows(); ++i) { rows.add(sheet.getRow(i));//from w ww .j av a 2 s. co m } if (rows.size() < 1) { throw new IllegalStateException("Sheet row must be greater than 0" + " (for header) [" + name + "]"); } String[] header = parseRow(sheet.getRow(0), 0); Map<String, Integer> headerMap = new HashMap(header.length); for (int i = 0; i < header.length; ++i) { headerMap.put(header[i], i); } List<XLSRecord> contents = new ArrayList(rows.size() - 1); for (int i = 1; i < rows.size(); ++i) { contents.add(this.createRecord(headerMap, rows.get(i))); } return new BaseXLSSheet(name, headerMap, contents); }