Skip to content

CSV & Excel

CSV and Excel import/export helpers.

7 functions. All require apis/main_webservices.php to be included.

On this page: Array2Csv · Csv2Array · CsvRows · CsvTitleRow · Html2Excel · bi_AllForTable · setExcelCellDecimalFormat

Array2Csv()

Prototype

Array2Csv( $arr, $titles=array() )

Description

  • Writes an array to a CSV file (with optional title row) and returns the file name.
  • Use to export data sets for download or interchange.

Parameters

  • $arr — 2 dimension array to be convered to csv
  • $titles — array having same number of column containing title for each column

Returns

  • String — the path/name of the created CSV file.

Return example

$f = Array2Csv($rows, ['code','name']);
// e.g. '../common/exp_8f3a.csv'

Usage example

$csv = Array2Csv( [['apple',100],['banana',120],['carrot',230]], ['Fruit','Price'] );

:material-file-code: Source: apis/core2.php

Csv2Array()

Prototype

Csv2Array( $csvfile, $nStartRec=0, $limit=10000 )

Description

  • Reads a CSV file into a PHP array, with optional start row and row limit.
  • Use for bulk imports and file-driven processing.

Parameters

  • $csvfile — csv file name with path
  • $nStartRec — Default 0
  • $limit — Default 10000 maximum no. of rows to be read & returned.

Returns

  • Array — the CSV rows parsed into arrays.

Return example

$rows = Csv2Array('/tmp/items.csv', 0, 1000);
// [ ['A','Yarn','120'], ... ]

Usage example

$data = Csv2Array( 'test.csv', $nStartRec=0, $limit=10000 );

:material-file-code: Source: apis/core2.php

CsvRows()

Prototype

CsvRows( $csvfile )

Description

  • Counts the number of rows in a CSV file.
  • Use to size progress bars or validate expected counts before import.

Parameters

  • $csvfile — csv file name with path

Returns

  • Integer — the number of rows in the CSV.

Return example

echo CsvRows('/tmp/items.csv');
// e.g. 542

Usage example

$csv = CsvRows( 'test.csv' );

:material-file-code: Source: apis/core2.php

CsvTitleRow()

Prototype

CsvTitleRow( $csvfile_with_fullpath )

Description

  • Returns the first (header) row of a CSV file as an array.
  • Use to read column names before importing the body.

Parameters

  • $csvfile_with_fullpath — CSV file name including full path

Returns

  • Array — the CSV header columns.

Return example

$cols = CsvTitleRow('/tmp/items.csv');
// ['code','name','rate']

Usage example

$headers = CsvTitleRow('test.csv');

:material-file-code: Source: apis/core2.php

Html2Excel()

Prototype

Html2Excel( $html )

Description

  • Converts an HTML table to an Excel file and triggers a download (foo_bar.xls).
  • Requires MS Excel on the client to open; use for quick tabular exports.

Parameters

  • <$html> : string Html Code to be converted into Excel

Returns

  • Produces the .xls download as a side effect.

Return example

Html2Excel($htmlTable);
// browser downloads .xls

:material-file-code: Source: apis/core.php

bi_AllForTable()

Prototype

bi_AllForTable( $cTable, $type='M' )

Description

  • Returns all possible key values for a table and period (BI helper).
  • Use to enumerate dimension values for business-intelligence rollups.

Parameters

  • $cTable — Base Table Name
  • $type — Period for report. D -> Day, M-> Month, Q->Quarter, Y->Year

Returns

  • Array — the key values available for the table/period.

Return example

$keys = bi_AllForTable('trn_salesorder', 'M');
// [ ... key values ... ]

Usage example

$values = bi_AllForTable( 'trn_sales', 'M' );
returns: Array

:material-file-code: Source: apis/bi.php

setExcelCellDecimalFormat()

Prototype

setExcelCellDecimalFormat( $sheet, $row, $col, $value )

Description

  • Sets an Excel cell's value as a decimal-padded string at the given row/col.
  • Use with the Excel writer to keep numeric formatting consistent.

Parameters

  • $sheet — object of Excel class
  • $row — row number in excel
  • $col — column no. in excel
  • $value — Value to set into excel

Returns

  • Nothing — formats the target cell as a side effect.

Return example

setExcelCellDecimalFormat($sheet, 3, 5, 1250.5);
// cell shows '1250.50'

Usage example

setExcelCellDecimalFormat( $sheet, $row, $col, 12345 );

:material-file-code: Source: apis/excel.php