Databases
StackOverflow2013
Postsdbo
POTake HTML Output and insert it Excel Document via PHPExcel?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTake HTML Output and insert it Excel Document via PHPExcel?
    primarykey
    data
    text
    <p>I already have my desired data retrieved from the database and output it via the browser for web use but now I am trying to insert it in to an excel doc and email it.</p> <p>I have the beginnings of the document and am able to email it as an attachment fine, it's just how to transpose the html output into the excel document that I am stuck at as I can't think of the best way to do it.</p> <p>This is how I achieve my HTML output (apologies for the length):</p> <pre><code>// print table echo '&lt;table&gt;'; echo '&lt;tr&gt;&lt;th rowspan="2"&gt;Day&lt;/th&gt;'; foreach($typesorder as $type) { if(in_array($type, $types)) { echo '&lt;th colspan="3"&gt;' . $type . '&lt;/th&gt;'; } } echo '&lt;th colspan="3"&gt;Total Conversions&lt;/th&gt;'; echo '&lt;/tr&gt;'; echo '&lt;tr&gt;'; foreach($typesorder as $type) { if(in_array($type, $types)) { echo '&lt;th&gt;Week ' . $weekstart_A_data['week'] . ' ' . $weekstart_A_data['year'] . '&lt;/th&gt;'; echo '&lt;th&gt;Week ' . $weekstart_B_data['week'] . ' ' . $weekstart_B_data['year'] . '&lt;/th&gt;'; echo '&lt;th&gt;+/-&lt;/th&gt;'; } } // Total Conversions section echo '&lt;th&gt;Week ' . $weekstart_A_data['week'] . ' ' . $weekstart_A_data['year'] . '&lt;/th&gt;'; echo '&lt;th&gt;Week ' . $weekstart_B_data['week'] . ' ' . $weekstart_B_data['year'] . '&lt;/th&gt;'; echo '&lt;th&gt;+/-&lt;/th&gt;'; echo '&lt;/tr&gt;'; foreach($dailytotals as $thedate =&gt; $data) { $daily_conversions = 0; $daily_conversionsB = 0; echo '&lt;tr&gt;'; echo '&lt;td&gt;' . date('l', strtotime($thedate)) . '&lt;/td&gt;'; foreach($typesorder as $type) { if(in_array($type, $types)) { $conversions = $data[$type]; $total_conversions[$type] += $conversions; $daily_conversions += $conversions; $week_A_conversions = $conversions; $week_B_conversions = $dailytotalsB[$weekstartB][$type]; $total_conversionsB[$type] += $dailytotalsB[$weekstartB][$type]; $daily_conversionsB += $week_B_conversions; $differential = $dailytotalsB[$weekstartB][$type] - $conversions; echo '&lt;td&gt;'. number_format($week_A_conversions) . '&lt;/td&gt;'; echo '&lt;td&gt;'. number_format($week_B_conversions) . '&lt;/td&gt;'; if($differential &lt; 0 ) { $class = "class='diffred'"; } else if($differential &gt; 0) { $class = "class='diffblue'"; } else { $class=''; } // differential between Week A and Week B echo '&lt;td ' . $class . '&gt;' . $differential . '&lt;/td&gt;'; } } $weekstartB = date("Y-m-d", strtotime('+1 day', strtotime($weekstartB))); $differentialtotal = $daily_conversionsB - $daily_conversions; echo '&lt;td&gt;' . number_format($daily_conversions) . '&lt;/td&gt;'; echo '&lt;td&gt;' . number_format($daily_conversionsB) . '&lt;/td&gt;'; if($differentialtotal &lt; 0 ) { $class = "class='diffred'"; } else if($differentialtotal &gt; 0) { $class = "class='diffblue'"; } else { $class=''; } echo '&lt;td ' . $class . '&gt;' . $differentialtotal . '&lt;/td&gt;'; echo '&lt;/tr&gt;'; } echo '&lt;tr&gt;'; echo '&lt;td&gt;&lt;strong&gt;Total&lt;/strong&gt;&lt;/td&gt;'; // reset both week A and B $overall_conversions = 0; $overall_conversionsB = 0; foreach($typesorder as $type) { if(in_array($type, $types)) { $conversions = $total_conversions[$type]; $overall_conversions += $conversions; $conversionsB = $total_conversionsB[$type]; $overall_conversionsB += $conversionsB; echo '&lt;th&gt;' . number_format($conversions) . '&lt;/th&gt;'; echo '&lt;th&gt;' . number_format($conversionsB) . '&lt;/th&gt;'; echo '&lt;th&gt;' . number_format($conversionsB - $conversions) . '&lt;/th&gt;'; } } echo '&lt;th&gt;' . number_format($overall_conversions) . '&lt;/th&gt;'; echo '&lt;th&gt;' . number_format($overall_conversionsB) . '&lt;/th&gt;'; echo '&lt;th&gt;' . number_format($overall_conversionsB - $overall_conversions) . '&lt;/th&gt;'; echo '&lt;/tr&gt;'; echo '&lt;/table&gt;'; </code></pre> <p>This outputs a table with the following structure:</p> <pre><code>---------------------------------------------------------------------------------- | | Type 1 | Type 2 | ... | Total Conversions | | Day -------------------------------------------------------------------------| | | Week 1 2013 | Week 1 2012 | ... | ... | ... | ... | ... | |--------------------------------------------------------------------------------| |Sunday | 135 | 143 | ... | ... | ... | ... | ... | |--------------------------------------------------------------------------------| | ... | ... | ... | ... | ... | ... | ... | ... | |--------------------------------------------------------------------------------| |Total | ... | ... | ... | ... | ... | ... | ... | ---------------------------------------------------------------------------------- </code></pre> <p>Hopefully that makes some sense, the <code>...</code> are just placeholders for repeating data.</p> <p>I don't need to do any formulas since I already have all the data I need but I wouldn't rule it out as it might be easier to total using PHPExcel.</p> <p>I know this is a horrible question but I am genuinely stumped how to start off. I'm not expecting the full, exact answer to my specific scenario (although that would be magical) but in reality any pointers would help.</p> <p>P.S. I know how to insert data into the Excel document using PHPExcel but it's transposing my table that's the problem. I think the first step is to add all the data to a multi-dimensional array instead of printing it but I will see what the responses are first.</p> <p>Also, please note that I don't want to output as CSV as I wish to automatically email the pre-prepared and formatted Excel sheet.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmartincarlin87
    1. USmartincarlin87
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COHave a look at http://stackoverflow.com/questions/8082523/export-records-in-excel-file accepted answer... may point you in the right direction
      singulars
      1. POTake HTML Output and insert it Excel Document via PHPExcel?
      1. USJason Fingar
    2. COthanks but I don't want to output as csv, just to email the formatted excel sheet automatically without any work needing done to it, I meant to put that in my question as I think a few people will suggest csv.
      singulars
      1. POTake HTML Output and insert it Excel Document via PHPExcel?
      1. USmartincarlin87
    3. COHave you tried saving your HTML output to file, then using PHPExcel's HTML Reader? I know the HTML Reader is pretty basic at the moment, and needs more work to make it useful, but it may be an option.... otherwise, wait till I've improved the HTML REader enough, or use the standard PHPExcel cell setting rather than your HTML output
      singulars
      1. POTake HTML Output and insert it Excel Document via PHPExcel?
      1. USMark Baker
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload