Color-Coded Weekly Excel Reports with Dynamic Columns in SugarCRM

The Problem

Our operations team needed a weekly snapshot of staff activity: how many students each worker called, how many were still waiting for a follow-up, and how long each connection took to happen. SugarCRM's built-in reporting tool couldn't handle this layout, mainly because two things about it were dynamic and visual rather than fixed:

• The activity columns depend on whatever records exist in the database at export time, so the number of columns changes as the CRM data changes.
• The team wanted the sheet color-coded into sections so they could scan it at a glance instead of reading every number.

To solve this we built a custom exporter using the PEAR Spreadsheet_Excel_Writer library, triggered directly from a SugarCRM controller action.

What We Built

A controller action on the PT_weekly_report module generates a fully formatted Excel workbook in memory and streams it straight to the browser as a download. The output has three visually distinct sections, each with its own merged, colored header:

1. ACTIVITIES (blue) — one column per activity type, generated dynamically
2. WAIT FOR CONNECT (orange) — new / waiting / total counts
3. TIME UNTIL CONNECTED (green) — average / min / max days

Non-zero values are highlighted in yellow so a manager can spot activity without scanning every cell.

How It Works

The Code

Setting up the workbook and formats

The workbook is built entirely in memory. We define one format object per header color up front and reuse them throughout:

require_once('vendor/pear/spreadsheet_excel_writer/Writer.php');

$workbook  = new Spreadsheet_Excel_Writer();
$worksheet = $workbook->addWorksheet('Weekly Report');

// Bold format for header cells
$format_bold = $workbook->addFormat();
$format_bold->setBold();
$format_bold->setFgColor(5);  // Blue — used for the ACTIVITIES header

$format_orange = $workbook->addFormat();
$format_orange->setBold();
$format_orange->setFgColor(41);  // Orange — WAIT FOR CONNECT

$format_green = $workbook->addFormat();
$format_green->setBold();
$format_green->setFgColor(42);  // Green — TIME UNTIL CONNECTED

// Stream to the browser as an .xls download
$workbook->send('weekly-report.xls');

Writing the three color-coded section headers

Each section header spans several columns via setMerge(). Since the activity columns are dynamic, we first query isa_activities to find out how many columns to reserve before writing anything:

public function general_header(&$workbook, &$worksheet, &$row, &$col, $format_bold)
{
    // Fixed columns: Name, Students, Called, Hours
    $worksheet->write($row, 0, 'Name',     $format_bold);
    $worksheet->write($row, 1, 'Students', $format_bold);
    $worksheet->write($row, 2, 'Called',   $format_bold);
    $worksheet->write($row, 3, 'Hours',    $format_bold);
    $col = 4;

    // --- ACTIVITIES section (blue) ---
    $format_blue = $workbook->addFormat();
    $format_blue->setBold();
    $format_blue->setFgColor(5);

    $activities_result = $GLOBALS['db']->query(
        "SELECT id, name FROM isa_activities WHERE deleted=0 ORDER BY name"
    );
    $activities = [];
    while ($act = $GLOBALS['db']->fetchByAssoc($activities_result)) {
        $activities[] = $act;
    }
    $act_start = $col;
    foreach ($activities as $act) {
        $worksheet->write($row + 1, $col, $act['name'], $format_bold);
        $col++;
    }
    $worksheet->setMerge($row, $act_start, $row, $col - 1);
    $worksheet->write($row, $act_start, 'ACTIVITIES', $format_blue);

    // --- WAIT FOR CONNECT section (orange) ---
    $format_orange = $workbook->addFormat();
    $format_orange->setBold();
    $format_orange->setFgColor(41);
    $wfc_start = $col;
    $worksheet->write($row + 1, $col++, 'New',     $format_bold);
    $worksheet->write($row + 1, $col++, 'Waiting', $format_bold);
    $worksheet->write($row + 1, $col++, 'Total',   $format_bold);
    $worksheet->setMerge($row, $wfc_start, $row, $col - 1);
    $worksheet->write($row, $wfc_start, 'WAIT FOR CONNECT', $format_orange);

    // --- TIME UNTIL CONNECTED section (green) ---
    $format_green = $workbook->addFormat();
    $format_green->setBold();
    $format_green->setFgColor(42);
    $tuc_start = $col;
    $worksheet->write($row + 1, $col++, 'Avg (days)', $format_bold);
    $worksheet->write($row + 1, $col++, 'Min',        $format_bold);
    $worksheet->write($row + 1, $col++, 'Max',        $format_bold);
    $worksheet->setMerge($row, $tuc_start, $row, $col - 1);
    $worksheet->write($row, $tuc_start, 'TIME UNTIL CONNECTED', $format_green);

    return $activities;
}

