kms

Got it. It was the > should have been < that fixed that. Thanks for the help!

Thanks I am looking at it right now. Right off the bat I am trying to figure out why it is only listing the first line. Seems like there is something wrong with the if statement it is not looping through the while loop.

Thanks, Ch0cu3r but I do not have the user's TeamName, (which is the users full name) or the UserID in the while loop so it only will show once as you can see in the attachment above.

Thanks for the information. The alignment I am having problems with is after the while loop. It is not aligning with Departments or Description. Attached file shows what it looks like from the browser.

I am having some trouble getting the formatting correct displaying SQL data. Below is the code but it is not lining up as it should just can't seem to get the tables right. Here is the code. echo "<table border='1'> <tr> <td width='90'><font size='2pt'>".TeamName."</td> <td ALIGN='left'><font size='2pt'>".UserID."</td> <td><font size='2pt'>".Departments."</td> <td><font size='2pt'>".Description."</td> <td><a href='csv.php'>Save to CSV file</a></td></tr><tr> <td style='vertical-align: top;'>".$name."</td> <td ALIGN='center' style='vertical-align: top;'>".$userid."</td><td> <table border='1' width='100%'>"; while($row = sqlsrv_fetch_array($stmt)){ echo "</td></tr><tr> <td>".$row[Departments]."</td> <td>".$row[Description]."</td></tr>"; } echo "</table>"; echo "</table>";

Looks like this will do it, thanks for the help. <?PHP require_once 'Excel/PHPExcel.php';//path for my config, rewrite for yours //include 'Classes/PHPExcel/Writer/Excel2007.php'; // not needed, lazy loader job $cars=array("Versa","Volt","Volt","Volt","Volt","Volkswagen","Bentley","Benz","BMW","BMW","BMW","BMW","BMW","BMW","BMW","Cobra","Cord","Daewoo","Datsun","Dodge","Dodge","Dixi"); $objPHPExcel = new PHPExcel(); $objWorksheet=$objPHPExcel->setActiveSheetIndex(0); $id=''; $countRows=0; while (list($var, $val) = each($cars)) { if ($val!=$id && $id!=''){ $objWorksheet->setTitle($id); $num=$countRows+5; $cell='A'.$num; $objWorksheet->setCellValue($cell , 'Comments:'); $objWorksheet = new PHPExcel_Worksheet($objPHPExcel); $objPHPExcel->addSheet($objWorksheet); $id = $val; $countRows=0; }//end if if($id=='') $id=$val;//the first car $no = ++$countRows; $objWorksheet->setCellValue('A'.$no , $no); $objWorksheet->setCellValue('B'.$no , $id);//Versa, Volt, ... } if($countRows>0 && $id!=''){// the last car - if $id=='' the workbook is empty $objWorksheet->setTitle($id); $num=$countRows+5; $cell='A'.$num; $objWorksheet->setCellValue($cell , 'Comments:'); }//end if // Save Excel 2007 file $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); ob_end_clean(); // We'll be outputting an excel file header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // note : use correct mime type (not xls for xlsx) // It will be called cars.xlsx header('Content-Disposition: attachment; filename="cars.xlsx"'); $objWriter->save('php://output'); Exit; ?>

I have this test script that is acting just like my production script except production script pulls from a SQL db. I want to set the cell placement using a variable based off the count of an array. In the script below, (which you can copy and run as an example) I want to set the cell number based off how many of the same car that is in an array. So for an example there are 7 BMW's listed in the array. So I want the Comments: data copied to cell A12 using the code $num=$no+5; $cell='A'.$num; The problem I am having is it does place it on that line but it also places it 7 more times about it. If there are 4 cars of that type it would place it on the right line but also place it 3 times above it. I just want it to place it once at the desired location. Any help would be great. Here is the code: <?PHP require_once 'Classes/PHPExcel.php'; include 'Classes/PHPExcel/Writer/Excel2007.php'; $dataArray= array(); $cars=array("Versa","Volt","Volt","Volt","Volt","Volkswagen","Bentley","Benz","BMW","BMW","BMW","BMW","BMW","BMW","BMW","Cobra","Cord","Daewoo","Datsun","Dodge","Dodge","Dixi"); $objPHPExcel = new PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); while (list($var, $val) = each($cars)) { if ($val!=$id){ $dataArray = array(); $objWorksheet = new PHPExcel_Worksheet($objPHPExcel); $objPHPExcel->addSheet($objWorksheet); $objWorksheet->setTitle(''. $val); $row_array[$val] = $val; array_push($dataArray,$row_array); $no = count($dataArray); $num=$no+5; $cell='A'.$num; $objWorksheet->setCellValue('A1' , $no); $objWorksheet->setCellValue('A2' , $val); $objWorksheet->setCellValue($cell , 'Comments:'); $id = $val; } else { $row_array[$val] = $val; $count=count($cars); $count2=count($loc); array_push($dataArray,$row_array); $no = count($dataArray); $num=$no+5; $cell='A'.$num; $objWorksheet->setCellValue('A2' , $no); $objWorksheet->setCellValue('A3' , $val); $objWorksheet->setCellValue($cell , 'Comments:'); $id = $val; } } // Save Excel 2007 file #echo date('H:i:s') . " Write to Excel2007 format\n"; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); ob_end_clean(); // We'll be outputting an excel file header('Content-type: application/vnd.ms-excel'); // It will be called file.xls header('Content-Disposition: attachment; filename="cars.xlsx"'); $objWriter->save('php://output'); Exit; ?>

Thank you! I believe that was it, solved. Thanks for helping.

