README

MNB PHPExcel is an array-first PHP Excel engine.

Main workflow:

PHP Array ⇄ XLSX ⇄ CSV ⇄ SQL

This package starts with the classic small-file PHPExcel-style workflow, but keeps the API modern, simple, and array-focused.

Current version

v1.0.0 — First Public Release

Install

composer require mnb/mnb-phpexcel

For local path development:

{
  "repositories": [
    {
      "type": "path",
      "url": "../mnb-phpexcel"
    }
  ],
  "require": {
    "mnb/mnb-phpexcel": "*"
  }
}

Required PHP extensions for XLSX support:

ext-zip
ext-xmlreader

CSV/array features work without those XLSX extensions.

Why MNB PHPExcel?

Most PHP applications already use arrays from database results, APIs, forms, and admin panels. MNB PHPExcel makes those arrays easy to export, import, validate, inspect, and convert.

MnbExcel::fromArray($data)->save('students.xlsx');
$data = MnbExcel::read('students.xlsx')->toArray();

Features

Array-first features

fromArray()
toArray()
fromWorkbookArray()
Multiple sheets
Associative arrays with header rows
Custom column headings
Text/date/number column handling
Formula-like text escaping

Formula and cell safety features

Explicit typed cell helpers: MnbExcel::text(), number(), bool(), date(), formula(), and blank().
Safe formula policy through formulaPolicy(): safe, allow, and block.
Formula-like text escaping is enabled by default to reduce Excel/CSV injection risk.
cellSafety() controls cell text limits and invalid XML control-character handling.
maxCellTextLength() protects Excel's 32,767-character cell text limit.
controlCharPolicy() strips or blocks XML-invalid characters.
scanCells() reports formula-like text, unsafe formulas, long text, invalid XML characters, and large numeric text.
Long numeric text can be preserved as text to avoid Excel precision loss.

$rows = [
    [
        'student_id' => MnbExcel::text('000123456789012345'),
        'maths' => 80,
        'science' => 70,
        'total' => MnbExcel::formula('SUM(B2:C2)', 150),
        'comment' => '=HYPERLINK("http://example.com", "Unsafe text")',
    ],
];

$safety = MnbExcel::scanCells($rows);

MnbExcel::fromArray($rows)
    ->withHeader()
    ->formulaPolicy('safe')
    ->cellSafety([
        'max_text_length' => 32767,
        'long_text_policy' => 'truncate',
        'control_char_policy' => 'strip',
    ])
    ->save('safe-report.xlsx');

Export polish features

autoWidth() estimates small-file column widths from row content.
formatColumns() applies custom Excel number formats to selected columns.
integerColumns(), decimalColumns(), datetimeStyleColumns(), and textStyleColumns() add common export format presets.
conditionalRowStyle() styles rows based on array data values.
metadata(), creator(), and company() write workbook document properties.
saveSafe() creates safe output filenames inside a target directory.
MnbExcel::safeFileName() and MnbExcel::safeSheetName() provide reusable safe naming helpers.
withImportSummarySheet() adds an import summary sheet beside failed-row reports.
MnbExcel::fromImportSummary() creates a standalone summary workbook.

Example:

MnbExcel::report($rows, 'business')
    ->title('Student Export Report')
    ->columns([
        'name' => 'Student Name',
        'email' => 'Email Address',
        'marks' => 'Marks',
        'status' => 'Status',
        'amount' => 'Amount',
    ])
    ->metadata([
        'title' => 'Student Export Report',
        'creator' => 'MNB PHPExcel',
        'company' => 'MNB',
    ])
    ->autoWidth(['min' => 10, 'max' => 38])
    ->integerColumns(['marks'])
    ->currencyColumns(['amount'], '₹')
    ->conditionalRowStyle(
        static fn (array $row): bool => ($row['status'] ?? '') === 'fail',
        'mnb.row.danger'
    )
    ->saveSafe(__DIR__ . '/exports', 'Student Export Report', 'xlsx');

Report builder features

MnbExcel::report() shortcut
Title rows and subtitle rows
Summary rows and footer rows
Built-in report templates: simple, business, finance
Named reusable styles
Per-column styles
Per-row styles
Per-cell styles
Per-range styles
Currency column formatting
Percentage column formatting
Date-style column formatting
Header freeze and auto-filter even when title rows appear before the header

XLSX small-file features

