Read and write Excel file using PHP (example)

This post will describe a basic usage of PHPExcel to read and write Excel file. Although in this demo I use .xls extension, PHPExcel really can write and read Excel 2007 (.xlsx) files too.

Download PHPExcel here (free of charge, it’s open source 😀 ).

Unzip the PHPExcel .zip file into your project destination. You only need to extract “Classes” folder.

Our scenario: user will fill some HTML form then from this input, we will populate a new Excel file (.xls) and send it to user as downloadable file.

Our Excel template screenshot

Excel template screen shot

We will fill C1 cell with the current date when the form submitted. C3 cell will be the user’s name and C4 cell will contain user’s message. Our template file –> http://demo.ahowto.net/phpexcel/template.xls

Full source code of our simple form can be seen by viewing source code (Ctrl+U on Firefox) our demo URL: http://demo.ahowto.net/phpexcel/

Form inputs will be processed by process.php below:

 

I already put a lot of comment on the codes. It should be clear. On the code above, I show you how to read Excel .xls file, change cell content, formatting a cell (alignment), change column width, set row height and finally “force” send result file to user.

The Excel result file should be like this:

Our Excel result from template created by PHP

You can do more than I showed here (such as setting cell color, rotation, inserting images, formula, printing, etc.)  by reading PHPExcel Documentation and see the codes in Tests folder (it is included in the PHPExcel download zip).

Leave a comment ?

43 Comments.

  1. 😈 👿 👿

  2. Beautiful, thanks.

  3. I says at first a lot of thanks .
    I want save the excel file at my physical location .
    c:\wamp\www\……
    Can you help me .

    • remove all header() PHP code and
      just change from this:

      into this:

      Your file will be saved in the same directory/folder as the PHP file

  4. Great for basic XLS, but …

    I have make a bot which write data into a template excel file.
    All are ok but not at all optimized for insert many lines from a n array. In this case performance are bad and crash the api.

    For write many lines dynamicaly, use writeexcel library.

  5. where must i set a location of my excel template..??

    • place it where-ever you want — as long as the script has access to it — . The safest place would in the same folder/directory as the script

      • I am sorry, I’m making an application using ExtJS and PHP,
        I put the same location with a php script,
        and the error :

        please help me to solve this.

        • I’m not familiar on how extJS work but you can always use “desperate measure” 😛 by using absolute path.. something like this:

          and put your excel template in c:\wamp\www\nrcfv2_prev\

          • thanks for the reply,
            here I just want to edit a field with a script:
            $objTpl->getActiveSheet()->setCellValue(‘C2’, date(‘Y-m-d’))
            and the error:
            Severity: Notice
            Message: Constant IDENTIFIER_OLE already defined
            Filename: Shared/OLERead.php
            Line Number: 28
            and the error:
            Severity: Notice
            Message: Constant IDENTIFIER_OLE already defined
            Filename: Shared/OLERead.php
            Line Number: 28
            Thanks

  6. @rizky:
    that’s just NOTICE error, you can disable it by editing your php.ini and edit your error_reporting value to:

    restart your Apache after that.

    you can also set this value via php script like this:

    but the first solution is more guaranteed to work.

  7. Thank you,
    I’ve tried, but no effect,
    I really need this, please, can you send a small sample project to edit excel file with php.
    if you are willing, please send it to: rizkylazu@yahoo.com
    Thank you very much

  8. oh, My application is already running

    thank you very much

  9. thanks.
    Dimitri Vargas Figueiredo Guimarães

  10. Harshish Patel

    Excellent Job…
    Works very nice!!
    Thank you

  11. Fuck this shit men!

  12. captnskippy

    Wanted to know if multiple people can write to or read from the same excel file over a network ??

  13. Hello, i hope you can answer my question.

    can this code let multiple user log into a website and enter a user number and a value, then not save a new file but all users use one xls file.

    ex.

    215 +50 kg
    524 -200 kg

  14. Hello!

    I am a hobby coder, so please bear with my inexperience, if you would be so kind.

    I love the demo version, works wonderfully. However, I am wondering if you know someway to add functionality in the html forms where delimiters from Excel sheet will appropriately skip ‘s

    So in essence: copying data from three cells in the same row with put one cells data in three different ‘s aligned horizontally, and the same but transposed for three cells in the same column:

    Maybe some sample code is in order:

    Column One
    Column Two
    Column Three

    And let’s say for arguments sake I had 6 cells in Excel, aligned exactly how the ‘s are in the above sample form:

    Blue Single 10
    Orange Double 20

    Is there any solution (javaScript or jQuery) where the “\t” and “\n” hidden delimiters would be recognized and skip ‘s appropriately rather than dumping all data of the 6 selected cells into only one

    Tall order I am guessing

    • Ok, the code didn’t show how I thought it ought to…. hence the inexperience

      the code:

      Column One
      Column Two
      Column Three

  15. crud, it still isn’t working to show the sample form code, and now I am barfing all over you blog…. great! Don’t I feel like the f’ing a’hole.

  16. Nothing so great in writing out a excel file. Real point is how would you “READ” an Excel file?

  17. i want to upload the excel file and show it on html tabl…

  18. I like the functionality, but I am unable to get it working. For example, the 01pharSimple.php example gives me “Failed opening required ‘../Build/PHPExcel.phar'” – but I haven’t PHPExcel.phar anywhere in my box at all… 🙁 Any idea?

  19. hai

    Kamu here.Actually i need to upload records by using excel with help of php.Ecel file having user details,in that email is primary key unique value,i need to check db email with excel email value.if exists value store in one table otherwise value store in one table. Please assist me?

  20. Hi. First of all Thank you for this article. Its amazing.
    I’m not able to write the data into the file. Here is my code

    include ‘Classes/PHPExcel/IOFactory.php’;
    include ‘Classes/PHPExcel/Writer/Excel2007.php’;

    $objPHPExcel = new PHPExcel();
    $objReader = PHPExcel_IOFactory::createReader(‘Excel2007’);
    //we load the file that we want to read
    $objPHPExcel = PHPExcel_IOFactory::load(“/home/bigc/Documents/test.xlsx”);
    $objPHPExcel->setActiveSheetIndex(0);

    // Change the file
    $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue(‘A1’, ‘Hello’)
    ->setCellValue(‘B1’, ‘World!’);

    // Write the file
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’);
    ini_set(‘display_errors’, 1);
    $objWriter->save(“test.xlsx”);

    The error is
    Fatal error: Uncaught exception ‘PHPExcel_Writer_Exception’ with message ‘Could not close zip file test.xlsx.’ in /home/bigc/Development/Development/Classes/PHPExcel/Writer/Excel2007.php:399 Stack trace: #0 /home/bigc/Development/Development/Upload.php(39): PHPExcel_Writer_Excel2007->save(‘test.xlsx’) #1 {main} thrown in /home/bigc/Development/Development/Classes/PHPExcel/Writer/Excel2007.php on line 399

    Do you have any idea about this?

  21. pawan nagar

    how we can write in excel file in codeigniter can please help i will we very thankfull to you

  22. hi,

    i have generated a excel file using eexcelview and phpexcel extension,i want to add title and header to that excel file i dont know exactly what to add and where to add.

    please help me as i am new to yii framework.

    • I have no idea about phpExcel on yii.
      What title and header? On a cell?

      • ya in a cell.

      • Everything is packed in eexcelview and phpexcel i hope so,I just have to define the title by some name.

        I did this but its still not working.

      • $date = new DateTime();
        echo $date->format(‘ Y-m-d ‘) . “\n”;
        this function change the current date format now i want change date format of my excel file from column A1

  23. $time = strtotime( $date);
    $newformat = date(‘Y-m-d’,$time);
    echo $newformat;
    when i put some numeric value in $time = strtotime( 12.15.2014); then he work perfectly but when i use $date then he show output is 1970.1.1 why? i store in $date string like this 10/12/2014
    anyone tell me how i do this

  24. Thank you so much ….
    Excellent Job…
    Works perfectly!!
    THAKS A TON !!
    🙂

  25. can any one help me to read the image from the excel sheet in php

  26. Hello there,
    I have a bit of different requirement/situation in here,
    Let me explain and please help-
    Suppose I have a user logging in using sessions.
    Now, the user has to fill a form having 3 inputs, these inputs belong to a specific user(unique).
    And, within his session login time, the user fills the form n number of times.
    Now in the end the user click on compare to find out the UNIQUE row or rows which he just entered. All of this data is saved in the excel sheet. After getting the unique row or rows if any and being displayed as the result, the user logs off and this happens all over again when a new user comes with a new excel sheet creation, input, comparison, result display.

    EXAMPLE VIEW OF INPUTS –

    FORM_INPUT 1st time col1 col2 col3
    FORM_INPUT 2nd time col1 col2 col3
    FORM_INPUT 3rd time col1 col2 col3
    FORM_INPUT 4th time col1 col2 col3
    FORM_INPUT 5th time col1 col2 col3
    FORM_INPUT 6th time col1 col2 col3
    FORM_INPUT 7th time col1 col2 col3

    as per the user..

    the column values gets and has to be same many times that’s why it is also required for me/user to get the unique row across three column values…

    I blv that u can help.

  27. hola
    y como poder hacerlo al reves?

    es decir que se pueda llevar lo escrito en el archivo excel al formulario para poder cargarlo? en php, teniendo al archivo excel como base de datos SIN mysql

  28. PHPExcel e Codeigniter | Journal - pingback on 2015/12/04 at 17:44
  29. Hi,

    I have excel file with 45 sheets in it. All the sheets have some value and formula to fetch data from other sheet. My problem is i’m unable to edit the whole sheet. Every time i tried doing this, only first 5 sheets are updated and rest all sheets are saved as blank.

    Please help me out.

    Please find my code below:

    load(“$file”);
    //$worksheet= $objPHPExcel->setActiveSheetIndex(0);
    $sheets = array(‘sheet1′,’sheet2′,…..,’sheet45’);
    foreach($sheets as $sheetname){
    //$worksheet = $objPHPExcel->setActiveSheetIndexByName($sheetname);
    $objPHPExcel->setActiveSheetIndex($sheetno);
    $sheetno++;
    $baseRow = 8;

    $end_date_2 = date(‘Y/m/d’);
    $end_date_1 = date(‘Y/m/d’,strtotime(‘first day of this month’));
    $startDate = DateTime::createFromFormat(“Y/m/d”,$end_date_1,new DateTimeZone(“Europe/London”));
    $endDate = DateTime::createFromFormat(“Y/m/d”,$end_date_2,new DateTimeZone(“Europe/London”));

    $periodInterval = new DateInterval( “P1D” ); // 1-day, though can be more sophisticated rule
    $period = new DatePeriod( $startDate, $periodInterval, $endDate );

    $stats = array();
    $temp = array();
    foreach($period as $date){
    $dbdate = $date->format(“Y-m-d”);

    //Search data starts here
    $result = mysqli_query($con, “SELECT ROUND( SUM( adcost ) , 2 ) AS searchcost, SUM( transactions ) as transactions, SUM( transactionRevenue ) as revenue FROM analytics_campaign_quantity_284 WHERE campaign NOT LIKE ‘%app%’ AND day = ‘$dbdate'”)or die(mysqli_error());
    while($row = mysqli_fetch_array($result))
    {
    $temp[‘date’] = $date->format(“m-d-Y”);
    $temp[‘searchcost’] = $row[‘searchcost’];
    $temp[‘transaction’] = $row[‘transactions’];
    $temp[‘idealTransaction’] = ‘=$G$2/($C$3-$D$8+1)*(ROW()-7)’;
    $temp[‘transactionDeviance’] = ‘=((SUMIFS($F$8:$F$99,$D$8:$D$99,”<="&$D8))-G8)/G8';
    $temp['seatSold'] = '';
    $temp['revenue'] = $row['revenue'];
    $temp['idealRevenue'] = '=$K$2/($C$3-$D$8+1)*(ROW()-7)';
    $temp['revenueDeviance'] = '=((SUMIFS($J$8:$J$99,$D$8:$D$99,"<="&$D8))-K8)/K8';
    $temp['costperTransaction'] = '=';
    $temp['avgTransactionValue'] = '=';
    $temp['CoS'] = '=';
    $temp['seatSoldDeviance'] = '=($P$2-O8)/$P$2';
    }

    //Search Data ends here

    //Apps Data starts here

    $result = mysqli_query($con, "SELECT ROUND( SUM( adcost ) , 2 ) AS appCost, SUM( itemQuantity ) as appInstalls, SUM( sessions ) as sessions, SUM( newUser ) as newUsers FROM analytics_campaign_quantity_284 WHERE campaign LIKE '%app%' AND day = '$dbdate'")or die(mysqli_error());
    while($row = mysqli_fetch_array($result))
    {
    $temp['appCost'] = $row['appCost'];
    $temp['appInstalls'] = $row['appInstalls'];
    $temp['idealInstalls'] = '=$G$2/($C$3-$D$8+1)*(ROW()-7)';
    $temp['appDeviance'] = '=((SUMIFS($F$8:$F$99,$D$8:$D$99,"<="&$D8))-T8)/T8';
    $temp['sessions'] = $row['sessions'];
    $temp['newUsers'] = $row['newUsers'];
    }
    $temp['totalCost'] = $temp['searchcost']+$temp['appCost'];
    $temp['idealSpends'] = '=$AB$2/($C$3-$D$8+1)*(ROW()-7)';
    $temp['totalDeviance'] = '=((SUMIFS($AA$8:$AA$99,$D$8:$D$99,"<="&$D8))-AB8)/AB8';
    $stats[] = $temp;

    //apps data ends here
    }
    //echo '

    '; print_r($stats); die;
    
    	$lastRow = 8;
    	$lastColumn = 'D';
        foreach($stats as $data){
    		$lastColumn = 'D';
            
            foreach($data as $key => $value){ 
    					if($lastColumn=='Q' || $lastColumn=='V' || $lastColumn=='Y'){
    						$lastColumn++;
    					}
    					//echo $lastColumn.$lastRow.'__'.$key.'__'.$value.'';
    					if (strpos($value,'=') !== false || $key = 'date') {
    						
    					}
    					else{
                        $objPHPExcel->getActiveSheet()->setCellValue($lastColumn.$lastRow, "$value");
    					}
    				$lastColumn++; 
                
            }
    		echo '';
    		$lastRow++;
        }
    	
    }
    	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $filename = 'XYZ_'.date('d-m-y').'.xlsx';
        $objWriter->save($filename);
    ?>
  30. this is quite awesome and has solved my problem to a large extent, but likewise still have some issues.
    please i will like to retrieve data from a particular cell

  31. Sent and processed! Thanks KC! – That is the form output.
    Whom was it sent to or how I can retrieve the output file – I vainly struugled to make out.

Leave a Reply

Trackbacks and Pingbacks:

%d bloggers like this: