PHP | XML to Html Table elment convertion

In this thread,
I will show generic & universal function that
gets XML string,
convert it,
and display it as an HTML table.
(you can add any css style to design your table later on of course)


The following code is using XML example from http://www.w3schools.com/xml/simple.xml.
The XML looks like that:

<breakfast_menu>
  <food>
    <name>Belgian Waffles</name>
    <price>$5.95</price>
    <description>Two of our famous...</description>
    <calories>650</calories>
  </food>
  <food>
    <name>Strawberry Belgian Waffles</name>
    <price>$7.95</price>
    <description>Light Belgian waffles...</description>
    <calories>900</calories>
  </food>
  <food>
    <name>Berry-Berry Belgian Waffles</name>
    <price>$8.95</price>
    <description>Light Belgian....</description>
    <calories>900</calories>
  </food>
  <food>
    <name>French Toast</name>
    <price>$4.50</price>
    <description>Thick slices made....</description>
    <calories>600</calories>
  </food>
  <food>
    <name>Homestyle Breakfast</name>
    <price>$6.95</price>
    <description>Two eggs, bacon...</description>
    <calories>950</calories>
  </food>
</breakfast_menu>

The function xmlToHtmlTable 

gets a SimpleXMLElement parameter which points to main parent for which will print all his children,
and returns html table representing the XML as string.

The Code:

 <?php  
   
 // get XML remotely / locally / or / just set it as string '<root>...</root>'  
 $sXml = file_get_contents('http://www.w3schools.com/xml/simple.xml');  
   
 // parse XML  
 $oXML = simplexml_load_string($sXml);  
 if (!$oXML) {  
      die('xml format not valid or simplexml module missing.');  
 }  
   
 // assuming running the root  
 $oXmlRoot = $oXML; // or can be [$oXML->food]  
   
 //echo '<pre>'; print_r( $oXmlRoot ); echo '</pre>';  
 echo xmlToHtmlTable($oXmlRoot);  
   
   
 function xmlToHtmlTable($p_oXmlRoot) {  
      $bIsHeaderProceessed = false;  
        
      $sTHead = '';  
      $sTBody = '';       
      foreach ($p_oXmlRoot as $oNode) {  
           $sTBody .= '<tr>';  
           foreach ($oNode as $sName => $oValue){  
                if (!$bIsHeaderProceessed) {  
                     $sTHead .= "<th>{$sName}</th>";  
                }  
                $sValue = (string)$oValue;  
                $sTBody .= "<td>{$sValue}</td>";                 
           }  
           $bIsHeaderProceessed = true;  
           $sTBody .= '</tr>';  
      }  
        
      $sHTML = "<table border=1>  
                     <thead><tr>{$sTHead}</tr></thead>  
                     <tbody>{$sTBody}</tbody>  
                </table>";  
      return $sHTML;  
 }  

This will output:

 

Hope it helps!

MDB BLOG!




 

C# | Asp.Net | Convert DataTable to Excel File

C# | Asp.Net | Convert DataTable to Excel File

Here is an example, to convert fro existing DataTable to Excel file

Working fine to me...
Tested on Excel 2003 and partially on Excel 2007 :)

///

/// Converts excel file sheet content into DataTable.
///

///
///
///
public static DataTable ExcelFileToDataTable(string p_fileUrl, int p_sheetIndex)
{
DataTable dbSchema = new DataTable();
DataSet dataSet = new DataSet();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p_fileUrl + ";" + "Extended Properties=Excel 8.0;Mode=Read;";

using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();

// Get all sheetnames from an excel file into data table
dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string sheetName = dbSchema.Rows[p_sheetIndex]["TABLE_NAME"].ToString();
string selectCommandText = string.Format("SELECT * FROM [{0}]", sheetName);

using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectCommandText, conn))
{
adapter.Fill(dataSet);
}

return dataSet.Tables[0];
}
}

///

/// Converts DataTable to Excel-File
///

///
///
///
///
public static bool DataTableToExcelFile(DataTable p_dt, string p_filePath, EventHandler p_onRowHandled)
{
try
{
// fix table name
if (p_dt.TableName == "" || p_dt.TableName.Equals("Table", StringComparison.OrdinalIgnoreCase) == true)
{
p_dt.TableName = "NONAME";
}

// fix file path name
if (p_filePath.EndsWith(".xls") == false)
{
p_filePath += ".xls";
}

// insure file not exist!
System.IO.FileInfo fi = new System.IO.FileInfo(p_filePath);
if (fi.Name.Replace(".xls", "").Trim().Length <= 0)
{
p_filePath = p_filePath.Replace(".xls", p_dt.TableName + ".xls");
}
if (System.IO.File.Exists(p_filePath))
{
System.IO.File.Delete(p_filePath);
}

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p_filePath + ";Extended Properties=Excel 8.0"))
{
using (OleDbCommand command = new OleDbCommand())
{
command.Connection = con;
con.Open();

// create table
command.CommandText = GenerateSqlStatementCreateTable(p_dt);
command.ExecuteNonQuery();

DataRow dr;
OleDbParameter oleDbParameter;

// Create columns & values (as parameters) script
string columns, parameters;
GenerateColumnsString(p_dt, out columns, out parameters);

for (int i = 0; i < p_dt.Rows.Count; i++)
{
dr = p_dt.Rows[i];

// set insert statement parameters
command.Parameters.Clear();
for (int j = 0; j < p_dt.Columns.Count; j++)
{
oleDbParameter = new OleDbParameter();
oleDbParameter.ParameterName = "@p" + j;

if (dr.IsNull(j) == true)
{
oleDbParameter.Value = DBNull.Value;
}
else
{
oleDbParameter.Value = dr[j];
}
command.Parameters.Add(oleDbParameter);
}

command.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", p_dt.TableName, columns, parameters);
command.ExecuteNonQuery();

// Perform step...
if (p_onRowHandled != null)
{
p_onRowHandled(p_dt, EventArgs.Empty);
}
}
}

con.Close();
}
return true;
}
catch (Exception ex)
{
throw (ex);
}
}


///

/// Generates columns names string delimited by commas
/// also supply parameters for the given columns, for example:
///
/// out string p_columns = [columnname0],[columnname1],[columnname2]
/// out string p_params = @p0, @p1, @p2
///
///

///
///
///
private static void GenerateColumnsString(DataTable p_dt, out string p_columns, out string p_params)
{
StringBuilder sbColumns = new StringBuilder();
StringBuilder sbParams = new StringBuilder();
for (int i = 0; i < p_dt.Columns.Count; i++)
{
if (i != 0)
{
sbColumns.Append(',');
sbParams.Append(',');
}
sbColumns.AppendFormat("[{0}]", p_dt.Columns[i].ColumnName);
sbParams.AppendFormat("@p{0}", i);
}

p_columns = sbColumns.ToString();
p_params = sbParams.ToString();
}

///

/// Create SQL-Script for creating table which represent the given DataTable
///

///
///
private static string GenerateSqlStatementCreateTable(DataTable p_dt)
{
StringBuilder sbCreateTable = new StringBuilder();

DataColumn dc;
sbCreateTable.AppendFormat("CREATE TABLE {0} (", p_dt.TableName);
for (int i = 0; i < p_dt.Columns.Count; i++)
{
dc = p_dt.Columns[i];
if (i != 0)
{
sbCreateTable.Append(",");
}

string dataType = dc.DataType.Equals(typeof(double)) ? "DOUBLE" : "NVARCHAR";

sbCreateTable.AppendFormat("[{0}] {1}", dc.ColumnName, dataType);
}
sbCreateTable.Append(")");

return sbCreateTable.ToString();
}