Generating Excel Files with the Merci Library

A developer guide to the newExcel() / excelsetCellValueByColumnAndRow() helper set used across MERCI ERP reports (GSTR-1, utilisation, stock statements, etc.).

Note on accuracy: the signatures below are derived from live usage in the GSTR-1.8 module. Where behaviour depends on the underlying PHPExcel / PhpSpreadsheet version, it is flagged. Verify against ../common/ library source before relying on edge-case behaviour.

1. Quick start

$objPHPExcel = newExcel();                 // workbook + one empty sheet

$sheet = $objPHPExcel->getActiveSheet();   // first sheet
$sheet->setTitle('Summary');

excelsetCellValueByColumnAndRow($sheet, 1, 2, 'My Report Title');
PhpExcelCellFont($sheet, 'A2', 'Arial', 10, true);

$sheet2 = $objPHPExcel->createSheet();     // second sheet
$sheet2->setTitle('Detail');

$fSaveAs = '../common/' . strtolower(newtempfile('XLSX', 'myreport_Y'.GetYearID().'_C'.GetCompanyID().'_'));
excelSave($objPHPExcel, $fSaveAs);

2. Core API reference

Function	Signature (as used)	Notes
`newExcel()`	`newExcel(): PHPExcel`	Returns a new workbook with one blank active sheet already present. Do not call `createSheet()` for your first sheet.
`getActiveSheet()`	`$objPHPExcel->getActiveSheet()`	The sheet created by `newExcel()`.
`createSheet()`	`$objPHPExcel->createSheet()`	Appends a new sheet and returns it. Assign it: `$sheet = $objPHPExcel->createSheet();`
`setTitle()`	`$sheet->setTitle(string $name)`	Max 31 chars. `[ ] : * ? / \` are illegal. Commas are fine — `'b2b,sez,de'` is valid.
`excelsetCellValueByColumnAndRow()`	`(sheet, int $col, int $row, mixed $value)`	Column first, then row. Columns are 1-based (`1` = A). Writes the value as-is (text/number).
`setExcelCellDecimalFormat()`	`(sheet, int $row, int $col, mixed $value)`	⚠️ Row first, then column — the opposite of the setter above. Writes the value and applies the numeric/decimal format. Use for all amount columns.
`PhpExcelCellFont()`	`(sheet, string $range, string $font, int $size, bool $bold)`	`$range` is an A1-style cell or range (`'A2'`, `'A3:M3'`).
`PhpExcelCellColor()`	`(sheet, string $range, string $hex)`	Sets the background fill. Hex without `#`, e.g. `'3b6ae2'`.
`PhpExcelCellFgColor()`	`(sheet, string $range, string $hex)`	Sets the font/text colour, despite the "Fg" name. Pair with `PhpExcelCellColor()` for white-on-blue headers.
`getColumnDimensionByColumn()`	`$sheet->getColumnDimensionByColumn(int $col)->setAutoSize(true)`	Native PHPExcel call. Indexing differs by version — classic PHPExcel is 0-based, PhpSpreadsheet ≥ 1.18 is 1-based. See §4.
`excelSave()`	`excelSave($objPHPExcel, string $path)`	Writes the XLSX to disk.
`newtempfile()`	`newtempfile(string $ext, string $prefix): string`	Returns a unique filename. Wrap in `strtolower()` for consistent, case-safe URLs.

Supporting helpers used alongside these:

Function	Purpose
`getsize($array)`	Element count (`count()` equivalent).
`xstr($number, $decimals)`	Number → fixed-decimal string, for comparisons and display.
`GetYearID()` / `GetCompanyID()`	Context IDs for filenames — always include both to avoid cross-company collisions.
`website()`	Current host, for building the public URL.

3. Coordinate conventions and gotchas

Columns are 1-based in the cell writers. excelsetCellValueByColumnAndRow($sheet, 1, 2, …) writes to A2, which is why the matching style call is PhpExcelCellFont($sheet, 'A2', …).