Sorry here it is: while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) { if ($row['UserID'] != $uid) { $tn = $row['TeamName']; $objWorksheet = new PHPExcel_Worksheet($objPHPExcel); $objPHPExcel->addSheet($objWorksheet); $objWorksheet->setTitle(''. $tn); $uid = $row['UserID']; } $row_array['TeamName'] = $row['TeamName']; $row_array['UserID'] = $row['UserID']; $row_array['Departments'] = $row['Departments']; $row_array['Description'] = $row['Description']; array_push($dataArray,$row_array); $objWorksheet->fromArray($dataArray, NULL, 'A2'); }

Okay I made a change to the code the change is below. Now when I run the script I get the sheets with the names and the first on is correct right now and right information but the next sheet no only has the first sheet info on it it has the right information on it as well and it keeps building like that. So for an example it looks like this; Sheet1 (Correct) Smith_Joe JOE4S 52200 Sales Smith_Joe JOE4S 53002 Budget Smith_Joe JOE4S 43302 Dev Sheet2 (Notice Smith_Joe is on this sheet and should not be) Smith_Joe JOE4S 52200 Sales Smith_Joe JOE4S 53002 Budget Smith_Joe JOE4S 43302 Dev Black_BJ BJ3OO 43332 Mrkt Sheet3 (Notice Smith_Joe and Black_BJ is on this should and should not be) Smith_Joe JOE4S 52200 Sales Smith_Joe JOE4S 53002 Budget Smith_Joe JOE4S 43302 Dev Black_BJ BJ3OO 43332 Mrkt Steh_Tray ST44S 65573 Prod Steh_Tray ST44S 52200 Sales and so on until the last page has all the details from everyone on it. I am just not getting why it is not going through the if statement one row at a time.

Thanks so much for helping I no it is not easy. You are right about that boy, oh boy. I changed the code to: $objWorksheet->setCellValue('A2' , $tn); $objWorksheet->setCellValue('B2' , $userid); $objWorksheet->setCellValue('C2', $dep); $objWorksheet->setCellValue('D2' , $des); $uid = $row['UserID']; } else { $objWorksheet->setCellValue('A3' , $tn); $objWorksheet->setCellValue('B3' , $userid); $objWorksheet->setCellValue('C3', $dep); $objWorksheet->setCellValue('D3' , $des); } } Now I get the fist row only written twice on each sheet. So on the sheet for Smith_Joe it shows: Smith_Joe JOE4S 52200 Sales Smith_Joe JOE4S 52200 Sales and that is all. It is getting the right person on the right sheet though.

Okay this is what I got so far but it only writes the first record for each user. What I am not sure of is how to set the $uid variable so the if statement work correctly. Here is the code so far. while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) { if ($row['UserID'] != $uid) { $tn = $row['TeamName']; $userid = $row['UserID']; $dep = $row['Departments']; $des = $row['Description']; $objWorksheet = new PHPExcel_Worksheet($objPHPExcel); $objPHPExcel->addSheet($objWorksheet); $objWorksheet->setTitle(''. $tn); $objWorksheet->setCellValue('A2' , $tn); $objWorksheet->setCellValue('B2' , $userid); $objWorksheet->setCellValue('C2', $dep); $objWorksheet->setCellValue('D2' , $uid); $uid = $row['UserID']; } else { $objWorksheet->setCellValue('A2' , $tn); $objWorksheet->setCellValue('B2' , $userid); $objWorksheet->setCellValue('C2', $dep); $objWorksheet->setCellValue('D2' , $des); $uid = $row['UserID']; } } So basically it is doing a if statement on the UserID. If the userid's do not match do the addsheet() part of the code and if they do match skip to the else statement. Just do not think I have the right syntax in place.

I just can't seem to get what I need. Basically I pull data from sql which is user and what department they belong to. Some user are in multiple departments. The sql data looks like this: TeamName UserID Deparment Description --------------------------------------------------- Smith_Joe JOE4S 52200 Sales Smith_Joe JOE4S 53002 Budget Smith_Joe JOE4S 43302 Dev Black_BJ BJ3OO 43332 Mrkt Steh_Tray ST44S 65573 Prod Steh_Tray ST44S 52200 Sales I need to have one workbook with each user with their own sheet. So Smith_Joe would have his own sheet with the department and other data listed on that sheet and Black_BJ would have his own sheet with he information on it. I changed my while loop but now I get all the sheets with names but each row is listed of separate sheets. So for an example with the code below. I get Smith_Joe with 3 sheets with one record per sheet. Black_BJ get one sheet and Steh_Tray gets 2 sheets. They all should just have one sheet each with all their information on it. Here is the change I made. while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){ $res = $row; $tn = $row['TeamName']; $userid = $row['UserID']; $dep = $row['Departments']; $des = $row['Description']; $objWorksheet = new PHPExcel_Worksheet($objPHPExcel); $objPHPExcel->addSheet($objWorksheet); $objWorksheet->setTitle(''. $tn); $objWorksheet->setCellValue('A2' , $tn); $objWorksheet->setCellValue('B2' , $userid); $objWorksheet->setCellValue('C2', $dep); $objWorksheet->setCellValue('D2' , $des);

Yes I can confirm that the workbook gets created and that it does create all the sheets with the name from $row['TeamName'] which ends up to be smith_joe and so on. I did not use the numeric approach because the sheets are being created as the script goes and was not sure how I would correlate the numeric number with the user name of the sheet.

Thanks for the reply. Yes that code creates the sheets and names them after the user name. But no data is ever written to any of the sheets but the last one. Are you saying I should not be using, $objPHPExcel->setActiveSheetIndexByName($row['TeamName']); but should be using, $objPHPExcel->setActiveSheetIndex(0); I thought the code I was using would set the active sheet to be written to. I think I am having more than just one problem though sense I am not getting any data on any sheet except the last one.