ต่อไป »

[dmax]

เพื่อนฝากมาถามครับ จะทำอย่างไรกับโค๊ดนี้ เพื่อให้สามารถ export ไฟล์ excel โดยให้มันจัดแบ่งคอลัมน์ให้ด้วย เพราะที่ทำตามโค๊ดด้านล่างนี้มันไม่ยอมแยกคอลัมน์ให้ด้วย

ขอบคุณล่วงหน้าสำหรับโปรแกรมเมอร์ใจดีครับ

โค๊ด:

<?PHP
 
//EDIT YOUR MySQL Connection Info:
$DB_Server = "";        //your MySQL Server
$DB_Username = "";                 //your MySQL User Name
$DB_Password = "";                //your MySQL Password
$DB_DBName = "";                //your MySQL Database Name
$DB_TBLName = "";                //your MySQL Table Name
 
//$DB_TBLName,  $DB_DBName, may also be commented out & passed to the browser
//as parameters in a query string, so that this code may be easily reused for
//any MySQL table or any MySQL database on your server
 
//DEFINE SQL QUERY:
//edit this to suit your needs
$sql = "Select * from $DB_TBLName";
 
//Optional: print out title to top of Excel or Word file with Timestamp
//for when file was generated:
//set $Use_Titel = 1 to generate title, 0 not to use title
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = DATE('m-d-Y H:i');
//define title for .doc or .xls file: EDIT this if you want
$title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date";
/*
 
Leave the connection info below as it is:
just edit the above.
 
(Editing of code past this point recommended only for advanced users.)
*/
//create MySQL connection
$Connect = @MYSQL_CONNECT($DB_Server, $DB_Username, $DB_Password)
     or DIE("Couldn't connect to MySQL:<br>" . MYSQL_ERROR() . "<br>" . MYSQL_ERRNO());
//select database
$Db = @MYSQL_SELECT_DB($DB_DBName, $Connect)
     or DIE("Couldn't select database:<br>" . MYSQL_ERROR(). "<br>" . MYSQL_ERRNO());
//execute query
$result = @MYSQL_QUERY($sql,$Connect)
     or DIE("Couldn't execute query:<br>" . MYSQL_ERROR(). "<br>" . MYSQL_ERRNO());
 
