Excel Data Crash.

Mar 14, 2011 at 2:51 AM


Hello,

I'm using php, mysql, and Excel2007

I export a data from php to Excel sheet. If the record count  is less than 30, data export perfectly. If more then 30 records, Excel crash.

Error message "Excel cannot open the file 'chart report count.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of file"

Here is my full code for exporting data from db[MySQL] to excel Please see and let me know where I need to change code.

<?php

error_reporting(E_ALL);
//ini_set('display_errors', 1); 

date_default_timezone_set('Europe/London');

include_once($_SERVER['DOCUMENT_ROOT']."/../include/common_class.php");
include_once(DOCUMENT_ROOT."/../include/db_class_mysqli.php");
include_once(DOCUMENT_ROOT."/../Classes/PHPExcel.php");

$db = new DB_MYSQL();
$search_by = "";
$search_text = "";

//set oblect
$objPHPExcel = new PHPExcel();

//get properties
$objPHPExcel->getProperties()->setCreator("CKgroove")
							 ->setLastModifiedBy("CK")
							 ->setTitle($chart_type . " count")
							 ->setSubject("count ")
							 ->setDescription( " count details")
							 ->setKeywords("")
							 ->setCategory("");

$result_XL_List = null;
$result_XL_List = $db->getXl_Data($search_by, $search_text);

$row = 31;
while ( $xl_Info = $db->fetchonce($result_XL_List) ) {
	$infodate = $xl_Info['info_date'];
	$mv_count = $xl_Info['main_view_count'];
	$mt_count = $xl_Info['main_touch_count'];
	$sv_count = $xl_Info['sub_view_count'];
	$st_count = $xl_Info['sub_touch_count'];
	
	$objPHPExcel->setActiveSheetIndex(0)
	            ->setCellValue('A'.$row, $infodate)
	            ->setCellValue('B'.$row, $mv_count)
	            ->setCellValue('C'.$row, $mt_count)
	            ->setCellValue('D'.$row, $sv_count)
	            ->setCellValue('E'.$row, $st_count);
	$row++;
}

//set sheet name
$objPHPExcel->getActiveSheet()->setTitle(' count');
$objPHPExcel->setActiveSheetIndex(0);
$file = "chart report count" . '.xlsx';

$len = filesize($file);
$filename = basename($file);
$file_extension = strtolower(substr(strrchr($filename,"."),1));

//Begin writing headers
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: public"); 
header("Content-Description: File Transfer");

//Use the switch-generated Content-Type
header("Content-Type: application/vnd.ms-excel");
//Force the download
$header="Content-Disposition: attachment; filename=".$filename.";";
header($header );
header("Content-Transfer-Encoding: binary");
header("Content-Length: ".$len);
@readfile($file);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;

?>

Thanks in advance,

thenndral.

Coordinator
Mar 14, 2011 at 10:29 AM

Start by opening the problem file in a text editor to see if it contains any PHP errors

Mar 14, 2011 at 11:19 AM
Hi Mark,

Thanks for your reply.

I check as you said, In that there is no data export. ie, its an empty file.but just the error come when I open the Excel file. I thought to sent file but its a zero kb so i cant able to sent.
can you give me any other suggestion.

pls note: I have a problem over 30 record set.

Thanks in advance,
thenndral.

On Mon, Mar 14, 2011 at 6:29 PM, MarkBaker <notifications@codeplex.com> wrote:

From: MarkBaker

Start by opening the problem file in a text editor to see if it contains any PHP errors

Read the full discussion online.

To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com)

To start a new discussion for this project, email PHPExcel@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Coordinator
Mar 14, 2011 at 5:03 PM

Is there anything in the PHP error log?

Mar 15, 2011 at 1:14 AM
Hello Mark,

Thanks for your reply.

I use a code to retrieve for php log error. Please see the attachment, when I open the excel sheet I'm getting this error message.

error_reporting(E_ALL);
ini_set('display_errors', 1);

I didn't received any error message. When I open the excel I received the error as I told before.
If error come means all the times have to come, ie. less than 30 records also have to come but the my situation is over 30 records then I cant able to open excel sheet. Its strange please tell me, what shall I have to do.

thanks in advance,
thenndral


On Tue, Mar 15, 2011 at 1:03 AM, MarkBaker <notifications@codeplex.com> wrote:

From: MarkBaker

Is there anything in the PHP error log?

Read the full discussion online.

To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com)

To start a new discussion for this project, email PHPExcel@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Mar 15, 2011 at 5:42 AM
Hello Mark,

I check My log File Again, yeah I got some PHP Error.
Here is it:

PHP Warning: filesize() [function.filesize]: stat failed for 63_count.xlsx in /home/ad.pt.co.kr/htdocs/rpt/test_xl.php on line 280, referer: http://si.pt.co.kr/rpt/rprt_ad.php

I get the file size and the same I use in header file. is that goes wrong,
Here is my code,

$len = filesize($file);
header("Content-Length: ".$len);

please let me know your suggestion.

