Export from existing HTML Table?

Topics: Developer Forum, User Forum
Jan 12, 2010 at 7:29 PM

This looks like a fantastic tool! I've been through a couple of the test scripts but do not see the ability to directly output an HTML table to an Excel (XLS or XLSX) file. Am I correct in assuming that I will need to iterate through the table, extract data and formatting and create a PHPExcel object as I go? Do you have an example of such a script? How will various colspans be handled?

Jan 13, 2010 at 3:13 PM
Edited Jan 13, 2010 at 4:08 PM

I think I have this figured out using ByColumnAndRow and mergeCells. Will check back in if I need more assistance.  Thanks!


Developer
Jan 14, 2010 at 12:46 AM

We unfortunately don't have the ability to read HTML tables using PHPExcel. Perhaps we should start looking into creating PHPExcel_Reader_HTML. There have been a couple of requests.

Jan 15, 2010 at 2:23 AM

Ok, so here's my dilemna... I have figured out how to dynamically create my XLS spreadsheet using PHPExcel with the following code:

          $objML->setActiveSheetIndex(0);
          // set default spreadsheet layout options
          $objML->getDefaultStyle()->getFont()->setName('Calibri');
          $objML->getDefaultStyle()->getFont()->setSize(11);
          $objML->getActiveSheet()->getPageMargins()->setTop(1);
          $objML->getActiveSheet()->getHeaderFooter()->setOddHeader("&C&B&36MATERIAL LIST");
          $objLogo = new PHPExcel_Worksheet_HeaderFooterDrawing();
          $objLogo->setName('MASTEC logo');
          $objLogo->setPath('images/ml_logo.jpg');
          $objLogo->setHeight(76);
          $objML->getActiveSheet()->getHeaderFooter()->addImage($objLogo, PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_CENTER);

          // set the top of every page that repeats
          $objML->getActiveSheet()->setCellValue("A1","CUSTOMER ORDER NO:")
                                        ->setCellValue("E1",$row['teo'])
                                        ->setCellValue("F1","MASTEC ORD NO:")
                                        ->setCellValue("G1",$row['proj_num'])
                                        ->setCellValue("H1","DATE:")
                                        ->setCellValue("I1",$row['completed'])
                                        ->mergeCells("A1:D1")
                                        ->setCellValue("A2","CLLI:")
                                        ->setCellValue("E2",$row['clli'])
                                        ->setCellValue("F2","CITY, STATE:")
                                        ->setCellValue("G2","$citystate")
                                        ->setCellValue("H2","APPX:")
                                        ->setCellValue("I2",$row['appx'])
                                        ->mergeCells("A2:D2")
                                        ->setCellValue("A3","OP")
                                        ->setCellValue("B3","ITEM")
                                        ->setCellValue("C3","NOTE")
                                        ->setCellValue("D3","QTY")
                                        ->setCellValue("E3","MATERIAL ID")
                                        ->setCellValue("F3","MATERIAL DESCRIPTION")
                                        ->setCellValue("H3","NOTES");
          $objML->getActiveSheet()->getStyle('I2')->getNumberFormat()->setFormatCode("00");
          $objML->getActiveSheet()->getStyle('B4:B9999')->getNumberFormat()->setFormatCode("00");
          $objML->getActiveSheet()->getStyle("A1:H1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
          $objML->getActiveSheet()->getStyle("A2:H2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
          $objML->getActiveSheet()->getStyle("E1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
          $objML->getActiveSheet()->getStyle("G1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
          $objML->getActiveSheet()->getStyle("I1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
          $objML->getActiveSheet()->getStyle("E2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
          $objML->getActiveSheet()->getStyle("G2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
          $objML->getActiveSheet()->getStyle("I2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
          $objML->getActiveSheet()->getStyle("A3:I3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
          $objML->getActiveSheet()->getStyle("E3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
          $objML->getActiveSheet()->getStyle("A1:I3")->getFont()->setBold(true);
          $objML->getActiveSheet()->getStyle("A1:I22")->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objML->getActiveSheet()->getStyle("A3:I3")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
          $objML->getActiveSheet()->mergeCells("F3:G3");
          $objML->getActiveSheet()->mergeCells("H3:I3");
          $objML->getActiveSheet()->getColumnDimension("A")->setWidth(6);
          $objML->getActiveSheet()->getColumnDimension("B")->setWidth(6);
          $objML->getActiveSheet()->getColumnDimension("C")->setWidth(6);
          $objML->getActiveSheet()->getColumnDimension("D")->setWidth(6);
          $objML->getActiveSheet()->getColumnDimension("E")->setWidth(26);
          $objML->getActiveSheet()->getColumnDimension("F")->setWidth(17);
          $objML->getActiveSheet()->getColumnDimension("G")->setWidth(33);
          $objML->getActiveSheet()->getColumnDimension("H")->setWidth(6);
          $objML->getActiveSheet()->getColumnDimension("I")->setWidth(17);
          $objML->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1,3);
          // setup some default styling for the worksheet
          $objML->getActiveSheet()->setBreak("J3",PHPExcel_Worksheet::BREAK_COLUMN);
          $objML->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
          $objML->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
          $objML->getActiveSheet()->getPageSetup()->setFitToWidth(1);
          $objML->getActiveSheet()->getPageSetup()->setFitToHeight(0);
          $objML->getActiveSheet()->getSheetView()->setZoomScale(105);
          $i = 0;
          $page = 1;
          $rowMLSheet = 3;
          $total_cost = 0;
          $contents = "<table id=\"ml_table\" class=\"ml_table\">";
          $q = "select c.quantity as qty, c.notes, m.*, l.teo as teo, q.clli as clli, l.material_dollars as dollars, l.proj_num as proj_num, l.city as city, l.state as state,";
          $q .= " l.appx as appx, date_format(l.completed,'%m/%d/%Y') as completed from materials m JOIN ml_contents c ON c.contained_item = m.id";
          $q .= " JOIN material_lists l ON l.id = c.ml_id JOIN clli_info as q ON q.id = l.clli";
          $q .= " where c.ml_id = '".$ml_id."'";
          $r = $db->select($q);
          while ($row=$db->get_row($r,'MYSQL_ASSOC')) {
            $i++;
            $rowMLSheet++;
            if (($i == 1) || ($i % 19 == 0)) {
              if ($i !== 1) {
                mlFooterPg();
                $objML->getActiveSheet()->setCellValueByColumnAndRow(1,$rowMLSheet,"RESTRICED - PROPIETARY\nNot for use or disclosure outside of AT&T except under written agreement.");
                $objML->getActiveSheet()->setCellValueByColumnAndRow(8,$rowMLSheet,"PAGE: $page");
                $rowMLSheet++;
                $objML->getActiveSheet()->setBreak("I$rowMLSheet",PHPExcel_Worksheet::BREAK_ROW);
              }
              mlHeaderPg();
            }
            $contents .= "<tr>";
            $contents .= "<td class=\"op\">A</td>";
            $objML->getActiveSheet()->setCellValueByColumnAndRow(1,$rowMLSheet,"A");
            if ($i < 10) { $n = "0".$i; } else { $n = $i; }
            $contents .= "<td class=\"item\">".$n."</td>";
            $objML->getActiveSheet()->setCellValueByColumnAndRow(2,$rowMLSheet,$n);
            $contents .= "<td class=\"note\"> </td>";
            $objML->getActiveSheet()->setCellValueByColumnAndRow(3,$rowMLSheet,"   ");
            $contents .= "<td class=\"qty\">".$row['qty']."</td>";
            $objML->getActiveSheet()->setCellValueByColumnAndRow(4,$rowMLSheet,$row['qty']);
            $contents .= "<td class=\"material_id\">".$row['mastec_partno']."</td>";
            $objML->getActiveSheet()->setCellValueByColumnAndRow(5,$rowMLSheet,$row['mastec_partno']);
            $desc = trim(addslashes($row['description']));
            $contents .= "<td colspan=\"2\" class=\"material_desc\" colpsan=\"2\">".$desc."</td>";
            $objML->getActiveSheet()->setCellValueByColumnAndRow(6,$rowMLSheet,$desc)
                                    ->mergeCells("F".$rowMLSheet.":G".$rowMLSheet);
            $contents .= "<td colspan=\"2\" class=\"notes\" colspan=\"2\">".$row['notes']."</td>";
            $objML->getActiveSheet()->setCellValueByColumnAndRow(6,$rowMLSheet,$row['notes'])
                                    ->mergeCells("H".$rowMLSheet.":I".$rowMLSheet);
            $contents .= "</tr>";
            $total_cost += $row['cost'] * $row['qty'];
            $dollars = $row['dollars'];
            $teo = $row['teo'];
            $clli = $row['clli'];
            if ($row['appx'] == "0") {
              $appendix = "";
            } else {
              $appendix = $row['appx'];
            }
            $mlWindow = "ML".$appendix."_".$teo."_".$clli;
          }
          mlFooterPg();
          $contents .= "</table>";
          $p_l = $dollars - $total_cost;
          $objML->getProperties()->setCreator("Material List Generator")
							 ->setLastModifiedBy("Material List Generator")
							 ->setTitle($title)
							 ->setSubject($title)
							 ->setDescription("Material list for ".$teo)
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Material List");

 

But I am unable to pass this sheet to another PHP page to get it to print! $objML is defined as global. Here's the code I am using to pass it:

          echo "<form action=\"sendXLS.php\" method=\"get\" name=\"testexport\" id=\"testexport\">\n";
          echo "<input type=\"hidden\" name=\"title\" id=\"title\" value=\"".$mlWindow."\" />\n";
          echo "<input type=\"submit\" name=\"exportit\" id=\"exportit\" value=\"EXPORT TO EXCEL\" />\n";
          echo "</form>\n";

And here is the sendXLS.php script:

<?
require_once('Classes/PHPExcel.php');
require_once('Classes/PHPExcel/IOFactory.php');
error_reporting(E_ALL);
ini_set("display_errors", 1);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$title.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objML, 'Excel5');
$objWriter->save('php://output');
?>

I am really racking my brain on this one and could desperately use some assistance!!!!!!!

 

Coordinator
Jan 15, 2010 at 9:03 AM

Pass $objML as a session variable, or write the workbook to a temporary file and then pass the filename via your form, or only generate the workbook when sendXLS.php is called.

Jan 15, 2010 at 8:13 PM
Edited Jan 15, 2010 at 9:08 PM

Thanks, Mark. I decided that all the work could be done in the sendXLS.php script by only
passing a single variable to name the record of the ml (ml_id) that I am going to generate.
While the script itself seems to be working just fine (NO ERRORS, NOTICES or WARNINGS - and the
QUERY is perfect), I am getting complete GARBAGE on the resulting worksheet.
Here's my new script (sendXLS.php):

<?
require_once("db/db.class.php");
$db = new db_class;
require_once("Classes/PHPExcel.php");
require_once("Classes/PHPExcel/IOFactory.php");
error_reporting(E_ALL);
ini_set("display_errors", 1);
// create the new instance
$objML = new PHPExcel();
// and make it the active sheet
$objML->setActiveSheetIndex(0);
// set default spreadsheet layout options
$objML->getDefaultStyle()->getFont()->setName('Calibri');
$objML->getDefaultStyle()->getFont()->setSize(11);
$objML->getActiveSheet()->getPageMargins()->setTop(1);
$objML->getActiveSheet()->getHeaderFooter()->setOddHeader("&C&B&36MATERIAL LIST");
// $objLogo = new PHPExcel_Worksheet_HeaderFooterDrawing();
// $objLogo->setName('MASTEC logo');
// $objLogo->setPath('images/ml_logo.jpg');
// $objLogo->setHeight(76);
// $objML->getActiveSheet()->getHeaderFooter()->addImage($objLogo, PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_CENTER);
// set the top of every page that repeats
if (!$db->connect()) {
  $db->print_last_error(); }
else {
  $q = "select l.teo, l.proj_num, l.city, l.state, q.clli as clli,";
  $q .= " l.appx, date_format(l.completed,'%m/%d/%Y') AS completed FROM material_lists l";
  $q .= " JOIN clli_info q ON q.id = l.clli";
  $q .= " WHERE l.id = '".$ml_id."' LIMIT 1";
  // echo $q."<br />";
  $r = $db->select($q);
  while ($row = $db->get_row($r,'MYSQL_ASSOC')) {
    // print_r($row)."<br />";
    // exit;
    if ($row['appx'] == "0") {
      $appendix = "";
    } else {
      $appendix = $row['appx'];
    }
    $citystate = strtoupper($row['city']).", ".strtoupper($row['state']);
    $teo = $row['teo'];
    $projno = $row['proj_num'];
    $completed = $row['completed'];
    $clli = $row['clli'];
    $appx = $row['appx'];
    $mlName = "ML".$appendix."_".$teo."_".$clli;
    $objML->getActiveSheet()->setCellValue("A1","CUSTOMER ORDER NO:")
                                  ->setCellValue("E1","$teo")
                                  ->setCellValue("F1","MASTEC ORD NO:")
                                  ->setCellValue("G1","$projno")
                                  ->setCellValue("H1","DATE:")
                                  ->setCellValue("I1","$completed")
                                  ->mergeCells("A1:D1")
                                  ->setCellValue("A2","CLLI:")
                                  ->setCellValue("E2","$clli")
                                  ->setCellValue("F2","CITY, STATE:")
                                  ->setCellValue("G2","$citystate")
                                  ->setCellValue("H2","APPX:")
                                  ->setCellValue("I2","$appx")
                                  ->mergeCells("A2:D2")
                                  ->setCellValue("A3","OP")
                                  ->setCellValue("B3","ITEM")
                                  ->setCellValue("C3","NOTE")
                                  ->setCellValue("D3","QTY")
                                  ->setCellValue("E3","MATERIAL ID")
                                  ->setCellValue("F3","MATERIAL DESCRIPTION")
                                  ->setCellValue("H3","NOTES");
  }
  $objML->getActiveSheet()->getStyle('I2')->getNumberFormat()->setFormatCode("00");
  $objML->getActiveSheet()->getStyle("A1:H1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objML->getActiveSheet()->getStyle("A2:H2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objML->getActiveSheet()->getStyle("E1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objML->getActiveSheet()->getStyle("G1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objML->getActiveSheet()->getStyle("I1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objML->getActiveSheet()->getStyle("E2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objML->getActiveSheet()->getStyle("G2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objML->getActiveSheet()->getStyle("I2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objML->getActiveSheet()->getStyle("A3:I3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objML->getActiveSheet()->getStyle("E3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objML->getActiveSheet()->getStyle("A1:I3")->getFont()->setBold(true);
  $objML->getActiveSheet()->getStyle("A1:I22")->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objML->getActiveSheet()->getStyle("A3:I3")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
  $objML->getActiveSheet()->mergeCells("F3:G3");
  $objML->getActiveSheet()->mergeCells("H3:I3");
  $objML->getActiveSheet()->getColumnDimension("A")->setWidth(6);
  $objML->getActiveSheet()->getColumnDimension("B")->setWidth(6);
  $objML->getActiveSheet()->getColumnDimension("C")->setWidth(6);
  $objML->getActiveSheet()->getColumnDimension("D")->setWidth(6);
  $objML->getActiveSheet()->getColumnDimension("E")->setWidth(26);
  $objML->getActiveSheet()->getColumnDimension("F")->setWidth(17);
  $objML->getActiveSheet()->getColumnDimension("G")->setWidth(33);
  $objML->getActiveSheet()->getColumnDimension("H")->setWidth(6);
  $objML->getActiveSheet()->getColumnDimension("I")->setWidth(17);
  $objML->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1,3);
  // setup some default styling for the worksheet
  $objML->getActiveSheet()->setBreak("J3",PHPExcel_Worksheet::BREAK_COLUMN);
  $objML->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
  $objML->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
  $objML->getActiveSheet()->getPageSetup()->setFitToWidth(1);
  $objML->getActiveSheet()->getPageSetup()->setFitToHeight(0);
  $objML->getActiveSheet()->getSheetView()->setZoomScale(105);
}
// creating the contents of the sheet
if (!$db->connect()) {
  $db->print_last_error(); }
else {
  $i = 0;
  $page = 1;
  $rowMLSheet = 3;
  $total_cost = 0;
  $q = "select c.quantity as qty, c.notes, m.*, l.teo as teo, q.clli as clli, l.material_dollars as dollars, l.proj_num as proj_num, l.city as city, l.state as state,";
  $q .= " l.appx as appx, date_format(l.completed,'%m/%d/%Y') as completed from materials m JOIN ml_contents c ON c.contained_item = m.id";
  $q .= " JOIN material_lists l ON l.id = c.ml_id JOIN clli_info as q ON q.id = l.clli";
  $q .= " where c.ml_id = '".$ml_id."'";
  $r = $db->select($q);
  while ($row=$db->get_row($r,'MYSQL_ASSOC')) {
    $i++;
    $rowMLSheet++;
    if ($i % 19 == 0) {
      $objML->getActiveSheet()->setCellValueByColumnAndRow(1,$rowMLSheet,"RESTRICED - PROPIETARY\nNot for use or disclosure outside of AT&T except under written agreement.");
      $objML->getActiveSheet()->setCellValueByColumnAndRow(8,$rowMLSheet,"PAGE: $page");
      $objML->getActiveSheet()->getRowDimension($rowMLSheet)->setRowHeight(32);
      $rowMLSheet++;
      $temp = "J".$rowMLSheet;
      $objML->getActiveSheet()->setBreak("$temp",PHPExcel_Worksheet::BREAK_ROW);
    }
    $objML->getActiveSheet()->setCellValueByColumnAndRow(1,$rowMLSheet,"A");
    $objML->getActiveSheet()->setCellValueByColumnAndRow(2,$rowMLSheet,"$i");
    $objML->getActiveSheet()->setCellValueByColumnAndRow(3,$rowMLSheet,"   ");
    $qty = $row['qty'];
    $objML->getActiveSheet()->setCellValueByColumnAndRow(4,$rowMLSheet,"$qty");
    $partno = $row['mastec_partno'];
    $objML->getActiveSheet()->setCellValueByColumnAndRow(5,$rowMLSheet,"$partno");
    $desc = trim(stripslashes($row['description']));
    $temp = "F".$rowMLSheet.":G".$rowMLSheet;
    $objML->getActiveSheet()->setCellValueByColumnAndRow(6,$rowMLSheet,"$desc")
                            ->mergeCells("$temp");
    $temp = "H".$rowMLSheet.":I".$rowMLSheet;
    $notes = $row['notes'];
    $objML->getActiveSheet()->setCellValueByColumnAndRow(6,$rowMLSheet,"$notes")
                            ->mergeCells("$temp");
  }
}
$temp = "B4:B".$i;
$objML->getActiveSheet()->getStyle($temp)->getNumberFormat()->setFormatCode("00");
// set some default sheet properties
$objML->getProperties()->setCreator("Material List Generator")
		 ->setLastModifiedBy("Material List Generator")
		 ->setTitle("Material List")
		 ->setSubject($mlName)
		 ->setDescription("Material list for ".$teo)
		 ->setKeywords("office 2007 openxml php")
		 ->setCategory("Material List");
// print_r($objML);
header('Content-Type: application/vnd.ms-excel');
// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$mlName.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objML, 'Excel5');
// $objWriter = PHPExcel_IOFactory::createWriter($objML, 'Excel2007');
$objWriter->save('php://output');
?>

I get an error that the resulting file is "in a different format than specified by the file extension"
and the resulting sheet is utter GARBAGE!

Developer
Jan 17, 2010 at 5:29 AM
DevlshOne wrote:

I get an error that the resulting file is "in a different format than specified by the file extension"
and the resulting sheet is utter GARBAGE!

In the file you uploaded: ML_78899000_PACEFLPV.xls

I find some whitespace at the top: a space and a newline, see screenshot:

Those stray characters should not be there. This is the reason why the file opens as corrupt in Excel. Try to delete them and it will open fine.

The stray characters come from somewhere in your script. Probably before your opening <?php or after your closing ?> in some included script. Remove them and the error should go away.

Developer
Jan 17, 2010 at 10:04 AM
DevlshOne wrote:
is related to "Export from existing HTML Table?" (http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=80599) I'm sorry, but I don't see any place where whitespace or a newline marker could possible exist in this script...

There is no whitespace problem in your sendXLS.php script. So it must be in "db/db.class.php".

Look for whitespace in that script before <?php or after ?>. If that script includes other scripts, also look there.

Jan 17, 2010 at 10:13 PM

You are a genius. There was a single blank line at the end of the db.class.php script. It's now working perfectly!!! I would still love to see "PHPExcel_Reader_HTML". Thanks again for your kind attention and assistance. I'll be sending in a donation shortly.

Jan 18, 2010 at 6:18 PM

One more question... how do I get an "ALT-ENTER" to be part of the text in one of my cell values?

 

Coordinator
Jan 18, 2010 at 9:29 PM
DevlshOne wrote:

One more question... how do I get an "ALT-ENTER" to be part of the text in one of my cell values?

 Try a "\n"

Jan 19, 2010 at 12:49 AM

Nope.. just that gives me a bogus character in the middle of text.

Developer
Jan 19, 2010 at 7:28 AM
DevlshOne wrote:

Nope.. just that gives me a bogus character in the middle of text.

Your question is a common one so I have updated the "PHPExcel developer documentation" for next release.

Also adding feature in AdvancedValueBinder.php to facilitate easier insertion of ALT+"Enter".

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=11550

Added this in the documentation:

Write a newline character "\n" in a cell like ALT+"Enter" in Excel

In Microsoft Office Excel you get a line break in a cell when you use ALT+"Enter". When you do this it automatically turns on "wrap text" for the cell.

Here is how an example to achieve this in PHPExcel:
$objPHPExcel->getActiveSheet()->getCell('A1')->setValue("hello\nworld");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);

Read more about formatting cells using getStyle() elsewhere.

If you use AdvancedValuebinder.php it will automatically turn on "wrap text" for the cell.

require_once 'PHPExcel/Cell/AdvancedValueBinder.php';
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

$objPHPExcel->getActiveSheet()->getCell('A1')->setValue("hello\nworld");

Read more about AdvancedValueBinder.php elsewhere.

Mar 16, 2010 at 9:08 AM

Hi,

I am new to all kinds of programming. So excuse me if you find any thing too basic.

I have a drop-down menu for making queries. The selection from this pafe are sent forr processing into another page (post.php). If I add the following code taken from 01simple-download-xls.php (in tests folder of PHPExcel) in post.php then a dialogue appears asking me to save the Excel file instead of displaying the results in Firefox. The Excel file is fine except some garbage at the end.

error_reporting(E_ALL);
require_once("C:\wamp\www\Classes\PHPExcel.php");
require_once("C:\wamp\www\Classes\PHPExcel\IOFactory.php");
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                             ->setLastModifiedBy("Maarten Balliauw")
                             ->setTitle("Office 2007 XLSX Test Document")
                             ->setSubject("Office 2007 XLSX Test Document")
                             ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

Now I want to just add a button below the MySQL results for exporting the results to Excel. I added the following code in post.php:

<form action="post2.php" method="post"><br>
<button type="submit">Export to Excel</button></form></div><br>

Moreover, I pasted the PHPExcel code (mentioned previously) into post2.php. This leads to a blank Excel file as I dont know how to pass the results to post2.php.

Any help is welcome.

 

 

Developer
Mar 16, 2010 at 10:53 AM

@kashif79:

Avoid sending all the MySQL data to post2.php. Instead do something like this:

 

/* post2.php */
<?php
error_reporting(E_ALL);
require_once("C:\wamp\www\Classes\PHPExcel.php");
require_once("C:\wamp\www\Classes\PHPExcel\IOFactory.php");
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                             ->setLastModifiedBy("Maarten Balliauw")
                             ->setTitle("Office 2007 XLSX Test Document")
                             ->setSubject("Office 2007 XLSX Test Document")
                             ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");

$worksheet = $objPHPExcel->getActiveSheet();

// fetch the relevant data from database
mysql_connect(bla bla...);
...
$result = mysql_query("SELECT bla bla...")
;
$i = 1;
while ($row = mysql_fetch_array($result)) {
    $worksheet->getCell("A$i")->setValue($row[0]);
    $worksheet->getCell("B$i")->setValue($row[1]);
    $i++;
}

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>


You need to be fill out the details above, but it will maybe get you on the track.

 

Mar 16, 2010 at 12:16 PM

Dear Koyama,

Thank you for your reply.

You recommend adding the query with PHPExcel code.  My query goes like this:

$name = $_POST['indicator'];
$str_name = '';
for( $i = 0; $i<count($name);$i++)
{
 $str_name .= $name[$i].",";
}
$query= "SELECT  ".substr($str_name,0,strlen($str_name)-1)." FROM data";
$result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());

However, using your code by adding this query in the post2.php results in an errror in the following command:

$name = $_POST['indicator'];

The 'indicator' is what is sent by dropdown.php to post.php. An error occurs, I think, because now I am pressing a submit button to get the data displayed in Excel. I think $_POST needs to be adjusted now.  In your code $i = 1 is used while in my part of the code $i = 0 is used. Is this problematic too?

Your code works fine if I include it in the post.php (the file which processes the items selected by the user in dropdown.php) though it does not show the headers or column names.

Developer
Mar 17, 2010 at 5:55 AM

@kashif79: It looks like you are on the right track sending just the value of 'indicator'.

1) Yes, $i = 1 is correct if data should start at the first row in the Excel file.

2) To get the headers / column names, modify like this:

 

$worksheet->getCell("A1")->setValue('some header for column A');
$worksheet->getCell("B1")->setValue('some header for column B');

$i = 2;
while ($row = mysql_fetch_array($result)) {
    $worksheet->getCell("A$i")->setValue($row[0]);
    $worksheet->getCell("B$i")->setValue($row[1]);
    $i++;
}


 

Mar 17, 2010 at 6:12 PM

Dear Koyama,

Thank you for your continued help.

I did not check the code provided by you earlier carefully. Using it results only one field from database to be shown in Excel even if I made multiple selections in the drop-down menu. Moreover, your new code, unfortunately did not help, as headers are hard-coded here. I got the solution for it from an example on net (http://www.weberdev.com/get_example-4249.html) after the query. Now multiple selections in drop-down menu were being shown in the Excel file and column names were there too but still there was some garbage at the end of the file. After some trial and error I found that removing the follwing line from your code produced neat results:

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

though I don't understand why it is so. Nevertheless, one part of the problem is solved, thanks to your input. 

All the above engineering was done in post.php instead of post2.php. However that results in a dialogue box asking for saving the Excel file instead of displaying the results. I want to display the results in a html page first and then the user would have option to export the data to Excel. So my post.php page would be:

<?php

// database connection details

...


//Selection from drop-down menu

$name = $_POST['indicator'];
$str_name = '';
for( $i = 0; $i<count($name);$i++)
{
 $str_name .= $name[$i].",";
}
// Query

$query= "SELECT  Year,".substr($str_name,0,strlen($str_name)-1)." FROM data";

$result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());

//following code is from http://www.weberdev.com/get_example-4249.html
if (($result)||(mysql_errno == 0))
{
  echo "<table width='100%'><tr>";
  if (mysql_num_rows($result)>0)
  {
          //loop thru the field names to print the correct headers
          $i = 0;
          while ($i < mysql_num_fields($result))
          {
       echo "<th>". mysql_field_name($result, $i) . "</th>";
       $i++;
    }
    echo "</tr>";
   
    //display the data
    while ($rows = mysql_fetch_array($result,MYSQL_ASSOC))
    {
      echo "<tr>";
      foreach ($rows as $data)
      {
        echo "<td align='center'>". $data . "</td>";
      }
    }
  }else{
    echo "<tr><td colspan='" . ($i+1) . "'>No Results found!</td></tr>";
  }
  echo "</table>";
}
else{
  echo "Error in running query :". mysql_error();

}

?>
<form action="post2.php" method="post">
<button type="submit">Export to Excel</button></div><br>

Your code for exporting to Excel would be there in post2.php alongside whole of post.php code but I don't know how to pass selection form the drop-down menu again (without making the selection again). Using this button code would not pass the value of 'indicator' . Please help with the code.

 

Developer
Mar 19, 2010 at 1:10 AM
kashif79 wrote:

Your code for exporting to Excel would be there in post2.php alongside whole of post.php code but I don't know how to pass selection form the drop-down menu again (without making the selection again). Using this button code would not pass the value of 'indicator' . Please help with the code.

 

How about passing it as a hidden field:

 

<form action="post2.php" method="post">
<input type="hidden" name="indicator" value="<?php echo $_POST['indicator']; ?>">
<button type="submit">Export to Excel</button>
</form>