{"id":128319,"date":"2022-09-23T04:58:12","date_gmt":"2022-09-23T04:58:12","guid":{"rendered":"https:\/\/phppot.com\/?p=17407"},"modified":"2022-09-23T04:58:12","modified_gmt":"2022-09-23T04:58:12","slug":"php-excel-export-code-data-to-file","status":"publish","type":"post","link":"https:\/\/sickgaming.net\/blog\/2022\/09\/23\/php-excel-export-code-data-to-file\/","title":{"rendered":"PHP Excel Export Code (Data to File)"},"content":{"rendered":"
by Vincy<\/a>. Last modified on September 23rd, 2022.<\/div>\n

Export data to an excel file is mainly used for taking a backup. When taking database backup, excel format is a convenient one to read and manage easily. For some applications exporting data is important to take a backup or an offline copy of the server database.<\/p>\n

This article shows how to export data to excel using PHP. There are many ways to implement this functionality. We have already seen an example of data export from MySQL<\/a>.<\/p>\n

This article uses the PHPSpreadSheet library for implementing PHP excel export.<\/strong><\/p>\n

It is a popular library that supports reading, and writing excel files. It will smoothen the excel import-export operations<\/a> through its built-in functions.<\/p>\n

The complete example in this article will let create your own export tool or your application.
\"php<\/p>\n

About this Example<\/h2>\n

It will show a minimal interface with the list of database records and an \u201cExport to Excel\u201d button. By clicking this button, it will call the custom ExportService created for this example.<\/p>\n

This service instantiates the PHPSpreadsheet library class and sets the column header and values. Then it creates a writer object by setting the PHPSpreadsheet instance to output the data to excel.<\/p>\n

Follow the below steps to let this example run in your environment.<\/p>\n

    \n
  1. Create and set up the database with data exported to excel.<\/li>\n
  2. Download the code at the end of this article and configure the database.<\/li>\n
  3. Add PHPSpreadSheet library and other dependencies into the application.<\/li>\n<\/ol>\n

    We have already used the PHPSpreadsheet library to store extracted image URLs<\/a>.<\/p>\n

    1) Create and set up the database with data exported to excel<\/h2>\n

    Create a database named \u201cdb_excel_export\u201d and import the below SQL script into it.<\/p>\n

    structure.sql<\/p>\n

    --\n-- Table structure for table `tbl_products`\n-- CREATE TABLE `tbl_products` ( `id` int(8) NOT NULL, `name` varchar(255) NOT NULL, `price` double(10,2) NOT NULL, `category` varchar(255) NOT NULL, `product_image` text NOT NULL, `average_rating` float(3,1) NOT NULL\n); --\n-- Dumping data for table `tbl_products`\n-- INSERT INTO `tbl_products` (`id`, `name`, `price`, `category`, `product_image`, `average_rating`) VALUES\n(1, 'Tiny Handbags', 100.00, 'Fashion', 'gallery\/handbag.jpeg', 5.0),\n(2, 'Men\\'s Watch', 300.00, 'Generic', 'gallery\/watch.jpeg', 4.0),\n(3, 'Trendy Watch', 550.00, 'Generic', 'gallery\/trendy-watch.jpeg', 4.0),\n(4, 'Travel Bag', 820.00, 'Travel', 'gallery\/travel-bag.jpeg', 5.0),\n(5, 'Plastic Ducklings', 200.00, 'Toys', 'gallery\/ducklings.jpeg', 4.0),\n(6, 'Wooden Dolls', 290.00, 'Toys', 'gallery\/wooden-dolls.jpeg', 5.0),\n(7, 'Advanced Camera', 600.00, 'Gadget', 'gallery\/camera.jpeg', 4.0),\n(8, 'Jewel Box', 180.00, 'Fashion', 'gallery\/jewel-box.jpeg', 5.0),\n(9, 'Perl Jewellery', 940.00, 'Fashion', 'gallery\/perls.jpeg', 5.0); --\n-- Indexes for dumped tables\n-- --\n-- Indexes for table `tbl_products`\n--\nALTER TABLE `tbl_products` ADD PRIMARY KEY (`id`); --\n-- AUTO_INCREMENT for dumped tables\n-- --\n-- AUTO_INCREMENT for table `tbl_products`\n--\nALTER TABLE `tbl_products` MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;\n<\/code><\/pre>\n
    2) Download the code and configure the database<\/h2>\n
    The source code contains the following files. This section explains the database configuration.<\/p>\n
    \"excel<\/p>\n
    Once you download the excel export code from this page, you can find DataSource.php<\/em> file in the lib<\/em> folder. Open it and configure the database details in it as below.<\/p>\n
    <?php class DataSource\n{ const HOST = 'localhost'; const USERNAME = 'root'; const PASSWORD = ''; const DATABASENAME = 'db_excel_export'; ... ...\n?>\n<\/code><\/pre>\n
    3) Add PHPSpreadSheet library and other dependencies into the application<\/h2>\n
    When you see the PHPSpreadsheet documentation<\/a>, it provides an easy to follow installation steps.<\/p>\n
    It gives the composer command to add the PHPSpreadsheet and related dependencies into the application.<\/p>\n
    composer require phpoffice\/phpspreadsheet\n<\/code><\/pre>\n
    For PHP version 7<\/h3>\n
    Add the below specification to the composer.json file.<\/p>\n
    { \"require\": { \"phpoffice\/phpspreadsheet\": \"^1.23\" }, \"config\": { \"platform\": { \"php\": \"7.3\" } }\n}\n<\/code><\/pre>\n
    then run<\/p>\n
    composer update\n<\/code><\/pre>\n
    Note:<\/strong> PHPSpreadsheet requires at least PHP 7.3 version.<\/p>\n
    How it works<\/h2>\n
    Simple interface with export option<\/h3>\n
    This page fetches the data from the MySQL database and displays it in a grid form<\/a>. Below the data grid, this page shows an \u201cExcel Export\u201d button.<\/p>\n
    By clicking this button the action parameter is sent to the URL to call the excel export service in PHP.<\/p>\n
    index.php<\/p>\n
    <?php\nrequire_once __DIR__ . '\/lib\/Post.php';\n$post = new post();\n$postResult = $post->getAllPost();\n$columnResult = $post->getColumnName();\nif (! empty($_GET[\"action\"])) { require_once __DIR__ . '\/lib\/ExportService.php'; $exportService = new ExportService(); $result = $exportService->exportExcel($postResult, $columnResult);\n}\n?>\n<html>\n<head>\n<meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">\n<link href=\".\/style.css\" type=\"text\/css\" rel=\"stylesheet\" \/>\n<\/head>\n<body> <div id=\"table-container\"> <table id=\"tab\"> <thead> <tr> <th width=\"5%\">Id<\/th> <th width=\"35%\">Name<\/th> <th width=\"20%\">Price<\/th> <th width=\"25%\">Category<\/th> <th width=\"25%\">product Image<\/th> <th width=\"20%\">Average Rating<\/th> <\/tr> <\/thead> <tbody> <?php if (! empty($postResult)) { foreach ($postResult as $key => $value) { ?> <tr> <td><?php echo $postResult[$key][\"id\"]; ?><\/td> <td><?php echo $postResult[$key][\"name\"]; ?><\/td> <td><?php echo $postResult[$key][\"price\"]; ?><\/td> <td><?php echo $postResult[$key][\"category\"]; ?><\/td> <td><?php echo $postResult[$key][\"product_image\"]; ?><\/td> <td><?php echo $postResult[$key][\"average_rating\"]; ?><\/td> <\/tr> <?php } } ?> <\/tbody> <\/table> <div class=\"btn\"> <form action=\"\" method=\"POST\"> <a href=\"<?php echo strtok($_SERVER[\"REQUEST_URI\"]);?><?php echo $_SERVER[\"QUERY_STRING\"];?>?action=export\"><button type=\"button\" id=\"btnExport\" name=\"Export\" value=\"Export to Excel\" class=\"btn btn-info\">Export to Excel<\/button><\/a> <\/form> <\/div> <\/div>\n<\/body>\n<\/html>\n<\/code><\/pre>\n
    PHP model calls prepare queries to fetch data to export<\/h3>\n
    This is a PHP model class that is called to read data from the database. The data array will be sent to the export service to build the excel sheet object.<\/p>\n
    The getColumnName()<\/em> reads the database table column name array. This array will supply data to form the first row in excel to create a column header.<\/p>\n
    The getAllPost()<\/em> reads the data rows that will be iterated and set the data cells with the values.<\/p>\n
    lib\/Post.php<\/p>\n
    <?php\nclass Post\n{ private $ds; public function __construct() { require_once __DIR__ . '\/DataSource.php'; $this->ds = new DataSource(); } public function getAllPost() { $query = \"select * from tbl_products\"; $result = $this->ds->select($query); return $result; } public function getColumnName() { $query = \"select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=N'tbl_products'\"; $result = $this->ds->select($query); return $result; }\n}\n?>\n<\/code><\/pre>\n
    PHP excel export service<\/h3>\n
    This service helps to export data to the excel sheet. The resultant file will be downloaded to the browser by setting the PHP header() properties<\/a>.<\/p>\n
    The $postResult has the row data and the $columnResult has the column data.<\/p>\n
    This example instantiates the PHPSpreadSheet library class and sets the column header and values. Then it creates a writer object by setting the spreadsheet instance to output the data to excel.<\/p>\n
    lib\/ExportService.php<\/p>\n
    <?php\nuse PhpOffice\\PhpSpreadsheet\\IOFactory;\nuse PhpOffice\\PhpSpreadsheet\\Spreadsheet;\nuse PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx;\nuse PhpOffice\\PhpSpreadsheet\\Calculation\\TextData\\Replace;\nrequire_once __DIR__ . '\/..\/vendor\/autoload.php'; class ExportService\n{ public function exportExcel($postResult, $columnResult) { $spreadsheet = new Spreadsheet(); $spreadsheet->getProperties()->setTitle(\"excelsheet\"); $spreadsheet->setActiveSheetIndex(0); $spreadsheet->getActiveSheet()->SetCellValue('A1', ucwords($columnResult[0][\"COLUMN_NAME\"])); $spreadsheet->getActiveSheet()->SetCellValue('B1', ucwords($columnResult[1][\"COLUMN_NAME\"])); $spreadsheet->getActiveSheet()->SetCellValue('C1', ucwords($columnResult[2][\"COLUMN_NAME\"])); $spreadsheet->getActiveSheet()->SetCellValue('D1', ucwords($columnResult[3][\"COLUMN_NAME\"])); $spreadsheet->getActiveSheet()->SetCellValue('E1', str_replace('_', ' ', ucwords($columnResult[4][\"COLUMN_NAME\"], '_'))); $spreadsheet->getActiveSheet()->SetCellValue('F1', str_replace('_', ' ', ucwords($columnResult[5][\"COLUMN_NAME\"], '_'))); $spreadsheet->getActiveSheet() ->getStyle(\"A1:F1\") ->getFont() ->setBold(true); $rowCount = 2; if (! empty($postResult)) { foreach ($postResult as $k => $v) { $spreadsheet->getActiveSheet()->setCellValue(\"A\" . $rowCount, $postResult[$k][\"id\"]); $spreadsheet->getActiveSheet()->setCellValue(\"B\" . $rowCount, $postResult[$k][\"name\"]); $spreadsheet->getActiveSheet()->setCellValue(\"C\" . $rowCount, $postResult[$k][\"price\"]); $spreadsheet->getActiveSheet()->setCellValue(\"D\" . $rowCount, $postResult[$k][\"category\"]); $spreadsheet->getActiveSheet()->setCellValue(\"E\" . $rowCount, $postResult[$k][\"product_image\"]); $spreadsheet->getActiveSheet()->setCellValue(\"F\" . $rowCount, $postResult[$k][\"average_rating\"]); $rowCount ++; } $spreadsheet->getActiveSheet() ->getStyle('A:F') ->getAlignment() ->setWrapText(true); $spreadsheet->getActiveSheet() ->getRowDimension($rowCount) ->setRowHeight(- 1); } $writer = IOFactory::createWriter($spreadsheet, 'Xls'); header('Content-Type: text\/xls'); $fileName = 'exported_excel_' . time() . '.xls'; $headerContent = 'Content-Disposition: attachment;filename=\"' . $fileName . '\"'; header($headerContent); $writer->save('php:\/\/output'); }\n}\n?>\n<\/code><\/pre>\n
    Download<\/a><\/p>\n
      <\/p>\n
    \n
    Popular Articles<\/h2>\n<\/p><\/div>\n
     \u2191 Back to Top<\/a> <\/p>\n","protected":false},"excerpt":{"rendered":"
    by Vincy. Last modified on September 23rd, 2022. Export data to an excel file is mainly used for taking a backup. When taking database backup, excel format is a convenient one to read and manage easily. For some applications exporting data is important to take a backup or an offline copy of the server database. […]<\/p>\n","protected":false},"author":2,"featured_media":128320,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[65],"tags":[],"class_list":["post-128319","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-php-updates"],"_links":{"self":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts\/128319","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/comments?post=128319"}],"version-history":[{"count":0,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts\/128319\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/media\/128320"}],"wp:attachment":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/media?parent=128319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/categories?post=128319"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/tags?post=128319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}