<?php
$db_host="localhost";
$db_user="root";
$db_pass="";
$db_name="timetrack";
$sql_select=mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
//select database
mysql_select_db($db_name, $sql_select) or die("could not find DB.");
require_once ('PHPExcel.php');
function cellColor($cells,$color){
global $objPHPExcel;
$objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()->applyFromArray(array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array(
'rgb' => $color
)
));
}
// Create your database query
$query = "SELECT * FROM sat_staff";
// Execute the database query
$result = mysql_query($query) or die(mysql_error());
// Instantiate a new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0);
// Initialise the Excel row number
$rowCount = 1;
// Iterate through each result from the SQL query in turn
// We fetch each database result row into $row in turn
while($row = mysql_fetch_array($result)){
$objPHPExcel->getActiveSheet()->freezePane('A1');
$objPHPExcel->getActiveSheet()->freezePane('B1');
$objPHPExcel->getActiveSheet()->freezePane('C1');
$objPHPExcel->getActiveSheet()->freezePane('D1');
if($rowCount%2 == 0){
cellColor('A'.$rowCount.':I'.$rowCount.'', 'F28A8C');
}else{
cellColor('A'.$rowCount.':I'.$rowCount.'', 'ffffff');
}
/* cellColor('A8:I8', 'F28A8C');
cellColor('G5', 'F28A8C');
cellColor('A7:I7', 'F28A8C');
cellColor('A17:I17', 'F28A8C');
cellColor('A30:Z30', 'F28A8C');*/
// Set cell An to the "name" column from the database (assuming you have a column called name)
// where n is the Excel row number (ie cell A1 in the first row)
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['firstname']);
// Set cell Bn to the "age" column from the database (assuming you have a column called age)
// where n is the Excel row number (ie cell A1 in the first row)
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['lastname']);
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $row['lastname']);
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $row['lastname']);
$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $row['lastname']);
$objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, $row['lastname']);
$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, $row['lastname']);
$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount, $row['lastname']);
$objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount, $row['lastname']);
// Increment the Excel row counter
$rowCount++;
}
// Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
// Write the Excel file to filename some_excel_file.xlsx in the current directory
$objWriter->save('some_excel_file.xlsx');