List of usage examples for org.apache.poi.hssf.usermodel HSSFRow removeCell
@Override public void removeCell(Cell cell)
From source file:citibob.reports.PoiXlsWriter.java
License:Open Source License
/** Only copies formatting from col0 to col1, non-inclusive. */ void copyRow(HSSFRow r0, HSSFRow r1, int col0, int col1) { // Clear r1// www . ja va2s.com int pcells = r1.getPhysicalNumberOfCells(); int pc = 0; for (int c = 0; pc < pcells; ++c) { HSSFCell c1 = r1.getCell((short) c); if (c1 == null) continue; ++pc; r1.removeCell(c1); } // Copy over cells from r0 pcells = r0.getPhysicalNumberOfCells(); pc = 0; for (int c = 0; pc < pcells; ++c) { HSSFCell c0 = r0.getCell((short) c); if (c0 == null) continue; ++pc; HSSFCell c1 = r1.createCell((short) c); if (c >= col0 && c < col1) copyCellFormatting(c0, c1); else copyCell(c0, c1); } }
From source file:citibob.reports.PoiXlsWriter.java
License:Open Source License
int replaceOneHolder(HSSFSheet sheet, int row, int col, Map<String, Object> models, String rsname) { // Do the replacement Object mod = (models.size() == 1 ? models.values().iterator().next() : models.get(rsname)); if (mod == null) return NOROWCHANGE; if (mod instanceof TableModel) return replaceOneHolder(sheet, row, col, (TableModel) mod); // It's just a simple item; put it in HSSFRow row0 = sheet.getRow(row); HSSFCell c0 = row0.getCell((short) col); HSSFComment comment = c0.getCellComment(); HSSFCellStyle style = c0.getCellStyle(); row0.removeCell(c0); HSSFCell c1 = row0.createCell((short) col); if (comment != null) c1.setCellComment(comment);/*from w w w .jav a 2 s. c o m*/ if (style != null) c1.setCellStyle(style); setValue(c1, mod); return NOROWCHANGE; }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
protected void cleanupCells(HSSFSheet resultSheet) { for (int i = resultSheet.getFirstRowNum(); i <= resultSheet.getLastRowNum(); i++) { HSSFRow row = resultSheet.getRow(i); if (row != null) { for (int j = 0; j < row.getLastCellNum(); j++) { HSSFCell cell = row.getCell(j); if (cell != null) { row.removeCell(cell); }/*w ww . ja v a2s . c o m*/ } } } }
From source file:include.excel_import.Outter.java
License:Open Source License
private void removeColumn(int i, HSSFSheet hssfsheet) { for (int j = 0; j < getRowCount(hssfsheet); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); HSSFCell hssfcell = hssfrow.getCell((short) i); if (hssfrow != null) hssfrow.removeCell(hssfcell); }/* w w w .j av a 2 s .c o m*/ }
From source file:org.sakaiproject.assignment.impl.BaseAssignmentService.java
License:Educational Community License
/** * Access and output the grades spreadsheet for the reference, either for an assignment or all assignments in a context. * * @param out/*from w w w. j a v a2s . c o m*/ * The outputStream to stream the grades spreadsheet into. * @param ref * The reference, either to a specific assignment, or just to an assignment context. * @return Whether the grades spreadsheet is successfully output. * @throws IdUnusedException * if there is no object with this id. * @throws PermissionException * if the current user is not allowed to access this. */ public boolean getGradesSpreadsheet(final OutputStream out, final String ref) throws IdUnusedException, PermissionException { boolean retVal = false; String typeGradesString = REF_TYPE_GRADES + Entity.SEPARATOR; String context = ref.substring(ref.indexOf(typeGradesString) + typeGradesString.length()); // get site title for display purpose String siteTitle = ""; try { Site s = SiteService.getSite(context); siteTitle = s.getTitle(); } catch (Exception e) { // ignore exception M_log.debug(this + ":getGradesSpreadsheet cannot get site context=" + context + e.getMessage()); } // does current user allowed to grade any assignment? boolean allowGradeAny = false; List assignmentsList = getListAssignmentsForContext(context); for (int iAssignment = 0; !allowGradeAny && iAssignment < assignmentsList.size(); iAssignment++) { if (allowGradeSubmission(((Assignment) assignmentsList.get(iAssignment)).getReference())) { allowGradeAny = true; } } if (!allowGradeAny) { // not permitted to download the spreadsheet return false; } else { int rowNum = 0; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(siteTitle)); // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(rb.getString("download.spreadsheet.title")); // empty line row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(""); // site title row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(rb.getString("download.spreadsheet.site") + siteTitle); // download time row = sheet.createRow(rowNum++); row.createCell(0).setCellValue( rb.getString("download.spreadsheet.date") + TimeService.newTime().toStringLocalFull()); // empty line row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(""); HSSFCellStyle style = wb.createCellStyle(); // this is the header row number int headerRowNumber = rowNum; // set up the header cells row = sheet.createRow(rowNum++); int cellNum = 0; // user enterprise id column HSSFCell cell = row.createCell(cellNum++); cell.setCellStyle(style); cell.setCellValue(rb.getString("download.spreadsheet.column.name")); // user name column cell = row.createCell(cellNum++); cell.setCellStyle(style); cell.setCellValue(rb.getString("download.spreadsheet.column.userid")); // starting from this row, going to input user data Iterator assignments = new SortedIterator(assignmentsList.iterator(), new AssignmentComparator("duedate", "true")); // site members excluding those who can add assignments List members = new ArrayList(); // hashmap which stores the Excel row number for particular user HashMap user_row = new HashMap(); List allowAddAnySubmissionUsers = allowAddAnySubmissionUsers(context); for (Iterator iUserIds = new SortedIterator(allowAddAnySubmissionUsers.iterator(), new AssignmentComparator("sortname", "true")); iUserIds.hasNext();) { String userId = (String) iUserIds.next(); try { User u = UserDirectoryService.getUser(userId); members.add(u); // create the column for user first row = sheet.createRow(rowNum); // update user_row Hashtable user_row.put(u.getId(), Integer.valueOf(rowNum)); // increase row rowNum++; // put user displayid and sortname in the first two cells cellNum = 0; row.createCell(cellNum++).setCellValue(u.getSortName()); row.createCell(cellNum).setCellValue(u.getDisplayId()); } catch (Exception e) { M_log.warn(" getGradesSpreadSheet " + e.getMessage() + " userId = " + userId); } } int index = 0; // the grade data portion starts from the third column, since the first two are used for user's display id and sort name while (assignments.hasNext()) { Assignment a = (Assignment) assignments.next(); int assignmentType = a.getContent().getTypeOfGrade(); // for column header, check allow grade permission based on each assignment if (!a.getDraft() && allowGradeSubmission(a.getReference())) { // put in assignment title as the column header rowNum = headerRowNumber; row = sheet.getRow(rowNum++); cellNum = (index + 2); cell = row.createCell(cellNum); // since the first two column is taken by student id and name cell.setCellStyle(style); cell.setCellValue(a.getTitle()); for (int loopNum = 0; loopNum < members.size(); loopNum++) { // prepopulate the column with the "no submission" string row = sheet.getRow(rowNum++); cell = row.createCell(cellNum); cell.setCellType(1); cell.setCellValue(rb.getString("listsub.nosub")); } // begin to populate the column for this assignment, iterating through student list for (Iterator sIterator = getSubmissions(a).iterator(); sIterator.hasNext();) { AssignmentSubmission submission = (AssignmentSubmission) sIterator.next(); String userId = submission.getSubmitterId(); if (a.isGroup()) { User[] _users = submission.getSubmitters(); for (int i = 0; _users != null && i < _users.length; i++) { userId = _users[i].getId(); if (user_row.containsKey(userId)) { // find right row row = sheet.getRow(((Integer) user_row.get(userId)).intValue()); if (submission.getGraded() && submission.getGrade() != null) { // graded and released if (assignmentType == 3) { try { // numeric cell type? String grade = submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId); //We get float number no matter the locale it was managed with. NumberFormat nbFormat = FormattedText.getNumberFormat(1, 1, null); float f = nbFormat.parse(grade).floatValue(); // remove the String-based cell first cell = row.getCell(cellNum); row.removeCell(cell); // add number based cell cell = row.createCell(cellNum); cell.setCellType(0); cell.setCellValue(f); style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("#,##0.0")); cell.setCellStyle(style); } catch (Exception e) { // if the grade is not numeric, let's make it as String type // No need to remove the cell and create a new one, as the existing one is String type. cell = row.getCell(cellNum); cell.setCellType(1); cell.setCellValue(submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId)); } } else { // String cell type cell = row.getCell(cellNum); cell.setCellValue(submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId)); } } else if (submission.getSubmitted() && submission.getTimeSubmitted() != null) { // submitted, but no grade available yet cell = row.getCell(cellNum); cell.setCellValue(rb.getString("gen.nograd")); } } // if } } else { if (user_row.containsKey(userId)) { // find right row row = sheet.getRow(((Integer) user_row.get(userId)).intValue()); if (submission.getGraded() && submission.getGrade() != null) { // graded and released if (assignmentType == 3) { try { // numeric cell type? String grade = submission.getGradeDisplay(); //We get float number no matter the locale it was managed with. NumberFormat nbFormat = FormattedText.getNumberFormat(1, 1, null); float f = nbFormat.parse(grade).floatValue(); // remove the String-based cell first cell = row.getCell(cellNum); row.removeCell(cell); // add number based cell cell = row.createCell(cellNum); cell.setCellType(0); cell.setCellValue(f); style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("#,##0.0")); cell.setCellStyle(style); } catch (Exception e) { // if the grade is not numeric, let's make it as String type // No need to remove the cell and create a new one, as the existing one is String type. cell = row.getCell(cellNum); cell.setCellType(1); // Setting grade display instead grade. cell.setCellValue(submission.getGradeDisplay()); } } else { // String cell type cell = row.getCell(cellNum); cell.setCellValue(submission.getGradeDisplay()); } } else if (submission.getSubmitted() && submission.getTimeSubmitted() != null) { // submitted, but no grade available yet cell = row.getCell(cellNum); cell.setCellValue(rb.getString("gen.nograd")); } } // if } } } index++; } // output try { wb.write(out); retVal = true; } catch (IOException e) { M_log.warn(" getGradesSpreadsheet Can not output the grade spread sheet for reference= " + ref); } return retVal; } }
From source file:uk.ac.manchester.cs.owl.semspreadsheets.model.hssf.impl.WorkbookHSSFImpl.java
License:BSD License
public void visit(SetCellValue setCellValue) { HSSFSheet hssfSheet = workbook.getSheet(setCellValue.getSheet().getName()); HSSFRow hssfRow = hssfSheet.getRow(setCellValue.getRow()); if (hssfRow == null && setCellValue.getNewValue() != null) { hssfRow = hssfSheet.createRow(setCellValue.getRow()); }/*w w w . j ava2 s. c o m*/ HSSFCell hssfCell = hssfRow.getCell(setCellValue.getCol()); if (hssfCell == null && setCellValue.getNewValue() != null) { hssfCell = hssfRow.createCell(setCellValue.getCol()); } if (hssfCell != null) { if (setCellValue.getNewValue() != null) { hssfCell.setCellValue(new HSSFRichTextString(setCellValue.getNewValue().toString())); } else { hssfRow.removeCell(hssfCell); } } }