Here's how to create Excel from and HTML Table

Topics: Developer Forum, User Forum
Oct 13, 2011 at 8:51 PM
Edited Oct 18, 2011 at 8:09 PM

I saw some topics in the forums from folks wanting to create Excel files from HTML tables that they've already generated.

I offer this code as a way of doing that.  I use it exclusively on my site and it works well.  I have the advantage of pretty much using

the same HTML to generate tables, so my HTML table inputs are predictable to a degree.  Anyway, hope someone finds this useful

or as a starting point for morphing into a HTML reader class.

<?php
session_start();
ini_set("memory_limit", "-1");
ini_set("set_time_limit", "0");
set_time_limit(0);
if (isset($_SESSION['zprofile'])) {
  $username = $_SESSION['zprofile']['username'];            // user's name
  $usermail = $_SESSION['zprofile']['useremail'];         // user's emailid
  $usercompany = $_SESSION['zprofile']['usercompany'];    // user's company
}else{
  header('Location: index.php?e=0');
}
if(!isset($_GET['var'])) {
  echo "<br />No Table Variable Present, nothing to Export.";
  exit;
}else{
  $tablevar = $_GET['var'];
}
if(!isset($_GET['limit'])) {
  $limit = 12;
}else{
  $limit = $_GET['limit'];
}
if(!isset($_GET['debug'])) {
  $debug = false;
}else{
  $debug = true;
  $handle = fopen("Auditlog/exportlog.txt", "w");
  fwrite($handle, "\nDebugging On...");
}
if(!isset($_SESSION[$tablevar]) OR $_SESSION[$tablevar] == '') {
  echo "<br />Empty HTML Table, nothing to Export.";
  exit;
}else{
  $htmltable = $_SESSION[$tablevar];
}
if(strlen($htmltable) == strlen(strip_tags($htmltable)) ) {
  echo "<br />Invalid HTML Table after Stripping Tags, nothing to Export.";
  exit;
}
$htmltable = strip_tags($htmltable, "<table><tr><th><thead><tbody><tfoot><td><br><br /><b><span>");
$htmltable = str_replace("<br />", "\n", $htmltable);
$htmltable = str_replace("<br/>", "\n", $htmltable);
$htmltable = str_replace("<br>", "\n", $htmltable);
$htmltable = str_replace("&nbsp;", " ", $htmltable);
$htmltable = str_replace("\n\n", "\n", $htmltable);
//
//  Extract HTML table contents to array
//
$dom = new domDocument;
$dom->loadHTML($htmltable);
if(!$dom) {
  echo "<br />Invalid HTML DOM, nothing to Export.";
  exit;
}
$dom->preserveWhiteSpace = false;             // remove redundant whitespace
$tables = $dom->getElementsByTagName('table');
if(!is_object($tables)) {
  echo "<br />Invalid HTML Table DOM, nothing to Export.";
  exit;
}
if($debug) {
  fwrite($handle, "\nTable Count: ".$tables->length);
}
$tbcnt = $tables->length - 1;                 // count minus 1 for 0 indexed loop over tables
if($tbcnt > $limit) {
  $tbcnt = $limit;
}
//
//
// Create new PHPExcel object with default attributes
//
require_once ('PHPExcel/PHPExcel.php');
$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
$tm = date(YmdHis);
$pos = strpos($usermail, "@");
$user = substr($usermail, 0, $pos);
$user = str_replace(".","",$user);
$tfn = $user."_".$tm."_".$tablevar.".xlsx";
//$fname = "AuditLog/".$tfn;
$fname = $tfn;
$objPHPExcel->getProperties()->setCreator($username)
							 ->setLastModifiedBy($username)
							 ->setTitle("Automated Export")
							 ->setSubject("Automated Report Generation")
							 ->setDescription("Automated report generation.")
							 ->setKeywords("Exported File")
               ->setCompany($usercompany)
							 ->setCategory("Export");
