CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> General/Other

 Excel export no longer works?

Print topic Send  topic

Author Message
Waspman

Posts: 946
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
View profile  Send private message
MichaelMcDonald

Posts: 638
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.

View profile  Send private message
Waspman
Posted: 05/16/2016, 11:15 PM


_________________
http://www.waspmedia.co.uk
---------------------------------------
Sent from YesSoftware forum
http://forums.yessoftware.com/
Waspman

Posts: 946
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
View profile  Send private message
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: 946
Posted: 05/17/2016, 9:01 AM

they understand DD, but they wont pay
_________________
http://www.waspmedia.co.uk
View profile  Send private message
MichaelMcDonald

Posts: 638
Posted: 05/20/2016, 1:22 AM

What is the implementation to supersede header()?

A jquery export, perhaps ...
_________________
Central Coast, NSW, Australia.

View profile  Send private message
MichaelMcDonald

Posts: 638
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.

View profile  Send private message
MichaelMcDonald

Posts: 638
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.

View profile  Send private message
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
View profile  Send private message
Waspman

Posts: 946
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
View profile  Send private message
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
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.