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(){
	$users = $this-&gt;newsletter_user_m-&gt;get_items($this-&gt;lang_id);
	
	$data = array(
		array(&#39;&#39;, &#39;姓名&#39;, &#39;電子郵件&#39;, &#39;啟用&#39;, &#39;建立時間&#39;, &#39;更新時間&#39;)
	);

	foreach($users as $index =&gt; $item){
		$arr = array(
			$index + 1, 
			$item-&gt;name, 
			$item-&gt;email, 
			($item-&gt;enabled == 1) ? &#39;是&#39; : &#39;否&#39;, 
			$item-&gt;created_time, 
			$item-&gt;updated_time
		);
		array_push($data, $arr);
	}

	$f = fopen(&#39;php://memory&#39;, &#39;w&#39;);
	$filename = &quot;Newsletter_List.csv&quot;;
	$delimiter=&quot;,&quot;;

	//Important!! Force to output UTF-8
	fprintf($f, chr(0xEF).chr(0xBB).chr(0xBF));

	foreach ($data as $line) { 
		// generate csv lines from the inner arrays
		fputcsv($f, $line, $delimiter); 
	}
	// rewrind the &quot;file&quot; with the csv lines
	fseek($f, 0);


	// tell the browser it&#39;s going to be a csv file
	header(&#39;Content-Type: application/csv&#39;);
	// tell the browser we want to save it instead of displaying it
	header(&#39;Content-Disposition: attachement; filename=&quot;&#39;.$filename.&#39;&quot;;&#39;);
	// make php send the generated csv lines to the browser
	fpassthru($f);
}

function download(){

$users = $this->newsletter_user_m->get_items($this->lang_id);

$data = array(

array('', '姓名', '電子郵件', '啟用', '建立時間', '更新時間')

);

foreach($users as $index => $item){

$arr = array(

$index + 1,

$item->name,

$item->email,

($item->enabled == 1) ? '是' : '否',

$item->created_time,

$item->updated_time

);

array_push($data, $arr);

}

$f = fopen('php://memory', 'w');

$filename = "Newsletter_List.csv";

$delimiter=",";

//Important!! Force to output UTF-8

fprintf($f, chr(0xEF).chr(0xBB).chr(0xBF));

foreach ($data as $line) {

// generate csv lines from the inner arrays

fputcsv($f, $line, $delimiter);

}

// rewrind the "file" with the csv lines

fseek($f, 0);

// tell the browser it's going to be a csv file

header('Content-Type: application/csv');

// tell the browser we want to save it instead of displaying it

header('Content-Disposition: attachement; filename="'.$filename.'";');

// make php send the generated csv lines to the browser

fpassthru($f);

}

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
« Aug
				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