How to Write Data into an Excel Using PHP

How can I write data into an excel using PHP?

You can use the PhpSpreadsheet library, to read an existing Excel file, add new rows/columns to it, then write it back as a real Excel file.

Disclaimer: I am one of the authors of this library.

How to insert data in excel Spreadsheet using php Excel

This is assuming all the libraries loaded and imported into the page

function report($result){
//result is the data to be filled

    $ea = new \PHPExcel();
    $ea->getProperties()
        ->setCreator('YOURNAME')
        ->setTitle('PHPExcel');

    $ews = $ea->getSheet(0);

    $ews->setCellValue('a1', 'ID'); // Sets cell 'a1' to value 'ID
    $ews->setCellValue('b1', 'first Name');
    $ews->setCellValue('c1', 'Last Name');

//this is to set header colour
    $header = 'a1:c1';
    $ews->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('0000ffe6');
    $style = array(
        'font' => array('bold' => true,),
        'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
    );
    $ews->getStyle($header)->applyFromArray($style);

    $ews->fromArray($result, '-', 'A2');

//this is to autosize columns to fit data
    for ($col = ord('a'); $col <= ord('c'); $col++)
    {
        $ews->getColumnDimension(chr($col))->setAutoSize(true);
    }

// Redirect output to a clients web browser (Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="report.xlsx"');
    header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');
//
//// If you're serving to IE over SSL, then the following may be needed
    header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
    header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header ('Pragma: public'); // HTTP/1.0

    $objWriter = PHPExcel_IOFactory::createWriter($ea, 'Excel2007');
    $objWriter->save('php://output');
    exit;

}

Add Data to a new row in excel file using PHP

use a+ instead of w mode of fopen(), so that it places the file pointer at the end of the file. Like:

...
$fp=fopen(".datacollection.xlsx","a+"); //use a+ instead of w
fputcsv($fp, array($name, $email, $title, $company), ';');
...

How to add multiple HTML form data into Excel sheet using PHPExcel

in your code for adding rows in excel sheet.
try to add this code as shown below.

//Insert that data from Row 2, Column A (index 0)
// $rowIndex=2;
// echo $rowIndex;
// getting the highest row.
$rowIndex= $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
$rowIndex++; // increment the highest row with 1
$columnIndex=0; //Column A
foreach($data as $row)
{           
    foreach($row as $ind_el)
    {       

        $Location = PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex;
        //var_dump($Location);
        $ActiveSheet->setCellValue($Location, $ind_el);     //Insert the Data at the specific cell specified by $Location
        $columnIndex++;
    }

    $rowIndex++;

}       

And this is done.

Edit
also do this first

//Result File name
$objPHPExcel = PHPExcel_IOFactory::load("myfile.xlsx");

$objWriter->save('myfile.xlsx');

So the final code will be

function write_excel1($data, $Header)
{
    //We are using PHPExcel Library for creating the Microsoft Excel file
    require_once  './PHPExcel/Classes/PHPExcel.php';

    //load your excel file here first.

Edit has done here

    $inputFileType = PHPExcel_IOFactory::identify("myfile.xlsx");
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load("myfile.xlsx");
    //Activate the First Excel Sheet
    $ActiveSheet = $objPHPExcel->setActiveSheetIndex(0);

    //Write the Header
    $i=0;
    foreach($Header as $ind_el)
    {
        //Convert index to Excel compatible Location
        $Location = PHPExcel_Cell::stringFromColumnIndex($i) . '1';
        $ActiveSheet->setCellValue($Location, $ind_el);
        $i++;
    }

    //Insert that data from Row 2, Column A (index 0)
    // $rowIndex=2;
    // echo $rowIndex;
    // getting the highest row.
    $rowIndex= $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();

Edit has done here

    $rowIndex++; // increment the highest row with 1

    foreach($data as $row)
    {           
        $columnIndex=0; //Column A
        foreach($row as $ind_el)
        {       

            $Location = PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex;
            //var_dump($Location);
            $ActiveSheet->setCellValue($Location, $ind_el);     //Insert the Data at the specific cell specified by $Location
            $columnIndex++;
        }

        $rowIndex++;

    }       

    //1. Mark the Header Row  in Color Red
    $Range = 'A1:B1:C1:D1';
    $color = 'FFFF0000';
    $ActiveSheet->getStyle($Range)->getFill($Range)->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($color);

    //2. Set the Column Width

    for($i=0; $i<count($Header);$i++)
    {
        $Location = PHPExcel_Cell::stringFromColumnIndex($i) ;
        $ActiveSheet->getColumnDimension($Location)->setAutoSize(true); 
    }

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    //Result File name

    $objWriter->save('myfile.xlsx');

}

