How to Export Html Table to CSV and PDF with formatting using Php

Question

I created Html Table, in which there is data from mysql tables, and now i want to export the data into CSV and PDF file, how?
I want to, when i click on button (separate for both CSV and PDF), it automatically download in CSV and PDF file with correct Formatting.
I try some of script i found on internet, but it is not working, some have formatting issue and save have downloading.
In Advance Thanks.

<table id="example2" class="table table-bordered table-hover">
                <thead>
                <tr>
        <th>District</th>
        <th>Union Council</th>
        <th>Village</th>
        <th>Recreational</th>
        <th>Awareness Raising</th>
        <th>Training</th>
        <th>Social Mobilization</th>
        <th>Total Activities</th>

                </tr>
                </thead>
                  <tbody>
      <tr>
      <td style="color:red;"><b><?php echo getTotalDistrictUnique();?></b></td>
      <td style="color:red;"><b><?php echo getTotalUnionCouncilUnique();?></b></td>
      <td style="color:red;"><b><?php echo getTotalVillages(); ?></b></td>
      <td style="color:red;"><b><?php echo getTotalRecreational();?></b></td>
      <td style="color:red;"><b><?php echo getTotalAwareness();?></b></td>
      <td style="color:red;"><b><?php echo getTotalTraining();?></b></td>
      <td style="color:red;"><b><?php echo getTotalSocial();?></b></td>
      <td style="color:red;"><b><?php echo getTotalRecreational() + getTotalAwareness() + getTotalTraining() + getTotalSocial(); ?></td>

      </tr>

      <?php
include("connection.php");
$query ="select * from general";
$run =mysqli_query($con,$query);
while ($row=mysqli_fetch_array($run)) {
    $id=$row['id'];
    $createdate=$row[1];
    $createday=$row[2];
    $partnername=$row[3];
    $district=$row[4];
    $unioncouncil=$row[5];
    $village=$row[6];
    $vannumber=$row[7];
    $facilitator=$row[8];
    $beneficiarytype=$row[9];
    $rmultimedia=$row[10];
    $rgame=$row[11];
    $rsprort=$row[12];
    $rart=$row[13];
    $rgroupreading=$row[14];
    $rother=$row[15];
    $alandminer=$row[16];
    $apersonalsafety=$row[17];
    $abirthregister=$row[18];
    $aother=$row[19];
    $tstickstone=$row[20];
    $tohthers=$row[21];
    $sbirthregister=$row[22];
    $sother=$row[23];
    $formnumber=$row[24];
    $submitdatatime=$row[25];
?>

      <tr>
      <td><?php echo $district?></td>
      <td><?php echo $unioncouncil?></td>
      <td><?php echo $village?></td>
      <td>
      <?php 
      if($rmultimedia=='true')
       {
        $rmultimedia_value = 1;
        }
        else{
            $rmultimedia_value = 0;
        }

        if($rgame=='true')
       {
        $rgame_value = 1;
        }
        else{
            $rgame_value = 0;
        }

        if($rsprort=='true')
       {
        $rsprort_value = 1;
        }
        else{
            $rsprort_value = 0;
        }

        if($rart=='true')
       {
        $rart_value = 1;
        }
        else{
            $rart_value = 0;
        }

        if($rgroupreading=='true')
       {
        $rgroupreading_value = 1;
        }
        else{
            $rgroupreading_value = 0;
        }

        if($rother=='true')
       {
        $rother_value = 1;
        }
        else{
            $rother_value = 0;
        }

      $recreational_sum = $rmultimedia_value + $rgame_value + $rsprort_value + $rart_value + $rgroupreading_value + $rother_value;

      echo $recreational_sum;?></td>

      <td>
          <?php 
      if($alandminer=='true')
       {
        $alandminer_value = 1;
        }
        else{
            $alandminer_value = 0;
        }

        if($apersonalsafety=='true')
       {
        $apersonalsafety_value = 1;
        }
        else{
            $apersonalsafety_value = 0;
        }

        if($abirthregister=='true')
       {
        $abirthregister_value = 1;
        }
        else{
            $abirthregister_value = 0;
        }

        if($aother=='true')
       {
        $aother_value = 1;
        }
        else{
            $aother_value = 0;
        }

      $awareness_raising_sum = $alandminer_value + $apersonalsafety_value + $abirthregister_value + $aother_value;

      echo $awareness_raising_sum;?>
      </td>

      <td>
              <?php 
      if($tstickstone=='true')
       {
        $tstickstone_value = 1;
        }
        else{
            $tstickstone_value = 0;
        }

        if($tohthers=='true')
       {
        $tohthers_value = 1;
        }
        else{
            $tohthers_value = 0;
        }

      $training_sum = $tstickstone_value + $tohthers_value;

      echo $training_sum;?>
      </td>

      <td>
        <?php 
      if($sbirthregister=='true')
       {
        $sbirthregister_value = 1;
        }
        else{
            $sbirthregister_value = 0;
        }

        if($sother=='true')
       {
        $sother_value = 1;
        }
        else{
            $sother_value = 0;
        }

      $social_mobilization_sum = $sbirthregister_value + $sother_value;

      echo $social_mobilization_sum;?>
      </td>

      <td style="color:red;"><?php echo $recreational_sum + $awareness_raising_sum + $training_sum + $social_mobilization_sum;?></td>

      </tr>
<?php } ?>
    </tbody>
              </table>

you should use foreach and mysqli_fetch_assoc to get more readbale code... CSV has no formatting, you can use some phpexcel project like https://github.com/PHPOffice/PHPExcel. Creating PDF is a option - see https://tcpdf.org/examples/ - there are a lot of examples... — Yedidia, Jun 21 '17 at 05:30
You have to try this because in CSv we can not do format much more https://stackoverflow.com/questions/4249432/export-to-csv-via-php — Arshad Shaikh, Jun 21 '17 at 05:32
For PDF try this https://stackoverflow.com/questions/10496122/pdf-export-in-php — Arshad Shaikh, Jun 21 '17 at 05:33

score 2 · Accepted Answer · answered Jun 21 '17 at 06:12

Download CSV from HTML from end

$(function () {
    $(".export-csv").on('click', function (event) {
        // CSV
        var filename = $(".export-csv").data("filename")
        var args = [$('#fixed_table'), filename + ".csv", 0];
        exportTableToCSV.apply(this, args);
    });
    $(".export-txt").on('click', function (event) {
        // txt
        var filename = $(".export-txt").data("filename")
        var args = [$('#fixed_table'), filename + ".txt", 0];
        exportTableToCSV.apply(this, args);
    });

    
    function exportTableToCSV($table, filename, type) {
        var startQuote = type == 0 ? '"' : '';
        var $rows = $table.find('tr').not(".no-csv"),
            // Temporary delimiter characters unlikely to be typed by keyboard
            // This is to avoid accidentally splitting the actual contents
            tmpColDelim = String.fromCharCode(11), // vertical tab character
            tmpRowDelim = String.fromCharCode(0), // null character
            // actual delimiter characters for CSV/Txt format
            colDelim = type == 0 ? '","' : '\t',
            rowDelim = type == 0 ? '"\r\n"' : '\r\n',
            // Grab text from table into CSV/txt formatted string
            csv = startQuote + $rows.map(function (i, row) {
                var $row = $(row),
                    $cols = $row.find('td,th');
                return $cols.map(function (j, col) {
                    var $col = $(col),
                        text = $col.text().trim().indexOf("is in cohort") > 0 ? $(this).attr('title') : $col.text().trim();
                    return text.replace(/"/g, '""'); // escape double quotes

                }).get().join(tmpColDelim);

            }).get().join(tmpRowDelim)
                .split(tmpRowDelim).join(rowDelim)
                .split(tmpColDelim).join(colDelim) + startQuote;
        // Deliberate 'false', see comment below
        if (false && window.navigator.msSaveBlob) {
            var blob = new Blob([decodeURIComponent(csv)], {
                type: 'text/csv;charset=utf8'
            });

              window.navigator.msSaveBlob(blob, filename);

        } else if (window.Blob && window.URL) {
            // HTML5 Blob        
            var blob = new Blob([csv], { type: 'text/csv;charset=utf8' });
            var csvUrl = URL.createObjectURL(blob);

            $(this)
                .attr({
                    'download': filename,
                    'href': csvUrl
                });
        } else {
            // Data URI
            var csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);

            $(this)
                .attr({
                    'download': filename,
                    'href': csvData,
                    'target': '_blank'
                });
        }
    }

});

<html>
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Export CSV</title>
<script 
 src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js">
