PHP code example of yidas / phpspreadsheet-helper

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

yidas / phpspreadsheet-helper example snippets

\yidas\phpSpreadsheet\Helper::newSpreadsheet()
    ->addRow(['ID', 'Name', 'Email'])
    ->addRows([
        ['1', 'Nick','[email protected]'],
        ['2', 'Eric','eric@.....'],
    ])
    ->output('My Excel');

$data = \yidas\phpSpreadsheet\Helper::newSpreadsheet('/tmp/My Excel.xlsx')
    ->getRows();
    
print_r($data);

\yidas\phpSpreadsheet\Helper::newSpreadsheet();

\yidas\phpSpreadsheet\Helper::newSpreadsheet('/tmp/excel.xlsx')
    ->addRow(['Modified A1'])
    ->output();

public static array newSpreadsheet(object|string $spreadSheet=null)

public static void output(string $filename='excel', string $format='Xlsx')

public static string save(string $filename='excel', string $format='Xlsx')

\yidas\phpSpreadsheet\Helper::newSpreadsheet()
    ->addRow(['Add A1'])
    ->save("/tmp/save");
// /tmp/save.xlsx

public static array getRow(boolean $toString=true, array $options=[], callable $callback=null)

use \yidas\phpSpreadsheet\Helper;

$row1 = Helper::newSpreadsheet($filepath)
    ->getRow();

$row2 = Helper::getRow();

print_r($row1);
print_r($row2);

$helper = \yidas\phpSpreadsheet\Helper::newSpreadsheet($filepath);

while ($row = $helper->getRow()) {
    // Each row data process
}

public static array getRows(boolean $toString=true, array $options=[], callable $callback=null)

public static self addRow(array $rowData, array $rowAttributes=null)

\yidas\phpSpreadsheet\Helper::newSpreadsheet()
    ->addRow([['value'=>'ID'], ['value'=>'Name'], ['value'=>'Email']])
    ->addRow(['ID', 'Name', 'Email']);

\yidas\phpSpreadsheet\Helper::newSpreadsheet()
    // Set width as 25 to all cells of this row
    ->addRow([['value'=>'ID'], ['value'=>'Name'], ['value'=>'Email']], ['width'=>25]);

public static self addRows(array $data, array $rowAttributes=null)

// Get a new PhpSpreadsheet object
$objSpreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet;
$objSpreadsheet->getProperties()
    ->setCreator("Nick Tsai")
    ->setTitle("Office 2007 XLSX Document");
    
// Get the actived sheet object from PhpSpreadsheet
$objSheet = $objSpreadsheet->setActiveSheetIndex(0);
$objSheet->setTitle('Sheet');
$objSheet->setCellValue('A1', 'SN');

// Inject PhpSpreadsheet Object and Sheet Object to Helper
\yidas\phpSpreadsheet\Helper::newSpreadsheet($objSpreadsheet)
    ->setSheet($objSheet)
    ->setRowOffset(1) // Point to 1nd row from 0
    ->addRows([
        ['1'],
        ['2'],
    ])
    ->output();

use \yidas\phpSpreadsheet\Helper;

Helper::newSpreadsheet()
    ->setSheet(0, 'Sheet')
    ->addRow(['SN']);
    
// Get the PhpSpreadsheet object created by Helper
$objSpreadsheet = Helper::getSpreadsheet();
$objSpreadsheet->getProperties()
    ->setCreator("Nick Tsai")
    ->setTitle("Office 2007 XLSX Document");
    
// Get the actived sheet object created by Helper
$objSheet = Helper::getSheet();
$objSheet->setCellValue('A2', '1');
$objSheet->setCellValue('A3', '2');

Helper::output();

\yidas\phpSpreadsheet\Helper::newSpreadsheet()
    ->addRows([
        [['value'=>'SN', 'row'=>2], ['value'=>'Language', 'col'=>2], ['value'=>'Block', 'row'=>2, 'col'=>2]],
        ['','English','繁體中文',['skip'=>2]],
    ])
    ->addRows([
        ['1', 'Computer','電腦','#15'],
        ['2', 'Phone','手機','#4','#62'],
    ])
    ->output('Merged Excel');

public static self setSheet($sheet=0, $title=NULL, $normalizeTitle=false)

public static object getSheet($identity=null, $autoCreate=false)

use \yidas\phpSpreadsheet\Helper;

Helper::newSpreadsheet()
    ->setSheet(0, 'First Sheet')
    ->addRow(['Sheet Index', 'Sheet Count'])
    ->addRows([
        [Helper::getActiveSheetIndex(), Helper::getSheetCount()],
    ]);
    
