目的:
根據某一欄位的值，改變整列的背景顏色
範例:
- A2:L42
範圍要選擇整個資料表，而不是只有一列A2:L2 - =$L2=”V”
- 開頭要是=
- $L2前面要加上$(絕對位置)
- “V” 字串前後要加上雙影號
Reference:
Category Archives: Excel
Alternative for PHP_excel
Remove non-breaking space from Excel (Filter Cell Value)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
function format_excel_value($str){ $str = trim($str); if(substr($str, 0, 1) == '='){//"=" will cause formular error $str = substr($str, 1); } $str = preg_replace('/\\\"/', '"', $str); $str = preg_replace("/\\\'/", ''', $str); $str = preg_replace("/[\n\r]/", '', $str); //$str = preg_replace("/[\s\n\r\t\v\x]/", '', $str);//Thsi will remove all space $str = preg_replace("~\x{00a0}~siu", '', $str); //$str = preg_replace('n\/a', '', $str); return $str; } |
Reference:
Create and Download CSV File on The Fly
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
function download(){ $this->check_permission('device', 'update', array('download')); $options = $this->request_params(); //logg($options); $data = $this->device_m->get_devices($this->lang_id, $options); if($data === false){ $this->failed_response(0, '無法取得訂單資料'); } //logg($data); $file_name = 'Devices.csv'; $file_path = FCPATH . 'uploads_temp' . DIRECTORY_SEPARATOR . $file_name; $file_url = base_url() . 'uploads_temp/' . $file_name . '?t=' . time(); if(file_exists($file_path) == true){ unlink($file_path); } $file = fopen($file_path,"w"); $rowN = 1; $columnN = 0; $fields = $this->config->item('device_download_fields'); $line = []; foreach($fields as $key => $item){ $label = iconv('utf-8', 'big5', $item[0]); //if($key == 'partner_id') $label = '經銷商名稱'; $line[] = $label; } fputcsv($file, $line); //Get options $products = $this->products_m->get_products_simple($this->lang_id); foreach($data as $device){ $line = []; foreach($fields as $key => $item){ $value = $device->{$key}; if($key == 'product_id'){ $value = $this->options_m->option_name($device->{$key}, $products, 'title'); } if($key == 'house'){ $value = $device->house_name; } if($key == 'status'){ $value = $device->status_name; } //$label = iconv('utf-8', 'big5', $value); $label = mb_convert_encoding($value, 'big5', 'utf-8'); //if($key == 'partner_id') $label = '經銷商名稱'; $value = str_replace(array("\r\n", "\n\r", "\n", "\r"), ' ', $value); $value = str_replace(array("\t"), ' ', $value); $line[] = $value; } fputcsv($file, $line); } fclose($file); //logg('done'); header('Content-Description: File Transfer'); header('Content-Type: application/octet-stream'); header('Content-Disposition: attachment; filename="'.basename($file_path).'"'); header('Expires: 0'); header('Cache-Control: must-revalidate'); header('Pragma: public'); header('Content-Length: ' . filesize($file_path)); readfile($file_path); exit; } |
Reference:
Keep Leading 0 of Numbers in Excel
By default, the leading 0 will be removed if the value is a number:
|
1 |
$worksheet->setCellValueByColumnAndRow($columnIndex, $rowN, $cell_value); |
Solution:
|
1 2 |
$cell = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($columnIndex, $rowN); $cell->setValueExplicit($cell_value, PHPExcel_Cell_DataType::TYPE_STRING); |
Reference:
Replace New Line Character in a Cell
Count In Some Condition
Count only when the cell value if bigger than 0 from M5 to Q5:
|
1 |
COUNTIF(W5:CG5, ">0") |