Java Code Examples for org.apache.poi.ss.usermodel.Sheet#addMergedRegion()
The following examples show how to use
org.apache.poi.ss.usermodel.Sheet#addMergedRegion() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
| Source File: POIUtils.java From xlsmapper with Apache License 2.0 | 6 votes |
|
/**
* 指定した範囲のセルを結合する。
* @param sheet
* @param startCol
* @param startRow
* @param endCol
* @param endRow
* @return 結合した範囲のアドレス情報
* @throws IllegalArgumentException {@literal sheet == null}
*/
public static CellRangeAddress mergeCells(final Sheet sheet, int startCol, int startRow, int endCol, int endRow) {
ArgUtils.notNull(sheet, "sheet");
// 結合先のセルの値を空に設定する
for(int r=startRow; r <= endRow; r++) {
for(int c=startCol; c <= endCol; c++) {
if(r == startRow && c == startCol) {
continue;
}
Cell cell = getCell(sheet, c, r);
cell.setCellType(CellType.BLANK);
}
}
final CellRangeAddress range = new CellRangeAddress(startRow, endRow, startCol, endCol);
sheet.addMergedRegion(range);
return range;
}
Example 2
| Source File: ExcelExportServer.java From easypoi with Apache License 2.0 | 6 votes |
|
/**
* 创建 表头改变
*
* @param entity
* @param sheet
* @param workbook
* @param feildWidth
*/
public int createHeaderRow(ExportParams entity, Sheet sheet, Workbook workbook, int feildWidth) {
Row row = sheet.createRow(0);
row.setHeight(entity.getTitleHeight());
createStringCell(row, 0, entity.getTitle(),
getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
for (int i = 1; i <= feildWidth; i++) {
createStringCell(row, i, "",
getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
}
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, feildWidth));
if (entity.getSecondTitle() != null) {
row = sheet.createRow(1);
row.setHeight(entity.getSecondTitleHeight());
CellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
createStringCell(row, 0, entity.getSecondTitle(), style, null);
for (int i = 1; i <= feildWidth; i++) {
createStringCell(row, i, "",
getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
}
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, feildWidth));
return 2;
}
return 1;
}
Example 3
| Source File: ExcelWriter.java From azeroth with Apache License 2.0 | 6 votes |
|
/**
* 合并行
*/
//TODO 暂时支持两行表头
private void mergeRows(Sheet sheet, CellStyle cellStyle, ExcelMeta excelMeta) {
Row row = null;
Cell cell = null;
String[] lastRowVals = new String[excelMeta.getTitleColumnNum()];
for (int r = 0; r < excelMeta.getTitleRowNum(); r++) {
for (int c = 0; c < excelMeta.getTitleColumnNum(); c++) {
row = sheet.getRow(r);
cell = row.getCell(c);
if (r == 0) {
lastRowVals[c] = cell.getStringCellValue();
} else {
if (StringUtils.equals(lastRowVals[c], cell.getStringCellValue())) {
cell.setCellValue("");
sheet.addMergedRegion(new CellRangeAddress(0, r, c, c));
Cell nowCell = sheet.getRow(0).getCell(c);
nowCell.setCellStyle(cellStyle);
}
}
}
}
}
Example 4
| Source File: ExcelWriter.java From jeesuite-libs with Apache License 2.0 | 6 votes |
|
/**
* 合并行
*/
//TODO 暂时支持两行表头
private void mergeRows(Sheet sheet,CellStyle cellStyle,ExcelMeta excelMeta) {
Row row = null;
Cell cell = null;
String[] lastRowVals = new String[excelMeta.getTitleColumnNum()];
for (int r = 0; r < excelMeta.getTitleRowNum(); r++) {
for (int c = 0; c < excelMeta.getTitleColumnNum(); c++) {
row = sheet.getRow(r);
cell = row.getCell(c);
if(r == 0){
lastRowVals[c] = cell.getStringCellValue();
}else{
if(StringUtils.equals(lastRowVals[c],cell.getStringCellValue())){
cell.setCellValue("");
sheet.addMergedRegion(new CellRangeAddress(0, r, c, c));
Cell nowCell = sheet.getRow(0).getCell(c);
nowCell.setCellStyle(cellStyle);
}
}
}
}
}
Example 5
| Source File: ExcelHelp.java From hy.common.report with Apache License 2.0 | 6 votes |
|
/**
* 合并单元格
*
* @author ZhengWei(HY)
* @createDate 2017-06-21
* @version v1.0
*
* @param i_Sheet 工作表
* @param i_FirstRow 首行
* @param i_LastRow 尾行
* @param i_FirstColumn 首列
* @param i_LastColumn 尾列
* @param i_IsSafe 是要安全？还是要性能
*/
public final static void addMergedRegions(Sheet i_Sheet ,int i_FirstRow ,int i_LastRow ,int i_FirstColumn ,int i_LastColumn ,boolean i_IsSafe)
{
if ( i_FirstRow == i_LastRow && i_FirstColumn == i_LastColumn )
{
return;
}
CellRangeAddress v_CellRA = new CellRangeAddress(i_FirstRow
,i_LastRow
,i_FirstColumn
,i_LastColumn);
if ( i_IsSafe )
{
i_Sheet.addMergedRegion(v_CellRA);
}
else
{
i_Sheet.addMergedRegionUnsafe(v_CellRA);
}
}
Example 6
| Source File: ExcelHelp.java From hy.common.report with Apache License 2.0 | 5 votes |
|
/**
* 合并单元格
*
* @author ZhengWei(HY)
* @createDate 2017-06-21
* @version v1.0
*
* @param i_Sheet 工作表
* @param i_FirstRow 首行
* @param i_LastRow 尾行
* @param i_FirstColumn 首列
* @param i_LastColumn 尾列
* @param i_IsSafe 是要安全？还是要性能
*/
public final static void addMergedRegionsSafe(Sheet i_Sheet ,int i_FirstRow ,int i_LastRow ,int i_FirstColumn ,int i_LastColumn)
{
if ( i_FirstRow == i_LastRow && i_FirstColumn == i_LastColumn )
{
return;
}
CellRangeAddress v_CellRA = new CellRangeAddress(i_FirstRow
,i_LastRow
,i_FirstColumn
,i_LastColumn);
i_Sheet.addMergedRegion(v_CellRA);
}
Example 7
| Source File: HorizontalRecordsProcessor.java From xlsmapper with Apache License 2.0 | 5 votes |
|
/**
* ネストしたレコードの親のセルを結合する
* @param sheet シート
* @param mergedSize 結合するセルのサイズ
* @param valueCellPositions 結合する開始位置のセルのアドレス
*/
private void processSavingNestedMergedRecord(final Sheet sheet, final int mergedSize,
final List<CellPosition> valueCellPositions) {
if(mergedSize <= 1) {
return;
}
// ネストした場合、上のセルのスタイルをコピーして、結合する
for(CellPosition position : valueCellPositions) {
Cell valueCell = POIUtils.getCell(sheet, position);
if(valueCell == null) {
continue;
}
final CellStyle style = valueCell.getCellStyle();
// 結合するセルに対して、上のセルのスタイルをコピーする。
// 行を挿入するときなどに必要になるため、スタイルを設定する。
for(int i=1; i < mergedSize; i++) {
Cell mergedCell = POIUtils.getCell(sheet, position.getColumn(), position.getRow() + i);
mergedCell.setCellStyle(style);
mergedCell.setCellType(CellType.BLANK);
}
final CellRangeAddress range = new CellRangeAddress(position.getRow(), position.getRow()+ mergedSize-1,
position.getColumn(), position.getColumn());
// 既に結合済みのセルがある場合、外す。
for(int rowIdx=range.getFirstRow(); rowIdx <= range.getLastRow(); rowIdx++) {
CellRangeAddress r = POIUtils.getMergedRegion(sheet, rowIdx, position.getColumn());
if(r != null) {
POIUtils.removeMergedRange(sheet, r);
}
}
sheet.addMergedRegion(range);
}
}
Example 8
| Source File: ExcelExportServer.java From jeasypoi with Apache License 2.0 | 5 votes |
|
/**
* 创建表头
*
* @param title
* @param index
*/
private int createTitleRow(ExportParams title, Sheet sheet, Workbook workbook, int index, List<ExcelExportEntity> excelParams) {
Row row = sheet.createRow(index);
int rows = getRowNums(excelParams);
row.setHeight((short) 450);
Row listRow = null;
if (rows == 2) {
listRow = sheet.createRow(index + 1);
listRow.setHeight((short) 450);
}
int cellIndex = 0;
CellStyle titleStyle = getExcelExportStyler().getTitleStyle(title.getColor());
for (int i = 0, exportFieldTitleSize = excelParams.size(); i < exportFieldTitleSize; i++) {
ExcelExportEntity entity = excelParams.get(i);
if (StringUtils.isNotBlank(entity.getName())) {
createStringCell(row, cellIndex, entity.getName(), titleStyle, entity);
}
if (entity.getList() != null) {
List<ExcelExportEntity> sTitel = entity.getList();
if (StringUtils.isNotBlank(entity.getName())) {
sheet.addMergedRegion(new CellRangeAddress(index, index, cellIndex, cellIndex + sTitel.size() - 1));
}
for (int j = 0, size = sTitel.size(); j < size; j++) {
createStringCell(rows == 2 ? listRow : row, cellIndex, sTitel.get(j).getName(), titleStyle, entity);
cellIndex++;
}
cellIndex--;
} else if (rows == 2) {
createStringCell(listRow, cellIndex, "", titleStyle, entity);
sheet.addMergedRegion(new CellRangeAddress(index, index + 1, cellIndex, cellIndex));
}
cellIndex++;
}
return rows;
}
Example 9
| Source File: ExcelCellMergerUnitTest.java From tutorials with MIT License | 5 votes |
|
@Test
public void givenCellRefString_whenAddMergeRegion_thenMergeRegionCreated() throws IOException {
Workbook workbook = new XSSFWorkbook(fileLocation);
Sheet sheet = workbook.getSheetAt(0);
assertEquals(0, sheet.getNumMergedRegions());
sheet.addMergedRegion(CellRangeAddress.valueOf("A1:C1"));
assertEquals(1, sheet.getNumMergedRegions());
workbook.close();
}
Example 10
| Source File: CellUtils.java From Octopus with MIT License | 5 votes |
|
public static void setMergeRegion(Sheet sheet, int row, int lastRow, int col, int lastCol, CellStyle cellStyle) {
int i = sheet.addMergedRegion(new CellRangeAddress(row, lastRow, col, lastCol));
/**
* seems like a bug
*/
CellRangeAddress region = sheet.getMergedRegion(sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet ? i - 1 : i);
RegionUtil.setBorderTop(cellStyle.getBorderTopEnum(), region, sheet);
RegionUtil.setBorderLeft(cellStyle.getBorderLeftEnum(), region, sheet);
RegionUtil.setBorderBottom(cellStyle.getBorderBottomEnum(), region, sheet);
RegionUtil.setBorderRight(cellStyle.getBorderRightEnum(), region, sheet);
}
Example 11
| Source File: ExcelTestHelper.java From dremio-oss with Apache License 2.0 | 4 votes |
|
private static void generateSheetData(final Sheet sheet, final CellStyle style, short startingRow) {
int currentRow = startingRow;
// Create first row values
Row row1 = sheet.createRow(currentRow++);
row1.createCell(0).setCellValue(1.0);
row1.createCell(1).setCellValue("One");
row1.createCell(2).setCellValue("One");
Cell c13 = row1.createCell(3);
c13.setCellValue(LocaleUtil.getLocaleCalendar(1983, 04/*zero based*/, 18, 4, 0, 0));
c13.setCellStyle(style);
Cell c14 = row1.createCell(4);
c14.setCellFormula("A2+1");
// For formulas we read pre-computed values. Editors set the precomputed value by default. We need to add it here
// explicitly as the library doesn't pre compute the formula value.
c14.setCellValue(2.0d);
row1.createCell(5).setCellValue(true);
row1.createCell(6).setCellFormula("B2*20");
row1.createCell(6).setCellValue("#ERROR");
// Create second row values
Row row2 = sheet.createRow(currentRow++);
row2.createCell(0).setCellValue(2.0);
row2.createCell(1).setCellValue("Two");
row2.createCell(2).setCellValue("Two");
Cell c23 = row2.createCell(3);
c23.setCellValue(LocaleUtil.getLocaleCalendar(2013, 06/*zero based*/, 05, 5, 0, 1));
c23.setCellStyle(style);
Cell c24 = row2.createCell(4);
c24.setCellFormula("A3+1");
c24.setCellValue(3.0d);
row2.createCell(5).setCellValue(false);
row2.createCell(6).setCellFormula("B3*20");
row2.createCell(6).setCellValue("#ERROR");
// Create third row values
Row row3 = sheet.createRow(currentRow++);
row3.createCell(0).setCellValue(3.0);
row3.createCell(1).setCellValue("Three and Three");
row3.createCell(5).setCellValue(false);
// Create fourth row values
Row row4 = sheet.createRow(currentRow++);
row4.createCell(0).setCellValue(4.0);
row4.createCell(1).setCellValue("Four and Four, Five and Five");
// Create fifth row values
Row row5 = sheet.createRow(currentRow++);
row5.createCell(0).setCellValue(5.0);
sheet.addMergedRegion(new CellRangeAddress(startingRow + 2, startingRow + 2, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(startingRow + 2, startingRow + 4, 5, 5));
sheet.addMergedRegion(new CellRangeAddress(startingRow + 3, startingRow + 4, 1, 2));
}
Example 12
| Source File: CrosstabXLSExporter.java From Knowage-Server with GNU Affero General Public License v3.0 | 4 votes |
|
/**
* Builds the columns' headers recursively with this order: |------------------------------------------| | 1 | 9 |
* |------------------------------------------| | 2 | 5 | 10 | |-----------|-----------------|------------| | 3 | 4 | 6 | 7 | 8 | 11 | 12 |
* |------------------------------------------|
*
* @param sheet
* The sheet of the XLS file
* @param siblings
* The siblings nodes of the headers structure
* @param rowNum
* The row number where the siblings must be inserted
* @param columnNum
* The column number where the first sibling must be inserted
* @param createHelper
* The file creation helper
* @param dimensionCellStyle
* The cell style for cells containing dimensions (i.e. attributes' names)
* @param memberCellStyle
* The cell style for cells containing members (i.e. attributes' values)
* @throws JSONException
*/
protected void buildColumnsHeader(Sheet sheet, CrossTab cs, List<Node> siblings, int rowNum, int columnNum, CreationHelper createHelper, Locale locale,
CellStyle memberCellStyle, CellStyle dimensionCellStyle) throws JSONException {
int columnCounter = columnNum;
for (int i = 0; i < siblings.size(); i++) {
Node aNode = siblings.get(i);
List<Node> childs = aNode.getChilds();
Row row = sheet.getRow(rowNum);
Cell cell = row.createCell(columnCounter);
String text = aNode.getDescription();
if (!cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) {
// apply the measure scale factor
text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale);
}
cell.setCellValue(createHelper.createRichTextString(text));
cell.setCellType(this.getCellTypeString());
int descendants = aNode.getLeafsNumber();
if (descendants > 1) {
sheet.addMergedRegion(new CellRangeAddress(rowNum, // first row (0-based)
rowNum, // last row (0-based)
columnCounter, // first column (0-based)
columnCounter + descendants - 1 // last column (0-based)
));
}
/*
* Now we have to set the style properly according to the nature of the node: if it contains the name of a dimension or a member. Since the
* structure foresees that a list of members follows a dimension, we calculate the position of the node with respect to the leaves; in case it is
* odd, the cell contains a dimension; in case it is even, the cell contains a dimension.
*/
int distanceToLeaves = aNode.getDistanceFromLeaves();
if (!cs.isMeasureOnRow()) {
distanceToLeaves--;
}
boolean isDimensionNameCell = distanceToLeaves > 0 && (distanceToLeaves % 2) == 1;
if (isDimensionNameCell) {
cell.setCellStyle(dimensionCellStyle);
} else {
cell.setCellStyle(memberCellStyle);
}
if (childs != null && childs.size() > 0) {
buildColumnsHeader(sheet, cs, childs, rowNum + 1, columnCounter, createHelper, locale, memberCellStyle, dimensionCellStyle);
}
int increment = descendants > 1 ? descendants : 1;
columnCounter = columnCounter + increment;
}
}
Example 13
| Source File: DefaultRowProcessor.java From onetwo with Apache License 2.0 | 4 votes |
|
protected Cell createCell(CellContextData cellContext){
int cellIndex = cellContext.getCellIndex();
Row row = cellContext.getCurrentRow();
FieldModel field = cellContext.getFieldModel();
Sheet sheet = cellContext.getSheet();
int rowNum = cellContext.getRowCount();
/*if(cellIndex < 0)
cellIndex = row.getLastCellNum();*/
// Cell cell = row.createCell(cellIndex);
Cell cell = createCell(row, cellIndex);
// System.out.println("cell width: " +sheet.getColumnWidth(cellIndex)+" w:" + sheet.getDefaultColumnWidth());
CellStyle cstyle = cellStyleBuilder.buildCellStyle(cellContext);
if(cstyle!=null){
cell.setCellStyle(cstyle);
}
// this.generator.getWorkbookData().getWorkbookListener().afterCreateCell(cell, cellIndex);
int colspan = cellContext.getColSpan();
if(colspan>1){
for(int i=1; i<colspan;i++){
Cell colspanCell = createCell(row, ++cellIndex);
if(cstyle!=null){
colspanCell.setCellStyle(cstyle);
}
// row.createCell(++cellIndex);
}
}
//性能关键点。。。。。。。
// if(field.isRange()){
int rowSpan = cellContext.getRowSpan();
if(rowSpan>1 || colspan>1){
// CellRangeAddress range = createCellRange(row, cell, field, root);
CellRangeAddress range = new CellRangeAddress(rowNum, rowNum+rowSpan-1, cell.getColumnIndex(), cell.getColumnIndex()+colspan-1);
sheet.addMergedRegion(range);
}
/*int cellType = getCellType(field.getDataType());
cell.setCellType(cellType);*/
return cell;
}
Example 14
| Source File: AbstractExcelFactory.java From myexcel with Apache License 2.0 | 4 votes |
|
/**
* 创建单元格
*
* @param td td
* @param sheet sheet
* @param currentRow 当前行
*/
protected void createCell(Td td, Sheet sheet, Row currentRow) {
Cell cell;
if (td.isFormula()) {
cell = currentRow.createCell(td.getCol(), CellType.FORMULA);
cell.setCellFormula(td.getContent());
} else {
String content = td.getContent();
switch (td.getTdContentType()) {
case STRING:
cell = currentRow.createCell(td.getCol(), CellType.STRING);
cell.setCellValue(content);
break;
case DOUBLE:
cell = currentRow.createCell(td.getCol(), CellType.NUMERIC);
if (null != content) {
cell.setCellValue(Double.parseDouble(content));
}
break;
case DATE:
cell = currentRow.createCell(td.getCol());
if (td.getDate() != null) {
cell.setCellValue(td.getDate());
} else if (td.getLocalDateTime() != null) {
cell.setCellValue(td.getLocalDateTime());
} else if (td.getLocalDate() != null) {
cell.setCellValue(td.getLocalDate());
}
break;
case BOOLEAN:
cell = currentRow.createCell(td.getCol(), CellType.BOOLEAN);
if (null != content) {
cell.setCellValue(Boolean.parseBoolean(content));
}
break;
case NUMBER_DROP_DOWN_LIST:
cell = currentRow.createCell(td.getCol(), CellType.NUMERIC);
String firstEle = setDropDownList(td, sheet, content);
if (firstEle != null) {
cell.setCellValue(Double.parseDouble(firstEle));
}
break;
case BOOLEAN_DROP_DOWN_LIST:
cell = currentRow.createCell(td.getCol(), CellType.BOOLEAN);
firstEle = setDropDownList(td, sheet, content);
if (firstEle != null) {
cell.setCellValue(Boolean.parseBoolean(firstEle));
}
break;
case DROP_DOWN_LIST:
cell = currentRow.createCell(td.getCol(), CellType.STRING);
firstEle = setDropDownList(td, sheet, content);
if (firstEle != null) {
cell.setCellValue(firstEle);
}
break;
case LINK_URL:
cell = setLink(td, currentRow, HyperlinkType.URL);
break;
case LINK_EMAIL:
cell = setLink(td, currentRow, HyperlinkType.EMAIL);
break;
case IMAGE:
cell = currentRow.createCell(td.getCol());
setImage(td, sheet);
break;
default:
cell = currentRow.createCell(td.getCol(), CellType.STRING);
cell.setCellValue(content);
break;
}
}
// 设置单元格样式
this.setCellStyle(currentRow, cell, td);
if (td.getCol() != td.getColBound()) {
for (int j = td.getCol() + 1, colBound = td.getColBound(); j <= colBound; j++) {
cell = currentRow.createCell(j);
this.setCellStyle(currentRow, cell, td);
}
}
if (td.getColSpan() > 0 || td.getRowSpan() > 0) {
sheet.addMergedRegion(new CellRangeAddress(td.getRow(), td.getRowBound(), td.getCol(), td.getColBound()));
}
}
Example 15
| Source File: ExcelExportUtil.java From jeewx with Apache License 2.0 | 4 votes |
|
/**
* 创建 最主要的 Cells
* @param styles
* @throws Exception
*/
private static int createCells(Drawing patriarch, int index, Object t,
List<ExcelExportEntity> excelParams, Sheet sheet,
HSSFWorkbook workbook, Map<String, HSSFCellStyle> styles) throws Exception {
ExcelExportEntity entity;
Row row = sheet.createRow(index);
row.setHeight((short) 350);
int maxHeight = 1, cellNum = 0;
for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
entity = excelParams.get(k);
if (entity.getList() != null) {
Collection<?> list = (Collection<?>) entity.getGetMethod()
.invoke(t, new Object[] {});
int listC = 0;
for (Object obj : list) {
createListCells(patriarch,index + listC, cellNum, obj,
entity.getList(), sheet, workbook,styles);
listC++;
}
cellNum += entity.getList().size();
if (list!=null&&list.size() > maxHeight) {
maxHeight = list.size();
}
} else {
Object value = getCellValue(entity,t);
if (entity.getType() == 1) {
createStringCell(row, cellNum++,
value == null ? "" : value.toString(),
index % 2 == 0 ? getStyles(styles, false, entity.isWrap())
: getStyles(styles, true, entity.isWrap()),entity);
} else {
createImageCell(patriarch,entity, row, cellNum++, value == null ? ""
: value.toString(),t);
}
}
}
//合并需要合并的单元格
cellNum = 0;
for(int k = 0, paramSize = excelParams.size(); k < paramSize; k++){
entity = excelParams.get(k);
if (entity.getList() != null) {
cellNum += entity.getList().size();
}else if (entity.isNeedMerge()) {
sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight-1, cellNum,
cellNum));
cellNum++;
}
}
return maxHeight;
}
Example 16
| Source File: EventWorksheet.java From sakai with Educational Community License v2.0 | 4 votes |
|
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;
}
Example 17
| Source File: ExcelExportBase.java From jeasypoi with Apache License 2.0 | 4 votes |
|
/**
* 创建 最主要的 Cells
*
* @param styles
* @param rowHeight
* @throws Exception
*/
public int createCells(Drawing patriarch, int index, Object t, List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook, short rowHeight) throws Exception {
ExcelExportEntity entity;
Row row = sheet.createRow(index);
row.setHeight(rowHeight);
int maxHeight = 1, cellNum = 0;
int indexKey = createIndexCell(row, index, excelParams.get(0));
cellNum += indexKey;
for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
entity = excelParams.get(k);
if (entity.getList() != null) {
Collection<?> list = getListCellValue(entity, t);
int listC = 0;
for (Object obj : list) {
createListCells(patriarch, index + listC, cellNum, obj, entity.getList(), sheet, workbook);
listC++;
}
cellNum += entity.getList().size();
if (list != null && list.size() > maxHeight) {
maxHeight = list.size();
}
} else {
Object value = getCellValue(entity, t);
if (entity.getType() == 1) {
createStringCell(row, cellNum++, value == null ? "" : value.toString(), index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
} else if (entity.getType() == 4){
createNumericCell(row, cellNum++, value == null ? "" : value.toString(), index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
} else {
createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), t);
}
}
}
// 合并需要合并的单元格
cellNum = 0;
for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
entity = excelParams.get(k);
if (entity.getList() != null) {
cellNum += entity.getList().size();
} else if (entity.isNeedMerge()) {
for (int i = index + 1; i < index + maxHeight; i++) {
sheet.getRow(i).createCell(cellNum);
sheet.getRow(i).getCell(cellNum).setCellStyle(getStyles(false, entity));
}
sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight - 1, cellNum, cellNum));
cellNum++;
}
}
return maxHeight;
}
Example 18
| Source File: ExcelExportServer.java From autopoi with Apache License 2.0 | 4 votes |
|
/**
* 创建表头
*
* @param title
* @param index
*/
private int createTitleRow(ExportParams title, Sheet sheet, Workbook workbook, int index, List<ExcelExportEntity> excelParams) {
Row row = sheet.createRow(index);
int rows = getRowNums(excelParams);
row.setHeight((short) 450);
Row listRow = null;
if (rows == 2) {
listRow = sheet.createRow(index + 1);
listRow.setHeight((short) 450);
}
int cellIndex = 0;
CellStyle titleStyle = getExcelExportStyler().getTitleStyle(title.getColor());
for (int i = 0, exportFieldTitleSize = excelParams.size(); i < exportFieldTitleSize; i++) {
ExcelExportEntity entity = excelParams.get(i);
//update-begin-author:taoyan date:20200319 for:建议autoPoi升级，优化数据返回List Map格式下的复合表头导出excel的体验 #873
if(entity.isColspan()){
List<String> subList = entity.getSubColumnList();
if(subList==null || subList.size()==0){
continue;
}else{
entity.initSubExportEntity(excelParams);
}
}
//update-end-author:taoyan date:20200319 for:建议autoPoi升级，优化数据返回List Map格式下的复合表头导出excel的体验 #873
if (StringUtils.isNotBlank(entity.getName())) {
createStringCell(row, cellIndex, entity.getName(), titleStyle, entity);
}
if (entity.getList() != null) {
List<ExcelExportEntity> sTitel = entity.getList();
if (StringUtils.isNotBlank(entity.getName())) {
sheet.addMergedRegion(new CellRangeAddress(index, index, cellIndex, cellIndex + sTitel.size() - 1));
}
for (int j = 0, size = sTitel.size(); j < size; j++) {
createStringCell(rows == 2 ? listRow : row, cellIndex, sTitel.get(j).getName(), titleStyle, entity);
cellIndex++;
}
cellIndex--;
} else if (rows == 2) {
createStringCell(listRow, cellIndex, "", titleStyle, entity);
sheet.addMergedRegion(new CellRangeAddress(index, index + 1, cellIndex, cellIndex));
}
cellIndex++;
}
return rows;
}
Example 19
| Source File: PoiMergeCellUtil.java From autopoi with Apache License 2.0 | 3 votes |
|
/**
* 字符为空的情况下判断
*
* @param index
* @param mergeDataMap
* @param sheet
*/
private static void mergeCellOrContinue(Integer index, Map<Integer, MergeEntity> mergeDataMap, Sheet sheet) {
if (mergeDataMap.containsKey(index) && mergeDataMap.get(index).getEndRow() != mergeDataMap.get(index).getStartRow()) {
sheet.addMergedRegion(new CellRangeAddress(mergeDataMap.get(index).getStartRow(), mergeDataMap.get(index).getEndRow(), index, index));
mergeDataMap.remove(index);
}
}
Example 20
| Source File: ExcelUtil.java From autopoi with Apache License 2.0 | 2 votes |
|
/**
* 合并单元格
* @param sheet
* @param firstRow 开始行
* @param lastRow 结束行
* @param firstCol 开始列
* @param lastCol 结束列
*/
public static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
