is it possible to create an excel spreadsheet on the fly with the infomation in mysql.
i want to design the spreadsheet and have it read the bits i require from mysql for users to then save as .xls and/or print.
is it possible to create an excel spreadsheet on the fly with the infomation in mysql.
i want to design the spreadsheet and have it read the bits i require from mysql for users to then save as .xls and/or print.
It would be utterly simply in SQL Server Using Data Transformation Services.
To do it via MYSQL would require programming effort to open an XLS File, run a query and pour the results into the spreadsheet.
TiG
-- Hexus Meets Rock! --
If you want to do it on the fly you would have to do it in a vba macro probably. Or do you just want to export it from mysql as something excel can read, if so the simplest option is a quick application to read the data and dump it to a csv file.
There's various ways to approach this, but the simplest one is just to create a CSV, since that's just a text-based format. When you install Excel it makes itself the default application for CSVs, and doesn't have any problems opening them, and it saves using the Excel ODBC drivers, scripting Office or anything like that. If you were needing to do formulae or the like, then scripting Office may be useful, but for just showing a table in Excel for printing, use CSV.
-----------------------------------------------
David Burton
Starfall Games - Poker Chip Sets and Accessories
-----------------------------------------------
If it's not poker or computers I'm probably not interested...
hello all, back again for the new year.
new year, new pestering questions.
who has the knowledge and ability and patience, to help/assist me with this. im sure the sql side is pretty simple (id hope so anyway)
basically, at our company we email our receptionist with our weekly schedules, and she inputs them weekly, consuming time and effort. then a spreadsheet is sent out (like this)
in order to avoid this procedure, and improve my non existant sql skills, i wrote some crappy code, which lets users input their name and mon-fri schedule and outputs it like below. this is very basic!
ideally i want it to look like:
with the week numbers and date automatically updating every monday and sectioning my output up into departments, with names in static locations. i believe the hardest part will be making sure the right person goes into the right department, i could be wrong.
i hope someone out there is capable of helping me, and hope this isnt going to be a massive job. if no-one can help, how hard do you think this looks like being.
Last edited by Vini; 10-01-2006 at 12:35 PM.
Here's some old code I wrote about 4 years ago so it's not up to much but it works. All you need is a SQL query to get all the data you want to export and then pass it into this. You can use it to output to Word or Excel. I don't have time to go through it in great detail but it's commented so you should be able to work it out
PHP Code:// set $use_title = 1 to generate title, 0 not to use title
$use_title = 1;
// define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
// define title for .doc or .xls file
$title = " Export Showing ".$_GET["title"]." on ".$now_date;
// if the word parameter is set then export to Word otherwise export to Excel
if ($_GET["word"]){
$file_type = "msword";
$file_ending = "doc";
} else {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/".$file_type);
header("Content-Disposition: attachment; filename=database_dump.".$file_ending);
header("Pragma: no-cache");
header("Expires: 0");
// format for Word
if (isset($_GET["word"]))
{
//create title with timestamp:
if ($use_title == 1)
{
echo("$title\n\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\n"; //new line character
while($row = mysql_fetch_row($result))
{
//set_time_limit(60);
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
//define field names
$field_name = mysql_field_name($result,$j);
//will show name of fields
$schema_insert .= "$field_name:\t";
if(!isset($row[$j]))
{
$schema_insert .= "NULL".$sep;
} elseif ($row[$j] == " ") {
$schema_insert .= "NULL".$sep;
} elseif ($row[$j] != "") {
$schema_insert .= str_replace($sep," ",$row[$j]).$sep;
} else {
$schema_insert .= "".$sep;
}
} // end of for loop that prints out all fields
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
//end of each mysql row
//creates line to separate data from each MySQL table row
print "\n----------------------------------------------------\n\n";
} // end of while loop that prints out all the rows from the query
// now do the same for Excel
} else {
//create title with timestamp:
if ($use_title == 1)
{
echo("$title\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] == " ")
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != ""){
$row[$j] = preg_replace("/\t/", " ", $row[$j]);
$schema_insert .= "$row[$j]".$sep;
}
else
$schema_insert .= "".$sep;
} // end of for loop that prints out all fields
$schema_insert = str_replace($sep."$", "", $schema_insert);
//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
} // end of while loop that prints out all the rows from the query
} // end of if statement which decides whether to output to a word or excel file
looks good, i think. but ive hit my wall of little knowledge - at actually implementing it.
is what i got, ive nicked the phpfreak bit which goes as far as 'selecting all my data and exporting it' i realise you dont have the time, and dont expect any help, but if there is anyone else who could offer suggestions im ears open!PHP Code:<?php
define(db_host, "localhost");
define(db_user, "vini");
define(db_pass, "sss");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "schedule");
mysql_select_db(db_name);
$select = "SELECT * FROM schedule";
$export = mysql_query($select);
$fields = mysql_num_fields($export);
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
$use_title = 1 to generate title, 0 not to use title
$use_title = 1;
// define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
// define title for .doc or .xls file
$title = " Export Showing ".$_GET["title"]." on ".$now_date;
// if the word parameter is set then export to Word otherwise export to Excel
if ($_GET["word"]){
$file_type = "msword";
$file_ending = "doc";
} else {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/".$file_type);
header("Content-Disposition: attachment; filename=database_dump.".$file_ending);
header("Pragma: no-cache");
header("Expires: 0");
// format for Word
if (isset($_GET["word"]))
{
//create title with timestamp:
if ($use_title == 1)
{
echo("$title\n\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\n"; //new line character
while($row = mysql_fetch_row($result))
{
//set_time_limit(60);
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
//define field names
$field_name = mysql_field_name($result,$j);
//will show name of fields
$schema_insert .= "$field_name:\t";
if(!isset($row[$j]))
{
$schema_insert .= "NULL".$sep;
} elseif ($row[$j] == " ") {
$schema_insert .= "NULL".$sep;
} elseif ($row[$j] != "") {
$schema_insert .= str_replace($sep," ",$row[$j]).$sep;
} else {
$schema_insert .= "".$sep;
}
} // end of for loop that prints out all fields
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
//end of each mysql row
//creates line to separate data from each MySQL table row
print "\n----------------------------------------------------\n\n";
} // end of while loop that prints out all the rows from the query
// now do the same for Excel
} else {
//create title with timestamp:
if ($use_title == 1)
{
echo("$title\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] == " ")
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != ""){
$row[$j] = preg_replace("/\t/", " ", $row[$j]);
$schema_insert .= "$row[$j]".$sep;
}
else
$schema_insert .= "".$sep;
} // end of for loop that prints out all fields
$schema_insert = str_replace($sep."$", "", $schema_insert);
//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
} // end of while loop that prints out all the rows from the query
} // end of if statement which decides whether to output to a word or excel file
?>
Nothing specific, but how are you planning to tell joe user in accounts from joe user in sales?
due to my restricted/none existant coding skills, by name solely. hence the reason for them entering their name exactly the same each time.Originally Posted by herulach
My point being, what if someone has the same name?Originally Posted by Vini
Just use your SQL query with my code as I stated earlier. It probably won't work straight away so you'll have to debug a bit, but the structure is there
PHP Code:define(db_host, "localhost");
define(db_user, "vini");
define(db_pass, "sss");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "schedule");
mysql_select_db(db_name);
$select = "SELECT * FROM schedule";
$result = mysql_query($select);
$use_title = 1 to generate title, 0 not to use title
$use_title = 1;
// define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
// define title for .doc or .xls file
$title = " Export Showing ".$_GET["title"]." on ".$now_date;
// if the word parameter is set then export to Word otherwise export to Excel
if ($_GET["word"]){
$file_type = "msword";
$file_ending = "doc";
} else {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/".$file_type);
header("Content-Disposition: attachment; filename=database_dump.".$file_ending);
header("Pragma: no-cache");
header("Expires: 0");
// format for Word
if (isset($_GET["word"]))
{
//create title with timestamp:
if ($use_title == 1)
{
echo("$title\n\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\n"; //new line character
while($row = mysql_fetch_row($result))
{
//set_time_limit(60);
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
//define field names
$field_name = mysql_field_name($result,$j);
//will show name of fields
$schema_insert .= "$field_name:\t";
if(!isset($row[$j]))
{
$schema_insert .= "NULL".$sep;
} elseif ($row[$j] == " ") {
$schema_insert .= "NULL".$sep;
} elseif ($row[$j] != "") {
$schema_insert .= str_replace($sep," ",$row[$j]).$sep;
} else {
$schema_insert .= "".$sep;
}
} // end of for loop that prints out all fields
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
//end of each mysql row
//creates line to separate data from each MySQL table row
print "\n----------------------------------------------------\n\n";
} // end of while loop that prints out all the rows from the query
// now do the same for Excel
} else {
//create title with timestamp:
if ($use_title == 1)
{
echo("$title\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] == " ")
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != ""){
$row[$j] = preg_replace("/\t/", " ", $row[$j]);
$schema_insert .= "$row[$j]".$sep;
}
else
$schema_insert .= "".$sep;
} // end of for loop that prints out all fields
$schema_insert = str_replace($sep."$", "", $schema_insert);
//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
} // end of while loop that prints out all the rows from the query
} // end of if statement which decides whether to output to a word or excel file
i completely understand, but we dont have anybody with that yet. and its over my head to code it any other way.Originally Posted by herulach
ive tried the above and am getting error codes on line 12. debugging again is over me, i could be stuck with the system as it is now
[e!] ive deleted the title bit and its worked
[e!] ive spotted the 'bug' in the title area, and debugged it, and it works. there was no // on --> //$use_title = 1 to generate title, 0 not to use title
PHP Code:<?php
define(db_host, "localhost");
define(db_user, "vini");
define(db_pass, "sdsdf");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "schedule");
mysql_select_db(db_name);
$select = "SELECT * FROM schedule";
$result = mysql_query($select);
//$use_title = 1 to generate title, 0 not to use title
$use_title = 1;
// define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
// define title for .doc or .xls file
$title = " Export Showing ".$_GET["title"]." on ".$now_date;
// if the word parameter is set then export to Word otherwise export to Excel
if ($_GET["word"]){
$file_type = "msword";
$file_ending = "doc";
} else {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/".$file_type);
header("Content-Disposition: attachment; filename=schedule.".$file_ending);
header("Pragma: no-cache");
header("Expires: 0");
// format for Word
if (isset($_GET["word"]))
{
//create title with timestamp:
if ($use_title == 1)
{
echo("$title\n\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\n"; //new line character
while($row = mysql_fetch_row($result))
{
//set_time_limit(60);
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
//define field names
$field_name = mysql_field_name($result,$j);
//will show name of fields
$schema_insert .= "$field_name:\t";
if(!isset($row[$j]))
{
$schema_insert .= "NULL".$sep;
} elseif ($row[$j] == " ") {
$schema_insert .= "NULL".$sep;
} elseif ($row[$j] != "") {
$schema_insert .= str_replace($sep," ",$row[$j]).$sep;
} else {
$schema_insert .= "".$sep;
}
} // end of for loop that prints out all fields
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
//end of each mysql row
//creates line to separate data from each MySQL table row
print "\n----------------------------------------------------\n\n";
} // end of while loop that prints out all the rows from the query
// now do the same for Excel
} else {
//create title with timestamp:
if ($use_title == 1)
{
echo("$title\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] == " ")
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != ""){
$row[$j] = preg_replace("/\t/", " ", $row[$j]);
$schema_insert .= "$row[$j]".$sep;
}
else
$schema_insert .= "".$sep;
} // end of for loop that prints out all fields
$schema_insert = str_replace($sep."$", "", $schema_insert);
//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
} // end of while loop that prints out all the rows from the query
} // end of if statement which decides whether to output to a word or excel file
?>
Last edited by Vini; 10-01-2006 at 05:09 PM.
are there any good tut's for customising the XLS output?
So does that mean it works now ?
Originally Posted by Iain
ooops, yeah its outpuuting to xls now, just need to work out how to customise the spreadsheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)