Problems exporting data to Excel

Codewalkers Forums Sponsor:

April 2nd, 2009, 11:20 AM

jej1216

Contributing User

Join Date: Apr 2007

Posts: 2

Time spent in forums: 28 m 3 sec
Reputation Power: 0

MySQL 5 - Problems exporting data to Excel

I have a simple PHP file that extracts data from a MySQL Database table and outputs it to an Excel Spreadsheet. I have Excel 2007, but it is an issue with 2003 as well.
There are 10 php pages with the same code, and the only difference is a qualifier in the select statement to select specific data rows.
For 6 months, all has worked with no issues. Yesterday, 2 of the 10 php pages now bring an error "Problems on Load - problems came up in the following areas during load: Table. This file cannot be opened because of errors. Errors are listed in C:\Documents and Settings\me\Local Settings\Temporary Internet Files\Content.MSO\F1C9FC58.log."
The log shows this:
XML PARSE ERROR: Null or missing attribute value
Error occurs at or below this element stack:
<ss:Workbook>
<ss:Worksheet>
<ss:Table>
<ss:Row>
<ss:Cell>
<ss

ata>

I think this has to be data related, since other php pages with the exact same logic work and even the 2 php pages that bring this error worked up until yesterday. I do not believe this is a php issue because of this. The error is definitely an MS Office error.
Am I right in that this is being caused by the data or should I be looking at something else?
Here is my PHP Code:

PHP Code:


		
			
<?php

// include the php-excel class

require (dirname (__FILE__) . "/class-excel-xml.inc.php");

$connection = mysql_connect("server", "login", "password");

mysql_select_db("dbname", $connection);

$query = "SELECT * FROM incidents WHERE fac_id = 'WXYZ'";

$result = mysql_query($query) or die($query . '<br>' . mysql_error());

$myheaderarray = array();

$myheaderarray [] = array('STAMP','incident_id','fac_id','room_descr',  'oth_descr','person_type','medrec_num','acct_num',  'person_name','person_lname','person_street','pers  on_city','person_state','person_zip','person_phone  ','dob','inc_date','inc_time','fall_type','assiste  d','observe','fall_type2','assisted2','observe2','  treat_var','med_var','pt_outcome','pt_outcome_oth'  ,'ni_type','pt_rights','near_miss','equip','enviro  n','desc_oth','inc_descr','injury','inj_descr','re  l_factor_fall','rel_factor_fall2','rel_factor_fall  3','rel_factor_fall4','rel_factor_meds','rel_facto  r_oth','severity','not_type','note_oth','not_time'  ,'not_type2','note2_oth','not_time2','not_type3','  note3_oth','not_time3','not_type4','note4_oth','no  t_time4','witness1_type','witness1_name','witness1  _street','witness1_city','witness1_st','witness1_z  ip','witness1_phone','witness2_type','witness2_nam  e','witness2_street','witness2_city','witness2_st'  ,'witness2_zip','witness2_phone','reporting_name',  'reporting_id','reporting_date','assess_ortho','re  score_morse','det_contrib','id_rec_medment','req_p  harm_review','rev_curr_int','id_add_int','addr_bwl  blddr','assure_safety_equip','id_env_issues','id_l  earn_pref','comm_team','comm_fam','notify_attendin  g','comm_name','actions_descr','followup_descr','r  m_severity','notified_ceo','not_ceo_datetime','not  ified_ho','not_ho_datetime','notified_plant','not_  plant_datetime','notified_food','not_food_datetime  ','notified_attending','not_sattending_datetime','  notified_ins','not_ins_datetime','notified_pharm',  'not_pharm_datetime','notified_dno','not_dno_datet  ime','notified_dto','not_dto_datetime','notified_o  th','not_oth_datetime','not_oth_descr','root_cause  _an','rca_date','rm_name','rm_date','Done');

$myarray = array();

