Json to Excel CSV or XLS using jquery and php

Question

I am new in json. I generated jason data from mysql table using php and want to export the generated json to .xls or .csv format. I used the following php code to generate the json data: examexport.php

<?php
    include 'conn.php';

    $page = isset($_POST['page']) ? intval($_POST['page']) : 1;
    $rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
    $semester = isset($_POST['semester']) ? 
    mysql_real_escape_string($_POST['semester']) : '';
    $entry = isset($_POST['entry']) ? mysql_real_escape_string($_POST['entry']) : '';
    $batch = isset($_POST['batch']) ? mysql_real_escape_string($_POST['batch']) : '';

    $offset = ($page-1)*$rows;

    $result = array();

$where = "semester like '$semester%' and entry like '$entry%' and batch like '$batch%'";
    $rs = mysql_query("select count(*) from ba_test where " . $where);
    $row = mysql_fetch_row($rs);
    $result["total"] = $row[0];

$rs = mysql_query("select * from ba_test where " . $where . " limit $offset,$rows");

$items = array();
while($row = mysql_fetch_object($rs)){
        array_push($items, $row);
    }
    $result["rows"] = $items;

    echo json_encode($result);
?>

Here in stackoverflow I got code for sending json to csv format but could not make it working. My php generated json:

 {"total":"6","rows":
 [{"id":"2","regd":"25","name":"Lalhmangaihsangi","class":"BA",
"rollno":"3","univ_roll":"UNVI573","univ_no":"MZU876","core":"Education",
"semester":"First","batch":"2014","subject":"Education",
"entry":"Second Internal Test","date":"2014-07-23",
"score":"55","fm":"100","remark":"She is guarded"}]}

Code for export to excel:

<input type="button" onclick="exportExcel()" value="Export to Excel " />
<script>                
  function exportExcel(){
    $('#dg').datagrid('load',{ //load data by semester/batch/entry
    semester: $('#semester').val(),
    batch: $('#batch').val(),
    entry: $('#entry').val(),
    document.location='excel/examexport.php'// How do I include entry/batch/ here?
 });
 }
</script>

Please check http://stackoverflow.com/questions/24972547/export-datagrid-to-excel-using-jquery-easyui You can achieve csv with the same method. — Mawia HL, Jul 27 '14 at 05:45

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

Instead of creating a JSON and then convert it into a CSV file, you could directly create a string/text of the CSV with the values that you fetch from the database.

You can have a look at the format of the CSV from here and create your CSV string accordingly, http://en.wikipedia.org/wiki/Comma-separated_values#Example

After creating the string/text you can write it to a file. Then set the header content to make the file being saved from the browser,

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=file.csv");
header("Pragma: no-cache");
header("Expires: 0");

Then return the response.

You can also refer this SO question for more clarification on the PHP to CSV creation, Create a CSV File for a user in PHP

edited May 23 '17 at 12:06

Community

1
1

answered Jul 26 '14 at 06:57

Domain

11,562
3
23
44

The problem is that I used json for displaying datagrid with search functionality and I want to export the search result into excel file. – Roise Ali Jul 26 '14 at 07:05
You should refer this SO answer, http://stackoverflow.com/questions/20667418/converting-json-to-csv-format-using-php – Domain Jul 26 '14 at 07:18

Json to Excel CSV or XLS using jquery and php

1 Answers1