cfspreadsheet

Read  
<cfspreadsheet   
    action="read" 
   src = "filepath" 
    columns = "range" 
    columnnames = "comma-delimited list" 
    excludeHeaderRow = "true | false" 
    format = "CSV|HTML" 
    headerrow = "row number" 
    name = "text" 
    query = "query name" 
    rows = "range" 
    sheet = "number" 
    sheetname = "text">  
   
Update  
<cfspreadsheet  
    action="update" 
    filename = "filepath" 
    format = "csv" 
    name = "text" 
    password = "password" 
    query = "query name" 
    sheetname = "text" >  
   
Write  
<cfspreadsheet   
    action="write" 
    autosize="true|false" 
    filename = "filepath" 
    format = "csv" 
    name = "text" 
    overwrite = "true | false" 
    password = "password" 
    query = "queryname" 
    sheetname = "text" 
    autosize = "true | false" >

<!--- Read data from two datasource tables. ---> 
<cfquery 
       name="courses" datasource="cfdocexamples"
       cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 
       SELECT CORNUMBER,DEPT_ID,COURSE_ID,CORNAME 
       FROM COURSELIST 
</cfquery> 
  
<cfquery 
       name="centers" datasource="cfdocexamples"
       cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 
       SELECT * 
       FROM CENTERS 
</cfquery> 
      
<cfscript> 
    //Use an absolute path for the files. ---> 
       theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
    theFile=theDir & "courses.xls"; 
    //Create two empty ColdFusion spreadsheet objects. ---> 
    theSheet = SpreadsheetNew("CourseData"); 
    theSecondSheet = SpreadsheetNew("CentersData"); 
    //Populate each object with a query. ---> 
    SpreadsheetAddRows(theSheet,courses); 
    SpreadsheetAddRows(theSecondSheet,centers); 
</cfscript> 
  
<!--- Write the two sheets to a single file ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" 
    sheetname="courses" overwrite=true> 
<cfspreadsheet action="update" filename="#theFile#" name="theSecondSheet"
    sheetname="centers"> 
  
<!--- Read all or part of the file into a spreadsheet object, CSV string, 
      HTML string, and query. ---> 
<cfspreadsheet action="read" src="#theFile#" sheetname="courses" name="spreadsheetData"> 
<cfspreadsheet action="read" src="#theFile#" sheet=1 rows="3,4" format="csv" name="csvData"> 
<cfspreadsheet action="read" src="#theFile#" format="html" rows="5-10" name="htmlData"> 
<cfspreadsheet action="read" src="#theFile#" sheetname="centers" query="queryData"> 
  
<h3>First sheet row 3 read as a CSV variable</h3> 
<cfdump var="#csvData#"> 
  
<h3>Second sheet rows 5-10 read as an HTML variable</h3> 
<cfdump var="#htmlData#"> 
  
<h3>Second sheet read as a query variable</h3> 
<cfdump var="#queryData#"> 
  
<!--- Modify the courses sheet. ---> 
<cfscript> 
    SpreadsheetAddRow(spreadsheetData,"03,ENGL,230,Poetry 1",8,1); 
    SpreadsheetAddColumn(spreadsheetData, 
    "Basic,Intermediate,Advanced,Basic,Intermediate,Advanced,Basic,Intermediate,Advanced", 
    3,2,true); 
</cfscript> 
  
<!--- Write the updated Courses sheet to a new XLS file ---> 
<cfspreadsheet action="write" filename="#theDir#updatedFile.xls" name="spreadsheetData" 
    sheetname="courses" overwrite=true> 
<!--- Write an XLS file containing the data in the CSV variable. --->     
<cfspreadsheet action="write" filename="#theDir#dataFromCSV.xls" name="CSVData" 
    format="csv" sheetname="courses" overwrite=true>

<cfscript>
 cfhttp( name="mydata", url="https://raw.githubusercontent.com/sauravg94/test-repo/master/MOCK_DATA.csv", firstrowasheaders="true" ,method="GET");
 writedump(mydata);
</cfscript>

<cfscript> 
    //Use an absolute path for the files. 
    theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
    theFile=theDir & "mock_data.xlsx"; 
    //Create an empty ColdFusion spreadsheet objects. 
    theSheet = SpreadsheetNew(true); 
    //Populate the object with data fetched with cfhttp
    SpreadsheetAddRows(theSheet,mydata,1);
</cfscript>

<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheetname="mock_data" overwrite=true> 

<cfspreadsheet action="read" src="#theFile#" sheetname="mock_data" name="spreadsheetData">

<cfspreadsheet action="read" src="#theFile#" sheet=1 rows="100-200" format="csv" name="csvData">

<cfspreadsheet action="read" src="#theFile#" format="html" rows="5-10" name="htmlData">

<cfspreadsheet action="read" src="#theFile#" sheetname="mock_data" query="queryData">

<cfdump var="#spreadsheetData#" >

<cfoutput >
 #csvData#
</cfoutput>

<cfdump var="#htmlData#" >

<cfdump var="#queryData#" >

<cfspreadsheet action="read" src="#expandPath('employee.xlsx')#" name="employee" password="abc123">
<cfdump var="#employee#" label="Result of cfspreadsheet read action (with password attribute)">

cfspreadsheet action="read" src="#expandPath('employee.xlsx')#" name="employee">
<cfdump var="#employee#" label="Result of cfspreadsheet read action (without password attribute)">

<cfspreadsheet action="read" src="#expandPath('employee.xls')#" name="employee" password="1234">
<cfdump var="#employee#" label="Result of cfspreadsheet read action (with incorrect password attribute)">

<cfspreadsheet action="read" src="#expandPath('employee.xlsx')#" name="employee" >
<cfspreadsheet
        action="update"
        filename = "#expandPath('employee.xlsx')#"
        name="employee"
        sheetname = "sheet1" 
        password="abc123" > 
<cfdump var="#var#" >

<cfscript>
    // Create sample order data (in production, this would be a database query)
    ordersQuery = queryNew(
        "OrderID,OrderDate,CustomerName,ProductName,Quantity,UnitPrice,Total,Status",
        "integer,date,varchar,varchar,integer,decimal,decimal,varchar"
    );
    
    queryAddRow(ordersQuery, [
        {
            OrderID: 1001,
            OrderDate: dateAdd("d", -7, now()),
            CustomerName: "Acme Corporation",
            ProductName: "Laptop Computer",
            Quantity: 5,
            UnitPrice: 1299.99,
            Total: 6499.95,
            Status: "Shipped"
        },
        {
            OrderID: 1002,
            OrderDate: dateAdd("d", -6, now()),
            CustomerName: "Tech Solutions Inc",
            ProductName: "Wireless Mouse",
            Quantity: 25,
            UnitPrice: 29.99,
            Total: 749.75,
            Status: "Delivered"
        },
        {
            OrderID: 1003,
            OrderDate: dateAdd("d", -5, now()),
            CustomerName: "Digital Dynamics",
            ProductName: "USB-C Hub",
            Quantity: 10,
            UnitPrice: 49.99,
            Total: 499.90,
            Status: "Processing"
        },
        {
            OrderID: 1004,
            OrderDate: dateAdd("d", -4, now()),
            CustomerName: "Global Enterprises",
            ProductName: "Wireless Keyboard",
            Quantity: 15,
            UnitPrice: 79.99,
            Total: 1199.85,
            Status: "Shipped"
        },
        {
            OrderID: 1005,
            OrderDate: dateAdd("d", -3, now()),
            CustomerName: "Innovative Systems",
            ProductName: "Monitor 27-inch",
            Quantity: 8,
            UnitPrice: 349.99,
            Total: 2799.92,
            Status: "Delivered"
        }
    ]);
    
    // Set file location (use expandPath to get web-accessible directory)
    // In production, configure a specific reports directory with proper permissions
    reportDir = expandPath("./reports/");
    
    // Create directory if it doesn't exist (with error handling)
    try {
        if (!directoryExists(reportDir)) {
            directoryCreate(reportDir);
        }
        canWriteFiles = true;
    } catch (any e) {
        // If we can't create directory, use memory-only mode
        canWriteFiles = false;
        reportDir = "Unable to create directory - using memory mode";
    }
    
    reportFileName = "Weekly_Sales_Report_" & dateFormat(now(), "yyyy-mm-dd") & ".xlsx";
    fullReportPath = canWriteFiles ? reportDir & reportFileName : "";
</cfscript>

<h2>Database Report Export to Excel</h2>

<!--- Check if we can write files --->
<cfif NOT canWriteFiles>
    <div style="background: ##fff3e0; border: 2px solid ##ff9800; padding: 15px; margin: 15px 0; border-radius: 5px;">
        <h4 style="color: orange; margin: 0;">⚠ File System Access Limited</h4>
        <p>Unable to write files to disk. This demo will show in-memory spreadsheet operations instead.</p>
        <p><strong>For production:</strong> Configure a directory with proper write permissions in your application.</p>
    </div>
</cfif>

<!--- Display the query data --->
<h3>Order Data (Query Results)</h3>
<cfdump 
    var="#ordersQuery#" 
    label="Orders to Export"
    expand="yes">

<hr>

<!--- Method 1: Simple export - Query directly to Excel --->
<h3>Method 1: Simple Query Export</h3>

<cfif canWriteFiles>
    <cftry>
        <cfspreadsheet 
            action="write" 
            query="#ordersQuery#"
            filename="#fullReportPath#"
            sheetname="Weekly Orders"
            overwrite="true">
        
        <div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 15px; margin: 15px 0; border-radius: 5px;">
            <h4 style="color: green; margin: 0;">✓ Excel File Created Successfully</h4>
            <p><strong>Location:</strong> #fullReportPath#</p>
            <p><strong>Records Exported:</strong> #ordersQuery.recordCount#</p>
        </div>
        
        <cfcatch type="any">
            <div style="background: ##ffebee; border: 2px solid ##f44336; padding: 15px; margin: 15px 0; border-radius: 5px;">
                <h4 style="color: red; margin: 0;">✗ Error Writing File</h4>
                <p><strong>Error:</strong> #cfcatch.message#</p>
                <p>The application may not have permission to write to this directory.</p>
            </div>
            <cfset canWriteFiles = false>
        </cfcatch>
    </cftry>
<cfelse>
    <div style="background: ##e3f2fd; border: 2px solid ##2196f3; padding: 15px; margin: 15px 0; border-radius: 5px;">
        <h4>📝 Code Example (File Write Disabled)</h4>
        <pre style="background: ##f5f5f5; padding: 10px; border-radius: 5px;">
&lt;cfspreadsheet 
    action="write" 
    query="##ordersQuery##"
    filename="##fullReportPath##"
    sheetname="Weekly Orders"
    overwrite="true"&gt;</pre>
        <p>This would create an Excel file with all query data when file permissions are available.</p>
    </div>
</cfif>

<!--- Method 2: Advanced export with formatting --->
<h3>Method 2: Formatted Excel Export (In-Memory)</h3>

<cfscript>
    // Create a new spreadsheet object (true = .xlsx format)
    // This works in memory even without file system access
    spreadsheetObj = SpreadsheetNew("Weekly Sales Report", true);
    
    // Add the query data starting at row 2 (leaving room for title)
    SpreadsheetAddRow(spreadsheetObj, "WEEKLY SALES REPORT - " & dateFormat(now(), "mmmm dd, yyyy"));
    SpreadsheetAddRow(spreadsheetObj, ""); // Blank row
    
    // Add query data
    SpreadsheetAddRows(spreadsheetObj, ordersQuery);
    
    // Format the title row
    SpreadsheetFormatRow(spreadsheetObj, 
        {
            bold: true,
            fontsize: 14,
            alignment: "center"
        }, 
        1
    );
    
    // Format the header row (row 3, after title and blank row)
    SpreadsheetFormatRow(spreadsheetObj, 
        {
            bold: true,
            fgcolor: "light_blue",
            alignment: "center"
        }, 
        3
    );
    
    // Set column widths for better readability
    SpreadsheetSetColumnWidth(spreadsheetObj, 1, 12);  // OrderID
    SpreadsheetSetColumnWidth(spreadsheetObj, 2, 15);  // OrderDate
    SpreadsheetSetColumnWidth(spreadsheetObj, 3, 25);  // CustomerName
    SpreadsheetSetColumnWidth(spreadsheetObj, 4, 25);  // ProductName
    SpreadsheetSetColumnWidth(spreadsheetObj, 5, 12);  // Quantity
    SpreadsheetSetColumnWidth(spreadsheetObj, 6, 12);  // UnitPrice
    SpreadsheetSetColumnWidth(spreadsheetObj, 7, 12);  // Total
    SpreadsheetSetColumnWidth(spreadsheetObj, 8, 15);  // Status
    
    // Try to save formatted spreadsheet
    formattedFileName = "Weekly_Sales_Report_Formatted_" & dateFormat(now(), "yyyy-mm-dd") & ".xlsx";
    formattedPath = canWriteFiles ? reportDir & formattedFileName : "";
    
    if (canWriteFiles) {
        try {
            SpreadsheetWrite(spreadsheetObj, formattedPath, true);
            formattedSaved = true;
        } catch (any e) {
            formattedSaved = false;
        }
    } else {
        formattedSaved = false;
    }
</cfscript>

<cfif formattedSaved>
    <div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 15px; margin: 15px 0; border-radius: 5px;">
        <h4 style="color: green; margin: 0;">✓ Formatted Excel File Created</h4>
        <p><strong>Location:</strong> #formattedPath#</p>
        <p><strong>Features:</strong> Title row, formatted headers, adjusted column widths</p>
    </div>
<cfelse>
    <div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 15px; margin: 15px 0; border-radius: 5px;">
        <h4 style="color: green; margin: 0;">✓ Spreadsheet Object Created in Memory</h4>
        <p><strong>Features Applied:</strong> Title row, formatted headers, adjusted column widths</p>
        <p><em>In production with file permissions, this would be saved to disk.</em></p>
        <p><strong>Alternative:</strong> Use <code>cfheader/cfcontent</code> to stream directly to browser for download.</p>
    </div>
</cfif>

<!--- Method 3: Multiple sheets in one workbook --->
<h3>Method 3: Multi-Sheet Workbook (In-Memory)</h3>

<cfscript>
    // Create summary data
    summaryQuery = queryNew(
        "Status,OrderCount,TotalAmount",
        "varchar,integer,decimal"
    );
    
    queryAddRow(summaryQuery, [
        {Status: "Shipped", OrderCount: 2, TotalAmount: 7699.80},
        {Status: "Delivered", OrderCount: 2, TotalAmount: 3549.67},
        {Status: "Processing", OrderCount: 1, TotalAmount: 499.90}
    ]);
    
    // Create main workbook
    workbook = SpreadsheetNew("Orders", true);
    SpreadsheetAddRows(workbook, ordersQuery);
    
    // Format headers
    SpreadsheetFormatRow(workbook, {bold: true, fgcolor: "light_blue"}, 1);
    
    // Add summary sheet
    SpreadsheetCreateSheet(workbook, "Summary");
    SpreadsheetSetActiveSheet(workbook, "Summary");
    SpreadsheetAddRows(workbook, summaryQuery);
    SpreadsheetFormatRow(workbook, {bold: true, fgcolor: "light_green"}, 1);
    
    // Try to save multi-sheet workbook
    multiSheetFileName = "Weekly_Sales_Report_MultiSheet_" & dateFormat(now(), "yyyy-mm-dd") & ".xlsx";
    multiSheetPath = canWriteFiles ? reportDir & multiSheetFileName : "";
    
    if (canWriteFiles) {
        try {
            SpreadsheetWrite(workbook, multiSheetPath, true);
            multiSheetSaved = true;
        } catch (any e) {
            multiSheetSaved = false;
        }
    } else {
        multiSheetSaved = false;
    }
</cfscript>

<cfif multiSheetSaved>
    <div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 15px; margin: 15px 0; border-radius: 5px;">
        <h4 style="color: green; margin: 0;">✓ Multi-Sheet Workbook Created</h4>
        <p><strong>Location:</strong> #multiSheetPath#</p>
        <p><strong>Sheets:</strong> Orders (detailed data) + Summary (aggregated data)</p>
    </div>
<cfelse>
    <div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 15px; margin: 15px 0; border-radius: 5px;">
        <h4 style="color: green; margin: 0;">✓ Multi-Sheet Workbook Created in Memory</h4>
        <p><strong>Sheets:</strong> Orders (detailed data) + Summary (aggregated data)</p>
        <p><em>In production with file permissions, this would be saved to: #multiSheetFileName#</em></p>
    </div>
</cfif>

<!--- Display summary data --->
<h3>Summary Data</h3>
<cfdump 
    var="#summaryQuery#" 
    label="Order Summary by Status"
    expand="yes">

<!--- Calculate totals --->
<cfscript>
    reportStats = {
        "totalOrders": ordersQuery.recordCount,
        "totalRevenue": dollarFormat(arraySum(ordersQuery["Total"])),
        "reportDate": dateTimeFormat(now(), "yyyy-mm-dd HH:nn:ss"),
        "filesGenerated": 3,
        "reportDirectory": reportDir
    };
</cfscript>

<h3>Report Generation Summary</h3>
<cfdump 
    var="#reportStats#" 
    label="Export Statistics"
    expand="yes">