while($row = mysql_fetch_array($result)) {

$myarray[] = array($row['STAMP'],

$row['incident_id'],    $row['fac_id'],      $row['room_descr'],    $row['oth_descr'],

$row['person_type'],    $row['medrec_num'],  $row['acct_num'],      $row['person_name'],

$row['person_lname'],   $row['person_street'],   $row['person_city'],   $row['person_state'],

$row['person_zip'],     $row['person_phone'],    $row['dob'],           $row['inc_date'],

$row['inc_time'],       $row['fall_type'],       $row['assisted'],      $row['observe'],

$row['fall_type2'],     $row['assisted2'],       $row['observe2'],      $row['treat_var'],

$row['med_var'],        $row['pt_outcome'],      $row['pt_outcome_oth'],   $row['ni_type'],

$row['pt_rights'],      $row['near_miss'],       $row['equip'],         $row['environ'],

$row['desc_oth'],       $row['inc_descr'],       $row['injury'],        $row['inj_descr'],

$row['rel_factor_fall'],$row['rel_factor_fall2'],$row['rel_factor_fall3'],$row['rel_factor_fall4'],

$row['rel_factor_meds'],    $row['rel_factor_oth'],   $row['severity'],   $row['not_type'],

$row['note_oth'],       $row['not_time'],       $row['not_type2'],      $row['note2_oth'],

$row['not_time2'],      $row['not_type3'],      $row['note3_oth'],      $row['not_time3'],

$row['not_type4'],      $row['note4_oth'],      $row['not_time4'],      $row['witness1_type'],

$row['witness1_name'],  $row['witness1_street'],  $row['witness1_city'],   $row['witness1_st'],

$row['witness1_zip'],  $row['witness1_phone'],   $row['witness2_type'],  $row['witness2_name'],

$row['witness2_street'], $row['witness2_city'],    $row['witness2_st'],   $row['witness2_zip'],

$row['witness2_phone'], $row['reporting_name'],   $row['reporting_id'],  $row['reporting_date'],

$row['assess_ortho'],  $row['rescore_morse'],    $row['det_contrib'],   $row['id_rec_medment'],

$row['req_pharm_review'], $row['rev_curr_int'],   $row['id_add_int'],    $row['addr_bwlblddr'],

$row['assure_safety_equip'],$row['id_env_issues'],  $row['id_learn_pref'],   $row['comm_team'],

$row['comm_fam'],       $row['notify_attending'], $row['comm_name'],    $row['actions_descr'],

$row['followup_descr'], $row['rm_severity'],   $row['notified_ceo'],   row['not_ceo_datetime'],

$row['notified_ho'], $row['not_ho_datetime'],$row['notified_plant'],$row['not_plant_datetime'],

$row['notified_food'],$row['not_food_datetime'],$row['notified_attending'],$row['not_sattending_datetime'],

$row['notified_ins'], $row['not_ins_datetime'], $row['notified_pharm'], $row['not_pharm_datetime'],

$row['notified_dno'],$row['not_dno_datetime'],$row['notified_dto'],$row['not_dto_datetime'],

$row['notified_oth'], $row['not_oth_datetime'], $row['not_oth_descr'],   $row['root_cause_an'],

$row['rca_date'], row['rm_name'],          $row['rm_date'],           $row['Done']);

}

// generate excel file

$xls = new Excel_XML;

$xls->addArray ( $myheaderarray );

$xls->addArray ( $myarray );

$xls->generateXML ( "incidents_WXYZ" );

?>

April 2nd, 2009, 01:31 PM

IAmALlama

Join Date: Apr 2007

Location: San Diego, CA

Posts: 2,120

Time spent in forums: 1 Week 6 Days 20 h 31 m 26 sec
Reputation Power: 6

try removing the [] from these parts:

