CodeCharge Studio
search Register Login  

Visual PHP Web Development

Visually Create Internationalized Web Applications, Web Reports, Calendars, and more.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 beating a dead excel horse

Print topic Send  topic

Author Message
kirchaj

Posts: 215
Posted: 08/29/2013, 12:32 PM

I know there is a lot of topics on excel but I can't seem to find the exact answer I need. Export works fine except when you open the file (which appears to be html with the extension .xls) the users complain because they get the error

"The file you are trying to open, '[filename]', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" (Yes | No | Help)

I can't seem to find a clear answer. Does anyone have a solution?

TK
View profile  Send private message
ckroon

Posts: 868
Posted: 08/29/2013, 2:17 PM

Found this:

Quote :
One option is simply to rename the file name to .csv, and keep the user interface as saying that it is an Excel file (Excel is quite happy to read csv files).

_________________
Walter Kempees...you are dearly missed.
View profile  Send private message
MichaelMcDonald

Posts: 639
Posted: 08/29/2013, 8:03 PM

Not so sure using the excel export feature is worthwhile at all, I found that it doesn't recognise float $0.00 or 0.00 values at all, in fact excel won't even recognise them as text.

Better to use an SQL query to export as .csv function as custom code.
Search these forums, the code is here ... if I can remember where I last posted it ... I will provide the link...
_________________
Central Coast, NSW, Australia.

View profile  Send private message
MichaelMcDonald

Posts: 639
Posted: 08/29/2013, 8:14 PM

Here's the original code with due credits to the author...


http://forums.yessoftware.com/posts.php?post_id=109235&...rd=export+excel

I had to remove a line in the common.php function to get it to work, this is my version of it using a session variable to determine the MAX records:

function exportMysqlToCsv($sql,$filename = 'export.csv')
{
$maxrecordscsv = CCGetSession("maxrecordscsv",ccsInteger);


$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, $maxrecordscsv");
while ($db->next_record())
{
$col_array = NULL;
for($i = 0 ; $i < $count_col ; $i++)
{
$col_array[] = $db->f($cols[$i]);
}
$table .= @implode(',',$col_array)."\n";
}

header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($table));
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=$filename");


echo $table;
exit;

}


And here is some SQL query as custom code on-click event on a search form:


$siteid = CCGetFromGet("siteid");
$sitename = CCGetFromGet("sitename");

$ordertypeid = CCGetParam("s_ordertypeid");
if ($ordertypeid > 0)
$ordertypeid = CCGetParam("s_ordertypeid");
else
$ordertypeid = "%";

$supplierid = CCGetParam("s_supplierid");
if ($supplierid > 0)
$supplierid = CCGetParam("s_supplierid");
else
$supplierid = "%";

$accountcodeid = CCGetParam("s_accountcodeid");
if ($accountcodeid > 0)
$accountcodeid = CCGetParam("s_accountcodeid");
else
$accountcodeid = "%";

$duedate1 = CCGetParam("s_duedate1");
$duedate2 = CCGetParam("s_duedate2");
$duedate1 = strtotime($duedate1);
$duedate2 = strtotime($duedate2);
$duedate1= date("Y/m/d",$duedate1);
$duedate2= date("Y/m/d",$duedate2);
$added1 = CCGetParam("s_added1");
$added2 = CCGetParam("s_added2");
$completed1 = CCGetParam("s_completed1");
$completed2 = CCGetParam("s_completed2");


$constaxincex= CCGetSession("constaxincex");
$constaxcode = CCGetSession("constaxcode");
$constax= $constaxincex."-".$constaxcode;



if($completed1 != NULL and $added1 != NULL){


$added1 = strtotime($added1);
$added1= date("Y/m/d",$added1);
$added2 = strtotime($added2);
$added2= date("Y/m/d",$added2);

$completed1 = strtotime($completed1);
$completed1 = date("Y/m/d",$completed1);
$completed2 = strtotime($completed2);
$completed2 = date("Y/m/d",$completed2);

$db = new clsDBFM();



$sql = "SELECT sitename AS 'Site Name', idorder as 'Order ID', added AS 'Added', duedate AS 'Due Date',
inprogress AS 'In Progress', completed AS 'Completed', displayname AS 'Supplier', description AS 'Description',
accountcode AS 'Account Code', cost AS 'Cost ($constax)', invoicenumber AS 'Invoice Number', invoicecost AS 'Invoice Cost ($constax)',
ordertype AS 'Order Type'"
." FROM (((orders LEFT JOIN ordertype ON
orders.ordertypeid = ordertype.idordertype) LEFT JOIN supplier ON
orders.supplierid = supplier.idsupplier) LEFT JOIN accountcode ON
orders.accountcodeid = accountcode.idaccountcode) LEFT JOIN site ON orders.siteid = site.idsite"
." WHERE orders.siteid = '$siteid' AND ordertype.idordertype LIKE '$ordertypeid'
AND accountcode.idaccountcode LIKE '$accountcodeid'
AND orders.supplierid LIKE '$supplierid'
AND (duedate >= '$duedate1' AND duedate <= '$duedate2')
AND (added >= '$added1' AND added <= '$added2')
AND (completed1 >= '$completed1' AND completed1 <= '$completed2')
ORDER BY duedate desc";
$db->query($sql);

$db->close();

exportMysqlToCsv($sql,'Order_Report('.date(dMYHis).').csv');
}



_________________
Central Coast, NSW, Australia.

View profile  Send private message
buzz


Posts: 11
Posted: 09/09/2013, 10:33 AM

I use PHPExcel.php

Works well no error messages.

require_once '../PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "Status");
$objPHPExcel->getActiveSheet()->setCellValue('B1', "First Name");
$objPHPExcel->getActiveSheet()->setCellValue('C1', "Last Name");
$objPHPExcel->getActiveSheet()->setCellValue('D1', "Title");
$objPHPExcel->getActiveSheet()->setCellValue('E1', "Reference");
More code.....
$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);

query in here

$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, "$status")
->setCellValue('B' . $i, "$fname")
->setCellValue('C' . $i, "$lname")
->setCellValue('D' . $i, "$title")
->setCellValue('E' . $i, "$reference")
more columns here

$objPHPExcel->setActiveSheetIndex(0);
//echo date('H:i:s') , " Write to Excel2007 format" , "<br>";
$callStartTime = microtime(true);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$objWriter->save('ats_job_candidate_report.xlsx');
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;

$filename = 'XXXXX.xlsx';
$download_path = "/XXXX/";
$file = "/XXXX.xlsx";
$type = filetype($file);

$time = time();

// Send file headers
//header("Content-type: $type");
header('Content-Description: File Transfer');
//header('Content-Type: application/vnd.ms-excel');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header("Content-Disposition: attachment;filename=$filename");
header("Content-Transfer-Encoding: binary");
header('Pragma: no-cache');
header('Expires: 0');
// Send the file contents.
set_time_limit(0);
readfile($file);
$db->close();


View profile  Send private message
JessicaRobort

Posts:
Posted: 09/24/2013, 2:22 AM

codings <3
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.

Web Database

Join thousands of Web developers who build Web applications with minimal coding.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


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