<!--- Example: Download link --->
<div style="background: ##e3f2fd; border-left: 4px solid ##2196f3; padding: 15px; margin: 20px 0;">
    <h4>📥 Generated Reports:</h4>
    <ul>
        <li><strong>Simple Export:</strong> #reportFileName#</li>
        <li><strong>Formatted Export:</strong> #formattedFileName#</li>
        <li><strong>Multi-Sheet Export:</strong> #multiSheetFileName#</li>
    </ul>
    <cfif canWriteFiles>
        <p><strong>Location:</strong> #reportDir#</p>
    <cfelse>
        <p><strong>Status:</strong> Created in memory (file system access not available)</p>
        <p><strong>Alternative:</strong> Stream directly to browser using cfheader/cfcontent</p>
    </cfif>
</div>

<!--- Show download example --->
<div style="background: ##f5f5f5; border: 1px solid ##ccc; padding: 15px; margin: 20px 0; border-radius: 5px;">
    <h4>💡 Alternative: Stream Excel to Browser (No File System Required)</h4>
    <p>When file system access is limited, stream the spreadsheet directly to the browser:</p>
    <pre style="background: ##fff; padding: 10px; border: 1px solid ##ddd; border-radius: 3px; overflow-x: auto;">
&lt;cfscript&gt;
    // Create spreadsheet in memory
    sheet = SpreadsheetNew("Report", true);
    SpreadsheetAddRows(sheet, myQuery);
    
    // Get binary data
    binaryData = SpreadsheetReadBinary(sheet);
&lt;/cfscript&gt;

&lt;cfheader name="Content-Disposition" value="attachment; filename=report.xlsx"&gt;
&lt;cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" 
           variable="##binaryData##" reset="true"&gt;</pre>
</div>

<cfscript>
    // Set upload directory (use expandPath for web-accessible directory)
    uploadDir = expandPath("./employee-uploads/");
    
    // Create directory if it doesn't exist (with error handling)
    try {
        if (!directoryExists(uploadDir)) {
            directoryCreate(uploadDir);
        }
        canUploadFiles = true;
    } catch (any e) {
        // If we can't create directory, disable file upload
        canUploadFiles = false;
        uploadDir = "File system access not available";
    }
</cfscript>

<h2>Employee Data Import from Excel</h2>

<!--- Check if file upload is available --->
<cfif NOT canUploadFiles>
    <div style="background: ##fff3e0; border: 2px solid ##ff9800; padding: 20px; margin: 20px 0; border-radius: 5px;">
        <h3 style="color: orange; margin: 0;">⚠ File Upload Not Available</h3>
        <p>The application doesn't have permission to create upload directories.</p>
        <p><strong>For production:</strong> Configure a directory with proper write permissions.</p>
        <p><strong>Alternative:</strong> This demo will show how the import process works with sample data instead.</p>
    </div>
    
    <!--- Set flag to use sample data --->
    <cfset useSampleData = true>
<cfelse>
    <cfset useSampleData = false>
</cfif>

<!--- File upload form --->
<cfif NOT isDefined("form.employeeFile") AND canUploadFiles>
    
    <div style="background: ##e3f2fd; border: 2px solid ##2196f3; padding: 20px; margin: 20px 0; border-radius: 5px;">
        <h3>Upload Employee Data</h3>
        <p>Please upload an Excel file (.xlsx or .xls) containing employee information.</p>
        <p><strong>Required columns:</strong> EmployeeID, FirstName, LastName, Email, Department, Position, HireDate, Salary</p>
    </div>
    
    <form method="post" enctype="multipart/form-data">
        <div style="margin: 20px 0;">
            <label for="employeeFile"><strong>Select Excel File:</strong></label><br>
            <input type="file" name="employeeFile" id="employeeFile" accept=".xlsx,.xls" required>
        </div>
        <div>
            <button type="submit" style="padding: 10px 20px; background: ##2196f3; color: white; border: none; border-radius: 5px; cursor: pointer;">
                Upload and Import
            </button>
        </div>
    </form>
    
    <!--- Show sample data format --->
    <h3>Sample Data Format</h3>
    <cfscript>
        sampleData = queryNew(
            "EmployeeID,FirstName,LastName,Email,Department,Position,HireDate,Salary",
            "varchar,varchar,varchar,varchar,varchar,varchar,date,decimal"
        );
        
        queryAddRow(sampleData, [
            {
                EmployeeID: "EMP001",
                FirstName: "John",
                LastName: "Smith",
                Email: "john.smith@company.com",
                Department: "Engineering",
                Position: "Software Developer",
                HireDate: now(),
                Salary: 85000
            },
            {
                EmployeeID: "EMP002",
                FirstName: "Jane",
                LastName: "Doe",
                Email: "jane.doe@company.com",
                Department: "Marketing",
                Position: "Marketing Manager",
                HireDate: now(),
                Salary: 75000
            }
        ]);
    </cfscript>
    
    <cfdump 
        var="#sampleData#" 
        label="Expected Excel Format"
        expand="yes">

<cfelseif useSampleData>
    
    <!--- Process with sample data when file upload is not available --->
    <h3>Processing Sample Data (File Upload Not Available)...</h3>
    
    <cfscript>
        // Use the sample data we created earlier
        employeeData = sampleData;
    </cfscript>
    
    <div style="background: ##e3f2fd; border: 2px solid ##2196f3; padding: 15px; margin: 15px 0; border-radius: 5px;">
        <h4 style="color: blue; margin: 0;">📝 Using Sample Data</h4>
        <p>Since file upload is not available, demonstrating with sample employee data.</p>
        <p><strong>Records:</strong> #employeeData.recordCount#</p>
    </div>
    
    <!--- Display imported data --->
    <h3>Sample Employee Data</h3>
    <cfdump 
        var="#employeeData#" 
        label="Data for Processing (#employeeData.recordCount# records)"
        expand="yes"
        top="10">
    
    <!--- Continue with validation process (skip to line after file read) --->
    <cfset continueWithValidation = true>

