/** Ajustar las propiedades de las hojas. */
protected void configPage() {
HSSFSheet sheet = getExcel().getActiveSheet();
HSSFPrintSetup ps = sheet.getPrintSetup();
sheet.setDisplayGridlines(false);
// Dejar fijo un conjuto filas o/y columnas
// sheet.createFreezePane( 0, 3 ); // deja fijas las 3 primeras filas.
// sheet.createFreezePane( 1, 0, 1, 0 ); // deja fija la primera columna.
// Create a split with the lower left side being the active quadrant
// sheet.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT );
// margen bottom de la hoja 0.6 = 1.5 en Excel (para que no se solapen las filas con el footer)
sheet.setMargin((short) 3, 0.6);
// marcar el radiobutton de ajustar a la página
sheet.setAutobreaks(true);
// excel.setColumnWidth(2, 30);
// ajustar a la página 1 a 500 páginas.
// excel.setFitToPage();
// sheet.setVerticallyCenter(true);
ps.setFitWidth((short) 1);
ps.setFitHeight((short) 500);
// horizontal
ps.setLandscape(landscape);
// Crear agrupaciones de filas o columnas.
// sheet.groupRow((short)3,(short)5);
// sheet.setRowGroupCollapsed((short)3, true);
}
public void beforeBody(TableModel model) {
logger.debug("XlsView.init()");
moneyFormat =
model
.getPreferences()
.getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.money");
if (StringUtils.isEmpty(moneyFormat)) {
moneyFormat = DEFAULT_MONEY_FORMAT;
}
percentFormat =
model
.getPreferences()
.getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.percent");
if (StringUtils.isEmpty(percentFormat)) {
percentFormat = DEFAULT_PERCENT_FORMAT;
}
wb = new HSSFWorkbook();
sheet = wb.createSheet();
wb.setSheetName(0, "Export Workbook");
styles = initStyles(wb);
ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short) 1);
ps.setFitWidth((short) 1);
createHeader(model);
}
/**
* 添加临时表格内容
*
* @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;
}