...

View Full Version : Help to make specific rows in other color in export to excel



El_Heso
09-18-2011, 12:24 AM
hi!
need help how to make the rows that are "new" ( 30 days back to present day a other color than the "old" rows )

here is my script:



<?php
include('config.php');
include('opendb.php');
$selected_radio = @$_POST['formradio1'] ;
switch ($selected_radio) {
case 1:
//$sql="SELECT * FROM foretag";
$sql="SELECT CONCAT(foretagsnamn) Företagsnamn, CONCAT(adress) Adress, CONCAT(postnr) Postnr, CONCAT(ort) Ort, CONCAT(telefon) Telefon, CONCAT(fornamn,' ',efternamn) Namn, CONCAT(email) Email, CONCAT(befattning) Befattning, CONCAT(orgnr) OrgNr, CONCAT(huvudinriktning)Huvudinriktning, CONCAT(huvudbransch) Huvudbransch, CONCAT(esn) ESN, CONCAT(branschkod) Branschkod, CONCAT(anstallda) Anställda, CONCAT(anstallda_text) AnställdaIntervall, CONCAT(omsattning_text) OmsättningIntervall, CONCAT(omsattning) Omsättning, CONCAT(kommun) Kommun, CONCAT(lan) Län, CONCAT(adressid) AdressID, CONCAT(land) Land, CONCAT(datum) Datum FROM foretag ORDER BY datum DESC";

//execute query
$result = @mysql_query($sql) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

//if this parameter is included ($w=1), file returned will be in word format ('.doc')
//if parameter is not included, file returned will be in excel format ('.xls')
if (isset($w) && ($w==1)) { $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=test.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");
/* Start of Formatting for Word or Excel */
if (isset($w) && ($w==1)) //check for $w again {
//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 .= "$row[$j]".$sep;
}
else {
$schema_insert .= "".$sep;
}
}
$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";
} }else{
//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
//start while loop to get data
while($row = mysql_fetch_row($result)) {
//set_time_limit(60);
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++) {
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$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"; } }
break; case 2:
include("Choice_2.php");
break;
case 3:
include("Choice_3.php");
break;
case 4:
include("Choice_4.php");
break;
case 5:
include("Choice_5.php");
break;
case 6:
include("Choice_6.php");
break;
}
?>

morongo47
09-18-2011, 03:02 AM
You could use gmdate(z) to return the sequential-numeric (1-365) day of the year, and save it to a field with your other row-data, but you'd have to deal with rollover when you began a new year.

Or, you could simply generate your own sequence of days starting with any arbitrary value ('1', possibly) and simply bump that value every new day. Anytime a db entry is made, you query the current sequential day and save it to a field. You just compare day-fields to decide color:

$rowcolor=($dayfield>=($thisday-30))?$color1:$color2; With this method, you'd never have an issue with year-rollovers, days in a month, etc. because you're just counting 24-hour periods.

Understand that with this method, you're just counting days, they continue indefinitely and don't stop at 365.
.

El_Heso
09-18-2011, 09:52 AM
Hi!
i´m novis at this so could you make a test code how to implement that $rowcolor in my own codes

for example i already have a date field but it´s like this 2011-09-09 11:08:00 is there a way to use that info for the task?

Regards Tony


You could use gmdate(z) to return the sequential-numeric (1-365) day of the year, and save it to a field with your other row-data, but you'd have to deal with rollover when you began a new year.

Or, you could simply generate your own sequence of days starting with any arbitrary value ('1', possibly) and simply bump that value every new day. Anytime a db entry is made, you query the current sequential day and save it to a field. You just compare day-fields to decide color:

$rowcolor=($dayfield>=($thisday-30))?$color1:$color2; With this method, you'd never have an issue with year-rollovers, days in a month, etc. because you're just counting 24-hour periods.

Understand that with this method, you're just counting days, they continue indefinitely and don't stop at 365.
.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum