-2

I'm trying to get my MySQL data to Excel file using php. The codes work fine but I want the field name to look like this

enter image description here

and not simply this

enter image description here.

Here is the code:

<?php 
    include_once('config.php');

   $xls_filename = 'export_'.date('Y-m-d').'.xls'; 

     $result = mysql_query("Select * from ojt_group3_pole"); 

   header("Content-Type: application/xls"); 
   header("Content-Disposition: attachment; filename=$xls_filename"); 
   header("Pragma: no-cache"); 
   header("Expires: 0"); 

   $sep = "\t"; 


   for ($i = 0; $i<mysql_num_fields($result); $i++) { 
    echo mysql_field_name($result, $i) . "\t"; 
   } 
   print("\n"); 
   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"; 
   } 
?>
Bilal Ahmed
  • 4,005
  • 3
  • 22
  • 42
Katherine
  • 13
  • 5
  • 2
    stop using mysql_* –  Jan 18 '18 at 05:49
  • First, make sure you stop using "mysql_" functions. They're deprecated and completely removed in newer versions of PHP. You should use either mysqli_ functions or PDO. – jhilgeman Jan 18 '18 at 05:50
  • your not writing excel formated data, but plain text –  Jan 18 '18 at 05:51
  • 1
    A quick and dirty solution you could try is to create the format you want using ordinary HTML tables. Just remove the headers while building it. When it looks correct in the browser, add the headers back and it might work. – M. Eriksson Jan 18 '18 at 06:00

1 Answers1

0

Your current code is basically generating a CSV file and giving it a ".xls" extension and expecting Excel to do the work of correcting the problem when the file is opened.

The CSV format does not support all the features that Excel has, so things like cell merging and styles are not going to be something you can do with your current approach.

You'll have to use a library like PHPExcel (there are likely others, too) to generate true Excel files and then write the code to do things like mergeCells() to set up the spreadsheet the way you want it to look. A very basic example:

<?php
// Load the libraries
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';

// Create a new PHPExcel instance
$objPHPExcel = new PHPExcel();

// Set the workbook properties
$objPHPExcel->getProperties()->setCreator("Your name here");
$objPHPExcel->getProperties()->setTitle("My Excel Document");

// Add data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello world!');
$objPHPExcel->getActiveSheet()->mergeCells('A1:B1');

// Save the file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save("mynewfile.xlsx");

EDIT: As someone pointed out, PHPExcel is deprecated. There's a question here on SO that lists out some alternatives: Alternative for PHP_excel

The underlying point here, though, is that if you want to make use of Excel's formatting features, you'll have to generate a true Excel document.

jhilgeman
  • 1,543
  • 10
  • 27