Write XLSX files
Read XLSX files to array
Select sheets by index or sheet name
Relationship-based worksheet lookup through workbook.xml.rels
Sheet names listing
Workbook inspection report
Date style detection from styles.xml
Hidden sheet/row/column reporting
Corrupted XLSX structure checks
Header styling
Font color, fill color, alignment, border
Freeze header
Auto filter
Merge cells
Row height
Column width
Image/logo insertion

CSV features

Array to CSV
CSV to array
Multi-sheet CSV export as separate files
CSV dialect presets: excel, semicolon, excel_tab, unix, pipe
Delimiter/enclosure/escape configuration
UTF-8 BOM on/off control
Encoding normalization for CSV reads/writes
Locale-aware decimal/date parsing
CSV injection policy modes: escape, tab_escape, strip, block, none
Empty row and empty column cleanup controls

SQL features

SQL query to Excel
Excel/array rows to SQL
Batch insert helper
Column map support
SQL dry-run import planning before insert

Validation features

Validate array rows
Import preview
Required column detection
Unexpected column detection / strict column mode
Column mapping suggestions
Duplicate row detection
Original row-number aware failed row reports
Failed rows report builder
Rules: required, email, numeric, integer, string, boolean, date, min, max, length, in, regex

Array to XLSX

use Mnb\PHPExcel\MnbExcel;

$data = [
    ['name' => 'Ravi', 'email' => 'ravi@example.com', 'phone' => '0987654321'],
    ['name' => 'Sita', 'email' => 'sita@example.com', 'phone' => '0912345678'],
];

MnbExcel::fromArray($data)
    ->withHeader()
    ->columns([
        'name' => 'Student Name',
        'email' => 'Email Address',
        'phone' => 'Phone Number',
    ])
    ->textColumns(['phone'])
    ->freezeHeader()
    ->autoFilter()
    ->save('students.xlsx');

XLSX styling, sizing, merge cells, and image

MnbExcel::fromArray($data)
    ->withHeader()
    ->styleHeader([
        'font' => ['bold' => true, 'color' => '#FFFFFF', 'size' => 12],
        'fill' => '#1F6FEB',
        'alignment' => [
            'horizontal' => 'center',
            'vertical' => 'center',
            'wrap_text' => true,
        ],
        'border' => ['color' => '#D0D7DE'],
    ])
    ->columnWidths([
        'A' => 22,
        'B' => 30,
        'C' => 18,
    ])
    ->rowHeight(1, 24)
    ->mergeCells('A5:C5')
    ->addImage(__DIR__ . '/logo.png', 'E1', [
        'width' => 120,
        'height' => 60,
        'name' => 'Logo',
    ])
    ->save('styled-students.xlsx');

Report builder

use Mnb\PHPExcel\MnbExcel;

$rows = [
    ['month' => 'January', 'revenue' => 12500.50, 'expenses' => 8200.25, 'margin' => 0.344],
    ['month' => 'February', 'revenue' => 18300.75, 'expenses' => 11100.50, 'margin' => 0.393],
    ['month' => 'March', 'revenue' => 22000.00, 'expenses' => 14300.00, 'margin' => 0.350],
];

MnbExcel::report($rows, 'finance')
    ->title('Quarterly Revenue Report')
    ->titleRow('Generated by MNB PHPExcel report builder', [
        'merge' => true,
        'style' => 'mnb.subtitle',
    ])
    ->columns([
        'month' => 'Month',
        'revenue' => 'Revenue',
        'expenses' => 'Expenses',
        'margin' => 'Margin',
    ])
    ->columnWidths([
        'A' => 18,
        'B' => 16,
        'C' => 16,
        'D' => 14,
    ])
    ->currencyColumns(['revenue', 'expenses'], '$')
    ->percentageColumns(['margin'])
    ->summaryRows([
        ['Total', 52801.25, 33600.75, 0.362],
    ])
    ->footerRows('Confidential — generated for internal review')
    ->freezeHeader()
    ->autoFilter()
    ->save('revenue-report.xlsx');

Custom named styles

MnbExcel::fromArray($rows)
    ->withHeader()
    ->namedStyle('warning', [
        'font' => ['bold' => true, 'color' => '#92400E'],
        'fill' => '#FEF3C7',
        'border' => ['color' => '#F59E0B'],
    ])
    ->rowStyle(5, 'warning')
    ->cellStyle('D5', [
        'font' => ['bold' => true, 'color' => '#B42318'],
        'fill' => '#FEE4E2',
    ])
    ->rangeStyle('A1:D1', 'mnb.header.blue')
    ->save('styled-report.xlsx');