//if this parameter is included ($w=1), file returned will be in word format ('.doc')
//if parameter is not included, file returned will be in excel format ('.xls')
IF (ISSET($w) && ($w==1))
{
     $file_type = "msword";
     $file_ending = "doc";
}ELSE {
     $file_type = "vnd.ms-excel";
     $file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
HEADER("Content-Type: application/$file_type");
HEADER("Content-Disposition: attachment; filename=database_dump.$file_ending");
HEADER("Pragma: no-cache");
HEADER("Expires: 0");
 
/*    Start of Formatting for Word or Excel    */
 
IF (ISSET($w) && ($w==1)) //check for $w again
{
     /*    FORMATTING FOR WORD DOCUMENTS ('.doc')   */
     //create title with timestamp:
     IF ($Use_Title == 1)
     {
         ECHO("$title\n\n");
     }
     //define separator (defines columns in excel & tabs in word)
     $sep = "\n"; //new line character
 
     WHILE($row = MYSQL_FETCH_ROW($result))
     {
         //set_time_limit(60); // HaRa
         $schema_insert = "";
         FOR($j=0; $j<mysql_num_fields($result);$j++)
         {
         //define field names
         $field_name = MYSQL_FIELD_NAME($result,$j);
         //will show name of fields
         $schema_insert .= "$field_name:\t";
             IF(!ISSET($row[$j])) {
                 $schema_insert .= "NULL".$sep;
                 }
             ELSEIF ($row[$j] != "") {
                 $schema_insert .= "$row[$j]".$sep;
                 }
             ELSE {
                 $schema_insert .= "".$sep;
                 }
         }
         $schema_insert = STR_REPLACE($sep."$", "", $schema_insert);
         $schema_insert .= "\t";
         PRINT(TRIM($schema_insert));
         //end of each mysql row
         //creates line to separate data from each MySQL table row
         PRINT "\n----------------------------------------------------\n";
     }
}ELSE{
     /*    FORMATTING FOR EXCEL DOCUMENTS ('.xls')   */
     //create title with timestamp:
     IF ($Use_Title == 1)
     {
         ECHO("$title\n");
     }
     //define separator (defines columns in excel & tabs in word)
     $sep = "\t"; //tabbed character
 
     //start of printing column names as names of MySQL fields
     FOR ($i = 0; $i < MYSQL_NUM_FIELDS($result); $i++)
     {
         ECHO MYSQL_FIELD_NAME($result,$i) . "\t";
     }
     PRINT("\n");
     //end of printing column names
 
     //start while loop to get data
     WHILE($row = MYSQL_FETCH_ROW($result))
     {
         //set_time_limit(60); // HaRa
         $schema_insert = "";
         FOR($j=0; $j<mysql_num_fields($result);$j++)
         {
             IF(!ISSET($row[$j]))
                 $schema_insert .= "NULL".$sep;
             ELSEIF ($row[$j] != "")
                 $schema_insert .= "$row[$j]".$sep;
             ELSE
                 $schema_insert .= "".$sep;
         }
         $schema_insert = STR_REPLACE($sep."$", "", $schema_insert);
         //following fix suggested by Josue (thanks, Josue!)
         //this corrects output in excel when table fields contain \n or \r
         //these two characters are now replaced with a space
         $schema_insert = PREG_REPLACE("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
         $schema_insert .= "\t";
         PRINT(TRIM($schema_insert));
         PRINT "\n";
     }
}
 
?>

[icenobu]

ถ้าใช้โค๊ดของคุณพี่ เราไม่เข้าใจอ่ะค่ะ แต่เรามีโค๊ดให้คุณพี่ดูเจ้าค่ะ ไม่รู้จะตรงกับโจทย์หรือไม่น่ะค่ะ

โค๊ด:

<?
$start = $_REQUEST["date23"];
$end = $_REQUEST["date24"];

#connect DB
$ServerName = "localhost";
$UserName = "root";
$Password = "root";
$DatabaseName = "chinobu";
   
$conn = mysql_connect($ServerName,$UserName,$Password) or die("ติดต่อฐานข้อมูลไม่ได้‰ !");
mysql_select_db($DatabaseName,$conn) or die("<H3>เลือกฐานข้อมูล MySQL ไม่ได้‰ !</H3>");
mysql_query("SET NAMES UTF8");
$SQL="select a.work_id, b.detail, c.worksub_detail, a.work_title, a.work_detail, a.workstart_time, a.workend_time from works a, worktype b, worksub c where a.worktype_id=b.worktype_id and a.worksub_id=c.worksub_id and a.workstart_time between '".$start."' and '".$end."' order by a.work_id;";
$result = mysql_query($SQL);
$result1 = mysql_query($SQL);
$check =  mysql_fetch_array($result1);
if($check<>NULL)
{
	while($row=mysql_fetch_array($result))
	{
		$workid[]=$row['work_id'];
		$worktype[]=$row['detail'];
		$worksub_detail[]=$row['worksub_detail'];
		$work_title[]=$row['work_title'];
		$description[]=$row['work_detail'];
		$it_id[]=$row['it_id'];
		$workstart_time[]=$row['workstart_time'];
		$workend_time[]=$row['workend_time'];
	}
}
else{
		$workid[]=NULL;
		$worktype[]=NULL;
		$worksub_detail[]=NULL;
		$work_title[]=NULL;
		$description[]=NULL;
		$it_id[]=NULL;
		$workstart_time[]=NULL;
		$workend_time[]=NULL;
}

/** Error reporting */
error_reporting(E_ALL);

/** PHPExcel */
require_once $_SERVER['DOCUMENT_ROOT'].'/classes/PHPExcel.php';


// Create new PHPExcel object
//echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

// Set properties
//echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Total IT Solution")
							 ->setLastModifiedBy("Total IT Solution")
							 ->setTitle("Office 2007 XLSX Summary All Works Report")
							 ->setSubject("Office 2007 XLSX Summary All Works Report")
							 ->setDescription("Summary All Works Report.")
							 ->setKeywords("All Works")
							 ->setCategory("Report");

// Create a first sheet
$objPHPExcel->setActiveSheetIndex(0);

//Merge cell
$objPHPExcel->getActiveSheet()->mergeCells('A1:H1');

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'All Summary Works');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
// Set fills
//echo date('H:i:s') . " Set fills\n";
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FFA0A0A0');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getEndColor()->setARGB('FFFFFFFF');
//Set Alignment
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$borderHead = array(
	'borders' => array(
		'outline' => array(
			'style' => PHPExcel_Style_Border::BORDER_THICK,
			'color' => array('argb' => 'FF000000'),
		),
	),
);
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->applyFromArray($borderHead);
//time
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Times:');
$objPHPExcel->getActiveSheet()->setCellValue('B2', $start.' - '.$end);

//Set columns header
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Work ID');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Work Type');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Work Sub.');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Title');
$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Description');
$objPHPExcel->getActiveSheet()->setCellValue('F3', 'IT ID');
$objPHPExcel->getActiveSheet()->setCellValue('G3', 'Start Date');
$objPHPExcel->getActiveSheet()->setCellValue('H3', 'End Date');
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$setHeaderBorder = 	array(
			'font'    => array(
				'bold'      => true
			),
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			),
			'borders' => array(
				'outline'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				)
			),
			'fill' => array(
	 			'type'       => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
	  			'rotation'   => 90,
	 			'startcolor' => array(
	 				'argb' => 'FFA0A0A0'
	 			),
	 			'endcolor'   => array(
	 				'argb' => 'FFFFFFFF'
	 			)
	 		)
		);
$objPHPExcel->getActiveSheet()->getStyle('A3:H3')->applyFromArray($setHeaderBorder);

// Set thin black border outline around column
$styleThinBlackBorderOutline = array(
	'borders' => array(
		'outline' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN,
			'color' => array('argb' => 'FF000000'),
		),
	),
);
$styleThickBlackBorderOutline = array(
	'borders' => array(
		'outline' => array(
			'style' => PHPExcel_Style_Border::BORDER_THICK,
			'color' => array('argb' => 'FF000000'),
		),
	),
);
for($i=0;$i<count($it_id);$i++)
{
	$j=$i+4;
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$j, $workid[$i]);
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$j, $worktype[$i]);
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$j, $worksub_detail[$i]);
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$j, $work_title[$i]);
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$j, $description[$i]);
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$j, $it_id[$i]);
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$j, $workstart_time[$i]);
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$j, $workend_time[$i]);
}

for($i=0;$i<count($it_id);$i++)
{
	$k=$i+4;
	$objPHPExcel->getActiveSheet()->getStyle('A3:A'.$k)->applyFromArray($styleThinBlackBorderOutline);
	$objPHPExcel->getActiveSheet()->getStyle('B3:B'.$k)->applyFromArray($styleThinBlackBorderOutline);
	$objPHPExcel->getActiveSheet()->getStyle('C3:C'.$k)->applyFromArray($styleThinBlackBorderOutline);
	$objPHPExcel->getActiveSheet()->getStyle('D3:D'.$k)->applyFromArray($styleThinBlackBorderOutline);
	$objPHPExcel->getActiveSheet()->getStyle('E3:E'.$k)->applyFromArray($styleThinBlackBorderOutline);
	$objPHPExcel->getActiveSheet()->getStyle('F3:F'.$k)->applyFromArray($styleThinBlackBorderOutline);
	$objPHPExcel->getActiveSheet()->getStyle('G3:G'.$k)->applyFromArray($styleThinBlackBorderOutline);
	$objPHPExcel->getActiveSheet()->getStyle('H3:H'.$k)->applyFromArray($styleThinBlackBorderOutline);
}
$objPHPExcel->getActiveSheet()->getStyle('A3:H'.$k)->applyFromArray($styleThickBlackBorderOutline);

$styleThickBrownBorderOutline = array(
	'borders' => array(
		'outline' => array(
			'style' => PHPExcel_Style_Border::BORDER_THICK,
			'color' => array('argb' => 'FF000000'),
		),
	),
);

$strSQL3="select * from position;";
$cmdQuery3=mysql_query($strSQL3);
	while($rs=mysql_fetch_array($cmdQuery3))
	{
		$name[]=$rs['name'];
	}
$z=$k+4;
$x=$k+5;
$y=$k+6;
$objPHPExcel->getActiveSheet()->setCellValue('A'.$z, '..............................');
$objPHPExcel->getActiveSheet()->setCellValue('B'.$z, '..............................');
$objPHPExcel->getActiveSheet()->setCellValue('C'.$z, '..............................');
$objPHPExcel->getActiveSheet()->setCellValue('A'.$x, '(                                  )');
$objPHPExcel->getActiveSheet()->setCellValue('B'.$x, '(                                  )');
$objPHPExcel->getActiveSheet()->setCellValue('C'.$x, '(                                  )');
$objPHPExcel->getActiveSheet()->setCellValue('A'.$y,$name[0]);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$y,$name[1]);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$y,$name[2]);
$objPHPExcel->getActiveSheet()->getStyle('A'.$z)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B'.$z)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C'.$z)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A'.$x)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B'.$x)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C'.$x)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A'.$y)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B'.$y)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C'.$y)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);



/** PHPExcel_IOFactory */
require_once $_SERVER['DOCUMENT_ROOT'].'/classes/PHPExcel/IOFactory.php';
// Save Excel 2003 file
//echo date('H:i:s') . " Write to Excel2007 format\n";
$today=date("dmY");
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="All summary works report'.$today.'.xls"');
header('Cache-Control: max-age=0');
// Save Excel 2003 file
//echo date('H:i:s') . " Write to Excel2003 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
?>