Skip to main content

A: NPOI and Excel, DocX and Word

Beyond simply using the host.excel() method, there are many ways to run queries or get lists of records and populate Excel files for download. The host.excel() method is great for a quick, single-she…

Beyond simply using the host.excel() method, there are many ways to run queries or get lists of records and populate Excel files for download.

The host.excel() method is great for a quick, single-sheet Excel workbook. However, if we need to create or populate more than one sheet, we must move on to some of the more specific commands for working with dynamic Excel workbooks.

One of the most useful advanced techniques is to return multiple result sets in a single query. Most databases allow multiple SELECT clauses separated by a statement terminator, usually a semicolon. These types of multi-result sets must be treated specially, however Trestle has a few built-in methods for simplifying their usage.

/**
Command function demonstrating multiple result sets to Excel sheets.
*/
def cmdDataSetToExcel() {
    from Trestle.Data import DbFactory;
    from Trestle.Reports import NpoiHelpers;
	
    var query =
    $$
    SELECT 
        p.name, c.* 
    FROM 
        party p 
    INNER JOIN 
        customer c ON c.id = p.id;
    SELECT 
        p1.name, p2.* 
    FROM 
        party p1 
    INNER JOIN 
        person p2 ON p2.id = p1.id;
    $$;
    var ds = DbFactory.GetDataSet(query, 'Customers', 'People');
    var wb = NpoiHelpers.FromDataSet(ds, false, true);
    ds.Dispose();
	
    return wb;
}

/**
Command function demonstrating manually-created Excel sheets.
*/
def cmdMultipleSheetsFromQueries() {
    from Trestle.Data import DbFactory as F;
    from Trestle.Reports import NpoiHelpers as NH;
	
    var q1 =
    $$
    SELECT 
        p.name, c.* 
    FROM 
        party p 
    INNER JOIN 
        customer c ON c.id = p.id;
    $$;
    
    var q2 =
    $$
    SELECT 
        p1.name, p2.* 
    FROM 
        party p1 
    INNER JOIN 
        person p2 ON p2.id = p1.id;
    $$;
	
    var customers = F.GetObjects(q1);
    var people = F.GetObjects(q2);
    var wb = NH.NewWorkbook();
    
    NH.AddSheetFromProxies(wb, customers, 'Customers');
    NH.AddSheetFromProxies(wb, people, 'People');
    // Get rid of the unused first sheet
    wb.RemoveSheetAt(0);
	
    return wb;
}

The cmdMultipleSheetsFromQueries command function also demonstrates the use of type import aliasing to avoid needing to type out full names. This is covered in Aliasing.

Advanced Excel Reports with Fields

We'll be expanding the documentation in this section soon. Please contact us if you have questions.

Using Excel as a Supplemental Data Store

We'll be expanding the documentation in this section soon. Please contact us if you have questions.

How did we do?

A: Direct Links - URLs and Downloads

A: Embedded Apps with MS Access Files

Contact