Argument order flips between the two writers. This is the single most common source of transposed output:

excelsetCellValueByColumnAndRow($sheet, $col, $row, $value);  // col, row
setExcelCellDecimalFormat     ($sheet, $row, $col, $value);   // row, col

Style calls take A1 ranges, not column/row numbers. If you change a layout row you must update both the write call and every style call that targets it. In the GSTR-1.8 source the title is written to row 6 but bolded at A5, and 'Please Note' is written to row 14 but bolded at A13 — both are off-by-one and the bold silently lands on the wrong (blank) row. Keep row numbers in constants if a sheet has more than a handful of styled rows.

Auto-size indexing. The autosize loop calls PHPExcel directly rather than a Merci wrapper:

for ($colx = 1; $colx <= 13; $colx++) {
    $sheet->getColumnDimensionByColumn($colx)->setAutoSize(true);
}

On classic PHPExcel this is 0-based, so 1..13 sizes columns B..N and leaves column A unsized. Confirm which library your build ships; if 0-based, use for ($colx = 0; $colx < 13; …).

Write before you style. Auto-size and fill are computed against cell contents, so populate the sheet first, then apply column dimensions.

4. Standard sheet layout (house style)

Merci report sheets follow a consistent grid. Reusing it keeps exports recognisable:

Row	Content	Styling
2	Report title (col 1)	Arial 10 bold, white text on `3b6ae2`
3	Summary labels	White text on `3b6ae2`, Arial 10 bold
4	Summary values	Default
5	Column headers	Fill `f9c86b`
6+	Detail rows	Decimal format on amount columns

PhpExcelCellFont   ($sheet, 'A2',    'Arial', 10, true);
PhpExcelCellFgColor($sheet, 'A2',    'ffffff');
PhpExcelCellColor  ($sheet, 'A2',    '3b6ae2');
PhpExcelCellColor  ($sheet, 'A3:M3', '3b6ae2');
PhpExcelCellFgColor($sheet, 'A3:M3', 'ffffff');
PhpExcelCellFont   ($sheet, 'A3:M3', 'Arial', 10, true);
PhpExcelCellColor  ($sheet, 'A5:M5', 'f9c86b');

Extend the :M end-column to match your actual column count.

Help sheet. Statutory exports carry a leading instructions sheet as the active sheet (setTitle('Help Instructions')) covering scope of the export, portal ordering, workbook compatibility, and the warning that re-importing overwrites existing offline-tool data. Copy the GSTR-1.8 sheet as a starting point — and note the source has three typos to fix when you do: Instructicon, vhalues, and exemp.

5. Saving, download, email, and preview

$fSaveAs = '../common/' . strtolower(newtempfile('XLSX', 'gstr1_8_Y'.GetYearID().'_C'.GetCompanyID().'_'));
excelSave($objPHPExcel, $fSaveAs);

$fSaveAs = str_replace('..', '', $fSaveAs);   // disk path → web path
$Epath   = 'https://' . website();

Files are written to ../common/ on disk; stripping the .. yields the browser-visible path. The Office Web Viewer preview requires a publicly reachable HTTPS URL — it will not render a file behind session auth or on localhost.

Download block:

