Jump to content

PHP - MySQL Data Output to MS Excel


PimCaris

Recommended Posts

Dear Forum Members,

 

I'm trying to create a snippet of PHP code which can select a specific number of rows from a Database table (Database "formulier", "tables under invoer"), and output this data into a .cvs file.

The script: export.php

This script will be running on a website, where after people click on a button and submit a client-specfic ID and Last Name, they get a .csv to download for administrative use.

 

Needless to say, I want the script to generate a .csv file with all entries (different dates of entries, total 23 variables) of one ID-number from the database.

I've started to work on this, and whilst it works for the larger part, I'm really stuck at the moment.

I hope that some of you can shed some light on the issue.

 

Attached are the scripts I'm currently running.

formulier.htmlinsert.php

Please note that they're definetly not final, and still insecure.

That's not the issue right now.

 

Kind regards,

Link to comment
Share on other sites

I've had good luck with the PHPExcel package for this sort of thing.  Brief example:

include "includes/PHPExcel.php";
 
 
      $template_file="includes/Load_Form.xlt";
 
      $objPHPExcel = PHPExcel_IOFactory::load($template_file);
 
      $dater=date("m/d/Y");
 
      $objPHPExcel->getProperties()
            ->setCreator("Foo.com")
            ->setLastModifiedBy("Foo.com/excel.php")
            ->setTitle("Foo.com Sample Inventory")
            ->setSubject("Products for Sale")
            ->setDescription("This is all data on products on FOO.com");
 
      $objPHPExcel->setActiveSheetIndex(0); // first sheet in the active sheet
      $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(1);

     $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
 
   /* Looping structure for data dump */
 
      $sheetrow=6; // Set header cell rows
 
      $objValidatin = $objPHPExcel->getActiveSheet()->getCell('A6')->getDataValidation();
      $objValidatin->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
      $objValidatin->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
      $objValidatin->setAllowBlank(false);
   
     //blah, blah .....
 
     //some kind of loop around this
 

            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $sheetrow, "Products for Sale");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $sheetrow, $category);
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $sheetrow, $brandname);
 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $sheetrow, str_replace($bad_char_list,$repl_char_list,$title));
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $sheetrow, str_replace("\n",'',nl2br(str_replace($bad_char_list,$repl_char_list,$description))));
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $sheetrow, $price);
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $sheetrow, urldecode($postcurrency));
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $sheetrow, urldecode($condition));
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $sheetrow, urldecode($product_type));

Made life pretty easy where "Excel" is concerned.
Link to comment
Share on other sites

Needless to say, I want the script to generate a .csv file with all entries (different dates of entries, total 23 variables) of one ID-number from the database.

I've started to work on this, and whilst it works for the larger part, I'm really stuck at the moment.

I hope that some of you can shed some light on the issue.

 

Wait. So what IS the issue. The title suggests you want an Excel file, but the content of your post states you want a CSV file. Then you state it "works" for the most part and you are now "stuck". But, you don't state what problem you are currently facing.

Link to comment
Share on other sites

OK, I've looked at your code and there seemed to be quite a few problems. For example, you were using mysql and mqsqli functions.  I have rewritten it as two functions. One to get the data from the database and another one to create/output the CSV file. That way you can reuse the output CSV function for other datasets as needed.

 

 

<?php
 
function getInvoerData()
{
 
    //Connect to database
    $con = mysqli_connect("localhost", "root", "root", "formulier");
    //Check if connection failed
    if (!$con)
    {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      exit;
    }
 
    //Create and run query to get the data
    $ID         = intval($IDnummer_E);
    $Achternaam = mysqli_real_escape_string($con, $Achternaam_E);
    $query = "SELECT * FROM invoer WHERE IDnummer = '{$ID}' AND Achternaam = '{$Achternaam}'";
    $result = mysqli_query($con, $query);
    //Check if query failed
    if(!$result)
    {
        echo 'Could not run query: ' . mysqli_error($con);
        exit;
    }
 
    //Put data into array
    $data = array();
    while($row = mysqli_fetch_assoc($result))
    {
        $data[] = $row;
    }
    return $data;
}
 
function csv_export($data, $baseName)
{
    //Add a time stamp to file name, to avoid duplicates
    $date = new DateTime();
    $ts = $date->format('Y-m-d His'); //Note: file names cannot have ':'
    $filename = "{$baseName}_{$ts}.csv";
 
    //Set header to expect a CSV file and bring up the save dialog
    header("Content-Type: text/csv");
    header("Content-Disposition: attachment; filename={$filename}");
    header("Pragma: no-cache");
    header("Expires: 0");
 
    //Open up the output buffer as a "file"
    $fh = fopen('php://output', 'w');
 
    //Create the header row
    fputcsv($fh, array_keys($data[0]));
    //Output the data
    foreach($data as $row)
    {
        //Add the current line to the output buffer
        fputcsv($fh, $row);
    }
 
    // Close the output buffer
    fclose($fh);
}
 
 
 
//Execute the functions
$data = getInvoerData($_POST["IDnummer"], $_POST["Achternaam"]);
csv_export($data, 'report');
exit();
 
?>
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.