// Set another sheet object without giving index 
Helper::setSheet(null, '2nd Sheet')
    ->addRow(['Sheet Index', 'Sheet Count'])
    ->addRows([
        [Helper::getActiveSheetIndex(), Helper::getSheetCount()],
    ]);
    
// Get a sheet which does not exsit with auto creating it  
$obj = Helper::getSheet('3nd Sheet', true);

// Set a sheet with the title which has been auto-normalized
Helper::setSheet(null, '*This [sheet] name has been auto-nomalizing', true)
    ->addRow(['Sheet Index', 'Sheet Count'])
    ->addRows([
        [Helper::getActiveSheetIndex(), Helper::getSheetCount()],
    ]);

Helper::output('MultiSheets');

use \yidas\phpSpreadsheet\Helper;

Helper::newSpreadsheet()
    ->addRows([
        [
            ['value'=>'SN', 'row'=>2, 'key'=>'sn'], 
            ['value'=>'Language', 'col'=>2, 'key'=>'lang'], 
            ['value'=>'Block', 'row'=>2, 'col'=>2, 'key'=>'block'],
        ],
        [   
            '',
            ['value'=>'English', 'key'=>'lang-en'],
            ['value'=>'繁體中文', 'key'=>'lang-zh'],
            ['skip'=>2, 'key'=>'block-skip'],
        ],
    ])
    ->addRows([
        ['1', 'Computer','電腦','#15'],
        ['2', 'Phone','手機','#4','#62'],
    ]);
// ->output('Merged Excel');  

print_r(Helper::getCoordinateMap());
print_r(Helper::getRangeMap());
// print_r(Helper::getColumnMap());
// print_r(Helper::getRowMap());
echo "sn start cell: ". Helper::getCoordinateMap('sn');
echo "\nsn start column: ". Helper::getColumnMap('sn');
echo "\nsn start row: ". Helper::getRowMap('sn');
echo "\nsn range: ". Helper::getRangeMap('sn');
echo "\nAll range: ". Helper::getRangeAll(); 

\yidas\phpSpreadsheet\Helper::newSpreadsheet()
    // Each cell with each style attributes
    ->addRow([
        'Percentage', 
        '10%', 
        ['value'=>'content', 'style'=> [
            'font' => [
                'bold' => true,
                'color' => ['argb' => 'FFFF0000']
            ],
            'alignment' => ['horizontal' => 'right'],
            'borders' => [
                'top' => ['borderStyle' => 'thin'],
            ],
            'fill' => [
                'fillType' => 'linear',
                'rotation' => 90,
                'startColor' => ['argb' => 'FFA0A0A0'],
                'endColor' => ['argb' => 'FFFFFFFF'],
            ],
        ]],
        ['value'=>'10000', 'style'=> [
            'numberFormat' => [
                'formatCode' => '#,##0',
            ],
        ]],
    ])
    // Row with thousands separator format style
    ->addRow(['1000', '2000', '3000', '4000'], ['style' => [
        'numberFormat' => [
            // const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00';
            'formatCode' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1,
        ],
    ]]) 
    // Row with percentage format style
    ->addRow(['0.1', '0.15', '0.3145', '0.855'], ['style' => [
        'numberFormat' => [
            // const FORMAT_PERCENTAGE_00 = '0.00%';
            'formatCode' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00,
        ],
    ]]) 
    ->output();

\yidas\phpSpreadsheet\Helper::newSpreadsheet()
    ->addRow([['value'=>'ID', 'width'=>10], ['value'=>'Name', 'width'=>25], ['value'=>'Email', 'width'=>50]])
    ->addRows([
        ['1', 'Nick','[email protected]'],
        ['2', 'Eric','eric@.....'],
    ])
    ->output('My Excel'); 

public static self setStyle(array $styleArray, string $range=NULL)

\yidas\phpSpreadsheet\Helper::newSpreadsheet()
    ->addRow(['Title', 'Content'])
    ->addRows([
        ['Basic Plan', "*Interface\n*Search Tool"],
        ['Advanced Plan', "*Interface\n*Search Tool\n*Statistics"],
    ])
    ->setWrapText()
    // ->setWrapText('B2')
    ->setAutoSize()
    // ->setAutoSize('B')
    ->setStyle([
        'borders' => [
            'inside' => ['borderStyle' => 'hair'],
            'outline' => ['borderStyle' => 'thin'],
        ],
        'fill' => [
            'fillType' => 'solid',
            'startColor' => ['argb' => 'FFCCCCCC'],
        ],
    ])
    ->output('Formatted Excel');  

public static self setWrapText(string $range=NULL, string $value=true)

public static self setAutoSize(string $colAlphaStart=NULL, string $colAlphaEnd=NULL, boolean $value=true)