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
|
 |
 |
ckroon
Posts: 869
|
| 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. |
|
|
|
MichaelMcDonald
Posts: 640
|
| 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.
|
|
|
|
MichaelMcDonald
Posts: 640
|
| 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.
|
|
|
|
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();
|
|
|
|
JessicaRobort
Posts:
|
| Posted: 09/24/2013, 2:22 AM |
|
codings <3
|
|
|
|
|
