Waspman
Posts: 948
|
| Posted: 05/16/2016, 1:56 PM |
|
This no longer works...
Header("Content-type: application/vnd.ms-excel");
Header("Content-Disposition: inline; filename=\"Filename.xls\"");
Even the CCS example doesn't work any ideas?
_________________
http://www.waspmedia.co.uk |
 |
 |
MichaelMcDonald
Posts: 640
|
| Posted: 05/16/2016, 4:47 PM |
|
(this is a variation on someone else's posted solution I can' recall who but it's somewhere in Tips & Solutions)
In an includes page:
function exportMysqlToXLS($sql,$filename = 'export.xls')
{
$db=new clsDBfm();
$sql_exec = $db->query($sql.' LIMIT 0, 1');
$cols = @array_keys(@mysql_fetch_assoc($sql_exec));
$table = @implode(',',$cols)."\n";
$count_col = count($cols);
$sql_exec = $db->query($sql. " LIMIT 0, 10000000");
while ($db->next_record())
{
$col_array = NULL;
for($i = 0 ; $i < $count_col ; $i++)
{
$col_array[] = $db->f($cols[$i]);
}
$table .= @implode("\t",$col_array)."\n";
}
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($table));
header("Content-type: text/x-xls");
header("Content-Disposition: attachment; filename=$filename");
echo $table;
exit;
}
and your query:
if($searchVariables->cb_csv1->GetValue() == 1){
$approved = $searchVariables->s16->GetValue();
if ($approved == "Y"){
$approved = "Y";
}
if ($approved == "N"){
$approved = "N";
}
if($approved == NULL){
$approved = "%";
}
$siteid = CCGetSession("siteid");
$ordertypeid = $searchVariables->s13->GetValue();
if ($ordertypeid > 0)
$ordertypeid = $ordertypeid;
else
$ordertypeid = "%";
$supplierid = $searchVariables->s14->GetValue();
if ($supplierid > 0)
$supplierid = $supplierid;
else
$supplierid = "%";
$s_duedate1 = CCFormatDate(CCParseDate($searchVariables->s_duedate1->GetFormattedValue(),array("dd", "-", "mmm", "-", "yyyy", " ", "HH", ":", "nn")), array("yyyy", "-", "mm", "-", "dd", " ", "HH", ":", "nn", ":", "ss"));
$s_duedate2 = CCFormatDate(CCParseDate($searchVariables->s_duedate2->GetFormattedValue(),array("dd", "-", "mmm", "-", "yyyy", " ", "HH", ":", "nn")), array("yyyy", "-", "mm", "-", "dd", " ", "HH", ":", "nn", ":", "ss"));
$s_completed1 = CCFormatDate(CCParseDate($searchVariables->s_completed1->GetFormattedValue(),array("dd", "-", "mmm", "-", "yyyy", " ", "HH", ":", "nn")), array("yyyy", "-", "mm", "-", "dd", " ", "HH", ":", "nn", ":", "ss"));
$s_completed2 = CCFormatDate(CCParseDate($searchVariables->s_completed2->GetFormattedValue(),array("dd", "-", "mmm", "-", "yyyy", " ", "HH", ":", "nn")), array("yyyy", "-", "mm", "-", "dd", " ", "HH", ":", "nn", ":", "ss"));
if($s_completed1 == NULL) {
$db = new clsDBfm();
$sql = "SELECT outputorderid AS 'Order ID', alternateid AS 'Alternate ID', idorderlineitems AS 'Line Item ID', sitecontainername AS 'Portfolio', sitename AS 'Site Name',
supplier AS 'Supplier', accountcode AS 'Cost Centre', amount AS 'Budget $', taxcode AS 'Tax Code', taxincex AS 'Tax inc/ex', invoicenumber AS 'Invoice No.', received as 'Invoice Amount',
approved AS 'Approved', remittanceid AS 'Remittance ID', duedate AS 'Due Date', inprogress AS 'In Progress', completed AS 'Completed' FROM orderlineitems WHERE siteid = '$siteid' and (duedate >= '$s_duedate1' AND duedate <= '$s_duedate2')
and supplierid LIKE '$supplierid' and ordertypeid LIKE '$ordertypeid' and approved LIKE '$approved' and amount > 0 ORDER BY duedate desc";
$db->query($sql);
$db->close();
exportMysqlToXLS($sql,'Order_Report('.date(dMYHis).').xls');
}
if($s_completed1 != NULL) {
$db = new clsDBfm();
$sql = "SELECT outputorderid AS 'Order ID', alternateid AS 'Alternate ID', idorderlineitems AS 'Line Item ID', sitecontainername AS 'Portfolio', sitename AS 'Site Name',
supplier AS 'Supplier', accountcode AS 'Cost Centre', amount AS 'Budget $', taxcode AS 'Tax Code', taxincex AS 'Tax inc/ex', invoicenumber AS 'Invoice No.', received as 'Invoice Amount',
approved AS 'Approved', remittanceid AS 'Remittance ID', duedate AS 'Due Date', inprogress AS 'In Progress', completed AS 'Completed' FROM orderlineitems WHERE siteid = '$siteid' and (duedate >= '$s_duedate1' AND duedate <= '$s_duedate2')
and (completed >= '$s_completed1' AND completed <= '$s_completed2')
and supplierid LIKE '$supplierid' and ordertypeid LIKE '$ordertypeid' and approved LIKE '$approved' and amount > 0 ORDER BY duedate desc";
$db->query($sql);
$db->close();
exportMysqlToXLS($sql,'Order_Report('.date(dMYHis).').xls');
}
}
_________________
Central Coast, NSW, Australia.
|
|
|
|
Waspman
|
| Posted: 05/16/2016, 11:15 PM |
|
_________________
http://www.waspmedia.co.uk
---------------------------------------
Sent from YesSoftware forum
http://forums.yessoftware.com/
|
|
|
|
Waspman
Posts: 948
|
| Posted: 05/17/2016, 12:03 AM |
|
Thanks Michael,
It's a bit too much work to convert the complicated report I have to do it this. I was hoping there was a rason why the original way no longer worked.
I'll change it if I have to, but the client is relictant to as it always worked inthe past.
I'll keep looking and let you know if I find a solution.
Thanks and it's great to see someone still on here.
I use CCS (v4) to drive all my websites, mobile and web app and lots of business systems can't imagine what I'd do without it.
:)
_________________
http://www.waspmedia.co.uk |
|
|
|
DataDoIT
|
| Posted: 05/17/2016, 7:17 AM |
|
The first thing that jumps out to me is the capital H in Header(). On a
Winblows host it wouldn't matter but on a Linux host it certainly would.
Secondly, the web has evolved tremendously over the last 10 years, so
header() calls will break as browsers are updated and methods are
sunsetted. Your customers should understand that.
|
|
|
|
Waspman
Posts: 948
|
| Posted: 05/17/2016, 9:01 AM |
|
they understand DD, but they wont pay
_________________
http://www.waspmedia.co.uk |
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 05/20/2016, 1:22 AM |
|
What is the implementation to supersede header()?
A jquery export, perhaps ...
_________________
Central Coast, NSW, Australia.
|
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 05/20/2016, 1:23 AM |
|
I just found this ...
http://www.jqueryscript.net/table/Export-Html-Table-To-...able2excel.html
_________________
Central Coast, NSW, Australia.
|
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 05/20/2016, 1:24 AM |
|
ooooohhhh this looks good....
http://www.jquerybyexample.net/2012/10/export-table-dat...ing-jquery.html
_________________
Central Coast, NSW, Australia.
|
|
|
|
eratech
Posts: 513
|
| Posted: 05/20/2016, 2:36 AM |
|
The jquery export is working well on one of my projects. I had previously used server-side and changed the headers.
Something to add for Exporting - the special Microsoft Office formatting when exporting as the HTML gets converted by Excel.
http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting
eg:
<td style="mso-number-format:"mm\/dd\/yyyy">10/01/2011</td>
And the ever-useful text formatting for numbers with leading zeros that would get stripped out:
<td style="mso-number-format:"\@">0002011</td>
They can be used in CSS too, of course.
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
Waspman
Posts: 948
|
| Posted: 05/20/2016, 2:38 AM |
|
haha, I'm on the same page, great minds and all that. Thanks for looking into this for me I really appreciate it:)
Tony
_________________
http://www.waspmedia.co.uk |
|
|
|
vron12
Posts: 3
|
| Posted: 09/04/2016, 11:00 PM |
|
I'll change it if I have to, but the client is relictant to as it always worked inthe past.
I'll keep looking and let you know if I find a solution.
-----------------------------
http://dewabet.com
_________________
The best game sbobet online in asia , so join us |
|
|
|
|
