/**
* 取单元格所在的合并区域，如果返回空，则说明没有在合并区域
*
* @param cell -- 指定的单元格
* @return
*/
private static CellRangeAddress getMergedRegion(HSSFCell cell) {
HSSFSheet sheet = cell.getSheet();
CellRangeAddress range = null;
int mergedNum = sheet.getNumMergedRegions();
for (int i = 0; i < mergedNum; i++) {
range = sheet.getMergedRegion(i);
if (range.getFirstColumn() == cell.getColumnIndex()
&& range.getFirstRow() == cell.getRowIndex()) {
return range;
}
}
return null;
}
/**
* 添加临时表格内容
*
* @param mainSheet -- 原表单内容
* @param subSheet -- 临时表单内容
* @return
*/
public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) {
if (mainSheet == null || subSheet == null) return null;
// 判断报表是否允许输出
if (!isAllowOut(mainSheet)) return mainSheet;
// 原报表的最后一行
int endRowNum = mainSheet.getPhysicalNumberOfRows();
HSSFRow sourow = null, descrow = null;
HSSFCell sourcell = null, descell = null, orgcell = null;
int i = 0, offsetcnt = 0;
// 复制表格中的图片
copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook());
// 设置以合并的单元格
CellRangeAddress range = null;
int mergedNum = subSheet.getNumMergedRegions();
for (i = 0; i < mergedNum; i++) {
range = subSheet.getMergedRegion(i);
range.setFirstRow(range.getFirstRow() + endRowNum);
range.setLastRow(range.getLastRow() + endRowNum);
mainSheet.addMergedRegion(range);
}
range = null;
// int k = 0;
// 设置相关参数
mainSheet.setAlternativeExpression(subSheet.getAlternateExpression());
mainSheet.setAlternativeFormula(subSheet.getAlternateFormula());
mainSheet.setAutobreaks(subSheet.getAutobreaks());
mainSheet.setDialog(subSheet.getDialog());
mainSheet.setDisplayGuts(subSheet.getDisplayGuts());
mainSheet.setFitToPage(subSheet.getFitToPage());
for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) {
sourow = (HSSFRow) iterow.next();
offsetcnt = sourow.getRowNum() + endRowNum;
descrow = mainSheet.createRow(offsetcnt);
descrow.setHeight(sourow.getHeight());
descrow.setHeightInPoints(sourow.getHeightInPoints());
java.util.Iterator<Cell> iter = sourow.cellIterator();
while (iter.hasNext()) {
sourcell = (HSSFCell) iter.next();
int column = sourcell.getColumnIndex();
descell = descrow.createCell(column);
// 取模板中的单元格，与来源表单位置相同
int row = sourcell.getRowIndex();
orgcell = mainSheet.getRow(row).getCell(column);
if (orgcell != null) {
// 取模板中的类型赋值
descell.setCellType(orgcell.getCellType());
// 取模板中的样式赋值
descell.setCellStyle(orgcell.getCellStyle());
} else {
_log.showWarn("module xls [{0}, {1}] cell is null!", row, column);
}
if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING)
descell.setCellValue(sourcell.getStringCellValue());
else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
descell.setCellValue(sourcell.getNumericCellValue());
else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ;
}
sourow = null;
sourcell = null;
descrow = null;
orgcell = null;
}
return mainSheet;
}
/**
* Собирает информацию о некоторой области из указанного листа шаблона excel.
*
* @param sheet шаблон листа отчета в котором находятся данные для данной области.
* @param top номер верхней строки (начиная с 0) относящейся к указанной области.
* @param height количество строк в области. Должно быть как минимум 1.
* @param palette реестр всех стилей используемых в данном отчете.
*/
public Area(final HSSFSheet sheet, final int top, final int height, final StylePalette palette) {
if (sheet == null || top < 0 || height < 1)
throw new IllegalArgumentException("Illegal area arguments");
rows = new ArrayList<Row>();
regions = new ArrayList<Region>();
final int bottom = top + height - 1;
int lastColumn = 0;
for (int i = top; i <= bottom; i++) {
final Row rm = new Row();
rows.add(rm);
final HSSFRow row = sheet.getRow(i);
if (row == null) {
rm.setHeight(sheet.getDefaultRowHeight());
continue;
}
rm.setHeight(row.getHeight());
rm.setHidden(row.getZeroHeight());
lastColumn = Math.max(lastColumn, row.getLastCellNum());
for (int j = 0; j <= row.getLastCellNum(); j++) {
final HSSFCell cell = row.getCell(j);
if (cell == null) {
rm.getCells().add(null);
} else {
rm.getCells().add(new Cell(cell, palette));
}
}
}
this.columnsCount = lastColumn + 1;
final int regcount = sheet.getNumMergedRegions();
for (int i = 0; i < regcount; i++) {
final CellRangeAddress src = sheet.getMergedRegion(i);
if (src.getFirstRow() >= top && src.getLastRow() <= bottom) {
final Region dst =
new Region(
this,
src.getFirstColumn(),
src.getFirstRow() - top,
src.getLastColumn(),
src.getLastRow() - top);
regions.add(dst);
} else if (src.getFirstRow() < top && src.getLastRow() >= top)
throw new IllegalArgumentException(
"Illegal region {top:"
+ top
+ ", height:"
+ height
+ "} bounds: conflict with region {top:"
+ src.getFirstRow()
+ ", bottom:"
+ src.getLastRow()
+ "}");
if (src.getFirstRow() >= top && src.getFirstRow() <= bottom && src.getLastRow() > bottom)
throw new IllegalArgumentException(
"Illegal region {top:"
+ top
+ ", height:"
+ height
+ "} bounds: conflict with region {top:"
+ src.getFirstRow()
+ ", bottom:"
+ src.getLastRow()
+ "}");
}
this.hidden = false;
}