Data Export

Export spreadsheet data to CSV, JSON, HTML, and query formats

Learn how to export spreadsheet data to various formats using the BoxLang Fluent API. Convert Excel files to CSV, JSON, HTML, queries, and more.

📄 Export to CSV

Basic CSV Export

// Load and export to CSV
csv = Spreadsheet( "employees.xlsx" ).toCSV();

// Save to file
fileWrite( "employees.csv", csv );

CSV with Custom Delimiter

sheet = Spreadsheet( "data.xlsx" );

// Export with semicolon delimiter
csv = sheet.toCSV( delimiter = ";" );

// Export with tab delimiter
csv = sheet.toCSV( delimiter = chr(9) );

fileWrite( "data.csv", csv );

CSV from Specific Sheet

sheet = Spreadsheet( "workbook.xlsx" );

// Select specific sheet
sheet.selectSheet( "Sales Data" );

// Export that sheet only
csv = sheet.toCSV();

fileWrite( "sales.csv", csv );

CSV with Headers

data = [
    [ "John Doe", "Engineering", 95000 ],
    [ "Jane Smith", "Marketing", 85000 ]
];

csv = Spreadsheet()
    .setRowData( 1, [ "Name", "Department", "Salary" ] )
    .addRows( data )
    .toCSV();

fileWrite( "employees.csv", csv );

🔤 Export to JSON

Array of Objects

// Load spreadsheet
sheet = Spreadsheet( "employees.xlsx" );

// Convert to JSON (uses first row as keys)
json = sheet.toJson();

// Result format:
// [
//     {"Name":"John Doe","Department":"Engineering","Salary":95000},
//     {"Name":"Jane Smith","Department":"Marketing","Salary":85000}
// ]

fileWrite( "employees.json", json );

Pretty-Printed JSON

sheet = Spreadsheet( "data.xlsx" );

// Get as array first
data = sheet.toArray();

// Serialize with formatting
json = serializeJSON( data, true );

fileWrite( "data-pretty.json", json );

JSON with Custom Structure

sheet = Spreadsheet( "products.xlsx" );

// Convert to array
products = sheet.toArray();

// Transform structure
output = {
    "exportDate": dateFormat( now(), "yyyy-mm-dd" ),
    "totalProducts": products.len(),
    "products": products
};

fileWrite( "products.json", serializeJSON( output, true ) );

🗄️ Export to Query

Basic Query Conversion

// Load and convert to query
qry = Spreadsheet( "employees.xlsx" ).toQuery();

// Now you can use query functions
writeDump( qry );

// Loop through query
for ( row in qry ) {
    writeOutput( "#row.Name# - #row.Department#<br>" );
}

Query with Custom Column Names

sheet = Spreadsheet()
    .setRowData( 1, [ "Full Name", "Job Title", "Annual Salary" ] )
    .addRow( [ "John Doe", "Engineer", 95000 ] );

// First row becomes column names (spaces replaced with underscores)
qry = sheet.toQuery();

// Access with: qry.Full_Name, qry.Job_Title, qry.Annual_Salary

Query from Specific Range

sheet = Spreadsheet( "data.xlsx" );

// Get specific rows as array
rows = sheet.getRows( startRow = 5, endRow = 20 );

// Convert to query
qry = queryNew( "Col1,Col2,Col3", "varchar,varchar,numeric" );

for ( row in rows ) {
    queryAddRow( qry, {
        "Col1": row[1],
        "Col2": row[2],
        "Col3": row[3]
    } );
}

🌐 Export to HTML

Simple HTML Table

html = Spreadsheet( "report.xlsx" ).toHtml();

// Generates complete HTML table
fileWrite( "report.html", html );

HTML with Custom Styling

sheet = Spreadsheet( "data.xlsx" );

