Solution to the problem that the color exported by phpexcel to excel is inconsistent with the color in the web page-PHP Tutorial-php.cn

Solution to the problem that the color exported by phpexcel to excel is inconsistent with the color in the web page

WBOY

Release： 2016-07-25 09:10:12

Original

972 people have browsed it

require_once './PHPExcel.php';
require_once './Excel5.php';
require_once './get_excel_row.php';
require "../include/base.php ";
require "../include/function/006/creatExcelDb.php";
define("COLOR1","#96B7F6");
//Check and process data------------ ---------------
$q = $db->query("select * from oa_event_sales");
while($a = $db->fetch_array($q)) {
$list[] = $a;
}
$ce = new creatExcelDb();
$re = $ce->_run($list,'served_time','client_status','oid');
$ all_nums=0;
$num=array();
foreach($re as $k=>$v){
$num[$k]=count($re[$k]);
$all_nums+=count ($re[$k]);
}
$jq = array();
$title1 = client_status;
$title2 = fin_confirm;
$title3 = oid;
//Remove the same values in the array
foreach($ re as $key => $val){
if(true){
foreach($val as $key2 => $val2){
if(!in_array($key2,$jq)){
$jq[ ] = $key2;
}
}
}
}
$arr_keys=array();
foreach($re as $k=>$v){
foreach($v as $k2=>$v2) {
$arr_keys[]=$k2;
}
}
$c=array_count_values($arr_keys);
//++===---------------- ---------
// Create a processing object instance
$objExcel = new PHPExcel();
// Create a file format writing object instance, uncomment
$objWriter = new PHPExcel_Writer_Excel5($objExcel);
//Set the basic properties of the document/**It seems that it is generally not used**/
$objProps = $objExcel->getProperties();
$objProps->setCreator("Yang Benmu");
$objProps->setLastModifiedBy("Yang Benmu" ");
$objProps->setTitle("杨本木");
$objProps->setSubject("杨本木");
$objProps->setDescription("杨本木");
$objProps->setKeywords( "杨本木");
$objProps->setCategory("杨本木");
//***************************************
//Set the current sheet index for subsequent content operations .
//Generally, display calls are only needed when using multiple sheets.
//By default, PHPExcel will automatically create the first sheet with SheetIndex=0
$objExcel->setActiveSheetIndex(0);
$objActSheet = $objExcel->getActiveSheet();
//Set the current The name of the active sheet
$objActSheet->setTitle('current sheetname');
//Set the width. This value is different from that in EXCEL. I don’t know what the unit is. It is slightly smaller than the width in EXCEL
//$objActSheet- >getColumnDimension('A')->setWidth(20);
//$objActSheet->getRowDimension(1)->setRowHeight(30); //Height
//Set the value of the cell
$objActSheet ->setCellValue('A1', 'Total title display');
/*
//Set style
$objStyleA1 = $objActSheet->getStyle('A1');
$objStyleA1->getAlignment()- >setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objFontA1 = $objStyleA1->getFont();
$objFontA1->setName('宋体');
$objFontA1->setSize(18);
$objFontA 1 ->setBold(true);
//Set column center alignment
$objActSheet->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
*/
//---------------first--- ------------------
$benmu=1;
$objActSheet->setCellValue('A1', 'row label column label');
foreach($jq as $k=>$v){
$objActSheet->setCellValue(get_excel_row($benmu).'1', $v);
$benmu+=1;
}
$objActSheet->setCellValue(get_excel_row($ benmu).'1', 'Total');
//Set the width
for($i=0;$i<$benmu+1;$i++){
$objActSheet->getColumnDimension(get_excel_row($i) )->setWidth(20);
//Width
$objActSheet->getStyle(get_excel_row($i)."1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$ objActSheet->getStyle(get_excel_row($i))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//Color
$objActSheet->getStyle(get_excel_row($i)."1" )->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objActSheet->getStyle(get_excel_row($i)."1")->getFill()->getStartColor()-> ;setARGB(COLOR1);
}
//-------------content--------------------
$y=2 ;
foreach($re as $k1=>$v1){ //All $k1 is placed in A2. . . .Later, k1 is the phone number and v1 is the person-》number
$objActSheet->setCellValue('A'.$y, $k1);
//Color
$objActSheet->getStyle('A'.$y )->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objActSheet->getStyle('A'.$y)->getFill()->getStartColor()->setARGB( COLOR1);
foreach($jq as $k2=>$v2){ //k2 is 0, v2 is person
foreach($v1 as $k3=>$v3){ //$k3 is person,$ v3 is the desired value
if($k3==$v2){
//$objActSheet->setCellValue(get_excel_row("1"+$k2).$y,$v1[$k3]);
$objActSheet ->setCellValueExplicit(get_excel_row("1"+$k2).$y,$v1[$k3],PHPExcel_Cell_DataType::TYPE_STRING);
}
}
}
$objActSheet->setCellValue(get_excel_row("1" +count($jq)).$y, $num[$k1]);
$y+=1;
}
//-----------------last-- ----------------
$objActSheet->setCellValue("A".$y,"Total");
//Color
$objActSheet->getStyle("A". $y)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objActSheet->getStyle("A".$y)->getFill()->getStartColor()-> setARGB(COLOR1);
foreach($jq as $k=>$v){ //k is person
$objActSheet->setCellValue(get_excel_row("1"+$k).$y,$c[$ v]);
//Color
$objActSheet->getStyle(get_excel_row("1"+$k).$y)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objActSheet- >getStyle(get_excel_row("1"+$k).$y)->getFill()->getStartColor()->setARGB(COLOR1);
}
$objActSheet->setCellValue(get_excel_row(" 1"+count($jq)).$y,$all_nums);
//Color
$objActSheet->getStyle(get_excel_row("1"+count($jq)).$y)->getFill( )->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objActSheet->getStyle(get_excel_row("1"+count($jq)).$y)->getFill()->getStartColor()-> ;setARGB(COLOR1);
//----------------------------------
//Output content
$outputFileName =time().".xls";
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/octet-stream");
header("Content-Type:application/ download");
header('Content-Disposition:attachment;filename='.$outputFileName.'');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php: //output');
?>

Copy the code

The above code, the color in define does not display the correct color in the exported excel. Start by defining a red color to see what is displayed, as follows: define(“COLOR1″,”#FF0000″); But the result shows that blue is defined to be displayed as a color similar to purple in excel, which is not the color displayed on the page anyway. Look at this code: $objActSheet->getStyle(‘A’.$y)->getFill()->getStartColor()->setARGB(COLOR1); Considering that it may be due to ARGB, try adding two digits of 00 in front of the color. The format may be like this argb(128,255,0,0). You can eliminate this problem first, because a is transparency. After testing, I found out that it is indeed increasing. 4-bit color fixation for transparency.