Built-in report styles

mnb.title
mnb.subtitle
mnb.header
mnb.header.blue
mnb.header.green
mnb.summary
mnb.footer
mnb.currency
mnb.percent
mnb.date

XLSX to array

$rows = MnbExcel::read('students.xlsx')
    ->sheet(1)
    ->toArray([
        'header_row' => true,
        'skip_empty_rows' => true,
        'max_rows' => 5000,
    ]);

Read sheet by name

$rows = MnbExcel::read('students.xlsx')
    ->sheet('Students')
    ->toArray([
        'header_row' => true,
        'format_dates' => true,
        'date_format' => 'Y-m-d',
        'datetime_format' => 'Y-m-d H:i:s',
    ]);

List XLSX sheet names

$names = MnbExcel::sheetNames('students.xlsx');

// Or from a read session:
$names = MnbExcel::read('students.xlsx')->sheetNames();

Inspect XLSX file

$report = MnbExcel::inspect('students.xlsx');

print_r($report);

The inspection report includes:

file status
file size
encrypted/password-protected detection
sheet names
sheet states: visible, hidden, veryHidden
relationship-based sheet XML paths
missing worksheet XML errors
declared dimensions
row tag count
hidden row count
hidden column count
merge/filter/drawing detection
warnings and errors

Date style detection

MNB PHPExcel reads xl/styles.xml, detects date-like number formats, and converts Excel serial dates to formatted strings.

$rows = MnbExcel::read('students.xlsx')
    ->sheet('Students')
    ->toArray([
        'format_dates' => true,
        'date_format' => 'Y-m-d',
        'datetime_format' => 'Y-m-d H:i:s',
    ]);

Disable date formatting when you want raw Excel serial values:

$rows = MnbExcel::read('students.xlsx')
    ->sheet('Students')
    ->toArray([
        'format_dates' => false,
    ]);

Hidden row handling

$rows = MnbExcel::read('students.xlsx')
    ->sheet('Students')
    ->toArray([
        'include_hidden_rows' => false,
    ]);

Hidden sheets are reported by inspect().

Formula cell handling

Default behavior returns formula text.

$rows = MnbExcel::read('report.xlsx')
    ->toArray([
        'formula_cells' => 'formula',
    ]);

Use cached values when available:

$rows = MnbExcel::read('report.xlsx')
    ->toArray([
        'formula_cells' => 'cached_value',
    ]);

Multiple sheets

MnbExcel::fromWorkbookArray([
    'Students' => $students,
    'Teachers' => $teachers,
])
    ->withHeader()
    ->save('school.xlsx');

Array to CSV

MnbExcel::fromArray($data)
    ->withHeader()
    ->save('students.csv');

CSV dialects, BOM, and injection policy

MnbExcel::fromArray($data)
    ->withHeader()
    ->csvDialect('semicolon')
    ->csvBom(true)
    ->csvInjectionPolicy('escape')
    ->save('students.csv');

Available dialects:

excel      comma CSV with UTF-8 BOM and CRLF
semicolon  semicolon CSV, useful for comma-decimal locales
excel_tab  tab-delimited Excel-friendly text
unix       comma CSV without BOM and LF
pipe       pipe-delimited text

CSV injection policies:

escape      prefix dangerous formula-like values with apostrophe
tab_escape  prefix dangerous formula-like values with tab
strip       remove formula trigger characters
block       throw exception when dangerous formula-like text is found
none        do not sanitize

CSV to array

$rows = MnbExcel::readCsv('students.csv')->toArray([
    'header_row' => true,
]);

Locale-aware CSV read

$rows = MnbExcel::readCsv('students.csv', [
    'dialect' => 'semicolon',
    'encoding' => 'UTF-8',
    'trim_values' => true,
])->toArray([
    'header_row' => true,
    'locale' => 'de_DE',
    'number_columns' => ['amount'],
    'date_columns' => ['started' => 'Y-m-d'],
    'skip_empty_rows' => true,
    'skip_empty_columns' => true,
]);

Supported locale presets:

en_US, en_IN, hi_IN, en_GB, de_DE, fr_FR

SQL export

MnbExcel::fromSql($pdo, 'SELECT name, email, phone FROM students')
    ->withHeader()
    ->save('students.xlsx');

Import quality preview

Use this before inserting imported rows into SQL.

$rows = MnbExcel::read('students.xlsx')
    ->sheet('Students')
    ->toArray([
        'header_row' => true,
        'preserve_original_row_numbers' => true,
    ]);

$preview = MnbExcel::previewImport($rows, [
    'required_columns' => ['name', 'email', 'phone', 'marks'],
    'allowed_columns' => ['_mnb_original_row_number', 'name', 'email', 'phone', 'marks'],
    'strict_columns' => true,
    'duplicate_by' => ['email'],
]);

print_r($preview['warnings']);

Preview result includes:

total rows
total columns
missing required columns
unexpected columns
empty row count
duplicate groups
sample rows
per-column filled/empty/type statistics
warnings

Column mapping suggestions

Useful for admin import screens where uploaded Excel headers are not exactly the same as database columns.

$map = MnbExcel::suggestColumnMap(
    ['Student Name', 'Email Address', 'Mobile No', 'Total Marks'],
    ['name', 'email', 'phone', 'marks'],
    [
        'phone' => ['mobile', 'mobile no', 'phone number'],
        'marks' => ['total marks', 'score'],
    ]
);

Duplicate row detection

$duplicates = MnbExcel::duplicateRows($rows, ['email'], [
    'row_number_key' => '_mnb_original_row_number',
]);

Import validation with original row numbers

$result = MnbExcel::validateImport($rows, [
    'name' => 'required|string|max:100',
    'email' => 'required|email',
    'marks' => 'required|numeric|min:0|max:100',
], [
    'row_number_key' => '_mnb_original_row_number',
    'strict_columns' => true,
    'allowed_columns' => ['_mnb_original_row_number', 'name', 'email', 'phone', 'marks'],
    'duplicate_by' => ['email'],
]);

MnbExcel::fromFailedRows($result['failed'])
    ->freezeHeader()
    ->autoFilter()
    ->save('failed-rows.xlsx');

SQL dry-run import

Dry-run validates the import plan and batch count without inserting rows.

$plan = MnbExcel::read('students.xlsx')
    ->sheet('Students')
    ->dryRunImportToSql($pdo, 'students', [
        'header_row' => true,
        'batch_size' => 500,
    ]);

SQL import

$result = MnbExcel::read('students.xlsx')
    ->sheet(1)
    ->importToSql($pdo, 'students', [
        'header_row' => true,
        'batch_size' => 500,
        'map' => [
            'Student Name' => 'name',
            'Email Address' => 'email',
            'Phone Number' => 'phone',
        ],
    ]);

Validation and failed rows report

$result = MnbExcel::validateArray($rows, [
    'name' => 'required|string|max:100',
    'email' => 'required|email',
    'marks' => 'required|numeric|min:0|max:100',
]);

MnbExcel::fromFailedRows($result['failed'])
    ->freezeHeader()
    ->autoFilter()
    ->save('failed-rows-report.xlsx');

Current scope

This package is currently optimized for small/medium PHPExcel-style workflows.

Not yet included:

Large-file streaming reader
Chunk reader
Parallel worker import
Formula calculation engine
Chart support
Pivot table support
Full style preservation when reading existing XLSX files

Those are planned for later engine layers.

Examples

php examples/array_to_csv.php
php examples/array_to_xlsx.php
php examples/xlsx_small_features.php
php examples/xlsx_reader_accuracy.php
php examples/validation_error_report.php
php examples/import_quality.php
php examples/csv_locale.php

Development checks

composer dump-autoload
composer run test:syntax
php tests/ArrayCsvSmokeTest.php
php tests/ValidationSmokeTest.php
php tests/CsvLocaleSmokeTest.php
php tests/XlsxReaderFeatureSmokeTest.php

Public release scope

MNB PHPExcel v1.0.0 combines the earlier internal foundation work into the first public package:

Array-first XLSX/CSV workflows.
Small-file PHPExcel-style reading and writing.
Report builder.
Import preview and validation.
CSV locale/dialect support.
Formula and cell safety layer.

Large-file streaming and parallel chunk processing are planned as future engine layers, not part of the first public release.

mnb/mnb-phpexcel

包简介

README 文档