excel to php

techker · July 27, 2007

hey guys i have a book (php hack)

it shows how to gen a php page with xml spread sheet.

http://techker.prophp.org/test/

now is there a way to save that spread sheet in a database and fetch it aftherwords?

im new to database so please be gental..lol

here is the code
index page with upload:
PHP Code:
<html>
<body>
    <form enctype="multipart/form-data" action="import.php" method="post">
     Excel XML file:
        <input type="hidden" name="MAX_FILE_SIZE" value="2000000" />

        <input type="file" name="file" /><br/>
    <input type="submit" value="Upload" />
</form>
</body>
</html> 

import page (generater)
PHP Code:
<html>
<body>
<?php
$data = array();
if ( $_FILES['file']['tmp_name'] )
{
    
    $dom = DOMDocument::load( $_FILES['file']['tmp_name'] );
    $rows = $dom->getElementsByTagName( 'Row' );
    foreach ($rows as $row)
    {
        $cells = $row->getElementsByTagName( 'Cell' );
        $datarow = array();
        foreach ($cells as $cell)
        {
            $datarow []= $cell->nodeValue;
        }
        $data []= $datarow;
            
    }
}
?>
<table>
<?php foreach( $data as $row ) { ?>
<tr>
<?php foreach( $row as $item ) { ?>
<td><?php echo( $item ); ?></td>
<?php } ?>
</tr>
<?php } ?>
</table>
</body>
</html>

there is this to in the book but i have no clue how to use it.do i link it to the upload form?

generates sql from xml

<?php
$tables = array();
$indata = 0;

function encode( $text )

{ 
$text = preg_replace( "/'/", "''", $text ); 
return "'".$text."'";
}
function start_element( $parser, $name, $attribs )
{ 
  global $tables, $indata; 
  if ( $name == "WORKSHEET" ) 
  {
        $tables []= array( 
                'name' => $attribs['SS:NAME'],                
                'data' => array()
        );
    }
    if ( $name == "ROW" )
    {
            $tables[count($tables)-1]['data'] []= array();
    }
    if ( $name == "DATA" )
    {
        $indata = 1;
    } 
}
function text( $parser, $text ) 
{
global $tables, $indata;
if ( $indata )
{

        $data =& $tables[count($tables)-1]['data'];
        $data[count($data)-1] []= $text;
    }
}
function end_element( $parser, $name )
{
    global $indata;
    if ( $name == "DATA" )
    $indata = 0;
}
$parser = xml_parser_create( );
xml_set_element_handler( $parser, "start_element", "end_element" );
xml_set_character_data_handler( $parser, "text" );
while( !feof( STDIN ) ) {
  $text = fgets( STDIN );
  xml_parse( $parser, $text );
}
xml_parser_free( $parser );
foreach( $tables as $table ) {
    $name = $table['name'];
    $data =& $table['data'];
    $cols = implode( ", ", $data[0] );
    for( $in = 1; $in < count( $data ); $in++ ) {
        $sqldata = implode( ", ", array_map( "encode", $data[$in] ) );
?>
INSERT INTO <?php echo( $name )?> ( <?php echo( $cols ) ?> ) VALUES ( <?php echo(
$sqldata ); ?> );
<?php } } ?>

what is command-line version of PHP ?

they say to use it with this?

this is what it says

Use the command-line version of PHP to run the SQL generator script on the Excel XML data:

    % php gen.php < data.xml
    INSERT INTO Publisher ( ID, Name ) VALUES ( '0', 'O''Reilly' );
    INSERT INTO Publisher ( ID, Name ) VALUES ( '0', 'Manning' );
    INSERT INTO Publisher ( ID, Name ) VALUES ( '0', 'Wiley' );
    INSERT INTO Publisher ( ID, Name ) VALUES ( '0', 'Addison-Wesley' );
    INSERT INTO Publisher ( ID, Name ) VALUES ( '0', 'Pragmatic Press' );
    INSERT INTO Publisher ( ID, Name ) VALUES ( '0', 'APress' );



You can also pipe this right into a file, for example:

    % php gen.php < data.xml > publishers.sql

Sign In

excel to php

Recommended Posts

techker

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information