List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet
@Override
public HSSFSheet getSheet(String name)
From source file:org.sakaiproject.assignment.impl.BaseAssignmentService.java
License:Educational Community License
protected void zipSubmissions(String assignmentReference, String assignmentTitle, String gradeTypeString, int typeOfSubmission, Iterator submissions, OutputStream outputStream, StringBuilder exceptionMessage, boolean withStudentSubmissionText, boolean withStudentSubmissionAttachment, boolean withGradeFile, boolean withFeedbackText, boolean withFeedbackComment, boolean withFeedbackAttachment, boolean withoutFolders, String gradeFileFormat) { ZipOutputStream out = null;//from w w w . j av a2s. com //Excel generation HSSFWorkbook gradesWorkbook = null; HSSFSheet dataSheet = null; try { out = new ZipOutputStream(outputStream); // create the folder structure - named after the assignment's title String root = escapeInvalidCharsEntry(Validator.escapeZipEntry(assignmentTitle)) + Entity.SEPARATOR; // create excel datasheet if ("excel".equals(gradeFileFormat)) { String sheetTitle = escapeInvalidCharsEntry(Validator.escapeZipEntry(assignmentTitle)); gradesWorkbook = createGradesWorkbook(sheetTitle, false); dataSheet = gradesWorkbook.getSheet(sheetTitle); } String submittedText = ""; if (!submissions.hasNext()) { exceptionMessage.append("There is no submission yet. "); } // the buffer used to store grade information ByteArrayOutputStream gradesBAOS = new ByteArrayOutputStream(); CSVWriter gradesBuffer = new CSVWriter(new OutputStreamWriter(gradesBAOS)); String[] values = { assignmentTitle, gradeTypeString }; gradesBuffer.writeNext(values); //Blank line was in original gradefile values = new String[] { "" }; gradesBuffer.writeNext(values); values = new String[] { rb.getString("grades.id"), rb.getString("grades.eid"), rb.getString("grades.lastname"), rb.getString("grades.firstname"), rb.getString("grades.grade") }; gradesBuffer.writeNext(values); // allow add assignment members List allowAddSubmissionUsers = allowAddSubmissionUsers(assignmentReference); // Create the ZIP file String submittersName = ""; int count = 1; int xlsRowCount = 1; String caughtException = null; while (submissions.hasNext()) { AssignmentSubmission s = (AssignmentSubmission) submissions.next(); boolean isAnon = assignmentUsesAnonymousGrading(s); if (s.getSubmitted()) { // get the submission user id and see if the user is still in site String userId = s.getSubmitterId(); try { User u = UserDirectoryService.getUser(userId); if (allowAddSubmissionUsers.contains(u)) { count = 1; submittersName = root; User[] submitters = s.getSubmitters(); String submittersString = ""; for (int i = 0; i < submitters.length; i++) { if (i > 0) { submittersString = submittersString.concat("; "); } String fullName = submitters[i].getSortName(); // in case the user doesn't have first name or last name if (fullName.indexOf(",") == -1) { fullName = fullName.concat(","); } submittersString = submittersString.concat(fullName); // add the eid to the end of it to guarantee folder name uniqness // if user Eid contains non ascii characters, the user internal id will be used String userEid = submitters[i].getEid(); String candidateEid = escapeInvalidCharsEntry(userEid); if (candidateEid.equals(userEid)) { submittersString = submittersString + "(" + candidateEid + ")"; } else { submittersString = submittersString + "(" + submitters[i].getId() + ")"; } submittersString = escapeInvalidCharsEntry(submittersString); String fullAnonId = s.getAnonymousSubmissionId(); String anonTitle = rb.getString("grading.anonymous.title"); // in grades file, Eid is used if ("csv".equals(gradeFileFormat)) { // SAK-17606 if (!isAnon) { values = new String[] { submitters[i].getDisplayId(), submitters[i].getEid(), submitters[i].getLastName(), submitters[i].getFirstName(), s.getGradeDisplay() }; gradesBuffer.writeNext(values); } else { // anonymous grading is true so we need to print different stuff in the csv values = new String[] { fullAnonId, fullAnonId, anonTitle, anonTitle, s.getGradeDisplay() }; gradesBuffer.writeNext(values); } } //Adding the row to the excel file if ("excel".equals(gradeFileFormat)) { if (!isAnon) { addExcelRowInfo(dataSheet, xlsRowCount, false, submitters[i].getDisplayId(), submitters[i].getEid(), submitters[i].getLastName(), submitters[i].getFirstName(), s.getGradeDisplay()); } else { addExcelRowInfo(dataSheet, xlsRowCount, false, fullAnonId, fullAnonId, anonTitle, anonTitle, s.getGradeDisplay()); } xlsRowCount++; } } if (StringUtils.trimToNull(submittersString) != null) { submittersName = submittersName.concat(StringUtils.trimToNull(submittersString)); submittedText = s.getSubmittedText(); // SAK-17606 if (isAnon) { submittersName = root + s.getAnonymousSubmissionId(); submittersString = s.getAnonymousSubmissionId(); } if (!withoutFolders) { submittersName = submittersName.concat("/"); } else { submittersName = submittersName.concat("_"); } // record submission timestamp if (!withoutFolders) { if (s.getSubmitted() && s.getTimeSubmitted() != null) { ZipEntry textEntry = new ZipEntry(submittersName + "timestamp.txt"); out.putNextEntry(textEntry); byte[] b = (s.getTimeSubmitted().toString()).getBytes(); out.write(b); textEntry.setSize(b.length); out.closeEntry(); } } // create the folder structure - named after the submitter's name if (typeOfSubmission != Assignment.ATTACHMENT_ONLY_ASSIGNMENT_SUBMISSION && typeOfSubmission != Assignment.NON_ELECTRONIC_ASSIGNMENT_SUBMISSION) { // include student submission text if (withStudentSubmissionText) { // create the text file only when a text submission is allowed String submittersNameString = submittersName + submittersString; //remove folder name if Download All is without user folders if (withoutFolders) { submittersNameString = submittersName; } ZipEntry textEntry = new ZipEntry(submittersNameString + "_submissionText" + ZIP_SUBMITTED_TEXT_FILE_TYPE); out.putNextEntry(textEntry); byte[] text = submittedText.getBytes(); out.write(text); textEntry.setSize(text.length); out.closeEntry(); } // include student submission feedback text if (withFeedbackText) { // create a feedbackText file into zip ZipEntry fTextEntry = new ZipEntry(submittersName + "feedbackText.html"); out.putNextEntry(fTextEntry); byte[] fText = s.getFeedbackText().getBytes(); out.write(fText); fTextEntry.setSize(fText.length); out.closeEntry(); } } if (typeOfSubmission != Assignment.TEXT_ONLY_ASSIGNMENT_SUBMISSION && typeOfSubmission != Assignment.NON_ELECTRONIC_ASSIGNMENT_SUBMISSION) { // include student submission attachment if (withStudentSubmissionAttachment) { //remove "/" that creates a folder if Download All is without user folders String sSubAttachmentFolder = submittersName + rb.getString("stuviewsubm.submissatt");//jh + "/"; if (!withoutFolders) { // create a attachment folder for the submission attachments sSubAttachmentFolder = submittersName + rb.getString("stuviewsubm.submissatt") + "/"; sSubAttachmentFolder = escapeInvalidCharsEntry(sSubAttachmentFolder); ZipEntry sSubAttachmentFolderEntry = new ZipEntry(sSubAttachmentFolder); out.putNextEntry(sSubAttachmentFolderEntry); } else { sSubAttachmentFolder = sSubAttachmentFolder + "_"; //submittersName = submittersName.concat("_"); } // add all submission attachment into the submission attachment folder zipAttachments(out, submittersName, sSubAttachmentFolder, s.getSubmittedAttachments()); out.closeEntry(); } } if (withFeedbackComment) { // the comments.txt file to show instructor's comments ZipEntry textEntry = new ZipEntry( submittersName + "comments" + ZIP_COMMENT_FILE_TYPE); out.putNextEntry(textEntry); byte[] b = FormattedText.encodeUnicode(s.getFeedbackComment()).getBytes(); out.write(b); textEntry.setSize(b.length); out.closeEntry(); } if (withFeedbackAttachment) { // create an attachment folder for the feedback attachments String feedbackSubAttachmentFolder = submittersName + rb.getString("download.feedback.attachment"); if (!withoutFolders) { feedbackSubAttachmentFolder = feedbackSubAttachmentFolder + "/"; ZipEntry feedbackSubAttachmentFolderEntry = new ZipEntry( feedbackSubAttachmentFolder); out.putNextEntry(feedbackSubAttachmentFolderEntry); } else { submittersName = submittersName.concat("_"); } // add all feedback attachment folder zipAttachments(out, submittersName, feedbackSubAttachmentFolder, s.getFeedbackAttachments()); out.closeEntry(); } } // if } } catch (Exception e) { caughtException = e.toString(); break; } } // if the user is still in site } // while -- there is submission if (caughtException == null) { // continue if (withGradeFile) { if ("csv".equals(gradeFileFormat)) { // create a grades.csv file into zip ZipEntry gradesCSVEntry = new ZipEntry(root + "grades.csv"); out.putNextEntry(gradesCSVEntry); gradesBuffer.close(); out.write(gradesBAOS.toByteArray()); gradesCSVEntry.setSize(gradesBAOS.size()); out.closeEntry(); } if ("excel".equals(gradeFileFormat)) { // create a grades.xls file into zip ZipEntry gradesEXCELEntry = new ZipEntry(root + "grades.xls"); out.putNextEntry(gradesEXCELEntry); gradesWorkbook.write(out); out.closeEntry(); } } } else { // log the error exceptionMessage.append(" Exception " + caughtException + " for creating submission zip file for assignment " + "\"" + assignmentTitle + "\"\n"); } } catch (IOException e) { exceptionMessage.append("IOException for creating submission zip file for assignment " + "\"" + assignmentTitle + "\" exception: " + e + "\n"); } finally { // Complete the ZIP file if (out != null) { try { out.finish(); out.flush(); } catch (IOException e) { // tried } try { out.close(); } catch (IOException e) { // tried } } } }
From source file:org.sns.tool.data.DataGeneratorSources.java
License:Open Source License
public DataGeneratorSources(final InputStream workbookStream) throws IOException { assert workbookStream != null; final POIFSFileSystem fileSystem = new POIFSFileSystem(workbookStream); final HSSFWorkbook workbook = new HSSFWorkbook(fileSystem); assert fileSystem != null; assert workbook != null; readNamesAndCounts(workbook.getSheet("Common Male Names"), maleNamesAndCounts); readNamesAndCounts(workbook.getSheet("Common Female Names"), femaleNamesAndCounts); readNamesAndCounts(workbook.getSheet("Common Surnames"), surnamesAndCounts); readCitiesAndPopulations(workbook.getSheet("Largest US Cities")); readSingleColumn(workbook.getSheet("Popular Street Names"), popularStreetNames); readSingleColumn(workbook.getSheet("Common Street Suffixes"), commonStreetSuffixes); readSingleColumn(workbook.getSheet("USPS Street Suffixes"), uspsStreetSuffixes); final HSSFSheet censusData = workbook.getSheet("Census Data"); totalNationalPopulation = (int) censusData.getRow(1).getCell((short) 1).getNumericCellValue(); malePopulationPercentage = censusData.getRow(4).getCell((short) 2).getNumericCellValue() / 100.0; femalePopulationPercentage = censusData.getRow(5).getCell((short) 2).getNumericCellValue() / 100.0; workbookStream.close();//from www. ja v a 2 s. co m }
From source file:org.springframework.batch.spreadsheet.ExcelTemplate.java
License:Apache License
/** * This is the work horse for row-level worksheet processing. * <p>// www. j av a 2 s . c o m * 1) Read data from file.<br/> * 3) Create an empty List.<br/> * 4) Iterate over the specific worksheet, building up the list.<br/> * 5) Return the list. * * @param <T> - type of the object to be returned * @param worksheetName - name of the worksheet to process * @param excelCallback - callback defining how to process a row of data * @param skipFirstRow * @param errorHandler * @return list of T objects */ public <T> List<T> onEachRow(String worksheetName, ExcelRowCallback<T> excelCallback, boolean skipFirstRow, ExcelTemplateErrorHandler<T> errorHandler) { try { InputStream inp = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp)); List<T> results = new ArrayList<T>(); if (skipFirstRow) { boolean firstRow = true; for (Row row : wb.getSheet(worksheetName)) { if (firstRow) { firstRow = false; continue; } processRow(excelCallback, errorHandler, results, row); } } else { for (Row row : wb.getSheet(worksheetName)) { processRow(excelCallback, errorHandler, results, row); } } return results; } catch (FileNotFoundException e) { throw new RuntimeException(e); } catch (IOException e) { throw new RuntimeException(e); } }
From source file:org.springframework.web.servlet.view.document.ExcelTestSuite.java
License:Apache License
public void testExcel() throws Exception { AbstractExcelView excelView = new AbstractExcelView() { protected void buildExcelDocument(Map model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.createSheet(); wb.setSheetName(0, "Test Sheet"); // test all possible permutation of row or column not existing HSSFCell cell = getCell(sheet, 2, 4); cell.setCellValue("Test Value"); cell = getCell(sheet, 2, 3); setText(cell, "Test Value"); cell = getCell(sheet, 3, 4); setText(cell, "Test Value"); cell = getCell(sheet, 2, 4); setText(cell, "Test Value"); }/* w ww . j a va 2 s . com*/ }; excelView.render(new HashMap(), request, response); POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray())); HSSFWorkbook wb = new HSSFWorkbook(poiFs); assertEquals("Test Sheet", wb.getSheetName(0)); HSSFSheet sheet = wb.getSheet("Test Sheet"); HSSFRow row = sheet.getRow(2); HSSFCell cell = row.getCell((short) 4); assertEquals("Test Value", cell.getStringCellValue()); }
From source file:org.springframework.web.servlet.view.document.ExcelTestSuite.java
License:Apache License
public void testExcelWithTemplateNoLoc() throws Exception { request.setAttribute(DispatcherServlet.LOCALE_RESOLVER_ATTRIBUTE, newDummyLocaleResolver("nl", "nl")); AbstractExcelView excelView = new AbstractExcelView() { protected void buildExcelDocument(Map model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.getSheet("Sheet1"); // test all possible permutation of row or column not existing HSSFCell cell = getCell(sheet, 2, 4); cell.setCellValue("Test Value"); cell = getCell(sheet, 2, 3); setText(cell, "Test Value"); cell = getCell(sheet, 3, 4); setText(cell, "Test Value"); cell = getCell(sheet, 2, 4); setText(cell, "Test Value"); }/*from w w w . j ava 2 s . c o m*/ }; excelView.setApplicationContext(webAppCtx); excelView.setUrl("template"); excelView.render(new HashMap(), request, response); POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray())); HSSFWorkbook wb = new HSSFWorkbook(poiFs); HSSFSheet sheet = wb.getSheet("Sheet1"); HSSFRow row = sheet.getRow(0); HSSFCell cell = row.getCell((short) 0); assertEquals("Test Template", cell.getStringCellValue()); }
From source file:org.springframework.web.servlet.view.document.ExcelTestSuite.java
License:Apache License
public void testExcelWithTemplateAndCountryAndLanguage() throws Exception { request.setAttribute(DispatcherServlet.LOCALE_RESOLVER_ATTRIBUTE, newDummyLocaleResolver("en", "US")); AbstractExcelView excelView = new AbstractExcelView() { protected void buildExcelDocument(Map model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.getSheet("Sheet1"); // test all possible permutation of row or column not existing HSSFCell cell = getCell(sheet, 2, 4); cell.setCellValue("Test Value"); cell = getCell(sheet, 2, 3); setText(cell, "Test Value"); cell = getCell(sheet, 3, 4); setText(cell, "Test Value"); cell = getCell(sheet, 2, 4); setText(cell, "Test Value"); }/*from w ww.j a v a 2 s. co m*/ }; excelView.setApplicationContext(webAppCtx); excelView.setUrl("template"); excelView.render(new HashMap(), request, response); POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray())); HSSFWorkbook wb = new HSSFWorkbook(poiFs); HSSFSheet sheet = wb.getSheet("Sheet1"); HSSFRow row = sheet.getRow(0); HSSFCell cell = row.getCell((short) 0); assertEquals("Test Template American English", cell.getStringCellValue()); }
From source file:org.springframework.web.servlet.view.document.ExcelTestSuite.java
License:Apache License
public void testExcelWithTemplateAndLanguage() throws Exception { request.setAttribute(DispatcherServlet.LOCALE_RESOLVER_ATTRIBUTE, newDummyLocaleResolver("de", "")); AbstractExcelView excelView = new AbstractExcelView() { protected void buildExcelDocument(Map model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.getSheet("Sheet1"); // test all possible permutation of row or column not existing HSSFCell cell = getCell(sheet, 2, 4); cell.setCellValue("Test Value"); cell = getCell(sheet, 2, 3); setText(cell, "Test Value"); cell = getCell(sheet, 3, 4); setText(cell, "Test Value"); cell = getCell(sheet, 2, 4); setText(cell, "Test Value"); }/*from w ww . j a v a 2 s . c om*/ }; excelView.setApplicationContext(webAppCtx); excelView.setUrl("template"); excelView.render(new HashMap(), request, response); POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray())); HSSFWorkbook wb = new HSSFWorkbook(poiFs); HSSFSheet sheet = wb.getSheet("Sheet1"); HSSFRow row = sheet.getRow(0); HSSFCell cell = row.getCell((short) 0); assertEquals("Test Template auf Deutsch", cell.getStringCellValue()); }
From source file:org.springframework.web.servlet.view.document.ExcelViewTests.java
License:Apache License
@Test public void testExcel() throws Exception { AbstractExcelView excelView = new AbstractExcelView() { @Override/*from w ww. jav a 2s . c om*/ protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.createSheet("Test Sheet"); // test all possible permutation of row or column not existing HSSFCell cell = getCell(sheet, 2, 4); cell.setCellValue("Test Value"); cell = getCell(sheet, 2, 3); setText(cell, "Test Value"); cell = getCell(sheet, 3, 4); setText(cell, "Test Value"); cell = getCell(sheet, 2, 4); setText(cell, "Test Value"); } }; excelView.render(new HashMap<String, Object>(), request, response); POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray())); HSSFWorkbook wb = new HSSFWorkbook(poiFs); assertEquals("Test Sheet", wb.getSheetName(0)); HSSFSheet sheet = wb.getSheet("Test Sheet"); HSSFRow row = sheet.getRow(2); HSSFCell cell = row.getCell(4); assertEquals("Test Value", cell.getStringCellValue()); }
From source file:org.springframework.web.servlet.view.document.ExcelViewTests.java
License:Apache License
@Test public void testExcelWithTemplateNoLoc() throws Exception { request.setAttribute(DispatcherServlet.LOCALE_RESOLVER_ATTRIBUTE, newDummyLocaleResolver("nl", "nl")); AbstractExcelView excelView = new AbstractExcelView() { @Override//from w w w. ja v a 2s . c o m protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.getSheet("Sheet1"); // test all possible permutation of row or column not existing HSSFCell cell = getCell(sheet, 2, 4); cell.setCellValue("Test Value"); cell = getCell(sheet, 2, 3); setText(cell, "Test Value"); cell = getCell(sheet, 3, 4); setText(cell, "Test Value"); cell = getCell(sheet, 2, 4); setText(cell, "Test Value"); } }; excelView.setApplicationContext(webAppCtx); excelView.setUrl("template"); excelView.render(new HashMap<String, Object>(), request, response); POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray())); HSSFWorkbook wb = new HSSFWorkbook(poiFs); HSSFSheet sheet = wb.getSheet("Sheet1"); HSSFRow row = sheet.getRow(0); HSSFCell cell = row.getCell(0); assertEquals("Test Template", cell.getStringCellValue()); }
From source file:org.springframework.web.servlet.view.document.ExcelViewTests.java
License:Apache License
@Test public void testExcelWithTemplateAndCountryAndLanguage() throws Exception { request.setAttribute(DispatcherServlet.LOCALE_RESOLVER_ATTRIBUTE, newDummyLocaleResolver("en", "US")); AbstractExcelView excelView = new AbstractExcelView() { @Override/*from w w w . j a va2 s .c om*/ protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.getSheet("Sheet1"); // test all possible permutation of row or column not existing HSSFCell cell = getCell(sheet, 2, 4); cell.setCellValue("Test Value"); cell = getCell(sheet, 2, 3); setText(cell, "Test Value"); cell = getCell(sheet, 3, 4); setText(cell, "Test Value"); cell = getCell(sheet, 2, 4); setText(cell, "Test Value"); } }; excelView.setApplicationContext(webAppCtx); excelView.setUrl("template"); excelView.render(new HashMap<String, Object>(), request, response); POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray())); HSSFWorkbook wb = new HSSFWorkbook(poiFs); HSSFSheet sheet = wb.getSheet("Sheet1"); HSSFRow row = sheet.getRow(0); HSSFCell cell = row.getCell(0); assertEquals("Test Template American English", cell.getStringCellValue()); }