Writing data rows with per-activity JOIN queries

For each worker we run one JOIN query per activity type to count how many students are tagged with it. Any non-zero result gets a yellow highlight:

public function general_row(&$workbook, &$worksheet, $worker, $row, $activities)
{
    $format_highlight = $workbook->addFormat();
    $format_highlight->setFgColor(43); // Yellow highlight for non-zero cells

    $col = 0;
    $worksheet->write($row, $col++, $worker['full_name']);
    $worksheet->write($row, $col++, $worker['total_students']);
    $worksheet->write($row, $col++, $worker['calls_made']);
    $worksheet->write($row, $col++, $worker['hours_worked']);

    foreach ($activities as $act) {
        $sql = "SELECT COUNT(s.id) AS cnt
                FROM isa_students s
                JOIN isa_students_isa_activities_1 rel
                  ON rel.isa_students_ida = s.id AND rel.deleted = 0
                WHERE rel.isa_activities_idb = '{$act['id']}'
                  AND s.assigned_user_id = '{$worker['id']}'
                  AND s.deleted = 0";

        $result = $GLOBALS['db']->query($sql);
        $data   = $GLOBALS['db']->fetchByAssoc($result);
        $count  = (int)$data['cnt'];

        $fmt = ($count > 0) ? $format_highlight : null;
        $worksheet->write($row, $col++, $count, $fmt);
    }

    // Wait-for-connect aggregates
    $wfc = $this->get_wait_for_connect($worker['id']);
    $worksheet->write($row, $col++, $wfc['new']);
    $worksheet->write($row, $col++, $wfc['waiting']);
    $worksheet->write($row, $col++, $wfc['total']);

    // Time-until-connected aggregates
    $tuc = $this->get_time_until_connected($worker['id']);
    $fmt_tuc = ($tuc['avg'] > 0) ? $format_highlight : null;
    $worksheet->write($row, $col++, $tuc['avg'], $fmt_tuc);
    $worksheet->write($row, $col++, $tuc['min']);
    $worksheet->write($row, $col++, $tuc['max']);
}

Streaming the file to the browser

Once every row is written, close() flushes the workbook straight into the HTTP response:

// In the main action method:
$workbook->send('weekly-report-' . date('Y-m-d') . '.xls');

$row = 0;
$activities = $this->general_header($workbook, $worksheet, $row, $col, $format_bold);
$row += 2; // skip header + subheader rows

$workers = $this->get_all_workers();
foreach ($workers as $worker) {
    $this->general_row($workbook, $worksheet, $worker, $row, $activities);
    $row++;
}

$workbook->close(); // streams the file and exits

Key Takeaways

• Dynamic columns: the activity columns aren't hardcoded anywhere — they're driven entirely by what's currently in isa_activities. Add a new activity type in the CRM and it shows up in the very next export, no code changes needed.
• Merged, color-coded headers: pairing setMerge() with setFgColor() makes the three report sections instantly distinguishable. Color indexes 5, 41, and 42 map to Excel's built-in blue, orange, and green palette.
• Cell-level highlighting: giving non-zero cells a yellow background lets a reader's eye land on activity immediately instead of scanning row by row.
• No framework overhead: the PEAR library writes straight to the XLS binary format — no Excel installation and no temp files, the workbook streams inline as the response body.

Stack

PHP · SugarCRM Custom Controller · Spreadsheet_Excel_Writer (PEAR) · MySQL

---

Originally published on the Northbeam Technologies blog. Need custom reporting out of SugarCRM? Get in touch.