List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet(String sheetname)
From source file:org.tdl.vireo.export.impl.ExcelPackagerImpl.java
/** * This function is used by Unit Test to export a {@link XSSFWorkbook} with all of the submissions as separate rows * /*from w w w . j ava 2 s .c o m*/ * This is needed because the Unit Test does not run through the ExportService (which is the iterator of {@link List} {@link Submission}) * * @param submissions * - list of submissions * @param columns * - the columns to include in the sheet * @return - The Excel workbook file (xssf format only) */ public XSSFWorkbook testWorkbook(List<Submission> submissions, List<SearchOrder> columns) { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName); XSSFRow header = sheet.createRow(0); int i = 1; // row counter for (Submission sub : submissions) { XSSFRow row = sheet.createRow(i); processWorkbookRow(header, row, sub, columns); i++; } return wb; }
From source file:org.tdl.vireo.export.impl.ExcelPackagerImpl.java
/** * Takes {@link XSSFWorkbook} argument, a single {@link Submission}, and a {@link List} of {@link SearchOrder}. * /*w ww . ja v a2 s . c om*/ * Creates a new {@link XSSFSheet} in the {@link XSSFWorkbook} and adds a header {@link XSSFRow} and a data {@link XSSFRow}. * * Passes the header row, the data row, the submission, and the {@link SearchOrder} {@link List} to processWorkbookRow() * * @param wb * - the workbook to modify in-place * @param sub * - the submission to add to the workbook * @param columns * - the columns to include in the sheet */ public XSSFWorkbook writeWorkbook(Submission sub, List<SearchOrder> columns) { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName); XSSFRow header = sheet.createRow(0); XSSFRow row = sheet.createRow(1); processWorkbookRow(header, row, sub, columns); return wb; }
From source file:org.tdl.vireo.model.jpa.JpaSubmissionImpl.java
private Attachment generateActionLogAttachment() { // create an Excel Workbook to store the action log as an attachment Attachment actionLogAttachment = null; String sheetName = "ActionLog"; XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName); XSSFRow header = sheet.createRow(0); int rowNum = 1; XSSFRow row = sheet.createRow(rowNum); int colNum = 0; for (ActionLog actionLog : actionLogs) { if (rowNum == 1) { header.createCell(colNum).setCellValue("Action Date"); }//from ww w . j av a2 s . co m row.createCell(colNum).setCellValue(actionLog.getActionDate()); colNum++; if (rowNum == 1) { header.createCell(colNum).setCellValue("Attachment Type"); } if (actionLog.getAttachment() != null) { row.createCell(colNum).setCellValue(actionLog.getAttachment().getType().name()); } colNum++; if (rowNum == 1) { header.createCell(colNum).setCellValue("Attachment Date"); } if (actionLog.getAttachment() != null) { row.createCell(colNum).setCellValue(actionLog.getAttachment().getDate()); } colNum++; if (rowNum == 1) { header.createCell(colNum).setCellValue("Attachment Name"); } if (actionLog.getAttachment() != null) { row.createCell(colNum).setCellValue(actionLog.getAttachment().getName()); } colNum++; if (rowNum == 1) { header.createCell(colNum).setCellValue("Attachment Size"); } if (actionLog.getAttachment() != null) { row.createCell(colNum).setCellValue(actionLog.getAttachment().getSize()); } colNum++; if (rowNum == 1) { header.createCell(colNum).setCellValue("Action Entry"); } row.createCell(colNum).setCellValue(actionLog.getEntry()); colNum++; if (rowNum == 1) { header.createCell(colNum).setCellValue("Submission State"); } row.createCell(colNum).setCellValue(actionLog.getSubmissionState().getBeanName()); colNum++; rowNum++; row = sheet.createRow(rowNum); colNum = 0; } try { File actionLogFile = File.createTempFile("actionlog-", ".xlsx"); actionLogFile.deleteOnExit(); FileOutputStream actionLogFileOS = new FileOutputStream(actionLogFile); wb.write(actionLogFileOS); actionLogFileOS.flush(); actionLogFileOS.close(); actionLogAttachment = new ActionLogAttachment(this, actionLogFile); } catch (IOException e) { play.Logger.error("Error while generating Action Log Attachment! [%s]", e); } return actionLogAttachment; }
From source file:org.tsukuba_bunko.lilac.helper.port.impl.ExportDataHelperBase.java
License:Open Source License
/** * @see org.tsukuba_bunko.lilac.helper.port.ExportDataHelper#exportData(org.apache.poi.xssf.usermodel.XSSFWorkbook) *//*w w w .ja v a2 s. com*/ @Override public void exportData(XSSFWorkbook book) { sheet = book.createSheet(getSheetName()); XSSFFont font = book.createFont(); font.setBold(true); font.setColor(IndexedColors.WHITE.index); headerCellStyle = book.createCellStyle(); headerCellStyle.setAlignment(HorizontalAlignment.CENTER); headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); headerCellStyle .setFillForegroundColor(new XSSFColor(new byte[] { (byte) 256, (byte) 0, (byte) 112, (byte) 192 })); headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerCellStyle.setFont(font); prepare(book); XSSFRow row = sheet.createRow(rowCount++); processHeaderRow(row); buildQuery().iterate(this); finish(book); }
From source file:org.wise.vle.web.VLEGetXLS.java
License:Open Source License
/** * Creates an excel workbook that contains student navigation data * Each sheet represents one student's work. The rows in each * sheet are sequential so the earliest navigation data is at * the top and the latest navigation data is at the bottom * //www. jav a 2s .co m * @param nodeIdToNodeTitlesMap a HashMap that contains nodeId to * nodeTitle mappings * @param workgroupIds a vector of workgroup ids * @param runId the run id * @param nodeIdToNode a mapping of node id to node object * @param nodeIdToNodeContent a mapping of node id to node content * @param workgroupIdToPeriodId a mapping of workgroup id to period id * @param teacherWorkgroupIds a list of teacher workgroup ids * * @return an excel workbook that contains the student navigation if we * are generating an xls file */ private XSSFWorkbook getAllStudentWorkXLSExport(HashMap<String, String> nodeIdToNodeTitlesMap, Vector<String> workgroupIds, String runId, HashMap<String, JSONObject> nodeIdToNode, HashMap<String, JSONObject> nodeIdToNodeContent, HashMap<Integer, Integer> workgroupIdToPeriodId, List<String> teacherWorkgroupIds) { XSSFWorkbook wb = null; if (isFileTypeXLS(fileType)) { //we are generating an xls file so we will create the workbook wb = new XSSFWorkbook(); } List<Node> customNodes = null; if (customSteps.size() != 0) { //the teacher has provided a list of custom steps to export //get all the Node objects for the custom steps customNodes = vleService.getNodesByNodeIdsAndRunId(customSteps, runId); } boolean isCSVHeaderRowWritten = false; //loop through all the workgroup ids for (int x = 0; x < workgroupIds.size(); x++) { //get a workgroup id String workgroupIdString = workgroupIds.get(x); //get the UserInfo object for the workgroup id //UserInfo userInfo = UserInfo.getByWorkgroupId(Long.parseLong(workgroupIdString)); UserInfo userInfo = vleService.getUserInfoByWorkgroupId(Long.parseLong(workgroupIdString)); if (userInfo != null) { //get the workgroup id Long workgroupId = userInfo.getWorkgroupId(); int workgroupIdInt = workgroupId.intValue(); //get the period id int periodId = workgroupIdToPeriodId.get(workgroupIdInt); List<StepWork> stepWorks = new ArrayList<StepWork>(); if (customNodes == null) { //the teacher has not provided a list of custom steps so we will gather work for all the steps //get all the work for that workgroup id stepWorks = vleService.getStepWorksByUserInfo(userInfo); } else { if (customNodes.size() > 0) { //the teacher has provided a list of custom steps so we will gather the work for those specific steps stepWorks = vleService.getStepWorksByUserInfoAndNodeList(userInfo, customNodes); } } //create a sheet in the excel for this workgroup id XSSFSheet userIdSheet = null; if (wb != null) { //create the sheet since we are generating an xls file userIdSheet = wb.createSheet(workgroupIdString); } //clear the step visit count clearStepVisitCount(); //clear the step revision count clearStepRevisionCount(); int rowCounter = 0; //counter for the header column cells int headerColumn = 0; //create the first row which will contain the headers Row headerRow = createRow(userIdSheet, rowCounter++); Vector<String> headerRowVector = createRowVector(); //the header column to just keep track of each row (which represents a step visit) headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "#"); //the header columns for the workgroup information and run information headerColumn = createUserDataHeaderRow(headerColumn, headerRow, headerRowVector, true, true); //the header column for the step work id headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Step Work Id"); //header step title column which already includes numbering headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Step Title"); //the header column for the step visit count headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Step Visit Count"); //the header column for the step revision count headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Step Revision Count"); //header step type column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Step Type"); //header step prompt column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Step Prompt"); //header node id column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Node Id"); //header post time column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Post Time (Server Clock)"); //header start time column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Start Time (Student Clock)"); //header end time column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "End Time (Student Clock)"); //header time the student spent on the step in seconds column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Visit Time Spent (Seconds)"); //header time the student spent on the revision in seconds column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Revision Time Spent (Seconds)"); //header time the student spent on the step in seconds column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Teacher Score Timestamp"); //header time the student spent on the step in seconds column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Teacher Score"); //header time the student spent on the step in seconds column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Teacher Max Score"); //header time the student spent on the step in seconds column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Teacher Comment Timestamp"); //header time the student spent on the step in seconds column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Teacher Comment"); //header cell for the auto score headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Auto Score"); //header cell for the max auto score headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Max Auto Score"); //header cell for the auto feedback headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Auto Feedback"); //header classmate id for review type steps headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Classmate Id"); //header receiving text for review type steps headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Receiving Text"); //header student work column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Student Work"); if (!isCSVHeaderRowWritten) { //we have not written the csv header row yet //write the csv row if we are generating a csv file writeCSV(headerRowVector); /* * set this flag to true so we don't write the header row into the csv again. * this means we will only output the header row once at the very top of the csv file. */ isCSVHeaderRowWritten = true; } //get all the work for a workgroup id List<StepWork> stepWorksForWorkgroupId = vleService.getStepWorksByUserInfo(userInfo); /* * loop through all the work for the current student, this will * already be ordered chronologically */ for (int y = 0; y < stepWorks.size(); y++) { /* * get a student work row which represents the work they * performed for a single step visit */ StepWork stepWork = stepWorks.get(y); //get the step work id Long stepWorkId = stepWork.getId(); //get the start and end time Timestamp startTime = stepWork.getStartTime(); Timestamp endTime = stepWork.getEndTime(); Timestamp postTime = stepWork.getPostTime(); //get the node id String nodeId = stepWork.getNode().getNodeId(); //get the node title String nodeTitle = nodeIdToNodeTitlesMap.get(nodeId); //get the node type String nodeType = getNodeTypeFromStepWork(stepWork); //get the step work data String stepWorkData = stepWork.getData(); //increase the step visit count and get the current value int stepVisitCount = increaseStepVisitCount(nodeId); //get the node content JSONObject nodeContent = nodeIdToNodeContent.get(nodeId); //check to see if node exists. if not, node has been deleted, so ignore it if (nodeContent == null) { continue; } //get the step prompt String nodePrompt = getPromptFromNodeContent(nodeContent); //get the node object JSONObject nodeJSONObject = nodeIdToNode.get(nodeId); String wiseId1 = ""; String wiseId2 = ""; String wiseId3 = ""; //get the post time in milliseconds long timestamp = postTime.getTime(); /* * get the student attendance that is relevant to the step work. we will * look for the first student attendance entry for this workgroup id * that has a login timestamp before the start time of this step work */ JSONObject studentAttendanceForWorkgroupIdTimestamp = getStudentAttendanceForWorkgroupIdTimestamp( workgroupId, timestamp); if (studentAttendanceForWorkgroupIdTimestamp == null) { /* * we could not find a student attendance entry so this probably * means this step work was created before we started logging * student absences. we will just display all the student ids for * the workgroup in this case. */ //get all the user ids for this workgroup String userIds = workgroupIdToUserIds.get(Integer.parseInt(workgroupId + "")); //the user ids string is delimited by ':' String[] userIdsArray = userIds.split(":"); //sort the user ids numerically and put them into a list ArrayList<Long> userIdsList = sortUserIdsArray(userIdsArray); //loop through all the user ids in this workgroup for (int z = 0; z < userIdsList.size(); z++) { //get a user id Long wiseId = userIdsList.get(z); //set the appropriate wise id if (z == 0) { wiseId1 = wiseId + ""; } else if (z == 1) { wiseId2 = wiseId + ""; } else if (z == 2) { wiseId3 = wiseId + ""; } } } else { try { //get the present and absent user ids JSONArray presentUserIds = studentAttendanceForWorkgroupIdTimestamp .getJSONArray("presentUserIds"); JSONArray absentUserIds = studentAttendanceForWorkgroupIdTimestamp .getJSONArray("absentUserIds"); HashMap<Long, String> studentAttendanceMap = new HashMap<Long, String>(); ArrayList<Long> userIds = new ArrayList<Long>(); //loop through all the present user ids for (int a = 0; a < presentUserIds.length(); a++) { long presentUserId = presentUserIds.getLong(a); studentAttendanceMap.put(presentUserId, "Present"); userIds.add(presentUserId); } //loop through all the absent user ids for (int b = 0; b < absentUserIds.length(); b++) { long absentUserId = absentUserIds.getLong(b); studentAttendanceMap.put(absentUserId, "Absent"); userIds.add(absentUserId); } //sort the user ids numerically Collections.sort(userIds); //loop through all the user ids for (int c = 0; c < userIds.size(); c++) { //get a user id Long tempUserId = userIds.get(c); //get whether the stuent was "Present" or "Absent" String studentAttendanceStatus = studentAttendanceMap.get(tempUserId); String studentAttendanceStatusSuffix = ""; if (studentAttendanceStatus != null && studentAttendanceStatus.equals("Absent")) { //the student was absent studentAttendanceStatusSuffix = " Absent"; } //set the appropriate wise id if (c == 0) { wiseId1 = tempUserId + studentAttendanceStatusSuffix; } else if (c == 1) { wiseId2 = tempUserId + studentAttendanceStatusSuffix; } else if (c == 2) { wiseId3 = tempUserId + studentAttendanceStatusSuffix; } } } catch (JSONException e) { e.printStackTrace(); } } try { //get the step work data as a JSONObject JSONObject stepWorkDataJSON = new JSONObject(stepWorkData); //check if there are are node states if (stepWorkDataJSON.has("nodeStates")) { //get the node states JSONArray nodeStates = stepWorkDataJSON.getJSONArray("nodeStates"); //write the student work rows for the node states rowCounter = writeAllStudentWorkRows(userIdSheet, rowCounter, nodeId, workgroupId, wiseId1, wiseId2, wiseId3, stepWorkId, stepVisitCount, nodeTitle, nodeType, nodePrompt, nodeContent, startTime, endTime, postTime, stepWork, periodId, userInfo, stepWorksForWorkgroupId, nodeJSONObject, nodeStates); } } catch (JSONException e) { e.printStackTrace(); } } } } return wb; }
From source file:org.wise.vle.web.VLEGetXLS.java
License:Open Source License
/** * Creates an excel workbook that contains student work data * All student work will be displayed on a single sheet. * The top row contains the node titles and the left column * contains the workgroup ids. Each x, y cell contains the latest * student work for that node, workgroup. * //from w w w . j a v a 2 s . co m * @param nodeIdToNodeTitlesMap a mapping of node id to node titles * @param workgroupIds a vector of workgroup ids * @param nodeIdList a list of ordered node ids * @param runId the id of the run * @param nodeIdToNode a mapping of node id to node object * @param nodeIdToNodeContent a mapping of node id to node content * @param workgroupIdToPeriodId a mapping of workgroup id to period id * @param teacherWorkgroupIds a list of teacher workgroup ids * * @return an excel workbook that contains student work data */ private XSSFWorkbook getLatestStudentWorkXLSExport(HashMap<String, String> nodeIdToNodeTitlesMap, Vector<String> workgroupIds, List<String> nodeIdList, String runId, HashMap<String, JSONObject> nodeIdToNode, HashMap<String, JSONObject> nodeIdToNodeContent, HashMap<Integer, Integer> workgroupIdToPeriodId, List<String> teacherWorkgroupIds) { XSSFWorkbook wb = null; if (isFileTypeXLS(fileType)) { //create the excel workbook wb = new XSSFWorkbook(); //create the sheet that will contain all the data XSSFSheet mainSheet = wb.createSheet("Latest Work For All Students"); } else if (isFileTypeCSV(fileType)) { wb = null; } /* * set the header rows in the sheet * Step Title * Step Type * Step Prompt * Node Id * Step Extra */ setGetLatestStudentWorkHeaderRows(wb, nodeIdList, nodeIdToNodeTitlesMap, nodeIdToNodeContent); //set the student work setGetLatestStudentWorkStudentRows(wb, nodeIdToNodeTitlesMap, workgroupIds, nodeIdList, runId, nodeIdToNode, nodeIdToNodeContent, workgroupIdToPeriodId, teacherWorkgroupIds); return wb; }
From source file:org.wise.vle.web.VLEGetXLS.java
License:Open Source License
/** * Get the explanation builder work excel export. We will generate a row * for each idea used in an explanation builder step. The order of * the explanation builder steps will be chronological from oldest to newest. * //from w w w. jav a2 s .c om * @param nodeIdToNodeTitlesMap a mapping of node id to node title * @param workgroupIds a vector of workgroup ids * @param runId the run id * @param nodeIdToNode a mapping of node id to node * @param nodeIdToNodeContent a mapping of node id to node content * @param workgroupIdToPeriodId a mapping of workgroup id to period id * @param teacherWorkgroupIds a list of teacher workgroup ids * * @return the excel workbook if we are generating an xls file */ private XSSFWorkbook getExplanationBuilderWorkExcelExport(HashMap<String, String> nodeIdToNodeTitlesMap, Vector<String> workgroupIds, String runId, HashMap<String, JSONObject> nodeIdToNode, HashMap<String, JSONObject> nodeIdToNodeContent, HashMap<Integer, Integer> workgroupIdToPeriodId, List<String> teacherWorkgroupIds) { //the excel workbook XSSFWorkbook wb = null; if (isFileTypeXLS(fileType)) { //we are generating an xls file so we will create the workbook wb = new XSSFWorkbook(); } boolean isCSVHeaderRowWritten = false; //loop through all the workgroups for (int x = 0; x < workgroupIds.size(); x++) { String workgroupId = workgroupIds.get(x); UserInfo userInfo = vleService.getUserInfoByWorkgroupId(Long.parseLong(workgroupId)); //create a sheet for the workgroup XSSFSheet userIdSheet = null; if (wb != null) { userIdSheet = wb.createSheet(workgroupId); } int rowCounter = 0; //counter for the header column cells int headerColumn = 0; //create the first row which will contain the headers Row headerRow = createRow(userIdSheet, rowCounter++); Vector<String> headerRowVector = createRowVector(); /* * create the cells that will display the user data headers such as workgroup id, * student login, teacher login, period name, etc. */ headerColumn = createUserDataHeaderRow(headerColumn, headerRow, headerRowVector, true, true); //vector that contains all the header column names Vector<String> headerColumnNames = new Vector<String>(); headerColumnNames.add("Step Work Id"); headerColumnNames.add("Step Title"); headerColumnNames.add("Step Prompt"); headerColumnNames.add("Node Id"); headerColumnNames.add("Post Time (Server Clock)"); headerColumnNames.add("Start Time (Student Clock)"); headerColumnNames.add("End Time (Student Clock)"); headerColumnNames.add("Time Spent (in seconds)"); headerColumnNames.add("Answer"); headerColumnNames.add("Idea Id"); headerColumnNames.add("Idea Text"); headerColumnNames.add("Idea X Position"); headerColumnNames.add("Idea Y Position"); headerColumnNames.add("Idea Color"); headerColumnNames.add("Idea Width"); headerColumnNames.add("Idea Height"); //add all the header column names to the row for (int y = 0; y < headerColumnNames.size(); y++) { headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, headerColumnNames.get(y)); } if (!isCSVHeaderRowWritten) { //we have not written the csv header row yet //write the csv row if we are generating a csv file writeCSV(headerRowVector); /* * set this flag to true so we don't write the header row into the csv again. * this means we will only output the header row once at the very top of the csv file. */ isCSVHeaderRowWritten = true; } //get all the work from the workgroup List<StepWork> stepWorks = vleService.getStepWorksByUserInfo(userInfo); //loop through all the work for (int z = 0; z < stepWorks.size(); z++) { StepWork stepWork = stepWorks.get(z); //get the node and node type Node node = stepWork.getNode(); String nodeType = node.getNodeType(); if (nodeType != null && nodeType.equals("ExplanationBuilderNode")) { //the work is for an explanation builder step //get the student work String data = stepWork.getData(); try { //get the JSONObject representation of the student work JSONObject dataJSONObject = new JSONObject(data); //get the node states from the student work JSONArray nodeStates = dataJSONObject.getJSONArray("nodeStates"); if (nodeStates != null && nodeStates.length() > 0) { //get the last node state JSONObject nodeState = nodeStates.getJSONObject(nodeStates.length() - 1); //get the answer the student typed in the text area String answer = nodeState.getString("answer"); //get all the ideas used in this step JSONArray explanationIdeas = nodeState.getJSONArray("explanationIdeas"); if (explanationIdeas != null && explanationIdeas.length() > 0) { //loop through all the ideas used in this step for (int i = 0; i < explanationIdeas.length(); i++) { //get one of the ideas that was used JSONObject explanationIdea = explanationIdeas.getJSONObject(i); //create a row for this idea Row ideaRow = createRow(userIdSheet, rowCounter++); Vector<String> ideaRowVector = createRowVector(); int columnCounter = 0; //get the step work id and node id Long stepWorkId = stepWork.getId(); String nodeId = node.getNodeId(); //get the title of the step String title = nodeIdToNodeTitlesMap.get(nodeId); //get the content for the step JSONObject nodeContent = nodeIdToNodeContent.get(nodeId); String prompt = ""; if (nodeContent != null) { if (nodeContent.has("prompt")) { //get the prompt prompt = nodeContent.getString("prompt"); } } //get the start, end and post time for the student visit Timestamp startTime = stepWork.getStartTime(); Timestamp endTime = stepWork.getEndTime(); Timestamp postTime = stepWork.getPostTime(); long timeSpentOnStep = 0; //calculate the time the student spent on the step if (endTime == null || startTime == null) { //set to -1 if either start or end was null so we can set the cell to N/A later timeSpentOnStep = -1; } else { /* * find the difference between start and end and divide by * 1000 to obtain the value in seconds */ timeSpentOnStep = (endTime.getTime() - startTime.getTime()) / 1000; } /* * create the cells that will display the user data such as the actual values * for workgroup id, student login, teacher login, period name, etc. */ columnCounter = createUserDataRow(columnCounter, ideaRow, ideaRowVector, workgroupId, true, true, null); columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, stepWorkId); columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, title); columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, prompt); columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, nodeId); //set the post time if (postTime != null) { columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, timestampToFormattedString(postTime)); } else { columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, ""); } //set the start time columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, timestampToFormattedString(startTime)); //set the end time if (endTime != null) { columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, timestampToFormattedString(endTime)); } else { columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, ""); } //set the time spent on the step if (timeSpentOnStep == -1) { columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, "N/A"); } else { columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, timeSpentOnStep); } columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, answer); columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, explanationIdea.getLong("id")); columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, explanationIdea.getString("lastAcceptedText")); columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, explanationIdea.getLong("xpos")); columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, explanationIdea.getLong("ypos")); columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, getColorNameFromRBGString(explanationIdea.getString("color"))); if (explanationIdea.has("width")) { columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, explanationIdea.getLong("width")); } else { columnCounter++; } if (explanationIdea.has("height")) { columnCounter = setCellValue(ideaRow, ideaRowVector, columnCounter, explanationIdea.getLong("height")); } else { columnCounter++; } //write the csv row if we are generating a csv file writeCSV(ideaRowVector); } } } } catch (JSONException e) { e.printStackTrace(); } } } } return wb; }
From source file:org.wise.vle.web.VLEGetXLS.java
License:Open Source License
/** * Get the annotator work excel export. We will generate a row * for each label in an annotator step. The order of * the annotator steps will be chronological from oldest to newest. * //from w w w.j a v a 2 s .c o m * @param nodeIdToNodeTitlesMap a mapping of node id to node title * @param workgroupIds a vector of workgroup ids * @param runId the run id * @param nodeIdToNode a mapping of node id to node * @param nodeIdToNodeContent a mapping of node id to node content * @param workgroupIdToPeriodId a mapping of workgroup id to period id * @param teacherWorkgroupIds a list of teacher workgroup ids * * @return the excel workbook if we are generating an xls file */ private XSSFWorkbook getAnnotatorWorkExcelExport(HashMap<String, String> nodeIdToNodeTitlesMap, Vector<String> workgroupIds, String runId, HashMap<String, JSONObject> nodeIdToNode, HashMap<String, JSONObject> nodeIdToNodeContent, HashMap<Integer, Integer> workgroupIdToPeriodId, List<String> teacherWorkgroupIds) { //the excel workbook XSSFWorkbook wb = null; if (isFileTypeXLS(fileType)) { //we are generating an xls file so we will create the workbook wb = new XSSFWorkbook(); } //loop through all the workgroups for (int x = 0; x < workgroupIds.size(); x++) { String workgroupId = workgroupIds.get(x); UserInfo userInfo = vleService.getUserInfoByWorkgroupId(Long.parseLong(workgroupId)); //create a sheet for the workgroup XSSFSheet userIdSheet = null; if (wb != null) { userIdSheet = wb.createSheet(workgroupId); } //counter for the rows int rowCounter = 0; //counter for the header column cells int headerColumn = 0; //map to keep track of how many revisions the student has submitted for a step HashMap<String, Integer> nodeIdToStepRevisionCount = new HashMap<String, Integer>(); //map to keep track of how many times the student has checked their score for a step HashMap<String, Integer> nodeIdToCheckScoreAttemptCount = new HashMap<String, Integer>(); //counter to keep track of the max number of scoring criteria out of all the steps Integer maxScoringCriteriaCount = 0; //create the first row which will contain the headers Row headerRow = createRow(userIdSheet, rowCounter++); Vector<String> headerRowVector = createRowVector(); /* * create the cells that will display the user data headers such as workgroup id, * student login, teacher login, period name, etc. */ headerColumn = createUserDataHeaderRow(headerColumn, headerRow, headerRowVector, true, true); //write the csv row if we are generating a csv file writeCSV(headerRowVector); //vector that contains all the header column names Vector<String> headerColumnNames = new Vector<String>(); headerColumnNames.add("Step Work Id"); headerColumnNames.add("Step Title"); headerColumnNames.add("Step Prompt"); headerColumnNames.add("Node Id"); headerColumnNames.add("Post Time (Server Clock)"); headerColumnNames.add("Start Time (Student Clock)"); headerColumnNames.add("End Time (Student Clock)"); headerColumnNames.add("Time Spent (in seconds)"); headerColumnNames.add("Step Revision Count"); headerColumnNames.add("Explanation"); headerColumnNames.add("Number of Labels"); headerColumnNames.add("Label Id"); headerColumnNames.add("Label Text"); headerColumnNames.add("Label Color"); headerColumnNames.add("Label Location X"); headerColumnNames.add("Label Location Y"); headerColumnNames.add("Text Color"); headerColumnNames.add("Text Location X"); headerColumnNames.add("Text Location Y"); headerColumnNames.add("Check Work"); headerColumnNames.add("Check Score Attempt Number"); headerColumnNames.add("Auto Score"); headerColumnNames.add("Max Auto Score"); //add all the header column names to the row for (int y = 0; y < headerColumnNames.size(); y++) { headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, headerColumnNames.get(y)); } //write the csv row if we are generating a csv file writeCSV(headerRowVector); //get all the work from the workgroup List<StepWork> stepWorks = vleService.getStepWorksByUserInfo(userInfo); //loop through all the work for (int z = 0; z < stepWorks.size(); z++) { StepWork stepWork = stepWorks.get(z); //get the node and node type Node node = stepWork.getNode(); String nodeType = node.getNodeType(); if (nodeType != null && nodeType.equals("AnnotatorNode")) { //the work is for an annotator step //get the node id String nodeId = node.getNodeId(); //get the student work String data = stepWork.getData(); try { //get the JSONObject representation of the student work JSONObject dataJSONObject = new JSONObject(data); //get the node states from the student work JSONArray nodeStates = dataJSONObject.optJSONArray("nodeStates"); if (nodeStates != null && nodeStates.length() > 0) { //loop through all the node states for (int n = 0; n < nodeStates.length(); n++) { //get a node state JSONObject nodeState = nodeStates.getJSONObject(n); //get the data from the node state JSONObject nodeStateData = nodeState.optJSONObject("data"); if (nodeStateData != null) { //get the step revision count Integer stepRevisionCount = nodeIdToStepRevisionCount.get(nodeId); if (stepRevisionCount == null) { //this is the first revision so we will initialize the value stepRevisionCount = 1; } /* * increment the step revision count which will now contain the next * revision number */ nodeIdToStepRevisionCount.put(nodeId, stepRevisionCount + 1); //get the student explanation String explanation = nodeStateData.optString("explanation"); //get the student labels JSONArray labels = nodeStateData.optJSONArray("labels"); //get the total number of labels the student has created including deleted ones Long totalNumberOfLabelsCreated = nodeStateData.optLong("total"); //get the auto scoring values Long autoScore = nodeState.optLong("autoScore"); Long maxAutoScore = nodeState.optLong("maxAutoScore"); Boolean checkWork = nodeState.optBoolean("checkWork"); JSONArray scoringCriteriaResults = nodeState .optJSONArray("scoringCriteriaResults"); Integer checkScoreAttemptCount = null; if (scoringCriteriaResults != null) { //the student checked this work //get the check score attempt count for the step checkScoreAttemptCount = nodeIdToCheckScoreAttemptCount.get(nodeId); if (checkScoreAttemptCount == null) { //this is the first check score attempt so we will initialize the value checkScoreAttemptCount = 1; } /* * increment the check score attempt count which will now contain the next * check score attempt number */ nodeIdToCheckScoreAttemptCount.put(nodeId, checkScoreAttemptCount + 1); } if (labels == null || labels.length() == 0) { /* * there are no labels so we will display a row with the step visit data and * the student explanation if there is one */ //create a row Row row = createRow(userIdSheet, rowCounter++); Vector<String> rowVector = createRowVector(); //initialize the column counter int columnCounter = 0; //get the number of labels int labelCount = labels.length(); //fill the common cells of the annotator row columnCounter = fillCommonCellsOfAnnotatorRow(columnCounter, row, rowVector, stepWork, nodeIdToNodeTitlesMap, nodeId, workgroupId, stepRevisionCount, explanation, labelCount); } else { //there is at least one label //get the number of labels int labelCount = labels.length(); //loop through all the student labels for (int labelCounter = 0; labelCounter < labels.length(); labelCounter++) { //get a student label JSONObject label = labels.optJSONObject(labelCounter); if (label != null) { //get label values String labelId = label.optString("id"); String labelTextColor = label.optString("textColor"); String labelText = label.optString("text"); String labelColor = label.optString("color"); Double labelLocationX = null; Double labelLocationY = null; Double labelTextLocationX = null; Double labelTextLocationY = null; JSONObject location = label.optJSONObject("location"); if (location != null) { labelLocationX = location.optDouble("x"); labelLocationY = location.optDouble("y"); } JSONObject textLocation = label.optJSONObject("textLocation"); if (textLocation != null) { labelTextLocationX = textLocation.optDouble("x"); labelTextLocationY = textLocation.optDouble("y"); } //create a row for this label Row row = createRow(userIdSheet, rowCounter++); Vector<String> rowVector = createRowVector(); //initialize the column counter int columnCounter = 0; //fill the common cells of the annotator row columnCounter = fillCommonCellsOfAnnotatorRow(columnCounter, row, rowVector, stepWork, nodeIdToNodeTitlesMap, nodeId, workgroupId, stepRevisionCount, explanation, labelCount); //fill the label values columnCounter = setCellValue(row, rowVector, columnCounter, labelId); columnCounter = setCellValue(row, rowVector, columnCounter, labelText); columnCounter = setCellValue(row, rowVector, columnCounter, getColorNameFromColorHex(labelColor)); columnCounter = setCellValue(row, rowVector, columnCounter, labelLocationX); columnCounter = setCellValue(row, rowVector, columnCounter, labelLocationY); columnCounter = setCellValue(row, rowVector, columnCounter, getColorNameFromColorHex(labelTextColor)); columnCounter = setCellValue(row, rowVector, columnCounter, labelTextLocationX); columnCounter = setCellValue(row, rowVector, columnCounter, labelTextLocationY); columnCounter = setCellValue(row, rowVector, columnCounter, Boolean.toString(checkWork)); //check if this student work was auto scored if (scoringCriteriaResults != null) { columnCounter = setCellValue(row, rowVector, columnCounter, checkScoreAttemptCount); columnCounter = setCellValue(row, rowVector, columnCounter, autoScore); columnCounter = setCellValue(row, rowVector, columnCounter, maxAutoScore); //loop through all the scoring criteria results for (int scr = 0; scr < scoringCriteriaResults .length(); scr++) { //get a scoring criteria result JSONObject scoringCriteriaResult = scoringCriteriaResults .optJSONObject(scr); //get the scoring criteria result values Long scoringCriteriaId = scoringCriteriaResult .optLong("id"); Long scoringCriteriaScore = scoringCriteriaResult .optLong("score"); Long scoringCriteriaMaxScore = scoringCriteriaResult .optLong("maxScore"); Boolean isSatisfied = scoringCriteriaResult .optBoolean("isSatisfied"); String feedback = scoringCriteriaResult .optString("feedback"); //populate the cells with the scoring criteria result values columnCounter = setCellValue(row, rowVector, columnCounter, scoringCriteriaId); columnCounter = setCellValue(row, rowVector, columnCounter, scoringCriteriaScore); columnCounter = setCellValue(row, rowVector, columnCounter, scoringCriteriaMaxScore); columnCounter = setCellValue(row, rowVector, columnCounter, Boolean.toString(isSatisfied)); columnCounter = setCellValue(row, rowVector, columnCounter, feedback); } /* * remember the max number of scoring criteria results for all steps so we can * display the appropriate number of header cells */ if (scoringCriteriaResults.length() > maxScoringCriteriaCount) { maxScoringCriteriaCount = scoringCriteriaResults.length(); } } //write the csv row if we are generating a csv file writeCSV(rowVector); } } } } } } } catch (JSONException e) { e.printStackTrace(); } } } //display the header cells for the scoring criteria for (int m = 0; m < maxScoringCriteriaCount; m++) { //get the scoring criteria count int scoringCriteriaCount = m + 1; //add additional header cells to the header column headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Scoring Criteria Id " + scoringCriteriaCount); headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Scoring Criteria Score " + scoringCriteriaCount); headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Scoring Criteria Max Score " + scoringCriteriaCount); headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Is Satisfied " + scoringCriteriaCount); headerColumn = setCellValue(headerRow, headerRowVector, headerColumn, "Feedback " + scoringCriteriaCount); } //create a blank row for spacing Vector<String> emptyVector2 = createRowVector(); writeCSV(emptyVector2); } return wb; }
From source file:org.wise.vle.web.VLEGetXLS.java
License:Open Source License
/** * Get the flash work excel export. We will generate a row * for each item used in a flash step. The order of * the flash steps will be chronological from oldest to newest. * /*from ww w .java2 s. c om*/ * @param nodeIdToNodeTitlesMap a mapping of node id to node titles * @param workgroupIds a vector of workgroup ids * @param runId the run id * @param nodeIdToNode a mapping of node id to node * @param nodeIdToNodeContent a mapping of node id to node content * @param workgroupIdToPeriodId a mapping of workgroup id to period id * @param teacherWorkgroupIds a list of teacher workgroup ids * * @return an excel workbook if we are generating an xls file */ private XSSFWorkbook getFlashWorkExcelExport(HashMap<String, String> nodeIdToNodeTitlesMap, Vector<String> workgroupIds, String runId, HashMap<String, JSONObject> nodeIdToNode, HashMap<String, JSONObject> nodeIdToNodeContent, HashMap<Integer, Integer> workgroupIdToPeriodId, List<String> teacherWorkgroupIds) { //the excel workbook XSSFWorkbook wb = null; if (isFileTypeXLS(fileType)) { //we are generating an xls file so we will create the workbook wb = new XSSFWorkbook(); } //whether to also export all the other student work boolean exportAllWork = true; //counter for the row that we are on int rowCounter = 0; //we will export everything onto one sheet XSSFSheet allWorkgroupsSheet = null; if (wb != null) { allWorkgroupsSheet = wb.createSheet("All Workgroups"); } String teacherLogin = ""; try { //get the teacher login teacherLogin = teacherUserInfoJSONObject.getString("userName"); } catch (JSONException e1) { e1.printStackTrace(); } //counter for the header column cells int headerColumn = 0; //create the first row which will contain the headers Row headerRow = createRow(allWorkgroupsSheet, rowCounter++); //vector that contains all the header column names Vector<String> headerColumnNames = new Vector<String>(); //define the text for the header cells headerColumnNames.add("Workgroup Id"); headerColumnNames.add("Wise Id 1"); headerColumnNames.add("Wise Id 2"); headerColumnNames.add("Wise Id 3"); headerColumnNames.add("Teacher Login"); headerColumnNames.add("Project Id"); headerColumnNames.add("Parent Project Id"); headerColumnNames.add("Project Name"); headerColumnNames.add("Run Id"); headerColumnNames.add("Run Name"); headerColumnNames.add("Start Date"); headerColumnNames.add("End Date"); headerColumnNames.add("Period"); headerColumnNames.add("Step Work Id"); headerColumnNames.add("Step Title"); headerColumnNames.add("Step Type"); headerColumnNames.add("Step Prompt"); headerColumnNames.add("Node Id"); headerColumnNames.add("Start Time"); headerColumnNames.add("End Time"); headerColumnNames.add("Time Spent (in seconds)"); headerColumnNames.add("Revision Number"); headerColumnNames.add("Item Number"); headerColumnNames.add("Custom Grading"); headerColumnNames.add("Label Text"); headerColumnNames.add("X Pos"); headerColumnNames.add("Y Pos"); headerColumnNames.add("Is Deleted"); headerColumnNames.add("X HandleBar"); headerColumnNames.add("Y HandleBar"); headerColumnNames.add("New"); headerColumnNames.add("Revised"); headerColumnNames.add("Repositioned"); headerColumnNames.add("Deleted False to True"); headerColumnNames.add("Deleted True to False"); //add all the header column names to the row for (int y = 0; y < headerColumnNames.size(); y++) { headerRow.createCell(headerColumn).setCellValue(headerColumnNames.get(y)); headerColumn++; } //loop through all the workgroups for (int x = 0; x < workgroupIds.size(); x++) { //get the workgroup id String workgroupId = workgroupIds.get(x); UserInfo userInfo = vleService.getUserInfoByWorkgroupId(Long.parseLong(workgroupId)); //get the period String periodName = workgroupIdToPeriodName.get(Integer.parseInt(workgroupId)); //get all the user ids for this workgroup String userIds = workgroupIdToUserIds.get(Integer.parseInt(workgroupId + "")); //the user ids string is delimited by ':' String[] userIdsArray = userIds.split(":"); //sort the user ids numerically and put them into a list ArrayList<Long> userIdsList = sortUserIdsArray(userIdsArray); String wiseId1 = ""; String wiseId2 = ""; String wiseId3 = ""; //loop through all the user ids in this workgroup for (int z = 0; z < userIdsList.size(); z++) { //get a user id Long wiseId = userIdsList.get(z); //set the appropriate wise id if (z == 0) { wiseId1 = wiseId + ""; } else if (z == 1) { wiseId2 = wiseId + ""; } else if (z == 2) { wiseId3 = wiseId + ""; } } /* * vector to keep track of all the start time timestamps to eliminate * duplicate step work entries. we previously had a bug where a student * client would send hundreds or even thousands of post requests with * the same stepwork. we have resolved this bug by checking for duplicates * whenever a post request comes into the server but some of the previous * runs that experienced this bug still have the duplicate step work entries. */ Vector<Timestamp> previousTimestamps = new Vector<Timestamp>(); //get all the work from the workgroup List<StepWork> stepWorks = vleService.getStepWorksByUserInfo(userInfo); //remember the previous response so we can determine what has changed JSONObject previousResponse = null; //stores the revision number for the node ids for the current workgroup HashMap<String, Long> nodeIdToRevisionNumber = new HashMap<String, Long>(); //stores the previous response for a node id HashMap<String, JSONObject> nodeIdToPreviousResponse = new HashMap<String, JSONObject>(); //loop through all the work for (int z = 0; z < stepWorks.size(); z++) { StepWork stepWork = stepWorks.get(z); //get the start and end time for the student visit Timestamp visitStartTime = stepWork.getStartTime(); Timestamp visitEndTime = stepWork.getEndTime(); if (!previousTimestamps.contains(visitStartTime)) { //get the node and node type Node node = stepWork.getNode(); String nodeType = node.getNodeType(); String nodeId = node.getNodeId(); if (nodeType != null && nodeType.equals("FlashNode") && nodeId != null) { //the work is for a flash step //get the step type e.g. "Flash" String stepType = nodeType.replace("Node", ""); //get the student work String data = stepWork.getData(); try { //get the JSONObject representation of the student work JSONObject dataJSONObject = new JSONObject(data); //get the node states from the student work JSONArray nodeStates = dataJSONObject.getJSONArray("nodeStates"); if (nodeStates != null && nodeStates.length() > 0) { //get the last node state JSONObject nodeState = nodeStates.getJSONObject(nodeStates.length() - 1); JSONObject response = nodeState.getJSONObject("response"); if (response != null) { //get the array of items JSONArray dataArray = response.getJSONArray("data"); //get the human readable custom grading String customGrading = response.getString("customGrading"); //get the current revision number to use Long revisionNumber = nodeIdToRevisionNumber.get(nodeId); if (revisionNumber == null) { //initialize the value if this is the first revision revisionNumber = 1L; } //get the previous response previousResponse = nodeIdToPreviousResponse.get(nodeId); //loop through all the items for (int i = 0; i < dataArray.length(); i++) { //get an item JSONObject itemLabel = dataArray.getJSONObject(i); //get the attributes of the item String labelText = itemLabel.getString("labelText"); long xPos = itemLabel.getLong("xPos"); long yPos = itemLabel.getLong("yPos"); boolean isDeleted = itemLabel.getBoolean("isDeleted"); long handleBarX = itemLabel.getLong("handleBarX"); long handleBarY = itemLabel.getLong("handleBarY"); //create a row for this idea Row itemRow = createRow(allWorkgroupsSheet, rowCounter++); Vector<String> itemRowVector = createRowVector(); int columnCounter = 0; //get the step work id and node id Long stepWorkId = stepWork.getId(); //get the title of the step String title = nodeIdToNodeTitlesMap.get(nodeId); //get the content for the step JSONObject nodeContent = nodeIdToNodeContent.get(nodeId); String prompt = ""; if (nodeContent != null) { if (nodeContent.has("prompt")) { //get the prompt prompt = nodeContent.getString("prompt"); } } long timeSpentOnStep = 0; //calculate the time the student spent on the step if (visitEndTime == null || visitStartTime == null) { //set to -1 if either start or end was null so we can set the cell to N/A later timeSpentOnStep = -1; } else { /* * find the difference between start and end and divide by * 1000 to obtain the value in seconds */ timeSpentOnStep = (visitEndTime.getTime() - visitStartTime.getTime()) / 1000; } //set the workgroup values into the row columnCounter = setCellValueConvertStringToLong(itemRow, itemRowVector, columnCounter, workgroupId); columnCounter = setCellValueConvertStringToLong(itemRow, itemRowVector, columnCounter, wiseId1); columnCounter = setCellValueConvertStringToLong(itemRow, itemRowVector, columnCounter, wiseId2); columnCounter = setCellValueConvertStringToLong(itemRow, itemRowVector, columnCounter, wiseId3); //set the project run values into the row columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, teacherLogin); columnCounter = setCellValueConvertStringToLong(itemRow, itemRowVector, columnCounter, projectId); columnCounter = setCellValueConvertStringToLong(itemRow, itemRowVector, columnCounter, parentProjectId); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, projectName); columnCounter = setCellValueConvertStringToLong(itemRow, itemRowVector, columnCounter, runId); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, runName); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, startTime); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, endTime); //set the step values into the row columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, periodName); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, stepWorkId); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, title); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, stepType); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, prompt); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, nodeId); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, timestampToFormattedString(visitStartTime)); //set the visit end time if (visitEndTime != null) { columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, timestampToFormattedString(visitEndTime)); } else { columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, ""); } //set the time spent on the step if (timeSpentOnStep == -1) { columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, "N/A"); } else { columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, timeSpentOnStep); } //set the student work values into the row columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, revisionNumber); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, i + 1); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, customGrading); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, labelText); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, xPos); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, yPos); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, Boolean.toString(isDeleted)); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, handleBarX); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, handleBarY); boolean isItemNew = isItemNew(itemLabel, i, previousResponse); boolean isItemLabelTextRevised = isItemLabelTextRevised(itemLabel, i, previousResponse); boolean isItemRepositioned = isItemRepositioned(itemLabel, i, previousResponse); boolean isItemDeletedFalseToTrue = isItemDeletedFalseToTrue(itemLabel, i, previousResponse); boolean isItemDeletedTrueToFalse = isItemDeletedTrueToFalse(itemLabel, i, previousResponse); //set the values that specify whether the student data has changed columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, getIntFromBoolean(isItemNew)); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, getIntFromBoolean(isItemLabelTextRevised)); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, getIntFromBoolean(isItemRepositioned)); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, getIntFromBoolean(isItemDeletedFalseToTrue)); columnCounter = setCellValue(itemRow, itemRowVector, columnCounter, getIntFromBoolean(isItemDeletedTrueToFalse)); //write the csv row if we are generating a csv file writeCSV(itemRowVector); } if (dataArray.length() > 0) { previousResponse = response; //remember the previous response nodeIdToPreviousResponse.put(nodeId, previousResponse); //update the revision number counter revisionNumber++; nodeIdToRevisionNumber.put(nodeId, revisionNumber); } } } } catch (JSONException e) { e.printStackTrace(); } } else if (exportAllWork) { //we will export all the non flash step student work //get the student work String data = stepWork.getData(); //get the work from the step work String stepWorkResponse = getStepWorkResponse(stepWork); /* * we will display the step work if it exists and is not for * SVGDrawNode because SVGDrawNode student data can sometimes * cause problems when Excel tries to parse the SVG student * data */ if (stepWorkResponse.equals("") && !nodeType.equals("SVGDrawNode")) { //get the JSONObject representation of the student work try { JSONObject dataJSONObject = new JSONObject(data); //get the node states from the student work JSONArray nodeStates = dataJSONObject.getJSONArray("nodeStates"); if (nodeStates != null && nodeStates.length() > 0) { //get the last node state JSONObject nodeState = nodeStates.getJSONObject(nodeStates.length() - 1); stepWorkResponse = nodeState.toString(); } } catch (JSONException e1) { e1.printStackTrace(); } } /* * there is a bug that saves SVGDraw step data in IdeaBasket steps so we will * not display any student data for IdeaBasket steps */ if (nodeType.equals("IdeaBasketNode")) { stepWorkResponse = ""; } //create a row for this idea Row workRow = createRow(allWorkgroupsSheet, rowCounter++); Vector<String> workRowVector = createRowVector(); int columnCounter = 0; //get the step work id and node id Long stepWorkId = stepWork.getId(); //get the title of the step String title = nodeIdToNodeTitlesMap.get(nodeId); String stepType = nodeType.replace("Node", ""); //get the content for the step JSONObject nodeContent = nodeIdToNodeContent.get(nodeId); //String prompt = ""; String prompt = getPromptFromNodeContent(nodeContent); long timeSpentOnStep = 0; //calculate the time the student spent on the step if (visitEndTime == null || visitStartTime == null) { //set to -1 if either start or end was null so we can set the cell to N/A later timeSpentOnStep = -1; } else { /* * find the difference between start and end and divide by * 1000 to obtain the value in seconds */ timeSpentOnStep = (visitEndTime.getTime() - visitStartTime.getTime()) / 1000; } //set the workgroup values into the row columnCounter = setCellValueConvertStringToLong(workRow, workRowVector, columnCounter, workgroupId); columnCounter = setCellValueConvertStringToLong(workRow, workRowVector, columnCounter, wiseId1); columnCounter = setCellValueConvertStringToLong(workRow, workRowVector, columnCounter, wiseId2); columnCounter = setCellValueConvertStringToLong(workRow, workRowVector, columnCounter, wiseId3); //set the run values into the row columnCounter = setCellValue(workRow, workRowVector, columnCounter, teacherLogin); columnCounter = setCellValueConvertStringToLong(workRow, workRowVector, columnCounter, projectId); columnCounter = setCellValueConvertStringToLong(workRow, workRowVector, columnCounter, parentProjectId); columnCounter = setCellValue(workRow, workRowVector, columnCounter, projectName); columnCounter = setCellValueConvertStringToLong(workRow, workRowVector, columnCounter, runId); columnCounter = setCellValue(workRow, workRowVector, columnCounter, runName); columnCounter = setCellValue(workRow, workRowVector, columnCounter, startTime); columnCounter = setCellValue(workRow, workRowVector, columnCounter, endTime); //set the step values into the row columnCounter = setCellValue(workRow, workRowVector, columnCounter, periodName); columnCounter = setCellValue(workRow, workRowVector, columnCounter, stepWorkId); columnCounter = setCellValue(workRow, workRowVector, columnCounter, title); columnCounter = setCellValue(workRow, workRowVector, columnCounter, stepType); columnCounter = setCellValue(workRow, workRowVector, columnCounter, prompt); columnCounter = setCellValue(workRow, workRowVector, columnCounter, nodeId); columnCounter = setCellValue(workRow, workRowVector, columnCounter, timestampToFormattedString(visitStartTime)); //set the visit end time if (visitEndTime != null) { columnCounter = setCellValue(workRow, workRowVector, columnCounter, timestampToFormattedString(visitEndTime)); } else { columnCounter = setCellValue(workRow, workRowVector, columnCounter, ""); } //set the time spent on the step if (timeSpentOnStep == -1) { columnCounter = setCellValue(workRow, workRowVector, columnCounter, "N/A"); } else { columnCounter = setCellValue(workRow, workRowVector, columnCounter, timeSpentOnStep); } columnCounter++; columnCounter++; addEmptyElementsToVector(workRowVector, 2); //set the student work into the row columnCounter = setCellValue(workRow, workRowVector, columnCounter, stepWorkResponse); //write the csv row if we are generating a csv file writeCSV(workRowVector); } //add the visit start time to our vector so we can check for duplicate entries previousTimestamps.add(visitStartTime); } } } return wb; }
From source file:packtest.BigGridDemo.java
License:Apache License
public static void main(String[] args) throws Exception { // Step 1. Create a template file. Setup sheets and workbook-level objects such as // cell styles, number formats, etc. XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Big Grid"); Map<String, XSSFCellStyle> styles = createStyles(wb); //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml String sheetRef = sheet.getPackagePart().getPartName().getName(); //save the template FileOutputStream os = new FileOutputStream("template.xlsx"); wb.write(os);//ww w .j a v a2 s . co m os.close(); //Step 2. Generate XML file. File tmp = File.createTempFile("sheet", ".xml"); Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING); generate(fw, styles); fw.close(); //Step 3. Substitute the template entry with the generated data FileOutputStream out = new FileOutputStream(Utils.getPath("big-grid.xlsx")); substitute(new File(Utils.getPath("template.xlsx")), tmp, sheetRef.substring(1), out); out.close(); wb.close(); }