</script>
    <style type="text/css">
        #page-wrapper {
             margin: 0 0 0 0; 
        }
    </style>
</head>
<body>
    <div id="wrapper">
        <!-- Page Content -->
        <div id="page-wrapper">
            <div class="container-fluid">
                <div class="row">
                    <div class="col-lg-12">
                       

<style>


</style>
<h2>
    Export to CSV 

    <!-- Single button -->
    <span class="btn-group  pull-right">
        <ul class="dropdown-menu">
            <li><a href=javascript:; class="export-csv" data-filename="CSVFILE">CSV</a></li>
            <li><a href=javascript:; class="export-txt" data-filename="TXTFILE">Flat file</a></li>
        </ul>
    </span>

</h2>
<hr />

<div class="row">
    <div class="col-md-2 col-sm-4 col-xs-12">

    </div>
</div>
<div class="row">
    <div class="col-md-12">
        <div class="table-responsive">
            <table id="fixed_table" class="table table-condensed  row-border order-column" cellspacing="0">
                <thead>
                    <tr>
                       <th>First Header</th>
                        <th >Second Header</th>

                       

                    </tr>
                </thead>
                <tbody>
                        <tr>
                            <td class="text-center"> First Row column 1</td>
                                <td>First Row column 2</td>
                            
                        </tr>
      </tbody>
            </table>
        </div>
    </div>
</div>






</body>
</html>

I am not sure in PHP what I think you need Any pdf library like fPDF — Ankesh Kumar, Jun 21 '17 at 06:42
If you wants shortcut, client side option, use: window print option in javascript that will print table in pdf — mukund, Jun 21 '17 at 06:59
sorry, i didnt understand... please tell me how to print table? — Muhammad Naeem, Jun 21 '17 at 07:13

score 0 · Answer 2 · answered Jun 21 '17 at 05:32

0

CSV has no formatting, you can use some phpexcel project like github.com/PHPOffice/PHPExcel. Creating PDF is a option - most known is TCPDF, see tcpdf.org/examples - There are a lot of examples.

answered Jun 21 '17 at 05:32

Yedidia

969
7
12

mukund · Answer 3 · 2017-06-21T06:29:54.913

0

For creating csv you need array and can do as below:

$list = array (
    array('aaa', 'bbb', 'ccc', 'dddd'),
    array('123', '456', '789'),
    array('"aaa"', '"bbb"')
);

$fp = fopen('file.csv', 'w');

foreach ($list as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);

For exporting html table into pdf you can use mpdf:

Table Example: https://github.com/mpdf/mpdf-examples/blob/development/example05_tables.php

To direct download file you can do as follows as well:

function array_to_csv_download($array, $filename = "export.csv", $delimiter=";") {
    header('Content-Type: application/csv');
    header('Content-Disposition: attachment; filename="'.$filename.'";');

    // open the "output" stream
    // see http://www.php.net/manual/en/wrappers.php.php#refsect2-wrappers.php-unknown-unknown-unknown-descriptioq
    $f = fopen('php://output', 'w');

    foreach ($array as $line) {
        fputcsv($f, $line, $delimiter);
    }
}

Usage:

array_to_csv_download(array(
  array(1,2,3,4),
  array(1,2,3,4)),
  "export.csv"
);

edited Jun 21 '17 at 06:29

answered Jun 21 '17 at 05:44

mukund

2,253
1
18
31

I want to export table which get data from mysql database, so how to write the table data on csv file – Muhammad Naeem Jun 21 '17 at 05:58
Select columns, which are required for the csv in query. 1) Open the file pointer $fp using fopen, 2) loop through the result while() inside that loop use fputcsv as per your field array and 3) close the pointer – mukund Jun 21 '17 at 06:05
when i click on button it open new page, where i paste this code, but it didnt download file? – Muhammad Naeem Jun 21 '17 at 06:12
I have added another way to download it – mukund Jun 21 '17 at 06:55

score 0 · Answer 4 · answered Jun 21 '17 at 06:39

Use this code for get table values

$row = mysqli_fetch_assoc($sql)   //$sql variable used to fetch custom table query

Here creates the csv file and writes the values inside the csv

$file = fopen("file.csv","w");    //csv file created here with write mode

foreach ($row as $line){
  fputcsv($file,explode(',',$line));
}

fclose($file);                   //csv file closed here

How to Export Html Table to CSV and PDF with formatting using Php

4 Answers4