Category Archives: Excel

Alternative for PHP_excel

http://stackoverflow.com/questions/3930975/alternative-for-php-excel

PHP_XLSXWriter

Remove non-breaking space from Excel (Filter Cell Value)

function format_excel_value($str){
	$str = trim($str);

	if(substr($str, 0, 1) == '='){//"=" will cause formular error
		$str = substr($str, 1);
	}

	$str = preg_replace('/\\\"/', '&quot;', $str);
	$str = preg_replace("/\\\'/", '&#39;', $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;
}

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

	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();
		$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 = '經銷商名稱';

	            $line[] = $label;
			}

			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;
	}

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();

$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 = '經銷商名稱';

$line[] = $label;

}

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:

$worksheet-&gt;setCellValueByColumnAndRow($columnIndex, $rowN, $cell_value);

1	$worksheet->setCellValueByColumnAndRow($columnIndex, $rowN, $cell_value);

Solution:

$cell = $objPHPExcel-&gt;getActiveSheet()-&gt;getCellByColumnAndRow($columnIndex, $rowN);
$cell-&gt;setValueExplicit($cell_value, PHPExcel_Cell_DataType::TYPE_STRING);

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:

COUNTIF(W5:CG5, &quot;&gt;0&quot;)

1	COUNTIF(W5:CG5, ">0")

M	T	W	T	F	S	S
« May
					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

Rex's Personal Site

This site is built on WordPress and contains personal notes, photos and videos

Category Archives: Excel

Alternative for PHP_excel

PHP_XLSXWriter

Remove non-breaking space from Excel (Filter Cell Value)

Create and Download CSV File on The Fly

Keep Leading 0 of Numbers in Excel

Solution:

Replace New Line Character in a Cell

Count In Some Condition