Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Sep 2011
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help to make specific rows in other color in export to excel

    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 Code:
    <?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;
     } 
     
    ?>
    Last edited by El_Heso; 09-18-2011 at 12:33 AM.

  • #2
    New Coder
    Join Date
    Sep 2011
    Posts
    43
    Thanks
    6
    Thanked 6 Times in 6 Posts
    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:
    Code:
    $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.
    .
    Last edited by morongo47; 09-18-2011 at 03:24 AM.

  • #3
    New Coder
    Join Date
    Sep 2011
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    Quote Originally Posted by morongo47 View Post
    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:
    Code:
    $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.
    .
    Last edited by El_Heso; 09-18-2011 at 09:55 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •