{"id":4454,"date":"2023-07-12T14:10:33","date_gmt":"2023-07-12T07:10:33","guid":{"rendered":"https:\/\/www.bagi2info.com\/?p=4454"},"modified":"2023-07-12T14:16:02","modified_gmt":"2023-07-12T07:16:02","slug":"membuat-laporan-excel-dari-mysql-menggunakan-php_xlsxwriter","status":"publish","type":"post","link":"https:\/\/www.bagi2info.com\/en\/creating-an-excel-report-from-mysql-using-php_xlsxwriter\/","title":{"rendered":"Creating an Excel Report from MySql using PHP_XLSXWriter"},"content":{"rendered":"\r\n<p>We&#8217;ll look at how to use <a href=\"https:\/\/github.com\/mk-j\/PHP_XLSXWriter\" target=\"_blank\" rel=\"noopener\" title=\"\">PHP_XLSXWriter<\/a> to generate an excel report from a MySQL database. The PHP_XLSXWriter library is intended to be lightweight and consume less memory.<\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-full is-style-default\"><a href=\"https:\/\/www.bagi2info.com\/wp-content\/uploads\/2023\/07\/image.png\"><img loading=\"lazy\" decoding=\"async\" width=\"921\" height=\"577\" src=\"https:\/\/www.bagi2info.com\/wp-content\/uploads\/2023\/07\/image.png\" alt=\"\" class=\"wp-image-4463\" srcset=\"https:\/\/www.bagi2info.com\/wp-content\/uploads\/2023\/07\/image.png 921w, https:\/\/www.bagi2info.com\/wp-content\/uploads\/2023\/07\/image-300x188.png 300w, https:\/\/www.bagi2info.com\/wp-content\/uploads\/2023\/07\/image-768x481.png 768w\" sizes=\"auto, (max-width: 921px) 100vw, 921px\" \/><\/a><\/figure>\r\n\r\n\r\n\r\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\r\n\r\n\r\n\r\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.bagi2info.com\/en\/creating-an-excel-report-from-mysql-using-php_xlsxwriter\/#Why_Report_using_Excel\" >Why Report using Excel?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.bagi2info.com\/en\/creating-an-excel-report-from-mysql-using-php_xlsxwriter\/#Precondition\" >Precondition<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.bagi2info.com\/en\/creating-an-excel-report-from-mysql-using-php_xlsxwriter\/#dbconphp\" >dbcon.php<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.bagi2info.com\/en\/creating-an-excel-report-from-mysql-using-php_xlsxwriter\/#reportphp\" >report.php<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Why_Report_using_Excel\"><\/span>Why Report using Excel?<span class=\"ez-toc-section-end\"><\/span><\/h2>\r\n\r\n\r\n\r\n<p>Because the majority of individuals in your business are likely acquainted with Excel, provide a short (or no) learning curve throughout installation.<\/p>\r\n\r\n\r\n\r\n<p>This allows you to mix data using formulae.<\/p>\r\n\r\n\r\n\r\n<p>It provides easy-to-use charting functionality and several handy built-in features when compared to competing tools.<\/p>\r\n\r\n\r\n\r\n<p>It&#8217;s also convenient because you can clip and paste into reports. Because most people create reports in Word, it&#8217;s simple to copy and paste data or charts from Excel into Word.<\/p>\r\n\r\n\r\n\r\n<p>Using PHP_XLSXWriter, we will construct a web application that will generate an excel report from a MySQL database.<\/p>\r\n\r\n\r\n\r\n<p>We will construct a table in the MySQL database, get entries from it, and display them in table format on a web page. We will send a link that will allow you to build an excel file from the table data. You will be prompted to save the created excel file. Then you may store the file at a convenient location.<\/p>\r\n\r\n\r\n\r\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\r\n\r\n\r\n\r\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Precondition\"><\/span>Precondition<span class=\"ez-toc-section-end\"><\/span><\/h2>\r\n\r\n\r\n\r\n<p>Mastering PHP<br>\r\nKnowledge of MySQL or other databases<\/p>\r\n\r\n\r\n\r\n<p><\/p>\r\n\r\n\r\n\r\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\r\n\r\n\r\n\r\n<pre title=\"SQL\" class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">\r\nDROP TABLE IF EXISTS `products`;\r\nCREATE TABLE `products` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `productid` varchar(255) NOT NULL,\r\n  `product_name` varchar(255) NOT NULL,\r\n  `description` text DEFAULT NULL,\r\n  `price` decimal(16,2) DEFAULT 0.00,\r\n  PRIMARY KEY (`id`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;\r\n\r\nINSERT INTO `products` (`id`, `productid`, `product_name`, `description`, `price`) VALUES\r\n(1,\t'P00001',\t'Telepon',\t'',\t5000.00),\r\n(2,\t'P00002',\t'Elektronik',\t'',\t600.00),\r\n(3,\t'P00003',\t'Televisi',\t'',\t500.00),\r\n(4,\t'P00004',\t'Router',\tNULL,\t5000.00),\r\n(5,\t'P00005',\t'Iphone',\t'Apple',\t9999.00),\r\n(6,\t'P00006',\t'Laptop',\tNULL,\t4000.00);\r\n<\/code><\/pre>\r\n\r\n\r\n\r\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\r\n\r\n\r\n\r\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"dbconphp\"><\/span>dbcon.php<span class=\"ez-toc-section-end\"><\/span><\/h2>\r\n\r\n\r\n\r\n<pre title=\"dbcon.php\" class=\"wp-block-code\"><code lang=\"php\" class=\"language-php line-numbers\">&lt;?php\r\n$mysqli = mysqli_connect(\"localhost\", \"\", \"\", \"\");\r\n\r\n\/\/ Check connection\r\nif (mysqli_connect_errno()) {\r\n  echo \"Failed to connect to MySQL: \" . mysqli_connect_error();\r\n}\r\n<\/code><\/pre>\r\n\r\n\r\n\r\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\r\n\r\n\r\n\r\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"reportphp\"><\/span>report.php<span class=\"ez-toc-section-end\"><\/span><\/h2>\r\n\r\n\r\n\r\n<pre title=\"report.php\" class=\"wp-block-code\"><code lang=\"php\" class=\"language-php line-numbers\">&lt;?php\r\n\r\ninclude_once(\"dbcon.php\");\r\ninclude_once(\"xlsxwriter.class.php\");\r\n\r\nini_set('display_errors', 0);\r\nini_set('log_errors', 1);\r\nerror_reporting(E_ALL &amp; ~E_NOTICE);\r\n\r\n$filename = \"example.xlsx\";\r\nheader('Content-disposition: attachment; filename=\"'.XLSXWriter::sanitize_filename($filename).'\"');\r\nheader(\"Content-Type: application\/vnd.openxmlformats-officedocument.spreadsheetml.sheet\");\r\nheader('Content-Transfer-Encoding: binary');\r\nheader('Cache-Control: must-revalidate');\r\nheader('Pragma: public');\r\n\r\n\/\/ GETING PARAMETER POST \/ GET\r\n\/\/ $json = file_get_contents('php:\/\/input');\r\n\/\/ \/\/ decoding the received JSON and store into $obj variable.\r\n\/\/ $obj  = json_decode($json, true);\r\n\r\n\/\/ $paramname = $obj['paramname'];  \r\n\r\n\r\n\/\/ start excel pointer\r\n$writer = new XLSXWriter();\r\n$writer->setAuthor('Some Author'); \r\n\r\n\/\/ Normal Style\r\n$styles1 = array(\r\n    'font' => 'Arial',\r\n    'font-size' => 8,\r\n    'valign' => 'center',\r\n    'halign' => 'center', 'border' => 'left,right,top,bottom'\r\n);\r\n\r\n\/\/ BOLD Style for header\r\n$styles2 = array(\r\n    'font' => 'Arial',\r\n    'valign' => 'center',\r\n    'font-style' => 'bold',\r\n    'halign' => 'center', 'border' => 'left,right,top,bottom'\r\n);\r\n\r\n\r\n\/\/==============================================================\r\n\/\/===========TITLE ============================================\r\n\/\/==============================================================\r\n\r\n$writer->writeSheetRow(\r\n    'Sheet1',\r\n    array('CONTOH LAPORAN'),\r\n    $styles2\r\n);\r\n\r\n$writer->writeSheetRow(\r\n    'Sheet1',\r\n    array('')\r\n);\r\n\r\n$writer->writeSheetRow(\r\n    'Sheet1',\r\n    array('')\r\n);\r\n\r\n\r\n\/\/ header\r\n$emparray = array();\r\n$emparray[] = \"No\";\r\n$emparray[] = \"ProductId\";\r\n$emparray[] = \"Product\";\r\n$emparray[] = \"Description\";\r\n$emparray[] = \"Price\";\r\n$writer->writeSheetRow(\r\n    'Sheet1',\r\n    $emparray,\r\n    $styles2\r\n);\r\n\r\n$query = sprintf(\r\n    \"SELECT *\r\n    FROM `products` \r\n    ORDER BY productid\",\r\n\/\/    add param for filtering\r\n\/\/    $mysqli->real_escape_string($param), \r\n);\r\n\r\n$result = $mysqli->query($query);\r\n\r\nif (!$result) {\r\n\r\n    $writer->writeSheetRow(\r\n        'Sheet1',\r\n        array('No data found'),\r\n        $styles2\r\n    );\r\n\r\n} else {\r\n\r\n    \/\/create an array\r\n    $emparray = array();\r\n    $no = 1;\r\n\r\n    while ($row = $result->fetch_assoc()) {\r\n\r\n        $dataarray = array();\r\n        $dataarray[] = $no;\r\n        $dataarray[] = $row['productid'];\r\n        $dataarray[] = $row['product_name'];\r\n        $dataarray[] = $row['description'];\r\n        $dataarray[] = number_format($row[\"price\"]);\r\n\r\n\r\n        $writer->writeSheetRow(\r\n            'Sheet1',\r\n            $dataarray,\r\n            $styles1\r\n        );\r\n\r\n        $no += 1;\r\n\r\n    }\r\n}\r\n\r\n$writer->writeSheetRow(\r\n    'Sheet1',\r\n    array('')\r\n);\r\n$writer->writeSheetRow(\r\n    'Sheet1',\r\n    array('')\r\n);\r\n\r\n$writer->writeSheetRow(\r\n    'Sheet1',\r\n    array('')\r\n);\r\n\r\n$writer->writeSheetRow(\r\n    'Sheet1',\r\n    array('Tutorial Laporan Excel')\r\n);\r\n\r\n$writer->writeSheetRow(\r\n    'Sheet1',\r\n    array('Website: bagi2info.com')\r\n);\r\n\r\n$writer->markMergedCell('Sheet1', $start_row = 0, $start_col = 0, $end_row = 0, $end_col = 2);\r\n\r\n$writer->writeToStdOut();\r\n\r\n\/\/ export to file and add date\r\n\/\/ $filename = \"laporan\" . \"-\" . date('dmY') . \".xlsx\";\r\n\r\n\/\/ $writer->writeToFile($filename);\r\n\r\nexit(0);<\/code><\/pre>\r\n\r\n\r\n\r\n<p><\/p>\r\n\r\n\r\n\r\n<p>Referensi <\/p>\r\n\r\n\r\n\r\n<p><a href=\"https:\/\/github.com\/mk-j\/PHP_XLSXWriter\">https:\/\/github.com\/mk-j\/PHP_XLSXWriter<\/a><\/p>\r\n\r\n\r\n\r\n<p><\/p>\r\n","protected":false},"excerpt":{"rendered":"<p>We&#8217;ll look at how to use PHP_XLSXWriter to generate an excel report from a MySQL database. The PHP_XLSXWriter library is intended to be lightweight and consume less memory. Why Report using Excel? Because the&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":4463,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,246],"tags":[],"class_list":["post-4454","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-komputer","category-sql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/4454","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/comments?post=4454"}],"version-history":[{"count":10,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/4454\/revisions"}],"predecessor-version":[{"id":4467,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/4454\/revisions\/4467"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/media\/4463"}],"wp:attachment":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/media?parent=4454"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/categories?post=4454"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/tags?post=4454"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}