Java Code Examples for org.apache.poi.ss.usermodel.Row#setHeightInPoints()

/**
 * 复制行高
 * 
 * @author      ZhengWei(HY)
 * @createDate  2020-05-29
 * @version     v1.0
 *
 * @param i_FromRow
 * @param io_ToRow
 */
public final static void copyRowHeight(Row i_FromRow ,Row io_ToRow)
{
    if ( i_FromRow instanceof HSSFRow )
    {
        io_ToRow.setHeight(        ((HSSFRow)i_FromRow).getHeight());
        io_ToRow.setHeightInPoints(((HSSFRow)i_FromRow).getHeightInPoints());
        io_ToRow.setZeroHeight(    ((HSSFRow)i_FromRow).getZeroHeight());
    }
    else if ( i_FromRow instanceof SXSSFRow )
    {
        io_ToRow.setHeight(        ((SXSSFRow)i_FromRow).getHeight());
        io_ToRow.setHeightInPoints(((SXSSFRow)i_FromRow).getHeightInPoints());
        io_ToRow.setZeroHeight(    ((SXSSFRow)i_FromRow).getZeroHeight());
    }
    else if ( i_FromRow instanceof XSSFRow )
    {
        io_ToRow.setHeight(        ((XSSFRow)i_FromRow).getHeight());
        io_ToRow.setHeightInPoints(((XSSFRow)i_FromRow).getHeightInPoints());
        io_ToRow.setZeroHeight(    ((XSSFRow)i_FromRow).getZeroHeight());
    }
}
 

@Override
public void printHeader(String... fields) {
	Row headerRow = iSheet.createRow(iRowNum++);
	
	int cellIdx = 0;
	int nrLines = 1;
	for (int idx = 0; idx < fields.length; idx++) {
		if (iHiddenColumns.contains(idx)) continue;
		Cell cell = headerRow.createCell(cellIdx++);
		cell.setCellStyle(iStyles.get("header"));
		cell.setCellValue(fields[idx]);
		if (fields[idx] != null)
			nrLines = Math.max(nrLines, fields[idx].split("\n").length);
	}
	if (nrLines > 1)
		headerRow.setHeightInPoints(nrLines * iSheet.getDefaultRowHeightInPoints() + 1f);
}
 

public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	cfSpreadSheetData	spreadsheet = null;
	int rowNo, rowHeight;
	
	/*
	 * Collect up the parameters
	 */
spreadsheet	= (cfSpreadSheetData)parameters.get(2);
rowNo				= parameters.get(1).getInt() - 1;
rowHeight		= parameters.get(0).getInt();

if ( rowNo < 0 )
	throwException(_session, "row must be 1 or greater (" + rowNo + ")");

Sheet	sheet = spreadsheet.getActiveSheet();
Row row	= sheet.getRow( rowNo );
if ( row == null )
	row	= sheet.createRow( rowNo );

row.setHeightInPoints( rowHeight );

	return cfBooleanData.TRUE;
}
 

@Override
public void printLine(String... fields) {
	int cellIdx = 0;
	Row row = iSheet.createRow(iRowNum++);
	int nrLines = 1;
	for (int idx = 0; idx < fields.length; idx++) {
		if (iHiddenColumns.contains(idx)) continue;
		if (iHiddenColumns.contains(idx)) continue;
		Cell cell = row.createCell(cellIdx ++);
		
		String f = fields[idx];
		if (f == null || f.isEmpty() || (iCheckLast && f.equals(iLastLine == null || idx >= iLastLine.length ? null : iLastLine[idx]))) f = "";
		
		boolean number = sNumber.matcher(f).matches();
		
		cell.setCellStyle(iStyles.get(number ? "number" : "plain"));
		if (f == null || f.isEmpty()) {
		} else if (number) {
			try {
				cell.setCellValue(Double.valueOf(f));
			} catch (NumberFormatException e) {
				cell.setCellValue(f);
			}
		} else {
			nrLines = Math.max(nrLines, f.split("\n").length);
			cell.setCellValue(f);
		}
	}
	if (nrLines > 1)
		row.setHeightInPoints(nrLines * iSheet.getDefaultRowHeightInPoints() + 1f);
	iLastLine = fields;
}
 

/**
 * 构建excel的表头
 * 
 * @param filename
 * @param headerList
 */
