Xls Writer uses 1-based column indices in BIFF DIMENSIONS record instead of required 0-based indices

[zlatanovic-nebojsa]

Description:

The PhpSpreadsheet Xls writer incorrectly uses 1-based
column indices when writing the BIFF8 DIMENSIONS record
(0x0200), causing an extra empty column to appear in the
generated XLS files. According to the [Microsoft BIFF8
specification](https://learn.microsoft.com/en-us/openspecs/
office_file_formats/ms-xls/5fd3837c-9f3d-4952-8a85-ad93ddb3
7ced), the DIMENSIONS record requires 0-based column
indices.

Root Cause:

In src/PhpSpreadsheet/Writer/Xls/Worksheet.php, the
constructor calculates column indices using:

$maxC = $this->phpSheet->getHighestColumn();
$this->firstColumnIndex =
Coordinate::columnIndexFromString($minC);
$this->lastColumnIndex =
Coordinate::columnIndexFromString($maxC);

The problem is that Coordinate::columnIndexFromString()
returns 1-based indices (e.g., 'A' = 1, 'G' = 7), but the
BIFF8 DIMENSIONS record expects 0-based indices (e.g., 'A'
= 0, 'G' = 6).

Impact:

When converting XLS files to CSV or text format, an extra
empty column appears with a trailing comma/delimiter on
each row.

Steps to Reproduce:

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xls;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Create 7 columns (A-G)
$sheet->setCellValue('A1', 'Column A');
$sheet->setCellValue('B1', 'Column B');
$sheet->setCellValue('C1', 'Column C');
$sheet->setCellValue('D1', 'Column D');
$sheet->setCellValue('E1', 'Column E');
$sheet->setCellValue('F1', 'Column F');
$sheet->setCellValue('G1', 'Column G');

$writer = new Xls($spreadsheet);
$writer->save('test.xls');

// Inspect DIMENSIONS record - shows colMic=1, colMac=8 
(should be colMic=0, colMac=7)

Expected Behavior:
- For 7 columns (A-G), DIMENSIONS record should have:
colMic = 0, colMac = 7
- XLS file should contain exactly 7 columns when converted
to CSV/text

Actual Behavior:
- DIMENSIONS record has: colMic = 1, colMac = 8
- XLS file appears to have 8 columns, resulting in trailing
 commas in CSV conversion

Specification Reference:

According to
https://learn.microsoft.com/en-us/openspecs/office_file_for
mats/ms-xls/5fd3837c-9f3d-4952-8a85-ad93ddb37ced:

colMic: A ColU structure that specifies the first column in
 the sheet that contains a used cell.

colMac: An unsigned integer that specifies the zero-based 
index of the column after the last column in the sheet that
 contains a used cell.

Proposed Fix:

In src/PhpSpreadsheet/Writer/Xls/Worksheet.php, convert the
 column indices to 0-based:

$maxC = $this->phpSheet->getHighestColumn();
$this->firstColumnIndex =
Coordinate::columnIndexFromString($minC) - 1;
$this->lastColumnIndex =
Coordinate::columnIndexFromString($maxC) - 1;

Environment:
- PhpSpreadsheet version: latest
- PHP version: 8.3
- Operating System: Windows

Workaround:

Binary post-processing of the XLS file to correct the
DIMENSIONS record after generation.

[zlatanovic-nebojsa]

---

Update: Additional Critical Issues Found - Complete Analysis

After extensive testing with both small (11 KB) and large (377 KB) XLS files comparing PhpSpreadsheet output against old PHPExcel Excel5 writer, we discovered 6 additional critical compatibility
issues beyond the DIMENSIONS record problem:

🔴 Issue #1: DIMENSIONS Record (Original Issue)

Problem: Uses 1-based column indices instead of 0-basedImpact: Extra empty column in generated filesFix: Subtract 1 from column indices:

$this->firstColumnIndex = Coordinate::columnIndexFromString($minC) - 1;
$this->lastColumnIndex = Coordinate::columnIndexFromString($maxC) - 1;

Also found: Row indices need same fix for PHPExcel compatibility:
$this->firstRowIndex = max(0, $this->firstRowIndex - 1);

🔴 Issue #2: COLINFO Records - Wrong Count

Problem: PhpSpreadsheet only generates COLINFO records for used columns. Old PHPExcel always generates 256 COLINFO records (columns 0-255).Location: Worksheet.php close() methodImpact: Different file
structure, compatibility issues with legacy systemsFix: Force 256 columns before calling parent close:

$lastColProp->setValue($this, 256); // Generate 256 COLINFO records
parent::close();

🔴 Issue #3: WINDOW2 Record - Zoom Factors

Problem: PhpSpreadsheet writes non-zero zoom factors (100), old PHPExcel writes 0x0000Location: Worksheet.php writeWindow2()BIFF Record: 0x023E, offset 10-13 (zoomScalePageBreak, zoomScaleNormal)Fix:
Set both zoom factors to 0x0000:

$window2Fields['zoomScalePageBreak'] = 0x0000;
$window2Fields['zoomScaleNormal'] = 0x0000;

🔴 Issue #4: PageLayoutView Record - Should Not Exist

