我想在报告生成期间导出一个具有LazyLoad数据模型的数据表(带分页)。
问题:当我导出时，从数据库逐页生成报告，然后导出到Excel/PDF，这需要更多的时间。我想得到它在一个单一的数据库访问跳过一页一页的数据集生成。
我生成的代码片段如下:
JSF:<p:dataTable id="dTable" var="dev" value="#{ReportAction.lazyModel}"
styleClass ="table_paginator" rowKey="#{device.macAddress}" paginatorPosition="bottom"
paginator="true" rowsPerPageTemplate="10,20,30" rows="10" lazy="true"
paginatorTemplate="{CurrentPageReport} {FirstPageLink} {PreviousPageLink} {PageLinks} {NextPageLink} {LastPageLink} {RowsPerPageDropdown}"
emptyMessage="Select appropriate conditions and click 'Generate Report'">
<f:facet name="header">
<h:commandLink actionListener="#{ReportAction.doExport}">
<p:graphicImage value="../../../resources/images/excel.png"
alt="XLS" style="float:right;width:32px;height:32px" />
<p:dataExporter type="xls" target="dTable" fileName="#{ReportAction.fileName}"
preProcessor="#{ReportAction.preProcess}"
postProcessor="#{ReportAction.postProcessXLS}" />
</h:commandLink>
</f:facet>
<!-- All the columns in Data Table -->
</p:dataTable>
管理Bean:
public class ReportAction {
private ConfigurationReportDataModel mediumConfigModel;
private List<FieldReportModel> configModelList;
private String fileName;
private LazyDataModel<ConfigurationReportModel> lazyModel;
private boolean export;
public ReportAction() {
configModelList = new ArrayList<ConfigurationReportModel>();
export = false;
mediumConfigModel = new ReportDataModel();
}
public void generateFieldReport() {
lazyFieldModel = new ConfigurationReportDataModel(day, fromDate,
location,store,engineer, status, toDate, export);
}
public void preProcess(Object document) {
export = true;
log.info("preProcess::Lazy model : Page Sizing");
if(lazyFieldModel != null) {
lazyFieldModel.setPageSize(1000000);
}
log.info("preProcess::Export All Details");
mediumConfigModel.setExport(true);
}
public void postProcessXLS(Object document) {
HSSFWorkbook wb = (HSSFWorkbook) document;
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow header = sheet.getRow(0);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
HSSFCell cell = header.getCell(i);
cell.setCellValue(cell.getStringCellValue().toUpperCase());
cell.setCellStyle(cellStyle);
sheet.autoSizeColumn(i);
}
export = false;
mediumConfigModel.setExport(false);
}
public List<ConfigurationReportModel> getConfigModelList() {
return configModelList;
}
public void setConfigModelList(
ArrayList<ConfigurationReportModel> configModelList) {
this.configModelList = configModelList;
}
public String getFileName() {
SimpleDateFormat formatter = new SimpleDateFormat("ddMMyyyy_HHmmss");
fileName = "Config_Report_".concat(formatter.format(new Date()));
return fileName;
}
public void setMediumConfigModel(
ConfigurationReportDataModel mediumConfigModel) {
this.mediumConfigModel = mediumConfigModel;
}
public void setConfigModelList(
List<ConfigurationReportModel> configModelList) {
this.configModelList = configModelList;
}
public LazyDataModel<ConfigurationReportModel> getLazyFieldModel() {
log.info("##########getLazyFieldModel###########");
if(export) {
log.info("getLazyFieldModel::Will get Exported........");
lazyFieldModel = new ConfigurationReportDataModel(day, fromDate,
location, store, engineer, status, toDate, true);
lazyFieldModel.load(1, 1000000000, null, null, null);
}
return lazyFieldModel;
}
public void setLazyFieldModel(
LazyDataModel<ConfigurationReportModel> lazyFieldModel) {
this.lazyFieldModel = lazyFieldModel;
}
}
public class ConfigurationReportDataModel extends
LazyDataModel<ConfigurationReportModel> {
private List<ConfigurationReportModel> configReport;
private boolean export;
public ConfigurationReportDataModel() {
this.export = false;
}
public List<ConfigurationReportModel> load(int first, int pageSize,
String sortField, SortOrder sortOrder, Map<String, String> filters) {
UIClient client = new UIClient();
ReportData data = null;
// ///////////////////
if(export) {
log.info("Do Export....");
first = 1;
pageSize = 1000000000;
}
deviceList = new ArrayList<DeviceGlobal>();
// Retrieves data from Database with the number of record (page size)
data = client.generateFieldReport(first, pageSize,
Integer.parseInt(location), Integer.parseInt(store),
engineer, getTimeToBeginningOfDay(), getTimeToEndofDay(),
status);
log.info("Obtained data : " + data);
if (data != null) {
log.info("Got devices : " + data.getRecords().size());
deviceList = (ArrayList<DeviceGlobal>) data.getRecords();
// ///////////////////
int record = first + 1;
ConfigurationReportModel storeModel = null;
DeviceGlobal deviceGlobal = null;
configReport = new ArrayList<ConfigurationReportModel>();
for (Iterator<DeviceGlobal> iterator = deviceList.iterator(); iterator
.hasNext();) {
deviceGlobal = (DeviceGlobal) iterator.next();
storeModel = new ConfigurationReportModel(deviceGlobal,
record++);
configReport.add(storeModel);
}
log.info("Total Config Report : " + configReport.size());
// rowCount
int dataSize = data.getReportCount();
this.setRowCount(dataSize);
log.info("Report Count: " + data.getReportCount());
if(export) {
return configReport;
}
else {
// paginate
if (dataSize > pageSize) {
try {
return configReport;
} catch (IndexOutOfBoundsException e) {
return configReport;
}
} else {
return configReport;
}
}
} else {
log.info("Got no devices");
deviceList = new ArrayList<DeviceGlobal>();
configReport = new ArrayList<ConfigurationReportModel>();
this.setRowCount(0);
return configReport;
}
}
}
注意:
- 没有语法错误。
- 定义所有自定义类型类。
如何通过跳过逐页生成记录来获得Excel报告?
您必须将pageOnly="false"添加到dataexporters
对于零除数问题，只需重写一个额外的LaztDataModel方法，如下所示。
@Override
public void setRowIndex(int rowIndex) {
// TODO Auto-generated method stub
if (rowIndex == -1 || getPageSize() == 0) {
super.setRowIndex(-1);
}
else
super.setRowIndex(rowIndex % getPageSize());
}