write form data to xls file

The first thing you need to do is learn PHPEXCEL. The second and quicker way is to use header() and print your expected output in a normal html table:

<?php

$col1row = $_POST['col1row1'];//Getting data from form

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="16.xls"');
header('Pragma: no-cache');
header('Expires: 0');
/*Saving purpose
**$output= @fopen('filename', 'w'); //error turn off
**$output = fopen('php://output', 'w');
 */
//HTML table will go here
   $table = '<table><tr>';
   $table .= '<th>Column 1</th>';
   $table .= '<th>Column 2</th>';
   $table .= '</tr>';
   $table .= '<tr>';
   $table .= '<td>'.$col1row1.'</td>';
   $table .= '<td>'.$col2row1.'</td>';
   $table .= '</tr>';
   $table .= '<tr>';
   $table .= '<td>'.$col1row2.'</td>';
   $table .= '<td>'.$col2row2.'</td>';
   $table .= '</tr>';
   $table .= '</table>';
   echo $table;
?>

Save this code as excelout.php and run it. You will get your table in excel. Hope this may help for starting.

The CSV updated and working version:

function myfputcsv($handle, $array, $delimiter = ',', $enclosure = '"', $eol = "\n") {
    $return = fputcsv($handle, $array, $delimiter, $enclosure);
    if($return !== FALSE && "\n" != $eol && 0 === fseek($handle, -1, SEEK_CUR)) {
        fwrite($handle, $eol);
    }
    return $return;
}
if(isset($_POST['mydrivers']))  {  
$header=array();
$data=array();
 foreach (array_slice($_POST,0,count($_POST)-1) as $key => $value) {    
     //$header[]=$key;
     $data[]=$value;
 }  
$fp = fopen('driver.csv', 'a+');
    //fputcsv($fp, $header);
    myfputcsv($fp, $data);
fclose($fp);
}

I don't change much thing in your form except the following:

<form action = 'driver.php' target = 'driver.php' method = "post">

to

<form action=""  method ="post">

and

<input type = "submit" value = "submit" onsubmit = "this.reset()">

to

<input type="submit" value ="submit" name="mydrivers">

You can check if $_POST if empty or not using array_filter():

$valid= array_filter($_POST);

if (!empty($valid)) {
   //all fields are furnished and can be saved.
}

Demo

Set data type in the php excel in column level

Setting the datatype after setting the data won't change the data; it's type in real terms is defined when you call setCellValue() or setCellValueExplicit() in the first place.

If you are using setCellValueExplicit(), you're explicitly telling PHPExcel what datatype to use. If you use setCellValue(), you're telling PHPExcel to work out what datatype it should use. The rules for working this out are defined in a "Value Binder". The fromArray() method uses setCellValue(), so it uses the "Value binder" to identify datatypes, and sets the cell values accordingly. How this works is explained in the PHPExcel Documentation in the section on "Excel DataTypes".

Unless you have specified otherwise, PHPExcel uses rules in the the Default Value Binder, which contains some very basic and simple rules. The Library also provides an AdvancedValueBinder with more sophisticated rules, such as converting a string like 5% to a float value of 0.05 and setting a format mask for the cell so that it will still be displayed as 5%, in much the same way that MS Excel does. You can also create your own "Value Binder" with your own rules, and use that instead of the "Default Value Binder".

---

Related Topics