Export from PHP to Excel

Export MySQL data to Excel in PHP

Just Try With The Following :

PHP Part :

<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "username"; //MySQL Username     
$DB_Password = "password";             //MySQL Password     
$DB_DBName = "databasename";         //MySQL Database Name  
$DB_TBLName = "tablename"; //MySQL Table Name   
$filename = "excelfilename";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    }   
?>

I think this may help you to resolve your problem.

How to export this table of data to Excel file from php

I suggest you to use javascript library, it work for me
Install excell javascript and FileSaver for saving file
Adding 2 library

<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>

XLX Js

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.1/xlsx.full.min.js"></script>

The script

<script>
  $('#download-btn').on('click', function(){
    var wb = XLSX.utils.table_to_book(document.getElementById('my-table'),{sheet: "Sheet name"})

    var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: true, type: 'binary'});

    function s2ab(s) {
      var buf = new ArrayBuffer(s.length);
      var view = new Uint8Array(buf);
      for (var i = 0; i < s.length; i++) {
        view[i] = s.charCodeAt(i) & 0xFF;
      }
      return buf;
    }

    saveAs(new Blob([s2ab(wbout)], {type:"application/octet-stream"}), 'test.xlsx');
  })
</script>

Export CSV for Excel

Despite the "C=comma" in CVS, Excel uses your locale native separator. So supposing fputcsv always uses a comma, it won't work, if your locale separator is for example a semicolon.

What Google AdSense does, when you click "Export to Excel CSV", is that it uses Tab as a separator. And that works.

To replicate that, set the third parameter (delimiter) of fputcsv to override the default comma. E.g. for Tab use: fputcsv($handle, $fields, "\t");

Compare the format of the CSV that works for you against the one generated by fputcsv.

Consider including example of both in your question. You might get better answers.

Export from PHP to Excel

I just got done with this yesterday. Using PHPExcel, I had no problems reading in a "master" document with formatting, writing 20-100 rows of content, and saving off the file (I save it "to screen" for immediate download. While some people on the forums complained about speed and overhead, I'm pushing a lot of data its way and it doesn't have any problem at all doing what it advertises.

Note that somewhere I read to do styling in series as opposed to in loops when possible. For example, style a1:a50 as opposed to style->a1, style->a2 in a loop. Apparently, the two different scenarios have very different memory implications.

The only gotcha I found was a few quirks between outputting and reading Excel 2003 files. If you're working entirely in XLSX files, it should function exactly as documented.

exporting php data into excel in text data type

A possible solution is to create a new value Binder method to override the PHPExcel method. (PHPExcel tries to guess the datatype we insert in the cells. So, following the advice of markBaker, I created this class to override this method:

class PHPExcel_Cell_MyValueBinder extends PHPExcel_Cell_DefaultValueBinder
implements PHPExcel_Cell_IValueBinder 
{ 
    public function bindValue(PHPExcel_Cell $cell, $value = null) { 
        // sanitize UTF-8 strings 
        if (is_string($value)) { 
            $value = PHPExcel_Shared_String::SanitizeUTF8($value); 
        } 

        // if it is a string and starts with 0, the value will be converted into string 
        if (is_string($value) && $value[0] == '0') { 
            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); 
            return true; 
        } 
        return parent::bindValue($cell, $value); 
    } 
} 

this was a tip that markbaker recommended to me in this question.

How to generate an .xlsx using php

SimpleXLSXGen

$books = [
    ['ISBN', 'title', 'author', 'publisher', 'ctry' ],
    [618260307, 'The Hobbit', 'J. R. R. Tolkien', 'Houghton Mifflin', 'USA'],
    [908606664, 'Slinky Malinki', 'Lynley Dodd', 'Mallinson Rendel', 'NZ']
];
$xlsx = Shuchkin\SimpleXLSXGen::fromArray( $books );
$xlsx->saveAs('books.xlsx');
//  $xlsx->downloadAs('books.xlsx');

Add another sheet to excel export from PHP

The reason you can't make this work is that you are not creating an Excel export here.

You're creating a plain-text, tab-delimited file. Excel can import files in that format, but it's not a true Excel file. It's a simple flat file, and as such, it doesn't support multiple worksheets, unlike a real Excel file.

If you want to solve this, you need to rewrite this code so that it outputs a file which is in the real Excel file format - it's considerably more complex than a tab-delimited text format. There are various libraries available for PHP which can help you easily create real Excel files in xlsx format. Recommendations are off-topic here but they are not hard to find using a search engine.

---

Related Topics