//
// Loop over tables in DOM to create an array, each table becomes a worksheet
//
for($z=0;$z<=$tbcnt;$z++) {
  $maxcols = 0;
  $totrows = 0;
  $headrows = array();
  $bodyrows = array();
  $r = 0;
  $h = 0;
  $rows = $tables->item($z)->getElementsByTagName('tr');
  $totrows = $rows->length;
  if($debug) {
    fwrite($handle, "\nTotal Rows: ".$totrows);
  }
  foreach ($rows as $row) {
      $ths = $row->getElementsByTagName('th');
      if(is_object($ths)) {
        if($ths->length > 0) {
          $headrows[$h]['colcnt'] = $ths->length;
          if($ths->length > $maxcols) {
            $maxcols = $ths->length;
          }
          $nodes = $ths->length - 1;
          for($x=0;$x<=$nodes;$x++) {
            $thishdg = $ths->item($x)->nodeValue;
            $headrows[$h]['th'][] = $thishdg;
            $headrows[$h]['bold'][] = findBoldText(innerHTML($ths->item($x)));
            if($ths->item($x)->hasAttribute('style')) {
              $style = $ths->item($x)->getAttribute('style');
              $stylecolor = findStyleColor($style);
              if($stylecolor == '') {
                $headrows[$h]['color'][] = findSpanColor(innerHTML($ths->item($x)));
              }else{
                $headrows[$h]['color'][] = $stylecolor;
              }
            }else{
              $headrows[$h]['color'][] = findSpanColor(innerHTML($ths->item($x)));
            }
            if($ths->item($x)->hasAttribute('colspan')) {
              $headrows[$h]['colspan'][] = $ths->item($x)->getAttribute('colspan');
            }else{
              $headrows[$h]['colspan'][] = 1;
            }
            if($ths->item($x)->hasAttribute('align')) {
              $headrows[$h]['align'][] = $ths->item($x)->getAttribute('align');
            }else{
              $headrows[$h]['align'][] = 'left';
            }
            if($ths->item($x)->hasAttribute('valign')) {
              $headrows[$h]['valign'][] = $ths->item($x)->getAttribute('valign');
            }else{
              $headrows[$h]['valign'][] = 'top';
            }
            if($ths->item($x)->hasAttribute('bgcolor')) {
              $headrows[$h]['bgcolor'][] = str_replace("#", "", $ths->item($x)->getAttribute('bgcolor'));
            }else{
              $headrows[$h]['bgcolor'][] = 'FFFFFF';
            }
          }
          $h++;
        }
      }
  }
  foreach ($rows as $row) {
      $tds = $row->getElementsByTagName('td');
      if(is_object($tds)) {
        if($tds->length > 0) {
          $bodyrows[$r]['colcnt'] = $tds->length;
          if($tds->length > $maxcols) {
            $maxcols = $tds->length;
          }
          $nodes = $tds->length - 1;
          for($x=0;$x<=$nodes;$x++) {
            $thistxt = $tds->item($x)->nodeValue;
            $bodyrows[$r]['td'][] = $thistxt;
            $bodyrows[$r]['bold'][] = findBoldText(innerHTML($tds->item($x)));
            if($tds->item($x)->hasAttribute('style')) {
              $style = $tds->item($x)->getAttribute('style');
              $stylecolor = findStyleColor($style);
              if($stylecolor == '') {
                $bodyrows[$r]['color'][] = findSpanColor(innerHTML($tds->item($x)));
              }else{
                $bodyrows[$r]['color'][] = $stylecolor;
              }
            }else{
              $bodyrows[$r]['color'][] = findSpanColor(innerHTML($tds->item($x)));
            }
            if($tds->item($x)->hasAttribute('colspan')) {
              $bodyrows[$r]['colspan'][] = $tds->item($x)->getAttribute('colspan');
            }else{
              $bodyrows[$r]['colspan'][] = 1;
            }
            if($tds->item($x)->hasAttribute('align')) {
              $bodyrows[$r]['align'][] = $tds->item($x)->getAttribute('align');
            }else{
              $bodyrows[$r]['align'][] = 'left';
            }
            if($tds->item($x)->hasAttribute('valign')) {
              $bodyrows[$r]['valign'][] = $tds->item($x)->getAttribute('valign');
            }else{
              $bodyrows[$r]['valign'][] = 'top';
            }
            if($tds->item($x)->hasAttribute('bgcolor')) {
              $bodyrows[$r]['bgcolor'][] = str_replace("#", "", $tds->item($x)->getAttribute('bgcolor'));
            }else{
              $bodyrows[$r]['bgcolor'][] = 'FFFFFF';
            }
          }
          $r++;
        }
      }
  }
  if($z > 0) {
    $objPHPExcel->createSheet($z);
  }
  $suf = $z + 1;
  $tableid = $tablevar.$suf;
  $wksheetname = ucfirst($tableid);
  $objPHPExcel->setActiveSheetIndex($z);                      // each sheet corresponds to a table in html
  $objPHPExcel->getActiveSheet()->setTitle($wksheetname);     // tab name
  $worksheet = $objPHPExcel->getActiveSheet();                // set worksheet we're working on
  $style_overlay = array('font' =>
                    array('color' =>
                      array('rgb' => '000000'),'bold' => false,),
                          'fill' 	=>
                              array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'CCCCFF')),
                          'alignment' =>
                              array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                         'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP),
                          'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                                             'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                                             'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                                             'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),
                       );
  $xcol = '';
  $xrow = 1;
  $usedhdrows = 0;
  $heightvars = array(1=>'42', 2=>'42', 3=>'48', 4=>'52', 5=>'58', 6=>'64', 7=>'68', 8=>'76', 9=>'82');
  for($h=0;$h<count($headrows);$h++) {
    $th = $headrows[$h]['th'];
    $colspans = $headrows[$h]['colspan'];
    $aligns = $headrows[$h]['align'];
    $valigns = $headrows[$h]['valign'];
    $bgcolors = $headrows[$h]['bgcolor'];
    $colcnt = $headrows[$h]['colcnt'];
    $colors = $headrows[$h]['color'];
    $bolds = $headrows[$h]['bold'];
    $usedhdrows++;
    $mergedcells = false;
    for($t=0;$t<count($th);$t++) {
      if($xcol == '') {$xcol = 'A';}else{$xcol++;}
      $thishdg = $th[$t];
      $thisalign = $aligns[$t];
      $thisvalign = $valigns[$t];
      $thiscolspan = $colspans[$t];
      $thiscolor = $colors[$t];
      $thisbg = $bgcolors[$t];
      $thisbold = $bolds[$t];
      $strbold = ($thisbold==true) ? 'true' : 'false';
      if($thisbg == 'FFFFFF') {
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE;
      }else{
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
      }
      $style_overlay['alignment']['vertical'] = $thisvalign;              // set styles for cell
      $style_overlay['alignment']['horizontal'] = $thisalign;
      $style_overlay['font']['color']['rgb'] = $thiscolor;
      $style_overlay['font']['bold'] = $thisbold;
      $style_overlay['fill']['color']['rgb'] = $thisbg;
      $worksheet->setCellValue($xcol.$xrow, $thishdg);
      $worksheet->getStyle($xcol.$xrow)->applyFromArray($style_overlay);
      if($debug) {
        fwrite($handle, "\n".$xcol.":".$xrow." ColSpan:".$thiscolspan." Color:".$thiscolor." Align:".$thisalign." VAlign:".$thisvalign." BGColor:".$thisbg." Bold:".$strbold." cellValue: ".$thishdg);
      }
      if($thiscolspan > 1) {                                                // spans more than 1 column
        $mergedcells = true;
        $lastxcol = $xcol;
        for($j=1;$j<$thiscolspan;$j++) {
          $lastxcol++;
          $worksheet->setCellValue($lastxcol.$xrow, '');
          $worksheet->getStyle($lastxcol.$xrow)->applyFromArray($style_overlay);
        }
        $cellRange = $xcol.$xrow.':'.$lastxcol.$xrow;
        if($debug) {
          fwrite($handle, "\nmergeCells: ".$xcol.":".$xrow." ".$lastxcol.":".$xrow);
        }
        $worksheet->mergeCells($cellRange);
        $worksheet->getStyle($cellRange)->applyFromArray($style_overlay);
        $num_newlines = substr_count($thishdg, "\n");                       // count number of newline chars
        if($num_newlines > 1) {
          $rowheight = $heightvars[1];                                      // default to 35
          if(array_key_exists($num_newlines, $heightvars)) {
            $rowheight = $heightvars[$num_newlines];
          }else{
            $rowheight = 75;
          }
          $worksheet->getRowDimension($xrow)->setRowHeight($rowheight);     // adjust heading row height
        }
        $xcol = $lastxcol;
      }
    }
    $xrow++;
    $xcol = '';
  }
  //Put an auto filter on last row of heading only if last row was not merged
  if(!$mergedcells) {
    $worksheet->setAutoFilter("A$usedhdrows:" . $worksheet->getHighestColumn() . $worksheet->getHighestRow() );
  }
  if($debug) {
    fwrite($handle, "\nautoFilter: A".$usedhdrows.":".$worksheet->getHighestColumn().$worksheet->getHighestRow());
  }
  // Freeze heading lines starting after heading lines
  $usedhdrows++;
  $worksheet->freezePane("A$usedhdrows");
  if($debug) {
    fwrite($handle, "\nfreezePane: A".$usedhdrows);
  }
  //
  // Loop thru data rows and write them out
  //
  $xcol = '';
  $xrow = $usedhdrows;
  for($b=0;$b<count($bodyrows);$b++) {
    $td = $bodyrows[$b]['td'];
    $colcnt = $bodyrows[$b]['colcnt'];
    $colspans = $bodyrows[$b]['colspan'];
    $aligns = $bodyrows[$b]['align'];
    $valigns = $bodyrows[$b]['valign'];
    $bgcolors = $bodyrows[$b]['bgcolor'];
    $colors = $bodyrows[$b]['color'];
    $bolds = $bodyrows[$b]['bold'];
    for($t=0;$t<count($td);$t++) {
      if($xcol == '') {$xcol = 'A';}else{$xcol++;}
      $thistext = $td[$t];
      $thisalign = $aligns[$t];
      $thisvalign = $valigns[$t];
      $thiscolspan = $colspans[$t];
      $thiscolor = $colors[$t];
      $thisbg = $bgcolors[$t];
      $thisbold = $bolds[$t];
      $strbold = ($thisbold==true) ? 'true' : 'false';
      if($thisbg == 'FFFFFF') {
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE;
      }else{
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
      }
      $style_overlay['alignment']['vertical'] = $thisvalign;              // set styles for cell
      $style_overlay['alignment']['horizontal'] = $thisalign;
      $style_overlay['font']['color']['rgb'] = $thiscolor;
      $style_overlay['font']['bold'] = $thisbold;
      $style_overlay['fill']['color']['rgb'] = $thisbg;
      if($thiscolspan == 1) {
        $worksheet->getColumnDimension($xcol)->setWidth(25);
      }
      $worksheet->setCellValue($xcol.$xrow, $thistext);
      if($debug) {
        fwrite($handle, "\n".$xcol.":".$xrow." ColSpan:".$thiscolspan." Color:".$thiscolor." Align:".$thisalign." VAlign:".$thisvalign." BGColor:".$thisbg." Bold:".$strbold." cellValue: ".$thistext);
      }
      $worksheet->getStyle($xcol.$xrow)->applyFromArray($style_overlay);
      if($thiscolspan > 1) {                                                // spans more than 1 column
        $lastxcol = $xcol;
        for($j=1;$j<$thiscolspan;$j++) {
          $lastxcol++;
        }
        $cellRange = $xcol.$xrow.':'.$lastxcol.$xrow;
        if($debug) {
          fwrite($handle, "\nmergeCells: ".$xcol.":".$xrow." ".$lastxcol.":".$xrow);
        }
        $worksheet->mergeCells($cellRange);
        $worksheet->getStyle($cellRange)->applyFromArray($style_overlay);
        $xcol = $lastxcol;
      }
    }
    $xrow++;
    $xcol = '';
  }
  // autosize columns to fit data
  $azcol = 'A';
  for($x=1;$x==$maxcols;$x++) {
    $worksheet->getColumnDimension($azcol)->setAutoSize(true);
    $azcol++;
  }
  if($debug) {
    fwrite($handle, "\nHEADROWS: ".print_r($headrows, true));
    fwrite($handle, "\nBODYROWS: ".print_r($bodyrows, true));
  }
} // end for over tables
$objPHPExcel->setActiveSheetIndex(0);                      // set to first worksheet before close
//
// Write to Browser
//
if($debug) {
  fclose($handle);
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=$fname");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//$objWriter->save($fname);
$objWriter->save('php://output');
exit;
function innerHTML($node) {
  $doc = $node->ownerDocument;
  $frag = $doc->createDocumentFragment();
  foreach ($node->childNodes as $child) {
    $frag->appendChild($child->cloneNode(TRUE));
  }
  return $doc->saveXML($frag);
}
function findSpanColor($node) {
  $pos = stripos($node, "color:");       // ie: looking for style='color: #FF0000;'
  if ($pos === false) {                  //                        12345678911111
    return '000000';                     //                                 01234
  }
  $node = substr($node, $pos);           // truncate to color: start
  $start = "#";                          // looking for html color string
  $end = ";";                            // should end with semicolon
  $node = " ".$node;                     // prefix node with blank
	$ini = stripos($node,$start);          // look for #
	if ($ini === false) return "000000";   // not found, return default color of black
	$ini += strlen($start);                // get 1 byte past start string
	$len = stripos($node,$end,$ini) - $ini; // grab substr between start and end positions
	return substr($node,$ini,$len);        // return the RGB color without # sign
}
function findStyleColor($style) {
  $pos = stripos($style, "color:");      // ie: looking for style='color: #FF0000;'
  if ($pos === false) {                  //                        12345678911111
    return '';                           //                                 01234
  }
  $style = substr($style, $pos);           // truncate to color: start
  $start = "#";                          // looking for html color string
  $end = ";";                            // should end with semicolon
  $style = " ".$style;                     // prefix node with blank
	$ini = stripos($style,$start);          // look for #
	if ($ini === false) return "";         // not found, return default color of black
	$ini += strlen($start);                // get 1 byte past start string
	$len = stripos($style,$end,$ini) - $ini; // grab substr between start and end positions
	return substr($style,$ini,$len);        // return the RGB color without # sign
}
function findBoldText($node) {
  $pos = stripos($node, "<b>");          // ie: looking for bolded text
  if ($pos === false) {                  //                        12345678911111
    return false;                        //                                 01234
  }
  return true;                           // found <b>
}
?>
Nov 8, 2011 at 9:33 AM

Hi mamuscia.

This code is what I'm looking for but this is way to complicated for me.

I know you are praboblly busy person but can you actualy tel us how to us this script?

All my tables are generated dynamically in php how can I add link to every table like -> [Save as Excel File]?

Do I have to save file as html on my HDD ? Or there is another way? 

 

Ps. Thank you for sharing!

Nov 8, 2011 at 4:12 PM

Hi Foxmark,

The method I use to pass the HTML table to this PHP script is by using $_SESSION variables to save the generated HTML into.

The steps are as follows:

  1. generate the HTML for the table
  2. put the generated HTML into a $_SESSION variable, as well as displaying the HTML table on the page
  3. generate links at the top of the page that reference the PHP script to export the table to Excel
  4. The PHP script to export the table gets the $_SESSION variable name, then gets the contents of the variable from the $_SESSION variable
  5. The DOM is built from the variable contents and executes like the sample I gave above to use the PHPExcel class 

I've attached a snippet to show you how I do this from the calling script:

session_start();
//
// get data for table
$data = getData(); // any function you have to get your data
// generate HTML table using data obtained
$html = genHTML($data);  // any function you have to generate the HTML table
//
// generate links to be displayed at top of table
$chtml = "<br /><div style='height:26px;width:50%;border: solid gray 1px;background:#F5F5F5;padding-top:4px;'>\n";
$chtml .= "<a href='javascript:void(0)' onclick='window.print();' title='Print this page'><img src='images/printer.png' alt='Print this page' hspace='3' /></a>\n";
$chtml .=  "<a href='generalexport_v2.php?var=tablecontents' target='_blank' title='Export table to Excel'><img src='images/excel.gif' alt='Export table to Excel' hspace='3' /></a>\n";
$chtml .=  "<a href='emailfile.php?var=tablecontents' target='_blank' title='eMail Excel file to me'><img src='images/email.png' alt='eMail Excel file to me' hspace='3' /></a>\n";
$chtml .= "</div><br />\n";
$_SESSION['tablecontents'] = $html;
echo $chtml;
echo $html;
exit;
Notice how the generalexport.php file uses a parameter of VAR=
This will be the session variable name where you stored the generated HTML table.
It should be a unique name so that other pages that use this same method generate their own unique session variable contents. 
Hope this helps explain how to call and use this generic export function.

 

 

Nov 8, 2011 at 4:39 PM
Edited Apr 2, 2013 at 2:59 PM
Here's an updated copy of the generalexport.php script. ~<?php session_start(); ini_set("memory_limit", "-1"); ini_set("set_time_limit", "0"); set_time_limit(0); if (isset($_SESSION['zprofile'])) { $username = $_SESSION['zprofile']['username']; // user's name $usermail = $_SESSION['zprofile']['useremail']; // user's emailid $usercompany = $_SESSION['zprofile']['usercompany']; // user's company }else{ header('Location: index.php?e=0'); } if(!isset($_GET['var'])) { echo "<br />No Table Variable Present, nothing to Export."; exit; }else{ $tablevar = $_GET['var']; } if(!isset($_GET['limit'])) { $limit = 12; // maximum number of Excel tabs to create, optional }else{ $limit = $_GET['limit']; } if(!isset($_GET['debug'])) { // optional, debug script by writing out to log file $debug = false; }else{ $debug = true; $handle = fopen("Auditlog/exportdebug_log.txt", "w"); fwrite($handle, "\nDebugging On..."); } if(!isset($_SESSION[$tablevar]) OR $_SESSION[$tablevar] == '') { echo "<br />Empty HTML Table, nothing to Export."; exit; }else{ $htmltable = $_SESSION[$tablevar]; } if(strlen($htmltable) == strlen(strip_tags($htmltable)) ) { // anything left after we strip HTML? echo "<br />Invalid HTML Table after Stripping Tags, nothing to Export."; exit; } if($debug) { fwrite($handle, "\n-------------------------------------------"); fwrite($handle, "\nHTML before prep: \n".$htmltable); fwrite($handle, "\n-------------------------------------------"); } $htmltable = strip_tags($htmltable, "<table><tr><th><thead><tbody><tfoot><td><br><b><span>"); $htmltable = str_replace("<br />", "\n", $htmltable); $htmltable = str_replace("<br/>", "\n", $htmltable); $htmltable = str_replace("<br>", "\n", $htmltable); $htmltable = str_replace("&nbsp;", " ", $htmltable); $htmltable = str_replace("\n\n", "\n", $htmltable); if($debug) { fwrite($handle, "\n-------------------------------------------"); fwrite($handle, "\nHTML after prep: \n".$htmltable); fwrite($handle, "\n-------------------------------------------"); } // // Create Document Object Model from HTML table contents // $dom = new domDocument; $dom->loadHTML($htmltable); if(!$dom) { echo "<br />Invalid HTML DOM, nothing to Export."; exit; } $dom->preserveWhiteSpace = false; // remove redundant whitespace $tables = $dom->getElementsByTagName('table'); if(!is_object($tables)) { echo "<br />Invalid HTML Table DOM, nothing to Export."; exit; } if($debug) { fwrite($handle, "\nTable Count: ".$tables->length); } if($tables->length < 1) { echo "<br />DOM Table Count is ".$tables->length.", nothing to Export."; exit; } $tbcnt = $tables->length - 1; // count minus 1 for 0 indexed loop over tables if($tbcnt > $limit) { $tbcnt = $limit; } // // // Create new PHPExcel object with default attributes // require_once ('PHPExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(9); $tm = date(YmdHis); $pos = strpos($usermail, "@"); $user = substr($usermail, 0, $pos); $user = str_replace(".","",$user); $tfn = $user."".$tm."".$tablevar.".xlsx"; //$fname = "AuditLog/".$tfn; $fname = $tfn; $objPHPExcel->getProperties()->setCreator($username) ->setLastModifiedBy($username) ->setTitle("Automated Export") ->setSubject("Automated Report Generation") ->setDescription("Automated Report generation.") ->setKeywords("Exported File") ->setCompany($usercompany) ->setCategory("Export"); // // Loop over tables in DOM to create an array, each table becomes a worksheet // for($z=0;$z<=$tbcnt;$z++) { $maxcols = 0; $totrows = 0; $headrows = array(); $bodyrows = array(); $r = 0; $h = 0; $rows = $tables->item($z)->getElementsByTagName('tr'); $totrows = $rows->length; if($debug) { fwrite($handle, "\nTotal Rows: ".$totrows); } // // Get TH values // foreach ($rows as $row) { $ths = $row->getElementsByTagName('th'); if(is_object($ths)) { if($ths->length > 0) { $headrows[$h]['colcnt'] = $ths->length; if($ths->length > $maxcols) { $maxcols = $ths->length; } $nodes = $ths->length - 1; for($x=0;$x<=$nodes;$x++) { $thishdg = $ths->item($x)->nodeValue; $headrows[$h]['th'][] = $thishdg; $headrows[$h]['bold'][] = findBoldText(innerHTML($ths->item($x))); if($ths->item($x)->hasAttribute('style')) { $style = $ths->item($x)->getAttribute('style'); $stylecolor = findStyleColor($style); if($stylecolor == '') { $headrows[$h]['color'][] = findSpanColor(innerHTML($ths->item($x))); }else{ $headrows[$h]['color'][] = $stylecolor; } }else{ $headrows[$h]['color'][] = findSpanColor(innerHTML($ths->item($x))); } if($ths->item($x)->hasAttribute('colspan')) { $headrows[$h]['colspan'][] = $ths->item($x)->getAttribute('colspan'); }else{ $headrows[$h]['colspan'][] = 1; } if($ths->item($x)->hasAttribute('align')) { $headrows[$h]['align'][] = $ths->item($x)->getAttribute('align'); }else{ $headrows[$h]['align'][] = 'left'; } if($ths->item($x)->hasAttribute('valign')) { $headrows[$h]['valign'][] = $ths->item($x)->getAttribute('valign'); }else{ $headrows[$h]['valign'][] = 'top'; } if($ths->item($x)->hasAttribute('bgcolor')) { $headrows[$h]['bgcolor'][] = str_replace("#", "", $ths->item($x)->getAttribute('bgcolor')); }else{ $headrows[$h]['bgcolor'][] = 'FFFFFF'; } } $h++; } } } // // Get TD values // foreach ($rows as $row) { $tds = $row->getElementsByTagName('td'); if(is_object($tds)) { if($tds->length > 0) { $bodyrows[$r]['colcnt'] = $tds->length; if($tds->length > $maxcols) { $maxcols = $tds->length; } $nodes = $tds->length - 1; for($x=0;$x<=$nodes;$x++) { $thistxt = $tds->item($x)->nodeValue; $bodyrows[$r]['td'][] = $thistxt; $bodyrows[$r]['bold'][] = findBoldText(innerHTML($tds->item($x))); if($tds->item($x)->hasAttribute('style')) { $style = $tds->item($x)->getAttribute('style'); $stylecolor = findStyleColor($style); if($stylecolor == '') { $bodyrows[$r]['color'][] = findSpanColor(innerHTML($tds->item($x))); }else{ $bodyrows[$r]['color'][] = $stylecolor; } }else{ $bodyrows[$r]['color'][] = findSpanColor(innerHTML($tds->item($x))); } if($tds->item($x)->hasAttribute('colspan')) { $bodyrows[$r]['colspan'][] = $tds->item($x)->getAttribute('colspan'); }else{ $bodyrows[$r]['colspan'][] = 1; } if($tds->item($x)->hasAttribute('align')) { $bodyrows[$r]['align'][] = $tds->item($x)->getAttribute('align'); }else{ $bodyrows[$r]['align'][] = 'left'; } if($tds->item($x)->hasAttribute('valign')) { $bodyrows[$r]['valign'][] = $tds->item($x)->getAttribute('valign'); }else{ $bodyrows[$r]['valign'][] = 'top'; } if($tds->item($x)->hasAttribute('bgcolor')) { $bodyrows[$r]['bgcolor'][] = str_replace("#", "", $tds->item($x)->getAttribute('bgcolor')); }else{ $bodyrows[$r]['bgcolor'][] = 'FFFFFF'; } } $r++; } } } if($z > 0) { $objPHPExcel->createSheet($z); } $suf = $z + 1; $tableid = $tablevar.$suf; $wksheetname = ucfirst($tableid); $objPHPExcel->setActiveSheetIndex($z); // each sheet corresponds to a table in html $objPHPExcel->getActiveSheet()->setTitle($wksheetname); // tab name $worksheet = $objPHPExcel->getActiveSheet(); // set worksheet we're working on $style_overlay = array('font' => array('color' => array('rgb' => '000000'),'bold' => false,), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'CCCCFF')), 'alignment' => array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), ); $xcol = ''; $xrow = 1; $usedhdrows = 0; $heightvars = array(1=>'42', 2=>'42', 3=>'48', 4=>'52', 5=>'58', 6=>'64', 7=>'68', 8=>'76', 9=>'82'); for($h=0;$h<count($headrows);$h++) { $th = $headrows[$h]['th']; $colspans = $headrows[$h]['colspan']; $aligns = $headrows[$h]['align']; $valigns = $headrows[$h]['valign']; $bgcolors = $headrows[$h]['bgcolor']; $colcnt = $headrows[$h]['colcnt']; $colors = $headrows[$h]['color']; $bolds = $headrows[$h]['bold']; $usedhdrows++; $mergedcells = false; for($t=0;$t<count($th);$t++) { if($xcol == '') {$xcol = 'A';}else{$xcol++;} $thishdg = $th[$t]; $thisalign = $aligns[$t]; $thisvalign = $valigns[$t]; $thiscolspan = $colspans[$t]; $thiscolor = $colors[$t]; $thisbg = $bgcolors[$t]; $thisbold = $bolds[$t]; $strbold = ($thisbold==true) ? 'true' : 'false'; if($thisbg == 'FFFFFF') { $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE; }else{ $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID; } $style_overlay['alignment']['vertical'] = $thisvalign; // set styles for cell $style_overlay['alignment']['horizontal'] = $thisalign; $style_overlay['font']['color']['rgb'] = $thiscolor; $style_overlay['font']['bold'] = $thisbold; $style_overlay['fill']['color']['rgb'] = $thisbg; if($thiscolspan > 1) { // spans more than 1 column $mergedcells = true; $lastxcol = $xcol; for($j=1;$j<$thiscolspan;$j++) { // count to last column in span $lastxcol++; } $cellRange = $xcol.$xrow.':'.$lastxcol.$xrow; if($debug) { fwrite($handle, "\nmergeCells: ".$xcol.":".$xrow." ".$lastxcol.":".$xrow); } $worksheet->mergeCells($cellRange); // merge the columns $worksheet->setCellValue($xcol.$xrow, $thishdg); $worksheet->getStyle($cellRange)->applyFromArray($style_overlay); $worksheet->getStyle($cellRange)->getAlignment()->setWrapText(tru
```
Nov 8, 2011 at 5:14 PM

Also, worht noting is that the HTML I generate for tables is fairly consistent and I use this general format.  You can put STYLE='COLOR: xxxxxxx' anywhere, as well as <BR />'s which will be

turned into newlines chars.

<table>
  <thead>
  <!-- Heading row(s) -->
  <tr>
    <th colspan='4' align='center' valign='top'><b>Some Heading Text</b></th>
  </tr>
  <tr>
    <th align='center' valign='top'>Heading 1</th>
    <th align='center' valign='top'>Heading 2</th>
    <th align='center' valign='top'>Heading 3</th>
    <th align='center' valign='top'>Heading 4</th>
  </tr>
  </thead>
  <!-- Data row(s) -->
  <tbody>
  <tr>
    <td align='left' valign='top'>data...</td>
    <td align='left' valign='top'>data...</td>
    <td align='left' valign='top'>data...</td>
    <td align='left' valign='top'>data...</td>
  </tr>
  <!-- more Data row(s), as needed ... -->
  </tbody>
</table>
Nov 9, 2011 at 8:39 AM

Hi mamuscia,

Thnak you for all your work!

I'm going to work on this beauty today, we will see how good am I :)

Best Regards,

Nov 30, 2011 at 12:11 PM

 

Hi mamuscia,

It’s been a while since my last post,

But this morning I returned to excel project and I have to say - great job!!!

I managed to generate about 5000 lines of data and save it to excel in a snap.

All I have to do now is change the way my functions return tables but this is easy.

One more time thx for your help and support.

Thx,

Paul

Nov 30, 2011 at 12:11 PM
Edited Nov 30, 2011 at 12:17 PM

 Ps. All I done was removing this condition:

if (isset($_SESSION['zprofile'])) {
  $username = $_SESSION['zprofile']['username'];            // user's name
  $usermail = $_SESSION['zprofile']['useremail'];         // user's emailid
  $usercompany = $_SESSION['zprofile']['usercompany'];    // user's company
}else{
  header('Location: index.php?e=0');
}

Nov 30, 2011 at 1:53 PM

Foxmark,

Glad you found this to be useful. 

That section you removed checking for the $_SESSION['zprofile'] is ok to do.  Just make sure you start a session from both pages so you can store your generated HTML table

into a session variable.

Nov 30, 2011 at 2:07 PM
Edited Nov 30, 2011 at 2:14 PM

I start looking at your code and I found this

$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
Can you tell me please how can I change font size for my TH tag?
I managed to change bgcolor but I dont know how to start with font size and decoration.
  // Get TH values
  //
  foreach ($rows as $row) {
      $ths = $row->getElementsByTagName('th');
      if(is_object($ths)) {
        if($ths->length > 0) {
          $headrows[$h]['colcnt'] = $ths->length;
          if($ths->length > $maxcols) {
            $maxcols = $ths->length;
          }
          $nodes = $ths->length - 1;
          for($x=0;$x<=$nodes;$x++) {
            $thishdg = $ths->item($x)->nodeValue;
            $headrows[$h]['th'][] = $thishdg;
            $headrows[$h]['bold'][] = findBoldText(innerHTML($ths->item($x)));
            if($ths->item($x)->hasAttribute('style')) {
              $style = $ths->item($x)->getAttribute('style');
              $stylecolor = findStyleColor($style);
              if($stylecolor == '') {
                $headrows[$h]['color'][] = findSpanColor(innerHTML($ths->item($x)));
              }else{
                $headrows[$h]['color'][] = $stylecolor;
              }
            }else{
              $headrows[$h]['color'][] = findSpanColor(innerHTML($ths->item($x)));
            }
            if($ths->item($x)->hasAttribute('colspan')) {
              $headrows[$h]['colspan'][] = $ths->item($x)->getAttribute('colspan');
            }else{
              $headrows[$h]['colspan'][] = 1;
            }
            if($ths->item($x)->hasAttribute('align')) {
              $headrows[$h]['align'][] = $ths->item($x)->getAttribute('align');
            }else{
              $headrows[$h]['align'][] = 'left';
            }
            if($ths->item($x)->hasAttribute('valign')) {
              $headrows[$h]['valign'][] = $ths->item($x)->getAttribute('valign');
            }else{
              $headrows[$h]['valign'][] = 'top';
            }
            if($ths->item($x)->hasAttribute('bgcolor')) {
              $headrows[$h]['bgcolor'][] = str_replace("#", "", $ths->item($x)->getAttribute('bgcolor'));
            }else{
              $headrows[$h]['bgcolor'][] = 'cccccc'; // my new color
            }
          }
          $h++;
        }
      }
  }
  //
Im using css to style all my tables so php output is raw html table with no colors, font size or any formating.
Thank you  and have a nice day!
Nov 30, 2011 at 10:02 PM

I did not write this to go after all those styling attributes from css.  Also, if you use classes the styling will not be picked up.

You'd have to write your own parsing function like I did to look for specific items. 

Dec 1, 2011 at 4:01 PM

Hello,

I am beginner in php and html.

Your above code is what i am searching to do. In fact, I display informations from an excel file in a html table and i add for each row a checkbox.

The purpose of the checkbox is to give the user the ability to select which lines hes is interested from  the global information (excel file)  to finaly create another excel file containing just the rows selected.

So the purpose of my code is :  To create Excel files from HTML tables that they've already generated.

1. I wrote the php code that read an excel file using the PHPExcel package and the html code that generate the page with the table with all informations in the excel file, below is the code :

<html>
<head>
    <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
    <title>Tableau de voisngae 2G 2G</title>
</head>
<body>

<?php
$File_name = "2G2G12.xls";
require_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load($File_name);
?>

<table id="tableau" border="1" align="center">
        <thead>
          <tr  BGCOLOR=FF6600>
              <th><font color=black>Nom_S</font></th>
              <th><font color=black>LAC_S</font></th>
              <th><font color=black>CI_S</font></th>
              <th><font color=black>Nom_V</font></th>
              <th><font color=black>LAC_V</font></th>
              <th><font color=black>CI_V</font></th>
              <th><font color=black>Distance(Km)</th>
              <th><font color=black>Plaque</font></th>
              <th>
                  <button type="button">
                  <font color=black>Ajouter</font>
                  </button>

              </th>
          </tr>
         </thead>

<tbody>
<?php
 foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
  $highestRow         = $worksheet->getHighestRow();
  $highestColumn      = $worksheet->getHighestColumn();
  $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
?>
                          <?php
for ($row = 2; $row <= $highestRow; ++ $row) {
                          ?>
                             <tr>
                             <?php
                             for ($col = 0; $col < $highestColumnIndex; ++ $col) {
                               $cell = $worksheet->getCellByColumnAndRow($col, $row);

                               $val = $cell->getValue();

                               $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);

                             if ( $val == "A" ) {
                             ?>
                               <td align="center"><input name="Ajouter" align="center" align="center" type="checkbox" value="0" id="<?php echo $row; ?>" checked></input></td>

                             <?php } else { ?>

                                     <td><?php echo $val; ?></td>

                             <?php }
                             }
                             ?>
                             </tr>
                           <?php
                           }
                           ?>

<?php
}
?>
</tbody>
<SCRIPT language="Javascript">
  function sauverResultatEpreuve()
  {
      var arrayLignes = document.getElementById("tableau").rows;
      var longueur = arrayLignes.length;
      for(var iter=1; iter < longueur; ++iter)
      {
        alert(iter);
        var arrayColonnes = arrayLignes[1].cells;
        alert((arrayColonnes[0].innerHTML));
        var largeur = arrayColonnes.length;
        var valeur =  arrayColonnes[8].innerHTML;
        alert(valeur);
        var input = document.getElementById(iter + 1);
        var id_cb = input.id;
        alert(id_cb);
        if ((document.getElementById(id_cb).checked) == false)
        {
           document.getElementById('tableau').deleteRow(arrayLignes[1].rowIndex);
        }
        else
        {
                    go();
       }
    }
  }
function getXhr(){
      var xhr = null;
      if(window.XMLHttpRequest) // Firefox et autres
           xhr = new XMLHttpRequest();
      else if(window.ActiveXObject){ // Internet Explorer
                                   try {
                                        xhr = new ActiveXObject("Msxml2.XMLHTTP");
                                    } catch (e) {
                                        xhr = new ActiveXObject("Microsoft.XMLHTTP");
                                    }
                                }
                                else { // XMLHttpRequest non supportéar le navigateur
                                   alert("Votre navigateur ne supporte pas les objets XMLHTTPRequest...");
                                   xhr = false;
                                }
                                return xhr
                        }

                        /**
                        * Méode qui sera appelésur le click du bouton
                        */

                        function go(){
                                var xhr = getXhr()
                                // On déni ce qu'on va faire quand on aura la rénse
                                xhr.onreadystatechange = function(){
                                        // On ne fait quelque chose que si on a tout reçet que le serveur est ok
                                        if(xhr.readyState == 4 && xhr.status == 200){
                                                alert(xhr.responseText);
                                        }
                                }
                                xhr.open("GET","ajax.php",true);
                                xhr.send(null);
                        }

</SCRIPT>
</table>
<button type="button" onclick="JavaScript:sauverResultatEpreuve()">
           <font color=black>Ajouter</font>
</button>

<script language="javascript"></script>

</body>
</html>
But i have always the problem how to create the excel file from the html table generated from the code above,and i found your code that i thing it will do what i really want :)
The problem that i don't have any idea about how to create the session variable and how to get the html table from the code above and how to call your code (generalexport.php) :((.
Hope that you will be able to help me to acheive my goal.
Thank you in advance
Kass34



Dec 1, 2011 at 4:10 PM
Edited Dec 1, 2011 at 4:17 PM

Hi Kass34

I started from smth easy llike:

<?php
session_start();
ini_set('display_errors',1); 
error_reporting(E_ALL);
function printTable() {
$part = "<table>
<thead>
<tr>
<th colspan=\"4\" align=\"center\" valign=\"top\"><b>Some Heading Text</b></th>
</tr>
<tr>
<th align=\"center\" valign=\"top\">Heading 1</th>
<th align=\"center\" valign=\"top\">Heading 2</th>
<th align=\"center\" valign=\"top\">Heading 3</th>
<th align=\"center\" valign=\"top\">Heading 4</th>
</tr>
</thead>
<tbody>";
$z = 101;
$x = 201;
$y = 301;
for ($i = 1; $i <= 50; $i++) {
$part .="<tr>
<td align=\"left\" valign=\"top\">$i</td>
<td align=\"left\" valign=\"top\">$z</td>
<td align=\"left\" valign=\"top\">$x</td>
<td align=\"left\" valign=\"top\">$y</td>
</tr>";
$z++;
$x++;
$y++;
}
$part .= "</tbody></table>";
return $part;
}
$chtml = "<br /><div style='height:26px;width:100px;border: solid gray 1px;background:#F5F5F5;padding-top:4px;'>\n";
$chtml .=  "<a href='generalexport.php?var=tablecontents' title='Export table to Excel'><img src='images/excel.png' alt='Export table to Excel' hspace='3' /></a>\n";
$chtml .= "</div><br />\n";
echo $chtml;
$table = printTable();
$_SESSION['tablecontents'] = $table;
echo $table;
?>
For session variable try:
<?php
session_start();
$_SESSION['test'] = "My Test";
echo $_SESSION['test'];
?>
And PS. make sure you name your file 'generalexport.php' !!
Dec 1, 2011 at 4:38 PM

Kass34 -

If you utilize the method described here to export to Excel, then the entire table will be exported, not just selected rows.  If you only want to export

check rows, you will have to modify generalexport.php to search through the check boxes to determine which rows to include/exclude from the export.

generalexport was meant to be lightweight and a simple table to excel option for export/download and doesn't check every iteration of HTML or CSS

 

The example Foxmark shows above is a good illustration of how to use the generalexport method

Dec 2, 2011 at 9:42 AM

Hello,

Thanks for your response.

I have problem with functions  "findBoldText, findSpanColor,....."

in fact there is errors and it doesn't work and when i comment them there is no problem.

Any idea please ?

the html table that i created is look like :

<table id="tableau" border="1" align="center">
        <thead>
          <tr  BGCOLOR=FF6600>
              <th><font color=black>Nom_S</font></th>
              <th><font color=black>LAC_S</font></th>
              <th><font color=black>CI_S</font></th>
              <th><font color=black>Nom_V</font></th>
              <th><font color=black>LAC_V</font></th>
              <th><font color=black>CI_V</font></th>
              <th><font color=black>Distance(Km)</th>
              <th><font color=black>Plaque</font></th>
              <th><font color=black>Ajouter</font></th>
          </tr>
         </thead>
     <tbody>
      <tr>
       <td align="center"><input name="Ajouter" align="center" align="center" type="checkbox" value="0" checked id=$row ></input></td>
      </tr>
     <!--more data rows as the excel file contains lines.. >>>

</tbody>
</table>

Does i have to get TH values for creating the excel file ?

Thnaks for your help

 

Dec 2, 2011 at 10:01 AM

Hello,

I am very sorry i didn't see that these functions are declared in the end.

I will try again, and sorry again.

 

Dec 2, 2011 at 12:58 PM

Hello,

I tested to print  $_SESSION['tablecontents''] and i get  the content of my table=> the table is not empty but when i run with generalexport it generate an empty Excel file   :( :( :( .

Also the two instructions below are not working :

$objWriter->save($tfn);

$objWriter->save('php://output');

ofcourse i used one of them.

I commented them in order to generate the excel file ( the result was empty excel file) if  they are not commented the programm did not run i have an error look like that the web site could not open the page.

Any idea please and thanks in advance for your help.

BR,

Kass34

 

Dec 2, 2011 at 5:58 PM

Hello,

I have another question please :

In my table for each row the last colome is a checkbox. The code of the generaleexport return a NULL value for the check box ? why ?

From the code of the generalexport how can i get the checkbox colom and how can i verify if it is checked or  no ?

I know it is not the purpose of the generalexport but i hope that you could help in this issue.

Thanks in advance

 

Dec 4, 2011 at 11:13 PM

Kass34,
generalexport will only export the original version of your table since you saved the HTML in the session variable right after you first generated it. 

The script does not maintain the session variable contents, it only uses the value. 

If you want to do other things to support dynamically updating the saved HTML as something changes in the table,  you'd need to write a combination of javascript and PHP script to accomplish that. 

 

I'm embedding a sample where I did that by using javascript and PHP.  Within the generated table, I embedded DIVs with ID's.  Then, I use SIMPLE_HTML_DOM to update values as they change in the table so

when I export it, the latest HTML values are available in the session variable. 

<?php
session_start();
error_reporting(E_ALL);
//
// Replace HTML ID element value
//
// ID passed to this script is the ID of the HTML element so that innerHTML can be changed to the passed value
// VALUE passed to this script is the new value to replace the existing value within the HTML element
//
if(isset($_GET['id'])) {
  $id = $_GET['id'];
}else{
  echo "Missing ID parameter!";      // can't do anything without ID
  exit;
}
if(isset($_GET['value'])) {
  $value = $_GET['value'];           // ascii string with \n for newline breaks
}else{
  echo "Missing Value parameter!";   // can't do anything without a value
  exit;
}
//
// Using Simple HTML DOM, update the HTML element in the session table, then save the session table
//
include('includes/simple_html_dom.php');                 // find this at  http://simplehtmldom.sourceforge.net/
$html = str_get_html($_SESSION['mysessionvarname']);     // get the table contents from the session variable
$valueexcel = str_ireplace("\n", "<br>", $value);        // replace line breaks \n to HTML <BR>
$e = $html->find("div[id=$id]");                         // find the HTML element
foreach($e as $single_e){
  if($single_e->id == $id) {                             // if we find our ID
    $single_e->innertext = $valueexcel;                  // replace the innerText (innerHTML) content with new value
    $_SESSION['mysessionvarname'] = "";                  // clear the session table value
    $_SESSION['mysessionvarname'] = $html->save();       // now save updated HTML into the session table value
  }
}
$html->clear();                                          // Close and release resources
unset($html);
// you could also update databases or other sources if needed while you have this data, or simply exit
?>
The functions within generalexport only look for limited HTML or CSS attributes.  You can write your own functions to look 
for other attributes.  For example, I see you use   <th><font color=black>Nom_S</font></th> you could write your own function to look for FONT attributes.    
Dec 5, 2011 at 8:56 AM
Mamuscia,
Thanks for this claridifcation.
The big issue for me now is why the generalexportt.php generate an empty excel file and the two functions are not  working :

$objWriter->save($tfn);

$objWriter->save('php://output');

I commented them in order to generate the excel file ( the result was empty excel file) if  they are not commented the programm did not run i have an error look like that the web site could not open the page.

P.S: i tried to generate excel file with the the original version of  my table (i saved the HTML in the session variable right after  i generate it). 

Any idea please and thanks in advance for your help

Kass34

Dec 5, 2011 at 2:58 PM

Kass34,

First of all, you should only be using one or the other of the save methods.  Also note to use the lastest version of the generalexport script I posted above. It uses $fname, not $tfn to save

the file to disk.  Send me your table and let me test in my browser.

 

Dec 5, 2011 at 5:52 PM

 

Mamuscia,

 

In fact the input is an excel file (see table in the end). From the excel file i generate my html table and i displayed it. The table is 9 coloms the las colum is a checkbox.

My goal:

By default all rows are checked. The user can choose to deselect a row.

when the user click on the "Submit" we call the generalexport.php as you mentioned above. In addition, i tried to get just the rows with the checkbox is selected ( when we create the arrays i will not add all rows with deselected rows) and then to export an excel file about the lef rows (that mean to export an excel file with the selected rows). I DONT WANT TO CHANGE VALUES.

Problems:

1. I couldn't run the generalexport.php, i got errors when arriving to save method. In fact i tried the both way and i have the same thing. And when i comment the save command i got an empty excel file ???

2. In the piece of program where i get the TD values, i tried to check if the checkbox is checked or not ? i succeed to get the attribute name , the id value but i didn't know how to check the status of the checkbox?

Below you found the :

1. piece of code that generate the HTML table

2. piece of code (genrateexport.php) with some modifications

3. an exemple of the contents of the input excel file

1>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

<html>
<head>
    <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
    <title>Tableau de voisngae 2G 2G</title>
</head>
<body>

<?php
session_start();
ini_set('display_errors',1); 
error_reporting(E_ALL);

require_once 'PHPExcel/IOFactory.php';

function printTable() {
    $File_name = "2G2G12.xls";
    
    $objPHPExcel = PHPExcel_IOFactory::load($File_name);

    $part = '<table id="tableau" border="1" align="center">
        <thead>
          <tr  BGCOLOR=FF6600>
              <th><font color=black>Nom_S</font></th>
              <th><font color=black>LAC_S</font></th>
              <th><font color=black>CI_S</font></th>
              <th><font color=black>Nom_V</font></th>
              <th><font color=black>LAC_V</font></th>
              <th><font color=black>CI_V</font></th>
              <th><font color=black>Distance(Km)</th>
              <th><font color=black>Plaque</font></th>
              <th><font color=black>Ajouter</font></th>
          </tr>
         </thead>

         <tbody>';
      foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
               $highestRow         = $worksheet->getHighestRow();
               $highestColumn      = $worksheet->getHighestColumn();
               $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
               
               for ($row = 2; $row <= $highestRow; ++ $row) {
                   
                   $part .='<tr>';
                  
                   for ($col = 0; $col < $highestColumnIndex; ++ $col) {
                        
                       $cell = $worksheet->getCellByColumnAndRow($col, $row);
                       $val = $cell->getValue();
                       $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
                       if ( $val == "A" ) {
                          $part .='<td align="center"><input name="Ajouter" align="center" align="center" type="checkbox" value="0" checked id=';
                          $part .= $row ;
                          $part .= '></input></td>';
                       }
                       else {
                           $part .='<td>';
                           $part.= $val;
                           $part .='</td>';
                       }

                   }
                          
                   $part .='</tr>';
               }
               
      }

$part .= "</tbody></table>";
return $part;
}

$chtml = "<br /><div style='height:26px;width:100px;border: solid gray 1px;background:#F5F5F5;padding-top:4px;'>\n";
$chtml .=  "<a href='generalexport.php?var=tablecontents&debug=false' title='Export table to Excel'><img src='ajouter.gif' alt='Export table to Excel' hspace='3' /></a>\n";
$chtml .= "</div><br />\n";

$table = printTable();
$_SESSION['tablecontents'] = $table;

echo $table;

echo $chtml;

?>
</body>
</html>
1>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
And here the generalexport i used ( in fact i modified some lines in order to run it in my envirronement) :
2>>>>>>>>>>>>>>>>>>>>>>>>>>>
<?php

session_start();
ini_set("memory_limit", "-1");
ini_set("set_time_limit", "0");
set_time_limit(0);

if(!isset($_GET['limit'])) {
  $limit = 12;
}else{
  $limit = $_GET['limit'];
}
if(!isset($_GET['debug'])) {
  $debug = false;
}else{
  $debug = true;
  $handle = fopen("/exec/users/orito_urso/www/exportlog.txt", "w"); // i modified this line
  fwrite($handle, "\nDebugging On...");
}

if(!isset($_GET['var'])) {
  echo "<br />No Table Variable Present, nothing to Export.";
  exit;
}else{
  $tablevar = $_GET['var'];
}

if(!isset($_SESSION[$tablevar]) OR $_SESSION[$tablevar] == '') {
  echo "<br />Empty HTML Table, nothing to Export.";
  exit;
}else{
  $htmltable = $_SESSION[$tablevar];
}
if(strlen($htmltable) == strlen(strip_tags($htmltable)) ) {     // anything left after we strip HTML?
  echo "<br />Invalid HTML Table after Stripping Tags, nothing to Export.";
  exit;
}

$htmltable = strip_tags($htmltable, "<table><tr><th><thead><tbody><tfoot><td><br><b><span><input>");
$htmltable = str_replace("<br />", "\n", $htmltable);
$htmltable = str_replace("<br/>", "\n", $htmltable);
$htmltable = str_replace("<br>", "\n", $htmltable);
$htmltable = str_replace("&nbsp;", " ", $htmltable);
$htmltable = str_replace("\n\n", "\n", $htmltable);

//
//  Create Document Object Model from HTML table contents
//
$dom = new domDocument;
$dom->loadHTML($htmltable);
if(!$dom) {
  echo "<br />Invalid HTML DOM, nothing to Export.";
  exit;
}

$dom->preserveWhiteSpace = false;             // remove redundant whitespace
$tables = $dom->getElementsByTagName('table');
if(!is_object($tables)) {
  echo "<br />Invalid HTML Table DOM, nothing to Export.";
  exit;
}

if($tables->length < 1) {
  echo "<br />DOM Table Count is ".$tables->length.", nothing to Export.";
  exit;
}

$tbcnt = $tables->length - 1;                 // count minus 1 for 0 indexed loop over tables

//
//
// Create new PHPExcel object with default attributes
//

require_once ('PHPExcel.php');
$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(9);

$tm = date(YmdHis);
$fname = "testprog.xls";  // i modified this line

// i did modification here
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
$objPHPExcel->getProperties()->setKeywords("Exported File");
$objPHPExcel->getProperties()->setCategory("Export");


//
// Loop over tables in DOM to create an array, each table becomes a worksheet
//

  for($z=0;$z<=$tbcnt;$z++) {
  $maxcols = 0;
  $totrows = 0;
  $headrows = array();
  $bodyrows = array();
  $r = 0;
  $h = 0;
  $rows = $tables->item($z)->getElementsByTagName('tr');
  $totrows = $rows->length;
  if($debug) {
    fwrite($handle, "\nTotal Rows: ".$totrows);
  }

           
          //
          // Get TH values
          //
          foreach ($rows as $row) {
                  $ths = $row->getElementsByTagName('th');
                  if(is_object($ths)) {
                     if($ths->length > 0) {
                         $headrows[$h]['colcnt'] = $ths->length;
                         if($ths->length > $maxcols) {
                            $maxcols = $ths->length;
                         }
                         $nodes = $ths->length - 1; // - 1
                        
                         for($x=0;$x<=$nodes;$x++) {
                            $thishdg = $ths->item($x)->nodeValue;

                            $headrows[$h]['th'][] = $thishdg;
                            $headrows[$h]['bold'][] = findBoldText(innerHTML($ths->item($x)));
                            if($ths->item($x)->hasAttribute('style')) {
                               $style = $ths->item($x)->getAttribute('style');
                               $stylecolor = findStyleColor($style);
                               if($stylecolor == '') {
                                 $headrows[$h]['color'][] = findSpanColor(innerHTML($ths->item($x)));
                               }else {
                                 $headrows[$h]['color'][] = $stylecolor;
                               }

                            }else {
                                 $headrows[$h]['color'][] = findSpanColor(innerHTML($ths->item($x)));

                            }
                            
                            if($ths->item($x)->hasAttribute('colspan')) {
                                  $headrows[$h]['colspan'][] = $ths->item($x)->getAttribute('colspan');
                            }else{
                                  $headrows[$h]['colspan'][] = 1;
                            }  
                            
                            if($ths->item($x)->hasAttribute('align')) {
                               $headrows[$h]['align'][] = $ths->item($x)->getAttribute('align');
                            }else{
                               $headrows[$h]['align'][] = 'left';
                            }

                            if($ths->item($x)->hasAttribute('valign')) {
                               $headrows[$h]['valign'][] = $ths->item($x)->getAttribute('valign');
                            }else{
                               $headrows[$h]['valign'][] = 'top';
                            }

                            if($ths->item($x)->hasAttribute('bgcolor')) {
                              $headrows[$h]['bgcolor'][] = str_replace("#", "", $ths->item($x)->getAttribute('bgcolor'));
                            }else{
                              $headrows[$h]['bgcolor'][] = 'FFFFFF';
                            }
                                                       
                         }
                         $h++;
                     }
                  }
          } // TH values      
  }
  

            //
            // Get TD values
            //
            foreach ($rows as $row) {
                  
                 $tds = $row->getElementsByTagName('td');
                 if(is_object($tds)) {
                   if($tds->length > 0) {
                     $bodyrows[$r]['colcnt'] = $tds->length;
                     if($tds->length > $maxcols) {
                        $maxcols = $tds->length;
                     }
                     $nodes = $tds->length - 1;
                     for($x=0;$x<=$nodes;$x++) {
                        $thistxt = $tds->item($x)->nodeValue;
                     // i added this lines in order to check if the checkbox is checked or not
			     if($x == $nodes) {
                        $tinputs = $row->getElementsByTagName('input');
                        $len = $tinputs->length;
                         fwrite($handle, "\n len: ".$len);
                        if(is_object($tds)) {
                            $valueID = $tinputs->item(0)->nodeName;
                            fwrite($handle, "\n ValueID: ".$valueID);
                            $attributeid = $tinputs->item(0)->getAttribute('id');
                            $attributename = $tinputs->item(0)->getAttribute('name');
                            fwrite($handle, "\n Attributes_name: ".$attributename);
                            fwrite($handle, "\n Attributes_id: ".$attributeid);
                        }
                    }    
                        if($debug){

                            fwrite($handle, "\n cellValue: ".$thistxt);
                        } 
                        $bodyrows[$r]['td'][] = $thistxt;
                        $bodyrows[$r]['bold'][] = findBoldText(innerHTML($tds->item($x)));
                        if($tds->item($x)->hasAttribute('style')) {
                          $style = $tds->item($x)->getAttribute('style');
                          $stylecolor = findStyleColor($style);
                          if($stylecolor == '') {
                                $bodyrows[$r]['color'][] = findSpanColor(innerHTML($tds->item($x)));
                          }else{
                               $bodyrows[$r]['color'][] = $stylecolor;
                          }
                          }else{
                               $bodyrows[$r]['color'][] = findSpanColor(innerHTML($tds->item($x)));
                          }
                          if($tds->item($x)->hasAttribute('colspan')) {
                            $bodyrows[$r]['colspan'][] = $tds->item($x)->getAttribute('colspan');
                          }else{
                            $bodyrows[$r]['colspan'][] = 1;
                          }
                          if($tds->item($x)->hasAttribute('align')) {
                            $bodyrows[$r]['align'][] = $tds->item($x)->getAttribute('align');
                          }else{
                            $bodyrows[$r]['align'][] = 'left';
                          }
                          if($tds->item($x)->hasAttribute('valign')) {
                             $bodyrows[$r]['valign'][] = $tds->item($x)->getAttribute('valign');
                          }else{
                             $bodyrows[$r]['valign'][] = 'top';
                          }
                          if($tds->item($x)->hasAttribute('bgcolor')) {
                             $bodyrows[$r]['bgcolor'][] = str_replace("#", "", $tds->item($x)->getAttribute('bgcolor'));
                          }else{
                             $bodyrows[$r]['bgcolor'][] = 'FFFFFF';
                          }
                      }
                 $r++;
                }
              }
     

          }// Get TD values

  if($z > 0) {
      $objPHPExcel->createSheet($z);
  }
         
  $suf = $z + 1;
  $tableid = $tablevar.$suf;
  $wksheetname = ucfirst($tableid);
  $objPHPExcel->setActiveSheetIndex($z);                      // each sheet corresponds to a table in html
  $objPHPExcel->getActiveSheet()->setTitle($wksheetname);     // tab name
  $worksheet = $objPHPExcel->getActiveSheet();                // set worksheet we're working on

  $style_overlay = array('font' =>
                    array('color' =>
                      array('rgb' => '000000'),'bold' => false,),
                          'fill' 	=>
                              array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'CCCCFF')),
                          'alignment' =>
                              array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                         'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP),
                          'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                                             'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                                             'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                                             'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),
                       );
  $xcol = '';
  $xrow = 1;
  $usedhdrows = 0;
  $heightvars = array(1=>'42', 2=>'42', 3=>'48', 4=>'52', 5=>'58', 6=>'64', 7=>'68', 8=>'76', 9=>'82');

  for($h=0;$h<count($headrows);$h++) {

    $th = $headrows[$h]['th'];
    $colspans = $headrows[$h]['colspan'];
    $aligns = $headrows[$h]['align'];
    $valigns = $headrows[$h]['valign'];
    $bgcolors = $headrows[$h]['bgcolor'];
    $colcnt = $headrows[$h]['colcnt'];
    $colors = $headrows[$h]['color'];
    $bolds = $headrows[$h]['bold'];
    $usedhdrows++;
    $mergedcells = false;
   
    for($t=0;$t<count($th);$t++) {
        
      if($xcol == '') {$xcol = 'A';}else{$xcol++;}
      $thishdg = $th[$t];
      $thisalign = $aligns[$t];
      $thisvalign = $valigns[$t];
      $thiscolspan = $colspans[$t];
      $thiscolor = $colors[$t];
      $thisbg = $bgcolors[$t];
      $thisbold = $bolds[$t];
      $strbold = ($thisbold==true) ? 'true' : 'false';
      if($thisbg == 'FFFFFF') {
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE;
      }else{
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
      }
      $style_overlay['alignment']['vertical'] = $thisvalign;              // set styles for cell
      $style_overlay['alignment']['horizontal'] = $thisalign;
      $style_overlay['font']['color']['rgb'] = $thiscolor;
      $style_overlay['font']['bold'] = $thisbold;
      $style_overlay['fill']['color']['rgb'] = $thisbg;
      
      if($thiscolspan > 1) {                                                // spans more than 1 column
        $mergedcells = true;
        $lastxcol = $xcol;
        for($j=1;$j<$thiscolspan;$j++) {                                    // count to last column in span
          $lastxcol++;
        }
        $cellRange = $xcol.$xrow.':'.$lastxcol.$xrow;
        if($debug) {
          fwrite($handle, "\nmergeCells: ".$xcol.":".$xrow." ".$lastxcol.":".$xrow);
        }
        $worksheet->mergeCells($cellRange);                                // merge the columns
        $worksheet->setCellValue($xcol.$xrow, $thishdg);
        $worksheet->getStyle($cellRange)->applyFromArray($style_overlay);
        $worksheet->getStyle($cellRange)->getAlignment()->setWrapText(true);
        $num_newlines = substr_count($thishdg, "\n");
        if($num_newlines > 1) {
          $rowheight = $heightvars[1];                                      // default to 42
          if(array_key_exists($num_newlines, $heightvars)) {                // I couldn't find a PHPExcel method
            $rowheight = $heightvars[$num_newlines];                        // to do this, so I look to see how
          }else{                                                            // many newlines and just guess at
            $rowheight = 75;                                                // row height
          }
          $worksheet->getRowDimension($xrow)->setRowHeight($rowheight);     // adjust heading row height
          //$worksheet->getRowDimension($xrow)->setRowHeight(-1);           // this doesn't work in PHPExcel
        }
        if($debug) {
          fwrite($handle, "\n".$cellRange." ColSpan:".$thiscolspan." Color:".$thiscolor." Align:".$thisalign." VAlign:".$thisvalign." BGColor:".$thisbg." Bold:".$strbold." cellValue: ".$thishdg);
        }
        $xcol = $lastxcol;



    }else{
      
        $worksheet->setCellValue($xcol.$xrow, $thishdg);
        $worksheet->getStyle($xcol.$xrow)->applyFromArray($style_overlay);
        if($debug) {
          fwrite($handle, "\n".$xcol.":".$xrow." ColSpan:".$thiscolspan." Color:".$thiscolor." Align:".$thisalign." VAlign:".$thisvalign." BGColor:".$thisbg." Bold:".$strbold." cellValue: ".$thishdg);
        }

    }

  }
  $xrow++;
  $xcol = '';

}
  //Put an auto filter on last row of heading only if last row was not merged
  if(!$mergedcells) {
    $worksheet->setAutoFilter("A$usedhdrows:" . $worksheet->getHighestColumn() . $worksheet->getHighestRow() );
  }
  if($debug) {
    fwrite($handle, "\nautoFilter: A".$usedhdrows.":".$worksheet->getHighestColumn().$worksheet->getHighestRow());
  }
  // Freeze heading lines starting after heading lines
  $usedhdrows++;
  $worksheet->freezePane("A$usedhdrows");
  if($debug) {
    fwrite($handle, "\nfreezePane: A".$usedhdrows);
  }

  //
  // Loop thru data rows and write them out
  //
    $xcol = '';
  $xrow = $usedhdrows;
  for($b=0;$b<count($bodyrows);$b++) {
    $td = $bodyrows[$b]['td'];
    $colcnt = $bodyrows[$b]['colcnt'];
    $colspans = $bodyrows[$b]['colspan'];
    $aligns = $bodyrows[$b]['align'];
    $valigns = $bodyrows[$b]['valign'];
    $bgcolors = $bodyrows[$b]['bgcolor'];
    $colors = $bodyrows[$b]['color'];
    $bolds = $bodyrows[$b]['bold'];
    for($t=0;$t<count($td);$t++) {
      if($xcol == '') {$xcol = 'A';}else{$xcol++;}
      $thistext = $td[$t];
      $thisalign = $aligns[$t];
      $thisvalign = $valigns[$t];
      $thiscolspan = $colspans[$t];
      $thiscolor = $colors[$t];
      $thisbg = $bgcolors[$t];
      $thisbold = $bolds[$t];
      $strbold = ($thisbold==true) ? 'true' : 'false';
      if($thisbg == 'FFFFFF') {
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE;
      }else{
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
      }
      $style_overlay['alignment']['vertical'] = $thisvalign;              // set styles for cell
      $style_overlay['alignment']['horizontal'] = $thisalign;
      $style_overlay['font']['color']['rgb'] = $thiscolor;
      $style_overlay['font']['bold'] = $thisbold;
      $style_overlay['fill']['color']['rgb'] = $thisbg;
      if($thiscolspan > 1) {                                              // spans more than 1 column
        $lastxcol = $xcol;
        for($j=1;$j<$thiscolspan;$j++) {                                  // count spanned columns
          $lastxcol++;
        }
        $cellRange = $xcol.$xrow.':'.$lastxcol.$xrow;
        if($debug) {
          fwrite($handle, "\nmergeCells: ".$xcol.":".$xrow." ".$lastxcol.":".$xrow);
        }
        $worksheet->mergeCells($cellRange);                               // merge columns in span
        $worksheet->setCellValue($xcol.$xrow, $thistext);
        $worksheet->getStyle($cellRange)->applyFromArray($style_overlay);
        $worksheet->getStyle($cellRange)->getAlignment()->setWrapText(true);
        $num_newlines = substr_count($thistext, "\n");                       // count number of newline chars
        if($num_newlines > 1) {
          $rowheight = $heightvars[1];                                      // default to 42
          if(array_key_exists($num_newlines, $heightvars)) {                // I could not find a method in PHPExcel
            $rowheight = $heightvars[$num_newlines];                        // that would set row height automatically
          }else{                                                            // based on content, so I guess based
            $rowheight = 75;                                                // on number of newlines in the content
          }
          $worksheet->getRowDimension($xrow)->setRowHeight($rowheight);     // adjust heading row height
          //$worksheet->getRowDimension($xrow)->setRowHeight(-1);           // this doesn't work in PHPExcel
        }
        if($debug) {
          fwrite($handle, "\n".$cellRange." ColSpan:".$thiscolspan." Color:".$thiscolor." Align:".$thisalign." VAlign:".$thisvalign." BGColor:".$thisbg." Bold:".$strbold." cellValue: ".$thistext);
        }
        //$worksheet->getRowDimension($xrow)->setRowHeight(-1);
        $xcol = $lastxcol;
      }else{
        $worksheet->getColumnDimension($xcol)->setWidth(25);                // default width
        $worksheet->setCellValue($xcol.$xrow, $thistext);
        $worksheet->getStyle($xcol.$xrow)->applyFromArray($style_overlay);
        if($debug) {
          fwrite($handle, "\n".$xcol.":".$xrow." ColSpan:".$thiscolspan." Color:".$thiscolor." Align:".$thisalign." VAlign:".$thisvalign." BGColor:".$thisbg." Bold:".$strbold." cellValue: ".$thistext);
        }
      }
    }
    $xrow++;
    $xcol = '';
  }

  // autosize columns to fit data
  $azcol = 'A';
  for($x=1;$x==$maxcols;$x++) {
    $worksheet->getColumnDimension($azcol)->setAutoSize(true);
    $azcol++;
  }
  if($debug) {
    fwrite($handle, "\nHEADROWS: ".print_r($headrows, true));
    fwrite($handle, "\nBODYROWS: ".print_r($bodyrows, true));
  }

 // end for over tables 

$objPHPExcel->setActiveSheetIndex(0);                      // set to first worksheet before close

//
// Write to Browser
//

if($debug) {
  fclose($handle);
}

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=$fname");
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//$objWriter->save($fname);

$objWriter->save('php://output');

exit;


function innerHTML($node) {
  $doc = $node->ownerDocument;
  $frag = $doc->createDocumentFragment();
  foreach ($node->childNodes as $child) {
    $frag->appendChild($child->cloneNode(TRUE));
  }
  return $doc->saveXML($frag);
}
function findSpanColor($node) {
  $pos = stripos($node, "color:");       // ie: looking for style='color: #FF0000;'
  if ($pos === false) {                  //                        12345678911111
    return '000000';                     //                                 01234
  }
  $node = substr($node, $pos);           // truncate to color: start
  $start = "#";                          // looking for html color string
  $end = ";";                            // should end with semicolon
  $node = " ".$node;                     // prefix node with blank
	$ini = stripos($node,$start);          // look for #
	if ($ini === false) return "000000";   // not found, return default color of black
	$ini += strlen($start);                // get 1 byte past start string
	$len = stripos($node,$end,$ini) - $ini; // grab substr between start and end positions
	return substr($node,$ini,$len);        // return the RGB color without # sign
}
function findStyleColor($style) {
  $pos = stripos($style, "color:");      // ie: looking for style='color: #FF0000;'
  if ($pos === false) {                  //                        12345678911111
    return '';                           //                                 01234
  }
  $style = substr($style, $pos);           // truncate to color: start
  $start = "#";                          // looking for html color string
  $end = ";";                            // should end with semicolon
  $style = " ".$style;                     // prefix node with blank
	$ini = stripos($style,$start);          // look for #
	if ($ini === false) return "";         // not found, return default color of black
	$ini += strlen($start);                // get 1 byte past start string
	$len = stripos($style,$end,$ini) - $ini; // grab substr between start and end positions
	return substr($style,$ini,$len);        // return the RGB color without # sign
}
function findBoldText($node) {
  $pos = stripos($node, "<b>");          // ie: looking for bolded text
  if ($pos === false) {                  //                        12345678911111
    return false;                        //                                 01234
  }
  return true;                           // found <b>
}
?>

2>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
and below the table (its an excel file content):

Cell 1

LAC 1

 

CI 1

Cell 2

LAC 2

CI 2

Distance

(km)

 

Plaque

Ajout

A

 

1539

12306

E

 

1539

16442

100

palque1

 

A

B

 

1543

29136

F

 

1543

62867

100

plaque2

 

A

C

 

1544

2222

G

 

1544

31551

100

plaque3

 

A

D

 

1544

2090

H

 

1544

42723

100

plaque4

 

A

 
Dec 6, 2011 at 11:03 PM

Kass34,

First off, I can't help you with  $objWriter->save($fname);  and $objWriter->save('php://output');  that is part of PHPExcel code.  It works either way on my systems.

Secondly, an explanation here is going to be wordy.......

If you simply want to capture the HTML table as it was first created and also check the values in checkboxes, then you can simply use the first example below instead of generalexport.

Beware that there is no way to actually see if a box was checked, you can only see the value of whatever attributes are set in the HTML at time of creation.  

<?php
session_start();
ini_set("memory_limit", "-1");
ini_set("set_time_limit", "0");
set_time_limit(0);
if (isset($_SESSION['zprofile'])) {
  $username = $_SESSION['zprofile']['username'];              // user's name
  $usermail = $_SESSION['zprofile']['useremail'];             // user's emailid
  $usercompany = $_SESSION['zprofile']['usercompany'];        // user's company
  $usergroup = $_SESSION['zprofile']['usergroup'];            // user's group
  $useraccount = $_SESSION['zprofile']['useraccount'];        // user's group
}else{
  header('Location: index.php?e=0');
}
if(!isset($_GET['var'])) {
  echo "<br />No Table Variable Present, nothing to Export.";
  exit;
}else{
  $tablevar = $_GET['var'];
}
if(!isset($_GET['limit'])) {
  $limit = 12;                            // maximum number of Excel tabs to create, optional
}else{
  $limit = $_GET['limit'];
}
if(!isset($_GET['debug'])) {             // optional, debug script by writing out to log file
  $debug = false;
}else{
  $debug = true;
  $handle = fopen("Auditlog/exportdebug_log.txt", "w");
  fwrite($handle, "\nDebugging On...");
}
if(!isset($_SESSION[$tablevar]) OR $_SESSION[$tablevar] == '') {
  echo "<br />Empty HTML Table, nothing to Export.";
  exit;
}else{
  $htmltable = $_SESSION[$tablevar];
}
if(strlen($htmltable) == strlen(strip_tags($htmltable)) ) {     // anything left after we strip HTML?
  echo "<br />Invalid HTML Table after Stripping Tags, nothing to Export.";
  exit;
}
if($debug) {
  fwrite($handle, "\n-------------------------------------------");
  fwrite($handle, "\nHTML before prep: \n".$htmltable);
  fwrite($handle, "\n-------------------------------------------");
}
$htmltable = strip_tags($htmltable, "<table><tr><th><thead><tbody><tfoot><td><br><b><span><input>");
$htmltable = str_replace("<br />", "\n", $htmltable);
$htmltable = str_replace("<br/>", "\n", $htmltable);
$htmltable = str_replace("<br>", "\n", $htmltable);
$htmltable = str_replace("&nbsp;", " ", $htmltable);
$htmltable = str_replace("\n\n", "\n", $htmltable);
if($debug) {
  fwrite($handle, "\n-------------------------------------------");
  fwrite($handle, "\nHTML after prep: \n".$htmltable);
  fwrite($handle, "\n-------------------------------------------");
}
//
//  Create Document Object Model from HTML table contents
//
$dom = new domDocument;
$dom->loadHTML($htmltable);
if(!$dom) {
  echo "<br />Invalid HTML DOM, nothing to Export.";
  exit;
}
$dom->preserveWhiteSpace = false;             // remove redundant whitespace
$tables = $dom->getElementsByTagName('table');
if(!is_object($tables)) {
  echo "<br />Invalid HTML Table DOM, nothing to Export.";
  exit;
}
if($debug) {
  fwrite($handle, "\nTable Count: ".$tables->length);
}
if($tables->length < 1) {
  echo "<br />DOM Table Count is ".$tables->length.", nothing to Export.";
  exit;
}
$tbcnt = $tables->length - 1;                 // count minus 1 for 0 indexed loop over tables
if($tbcnt > $limit) {
  $tbcnt = $limit;
}
//
//
// Create new PHPExcel object with default attributes
//
require_once ('PHPExcel/PHPExcel.php');
$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
$tm = date(YmdHis);
$pos = strpos($usermail, "@");
$user = substr($usermail, 0, $pos);
$user = str_replace(".","",$user);
$tfn = $user."_".$tm."_".$tablevar.".xlsx";
//$fname = "AuditLog/".$tfn;
$fname = $tfn;
$objPHPExcel->getProperties()->setCreator($username)
->setLastModifiedBy($username)
->setTitle("Automated Export")
->setSubject("Automated Report Generation")
->setDescription("Automated Report generation.")
->setKeywords("Exported File")
               ->setCompany($usercompany)
->setCategory("Export");
//
// Loop over tables in DOM to create an array, each table becomes a worksheet
//
for($z=0;$z<=$tbcnt;$z++) {
  $maxcols = 0;
  $totrows = 0;
  $headrows = array();
  $bodyrows = array();
  $r = 0;
  $h = 0;
  $rows = $tables->item($z)->getElementsByTagName('tr');
  $totrows = $rows->length;
  if($debug) {
    fwrite($handle, "\nTotal Rows: ".$totrows);
  }
  //
  // Get TH values
  //
  foreach ($rows as $row) {
      $ths = $row->getElementsByTagName('th');
      if(is_object($ths)) {
        if($ths->length > 0) {
          $headrows[$h]['colcnt'] = $ths->length;
          if($ths->length > $maxcols) {
            $maxcols = $ths->length;
          }
          $nodes = $ths->length - 1;
          for($x=0;$x<=$nodes;$x++) {
            $thishdg = $ths->item($x)->nodeValue;
            $headrows[$h]['th'][] = $thishdg;
            $headrows[$h]['bold'][] = findBoldText(innerHTML($ths->item($x)));
            if($ths->item($x)->hasAttribute('style')) {
              $style = $ths->item($x)->getAttribute('style');
              $stylecolor = findStyleColor($style);
              if($stylecolor == '') {
                $headrows[$h]['color'][] = findSpanColor(innerHTML($ths->item($x)));
              }else{
                $headrows[$h]['color'][] = $stylecolor;
              }
            }else{
              $headrows[$h]['color'][] = findSpanColor(innerHTML($ths->item($x)));
            }
            if($ths->item($x)->hasAttribute('colspan')) {
              $headrows[$h]['colspan'][] = $ths->item($x)->getAttribute('colspan');
            }else{
              $headrows[$h]['colspan'][] = 1;
            }
            if($ths->item($x)->hasAttribute('align')) {
              $headrows[$h]['align'][] = $ths->item($x)->getAttribute('align');
            }else{
              $headrows[$h]['align'][] = 'left';
            }
            if($ths->item($x)->hasAttribute('valign')) {
              $headrows[$h]['valign'][] = $ths->item($x)->getAttribute('valign');
            }else{
              $headrows[$h]['valign'][] = 'top';
            }
            if($ths->item($x)->hasAttribute('bgcolor')) {
              $headrows[$h]['bgcolor'][] = str_replace("#", "", $ths->item($x)->getAttribute('bgcolor'));
            }else{
              $headrows[$h]['bgcolor'][] = 'FFFFFF';
            }
          }
          $h++;
        }
      }
  }
  //
  // Get TD values
  //
  foreach ($rows as $row) {
      $tds = $row->getElementsByTagName('td');
      if(is_object($tds)) {
        if($tds->length > 0) {
          //
          // see if there are checkboxes within each TD for the row and only include checked rows in output
          //
          $nodes = $tds->length - 1;                // number of TD's present
          $includerow = true;                       // include row? (true or false), default = true
          for($x=0;$x<=$nodes;$x++) {               // td nodes
            $allinputs = $tds->item($x)->getElementsByTagName('input');
            if(is_object($allinputs)) {
              for ($i = 0; $i < $allinputs->length; $i++) {
                if($debug) {
                  if ($allinputs->item($i)->hasAttributes()) {
                    foreach ($allinputs->item($i)->attributes as $attr) {
                      $name = $attr->nodeName;
                      $value = $attr->nodeValue;
                      fwrite($handle, "Attribute '$name' :: '$value'\n");
                    }
                  }
                }
                if($allinputs->item($i)->hasAttribute('type')) {
                  $type = $allinputs->item($i)->getAttribute('type');
                  if($debug) { fwrite($handle, "\nType: ".$type."\n"); }
                }
                if($type == 'checkbox') {
                  if($allinputs->item($i)->hasAttribute('value')) {
                    $value = $allinputs->item($i)->getAttribute('value');
                    if($debug) { fwrite($handle, "\nValue: ".$value."\n"); }
                    if($value == '0') {
                      $includerow = false;
                    }
                  }else{
                    if($debug) {fwrite($handle, "\nInput field found as Type: ".$type."  with empty Value: ".$value."\n"); }
                  }
                }
              }
            }
          }
          // end checkbox row exclusion
          if($includerow) {                     // checkbox is checked
            $bodyrows[$r]['colcnt'] = $tds->length;
            if($tds->length > $maxcols) {
              $maxcols = $tds->length;
            }
            $nodes = $tds->length - 1;
            for($x=0;$x<=$nodes;$x++) {
              $thistxt = $tds->item($x)->nodeValue;
              $bodyrows[$r]['td'][] = $thistxt;
              $bodyrows[$r]['bold'][] = findBoldText(innerHTML($tds->item($x)));
              if($tds->item($x)->hasAttribute('style')) {
                $style = $tds->item($x)->getAttribute('style');
                $stylecolor = findStyleColor($style);
                if($stylecolor == '') {
                  $bodyrows[$r]['color'][] = findSpanColor(innerHTML($tds->item($x)));
                }else{
                  $bodyrows[$r]['color'][] = $stylecolor;
                }
              }else{
                $bodyrows[$r]['color'][] = findSpanColor(innerHTML($tds->item($x)));
              }
              if($tds->item($x)->hasAttribute('colspan')) {
                $bodyrows[$r]['colspan'][] = $tds->item($x)->getAttribute('colspan');
              }else{
                $bodyrows[$r]['colspan'][] = 1;
              }
              if($tds->item($x)->hasAttribute('align')) {
                $bodyrows[$r]['align'][] = $tds->item($x)->getAttribute('align');
              }else{
                $bodyrows[$r]['align'][] = 'left';
              }
              if($tds->item($x)->hasAttribute('valign')) {
                $bodyrows[$r]['valign'][] = $tds->item($x)->getAttribute('valign');
              }else{
                $bodyrows[$r]['valign'][] = 'top';
              }
              if($tds->item($x)->hasAttribute('bgcolor')) {
                $bodyrows[$r]['bgcolor'][] = str_replace("#", "", $tds->item($x)->getAttribute('bgcolor'));
              }else{
                $bodyrows[$r]['bgcolor'][] = 'FFFFFF';
              }
            }
            $r++;
          }
        }
      }
  }
  if($z > 0) {
    $objPHPExcel->createSheet($z);
  }
  $suf = $z + 1;
  $tableid = $tablevar.$suf;
  $wksheetname = ucfirst($tableid);
  $objPHPExcel->setActiveSheetIndex($z);                      // each sheet corresponds to a table in html
  $objPHPExcel->getActiveSheet()->setTitle($wksheetname);     // tab name
  $worksheet = $objPHPExcel->getActiveSheet();                // set worksheet we're working on
  $style_overlay = array('font' =>
                    array('color' =>
                      array('rgb' => '000000'),'bold' => false,),
                          'fill' =>
                              array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'CCCCFF')),
                          'alignment' =>
                              array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                         'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP),
                          'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                                             'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                                             'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                                             'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),
                       );
  $xcol = '';
  $xrow = 1;
  $usedhdrows = 0;
  $heightvars = array(1=>'42', 2=>'42', 3=>'48', 4=>'52', 5=>'58', 6=>'64', 7=>'68', 8=>'76', 9=>'82');
  for($h=0;$h<count($headrows);$h++) {
    $th = $headrows[$h]['th'];
    $colspans = $headrows[$h]['colspan'];
    $aligns = $headrows[$h]['align'];
    $valigns = $headrows[$h]['valign'];
    $bgcolors = $headrows[$h]['bgcolor'];
    $colcnt = $headrows[$h]['colcnt'];
    $colors = $headrows[$h]['color'];
    $bolds = $headrows[$h]['bold'];
    $usedhdrows++;
    $mergedcells = false;
    for($t=0;$t<count($th);$t++) {
      if($xcol == '') {$xcol = 'A';}else{$xcol++;}
      $thishdg = $th[$t];
      $thisalign = $aligns[$t];
      $thisvalign = $valigns[$t];
      $thiscolspan = $colspans[$t];
      $thiscolor = $colors[$t];
      $thisbg = $bgcolors[$t];
      $thisbold = $bolds[$t];
      $strbold = ($thisbold==true) ? 'true' : 'false';
      if($thisbg == 'FFFFFF') {
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE;
      }else{
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
      }
      $style_overlay['alignment']['vertical'] = $thisvalign;              // set styles for cell
      $style_overlay['alignment']['horizontal'] = $thisalign;
      $style_overlay['font']['color']['rgb'] = $thiscolor;
      $style_overlay['font']['bold'] = $thisbold;
      $style_overlay['fill']['color']['rgb'] = $thisbg;
      if($thiscolspan > 1) {                                                // spans more than 1 column
        $mergedcells = true;
        $lastxcol = $xcol;
        for($j=1;$j<$thiscolspan;$j++) {                                    // count to last column in span
          $lastxcol++;
        }
        $cellRange = $xcol.$xrow.':'.$lastxcol.$xrow;
        if($debug) {
          fwrite($handle, "\nmergeCells: ".$xcol.":".$xrow." ".$lastxcol.":".$xrow);
        }
        $worksheet->mergeCells($cellRange);                                // merge the columns
        $worksheet->setCellValue($xcol.$xrow, $thishdg);
        $worksheet->getStyle($cellRange)->applyFromArray($style_overlay);
        $worksheet->getStyle($cellRange)->getAlignment()->setWrapText(true);
        $num_newlines = substr_count($thishdg, "\n");                       // count number of newline chars
        if($num_newlines > 1) {
          $rowheight = $heightvars[1];                                      // default to 35
          if(array_key_exists($num_newlines, $heightvars)) {                // I couldn't find a PHPExcel method
            $rowheight = $heightvars[$num_newlines];                        // to do this, so I look to see how
          }else{                                                            // many newlines and just guess at
            $rowheight = 75;                                                // row height
          }
          $worksheet->getRowDimension($xrow)->setRowHeight($rowheight);     // adjust heading row height
          //$worksheet->getRowDimension($xrow)->setRowHeight(-1);           // this doesn't work in PHPExcel
        }
        if($debug) {
          fwrite($handle, "\n".$cellRange." ColSpan:".$thiscolspan." Color:".$thiscolor." Align:".$thisalign." VAlign:".$thisvalign." BGColor:".$thisbg." Bold:".$strbold." cellValue: ".$thishdg);
        }
        $xcol = $lastxcol;
      }else{
        $worksheet->setCellValue($xcol.$xrow, $thishdg);
        $worksheet->getStyle($xcol.$xrow)->applyFromArray($style_overlay);
        if($debug) {
          fwrite($handle, "\n".$xcol.":".$xrow." ColSpan:".$thiscolspan." Color:".$thiscolor." Align:".$thisalign." VAlign:".$thisvalign." BGColor:".$thisbg." Bold:".$strbold." cellValue: ".$thishdg);
        }
      }
    }
    $xrow++;
    $xcol = '';
  }
  //Put an auto filter on last row of heading only if last row was not merged
  if(!$mergedcells) {
    $worksheet->setAutoFilter("A$usedhdrows:" . $worksheet->getHighestColumn() . $worksheet->getHighestRow() );
  }
  if($debug) {
    fwrite($handle, "\nautoFilter: A".$usedhdrows.":".$worksheet->getHighestColumn().$worksheet->getHighestRow());
  }
  // Freeze heading lines starting after heading lines
  $usedhdrows++;
  $worksheet->freezePane("A$usedhdrows");
  if($debug) {
    fwrite($handle, "\nfreezePane: A".$usedhdrows);
  }
  //
  // Loop thru data rows and write them out
  //
  $xcol = '';
  $xrow = $usedhdrows;
  for($b=0;$b<count($bodyrows);$b++) {
    $td = $bodyrows[$b]['td'];
    $colcnt = $bodyrows[$b]['colcnt'];
    $colspans = $bodyrows[$b]['colspan'];
    $aligns = $bodyrows[$b]['align'];
    $valigns = $bodyrows[$b]['valign'];
    $bgcolors = $bodyrows[$b]['bgcolor'];
    $colors = $bodyrows[$b]['color'];
    $bolds = $bodyrows[$b]['bold'];
    for($t=0;$t<count($td);$t++) {
      if($xcol == '') {$xcol = 'A';}else{$xcol++;}

$thistext = $td[$t]; $thisalign = $aligns[$t]; $thisvalign = $valigns[$t]; $thiscolspan = $colspans[$t]; $thiscolor = $colors[$t]; $thisbg = $bgcolors[$t]; $thisbold = $bolds[$t]; $strbold = ($thisbold==true) ? 'true' : 'false';
      if($thisbg == 'FFFFFF') {
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE;
      }else{
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
      }
      $style_overlay['alignment']['vertical'] = $thisvalign;              // set styles for cell
      $style_overlay['alignment']['horizontal'] = $thisalign;
      $style_overlay['font']['color']['rgb'] = $thiscolor;
      $style_overlay['font']['bold'] = $thisbold;
      $style_overlay['fill']['color']['rgb'] = $thisbg;
      if($thiscolspan > 1) {                                              // spans more than 1 column
        $lastxcol = $xcol;
        for($j=1;$j<$thiscolspan;$j++) {                                  // count spanned columns
          $lastxcol++;
        }
        $cellRange = $xcol.$xrow.':'.$lastxcol.$xrow;
        if($debug) {
          fwrite($handle, "\nmergeCells: ".$xcol.":".$xrow." ".$lastxcol.":".$xrow);
        }
        $worksheet->mergeCells($cellRange);                               // merge columns in span
        $worksheet->setCellValue($xcol.$xrow, $thistext);
        $worksheet->getStyle($cellRange)->applyFromArray($style_overlay);
        $worksheet->getStyle($cellRange)->getAlignment()->setWrapText(true);
        $num_newlines = substr_count($thistext, "\n");                       // count number of newline chars
        if($num_newlines > 1) {
          $rowheight = $heightvars[1];                                      // default to 35
          if(array_key_exists($num_newlines, $heightvars)) {                // I could not find a method in PHPExcel
            $rowheight = $heightvars[$num_newlines];                        // that would set row height automatically
          }else{                                                            // based on content, so I guess based
            $rowheight = 75;                                                // on number of newlines in the content
          }
          $worksheet->getRowDimension($xrow)->setRowHeight($rowheight);     // adjust heading row height
          //$worksheet->getRowDimension($xrow)->setRowHeight(-1);           // this doesn't work in PHPExcel
        }
        if($debug) {
          fwrite($handle, "\n".$cellRange." ColSpan:".$thiscolspan." Color:".$thiscolor." Align:".$thisalign." VAlign:".$thisvalign." BGColor:".$thisbg." Bold:".$strbold." cellValue: ".$thistext);
        }
        //$worksheet->getRowDimension($xrow)->setRowHeight(-1);
        $xcol = $lastxcol;
      }else{
        $worksheet->getColumnDimension($xcol)->setWidth(25);                // default width
        $worksheet->setCellValue($xcol.$xrow, $thistext);
        $worksheet->getStyle($xcol.$xrow)->applyFromArray($style_overlay);
        if($debug) {
          fwrite($handle, "\n".$xcol.":".$xrow." ColSpan:".$thiscolspan." Color:".$thiscolor." Align:".$thisalign." VAlign:".$thisvalign." BGColor:".$thisbg." Bold:".$strbold." cellValue: ".$thistext);
        }
      }
    }
    $xrow++;
    $xcol = '';
  }
  // autosize columns to fit data
  $azcol = 'A';
  for($x=1;$x==$maxcols;$x++) {
    $worksheet->getColumnDimension($azcol)->setAutoSize(true);
    $azcol++;
  }
  if($debug) {
    fwrite($handle, "\nHEADROWS: ".print_r($headrows, true));
    fwrite($handle, "\nBODYROWS: ".print_r($bodyrows, true));
  }
} // end for over tables
$objPHPExcel->setActiveSheetIndex(0);                      // set to first worksheet before close
//
// Write to Browser
//
if($debug) {
  fclose($handle);
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=$fname");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//$objWriter->save($fname);
$objWriter->save('php://output');
exit;
function innerHTML($node) {
  $doc = $node->ownerDocument;
  $frag = $doc->createDocumentFragment();
  foreach ($node->childNodes as $child) {
    $frag->appendChild($child->cloneNode(TRUE));
  }
  return $doc->saveXML($frag);
}
function findSpanColor($node) {
  $pos = stripos($node, "color:");       // ie: looking for style='color: #FF0000;'
  if ($pos === false) {                  //                        12345678911111
    return '000000';                     //                                 01234
  }
  $node = substr($node, $pos);           // truncate to color: start
  $start = "#";                          // looking for html color string
  $end = ";";                            // should end with semicolon
  $node = " ".$node;                     // prefix node with blank
$ini = stripos($node,$start);          // look for #
if ($ini === false) return "000000";   // not found, return default color of black
$ini += strlen($start);                // get 1 byte past start string
$len = stripos($node,$end,$ini) - $ini; // grab substr between start and end positions
return substr($node,$ini,$len);        // return the RGB color without # sign
}
function findStyleColor($style) {
  $pos = stripos($style, "color:");      // ie: looking for style='color: #FF0000;'
  if ($pos === false) {                  //                        12345678911111
    return '';                           //                                 01234
  }
  $style = substr($style, $pos);           // truncate to color: start
  $start = "#";                          // looking for html color string
  $end = ";";                            // should end with semicolon
  $style = " ".$style;                     // prefix node with blank
$ini = stripos($style,$start);          // look for #
if ($ini === false) return "";         // not found, return default color of black
$ini += strlen($start);                // get 1 byte past start string
$len = stripos($style,$end,$ini) - $ini; // grab substr between start and end positions
return substr($style,$ini,$len);        // return the RGB color without # sign
}
function findBoldText($node) {
  $pos = stripos($node, "<b>");          // ie: looking for bolded text
  if ($pos === false) {                  //                        12345678911111
    return false;                        //                                 01234
  }
  return true;                           // found <b>
}
?>

 

 If you really want to make this dynamic and pick up the latest checked status (value), you will need to use a combination of PHP and javascript.

You need to do  something like I outline below to keep the saved HTML values in sync with what the user checks or unchecks.

Here is how the HTML should look:

<table>
  <thead>
  <tr>
  <td>heading1</td>
  <td>heading2</td>
  <td>heading3</td>
  <td>LEF</td>
  </tr>
  </thead>
  <tbody>
  <tr>
    <td>row1data1</td>
    <td>row1data2</td>
    <td>row1data3</td>
    <!-- make sure you use a unique ID for each checkbox, so the right one is updated in the session variable copy .... -->
    <!-- values should be set to 0 with no checked='checked' parameter, OR '1' and checked='checked' parameter -->
    <td><input type="checkbox" id="mycheckbox1" value="1" onchange="updateCheckBoxInSessionTable(this, 'tablecontents');" checked="checked" /></td>
  </tr>
  <tr>
    <td>row1data1</td>
    <td>row1data2</td>
    <td>row1data3</td>
    <td><input type="checkbox" id="mycheckbox2" value="2" onchange="updateCheckBoxInSessionTable(this, 'tablecontents');" /></td>
  </tr>
  <!-- more rows as needed .... ->
  </tbody>
</table>

Then you need to have this javascript function available to your page displaying the table.  Everytime a checkbox changes, it will call this function.

<script type='javascript'>
  function updateCheckBoxInSessionTable(o_chkbx, sessvarname) {
    //
    // get the checkbox id and value
    //
    if(o_chkbx.checked) {
      var value = '1';
    }else{
      var value = '0';
    }
    var id = o_chkbx.id;
    //
    // this is my Ajax function, use your own Ajax function to call the PHP program from javascript
    //
    var zserverpgm = "checkboxupdate.php";
    var zserverparms = "id="+id+"&value="+value+"&var="+sessvarname;
    zAsync = false;
    zajaxgettempnd(zserverpgm, zserverparms);
  }

  </script>
This PHP script is then used to keep the session variable HTML contents in sync with the displayed page checkbox values, so when you export it, its in sync with what the user sees.
<?php // script name: checkboxupdate.php
session_start();
error_reporting(E_ALL);
//
// Replace HTML ID element value
//
// ID passed to this script is the ID of the HTML element so that innerHTML can be changed to the passed value
// VALUE passed to this script is the new value to replace the existing value within the HTML element
//
if(isset($_GET['id'])) {
  $id = $_GET['id'];
}else{
  echo "Missing ID parameter!";      // can't do anything without ID
  exit;
}
if(isset($_GET['value'])) {
  $value = $_GET['value'];           // ascii string with \n for newline breaks
}else{
  echo "Missing Value parameter!";   // can't do anything without a value
  exit;
}
if(isset($_GET['var'])) {
  $var = $_GET['var'];
}else{
  echo "Missing VAR parameter!";      // can't do anything without the session var
  exit;
}
//
// Using Simple HTML DOM, update the checkbox element in the session table, then save the session table
//
include('includes/simple_html_dom.php');                 // find this at  http://simplehtmldom.sourceforge.net/
$html = str_get_html($_SESSION[$var]);                   // get the table contents from the session variable
$valueexcel = str_ireplace("\n", "<br>", $value);        // replace line breaks \n to HTML <BR>
$e = $html->find("input[id=$id]");                       // find the HTML input element
foreach($e as $single_e){
  if($single_e->id == $id) {                             // if we find our ID
    $single_e->value = $valueexcel;                      // replace the innerText (innerHTML) content with new value
    $_SESSION[$var] = "";                                // clear the session table value
    $_SESSION[$var] = $html->save();                     // now save updated HTML into the session table value
  }
}
$html->clear();                                          // Close and release resources
unset($html);
// you could also update databases or other sources if needed while you have this data, or simply exit
?>


To export this table, use the sample I provided at the top of this reply.

Dec 6, 2011 at 11:15 PM

Sorry, I have an error in the sample HTML in last reply.  The value for non-check checkbox should be 0 (zero), not 2.

td><input type="checkbox" id="mycheckbox1" value="1" onchange="updateCheckBoxInSessionTable(this, 'tablecontents');" checked="checked" /></td>
  </tr>
  <tr>
    <td>row1data1</td>
    <td>row1data2</td>
    <td>row1data3</td>
    <td><input type="checkbox" id="mycheckbox2" value="0" onchange="updateCheckBoxInSessionTable(this, 'tablecontents');" /></td>
  </tr>
  <!-- more rows as needed .... ->

Dec 8, 2011 at 1:50 PM

Hello mamuscia,

Could you tell me which version of simple_html_dom.php you used ?

because i download the version 1.5 and the str_get_html is not working. do you have an idea ?

Thx in advance

Dec 8, 2011 at 1:52 PM

Sorry it seems that the version is :

* @author S.C. Chen <me578022@gmail.com>
 * @author John Schlick
 * @author Rus Carroll
 * @version 1.11 ($Rev: 184 $)
 * @package PlaceLocalInclude
 * @subpackage simple_html_dom

Thx

Dec 8, 2011 at 3:03 PM

I found that in the net : (SourceForge site)

 

str_get_html()'s defaultBRText does'nt work. - ID: 3441953

Last Update: Tracker Item Submitted ( okinakak )
<label>Details:</label>

<!-- google_ad_section_start -->In str_get_html(), set $stripRN simple_html_dom object
and set $defaultBRText in $dom->load().


@@ -82,13 +82,13 @@ function file_get_html($url, $use_include_path = false, $context=null, $offset =
// get html dom from string
function str_get_html($str, $lowercase=true, $forceTagsClosed=true, $target_charset = DEFAULT_TARGET_CHARSET, $stripRN=true, $defaultBRText=DEFAULT_BR_TEXT)
{
- $dom = new simple_html_dom(null, $lowercase, $forceTagsClosed, $target_charset, $defaultBRText);
+ $dom = new simple_html_dom(null, $lowercase, $forceTagsClosed, $target_charset, $stripRN, $defaultBRText);
if (empty($str))
{
$dom->clear();
return false;
}
- $dom->load($str, $lowercase, $stripRN);
+ $dom->load($str, $lowercase, $stripRN, $defaultBRText);
return $dom;
}
<!-- google_ad_section_end -->

Any idea about that ?

Dec 8, 2011 at 3:16 PM

I am using the following version which works fine.

/*******************************************************************************
Version: 1.11 ($Rev: 175 $)
Website: http://sourceforge.net/projects/simplehtmldom/
Author: S.C. Chen <me578022@gmail.com>
Acknowledge: Jose Solorzano (https://sourceforge.net/projects/php-html/)
Contributions by:
    Yousuke Kumakura (Attribute filters)
    Vadim Voituk (Negative indexes supports of "find" method)
    Antcs (Constructor with automatically load contents either text or file/url)
Licensed under The MIT License
Redistributions of files must retain the above copyright notice.
*******************************************************************************/

Download the 1.11 version which is what I use.

Dec 14, 2011 at 5:16 PM

Hello mamuscia,

Thanks for your help, now i can export my table in an excel file :) :):) .....

Just one think also :

What i want to do that when the file is exported  i hope that my page display just only ther headers of my table, how can i do that ?

Thanks in advance

 

 

Dec 14, 2011 at 6:07 PM

Kass34,

I'm not sure I understand what you are asking in previous post....can you elaborate please?

Dec 15, 2011 at 8:13 AM

Mamusica,

here are the steps f my prohram:

1. Read data from an excel file

2. Display them as an html table in a web page

3. The user can select or deselect lines of the thable in web page

4. export the selected lines from the hatml table of the web page

the above steps are well done by your help as described in the previous posts ad your generaleexport.

i have to do the last step:

5. when the user export the selected lines, i want to delete all lines of the table except the header line : that the user don't see anymore the content of the input excel file in order to remind him that he has exported them. then the web page will display just the header line.

Any idea how tyo do that ?

Thx in advance

 

Dec 15, 2011 at 9:46 PM

There are a few things to know before we can solve this.

1.  how is the table first displayed to the user?  does it contain both checked and unchecked rows?

2.  how do you want users to check unseen rows?  ie: if a row is unchecked you said you didn't want to display it, so how is a user supposed to check a row they cannot see?

3.  I don't quite understand what process you want to follow from #5 above.  can you break it down a bit into steps

 

 

Dec 22, 2011 at 10:52 PM

Hi.
Excellent example, but I have a table with colspan and rowspan html, but does not work rowspan, could you help me?

 

Tanks!!

Coordinator
Jan 2, 2012 at 1:44 PM
Edited Jan 2, 2012 at 1:46 PM

I'm working on an HTML Reader using domDocument (which is meant to be the best tool when working with HTML that's not well-formed, as well as markup that is) that handles colspan and rowspan without problems, though still issues with nested tables (believe me, I've seen examples of this in "fake" .xls files where people have simply dumped their HTML markup with an .xls extension and expected PHPExcel to read it) and with (a real surprise, though I understand why) <br />

It's on the roadmap to be included in 2012.

Jan 25, 2012 at 1:09 PM

hi thanks for your code is very interesting, but I'm trying to export a table to excel and I have two problems.


The first is that I have problems with rowsnap that do not take the code.

the second is that when you open the excel file tells me that he had to repair the archvio on line 2 column XXXX column is always a 4-digit number, and do not know why this will be produced.
 

 

May 25, 2012 at 12:26 AM

Hello mamuscia,

Thanks for the code, It works great. The only issue I had is with the dom creation:

$dom->loadHTML($htmltable);
It gave me an error. To resolve this I just simply added @ in front to produce:
@$dom->loadHTML($htmltable);
Figured I would share in case anyone else had the same issue. The error I got was within the spreadsheet stating:
<b>Warning</b>:  DOMDocument::loadHTML() [<a href='domdocument.loadhtml'>domdocument.loadhtml</a>]: htmlParseEntityRef: no name in Entity, line: 3 in <b>C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\rtms\rtms\excel_creator2.php</b> on line <b>45</b><br />
Thanks again for the code. 
Jun 2, 2012 at 10:44 PM

I have problem, then I call it by AJAX: it's post as utf8 encoded

problem was in domDocument->loadHTML wich doesn't work with UTF-8 and  Russian letters.

It's solved by change to ->loadXML()

 

Thanks for great code

Apr 2, 2013 at 8:22 AM
hello mamuscia,

Thank you for making the code very great,

I need help to create a rowspan,

This code is perfect, and the more perfect if it makes the code to generate rowspan.

Can you help me? please

thanks

Sorry for my bad english.
Apr 2, 2013 at 9:02 PM
Hello Tonbad,
it's been a while since I looked at this code. I don't have a lot of time to help right now given my workload, but let me try to outline something that will get you going.

If you look at the section where it's looping thru the TH's and TD's to find attributes that are set, such as COLSPAN,
then just duplicate it to look for ROWSPAN and save the value. Do it in both the TH and TD loops. For example:
// 
if($ths->item($x)->hasAttribute('rowspan')) {
  $headrows[$h]['rowspan'][] = $ths->item($x)->getAttribute('rowspan');
}else{
  $headrows[$h]['rowspan'][] = 1;
}
When it generates the Excel, you'll need to MERGE cells based on what you found for the ROWSPAN attribute.
You'll have to determine what the cell range is for the element you're getting from the TH or TD array, For example:
for($t=0;$t<count($th);$t++) {
      if($xcol == '') {$xcol = 'A';}else{$xcol++;}
      $thishdg = $th[$t];
      $thisalign = $aligns[$t];
      $thisvalign = $valigns[$t];
      $thiscolspan = $colspans[$t];
      $thisrowspan = $rowspans[$t];
      $thiscolor = $colors[$t];
      $thisbg = $bgcolors[$t];
      $thisbold = $bolds[$t];
      $strbold = ($thisbold==true) ? 'true' : 'false';
      if($thisbg == 'FFFFFF') {
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE;
      }else{
        $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
      }
      $style_overlay['alignment']['vertical'] = $thisvalign;              // set styles for cell
      $style_overlay['alignment']['horizontal'] = $thisalign;
      $style_overlay['font']['color']['rgb'] = $thiscolor;
      $style_overlay['font']['bold'] = $thisbold;
      $style_overlay['fill']['color']['rgb'] = $thisbg;
      if($thiscolspan > 1) {                       

....... rest of loop code not shown .......

$worksheet->mergeCells($cellRange); 
Agaun, sorry, but I cannot spend more time helping right now.
Apr 3, 2013 at 1:28 AM
Edited Apr 3, 2013 at 1:41 AM
Hello mamuscia,

Thanks for quick respond,
I will try to start modify your code

Actually i'm beginner in phpexcel,

can you give me extra commentary in your code,? Please

I think i will understand the meaning of the code much better

Thanks,

sorry my bad english :).
Apr 6, 2013 at 4:39 AM
Hello Mamuscia,

I can't Copy Code that you have updated.

do you have Insert Code function to post your code?

Thanks.
Aug 10, 2013 at 2:05 PM
Edited Aug 10, 2013 at 2:06 PM
Mamuscia, this script is awesome and working great! :)

The only disadvantage I have found so far is that it has problems with nested tables as it will consider each table as a separate one and put it in a separate worksheet. Any ideas of how to make it work with nested tables?
<table>
<thead>
<tr>
<th>test</th>
</tr>
</thead>
<tbody>
<tr>
<td>
<table>
<tr>
<td>val1</td>
</tr>
</table>
<table>
<tr>
<td>val2</td>
</tr>
</table>
</td>
</tr>
</tbody>
</table>
Coordinator
Aug 10, 2013 at 3:53 PM
On 10/08/2013 14:05, king999 wrote:

From: king999

Mamuscia, this script is awesome and working great! :)

The only disadvantage I have found so far is that it has problems with nested tables as it will consider each table as a separate one and put it in a separate worksheet. Any ideas of how to make it work with nested tables?
test
val1</test>
val2
I have, similar to handling merged cells, by maintaining a stack; and I plan to implement it at some point, but it does add a fair degree of complexity to the writer... I may find time to implement it as part of my version 2 rewrite

-- 
Mark Baker
Aug 10, 2013 at 5:24 PM
Mark, it would be a good idea to include it in version 2. Any ideas of a workaround until then with the current script?
Oct 11, 2013 at 4:14 PM
Mark,
it's been a while since I have posted. I am still using PHPExcel and it's fantastic. I see from above that you have included an HTML reader now. Is there an example in the latest download on how to use it? Does the HTML have to come from a file, or can it come from a variable as well?

Thanks.