$form->addRawHtml("<div style='float:right;display:flex;margin-right:10px;margin-bottom:10px;font-size:12pt;'>
    <div style='margin-right:20px;'><a href='$Epath/$fSaveAs'><i class='fa-light fa-download'></i> Download file</a></div>
    <div class='pointer' data-subject='GSTR1 File' data-file='$fSaveAs' onclick='emailFile(this)'>
        <i class='fa-light fa-envelope'></i> Send On Email</div>
</div>");

Inline preview:

$form->addLabel("<iframe id='xlreport_container'
    src='https://view.officeapps.live.com/op/embed.aspx?src=$Epath/$fSaveAs&wdDownloadButton=1&wdHideGridlines=0&wdHideHeaders=0&wdAllowInteractivity=0'
    width='100%' frameborder='0' style='height:calc(100vh - 20vh);'></iframe>");

Then close the screen:

$form->render();

6. Copy-paste skeleton

$objPHPExcel = newExcel();

// ---- Sheet 1: Help ----------------------------------------------------
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setTitle('Help Instructions');
excelsetCellValueByColumnAndRow($sheet, 1, 6, 'Help Instructions');
PhpExcelCellFont($sheet, 'A6', 'Arial', 11, true);
excelsetCellValueByColumnAndRow($sheet, 1, 7, '1. …');

// ---- Sheet 2: Detail --------------------------------------------------
$data  = auto_myreport_section($sDate, $eDate, $cap, $form);
$sheet = $objPHPExcel->createSheet();
$sheet->setTitle('section');

$COLS = 13;

// Title + summary
excelsetCellValueByColumnAndRow($sheet, 1, 2, 'Summary For …');
excelsetCellValueByColumnAndRow($sheet, 3, 3, 'No. of Invoices');
excelsetCellValueByColumnAndRow($sheet, 3, 4, getsize($data) - 2);
excelsetCellValueByColumnAndRow($sheet, 5, 3, 'Total Invoice Value');
excelsetCellValueByColumnAndRow($sheet, 5, 4, $data[getsize($data)-1][4]);

// Column headers (row 5)
$headers = ['GSTIN/UIN of Recipient','Receiver Name','Invoice Number','Invoice date',
            'Invoice Value','Place Of Supply','Reverse Charge','Applicable % of Tax Rate',
            'Invoice Type','E-Commerce GSTIN','Rate','Taxable Value','Cess Amount'];
foreach ($headers as $i => $h) {
    excelsetCellValueByColumnAndRow($sheet, $i + 1, 5, $h);
}

// Styling
PhpExcelCellFont   ($sheet, 'A2',    'Arial', 10, true);
PhpExcelCellFgColor($sheet, 'A2',    'ffffff');
PhpExcelCellColor  ($sheet, 'A2',    '3b6ae2');
PhpExcelCellColor  ($sheet, 'A3:M3', '3b6ae2');
PhpExcelCellFgColor($sheet, 'A3:M3', 'ffffff');
PhpExcelCellFont   ($sheet, 'A3:M3', 'Arial', 10, true);
PhpExcelCellColor  ($sheet, 'A5:M5', 'f9c86b');

for ($colx = 1; $colx <= $COLS; $colx++) {
    $sheet->getColumnDimensionByColumn($colx)->setAutoSize(true);
}

// Detail rows (row 6 onward)
for ($x = 1; $x < getsize($data) - 1; $x++) {
    $row = $data[$x];
    for ($y = 0; $y < getsize($row); $y++) {
        setExcelCellDecimalFormat($sheet, ($x + 5), $y + 1, $row[$y]);
    }
}

// ---- Save + deliver ---------------------------------------------------
$fSaveAs = '../common/' . strtolower(newtempfile('XLSX', 'myreport_Y'.GetYearID().'_C'.GetCompanyID().'_'));
excelSave($objPHPExcel, $fSaveAs);
$fSaveAs = str_replace('..', '', $fSaveAs);
$Epath   = 'https://' . website();

7. Pre-release checklist

[ ] First sheet obtained via getActiveSheet(), not an extra createSheet() (avoids a stray blank sheet).
[ ] Every setExcelCellDecimalFormat() call passes row, then column.
[ ] Every A1 range in a style call matches the row/column actually written.
[ ] Auto-size loop bounds match the library's column indexing base.
[ ] Amount columns use setExcelCellDecimalFormat(), not the plain value setter.
[ ] Filename includes GetYearID() and GetCompanyID().
[ ] Disk path converted to web path with str_replace('..', '', …) before use in HTML.
[ ] data-subject and data-file both present on the email trigger.
[ ] Totals row excluded from the detail loop and reflected in the summary block.
[ ] Reconciliation check compares xstr() output, not floats.