html = '
<!DOCTYPE html>
<html>
<head>
    <title>Data Export</title>
    <style>
        table { border-collapse: collapse; width: 100%; }
        th { background: ##4CAF50; color: white; padding: 10px; }
        td { border: 1px solid ##ddd; padding: 8px; }
        tr:nth-child(even) { background: ##f2f2f2; }
    </style>
</head>
<body>
    <h1>Exported Data</h1>
    #sheet.toHtml()#
</body>
</html>
';

fileWrite( "styled-report.html", html );

📦 Export to Array

Array of Arrays

sheet = Spreadsheet( "data.xlsx" );

// Get all data as array of arrays
data = sheet.toArray( includeHeaderRow = false );

// Result: [ ["John", "Engineering"], ["Jane", "Marketing"] ]

// Access data
for ( row in data ) {
    writeOutput( "#row[1]# - #row[2]#<br>" );
}

Array of Structs

sheet = Spreadsheet( "employees.xlsx" );

// Convert to array of structs (default behavior)
employees = sheet.toArray();

// Result: [
//     { "Name": "John", "Department": "Engineering" },
//     { "Name": "Jane", "Department": "Marketing" }
// ]

// Access using keys
for ( employee in employees ) {
    writeOutput( "#employee.Name# works in #employee.Department#<br>" );
}

Array with Specific Columns

sheet = Spreadsheet( "data.xlsx" );

// Get specific columns
names = sheet.getColumn( 1 );       // First column
salaries = sheet.getColumn( 3 );    // Third column

// Combine into new structure
data = names.map( ( name, index ) => {
    return {
        "name": name,
        "salary": salaries[ index ]
    };
} );

🔄 Batch Export

Export All Sheets to CSV

sheet = Spreadsheet( "workbook.xlsx" );

// Get all sheet names
sheetNames = sheet.getSheetNames();

// Export each sheet
for ( sheetName in sheetNames ) {
    sheet.selectSheet( sheetName );
    csv = sheet.toCSV();

    // Save with sheet name
    fileName = "#sheetName#.csv";
    fileWrite( fileName, csv );
}

Export Multiple Formats

sheet = Spreadsheet( "data.xlsx" );

// Export to multiple formats
formats = [
    { extension: "csv", content: sheet.toCSV() },
    { extension: "json", content: sheet.toJson() },
    { extension: "html", content: sheet.toHtml() }
];

// Save each format
for ( format in formats ) {
    fileWrite( "export.#format.extension#", format.content );
}

🎯 Export Patterns

Pattern: Filtered Export

// Load data
employees = Spreadsheet( "employees.xlsx" ).toArray();

// Filter data
engineeringStaff = employees.filter( ( emp ) => emp.Department == "Engineering" );

// Export filtered data
Spreadsheet()
    .setRowData( 1, [ "Name", "Department", "Salary" ] )
    .addRows( engineeringStaff.map( ( emp ) =>
        [ emp.Name, emp.Department, emp.Salary ]
    ) )
    .save( "engineering-staff.xlsx" );

Pattern: Transformed Export

// Load data
products = Spreadsheet( "products.xlsx" ).toArray();

// Transform data
transformed = products.map( ( product ) => {
    return {
        "code": product.ProductCode,
        "name": product.ProductName,
        "price": dollarFormat( product.Price ),
        "available": product.Stock > 0 ? "Yes" : "No"
    };
} );

// Export as JSON
fileWrite( "products-api.json", serializeJSON( transformed, true ) );

Pattern: Export with Metadata

// Load and process data
sheet = Spreadsheet( "sales.xlsx" );
data = sheet.toArray();

// Create export package
export = {
    "metadata": {
        "exportDate": dateFormat( now(), "yyyy-mm-dd HH:nn:ss" ),
        "recordCount": data.len(),
        "source": "sales.xlsx",
        "version": "1.0"
    },
    "data": data
};

fileWrite( "sales-export.json", serializeJSON( export, true ) );

Pattern: Chunked Export

// Load large dataset
sheet = Spreadsheet( "large-file.xlsx" );
allData = sheet.toArray();

// Export in chunks
chunkSize = 1000;
chunks = ceiling( allData.len() / chunkSize );

for ( i = 1; i <= chunks; i++ ) {
    startIdx = ( i - 1 ) * chunkSize + 1;
    endIdx = min( i * chunkSize, allData.len() );

    chunk = allData.slice( startIdx, endIdx );

    // Export chunk
    json = serializeJSON( chunk );
    fileWrite( "chunk-#i#.json", json );
}

📊 Export for APIs

REST API Response

// Load data
products = Spreadsheet( "products.xlsx" ).toArray();

// Transform for API
apiResponse = {
    "success": true,
    "count": products.len(),
    "data": products.map( ( p ) => {
        return {
            "id": p.ID,
            "name": p.Name,
            "price": numberFormat( p.Price, "0.00" ),
            "inStock": p.Stock > 0
        };
    } )
};

// Return as JSON (in API handler)
return serializeJSON( apiResponse );

GraphQL Response Format

// Load data
employees = Spreadsheet( "employees.xlsx" ).toArray();

// Format for GraphQL
graphQLResponse = {
    "data": {
        "employees": employees.map( ( emp ) => {
            return {
                "id": hash( emp.Email ),
                "name": emp.Name,
                "department": {
                    "name": emp.Department
                },
                "salary": emp.Salary
            };
        } )
    }
};

return serializeJSON( graphQLResponse );

📦 Export to Binary

Get Spreadsheet as Binary

sheet = Spreadsheet()
    .setRowData( 1, [ "Data" ] )
    .addRow( [ "Value" ] );

// Get as binary
binary = sheet.toBinary();

// Save binary
fileWrite( "output.xlsx", binary );

// Or serve as download
cfheader( name="Content-Disposition", value='attachment; filename="export.xlsx"' );
cfcontent( type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", variable=binary );

Stream Binary Download

// Create spreadsheet
sheet = Spreadsheet();
sheet.setRowData( 1, [ "Report Data" ] );
// ... add data ...

// Get binary
binary = sheet.toBinary();

// Set headers for download
cfheader( name="Content-Type", value="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" );
cfheader( name="Content-Disposition", value='attachment; filename="report-#dateFormat(now(),"yyyy-mm-dd")#.xlsx"' );
cfheader( name="Content-Length", value=arrayLen( binary ) );

// Send binary
cfcontent( variable=binary, reset=true );

🔄 Import and Export Pipeline

CSV to Excel

// Read CSV file
csvData = fileRead( "input.csv" );
lines = csvData.split( chr(10) );

// Create Excel file
sheet = Spreadsheet( "output.xlsx" );

for ( line in lines ) {
    row = line.split( "," );
    sheet.addRow( row );
}

// Format and save
sheet.formatRow( 1, { bold: true } )
    .autoSizeColumns()
    .save();

Excel to Database

// Load Excel file
employees = Spreadsheet( "new-employees.xlsx" ).toArray();

// Insert into database
for ( emp in employees ) {
    queryExecute(
        "INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)",
        [ emp.Name, emp.Department, emp.Salary ]
    );
}

Database to Excel Export

// Query database
employees = queryExecute(
    "SELECT name, department, salary, hire_date FROM employees ORDER BY name"
);

// Export to Excel
Spreadsheet( "employee-export.xlsx" )
    .addRows( employees, includeColumnNames = true )
    .formatRow( 1, {
        bold: true,
        fgcolor: "darkblue",
        fontColor: "white"
    } )
    .formatColumn( 3, { dataformat: "$#,##0.00" } )
    .formatColumn( 4, { dataformat: "mm/dd/yyyy" } )
    .autoSizeColumns()
    .save();

// Also export to CSV
csv = Spreadsheet( "employee-export.xlsx" ).toCSV();
fileWrite( "employee-export.csv", csv );

📚 Next Steps

Advanced Features Examples Fluent API

PreviousFormulas NextLarge File Streaming

Last updated 9 months ago

Was this helpful?