Table data parsing and saving the table data in excel sheet using php

Question

I have a file "import.php" in which html data is written under table tags. Now i want to parse that data and save that data in an excel sheet. Format is undermentioned and first tr contains the heading and then the data

<html>
  <body>
  <table>
  <tr>
  <th>Name</th>
  <th>Email</th>
  <th>Addr</th>
  <th>City</th>
  </tr>

  <tr>
  <td>Jack</td>
  <td>[email protected]</td>
  <td>xyz Road</td>
  <td>LOS ANGELES</td>
  </tr>

  <tr>
  <td>Sam</td>
  <td>[email protected]</td>
  <td>pr Road</td>
  <td>TUSTIN</td>
  </tr>
    </table>
  </body>
  </html>

Where are you having problems? With parsing the data? or with saving it as Excel? — Mark Baker
– Mark Baker, Commented Apr 17, 2012 at 6:43
Have you already parsed the HTML? what's the product of parsing? do you have an array? JSON object? Do you know how excel is structured in general? these two links will help you: bit.ly/2Ltpr , bit.ly/a8lS74 — Adi
– Adi, Commented Apr 17, 2012 at 7:19
Actually i ned to know how to parse that html data to create and excel doc — swapnesh
– swapnesh, Commented Apr 17, 2012 at 7:39
@swapnesh - For parsing the HTML, look at DOM... I do have a basic HTML to PHPExcel parser, but it isn't yet production ready — Mark Baker
– Mark Baker, Commented Apr 17, 2012 at 8:04

dimmat · Accepted Answer · 2012-04-17 10:56:32Z

Maybe you've better look at this: http://phpexcel.codeplex.com/

and this: http://www.easyxls.com/

Another trick is to save your data as a CSV file: http://www.homeandlearn.co.uk/php/php10p6.html

UPDATE:

There is no simpler way for saving data into an Excel file directly but saving as CSV. Try this code:

<?php 
$data = array( array("firstname" => "Mary", "lastname" => "Johnson", "age" => 25), 
    array("firstname" => "Amanda", "lastname" => "Miller", "age" => 18), 
    array("firstname" => "James", "lastname" => "Brown", "age" => 31), 
    array("firstname" => "Patricia", "lastname" => "Williams", "age" => 7), 
    array("firstname" => "Michael", "lastname" => "Davis", "age" => 43), 
    array("firstname" => "Sarah", "lastname" => "Miller", "age" => 24), 
    array("firstname" => "Patrick", "lastname" => "Miller", "age" => 27) ); 

    # filename for download 
    $filename = "website_data_" . date('Ymd') . ".xls"; 
    header("Content-Disposition: attachment; filename=\"$filename\""); 
    header("Content-Type: application/vnd.ms-excel");
    $flag = false; 
    foreach($data as $row) 
    { 
        if(!$flag) 
        { # display field/column names as first row echo 
            implode("\t", array_keys($row)) . "\r\n"; $flag = true; 
        } 
        array_walk($row, 'cleanData');
        print implode("\t", array_values($row)) . "\r\n"; 
    }


    function cleanData(&$str) 
    { 
        $str = preg_replace("/\t/", "\\t", $str); 
        $str = preg_replace("/\r?\n/", "\\n", $str); 
        if(strstr($str, '"')) 
            $str = '"' . str_replace('"', '""', $str) . '"';
    }
?>

As for the seperation of the values from the html tags, you could read your html code from import.php, remove all unnecessary tags, put a seperator to the end of each or tag and then put your values into a single dimension array:

<?php 
$htmldata = "";
$htmldata .= "<html>";
$htmldata .= "<body>";
$htmldata .= "<tr>";
$htmldata .= "<th>header1</th>";
$htmldata .= "<th>header2</th>";
$htmldata .= "<th>header3</th>";
$htmldata .= "<th>header4</th>";
$htmldata .= "</tr>";
$htmldata .= "<tr>";
$htmldata .= "<td>data1</td>";
$htmldata .= "<td>data2</td>";
$htmldata .= "<td>data3</td>";
$htmldata .= "<td>data4</td>";
$htmldata .= "</tr>";
$htmldata .= "</body>";
$htmldata .= "</html>";

//Remove the unecessary tags like <html>, </html>, <body>, </body>, <th>, </th>, <td>, </td>
$searchfor = array("<html>", "</html>", "<body>", "</body>", "<tr>", "</tr>", "<th>", "</th>", "<td>", "</td>");
$replacewith = array("", "", "", "", "", "", "", "**SEPERATOR**", "", "**SEPERATOR**"); // Replace </th> & </td> with **SEPERATOR** text
$htmldata = str_replace($searchfor, $replacewith, $htmldata);

$values = explode("**SEPERATOR**", $htmldata); ;
print_r($values);

?>

The first 4 values of the array $values contain your header values. Hope that it helps...

I dont want ot use heavy phpExcel classes..i need some script if u have any

Collectives™ on Stack Overflow

Table data parsing and saving the table data in excel sheet using php

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related