Category Archives: PHPExcel

Update Excel Content

function download_tpl(){
	//$this->check_permission('orders', 'update', array('download', 'download_m'));

	$sep = DIRECTORY_SEPARATOR;

	$file_name = 'coupon_upload_tpl_origin.xls';
	$tpl = FCPATH . 'assets' . $sep . 'admin' . $sep . 'excel_templates' . $sep . $file_name;

	$file_name = 'coupon_upload_tpl.xls';
	$file = FCPATH . 'assets' . $sep . 'admin' . $sep . 'excel_templates' . $sep . $file_name;
	$file_url = base_url() . 'assets/admin/excel_templates/' . $file_name;
	
	// Initiate cache
	require(APPPATH.'libraries/PHPExcel.php');
	$inputFileType = PHPExcel_IOFactory::identify($tpl);
	$objReader = PHPExcel_IOFactory::createReader($inputFileType);
	$objPHPExcel = $objReader->load($tpl);

	//選項sheet
	$worksheet = $objPHPExcel->getSheet(1); 
	//logg($worksheet);
	
	//修改產品清單
	$rowN = 2;
	$columnN = 5;
	$products = $this->b2c_products();

	foreach($products as $key => $prod){
		$worksheet->setCellValueByColumnAndRow($columnN, $rowN, $prod->title);
		$worksheet->setCellValueByColumnAndRow($columnN+1, $rowN, $prod->unique_id);

		$rowN++;
	}
	
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save($file);

	redirect($file_url);
	exit();
}

function download_tpl(){

//$this->check_permission('orders', 'update', array('download', 'download_m'));

$sep = DIRECTORY_SEPARATOR;

$file_name = 'coupon_upload_tpl_origin.xls';

$tpl = FCPATH . 'assets' . $sep . 'admin' . $sep . 'excel_templates' . $sep . $file_name;

$file_name = 'coupon_upload_tpl.xls';

$file = FCPATH . 'assets' . $sep . 'admin' . $sep . 'excel_templates' . $sep . $file_name;

$file_url = base_url() . 'assets/admin/excel_templates/' . $file_name;

// Initiate cache

require(APPPATH.'libraries/PHPExcel.php');

$inputFileType = PHPExcel_IOFactory::identify($tpl);

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$objPHPExcel = $objReader->load($tpl);

//選項sheet

$worksheet = $objPHPExcel->getSheet(1);

//logg($worksheet);

//修改產品清單

$rowN = 2;

$columnN = 5;

$products = $this->b2c_products();

foreach($products as $key => $prod){

$worksheet->setCellValueByColumnAndRow($columnN, $rowN, $prod->title);

$worksheet->setCellValueByColumnAndRow($columnN+1, $rowN, $prod->unique_id);

$rowN++;

}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save($file);

redirect($file_url);

exit();

}

Set cell value as string

if($columnN == 1){
	$worksheet->setCellValueExplicit(
	    'B' . $rowN, 
	    $value,
	    PHPExcel_Cell_DataType::TYPE_STRING
	);
}else{
	$worksheet->setCellValueByColumnAndRow($columnN, $rowN, $value);
}

if($columnN == 1){

$worksheet->setCellValueExplicit(

'B' . $rowN,

$value,

PHPExcel_Cell_DataType::TYPE_STRING

);

}else{

$worksheet->setCellValueByColumnAndRow($columnN, $rowN, $value);

}

https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/07-Accessing-Cells.md

Get text value of a RichTextObject

$value = $sheet->getCellByColumnAndRow($i, $row_index)->getValue();

if(is_object($value) == true){
	$value = $value->getPlainText();
}

$value = $sheet->getCellByColumnAndRow($i, $row_index)->getValue();

if(is_object($value) == true){

$value = $value->getPlainText();

}

Read Excel

private function excel_handler($file){
	//Start to read
	require(APPPATH.'libraries/PHPExcel.php');
	include APPPATH.'libraries/PHPExcel/IOFactory.php';

	$fields = $this->config->item('order_upload_fields');
	//log_g($fields);

	//  Read your Excel workbook
	try {
		$inputFileType = PHPExcel_IOFactory::identify($file);
		$objReader = PHPExcel_IOFactory::createReader($inputFileType);
		$objPHPExcel = $objReader->load($file);
		//log_g($objPHPExcel);
	} catch(Exception $e) {
		die('Error loading file "'.pathinfo($file,PATHINFO_BASENAME).'": '.$e->getMessage());
	}

	//  Get worksheet dimensions
	$sheet = $objPHPExcel->getSheet(0); 
	$highestRow = $sheet->getHighestRow(); 
	$highestColumn = $sheet->getHighestColumn();

	$fields = $this->config->item('order_fields');
	$upload_fields = $this->config->item('order_upload_fields');
	//log_g($fields);

	for ($row = 2; $row <= $highestRow; $row++){ 
		//  Read a row of data into an array
		// The fourth param will convert datetime value automatically
		$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
										NULL,
										TRUE,
										true);
		//  Insert row data array into your database of choice here
		$data = $this->fill_fields($fields, $upload_fields, $rowData[0]);
		log_g($data);
	}
	log_g($highestColumn);
}

private function excel_handler($file){

//Start to read

require(APPPATH.'libraries/PHPExcel.php');

include APPPATH.'libraries/PHPExcel/IOFactory.php';

$fields = $this->config->item('order_upload_fields');

//log_g($fields);

// Read your Excel workbook

try {

$inputFileType = PHPExcel_IOFactory::identify($file);

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$objPHPExcel = $objReader->load($file);

//log_g($objPHPExcel);

} catch(Exception $e) {

die('Error loading file "'.pathinfo($file,PATHINFO_BASENAME).'": '.$e->getMessage());

}

// Get worksheet dimensions

$sheet = $objPHPExcel->getSheet(0);

$highestRow = $sheet->getHighestRow();

$highestColumn = $sheet->getHighestColumn();

$fields = $this->config->item('order_fields');

$upload_fields = $this->config->item('order_upload_fields');

//log_g($fields);

for ($row = 2; $row <= $highestRow; $row++){

// Read a row of data into an array

// The fourth param will convert datetime value automatically

$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,

NULL,

TRUE,

true);

// Insert row data array into your database of choice here

$data = $this->fill_fields($fields, $upload_fields, $rowData[0]);

log_g($data);

}

log_g($highestColumn);

}

Reference:

PHPExcel Ultimate Example

require(APPPATH.'libraries/PHPExcel.php');
$file_name = 'Statement_' . compact_date($from) . '-' . compact_date($to) . '_' . $partner_id . '.xls';
$file = FCPATH . 'uploads' . DIRECTORY_SEPARATOR . $file_name;
$file_url = base_url() . 'uploads/' . $file_name;
//log_g($file);

// Initiate cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '32MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel = new PHPExcel();

//Create $worksheet object to save data
$worksheet = $objPHPExcel->setActiveSheetIndex(0);

//This line is must because of unknwon reason or follow setCellValue won't work
$worksheet->setCellValueByColumnAndRow(0, 1, 'xxx股份有限公司');

$worksheet->mergeCells('A1:I1');
$worksheet->mergeCells('A2:I2');

$worksheet->setCellValue('A2','xxx Inc.');

$title_style = array(
	'font' => array(
		'bold' => true, 
		'size' => '22'
	),
	'alignment' => array(
		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
	)
);

$center_style = array(
	'alignment' => array(
		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
	)
);

$style_bu = array(
	'font' => array(
		'bold' => true, 
		'size' => '18', 
		'underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE
	),
	'alignment' => array(
		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
	)
);

$style_border_t = array(
	'borders' => array(
		'top' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
		)
	)
);

$style_border_b = array(
	'borders' => array(
		'bottom' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
		)
	)
);

$style_border_d = array(
	'font' => array(
		'bold' => true 
	),
	'borders' => array(
		'bottom' => array(
			'style' => PHPExcel_Style_Border::BORDER_DOUBLE
		)
	)
);

$worksheet->getStyle('A1')->applyFromArray($title_style);
$worksheet->getStyle('A2')->applyFromArray($title_style);
$worksheet->getStyle('A3')->applyFromArray($center_style);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($file);

require(APPPATH.'libraries/PHPExcel.php');

$file_name = 'Statement_' . compact_date($from) . '-' . compact_date($to) . '_' . $partner_id . '.xls';

$file = FCPATH . 'uploads' . DIRECTORY_SEPARATOR . $file_name;

$file_url = base_url() . 'uploads/' . $file_name;

//log_g($file);

// Initiate cache

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;

$cacheSettings = array( 'memoryCacheSize' => '32MB');

PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

$objPHPExcel = new PHPExcel();

