List of usage examples for org.apache.poi.ss.usermodel Sheet getPrintSetup
PrintSetup getPrintSetup();
From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java
License:Apache License
@Override public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle, boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor, MethodExpression postProcessor, boolean subTable) throws IOException { wb = new XSSFWorkbook(); String safeName = WorkbookUtil.createSafeSheetName(filename); Sheet sheet = wb.createSheet(safeName); cellStyle = wb.createCellStyle();/* w ww . j a v a 2 s . c om*/ facetStyle = wb.createCellStyle(); titleStyle = wb.createCellStyle(); facetStyleLeftAlign = wb.createCellStyle(); facetStyleCenterAlign = wb.createCellStyle(); facetStyleRightAlign = wb.createCellStyle(); cellStyleLeftAlign = wb.createCellStyle(); cellStyleCenterAlign = wb.createCellStyle(); cellStyleRightAlign = wb.createCellStyle(); createCustomFonts(); int maxColumns = 0; StringTokenizer st = new StringTokenizer(tableId, ","); while (st.hasMoreElements()) { String tableName = (String) st.nextElement(); UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(), tableName); if (component == null) { throw new FacesException("Cannot find component \"" + tableName + "\" in view."); } if (!(component instanceof DataTable || component instanceof DataList)) { throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName() + "\", exporter must target a PrimeFaces DataTable/DataList."); } DataList list = null; DataTable table = null; int cols = 0; if (preProcessor != null) { preProcessor.invoke(context.getELContext(), new Object[] { wb }); } if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) { Row titleRow = sheet.createRow(sheet.getLastRowNum()); int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum(); Cell cell = titleRow.createCell(cellIndex); cell.setCellValue(new XSSFRichTextString(tableTitle)); Font titleFont = wb.createFont(); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); titleStyle.setFont(titleFont); cell.setCellStyle(titleStyle); sheet.createRow(sheet.getLastRowNum() + 3); } if (component instanceof DataList) { list = (DataList) component; if (list.getHeader() != null) { tableFacet(context, sheet, list, "header"); } if (pageOnly) { exportPageOnly(context, list, sheet); } else { exportAll(context, list, sheet); } cols = list.getRowCount(); } else { table = (DataTable) component; int columnsCount = getColumnsCount(table); if (table.getHeader() != null && !subTable) { tableFacet(context, sheet, table, columnsCount, "header"); } if (!subTable) { tableColumnGroup(sheet, table, "header"); } addColumnFacets(table, sheet, ColumnType.HEADER); if (pageOnly) { exportPageOnly(context, table, sheet); } else if (selectionOnly) { exportSelectionOnly(context, table, sheet); } else { exportAll(context, table, sheet, subTable); } if (table.hasFooterColumn() && !subTable) { addColumnFacets(table, sheet, ColumnType.FOOTER); } if (!subTable) { tableColumnGroup(sheet, table, "footer"); } table.setRowIndex(-1); if (postProcessor != null) { postProcessor.invoke(context.getELContext(), new Object[] { wb }); } cols = table.getColumnsCount(); if (maxColumns < cols) { maxColumns = cols; } } sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding)); } if (!subTable) for (int i = 0; i < maxColumns; i++) { sheet.autoSizeColumn((short) i); } PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE); sheet.setPrintGridlines(true); writeExcelToResponse(context.getExternalContext(), wb, filename); }
From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java
License:Apache License
@Override public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle, boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor, MethodExpression postProcessor, boolean subTable) throws IOException { wb = new XSSFWorkbook(); String safeName = WorkbookUtil.createSafeSheetName(filename); Sheet sheet = wb.createSheet(safeName); cellStyle = wb.createCellStyle();/*from w ww . ja v a 2 s .c om*/ facetStyle = wb.createCellStyle(); titleStyle = wb.createCellStyle(); createCustomFonts(); int maxColumns = 0; StringTokenizer st = new StringTokenizer(tableId, ","); while (st.hasMoreElements()) { String tableName = (String) st.nextElement(); UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(), tableName); if (component == null) { throw new FacesException("Cannot find component \"" + tableName + "\" in view."); } if (!(component instanceof DataTable || component instanceof DataList)) { throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName() + "\", exporter must target a PrimeFaces DataTable/DataList."); } DataList list = null; DataTable table = null; int cols = 0; if (preProcessor != null) { preProcessor.invoke(context.getELContext(), new Object[] { wb }); } if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) { Row titleRow = sheet.createRow(sheet.getLastRowNum()); int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum(); Cell cell = titleRow.createCell(cellIndex); cell.setCellValue(new XSSFRichTextString(tableTitle)); Font titleFont = wb.createFont(); titleFont.setBold(true); titleStyle.setFont(titleFont); cell.setCellStyle(titleStyle); sheet.createRow(sheet.getLastRowNum() + 3); } if (component instanceof DataList) { list = (DataList) component; if (list.getHeader() != null) { tableFacet(context, sheet, list, "header"); } if (pageOnly) { exportPageOnly(context, list, sheet); } else { exportAll(context, list, sheet); } cols = list.getRowCount(); } else { table = (DataTable) component; int columnsCount = getColumnsCount(table); if (table.getHeader() != null && !subTable) { tableFacet(context, sheet, table, columnsCount, "header"); } if (!subTable) { tableColumnGroup(sheet, table, "header"); } addColumnFacets(table, sheet, ColumnType.HEADER); if (pageOnly) { exportPageOnly(context, table, sheet); } else if (selectionOnly) { exportSelectionOnly(context, table, sheet); } else { exportAll(context, table, sheet, subTable); } if (table.hasFooterColumn() && !subTable) { addColumnFacets(table, sheet, ColumnType.FOOTER); } if (!subTable) { tableColumnGroup(sheet, table, "footer"); } table.setRowIndex(-1); if (postProcessor != null) { postProcessor.invoke(context.getELContext(), new Object[] { wb }); } cols = table.getColumnsCount(); if (maxColumns < cols) { maxColumns = cols; } } sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding)); } if (!subTable) { for (int i = 0; i < maxColumns; i++) { sheet.autoSizeColumn((short) i); } } PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE); sheet.setPrintGridlines(true); writeExcelToResponse(context.getExternalContext(), wb, filename); }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a data excel worksheet for attendee's informaiton *///from w ww .j ava 2 s . c o m private Workbook createAttendeeDataWorksheet(List<SignupMeetingWrapper> wrappers) { String eventTitle = rb.getString("sheet_name_Attendee_schedules", "Attendees' Schedules"); Sheet sheet = wb.createSheet(eventTitle); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); /* Define column numbers and width here */ int numberOfColumn = 13; sheet.setColumnWidth(0, 25 * 256);// event title sheet.setColumnWidth(1, 20 * 256);// attendee display name sheet.setColumnWidth(2, 20 * 256);// attendee user id sheet.setColumnWidth(3, 25 * 256);// attendee user email sheet.setColumnWidth(4, 25 * 256);// site name sheet.setColumnWidth(5, 20 * 256);// appointment start time sheet.setColumnWidth(6, 16 * 256);// duration sheet.setColumnWidth(7, 22 * 256);// #num of attendees sheet.setColumnWidth(8, 25 * 256);// #user comment sheet.setColumnWidth(9, 20 * 256);// event owner sheet.setColumnWidth(10, 20 * 256);// event location sheet.setColumnWidth(11, 20 * 256);// event category sheet.setColumnWidth(12, 20 * 256);// event start time sheet.setColumnWidth(13, 20 * 256);// duration if (wrappers == null) return wb; int rowNum = 0; Cell cell = null; Row titleRow = sheet.createRow(rowNum++); titleRow.setHeightInPoints(rowHigh); for (int i = 0; i <= numberOfColumn; i++) { titleRow.createCell(i).setCellStyle(styles.get("item_leftBold")); } int cellNum = 0; titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_meeting_name", "Event Name")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_user_name", "Attendee Name")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_user_id", "Attendee User Id")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_user_email", "Email")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_site_name", "Site Title")); titleRow.getCell(cellNum++) .setCellValue(rb.getString("wksheet_appointment_start_time", "Appointment Time")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_appointment_duration", "Duration (min)")); titleRow.getCell(cellNum++) .setCellValue(rb.getString("wksheet_num_of_attendees", "#Num Attendees in Slot")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_user_comment", "User Comment")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_organizer", "Organizer")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_location", "Location")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_category", "Category")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_meeting_start_time", "Event Start Time")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_meeting_duration", "Event Duration (min)")); for (SignupMeetingWrapper wrp : wrappers) { List<SignupTimeslot> tsItems = wrp.getMeeting().getSignupTimeSlots(); if (tsItems != null) { for (SignupTimeslot tsItem : tsItems) { /*strange thing happen for hibernate, tsItem can be null for mySql 4.x*/ List<SignupAttendee> attendees = tsItem == null ? null : getValidAttendees(tsItem.getAttendees()); if (attendees != null) { // JIRA Signup-204: do we need to do the sorting here for data sheet? //it may affect downstream data decoding by other system. for (SignupAttendee s : attendees) { s.setDisplayName(sakaiFacade.getUserDisplayLastFirstName(s.getAttendeeUserId())); } //Sorting by last-first name JIRA Signup-204 Collections.sort(attendees); for (SignupAttendee att : attendees) { Row row = sheet.createRow(rowNum++); for (int i = 0; i <= numberOfColumn; i++) { row.createCell(i).setCellStyle(styles.get("item_left")); } User attendee = sakaiFacade.getUser(att.getAttendeeUserId()); /* reset */ cellNum = 0; /* meeting title */ cell = row.getCell(cellNum++); cell.setCellValue(wrp.getMeeting().getTitle()); /* attendee name */ cell = row.getCell(cellNum++); //cell.setCellValue(attendee ==null? "--" :attendee.getDisplayName()); cell.setCellValue(attendee == null ? "--" : att.getDisplayName()); cell = row.getCell(cellNum++); cell.setCellValue(attendee == null ? "--" : attendee.getDisplayId()); cell = row.getCell(cellNum++); cell.setCellValue(attendee == null ? "--" : attendee.getEmail()); cell = row.getCell(cellNum++); cell.setCellValue(getSiteTitle(att.getSignupSiteId())); cell = row.getCell(cellNum++); cell.setCellValue(sakaiFacade.getTimeService().newTime(tsItem.getStartTime().getTime()) .toStringLocalFull()); cell = row.getCell(cellNum++); cell.setCellValue(getDurationLength(tsItem.getEndTime(), tsItem.getStartTime()));// minutes cell = row.getCell(cellNum++); cell.setCellValue(getValidAttendees(tsItem.getAttendees()).size()); cell = row.getCell(cellNum++); cell.setCellValue(att.getComments()); cell = row.getCell(cellNum++); cell.setCellValue(sakaiFacade.getUserDisplayName(wrp.getMeeting().getCreatorUserId())); cell = row.getCell(cellNum++); cell.setCellValue(wrp.getMeeting().getLocation()); cell = row.getCell(cellNum++); cell.setCellValue(wrp.getMeeting().getCategory()); cell = row.getCell(cellNum++); cell.setCellValue(sakaiFacade.getTimeService() .newTime(wrp.getMeeting().getStartTime().getTime()).toStringLocalFull()); cell = row.getCell(cellNum++); cell.setCellValue(getDurationLength(wrp.getMeeting().getEndTime(), wrp.getMeeting().getStartTime())); } } } } } return wb; }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a short version excel worksheet *//*from w w w . j a v a 2s . c o m*/ private Workbook createShortVersonWorksheet(List<SignupMeetingWrapper> wrappers) { String eventTitle = rb.getString("event_overview", "Events Overview"); Sheet sheet = wb.createSheet(eventTitle); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 20 * 256); sheet.setColumnWidth(1, 15 * 256); sheet.setColumnWidth(2, 16 * 256); sheet.setColumnWidth(3, 15 * 256); sheet.setColumnWidth(4, 25 * 256); sheet.setColumnWidth(5, 19 * 256); // title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 0; i <= 6; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(0); titleCell.setCellValue(eventTitle); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); // Cureent viewer row Row row = sheet.createRow(2); row.setHeightInPoints(rowHigh); Cell cell = row.createCell(0); cell.setCellValue(rb.getString("event_viewer", "Viewer:")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(1); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getCurrentUserName()); // site title row row = sheet.createRow(3); row.setHeightInPoints(rowHigh); cell = row.createCell(0); cell.setCellValue(rb.getString("event_site_title", "Site Title:")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(1); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getCurrentSiteTitle()); // Table titles th row row = sheet.createRow(5); row.setHeightInPoints(rowHigh); for (int i = 0; i <= 6; i++) { row.createCell(i).setCellStyle(styles.get("tabColNames")); } cell = row.getCell(0); cell.setCellValue(tabTitles_shortVersion[0]); cell = row.getCell(1); cell.setCellValue(tabTitles_shortVersion[1]); cell = row.getCell(2); cell.setCellValue(tabTitles_shortVersion[2]); cell = row.getCell(3); cell.setCellValue(tabTitles_shortVersion[3]); cell = row.getCell(4); cell.setCellValue(tabTitles_shortVersion[4]); cell = row.getCell(5); cell.setCellValue(tabTitles_shortVersion[5]); cell = row.getCell(6); cell.setCellValue(tabTitles_shortVersion[6]); /* table row data */ int rowNum = 6; int seqNum = 1; for (SignupMeetingWrapper wrp : wrappers) { if (wrp.isToDownload()) { row = sheet.createRow(rowNum); int rowHighNum = 1; rowNum++; for (int i = 0; i <= 6; i++) { row.createCell(i).setCellStyle(styles.get("tabItem_fields")); } // event ttile cell = row.getCell(0); cell.setCellStyle(styles.get("item_left_wrap")); cell.setCellValue(wrp.getMeeting().getTitle()); Hyperlink sheetLink = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); String validSheetName = CreateValidWorksheetName(wrp.getMeeting().getTitle(), seqNum, true); String hlinkAddr = "'" + validSheetName + "'" + "!A1"; sheetLink.setAddress(hlinkAddr); cell.setHyperlink(sheetLink); cell.setCellStyle(styles.get("hyperLink")); seqNum++; // event owner cell = row.getCell(1); cell.setCellValue(wrp.getCreator()); // event location cell = row.getCell(2); cell.setCellValue(wrp.getMeeting().getLocation()); // event category cell = row.getCell(3); cell.setCellValue(wrp.getMeeting().getCategory()); // event Date cell = row.getCell(4); cell.setCellValue(getShortWeekDayName(wrp.getStartTime()) + ", " + getTime(wrp.getStartTime()).toStringLocalShortDate()); // event time period cell = row.getCell(5); cell.setCellValue(getMeetingPeriodShortVersion(wrp)); // event status cell = row.getCell(6); cell.setCellValue( ExcelPlainTextFormat.convertFormattedHtmlTextToExcelPlaintext(wrp.getAvailableStatus())); } } // end of table line row = sheet.createRow(rowNum); for (int i = 0; i <= 6; i++) { row.createCell(i).setCellStyle(styles.get("tab_endline")); } return wb; }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a full version excel worksheet *///from www . ja va 2 s. c o m private void createWorksheet(SignupMeetingWrapper wrapper, int serialNum, boolean hasSerialNum) { String validSheetName = CreateValidWorksheetName(wrapper.getMeeting().getTitle(), serialNum, hasSerialNum); Sheet sheet = wb.createSheet(validSheetName); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 17 * 256); sheet.setColumnWidth(3, 15 * 256); sheet.setColumnWidth(4, 22 * 256); sheet.setColumnWidth(5, 22 * 256); sheet.setColumnWidth(6, 22 * 256); // title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue(wrapper.getMeeting().getTitle()); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); // timezone row Row timezoneRow = sheet.createRow(1); timezoneRow.setHeightInPoints(16); for (int i = 1; i <= 7; i++) { timezoneRow.createCell(i).setCellStyle(styles.get("tabItem_fields")); } Cell timezoneCell = timezoneRow.getCell(2); timezoneCell.setCellValue("(" + rb.getString("event_timezone") + " " + sakaiFacade.getTimeService().getLocalTimeZone().getID() + ")"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$2:$H$2")); // owner row Row row = sheet.createRow(2); row.setHeightInPoints(rowHigh); Cell cell = row.createCell(2); cell.setCellValue(rb.getString("event_owner")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(3); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(wrapper.getCreator()); // meeting Date row row = sheet.createRow(3); row.setHeightInPoints(rowHigh); cell = row.createCell(2); cell.setCellValue(rb.getString("event_date")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(3); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getTime(wrapper.getStartTime()).toStringLocalDate()); // Time Period row row = sheet.createRow(4); row.setHeightInPoints(rowHigh); cell = row.createCell(2); cell.setCellValue(rb.getString("event_time_period")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(3); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getMeetingPeriod(wrapper.getMeeting())); // Sign-up Begins row row = sheet.createRow(5); row.setHeightInPoints(rowHigh); cell = row.createCell(2); cell.setCellValue(rb.getString("event_signup_start")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(3); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getTime(wrapper.getMeeting().getSignupBegins()).toStringLocalDate() + ", " + getTime(wrapper.getMeeting().getSignupBegins()).toStringLocalTime()); // Sign-up Ends row row = sheet.createRow(6); row.setHeightInPoints(rowHigh); cell = row.createCell(2); cell.setCellValue(rb.getString("event_signup_deadline")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(3); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getTime(wrapper.getMeeting().getSignupDeadline()).toStringLocalDate() + ", " + getTime(wrapper.getMeeting().getSignupDeadline()).toStringLocalTime()); // Available To row row = sheet.createRow(7); for (int i = 1; i <= 5; i++) { row.createCell(i); } cell = row.getCell(2); cell.setCellValue(rb.getString("event_publish_to")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.getCell(3); cell.setCellStyle(styles.get("item_left_wrap")); String availSitesGroups = getAvailableSitesGroups(wrapper.getMeeting()); cell.setCellValue(availSitesGroups); int rownum = getNumRows(availSitesGroups); row.setHeightInPoints(rowHigh * rownum); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$8:$F$8")); // Description row row = sheet.createRow(8); for (int i = 1; i <= 7; i++) { row.createCell(i);// setCellStyle(styles.get("description")); } // cell = row.createCell(2); cell = row.getCell(2); cell.setCellValue(rb.getString("event_description")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.getCell(3); cell.setCellStyle(styles.get("item_left_wrap_top")); String description = wrapper.getMeeting().getDescription(); if (description != null && description.length() > 0) { description = ExcelPlainTextFormat.convertFormattedHtmlTextToExcelPlaintext(description); row.setHeightInPoints(rowHigh * getDescRowNum(description)); } cell.setCellValue(description); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$9:$H$9")); /* add attachment links */ int cur_rowNum = 9; row = sheet.createRow(cur_rowNum); for (int i = 1; i <= 5; i++) { row.createCell(i); } row.setHeightInPoints(rowHigh); cell = row.getCell(2); cell.setCellValue(rb.getString("attachments")); cell.setCellStyle(styles.get("item_leftBold")); List<SignupAttachment> attachs = wrapper.getEventMainAttachments(); if (attachs != null && !attachs.isEmpty()) { for (int i = 0; i < attachs.size(); i++) { SignupAttachment attach = attachs.get(i); if (i > 0) {// start with second attachment cur_rowNum++; row = sheet.createRow(cur_rowNum);// create next // attachment row row.setHeightInPoints(rowHigh); for (int j = 1; j <= 5; j++) { row.createCell(j); } } cell = row.getCell(3); cell.setCellStyle(styles.get("hyperLink")); cell.setCellValue(attach.getFilename()); cell.setHyperlink(setAttachmentURLLinks(attach)); } } else { cell = row.getCell(3); cell.setCellStyle(styles.get("item_left_wrap")); cell.setCellValue(rb.getString("event_no_attachment")); } /* Case: for announcement event */ if (ANNOUNCEMENT.equals(wrapper.getMeeting().getMeetingType())) { row = sheet.createRow(cur_rowNum + 3); row.setHeightInPoints(rowHigh); cell = row.createCell(3); cell.setCellValue(rb.getString("event_is_open_session", "This is an open session meeting. No sign-up is necessary.")); cell.setCellStyle(styles.get("item_leftBold")); return; } /* Case: for group and individual events */ // Table titles row cur_rowNum = cur_rowNum + 2; row = sheet.createRow(cur_rowNum); row.setHeightInPoints(rowHigh); for (int i = 2; i <= 7; i++) { row.createCell(i).setCellStyle(styles.get("tabColNames")); } cell = row.getCell(2); currentTabTitles = isOrganizer(wrapper.getMeeting()) ? tabTitles_Organizor : tabTitles_Participant; cell.setCellValue(currentTabTitles[0]); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + (cur_rowNum + 1) + ":$D$" + (cur_rowNum + 1))); cell = row.getCell(4); cell.setCellValue(currentTabTitles[1]); cell = row.getCell(5); cell.setCellValue(currentTabTitles[2]); cell = row.getCell(6); cell.setCellValue(currentTabTitles[3]); cell = row.getCell(7); cell.setCellValue(currentTabTitles[4]); // Table schedule Info int rowNum = cur_rowNum + 1; List<SignupTimeslot> tsItems = wrapper.getMeeting().getSignupTimeSlots(); if (tsItems != null) { for (SignupTimeslot tsItem : tsItems) { /*strange thing happen for hibernate, it can be null for mySql 4.x*/ if (tsItem == null) { continue; } row = sheet.createRow(rowNum); int rowHighNum = 1; rowNum++; for (int i = 1; i <= 7; i++) { row.createCell(i).setCellStyle(styles.get("tabItem_fields")); } // timeslot period cell = row.getCell(2); cell.setCellValue(getTimeSlotPeriod(tsItem, wrapper.getMeeting().isMeetingCrossDays())); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + rowNum + ":$D$" + rowNum));// "$C$11:$D$11" // Max # of participants cell = row.getCell(4); if (tsItem.isUnlimitedAttendee()) cell.setCellValue(rb.getString("event_unlimited")); else if (isOrganizer(wrapper.getMeeting())) { cell.setCellValue(tsItem.getMaxNoOfAttendees()); } else { int availableSpots = getValidAttendees(tsItem.getAttendees()) != null ? tsItem.getMaxNoOfAttendees() - getValidAttendees(tsItem.getAttendees()).size() : tsItem.getMaxNoOfAttendees(); availableSpots = availableSpots < 1 ? 0 : availableSpots; String value = String.valueOf(availableSpots); if (tsItem.isLocked()) value = rb.getString("event_is_locked"); else if (tsItem.isCanceled()) value = rb.getString("event_is_canceled"); cell.setCellValue(value); } List<SignupAttendee> attendees = getValidAttendees(tsItem.getAttendees()); // attendee names cell = row.getCell(5); String aNames = rb.getString("event_show_no_attendee_info"); if (isDisplayNames(wrapper.getMeeting())) { if (attendees != null && attendees.size() > rowHighNum) { rowHighNum = attendees.size(); } aNames = getNames(attendees, true); } if (tsItem.isCanceled() && isOrganizer(wrapper.getMeeting())) { aNames = rb.getString("event_is_canceled"); } cell.setCellValue(aNames); cell.setCellStyle(styles.get("attendee_layout")); // attendee userids // without completely reformatting the way the table is constructed, this gives the userids in a separate column cell = row.getCell(6); String aIds = rb.getString("event_show_no_attendee_info"); if (isDisplayNames(wrapper.getMeeting())) { if (attendees != null && attendees.size() > rowHighNum) { rowHighNum = attendees.size(); } aIds = getIds(attendees); } if (tsItem.isCanceled() && isOrganizer(wrapper.getMeeting())) { aIds = rb.getString("event_is_canceled"); } cell.setCellValue(aIds); cell.setCellStyle(styles.get("attendee_layout")); // waiters cell = row.getCell(7); String fieldValue = ""; if (isOrganizer(wrapper.getMeeting())) { List<SignupAttendee> waiters = tsItem.getWaitingList(); if (waiters != null && waiters.size() > rowHighNum) { rowHighNum = waiters.size(); } fieldValue = getNames(waiters, false); } else { fieldValue = getYourStatus(tsItem); } cell.setCellValue(fieldValue); cell.setCellStyle(styles.get("attendee_layout")); // set row high row.setHeightInPoints(rowHigh * rowHighNum); } } // end of table line row = sheet.createRow(rowNum); for (int i = 2; i <= 7; i++) { row.createCell(i).setCellStyle(styles.get("tab_endline")); } /* process attendee's comments */ rowNum = rowNum + 2; // Comment Title row Row commentsRow = sheet.createRow(rowNum); commentsRow.setHeightInPoints(25); for (int i = 1; i <= 7; i++) { commentsRow.createCell(i).setCellStyle(styles.get("commentTitle")); } Cell commentsCell = commentsRow.getCell(2); commentsCell.setCellValue(rb.getString("event_comments_title", "Participant's Comments")); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + (rowNum + 1) + ":$H$" + (rowNum + 1))); // separate line rowNum++; row = sheet.createRow(rowNum); for (int i = 2; i <= 4; i++) { row.createCell(i).setCellStyle(styles.get("tab_endline")); } rowNum++; ; boolean hasComment = false; if (tsItems != null) { for (SignupTimeslot ts : tsItems) { /*strange thing happen for hibernate, it can be null for mySql 4.x*/ List<SignupAttendee> attendees = ts != null ? getValidAttendees(ts.getAttendees()) : null; if (attendees != null) { for (SignupAttendee att : attendees) { if (isOrganizer(wrapper.getMeeting()) || isViewerSelf(att)) { String comment = att.getComments(); if (comment != null && comment.trim().length() > 0) { row = sheet.createRow(rowNum++); for (int i = 1; i <= 7; i++) { row.createCell(i); } cell = row.getCell(2); cell.setCellValue(sakaiFacade.getUserDisplayName(att.getAttendeeUserId()) + ":"); cell.setCellStyle(styles.get("item_leftBold")); cell = row.getCell(3); cell.setCellStyle(styles.get("item_left_wrap_top")); comment = ExcelPlainTextFormat.convertFormattedHtmlTextToExcelPlaintext(comment); row.setHeightInPoints(rowHigh * getDescRowNum(comment)); cell.setCellValue(comment); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$" + rowNum + ":$H$" + rowNum)); rowNum++;// one row space between comment hasComment = true; } } } } } } if (!hasComment) { row = sheet.createRow(rowNum); row.createCell(2); cell = row.getCell(2); cell.setCellValue(rb.getString("event_no_comments", "There is no comments written by participants.")); cell.setCellStyle(styles.get("item_leftBold")); } }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a data excel worksheet for attendee's informaiton, which is in the gradebook import format *///w w w . j a v a 2s .com private Workbook createAttendanceDataWorksheet(List<SignupMeetingWrapper> wrappers) { String eventTitle = rb.getString("sheet_name_Attendance_data", "Attendance"); Sheet sheet = wb.createSheet(eventTitle); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //Map to store all data Map<String, List<Integer>> m = new HashMap<String, List<Integer>>(); if (wrappers == null) return wb; /* Define column numbers and width here */ int numberOfColumn = wrappers.size() + 1; sheet.setColumnWidth(0, 25 * 256);// event title int rowNum = 0; Cell cell = null; Row titleRow = sheet.createRow(rowNum++); titleRow.setHeightInPoints(rowHigh); for (int i = 0; i <= numberOfColumn; i++) { titleRow.createCell(i).setCellStyle(styles.get("item_leftBold")); } int cellNum = 0; titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_attendance_UserID", "UserID")); int index = -1; for (SignupMeetingWrapper wrp : wrappers) { if (wrp.getMeeting().isAllowAttendance()) { index++; titleRow.getCell(cellNum++).setCellValue(wrp.getMeeting().getTitle()); List<SignupTimeslot> tsItems = wrp.getMeeting().getSignupTimeSlots(); if (tsItems != null) { for (SignupTimeslot tsItem : tsItems) { /*strange thing happen for hibernate, tsItem can be null for mySql 4.x*/ List<SignupAttendee> attendees = tsItem == null ? null : getValidAttendees(tsItem.getAttendees()); if (attendees != null) { for (SignupAttendee att : attendees) { //If attended then score is 1 else 0 Integer attended = 0; if (att.isAttended()) { attended = 1; } User attendee = sakaiFacade.getUser(att.getAttendeeUserId()); String attendeeEID = attendee.getEid(); if (m.containsKey(attendeeEID)) { //Integer value=m.get(attendeeEID).get(index); List<Integer> attendanceList = m.get(attendeeEID); if (attendanceList.get(index) != null) { attendanceList.set(index, attendanceList.get(index) + attended); } else { attendanceList.set(index, attended); } } else { List<Integer> newList = Arrays.asList(new Integer[wrappers.size()]); newList.set(index, attended); m.put(attendeeEID, newList); } } } //Recording attendance for waitlisted user Signup-106 List<SignupAttendee> waitingList = tsItem == null ? null : tsItem.getWaitingList(); if (waitingList != null) { //For waitlisted user, if added it is scored, if not ignored Integer attended = 1; for (SignupAttendee wt : waitingList) { if (wt.isAttended()) { User attendee = sakaiFacade.getUser(wt.getAttendeeUserId()); String attendeeEID = attendee.getEid(); if (m.containsKey(attendeeEID)) { //Integer value=m.get(attendeeEID).get(index); List<Integer> attendanceList = m.get(attendeeEID); if (attendanceList.get(index) != null) { attendanceList.set(index, attendanceList.get(index) + attended); } else { attendanceList.set(index, attended); } } else { List<Integer> newList = Arrays.asList(new Integer[wrappers.size()]); newList.set(index, attended); m.put(attendeeEID, newList); } } } } } } } } //populate the map into workbook Iterator it = m.entrySet().iterator(); while (it.hasNext()) { //key value pair of Map Map.Entry pairs = (Map.Entry) it.next(); //create new row Row row = sheet.createRow(rowNum++); for (int i = 0; i <= numberOfColumn; i++) { row.createCell(i).setCellStyle(styles.get("item_left")); } cellNum = 0; row.getCell(cellNum++).setCellValue((String) pairs.getKey()); List<Integer> attendanceList = (List) pairs.getValue(); for (int i = 0; i < attendanceList.size(); i++) { if (attendanceList.get(i) != null) { row.getCell(cellNum++).setCellValue(attendanceList.get(i)); } else { //empty value if null row.getCell(cellNum++).setCellValue(""); } } } return wb; }
From source file:output.ExcelM3Upgrad.java
private void writeMigration() { Sheet sheet = workbook.getSheetAt(0); workbook.setSheetName(0, "Migration"); sheet.setDisplayGridlines(false);//from w ww .j ava2s. c o m sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); styles = createStyles(workbook); int rownum = beginROW; int cellnum = beginCOL; Row row = sheet.createRow(rownum++); for (int k = 0; k < model.getListColumn().length; k++) { Cell cell = row.createCell(cellnum++); cell.setCellValue(i18n.Language.getLabel(model.getListColumn()[k].getIdLng())); cell.setCellStyle(styles.get("header")); sheet.setColumnHidden(cell.getColumnIndex(), model.getListColumn()[k].isHidden()); sheet.autoSizeColumn(k); dialStatus(); } ArrayList<Integer> listHeader = new ArrayList<>(); for (int i = 0; i < M3UpdObjModel.header.length; i++) { listHeader.add(M3UpdObjModel.header[i]); } String[] listLevel = i18n.Language.traduce(Ressource.listLevel) .toArray(new String[Ressource.listLevel.length]); data = model.getData(); for (int i = 0; i < data.length; i++) { busyDial.setText("Alimentation de la ligne " + (i + 1) + " sur " + data.length); row = sheet.createRow(rownum++); Object[] objArr = data[i]; cellnum = beginCOL; boolean first = true; int j = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Boolean) { if (first) { first = false; if ((Boolean) obj) { cell.setCellValue("Oui"); } else { cell.setCellValue("Non"); } } else { if ((Boolean) obj) { cell.setCellValue("OK"); } else { cell.setCellValue("KO"); } } } else if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } if (listHeader.indexOf(218) == j) { try { int n = Integer.parseInt(obj.toString().trim()); if (n == -1) { cell.setCellValue("ERROR"); } else { cell.setCellValue(listLevel[n]); } } catch (NumberFormatException ex) { cell.setCellValue(""); } } if (j < objArr.length - 3) { cell.setCellStyle(styles.get("cell_b_centered_locked")); } else { cell.setCellStyle(styles.get("cell_b_centered")); } j++; dialStatus(); } dialStatus(); } dialStatus(); busyDial.setText("Formatage du document"); CellRangeAddressList userList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 1, beginCOL + data[0].length - 1); DataValidationConstraint userConstraint; DataValidation userValidation; if (type == 0) { userConstraint = DVConstraint.createExplicitListConstraint((String[]) model.getM3UserModel() .getListUserSelect().toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = new HSSFDataValidation(userList, userConstraint); } else { XSSFDataValidationHelper userHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); userConstraint = (XSSFDataValidationConstraint) userHelper .createExplicitListConstraint((String[]) model.getM3UserModel().getListUserSelect() .toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = (XSSFDataValidation) userHelper.createValidation(userConstraint, userList); } sheet.addValidationData(userValidation); CellRangeAddressList migList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 2, beginCOL + data[0].length - 2); DataValidationConstraint migConstraint; DataValidation migValidation; if (type == 0) { migConstraint = DVConstraint.createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = new HSSFDataValidation(migList, migConstraint); } else { XSSFDataValidationHelper migHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); migConstraint = (XSSFDataValidationConstraint) migHelper .createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = (XSSFDataValidation) migHelper.createValidation(migConstraint, migList); } sheet.addValidationData(migValidation); CellRangeAddressList levelList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 3, beginCOL + data[0].length - 3); DataValidationConstraint levelConstraint; DataValidation levelValidation; ArrayList<String> listNameLevel = new ArrayList<>(); listNameLevel.add("ERROR"); listNameLevel.addAll(i18n.Language.traduce(Ressource.listLevel));//.toArray(new String[Ressource.listLevel.length]) if (type == 0) { levelConstraint = DVConstraint .createExplicitListConstraint(listNameLevel.toArray(new String[listNameLevel.size()])); levelValidation = new HSSFDataValidation(levelList, levelConstraint); } else { XSSFDataValidationHelper levelHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); levelConstraint = (XSSFDataValidationConstraint) levelHelper.createExplicitListConstraint( i18n.Language.traduce(Ressource.listLevel).toArray(new String[Ressource.listLevel.length])); levelValidation = (XSSFDataValidation) levelHelper.createValidation(levelConstraint, levelList); } sheet.addValidationData(levelValidation); int irow = beginROW; int icol = beginCOL + model.getListColumn().length + 2; row = sheet.getRow(irow); Cell cell = row.createCell(icol); sheet.addMergedRegion(new CellRangeAddress(irow, irow, icol, icol + 1)); cell.setCellValue("Estimation de la charge"); cell.setCellStyle(styles.get("header")); irow++; row = sheet.getRow(irow); int cpt = 0; ArrayList<String> listStringLevel = i18n.Language.traduce(Ressource.listLevel); for (String s : listStringLevel) { cell = row.createCell(icol); cell.setCellValue(s); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(218) + beginCOL); cell.setCellFormula( "COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + s + "\")*" + Ressource.listWeightLevel[cpt]); cell.setCellStyle(styles.get("cell_b_centered_locked")); irow++; row = sheet.getRow(irow); cpt++; } row = sheet.getRow(irow); cell = row.createCell(icol); cell.setCellValue("Total des charges"); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(icol + 1)); cell.setCellFormula("SUM(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + Ressource.listLevel.length + 1) + ")"); cell.setCellStyle(styles.get("cell_b_centered_locked")); for (int k = 0; k < model.getListColumn().length + 3; k++) { sheet.autoSizeColumn(k); } sheet.protectSheet("3kles2014"); }
From source file:packtest.FitSheetToOnePage.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true);//from w w w . ja v a 2 s . c o m ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); // Create various cells and rows for spreadsheet. FileOutputStream fileOut = new FileOutputStream("fitSheetToOnePage.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:project1.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*w w w. j a v a 2 s . c om*/ // if(args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); // else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } //row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); //set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } //finally set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 30 * 256); //30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); //6 characters wide } sheet.setColumnWidth(10, 10 * 256); //10 characters wide // Write the output to a file String file = "timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:ro.dabuno.office.integration.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*from w w w . j ava2s.c om*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(75); //75% scale // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); wb.close(); }