Thanks in advance,
thenndral

On Tue, Mar 15, 2011 at 9:14 AM, Thendral <thenndral@gmail.com> wrote:
Hello Mark,

Thanks for your reply.

I use a code to retrieve for php log error. Please see the attachment, when I open the excel sheet I'm getting this error message.

error_reporting(E_ALL);
ini_set('display_errors', 1);

I didn't received any error message. When I open the excel I received the error as I told before.
If error come means all the times have to come, ie. less than 30 records also have to come but the my situation is over 30 records then I cant able to open excel sheet. Its strange please tell me, what shall I have to do.

thanks in advance,
thenndral


On Tue, Mar 15, 2011 at 1:03 AM, MarkBaker <notifications@codeplex.com> wrote:

From: MarkBaker

Is there anything in the PHP error log?

Read the full discussion online.

To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com)

To start a new discussion for this project, email PHPExcel@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com



Mar 15, 2011 at 9:04 AM

Hi,

 

I have a few questions for you:

- why do you send headers yourself? The objWriter does that for you

- you specify the length of an apparantly totally different file in your http headers... why?

- what file are you fstat-ing? since phpExcel outputs to stdout.

Could it be that the outputted excelfile gets truncated because you send the wrong file length in the http headers?

Mar 15, 2011 at 10:01 AM
Edited Mar 15, 2011 at 10:02 AM

Hello borft,


Thanks so much Mark and borft.

Well, I'm newbie to PHPExcel.
When I start code through test folder file. I got an header error, I Google and search in our forums. I got the solution, to change the header it may work. so I change the header and it works too. [ now I removed the other header, right now also working without any error, I don't know what mistake I done before.Strange.]

After your mail I remove the header and remove the filesize header line also. now it working fine.
Data export to excel well. Thanks Again.


I have two questions,
1. I want to export data to excel [its already done] and have to make a chart, is it possible in PHPExcel.
Is PHPEXcel Support Chart?
2. Filename problem.ie. I got a company name from DB and set the name as a FileName. IT is in korean font.
Inside the excel sheet korean font works fine, no problem in it. But for file name is not set.
I already define,
define('DB_CHARSET_EUCKR','euckr');
define('DB_CHARSET_UTF8','UTF8');
this code is for db,
mysqli_set_charset($this->dbh,"UTF8");

My Original Filename from DB : KAL愿묎퀬_count.xlsx
Change Filename : KAL세로막대형_count.xlsx

How to Correct this?

Thanks for your Best Support,
thenndral



On Tue, Mar 15, 2011 at 5:04 PM, borft <notifications@codeplex.com> wrote:

From: borft

Hi,

 

I have a few questions for you:

- why do you send headers yourself? The objWriter does that for you

- you specify the length of an apparantly totally different file in your http headers... why?

- what file are you fstat-ing? since phpExcel outputs to stdout.

Could it be that the outputted excelfile gets truncated because you send the wrong file length in the http headers?

Read the full discussion online.

To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com)

To start a new discussion for this project, email PHPExcel@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com

Mar 16, 2011 at 6:42 AM
Hello,

I'm expecting your reply, Please tell your suggestion/idea.
suppose, PHPExcel doesn't support chart, could you tell me how to make an excel chart?


thanks,
thenndral.

On Tue, Mar 15, 2011 at 6:00 PM, Thendral <thenndral@gmail.com> wrote:
Hello borft,

Thanks so much Mark and borft.

Well, I'm newbie to PHPExcel.
When I start code through test folder file. I got an header error, I Google and search in our forums. I got the solution, to change the header it may work. so I change the header and it works too. [ now I removed the other header, right now also working without any error, I don't know what mistake I don't before.Strange.]

After your mail I remove the header and remove the filesize header line also. now it working fine.
Data export to excel well. Thanks Again.


I have two questions,
1. I want to export data to excel [its already done] and have to make a chart, is it possible in PHPExcel.
Is PHPEXcel Support Chart?
2. Filename problem.ie. I got a company name from DB and set the name as a FileName. IT is in korean font.
Inside the excel sheet korean font works fine, no problem in it. But for file name is not set.
I already define,

define('DB_CHARSET_EUCKR','euckr');
define('DB_CHARSET_UTF8','UTF8');

this code is for db,
mysqli_set_charset($this->dbh,"UTF8");

My Original Filename from DB : KAL愿묎퀬_count.xlsx
Change Filename : KAL세로막대형_count.xlsx

How to Correct this?

Thanks for your Best Support,
thenndral



On Tue, Mar 15, 2011 at 5:04 PM, borft <notifications@codeplex.com> wrote:

From: borft

Hi,

I have a few questions for you:

- why do you send headers yourself? The objWriter does that for you

- you specify the length of an apparantly totally different file in your http headers... why?

- what file are you fstat-ing? since phpExcel outputs to stdout.

Could it be that the outputted excelfile gets truncated because you send the wrong file length in the http headers?

Read the full discussion online.

To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com)

To start a new discussion for this project, email PHPExcel@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com