Download to CSV

Questions related to customising nuBuilder Forte with Javascript or PHP.

Download to CSV

Postby Timo » Thu Oct 17, 2019 1:06 am

HI guys,

I would like to add an additional button next to the Print button with which I can import the Browse table into a CSV file. Has anyone done this before?
Timo
 
Posts: 199
Joined: Fri Mar 16, 2018 5:56 am

Re: Download to CSV

Postby admin » Thu Oct 17, 2019 6:18 am

Timo,

You should be able to import the HTML made by the print button.

Steven
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am

Re: Download to CSV

Postby Timo » Wed Oct 23, 2019 2:39 am

Thanks for you reply! That would be the manual way. But I want the date fields, integer fields formatted the way I set them. The print function does not take into account the format I have set. Since I have a lot of columns, formatting in Excel would be too time-consuming.
Timo
 
Posts: 199
Joined: Fri Mar 16, 2018 5:56 am

Re: Download to CSV

Postby kev1n » Tue Oct 29, 2019 1:57 pm

You can add a Procedure that runs some PHP code and saves data to a file. I'm going to post some code later
kev1n
 
Posts: 364
Joined: Mon Oct 15, 2018 2:13 am

Re: Download to CSV

Postby kev1n » Tue Oct 29, 2019 4:54 pm

Here you go:

1. Create a Procedure: Builders -> Procedure -> Add
2. Code: BrowseDownloadToCSV
3. Give it a Description

4. Paste this PHP
(I could also have used nuBuilder's db connection and PHP functions. But in this way the code can also be used independently from nuBuilder)

Code: Select all
// Use nuBuilder config file for db settings
include("nuconfig.php");

// Set the CSV delimiter
$delimiter = ';';
// Default file name if no has cookie (browse_export_filename) passed
$default_file_name = 'browse-export.csv';

// Setup the filename that our CSV will have when it is downloaded.
$fileName = "#browse_export_filename#";
if (strpos($fileName, '#') === false) {
   $fileName = $default_file_name;
}

//Connect to the DB using PDO.
try {
    $pdo = new PDO("mysql:host=$nuConfigDBHost;dbname=$nuConfigDBName", $nuConfigDBUser, $nuConfigDBPassword);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e){
    echo "Error!: " . $e->getMessage() . "<br/>";
    die();
}

//Retrieve Browse SQL from hash cookie

$sql = "#browse_sql#";
// Test with: $sql = "SELECT * FROM `zzzzsys_user` LIMIT 20";

//Prepare the SQL query.
$statement = $pdo->prepare($sql);

//Execute the SQL query.
$statement->execute();

//Fetch all of the rows from our table
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

//Get the column names.
$columnNames = array();
if(!empty($rows)){
    //We only need to loop through the first row of our result
    //in order to collate the column names.
    $firstRow = $rows[0];
    foreach($firstRow as $colName => $val){
        $columnNames[] = $colName;
    }
}

//Set the Content-Type and Content-Disposition headers to force the download.
header('Content-Type: application/excel');
header('Content-Encoding: UTF-8');
header('Content-Disposition: attachment; filename="' . $fileName . '"');

//Open up a file pointer
$fp = fopen('php://output', 'w');

//Start off by writing the column names to the file.
fputcsv($fp, $columnNames, $delimiter);

//Then, loop through the rows and write them to the CSV file.
foreach ($rows as $row) {
    fputcsv($fp, $row, $delimiter);
}

//Close the file pointer.
fclose($fp);


5. Save

6. In you form's Custom Code, paste this JS:

Code: Select all
if(nuFormType() == 'browse'){
    nuAddActionButton('nuRunPHPHidden', 'Download to CSV', 'nuSetProperty("browse_sql",nuCurrentProperties().browse_sql); nuRunPHP("DownloadCSV")');
}


7. Save

Now you see a new button in your Browse Screen (Download to CSV)!
Last edited by kev1n on Fri Dec 13, 2019 5:32 pm, edited 1 time in total.
kev1n
 
Posts: 364
Joined: Mon Oct 15, 2018 2:13 am

Re: Download to CSV

Postby Timo » Thu Oct 31, 2019 7:56 am

Fantastic, this works great!

Another wish: How can I only display the button if the print permission is set and otherwise do not allow the download?
Timo
 
Posts: 199
Joined: Fri Mar 16, 2018 5:56 am

Re: Download to CSV

Postby kev1n » Sat Nov 23, 2019 7:19 pm

Only add the "Download to CSV" button if the Print button exists:

Code: Select all
if(nuFormType() == 'browse'){
    if ($('#nuPrintButton').length == 1) {
       nuAddActionButton('nuRunPHPHidden', 'Download to CSV', 'nuSetProperty("browse_sql",nuCurrentProperties().browse_sql); nuRunPHP("DownloadCSV")');
   }
}
kev1n
 
Posts: 364
Joined: Mon Oct 15, 2018 2:13 am

Re: Download to CSV

Postby Timo » Thu Dec 05, 2019 4:04 pm

all right, thank you.
Timo
 
Posts: 199
Joined: Fri Mar 16, 2018 5:56 am

Re: Download to CSV

Postby kev1n » Fri Dec 06, 2019 1:06 am

Your're welcome!
kev1n
 
Posts: 364
Joined: Mon Oct 15, 2018 2:13 am

Re: Download to CSV

Postby mariri » Fri Dec 13, 2019 2:00 am

Hi guys !

I tried the code whitout any changes and I don't know why but I have a html code in my CSV file. Is it normal ? :shock:

Thanks !
Attachments
HTML code in output file.PNG
HTML code in output file.PNG (88.46 KiB) Viewed 33 times
mariri
 
Posts: 23
Joined: Mon Sep 02, 2019 7:24 pm

Next

Return to Custom Code



cron