• problem exporting to excel workbook

    By Lisa Gerlich 3 years ago

    Hello,
    I am playing with poi4xpages to see if it will help us. For testing purposes, I have a view with about 13,000 documents (note: I do not plan on allowing application users to export 13,000 documents - that is what searches are for). I have worked with both the Simple View Export widget and the POI Workbook widget. The Simple View Export widget takes a bit (obviously) but does export all 13,000 documents. The POI Workbook widget works fast but only exports 1000 documents. I added the debugging and the log does not record anything amiss as far as I can see:
    01/08/2016 15:14:01 HTTP JVM: FINER: First getting the File (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    01/08/2016 15:14:01 HTTP JVM: INFO: Getting NoteCollection (biz.webgate.dominoext.poi.component.data.ResourceTemplateSource)
    01/08/2016 15:14:01 HTTP JVM: INFO: Select only MiscFormaElements (biz.webgate.dominoext.poi.component.data.ResourceTemplateSource)
    01/08/2016 15:14:01 HTTP JVM: INFO: buildColleciton (biz.webgate.dominoext.poi.component.data.ResourceTemplateSource)
    01/08/2016 15:14:01 HTTP JVM: INFO: browseColllection (biz.webgate.dominoext.poi.component.data.ResourceTemplateSource)
    01/08/2016 15:14:01 HTTP JVM: INFO: DONE (biz.webgate.dominoext.poi.component.data.ResourceTemplateSource)
    01/08/2016 15:14:01 HTTP JVM: INFO: No recycle -> sesSigner.currentDB (biz.webgate.dominoext.poi.util.DatabaseProvider)
    01/08/2016 15:14:01 HTTP JVM: FINER: Push the Result to the HttpServlet (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    01/08/2016 15:14:01 HTTP JVM: FINER: Proccess Spread Sheet (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    01/08/2016 15:14:01 HTTP JVM: FINER: Proccess Cell Values (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    01/08/2016 15:14:01 HTTP JVM: FINER: Proccess ExportDefinition (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    01/08/2016 15:14:01 HTTP JVM: FINER: Proccess Export Row (biz.webgate.dominoext.poi.component.kernel.workbook.EmbeddedDataSourceExportProcessor)
    01/08/2016 15:14:01 HTTP JVM: FINER: Load Access Source (biz.webgate.dominoext.poi.component.kernel.workbook.EmbeddedDataSourceExportProcessor)
    01/08/2016 15:14:02 HTTP JVM: INFO: No recycle -> sesSigner.currentDB (biz.webgate.dominoext.poi.util.DatabaseProvider)
    01/08/2016 15:14:02 HTTP JVM: FINER: Start Processing Cells (biz.webgate.dominoext.poi.component.kernel.workbook.EmbeddedDataSourceExportProcessor)
    01/08/2016 15:14:02 HTTP JVM: INFO: No recycle -> sesSigner.currentDB (biz.webgate.dominoext.poi.util.DatabaseProvider)
    01/08/2016 15:14:02 HTTP JVM: FINER: Proccess Export Row - DONE (biz.webgate.dominoext.poi.component.kernel.workbook.EmbeddedDataSourceExportProcessor)
    01/08/2016 15:14:02 HTTP JVM: FINER: Post Generation Process (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    01/08/2016 15:14:02 HTTP JVM: FINER: Push the Result to the HttpServlet (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)

    I would much rather use the POI Workbook widget, but will need at times to export more than 1000 documents.

    Here is my source:

    <?xml version="1.0" encoding="UTF-8"?>
    <xp:view xmlns:xp="http://www.ibm.com/xsp/core"
        xmlns:xp_1="http://www.webgate.biz/xsp/poi">
    
    
    
    
    
        <xp:this.data>
            <xp:dominoView var="view1" viewName="PDirGenSearch"></xp:dominoView>
        </xp:this.data>
    
        <xp:button id="button1" value="Export to Excel Workbook">
    
        <xp:eventHandler event="onclick" submit="true" refreshMode="complete">
            <xp:this.action>
                <xp_1:generateWorkbook workbookId="spreadsheet1"></xp_1:generateWorkbook>
            </xp:this.action></xp:eventHandler></xp:button>
        <xp:br></xp:br>
        <xp_1:spreadsheet id="spreadsheet1" disableTheme="false"
            downloadFileName="export2.xls">
    
    
    
    
            <xp_1:this.templateSource>
                <xp_1:resourcetemplate fileName="Book1.xls"></xp_1:resourcetemplate>
            </xp_1:this.templateSource>
            <xp_1:this.spreadsheets>
                <xp_1:table name="Sheet1">
                    <xp_1:this.exportDefinitions>
                        <xp_1:data2rowExport startRow="33">
    
                            <xp_1:this.columns>
                                <xp_1:columnDefinition columnNumber="0"
                                    columnTitle="Lastname">
                                </xp_1:columnDefinition>
                                <xp_1:columnDefinition columnNumber="1"
                                    columnTitle="Firstname">
                                </xp_1:columnDefinition>
                                <xp_1:columnDefinition columnNumber="2"
                                    columnTitle="EmpID">
                                </xp_1:columnDefinition>
                                <xp_1:columnDefinition columnNumber="3"
                                    columnTitle="Campus">
                                </xp_1:columnDefinition>
                                <xp_1:columnDefinition columnNumber="4"
                                    columnTitle="CampusID">
                                </xp_1:columnDefinition>
                                <xp_1:columnDefinition columnNumber="5"
                                    columnTitle="Title">
                                </xp_1:columnDefinition>
                                <xp_1:columnDefinition columnNumber="6"
                                    columnTitle="JobCode">
                                </xp_1:columnDefinition>
                                <xp_1:columnDefinition columnNumber="7"
                                    columnTitle="Email">
                                </xp_1:columnDefinition>
                            </xp_1:this.columns>
                            <xp_1:this.dataSource>
                                <xp_1:dominoViewSource
                                    viewName="PDirGenSearch">
                                </xp_1:dominoViewSource>
                            </xp_1:this.dataSource>
                        </xp_1:data2rowExport>
                    </xp_1:this.exportDefinitions>
                    <xp_1:this.cellValues>
                        <xp_1:cellValue columnNumber="0" rowNumber="32"
                            value="Lastname">
                            <xp_1:this.cellStyle>
                                <xp_1:cellStyle
                                    fontBoldweight="BOLDWEIGHT_BOLD" fontHeightInPoints="12"
                                    wrapText="true">
                                </xp_1:cellStyle>
                            </xp_1:this.cellStyle>
                        </xp_1:cellValue>
                        <xp_1:cellValue columnNumber="1" value="Firstname"
                            rowNumber="32">
                            <xp_1:this.cellStyle>
                                <xp_1:cellStyle
                                    fontBoldweight="BOLDWEIGHT_BOLD" fontHeightInPoints="12"
                                    wrapText="true">
                                </xp_1:cellStyle>
                            </xp_1:this.cellStyle>
                        </xp_1:cellValue>
                        <xp_1:cellValue rowNumber="32" value="EmpID"
                            columnNumber="2">
                            <xp_1:this.cellStyle>
                                <xp_1:cellStyle
                                    fontBoldweight="BOLDWEIGHT_BOLD" wrapText="true"
                                    fontHeightInPoints="12">
                                </xp_1:cellStyle>
                            </xp_1:this.cellStyle>
                        </xp_1:cellValue>
                        <xp_1:cellValue rowNumber="32" value="Campus"
                            columnNumber="3">
                            <xp_1:this.cellStyle>
                                <xp_1:cellStyle
                                    fontBoldweight="BOLDWEIGHT_BOLD" fontHeightInPoints="12"
                                    wrapText="true">
                                </xp_1:cellStyle>
                            </xp_1:this.cellStyle>
                        </xp_1:cellValue>
    
                        <xp_1:cellValue columnNumber="4" value="CampusID"
                            rowNumber="32">
                            <xp_1:this.cellStyle>
                                <xp_1:cellStyle
                                    fontBoldweight="BOLDWEIGHT_BOLD" fontHeightInPoints="12"
                                    wrapText="true">
                                </xp_1:cellStyle>
                            </xp_1:this.cellStyle>
                        </xp_1:cellValue>
                        <xp_1:cellValue rowNumber="32" value="Title"
                            columnNumber="5">
                            <xp_1:this.cellStyle>
                                <xp_1:cellStyle
                                    fontBoldweight="BOLDWEIGHT_BOLD" fontHeightInPoints="12"
                                    wrapText="true">
                                </xp_1:cellStyle>
                            </xp_1:this.cellStyle>
                        </xp_1:cellValue>
                        <xp_1:cellValue rowNumber="32" value="JobCode"
                            columnNumber="6">
                            <xp_1:this.cellStyle>
                                <xp_1:cellStyle
                                    fontBoldweight="BOLDWEIGHT_BOLD" fontHeightInPoints="12"
                                    wrapText="true">
                                </xp_1:cellStyle>
                            </xp_1:this.cellStyle>
                        </xp_1:cellValue>
                        <xp_1:cellValue rowNumber="32" value="Email"
                            columnNumber="7">
                            <xp_1:this.cellStyle>
                                <xp_1:cellStyle
                                    fontBoldweight="BOLDWEIGHT_BOLD" wrapText="true"
                                    fontHeightInPoints="12">
                                </xp_1:cellStyle>
                            </xp_1:this.cellStyle>
                        </xp_1:cellValue>
                    </xp_1:this.cellValues>
                </xp_1:table>
            </xp_1:this.spreadsheets>
        </xp_1:spreadsheet>
    </xp:view>
    

    Thanks for any guidance.
    —Lisa&

    • By Christian Güdemann 3 years ago

      Hello Lisa

      There is a property on the datasource tag called maxRow. If not set, this is limited to 1000 rows. Please change this value. The purpose to set a value (like 1000) was to avoid unresponsive servers.


      Change the value to 20000 and see whats happens

      Best regard
      Christian

      • By Lisa A Gerlich 3 years ago

        Christian,
        Thank you very much. It worked like a charm. POI 4 Xpages rocks.
        —Lisa&