Create Multiple Worksheet from MySQL Data and loading template

Topics: Developer Forum, User Forum
Jan 13, 2013 at 11:30 PM
Edited Jan 14, 2013 at 12:33 AM

Good Am. I am new to PHPExcel. I'm Just wondering, how can I make multiple worksheets from my data retrieved from MySQL and at the same time each new sheet must use the same template.

For example, I have 3 employee information in my database and when I generate them to excel using PHPExcel, they would create an excel file with 3 worksheets and each worksheet use the same template.

Currently, the template is just applied to the first sheet.

Thank you in advance.

Coordinator
Jan 14, 2013 at 12:40 AM

Look to the worksheet copy() method to create copies of your template worksheet, then use the PHPExcel objects addSheet() method to add each copy to the workbook you're creating

Jan 14, 2013 at 6:44 AM

Thank you for the reply.

By the way, can you provide a sample of how to use the copy() method?

Currently, here is my code:

 

$objPHPExcel = new PHPExcel();

  $ws_name = "Worksheet_";

 $i=0; while ($i < 2) {

$objWorksheet = new PHPExcel_Worksheet($objPHPExcel);

$objPHPExcel->addSheet($objWorksheet);

$objWorksheet->setTitle($ws_name.$i);

$objWorksheet->setCellValue('A1', 'Something_'.$i); $i++; //Assuming that this is the data from the database

}

$objPHPExcel->removeSheetByIndex(0); 

 

I do not know how to load the template through the copy() method.

Jan 14, 2013 at 10:44 PM
Edited Jan 14, 2013 at 11:10 PM

Thanks for your suggestion.

I have already figured it out.

Thanks for your help!

By the way, now I am having a problem loading my template file which has a formatted Japanese character.

For example, in cell A1, i have there 「My 日本語 template」 (that is, in cell A1 there is this text with Japanese characters and a different font-style). When I used this as template, the output excel file is corrupted. But when I removed the formatting, the ouput excel file is ok.

Any suggestions? Thanks in advance.

Coordinator
Jan 15, 2013 at 12:30 AM
Edited Jan 15, 2013 at 12:31 AM

PHPExcel 1.7.8 doesn't support "Rich Text" containing non-ASCII characters in the Excel5 Writer. There have been some changes in the latest github that go some way to fixing this issue, although it isn't yet fully resolved. Non-ASCII Rich Text is not an issue with the Excel2007 Writer.

Jan 17, 2013 at 5:00 AM

So using Excel5 writer, I can not accomplish this output?

Also, if my template file has a box or a line (drawing objects), will these objects be loaded?

Thanks in advance.

Mar 20, 2013 at 9:01 AM
Edited Mar 20, 2013 at 11:15 AM
Hi Mark (&egocentric13)

I also have the same issue has 1 you had before. Do you mind posting some sample code on how you using the copy () and addSheet methods?

Here is link for the discusion that i have posted - http://phpexcel.codeplex.com/discussions/437280

Thanks in advance
Mar 20, 2013 at 11:53 PM
To jaahvicky,

Here's what I did:

$xls = new PHPExcel();

$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("templates/mytemplate.xls");
$sheetTemplate = $objPHPExcel->getSheet(0);

$i=0;
while ($i < 10)
{
$sheet = clone $sheetTemplate; // cloning the template active sheet
$sheet->setTitle('Cloned Sheet'.$i); //title
$sheet->setCellValue("A1", "Hello World ".$i); //data
$xls->addExternalSheet( $sheet, 0 ); //adding new sheet
$i++;
}
$xls->removeSheetByIndex($i);

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\""."Sample.xls"."\"");
header("Cache-Control: max-age=0");
$objWriter = PHPExcel_IOFactory::createWriter($xls, 'Excel5');
$objWriter->save("php://output");