<cfelse>
    
    <!--- Process file upload --->
    <h3>Processing Upload...</h3>
    
    <cftry>
        <!--- Upload the file --->
        <cffile 
            action="upload" 
            fileField="employeeFile" 
            destination="#uploadDir#" 
            nameConflict="makeunique"
            result="uploadResult">
        
        <cfscript>
            uploadedFile = uploadResult.serverDirectory & "/" & uploadResult.serverFile;
        </cfscript>
        
        <div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 15px; margin: 15px 0; border-radius: 5px;">
            <h4 style="color: green; margin: 0;">✓ File Uploaded Successfully</h4>
            <p><strong>Filename:</strong> #uploadResult.clientFile#</p>
            <p><strong>Size:</strong> #numberFormat(uploadResult.fileSize / 1024, "0.00")# KB</p>
        </div>
        
        <!--- Read Excel file into query --->
        <cfspreadsheet 
            action="read" 
            src="#uploadedFile#"
            query="employeeData"
            headerrow="1">
        
        <!--- Display imported data --->
        <h3>Imported Employee Data</h3>
        <cfdump 
            var="#employeeData#" 
            label="Data from Excel (#employeeData.recordCount# records)"
            expand="yes"
            top="10">
        
        <cfset continueWithValidation = true>
        
        <cfcatch type="any">
            <div style="background: ##ffebee; border: 2px solid ##f44336; padding: 20px; margin: 20px 0; border-radius: 5px;">
                <h3 style="color: red; margin: 0;">✗ Upload Failed</h3>
                <p><strong>Error:</strong> #cfcatch.message#</p>
                <p><strong>Detail:</strong> #cfcatch.detail#</p>
                <p>Please ensure the file is a valid Excel format with the correct column structure.</p>
            </div>
            
            <p><a href="?">Try Again</a></p>
            <cfset continueWithValidation = false>
        </cfcatch>
    </cftry>
    
</cfif>

<!--- Continue with validation if we have data --->
<cfif (isDefined("continueWithValidation") AND continueWithValidation) OR useSampleData>
    
    <!--- Validate and process data --->
        <cfscript>
            validRecords = [];
            invalidRecords = [];
            duplicateRecords = [];
            processedCount = 0;
            
            // Simulate existing employee IDs (in production, query database)
            existingEmployeeIDs = ["EMP001", "EMP005"];
            
            // Process each row
            for (row = 1; row <= employeeData.recordCount; row++) {
                employee = {
                    "employeeID": employeeData.EmployeeID[row],
                    "firstName": employeeData.FirstName[row],
                    "lastName": employeeData.LastName[row],
                    "email": employeeData.Email[row],
                    "department": employeeData.Department[row],
                    "position": employeeData.Position[row],
                    "hireDate": employeeData.HireDate[row],
                    "salary": employeeData.Salary[row],
                    "rowNumber": row
                };
                
                // Validation
                isValid = true;
                validationErrors = [];
                
                // Check required fields
                if (len(trim(employee.employeeID)) == 0) {
                    arrayAppend(validationErrors, "Employee ID is required");
                    isValid = false;
                }
                
                if (len(trim(employee.firstName)) == 0) {
                    arrayAppend(validationErrors, "First Name is required");
                    isValid = false;
                }
                
                if (len(trim(employee.email)) == 0) {
                    arrayAppend(validationErrors, "Email is required");
                    isValid = false;
                }
                
                // Check for duplicates
                if (arrayFind(existingEmployeeIDs, employee.employeeID) > 0) {
                    arrayAppend(validationErrors, "Employee ID already exists");
                    employee.errors = validationErrors;
                    arrayAppend(duplicateRecords, employee);
                    continue;
                }
                
                // Categorize record
                if (isValid) {
                    arrayAppend(validRecords, employee);
                    processedCount++;
                } else {
                    employee.errors = validationErrors;
                    arrayAppend(invalidRecords, employee);
                }
            }
            
            // Import summary
            importSummary = {
                "totalRecords": employeeData.recordCount,
                "validRecords": arrayLen(validRecords),
                "invalidRecords": arrayLen(invalidRecords),
                "duplicateRecords": arrayLen(duplicateRecords),
                "importDate": dateTimeFormat(now(), "yyyy-mm-dd HH:nn:ss"),
                "filename": uploadResult.clientFile
            };
        </cfscript>
        
        <!--- Display import summary --->
        <h3>Import Summary</h3>
        <cfdump 
            var="#importSummary#" 
            label="Import Results"
            expand="yes">
        
        <!--- Show valid records --->
        <cfif arrayLen(validRecords) GT 0>
            <h3 style="color: green;">✓ Valid Records (Ready to Import)</h3>
            <cfdump 
                var="#validRecords#" 
                label="Records that passed validation"
                expand="yes">
            
            <div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 15px; margin: 15px 0; border-radius: 5px;">
                <h4 style="color: green; margin: 0;">Ready for Database Import</h4>
                <p>#arrayLen(validRecords)# employee record(s) validated successfully.</p>
                <p><em>In production, these would be inserted into the database.</em></p>
            </div>
        </cfif>
        
        <!--- Show invalid records --->
        <cfif arrayLen(invalidRecords) GT 0>
            <h3 style="color: red;">✗ Invalid Records (Errors Found)</h3>
            <cfdump 
                var="#invalidRecords#" 
                label="Records with validation errors"
                expand="yes">
        </cfif>
        
        <!--- Show duplicate records --->
        <cfif arrayLen(duplicateRecords) GT 0>
            <h3 style="color: orange;">⚠ Duplicate Records (Already Exist)</h3>
            <cfdump 
                var="#duplicateRecords#" 
                label="Records with duplicate Employee IDs"
                expand="yes">
        </cfif>
        
        <!--- Success/Error summary --->
        <cfif arrayLen(invalidRecords) EQ 0 AND arrayLen(duplicateRecords) EQ 0>
            <div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 20px; margin: 20px 0; border-radius: 5px;">
                <h3 style="color: green; margin: 0;">🎉 Import Successful!</h3>
                <p>All #importSummary.totalRecords# employee record(s) were validated successfully and are ready for database import.</p>
            </div>
        <cfelse>
            <div style="background: ##fff3e0; border: 2px solid ##ff9800; padding: 20px; margin: 20px 0; border-radius: 5px;">
                <h3 style="color: orange; margin: 0;">⚠ Import Completed with Issues</h3>
                <p><strong>Valid:</strong> #arrayLen(validRecords)# records</p>
                <p><strong>Invalid:</strong> #arrayLen(invalidRecords)# records</p>
                <p><strong>Duplicates:</strong> #arrayLen(duplicateRecords)# records</p>
                <p>Please correct the errors and re-upload the file.</p>
            </div>
        </cfif>
        
        <!--- Clean up uploaded file --->
        <cfif NOT useSampleData AND isDefined("uploadedFile") AND fileExists(uploadedFile)>
            <cftry>
                <cffile action="delete" file="#uploadedFile#">
                <cfcatch type="any">
                    <!--- Ignore cleanup errors --->
                </cfcatch>
            </cftry>
        </cfif>
        
        <cfif canUploadFiles>
            <p><a href="?" style="padding: 10px 20px; background: ##2196f3; color: white; text-decoration: none; border-radius: 5px; display: inline-block; margin-top: 20px;">
                Upload Another File
            </a></p>
        <cfelse>
            <p><a href="?" style="padding: 10px 20px; background: ##2196f3; color: white; text-decoration: none; border-radius: 5px; display: inline-block; margin-top: 20px;">
                View Demo Again
            </a></p>
        </cfif>
    