Problem: PhpSpreadsheet writes PageLayoutView record (0x088B), old PHPExcel never wrote thisImpact: Extra 20 bytes in file, breaks byte-identical comparisonFix: Remove the record from output data
after generation

🔴 Issue #5: ConditionalFormatting Records - CRITICAL BUG 🐛

Problem: PhpSpreadsheet writes CF records (0x01B0, 0x01B1, 0x01B2) even when no conditional formatting exists

CRITICAL: Any removal code using strpos() to find these records will corrupt large files because the byte patterns appear in legitimate cell data (e.g., LABELSST record with row=432 has bytes B0 01)

Impact:

• ✅ Small files work fine (no cells at row 432+)
• ❌ Large files get corrupted - cell data is mistakenly removed

Example corruption:
Offset 0x0001CFF1: LABELSST record
Row: 0x01B0 (432) <- These bytes "B0 01" match CF pattern!
Data: FD 00 0A 00 B0 01 00 00 0F 00 07 00 00 00

Correct Fix: Must use sequential BIFF record parsing, NOT string search:

  private function removeConditionalFormattingRecords(): void
  {
      $cfRecordIds = [0x01B0, 0x01B1, 0x01B2];
      $newData = '';
      $pos = 0;
      $dataLen = strlen($this->_data);

      // Walk through BIFF records sequentially
      while ($pos + 4 <= $dataLen) {
          $recordId = unpack('v', substr($this->_data, $pos, 2))[1];
          $recordLen = unpack('v', substr($this->_data, $pos + 2, 2))[1];

          if (in_array($recordId, $cfRecordIds)) {
              // Skip CF record
              $pos += 4 + $recordLen;
              continue;
          }

          // Keep other records
          $newData .= substr($this->_data, $pos, 4 + $recordLen);
          $pos += 4 + $recordLen;
      }

      $this->_data = $newData;
  }

🔴 Issue #6: SHEETPROTECTION Record - Wrong Options Bitmask

Problem: PhpSpreadsheet writes options = 0x4403, old PHPExcel writes 0x7FFFLocation: BIFF record 0x0867, offset 18-19Impact: Different protection behaviorFix: Force old PHPExcel value:

$fields['options'] = 0x7FFF;

🔴 Issue #7: OLE Timestamps

Problem: Different timestamp handling between versionsFix: Use time(), time() consistently or null, null for deterministic testing

---

📊 Test Results

After applying ALL 7 fixes:

• ✅ Small files (11 KB): Byte-identical to old PHPExcel
• ✅ Large files (377 KB): Byte-identical to old PHPExcel
• ✅ 20,000+ BIFF records: Parsed correctly without corruption

---

🛠️ Workaround Solution

Created complete PHPExcel compatibility layer (application/libraries/Excel/):

Files:

• Writer/XlsExcel5.php - Custom writer with overridden save()
• Xls/Worksheet.php - Custom worksheet extending PhpSpreadsheet with all 7 fixes
• Xls/PHPExcelWorkbook.php - Workbook writer using old PHPExcel algorithm
• Xls/PHPExcelOLERoot.php - OLE structure using exact old PHPExcel code

Usage:

  $writer = new XlsExcel5();
  $writer->setSpreadsheet($spreadsheet);

  // Enable PHPExcel compatibility mode
  foreach ($writer->customWriterWorksheets as $worksheet) {
      $worksheet->setPHPExcelCompatMode(true);
  }

  $writer->save('output.xls');

This generates 100% byte-identical files to old PHPExcel, maintaining full backward compatibility with legacy systems.

---

🎯 Recommendation for PhpSpreadsheet

These issues should be fixed in the core library with a compatibility flag:

  $writer = new Xls($spreadsheet);
  $writer->setLegacyMode(true); // Enable PHPExcel Excel5 compatibility
  $writer->save('output.xls');

This would help thousands of users migrating from PHPExcel who need identical file output for legacy API integrations.

---

💾 Workaround Code Available

I've implemented a complete compatibility layer that fixes all 7 issues. The code is available if anyone needs it or if the maintainers want to review the implementation.

Implementation approach:

• Extends PhpSpreadsheet's Xls writer classes
• Preserves all PhpSpreadsheet functionality
• Adds PHPExcel compatibility mode via flag
• Uses sequential BIFF record parsing (no regex/strpos corruption)
• Implements exact old PHPExcel algorithms for OLE structure

Files (can share if helpful):

1. Writer/XlsExcel5.php - Main writer with custom save() method
2. Xls/Worksheet.php - Worksheet writer with all BIFF record fixes
3. Xls/PHPExcelWorkbook.php - Workbook writer
4. Xls/PHPExcelOLERoot.php - OLE root writer

Happy to:

• Share the complete implementation
• Create a pull request if maintainers are interested
• Help implement these fixes directly in PhpSpreadsheet
• Provide test files demonstrating each issue

The workaround generates 100% byte-identical output to old PHPExcel Excel5 writer, tested with files up to 377 KB / 20,000+ BIFF records.

---

[oleibman]

Yes, please go ahead and prepare a PR. It will not be necessary to have custom and legacy versions of save; that adds complexity without any real value. If the current version of save is not correct, it just needs to be fixed, not preserved.