How to display excel file in php

Question

For showing excel in php you can use PHPExcel like this:

Nội dung chính Show

More from Ampersand Academy
Stop Wasting Time on Servers
Alternate Approach:
How To Edit an Existing Spreadsheet in PHP?
How To Prepare Spreadsheets for Printing?
Share This Article
Get Our Newsletter Be the first to get the latest updates and tutorials.
Thankyou for Subscribing Us!
How can I open XLSX File in PHP?
How connect Excel to PHP?
How do I display an Excel File in HTML page?
How do I get Excel to show data in a Web page?

include 'PHPExcel/IOFactory.php';
$inputFileType = 'Excel5';
$inputFileName = 'MyExcelFile.xls';

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

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

to upload a file you can use simple file upload like this:

PHP "upload.php":

<?php
        $target_dir = "uploads/";
        $target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);
        $uploadOk = 1;
        $imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);
        if(isset($_POST["submit"])) {
            if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {
              echo "The file ". basename( $_FILES["fileToUpload"]["name"]). " has been uploaded.";
            } else {
               echo "Sorry, there was an error uploading your file.";
              }
        }
    ?>

HTML form :

<!DOCTYPE html>
<html>
<body>

<form action="upload.php" method="post" enctype="multipart/form-data">
    Select image to upload:
    <input type="file" name="fileToUpload" id="fileToUpload">
    <input type="submit" value="Upload Image" name="submit">
</form>

</body>
</html>

Update:

PHPExcel - DEAD PHPExcel last version, 1.8.1, was released in 2015. The project was officially deprecated in 2017 and permanently archived in 2019.

The project has not be maintained for years and must not be used anymore. All users must migrate to its direct successor PhpSpreadsheet, or another alternative.

More from Ampersand Academy

Ampersand Academy offers classroom & online training for Data Analytics using SAS, R & Python, Data Science using R & Python, Deep Learning, Ionic, & Tableau. We also offer free tutorials and plugins. Write for us. Mail your article link to

$inputFileName = 'upload/'.$_POST['filename'];
    //  Read your Excel workbook
    try {
        $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($inputFileName);
    } catch (Exception $e) {
        die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) 
        . '": ' . $e->getMessage());
    }
    //  Get worksheet dimensions
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();
    $str = '';
    //  Loop through each row of the worksheet in turn
    for ($row = 1; $row <= $highestRow; $row++) {
        //  Read a row of data into an array
        $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, 
        NULL, TRUE, FALSE);
        $even = $row%2==0? "class='even'":"";
        $str .= '';
        foreach($rowData[0] as $k=>$v){
            //echo "Row: ".$row."- Col: ".($k+1)." = ".$v."
";
            //$data[$row][$k+1][] = $v;
            if($row == 1){
                $str .= '';
            } else {
                $str .= '';
            }
        }  
        $str .= ''; 
    }
    $str .= ''.$v.' '.$v.'';
    echo $str;
}

PHP is highly enriched with features of a good scripting language. It provides numerous libraries for specific purposes. PHP provides a library to deal with Excel files. It is called PHP Excel library. It enables you to read and write spreadsheets in various formats including csv, xls, ods, and xlsx. You will need to ensure that you have PHP’s upgraded version not older than PHP 5.2 . Moreover, you should have installed extensions php_qd2, php_xml and php_zip.

You might also like: How To Host PHP On DigitalOcean

Spreadsheet creation is a very common use case in PHP development. It is used to export data to an Excel spreadsheet. Below is the code for creating an excel spreadsheet using the PHP Excel library.

Code:

//Including PHPExcel library and creation of its object

require('PHPExcel.php');

$phpExcel = new PHPExcel;

// Setting font to Arial Black

$phpExcel->getDefaultStyle()->getFont()->setName('Arial Black');

// Setting font size to 14

$phpExcel->getDefaultStyle()->getFont()->setSize(14);

//Setting description, creator and title

$phpExcel ->getProperties()->setTitle("Vendor list");

$phpExcel ->getProperties()->setCreator("Robert");

$phpExcel ->getProperties()->setDescription("Excel SpreadSheet in PHP");

// Creating PHPExcel spreadsheet writer object

// We will create xlsx file (Excel 2007 and above)

$writer = PHPExcel_IOFactory::createWriter($phpExcel, "Excel2007");

// When creating the writer object, the first sheet is also created

// We will get the already created sheet

$sheet = $phpExcel ->getActiveSheet();

// Setting title of the sheet

$sheet->setTitle('My product list');

// Creating spreadsheet header

$sheet ->getCell('A1')->setValue('Vendor');

$sheet ->getCell('B1')->setValue('Amount');

$sheet ->getCell('C1')->setValue('Cost');

// Making headers text bold and larger

$sheet->getStyle('A1:D1')->getFont()->setBold(true)->setSize(14);

// Insert product data

// Autosize the columns

$sheet->getColumnDimension('A')->setAutoSize(true);

$sheet->getColumnDimension('B')->setAutoSize(true);

$sheet->getColumnDimension('C')->setAutoSize(true);

// Save the spreadsheet

$writer->save('products.xlsx');

Stop Wasting Time on Servers

Cloudways handle server management for you so you can focus on creating great apps and keeping your clients happy.

Alternate Approach:

Alternatively, spreadsheet can be downloaded rather than saving it to the server. You may use the following code:

header('Content-Type: application/vnd.ms-excel');

header('Content-Disposition: attachment;filename="file.xlsx"');

header('Cache-Control: max-age=0');

$writer->save('php://output');

How To Edit an Existing Spreadsheet in PHP?

The editing process is rather similar to the spreadsheet creation process. Use the following code to do this:

// Include PHPExcel library and create its object

require('PHPExcel.php');

// Load an existing spreadsheet

$phpExcel = PHPExcel_IOFactory::load('products.xlsx');

// Get the first sheet

$sheet = $phpExcel ->getActiveSheet();

// Remove 2 rows starting from the row 2

$sheet ->removeRow(2,2);

// Insert one new row before row 2

$sheet->insertNewRowBefore(2, 1);

// Create the PHPExcel spreadsheet writer object

// We will create xlsx file (Excel 2007 and above)

$writer = PHPExcel_IOFactory::createWriter($phpExcel, "Excel2007");

// Save the spreadsheet

$writer->save('products.xlsx');

You might also like: How To Import And Export CSV Files Using PHP And MySQL

How To Prepare Spreadsheets for Printing?

To get a spreadsheet ready, you should set size, margins and paper orientation.

$sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);

$sheet -> getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

$sheet->getPageMargins()->setTop(1);

$sheet ->getPageMargins()->setRight(0.75);

$sheet ->getPageMargins()->setLeft(0.75);

$sheet ->getPageMargins()->setBottom(1);

Conclusion

The PHPExcel library is very useful for calculations and charts. It is often used to store data in Excel sheets or to import data from Excel spreadsheets via PHP website. Got any questions about what we discussed? Leave a comment and I’ll get back to you!

Apart from knowing tricks such as working on spreadsheets in PHP, any smart PHP developer knows the importance of an excellent hosting solution for the web apps he/she creates. The Cloudways Managed PHP Hosting Platform gives you ultra-fast performance thanks to its optimized hosting stack. Launch your free trial today to venture into the future of PHP hosting.

Share your opinion in the comment section. COMMENT NOW

Customer Review at

“Cloudways hosting has one of the best customer service and hosting speed”

Sanjit C [Website Developer]

Ahmed Khan

Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works. You can email him at [email protected]

×

Thankyou for Subscribing Us!

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Thank you for your feedback!

How can I open XLSX File in PHP?

Read an Excel File (XLSX).

Create the Excel Reader using ReaderEntityFactory::createXLSXReader().

Open the XSLX file with $reader->open($path).

Browse each Sheet of the spreadsheet with $reader->getSheetIterator().

Browse each Row of the Sheet with $sheet->getRowIterator().

Browse each Cell of the Row with $row->getCells().

How connect Excel to PHP?

Establish a Connection Open the connection to Excel by calling the odbc_connect or odbc_pconnect methods. To close connections, use odbc_close or odbc_close_all. $conn = odbc_connect("CData ODBC Excel Source","user","password"); Connections opened with odbc_connect are closed when the script ends.