List of usage examples for org.apache.poi.ss.usermodel Sheet setFitToPage
void setFitToPage(boolean value);
From source file:org.h819.commons.file.excel.poi.examples.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*www.ja 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:org.jboss.dashboard.displayer.table.ExportTool.java
License:Apache License
public InputStream exportExcel(Table table) { // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters if (table == null) throw new IllegalArgumentException("Null table specified!"); int columnCount = table.getColumnCount(); int rowCount = table.getRowCount() + 1; //Include header row int row = 0;/* www . jav a2s .c o m*/ SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk Map<String, CellStyle> styles = createStyles(wb); Sheet sh = wb.createSheet("Sheet 1"); // General setup sh.setDisplayGridlines(true); sh.setPrintGridlines(false); sh.setFitToPage(true); sh.setHorizontallyCenter(true); PrintSetup printSetup = sh.getPrintSetup(); printSetup.setLandscape(true); // Create header Row header = sh.createRow(row++); header.setHeightInPoints(20f); for (int i = 0; i < columnCount; i++) { Cell cell = header.createCell(i); cell.setCellStyle(styles.get("header")); cell.setCellValue(table.getColumnName(i)); } // Create data rows for (; row < rowCount; row++) { Row _row = sh.createRow(row); for (int cellnum = 0; cellnum < columnCount; cellnum++) { Cell cell = _row.createCell(cellnum); Object value = table.getValueAt(row - 1, cellnum); if (value instanceof Short || value instanceof Long || value instanceof Integer || value instanceof BigInteger) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(styles.get("integer_number_cell")); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(styles.get("decimal_number_cell")); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("date_cell")); cell.setCellValue((Date) value); } else if (value instanceof Interval) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(((Interval) value).getDescription(LocaleManager.currentLocale())); } else if (value == null) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(""); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(value.toString()); } } } // Adjust column size for (int i = 0; i < columnCount; i++) { sh.autoSizeColumn(i); } ByteArrayInputStream bis = null; try { ByteArrayOutputStream bos = new ByteArrayOutputStream(); wb.write(bos); bis = new ByteArrayInputStream(bos.toByteArray()); bos.close(); } catch (IOException e) { log.error("Data export error: ", e); } // Dispose of temporary files backing this workbook on disk if (!wb.dispose()) log.warn("Could not dispose of temporary file associated to data export!"); return bis; }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a data excel worksheet for attendee's informaiton *///w w w . j a v a 2 s . co 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 *//* w w w .j av a 2 s . 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 *//*w w w . j av a 2 s.c om*/ 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 ww . j a va 2 s .c om 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);/* ww w . j a v a2 s. c om*/ 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:project1.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from www . ja va 2 s. com // 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;// w w w.j a v a2s. com 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(); }
From source file:sample.poi.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); SimpleDateFormat formatFile = new SimpleDateFormat("dd-MM-yyyy-hh-mm-ss"); HSSFWorkbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month Sheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);//from w ww .ja v a 2s .c om 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(80); Cell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + "2013"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles Row monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); Cell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file String file = "calendar.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }