0

I have to fetch the result of a mysql query to a "user friendly" excel (.xls or .xlsx) table. I don't want to use imports or packages for php.

This is what I got so far:

<?php

function query_to_csv($database, $query, $filename, $attachment = false, $headers = true) {

if ($attachment) {
    // send response headers to the browser
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment;filename=' . $filename);
    $fp = fopen('php://output', 'w');
} else {
    $fp = fopen($filename, 'w');
}

$result = mysqli_query($database, $query) or die(mysqli_error($database));
foreach ($result as $fields) {
    fputcsv($fp, $fields);
    
}
    
    fclose($fp);
}

This is the Result

How can I get the array sperated in different Cells?

Mario S.
  • 11
  • 1
  • That data looks like it's probably fine. It's likely just the way you imported it into Excel that's wrong. Try looking at the data in a text editor, so you can see the raw CSV data. I expect it will look ok. – ADyson Jul 01 '20 at 12:23
  • 1
    @ADyson I agree, i think he used different delimiter to open csv file in excel. Try to chose `comma` as a delimiter. – Serghei Leonenco Jul 01 '20 at 12:25
  • 1
    But of course this code will not produce a real XLS or XLSX file, it'll only ever be a CSV text file. If you want to create a genuine Excel file then you'll need to use a library (unless you fancy implementing the openXML standard yourself of course, but that would be rather a lot of work). What's your objection to using some sort of library or package, exactly? There are some free ones around I think which could do the job you want. – ADyson Jul 01 '20 at 12:25
  • @ADyson I know that the data is ok. I just want a idea how to fetch Comma Seperated Values (CSV) into a excel table. Each Value should have a different Cell. – Mario S. Jul 01 '20 at 12:29
  • Well ok but that's not a programming question. Just consult the Excel documentation on how to import a CSV or other delimited file correctly. You need to make sure you specify the correct delimiters etc. This site is for programming questions, not "how to use Excel" questions. – ADyson Jul 01 '20 at 12:30
  • @ADyson u got me there. I know i can specify the delimiters, but only in Programm itself right? I don't want the user to do that. Regarding the libraries I know PHP Excel or PhpSpreadsheet excist, but its hard to crate a .xls file there and fetch the query results in a nice table for a beginner. – Mario S. Jul 01 '20 at 12:41
  • "I don't want the user to do that" ...importing into Excel is the user's job. You cannot control that. They need to understand how to do it correctly. A CSV file itself does not know or care anything about Excel, it's just a bunch of text. You cannot put anything into the file which directly controls the way it is opened in Excel. – ADyson Jul 01 '20 at 12:51
  • Yes maybe those other libraries are tricky for beginners, but it's not like there aren't dozens of examples of usage out there. e.g. I googled "phpexcel tutorial" and this showed pretty high up the results. See the accepted answer - looks like it would be very similar to what you want: https://stackoverflow.com/questions/11360461/using-phpexcel-to-make-automatic-generated-excel-files . Or there's an entire tutorial here: https://www.sitepoint.com/generate-excel-files-charts-phpexcel/ . And plenty more available from a brief search. – ADyson Jul 01 '20 at 12:54
  • 1
    @ADyson yeah... u know the biggest threat sits behind the screen. :) Well anyway I got it now using PHP Excel and set with that the delimiters. U helped a lot thanks ADyson! – Mario S. Jul 01 '20 at 12:56
  • It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Jul 01 '20 at 14:50

1 Answers1

0

Sadly I had to use a library named PHP Excel (old) or PhpSpreadsheet (new).

$objReader->setDelimiter(',');

That's the PHP Excel Function to set the Delimiter.

Mario S.
  • 11
  • 1