Java tutorial
/* * ConcourseConnect * Copyright 2009 Concursive Corporation * http://www.concursive.com * * This file is part of ConcourseConnect, an open source social business * software and community platform. * * Concursive ConcourseConnect is free software: you can redistribute it and/or * modify it under the terms of the GNU Affero General Public License as published * by the Free Software Foundation, version 3 of the License. * * Under the terms of the GNU Affero General Public License you must release the * complete source code for any application that uses any part of ConcourseConnect * (system header files and libraries used by the operating system are excluded). * These terms must be included in any work that has ConcourseConnect components. * If you are developing and distributing open source applications under the * GNU Affero General Public License, then you are free to use ConcourseConnect * under the GNU Affero General Public License. * * If you are deploying a web site in which users interact with any portion of * ConcourseConnect over a network, the complete source code changes must be made * available. For example, include a link to the source archive directly from * your web site. * * For OEMs, ISVs, SIs and VARs who distribute ConcourseConnect with their * products, and do not license and distribute their source code under the GNU * Affero General Public License, Concursive provides a flexible commercial * license. * * To anyone in doubt, we recommend the commercial license. Our commercial license * is competitively priced and will eliminate any confusion about how * ConcourseConnect can be used and distributed. * * ConcourseConnect is distributed in the hope that it will be useful, but WITHOUT * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more * details. * * You should have received a copy of the GNU Affero General Public License * along with ConcourseConnect. If not, see <http://www.gnu.org/licenses/>. * * Attribution Notice: ConcourseConnect is an Original Work of software created * by Concursive Corporation */ package com.concursive.connect.web.modules.plans.utils; import com.concursive.connect.web.modules.plans.dao.Assignment; import com.concursive.connect.web.modules.plans.dao.Requirement; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import java.io.ByteArrayInputStream; import java.sql.Connection; import java.sql.SQLException; import java.util.Date; /** * Imports data from other formats into an outline * * @author matt rajkowski * @created June 30, 2004 */ public class AssignmentExcelImporter { /** * Description of the Method * * @param requirement Description of the Parameter * @param db Description of the Parameter * @param buffer Description of the Parameter * @return Description of the Return Value * @throws java.sql.SQLException Description of the Exception */ public static boolean parse(byte[] buffer, Requirement requirement, Connection db) throws SQLException { if (System.getProperty("DEBUG") != null) { System.out.println("AssignmentExcelImporter-> parseExcel"); } try { db.setAutoCommit(false); // stream the Excel Spreadsheet from the uploaded byte array POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(buffer)); HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); // get the first sheet HSSFSheet sheet = hssfworkbook.getSheetAt(0); // define objects for housing spreadsheet data HSSFRow currentRow = sheet.getRow(0); // parse each row, create and insert into a new requirement with a tree int rows = sheet.getPhysicalNumberOfRows(); if (System.getProperty("DEBUG") != null) { System.out.println("AssignmentExcelImporter-> Number of rows: " + rows); } // Columns int columnHeader = -1; int columnMax = -1; boolean columnItemComplete = false; short itemColumn = -1; short priorityColumn = -1; short assignedToColumn = -1; short effortColumn = -1; short startColumn = -1; short endColumn = -1; // parse for (int r = 0; r < rows; r++) { currentRow = sheet.getRow(r); if (currentRow != null) { // Search for header if (columnHeader == -1) { int cells = currentRow.getPhysicalNumberOfCells(); for (short c = 0; c < cells; c++) { HSSFCell cell = currentRow.getCell(c); if (cell != null) { if ("Item".equals(getValue(cell))) { columnHeader = r; itemColumn = c; columnMax = c; } else if (itemColumn > -1 && !columnItemComplete && c > itemColumn) { if ("".equals(getValue(cell))) { columnMax = c; } else if (!"".equals(getValue(cell))) { columnItemComplete = true; } } if ("Priority".equals(getValue(cell))) { columnHeader = r; priorityColumn = c; } else if ("Assigned To".equals(getValue(cell))) { columnHeader = r; assignedToColumn = c; } else if ("Lead".equals(getValue(cell))) { columnHeader = r; assignedToColumn = c; } else if ("Effort".equals(getValue(cell))) { columnHeader = r; effortColumn = c; } else if ("Start".equals(getValue(cell))) { columnHeader = r; startColumn = c; } else if ("End".equals(getValue(cell))) { columnHeader = r; endColumn = c; } } } } // Process each column if (columnHeader > -1 && r > columnHeader) { boolean gotOne = false; Assignment assignment = new Assignment(); assignment.setProjectId(requirement.getProjectId()); assignment.setRequirementId(requirement.getId()); // Activities and folders if (itemColumn > -1) { // Get the first indent level that has data for (short c = itemColumn; c <= columnMax; c++) { HSSFCell cell = currentRow.getCell(c); if (cell != null && !"".equals(getValue(cell))) { assignment.setRole(getValue(cell)); assignment.setIndent(c); gotOne = true; break; } } } if (gotOne) { // Priority if (priorityColumn > -1) { HSSFCell cell = currentRow.getCell(priorityColumn); if (cell != null) { assignment.setPriorityId(getValue(cell)); } } // Effort if (effortColumn > -1) { HSSFCell cell = currentRow.getCell(effortColumn); if (cell != null) { assignment.setEstimatedLoe(getValue(cell)); if (assignment.getEstimatedLoeTypeId() == -1) { assignment.setEstimatedLoeTypeId(2); } } } // Assigned To if (assignedToColumn > -1) { HSSFCell cell = currentRow.getCell(assignedToColumn); if (cell != null) { assignment.addUsers(getValue(cell)); } } // Start Date if (startColumn > -1) { HSSFCell cell = currentRow.getCell(startColumn); if (cell != null) { assignment.setEstStartDate(getDateValue(cell)); } } // Due Date if (endColumn > -1) { HSSFCell cell = currentRow.getCell(endColumn); if (cell != null) { assignment.setDueDate(getDateValue(cell)); } } assignment.setEnteredBy(requirement.getEnteredBy()); assignment.setModifiedBy(requirement.getModifiedBy()); assignment.setStatusId(1); // Make sure a valid priority is set if (assignment.getPriorityId() < 1 || assignment.getPriorityId() > 3) { assignment.setPriorityId(2); } // Make sure user is on team, before adding, else unset the field if (!assignment.hasValidTeam(db)) { assignment.getAssignedUserList().clear(); } // Insert the assignment assignment.insert(db); if (System.getProperty("DEBUG") != null) { System.out.println( "AssignmentExcelImporter-> Assignment Inserted: " + assignment.getId()); } } } } } db.commit(); } catch (Exception e) { db.rollback(); e.printStackTrace(System.out); return false; } finally { db.setAutoCommit(true); } return true; } /** * Gets the value attribute of the AssignmentImporter class * * @param cell Description of the Parameter * @return The value value */ private static String getValue(HSSFCell cell) { if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()); } if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { return cell.getStringCellValue().trim(); } if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { return ""; } if (System.getProperty("DEBUG") != null) { System.out.println("AssignmentExcelImporter-> NONE: " + cell.getCellType()); } try { return cell.getStringCellValue().trim(); } catch (Exception e) { } try { return String.valueOf(cell.getNumericCellValue()); } catch (Exception e) { } try { return String.valueOf(cell.getBooleanCellValue()); } catch (Exception e) { } return null; } private static Date getDateValue(HSSFCell cell) { try { return cell.getDateCellValue(); } catch (Exception e) { return null; } } }