//Create $worksheet object to save data

$worksheet = $objPHPExcel->setActiveSheetIndex(0);

//This line is must because of unknwon reason or follow setCellValue won't work

$worksheet->setCellValueByColumnAndRow(0, 1, 'xxx股份有限公司');

$worksheet->mergeCells('A1:I1');

$worksheet->mergeCells('A2:I2');

$worksheet->setCellValue('A2','xxx Inc.');

$title_style = array(

'font' => array(

'bold' => true,

'size' => '22'

'alignment' => array(

'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER

)

);

$center_style = array(

'alignment' => array(

'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER

)

);

$style_bu = array(

'font' => array(

'bold' => true,

'size' => '18',

'underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE

'alignment' => array(

'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER

)

);

$style_border_t = array(

'borders' => array(

'top' => array(

'style' => PHPExcel_Style_Border::BORDER_THIN

)

);

$style_border_b = array(

'borders' => array(

'bottom' => array(

'style' => PHPExcel_Style_Border::BORDER_THIN

)

);

$style_border_d = array(

'font' => array(

'bold' => true

'borders' => array(

'bottom' => array(

'style' => PHPExcel_Style_Border::BORDER_DOUBLE

)

);

$worksheet->getStyle('A1')->applyFromArray($title_style);

$worksheet->getStyle('A2')->applyFromArray($title_style);

$worksheet->getStyle('A3')->applyFromArray($center_style);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save($file);

PHPExcel

	public function excel_get(){
		$this->check_login();

		$file = FCPATH . 'uploads' . DIRECTORY_SEPARATOR . 'Members.xls';
		$file_url = base_url() . 'uploads/Members.xls';
		
		// Initiate cache
		$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
		$cacheSettings = array( 'memoryCacheSize' => '32MB');
		PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
		$objPHPExcel = new PHPExcel();

		//Create $worksheet object to save data
		$worksheet = $objPHPExcel->setActiveSheetIndex(0);
		$rowN = 1;
		$columnN = 0;

		$headers = array(
			'ID', 
			'姓名', 
			'性別', 
			'生日', 
			'國碼', 
			'電話', 
			'國家', 
			'身份證明', 
			'單位', 
			'居住地', 
			'Email', 
			'建立時間', 
			'作者介紹', 
			'劇本名', 
			'投稿組別', 
			'劇本大綱', 
			'是否參與其他競賽', 
			'劇本發表', 
			'PDF', 
			'Word', 
			'上傳時間' 
		);
		foreach($headers as $value){
            $worksheet->setCellValueByColumnAndRow($columnN, $rowN, $value);
			$columnN++;
		}

		$members = $this->competition_m->get_items(false);
		$rowN++;
		foreach($members as $member){
			$data = array(
				$member->id, 
				$member->name, 
				($member->gender == 'f') ? '女' : '男', 
				$member->birthday, 
				$member->phone_country_code, 
				$member->phone, 
				$member->country, 
				$member->attachment, 
				$member->company, 
				$member->city, 
				$member->email, 
				$member->created_time, 
				$member->introduction, 
				$member->title, 
				$member->language, 
				$member->summary, 
				($member->exclusive == 1) ? '是' : '否', 
				($member->public == 1) ? '是' : '否', 
				$member->pdf, 
				$member->word, 
				$member->upload_time
			);

			$columnN = 0;
			foreach($data as $value){
	            $worksheet->setCellValueByColumnAndRow($columnN, $rowN, $value);
				$columnN++;
			}

			$rowN++;
		}

		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save($file);

		$this->success_response($file_url);
	}

public function excel_get(){

$this->check_login();

$file = FCPATH . 'uploads' . DIRECTORY_SEPARATOR . 'Members.xls';

$file_url = base_url() . 'uploads/Members.xls';

// Initiate cache

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;

$cacheSettings = array( 'memoryCacheSize' => '32MB');

PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

$objPHPExcel = new PHPExcel();

//Create $worksheet object to save data

$worksheet = $objPHPExcel->setActiveSheetIndex(0);

$rowN = 1;

$columnN = 0;

$headers = array(

'ID',

'姓名',

'性別',

'生日',

'國碼',

'電話',

'國家',

'身份證明',

'單位',

'居住地',

'Email',

'建立時間',

'作者介紹',

'劇本名',

'投稿組別',

'劇本大綱',

'是否參與其他競賽',

'劇本發表',

'PDF',

'Word',

'上傳時間'

);

foreach($headers as $value){

$worksheet->setCellValueByColumnAndRow($columnN, $rowN, $value);

$columnN++;

}

$members = $this->competition_m->get_items(false);

$rowN++;

foreach($members as $member){

$data = array(

$member->id,

$member->name,

($member->gender == 'f') ? '女' : '男',

$member->birthday,

$member->phone_country_code,

$member->phone,

$member->country,

$member->attachment,

$member->company,

$member->city,

$member->email,

$member->created_time,

$member->introduction,

$member->title,

$member->language,

$member->summary,

($member->exclusive == 1) ? '是' : '否',

($member->public == 1) ? '是' : '否',

$member->pdf,

$member->word,

$member->upload_time

);

$columnN = 0;

foreach($data as $value){

$worksheet->setCellValueByColumnAndRow($columnN, $rowN, $value);

$columnN++;

}

$rowN++;

}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save($file);

$this->success_response($file_url);

}

常用方法:

Memory Exausted Issue

//Enhance cache of phpexcel
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( &#39; memoryCacheSize &#39; =&gt; &#39;8MB&#39;);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

//Enhance cache of phpexcel

$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;

$cacheSettings = array( ' memoryCacheSize ' => '8MB');

PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

http://jeffath.pixnet.net/blog/post/53919683-phpexcel%E8%A8%98%E6%86%B6%E9%AB%94%E4%BD%BF%E7%94%A8%E9%87%8F%E9%81%8E%E5%A4%A7

Date value from Excel to PHP

$sheet -> getCell(‘A1’) -> getValue()

If the value is a “date”, then this statement will get a “number”.

To convert this number for PHP to use:

//This way will cause incorrect result because of unknown reason
$birth = $sheet->getCellByColumnAndRow(1, $row)->getValue(); 
//PHPExcel will read as GMT+0
$birth = PHPExcel_Shared_Date::ExcelToPHP( $birth );

//If PHP timezone is UTC+0
echo var_dump(date('Y-m-d', $birth));

//If PHP timezone is not UTC+0
echo var_dump(gmdate('Y-m-d', $birth));

//This way will cause incorrect result because of unknown reason

$birth = $sheet->getCellByColumnAndRow(1, $row)->getValue();

//PHPExcel will read as GMT+0

$birth = PHPExcel_Shared_Date::ExcelToPHP( $birth );

//If PHP timezone is UTC+0

echo var_dump(date('Y-m-d', $birth));

//If PHP timezone is not UTC+0

echo var_dump(gmdate('Y-m-d', $birth));

//This way create a formatted string directly
$birth = $sheet->getCellByColumnAndRow(1, $row)->getValue(); 
$birth = PHPExcel_Style_NumberFormat::toFormattedString($birth, "YYYY-M-D");

//This way create a formatted string directly

$birth = $sheet->getCellByColumnAndRow(1, $row)->getValue();

$birth = PHPExcel_Style_NumberFormat::toFormattedString($birth, "YYYY-M-D");

//Get time value only
$cell = $sheet->getCellByColumnAndRow($i, $row_index);
$value = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'hh:mm:ss');

//Get time value only

$cell = $sheet->getCellByColumnAndRow($i, $row_index);

$value = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'hh:mm:ss');

Reference:

Set Widths of Columns

$objPHPExcel-&gt;getActiveSheet()-&gt;getColumnDimension(&#39;B&#39;)-&gt;setAutoSize(true);

$objPHPExcel-&gt;getActiveSheet()-&gt;getColumnDimension(&#39;D&#39;)-&gt;setWidth(12);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

Example:

//Can&#39;t use column index
$column_codes = array(&#39;A&#39;, &#39;B&#39;, &#39;C&#39;, &#39;D&#39;, &#39;E&#39;, &#39;F&#39;, &#39;G&#39;, &#39;H&#39;);
foreach($column_codes as $value){
	$worksheet-&gt;getColumnDimension($value)-&gt;setAutoSize(true);
}

//Can't use column index

$column_codes = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H');

foreach($column_codes as $value){

$worksheet->getColumnDimension($value)->setAutoSize(true);

}

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: PHPExcel

Update Excel Content

Set cell value as string

Get text value of a RichTextObject

Read Excel

PHPExcel Ultimate Example

PHPExcel

Memory Exausted Issue

Date value from Excel to PHP

Set Widths of Columns