PHP Code:


		
			
 $myheaderarray [] = array('STAMP','incident_id','fac_id',.....etc



//and this:

$myarray[] = array($row['STAMP'], ....etc

when you put [] after an array variable, it adds a new key into the array and whatever is after the = is added into that spot. you are basically making a multi-dimensional array where the first dimension is [0] and under that is the array with all the values you want. example:

PHP Code:


		
			
<pre>

<?php

$array = array();



//this will set $array[0] to 'test'

$array[] = "test";

print_r($array);



//resetting the array to blank

$array = array();



//this will set $array[0][0]  to 'test' making it multi-dimensional (an array within an array).

$array[] = array("test");

print_r($array);



//resetting the array to blank.

$array = array();



//this will set $array to an array with all the array values.

//this is what you want to do.

$array = array("test", "test2", "test3");

?>

</pre>

Try running that if you need to see what I mean.

April 3rd, 2009, 09:33 AM

jej1216

Contributing User

Join Date: Apr 2007

Posts: 2

Time spent in forums: 28 m 3 sec
Reputation Power: 0

I forgot a major part of my code

Thanks, IAmALlama.

The real frustration I have is that my code works perfectly for 8 out of the 10 php pages using it. But...........

When I tried to remove the two sets of brackets, it errored, and then I realized I left out an important piece of my issue (sorry - brain-fart on my part).

I am using an include file from Oliver Schwarz that I had found earlier in a user group site.

PHP Code:


		
			
require (dirname (__FILE__) . "/class-excel-xml.inc.php");

The class-excel-xml.inc.php code:

PHP Code:


		
			
<?php class Excel_XML

{



    private $header = "<?xml version=\"1.0\" encoding=\"UTF-8\"?\>

<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"

 xmlns:x=\"urn:schemas-microsoft-com:office:excel\"

 xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"

 xmlns:html(blocked)\">"; * I had to remove this to post it.



    private $footer = "</Workbook>";



    private $lines = array ();



    private $worksheet_title = "IncidentReports";



    private function addRow ($array)

    {



        // initialize all cells for this row

        $cells = "";



        // foreach key -> write value into cells

        foreach ($array as $k => $v):



            $cells .= "<Cell><Data ss:Type=\"String\">" . utf8_encode($v) . "</Data></Cell>\n";



        endforeach;



        // transform $cells content into one row

        $this->lines[] = "<Row>\n" . $cells . "</Row>\n";



    }



    public function addArray ($array)

    {



        // run through the array and add them into rows

        foreach ($array as $k => $v):

            $this->addRow ($v);

        endforeach;



    }



    public function setWorksheetTitle ($title)

    {



        // strip out special chars first

        $title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);



        // now cut it to the allowed length

        $title = substr ($title, 0, 31);



        // set title

        $this->worksheet_title = $title;



    }



    function generateXML ($filename)

    {



        // deliver header (as recommended in php manual)

        header("Content-Type: application/vnd.ms-excel; charset=UTF-8");

        header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");



        // print out document to the browser

        // need to use stripslashes for the damn ">"

        echo stripslashes ($this->header);

        echo "\n<Worksheet ss:Name=\"" . $this->worksheet_title . "\">\n<Table>\n";

        echo "<Column ss:Index=\"1\" ss:AutoFitWidth=\"0\" ss:Width=\"110\"/>\n";

        echo implode ("\n", $this->lines);

        echo "</Table>\n</Worksheet>\n";

        echo $this->footer;



    }



}



?>

Viewing: Codewalkers Forums > Other Technologies > Database Help > MySQL 5 - Problems exporting data to Excel

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Create the Optimal Architecture for your Critical Applications
Warburton's the largest independently owned bakery in the UK faced a number of difficult challenges in providing the most robust yet efficient IT infrastructure for their organization's success. IBM's services combined with their xSeries servers created the perfect platform for their SAP environment with sufficient flexibility, and did so in very time effective fashion.
Request Your Free Technology Downloads!

Five Best Practices for Deploying a Successful Service-Oriented Architecture
This white paper describes the benefits you can expect with SOA, and how IBM can help take your business there.
Request Your Free Technology Downloads!

Gartner Magic Quadrant for Application Delivery Controllers
Gartner summarizes its view on Application Delivery Controllers, evaluates strengths and weaknesses of solutions, and provides Magic Quadrant reporting for a quick comparison across all vendors. Learn from Gartner how you can benefit from an all-in-one device like Citrix NetScaler that delivers the highest levels of availability, performance and security.
Request Your Free Technology Downloads!

Knowledge is Power
What you don't know can hurt you, and is likely costing you money and increasing your security risks during an era of scarce resources. This white paper proposes six key strategies that enterprise security managers can use to improve their network defense posture.
Request Your Free Technology Downloads!

Rationalizing the Multi-Tool Environment
The rationalized multi-tool approach is flexible, scalable and cost effective. It provides the necessary input to the IT service management business processes. It preserves prior investments in monitoring tools, empowers technologists to select the best tools with which to do their jobs, and enhances effective response to incidents.
Request Your Free Technology Downloads!

More Free IT White Papers!