</cfif>

<!--- Show alternative when file system is not available --->
<cfif NOT canUploadFiles AND NOT isDefined("form.employeeFile") AND NOT useSampleData>
    <div style="background: ##f5f5f5; border: 1px solid ##ccc; padding: 15px; margin: 20px 0; border-radius: 5px;">
        <h4>💡 Alternative: Process Excel Data Without File Upload</h4>
        <p>When file system access is limited, you can still process Excel data by:</p>
        <ol>
            <li><strong>Reading from URL:</strong> Use cfhttp to fetch Excel files from external URLs</li>
            <li><strong>Binary Upload:</strong> Read uploaded file directly to memory without saving to disk</li>
            <li><strong>Database Storage:</strong> Store uploaded files in database BLOB fields</li>
        </ol>
        <pre style="background: ##fff; padding: 10px; border: 1px solid ##ddd; border-radius: 3px; overflow-x: auto;">
&lt;!--- Example: Read Excel from form without saving to disk ---&gt;
&lt;cffile action="readBinary" filefield="employeeFile" variable="excelBinary"&gt;
&lt;cfset tempFile = getTempFile(getTempDirectory(), "upload")&gt;
&lt;cffile action="write" file="##tempFile##" output="##excelBinary##"&gt;
&lt;cfspreadsheet action="read" src="##tempFile##" query="employeeData"&gt;
&lt;cffile action="delete" file="##tempFile##"&gt;</pre>
    </div>
</cfif>

<cfscript>
    // Generate sample sales data for the month
    salesData = queryNew(
        "SaleDate,Region,SalesRep,ProductCategory,ProductName,Quantity,UnitPrice,TotalSale",
        "date,varchar,varchar,varchar,varchar,integer,decimal,decimal"
    );
    
    regions = ["North", "South", "East", "West"];
    salesReps = ["Alice Johnson", "Bob Martinez", "Carol White", "David Lee", "Emma Davis"];
    categories = ["Electronics", "Software", "Hardware", "Accessories"];
    
    // Generate 20 sales records
    for (i = 1; i <= 20; i++) {
        saleDate = dateAdd("d", -randRange(1, 30), now());
        quantity = randRange(1, 10);
        unitPrice = randRange(50, 500);
        
        queryAddRow(salesData, {
            SaleDate: saleDate,
            Region: regions[randRange(1, arrayLen(regions))],
            SalesRep: salesReps[randRange(1, arrayLen(salesReps))],
            ProductCategory: categories[randRange(1, arrayLen(categories))],
            ProductName: "Product " & i,
            Quantity: quantity,
            UnitPrice: unitPrice,
            TotalSale: quantity * unitPrice
        });
    }
    
    // Calculate summary statistics
    totalSales = 0;
    for (i = 1; i <= salesData.recordCount; i++) {
        totalSales += salesData.TotalSale[i];
    }
    totalOrders = salesData.recordCount;
    averageOrderValue = totalSales / totalOrders;
    
    // Create summary by region
    regionSummary = queryNew(
        "Region,OrderCount,TotalSales,AvgOrderValue",
        "varchar,integer,decimal,decimal"
    );
    
    for (region in regions) {
        regionOrders = 0;
        regionTotal = 0;
        
        for (row = 1; row <= salesData.recordCount; row++) {
            if (salesData.Region[row] == region) {
                regionOrders++;
                regionTotal += salesData.TotalSale[row];
            }
        }
        
        queryAddRow(regionSummary, {
            Region: region,
            OrderCount: regionOrders,
            TotalSales: regionTotal,
            AvgOrderValue: regionOrders > 0 ? regionTotal / regionOrders : 0
        });
    }
    
    // Set report directory (use expandPath for web-accessible directory)
    reportDir = expandPath("./monthly-reports/");
    
    // Create directory if it doesn't exist (with error handling)
    try {
        if (!directoryExists(reportDir)) {
            directoryCreate(reportDir);
        }
        canWriteFiles = true;
    } catch (any e) {
        // If we can't create directory, work in memory only
        canWriteFiles = false;
        reportDir = "File system access not available";
    }
    
    reportMonth = dateFormat(now(), "mmmm yyyy");
    reportFileName = "Monthly_Sales_Report_" & dateFormat(now(), "yyyy-mm") & ".xlsx";
    reportPath = canWriteFiles ? reportDir & reportFileName : "";
</cfscript>

<h2>Monthly Sales Report Generation</h2>

<cfif NOT canWriteFiles>
    <div style="background: ##fff3e0; border: 2px solid ##ff9800; padding: 20px; margin: 20px 0; border-radius: 5px;">
        <h3 style="color: orange; margin: 0;">⚠ File System Access Limited</h3>
        <p>Unable to write files to disk. Report will be created in memory.</p>
        <p><strong>For production:</strong> Configure a directory with proper write permissions.</p>
        <p><strong>Alternative:</strong> Use cfheader/cfcontent to stream directly to browser.</p>
    </div>
</cfif>

<div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 20px; margin: 20px 0; border-radius: 5px;">
    <h3 style="margin: 0;">📊 Generating Report for #reportMonth#</h3>
</div>

<!--- Display source data --->
<h3>Sales Data</h3>
<cfdump 
    var="#salesData#" 
    label="Detailed Sales Transactions (#salesData.recordCount# records)"
    expand="no">

<h3>Regional Summary</h3>
<cfdump 
    var="#regionSummary#" 
    label="Sales by Region"
    expand="yes">

<hr>

<!--- Create formatted Excel workbook --->
<cfscript>
    // Create new workbook
    workbook = SpreadsheetNew("Detailed Sales", true);
    
    // ===== SHEET 1: Company Header and Sales Details =====
    
    // Add company header (row 1)
    SpreadsheetAddRow(workbook, "COMPANY NAME - MONTHLY SALES REPORT");
    SpreadsheetMergeCells(workbook, 1, 1, 1, 8);
    headerFormat = {};
    headerFormat.bold = true;
    headerFormat.fontsize = 16;
    headerFormat.alignment = "center";
    headerFormat.fgcolor = "dark_blue";
    headerFormat.color = "white";
    SpreadsheetFormatRow(workbook, headerFormat, 1);
    
    // Add report period (row 2)
    SpreadsheetAddRow(workbook, "Report Period: " & reportMonth);
    SpreadsheetMergeCells(workbook, 2, 2, 1, 8);
    periodFormat = {};
    periodFormat.bold = true;
    periodFormat.fontsize = 12;
    periodFormat.alignment = "center";
    periodFormat.fgcolor = "light_blue";
    SpreadsheetFormatRow(workbook, periodFormat, 2);
    
    // Add blank row
    SpreadsheetAddRow(workbook, "");
    
    // Add sales data starting at row 4
    SpreadsheetAddRows(workbook, salesData, 4, 1);
    
    // Format header row (row 4)
    headerRowFormat = {};
    headerRowFormat.bold = true;
    headerRowFormat.fgcolor = "light_green";
    headerRowFormat.alignment = "center";
    headerRowFormat.border = "thin";
    SpreadsheetFormatRow(workbook, headerRowFormat, 4);
    
    // Format data rows with borders
    borderFormat = {};
    borderFormat.border = "thin";
    for (row = 5; row <= salesData.recordCount + 4; row++) {
        SpreadsheetFormatRow(workbook, borderFormat, row);
    }
    
    // Note: Currency formatting removed to simplify code
    // Excel will display numbers, you can format them manually in Excel if needed
    
    // Set column widths
    SpreadsheetSetColumnWidth(workbook, 1, 12);  // SaleDate
    SpreadsheetSetColumnWidth(workbook, 2, 12);  // Region
    SpreadsheetSetColumnWidth(workbook, 3, 18);  // SalesRep
    SpreadsheetSetColumnWidth(workbook, 4, 15);  // ProductCategory
    SpreadsheetSetColumnWidth(workbook, 5, 20);  // ProductName
    SpreadsheetSetColumnWidth(workbook, 6, 10);  // Quantity
    SpreadsheetSetColumnWidth(workbook, 7, 12);  // UnitPrice
    SpreadsheetSetColumnWidth(workbook, 8, 12);  // TotalSale
    
    // Add totals row
    totalRow = salesData.recordCount + 5;
    SpreadsheetSetCellValue(workbook, "TOTALS", totalRow, 5);
    SpreadsheetSetCellValue(workbook, totalOrders, totalRow, 6);
    SpreadsheetSetCellValue(workbook, totalSales, totalRow, 8);
    
    totalRowFormat = {};
    totalRowFormat.bold = true;
    totalRowFormat.fgcolor = "gold";
    totalRowFormat.border = "medium";
    totalRowFormat.alignment = "right";
    SpreadsheetFormatRow(workbook, totalRowFormat, totalRow);
    
    // ===== SHEET 2: Regional Summary =====
    SpreadsheetCreateSheet(workbook, "Regional Summary");
    SpreadsheetSetActiveSheet(workbook, "Regional Summary");
    
    // Add header
    SpreadsheetAddRow(workbook, "REGIONAL SALES SUMMARY - " & reportMonth);
    SpreadsheetMergeCells(workbook, 1, 1, 1, 4);
    regionHeaderFormat = {};
    regionHeaderFormat.bold = true;
    regionHeaderFormat.fontsize = 14;
    regionHeaderFormat.alignment = "center";
    regionHeaderFormat.fgcolor = "dark_blue";
    regionHeaderFormat.color = "white";
    SpreadsheetFormatRow(workbook, regionHeaderFormat, 1);
    
    SpreadsheetAddRow(workbook, "");
    
    // Add summary data
    SpreadsheetAddRows(workbook, regionSummary, 3, 1);
    
    // Format header
    regionDataHeaderFormat = {};
    regionDataHeaderFormat.bold = true;
    regionDataHeaderFormat.fgcolor = "light_green";
    regionDataHeaderFormat.alignment = "center";
    regionDataHeaderFormat.border = "thin";
    SpreadsheetFormatRow(workbook, regionDataHeaderFormat, 3);
    
    // Format data rows with borders
    regionBorderFormat = {};
    regionBorderFormat.border = "thin";
    for (row = 4; row <= regionSummary.recordCount + 3; row++) {
        SpreadsheetFormatRow(workbook, regionBorderFormat, row);
    }
    
    // Note: Currency formatting removed to simplify code
    // Numbers will display in Excel and can be formatted there
    
    // Set column widths
    SpreadsheetSetColumnWidth(workbook, 1, 15);
    SpreadsheetSetColumnWidth(workbook, 2, 12);
    SpreadsheetSetColumnWidth(workbook, 3, 15);
    SpreadsheetSetColumnWidth(workbook, 4, 15);
    
    // ===== SHEET 3: Executive Summary =====
    SpreadsheetCreateSheet(workbook, "Executive Summary");
    SpreadsheetSetActiveSheet(workbook, "Executive Summary");
    
    // Add title
    SpreadsheetAddRow(workbook, "EXECUTIVE SUMMARY");
    SpreadsheetMergeCells(workbook, 1, 1, 1, 2);
    summaryTitleFormat = {};
    summaryTitleFormat.bold = true;
    summaryTitleFormat.fontsize = 16;
    summaryTitleFormat.alignment = "center";
    summaryTitleFormat.fgcolor = "dark_blue";
    summaryTitleFormat.color = "white";
    SpreadsheetFormatRow(workbook, summaryTitleFormat, 1);
    
    // Add metrics
    SpreadsheetAddRow(workbook, "");
    SpreadsheetAddRow(workbook, ["Metric", "Value"]);
    summaryHeaderFormat = {};
    summaryHeaderFormat.bold = true;
    summaryHeaderFormat.fgcolor = "light_blue";
    SpreadsheetFormatRow(workbook, summaryHeaderFormat, 3);
    
    SpreadsheetAddRow(workbook, ["Total Sales", dollarFormat(totalSales)]);
    SpreadsheetAddRow(workbook, ["Total Orders", totalOrders]);
    SpreadsheetAddRow(workbook, ["Average Order Value", dollarFormat(averageOrderValue)]);
    SpreadsheetAddRow(workbook, ["Report Month", reportMonth]);
    SpreadsheetAddRow(workbook, ["Generated On", dateTimeFormat(now(), "yyyy-mm-dd HH:nn")]);
    
    // Set column widths
    SpreadsheetSetColumnWidth(workbook, 1, 25);
    SpreadsheetSetColumnWidth(workbook, 2, 20);
    
    // Try to save the workbook
    if (canWriteFiles) {
        try {
            SpreadsheetWrite(workbook, reportPath, true);
            reportSaved = true;
        } catch (any e) {
            reportSaved = false;
        }
    } else {
        reportSaved = false;
    }
</cfscript>

<cfif reportSaved>
    <div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 20px; margin: 20px 0; border-radius: 5px;">
        <h3 style="color: green; margin: 0;">✓ Monthly Sales Report Generated Successfully</h3>
        <p><strong>Filename:</strong> #reportFileName#</p>
        <p><strong>Location:</strong> #reportPath#</p>
        <p><strong>Sheets:</strong> Detailed Sales, Regional Summary, Executive Summary</p>
        <p><strong>Total Records:</strong> #salesData.recordCount# sales transactions</p>
    </div>
<cfelse>
    <div style="background: ##e8f5e9; border: 2px solid ##4caf50; padding: 20px; margin: 20px 0; border-radius: 5px;">
        <h3 style="color: green; margin: 0;">✓ Monthly Sales Report Created in Memory</h3>
        <p><strong>Filename:</strong> #reportFileName#</p>
        <p><strong>Sheets:</strong> Detailed Sales, Regional Summary, Executive Summary</p>
        <p><strong>Total Records:</strong> #salesData.recordCount# sales transactions</p>
        <p><em>In production with file permissions, this would be saved to disk.</em></p>
    </div>
</cfif>

<!--- Display report statistics --->
<cfscript>
    reportStats = {};
    reportStats.reportMonth = reportMonth;
    reportStats.totalSales = dollarFormat(totalSales);
    reportStats.totalOrders = totalOrders;
    reportStats.averageOrderValue = dollarFormat(averageOrderValue);
    reportStats.generatedOn = dateTimeFormat(now(), "yyyy-mm-dd HH:nn:ss");
    if (reportSaved) {
        reportStats.fileSize = numberFormat(getFileInfo(reportPath).size / 1024, "0.00") & " KB";
    } else {
        reportStats.fileSize = "N/A (in memory)";
    }
    reportStats.sheets = "Detailed Sales, Regional Summary, Executive Summary";
    reportStats.saved = reportSaved ? "Yes" : "No (memory only)";
</cfscript>

<h3>Report Statistics</h3>
<cfdump 
    var="#reportStats#" 
    label="Generated Report Details"
    expand="yes">

<!--- Show alternative download method --->
<cfif NOT canWriteFiles>
    <div style="background: ##f5f5f5; border: 1px solid ##ccc; padding: 15px; margin: 20px 0; border-radius: 5px;">
        <h4>💡 Alternative: Stream Report to Browser</h4>
        <p>When file system access is limited, stream the Excel report directly:</p>
        <pre style="background: ##fff; padding: 10px; border: 1px solid ##ddd; border-radius: 3px; overflow-x: auto;">
&lt;cfscript&gt;
    // Get binary data from workbook
    binaryData = SpreadsheetReadBinary(invoice);
&lt;/cfscript&gt;

&lt;cfheader name="Content-Disposition" 
          value="attachment; filename=#reportFileName#"&gt;
&lt;cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" 
           variable="##binaryData##" reset="true"&gt;</pre>
    </div>
</cfif>