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 3 months ago

Was this helpful?

hashtag📄 Export to CSV

hashtagBasic CSV Export

hashtagCSV with Custom Delimiter

hashtagCSV from Specific Sheet

hashtagCSV with Headers

hashtag🔤 Export to JSON

hashtagArray of Objects

hashtagPretty-Printed JSON

hashtagJSON with Custom Structure

hashtag🗄️ Export to Query

hashtagBasic Query Conversion

hashtagQuery with Custom Column Names

hashtagQuery from Specific Range

hashtag🌐 Export to HTML

hashtagSimple HTML Table

hashtagHTML with Custom Styling

hashtag📦 Export to Array

hashtagArray of Arrays

hashtagArray of Structs

hashtagArray with Specific Columns

hashtag🔄 Batch Export

hashtagExport All Sheets to CSV

hashtagExport Multiple Formats

hashtag🎯 Export Patterns

hashtagPattern: Filtered Export

hashtagPattern: Transformed Export

hashtagPattern: Export with Metadata

hashtagPattern: Chunked Export

hashtag📊 Export for APIs

hashtagREST API Response

hashtagGraphQL Response Format

hashtag📦 Export to Binary

hashtagGet Spreadsheet as Binary

hashtagStream Binary Download

hashtag🔄 Import and Export Pipeline

hashtagCSV to Excel

hashtagExcel to Database

hashtagDatabase to Excel Export

hashtag📚 Next Steps