private void buildExcelHead(String filename, HSSFWorkbook workbook) {
    // Initialize
    List<String> headerList = Lists.newArrayList();
    for (Object[] os : annotationList) {
        String t = ((ExcelField) os[0]).title();
        headerList.add(t);
    }

    sheet = workbook.createSheet("导出数据");

    // Create header
    Row headerRow = sheet.createRow(rownum++);
    headerRow.setHeightInPoints(16);
    for (int i = 0; i < headerList.size(); i++) {
        Cell cell = headerRow.createCell(i);
        String[] ss = StringUtils.split(headerList.get(i), "**", 2);
        if (ss.length == 2) {
            cell.setCellValue(ss[0]);
            Comment comment = sheet.createDrawingPatriarch().createCellComment(
                    new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
        } else {
            cell.setCellValue(headerList.get(i));
        }
        sheet.autoSizeColumn(i);
    }
    for (int i = 0; i < headerList.size(); i++) {
        int colWidth = sheet.getColumnWidth(i) * 2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
    }
}
 

/**
	 * 创建工作表
	 * @param sheetName 指定Sheet名称
	 * @param title 表格标题，传“空值”，表示无标题
	 * @param headerList 表头字段设置
	 * @param headerWidthList 表头字段宽度设置
	 */
	public void createSheet(String sheetName, String title, List<String> headerList, List<Integer> headerWidthList) {
		this.sheet = wb.createSheet(StringUtils.defaultString(sheetName, StringUtils.defaultString(title, "Sheet1")));
		this.styles = createStyles(wb);
		this.rownum = 0;
		// Create title
		if (StringUtils.isNotBlank(title)){
			Row titleRow = sheet.createRow(rownum++);
			titleRow.setHeightInPoints(30);
			Cell titleCell = titleRow.createCell(0);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);
			sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
					titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
		}
		// Create header
		if (headerList == null){
			throw new ExcelException("headerList not null!");
		}
		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints(16);
		for (int i = 0; i < headerList.size(); i++) {
			Cell cell = headerRow.createCell(i);
			cell.setCellStyle(styles.get("header"));
			String[] ss = StringUtils.split(headerList.get(i), "**", 2);
			if (ss.length==2){
				cell.setCellValue(ss[0]);
				Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
						new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
				comment.setRow(cell.getRowIndex());
				comment.setColumn(cell.getColumnIndex());
				comment.setString(new XSSFRichTextString(ss[1]));
				cell.setCellComment(comment);
			}else{
				cell.setCellValue(headerList.get(i));
			}
//			sheet.autoSizeColumn(i);
		}
		boolean isDefWidth = (headerWidthList != null && headerWidthList.size() == headerList.size());
		for (int i = 0; i < headerList.size(); i++) {
			int colWidth = -1;
			if (isDefWidth){
				colWidth = headerWidthList.get(i);
			}
			if (colWidth == -1){
				colWidth = sheet.getColumnWidth(i)*2;
				colWidth = colWidth < 3000 ? 3000 : colWidth;
			}
			if (colWidth == 0){
				sheet.setColumnHidden(i, true);
			}else{
				sheet.setColumnWidth(i, colWidth);  
			}
		}
		log.debug("Create sheet {} success.", sheetName);
	}
 

@Override
public void accept(final Row row) {
    row.setHeightInPoints(this.value);
}
 

@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
    if (headRowHeight != null) {
        row.setHeightInPoints(headRowHeight);
    }
}
 

@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {
    if (contentRowHeight != null) {
        row.setHeightInPoints(contentRowHeight);
    }
}
 

/**
 * 初始化函数
 * @param title 表格标题，传“空值”，表示无标题
 * @param headerList 表头列表
 */
private void initialize(String title, List<String> headerList) {
	this.wb = new SXSSFWorkbook(500);
	this.sheet = wb.createSheet("Export");
	this.styles = createStyles(wb);
	// Create title
	if (StringUtils.isNotBlank(title)){
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints(30);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellStyle(styles.get("title"));
		titleCell.setCellValue(title);
		sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
				titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
	}
	// Create header
	if (headerList == null){
		throw new RuntimeException("headerList not null!");
	}
	Row headerRow = sheet.createRow(rownum++);
	headerRow.setHeightInPoints(16);
	for (int i = 0; i < headerList.size(); i++) {
		Cell cell = headerRow.createCell(i);
		cell.setCellStyle(styles.get("header"));
		String[] ss = StringUtils.split(headerList.get(i), "**", 2);
		if (ss.length==2){
			cell.setCellValue(ss[0]);
			Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
					new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
			comment.setString(new XSSFRichTextString(ss[1]));
			cell.setCellComment(comment);
		}else{
			cell.setCellValue(headerList.get(i));
		}
		sheet.autoSizeColumn(i);
	}
	for (int i = 0; i < headerList.size(); i++) {  
		int colWidth = sheet.getColumnWidth(i)*2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);  
	}
	log.debug("Initialize success.");
}
 

private int getTableScheduleInfoStudentsGroupBySlot(SignupMeetingWrapper wrapper, final Sheet sheet,
		int rowNumParameter, List<SignupTimeslot> tsItems) {
	int rowNum = rowNumParameter;
	if (tsItems != null) {
		rowNum++;
		for (SignupTimeslot tsItem : tsItems) {
			/* strange thing happen for hibernate, it can be null for mySql 4.x */
			if (tsItem == null) {
				continue;
			}

			Row 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 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);
		}
	}
	return rowNum;
}
 

private int getTableScheduleInfoStudentsGroupBySlot(SignupMeetingWrapper wrapper, final Sheet sheet,
		int rowNumParameter, List<SignupTimeslot> tsItems) {
	int rowNum = rowNumParameter;
	if (tsItems != null) {
		rowNum++;
		for (SignupTimeslot tsItem : tsItems) {
			/* strange thing happen for hibernate, it can be null for mySql 4.x */
			if (tsItem == null) {
				continue;
			}

			Row 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 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);
		}
	}
	return rowNum;
}