Formatting csv output of query

Question

I managed to export result of my query to a downloadable csv, but can I format this csv output? for example with different font size, column width. Or can I turn it into xls file? also gonna need help with formatting it.

$connect = pg_connect($link);

$random = $_POST['random'];

$query = "SELECT nivelacija.broj_nivelacije, nivelacija.vezni_dokument, roba.sifra, roba.naziv, nivelacija_stavka.kolicina, nivelacija_stavka.pdv, nivelacija.datum_nivelacije, nivelacija_stavka.stara, nivelacija_stavka.nova, nivelacija_stavka.marza, nivelacija_stavka.nova - nivelacija_stavka.stara as razlika , nivelacija_stavka.kolicina * (nivelacija_stavka.nova - nivelacija_stavka.stara) as zaduzenje
from (select * from nivelacija where datum_nivelacije='$random') nivelacija
LEFT JOIN nivelacija_stavka ON nivelacija.nivelacija_id = nivelacija_stavka.nivelacija_id
LEFT JOIN roba on nivelacija_stavka.roba_id = roba.roba_id
ORDER BY nivelacija.nivelacija_id DESC;";

$resultID = pg_query($connect, $query) or die("Greska1. ");


$num_column = pg_num_fields($resultID);     

$csv_header = '';
for($i=0;$i<$num_column;$i++) {
    $csv_header .= "" . pg_field_name($resultID,$i) . "*";
}   
$csv_header .= "\n";

$csv_row ='';
while($row = pg_fetch_row($resultID)) {
    for($i=0;$i<$num_column;$i++) {
        $csv_row .= "" . $row[$i] . "*";
    }
    $csv_row .= "\n";
}
function stripInvalidXml($OriginalString) {
    $NekiKarakteriZaZamenu = array("&", "<", ">", "\"", "'" ,'"');
    $KarakteriKojiMenjaju = array("&amp;", "&lt;", "&gt;", "&quot;", "&apos;" , '');
    $ZamenjenString = str_replace($NekiKarakteriZaZamenu, $KarakteriKojiMenjaju, $OriginalString);                              
    return $ZamenjenString;
}
/* Download as CSV File */
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename=nivelacija.csv');
echo $csv_header . $csv_row;
exit;
?>

This works but I just want a way of formatting it to xls so I can maybe add company logo, change font size, column width through php since CSV is text file. So we don't need to edit xls everytime we import this csv to excel.

you should try using some php library for generating excel file...like exceljs https://github.com/guyonroche/exceljs — Dhairya Vora, Nov 20 '17 at 08:59

score 2 · Accepted Answer · answered Nov 20 '17 at 08:57

2

Since a csv file is nothing more than a text file with some comma seperated values you can't format it. As you kind of pointed out already you want to switch to an xls file (Excel) here.

You could check out some libraries, like PHPExcel, that should make it easy to create and format an Excel file.

answered Nov 20 '17 at 08:57

Niellles

868
10
27

There is a single tiny bit of formatting that can be done on CSVs, and that's forcing strings: `3,17,"2017-11-20 09:20:17.23255",Some simple string`. Upon opening in Excel, Excel would display that timestamp exactly as-is, instead of converting it to its own date format – Scoots Nov 20 '17 at 09:22
I have installed PHPExcel, how should i change code so it exports it to xls? – Nemanja J. Nov 20 '17 at 09:28
@Scoots: You are correct. I meant formatting in the way of font style, font size, etc.. – Niellles Nov 20 '17 at 10:13
@NemanjaJ.: The [examples](https://github.com/PHPOffice/PHPExcel/tree/1.8/Examples) may provide some insights. Especially the [heavily formatted](https://github.com/PHPOffice/PHPExcel/blob/1.8/Examples/22heavilyformatted.php) one. – Niellles Nov 20 '17 at 10:13
yea that one helps alot. tnx – Nemanja J. Nov 20 '17 at 11:00

score 0 · Answer 2 · answered Nov 20 '17 at 08:51

0

Firstly you need to convert your CSV to xls. This article might help you: csv to excel conversion

Then you can edit styling of your xls: How to format excel file with styles, fonts, colors, tables etc with pure PHP?

answered Nov 20 '17 at 08:51

Andrii Pryimak

797
2
10
33

Formatting csv output of query

2 Answers2