下面列出了org.apache.poi.ss.usermodel.Sheet#addMergedRegion ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* 指定した範囲のセルを結合する。
* @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;
}
/**
* 创建 表头改变
*
* @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;
}
/**
* 合并行
*/
//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);
}
}
}
}
}
/**
* 合并单元格
*
* @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);
}
}
/**
* 合并行
*/
//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);
}
}
}
}
}
/**
* 合并单元格
*
* @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);
}
/**
* ネストしたレコードの親のセルを結合する
* @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);
}
}
/**
* 创建表头
*
* @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;
}
@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();
}
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);
}
/**
* 创建表头
*
* @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;
}
/**
* 创建 最主要的 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;
}
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;
}
/**
* 创建 最主要的 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;
}
/**
* 创建单元格
*
* @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()));
}
}
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;
}
/**
* 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;
}
}
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));
}
/**
* 字符为空的情况下判断
*
* @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);
}
}
/**
* 合并单元格
* @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));
}