Yii Framework Forum: cara baca varchar date excel - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

cara baca varchar date excel cara baca varchar date excel Rate Topic: -----

#1 User is offline   epi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 06-April 11

Posted 06 April 2011 - 01:27 AM

hi agan2

gmn sih cara baca varchar date excel di yii ?
jadi ana punya field tanggal yang tipenya varchar dan diisi nilai varchar dari date di excel. misal angka ini 38872.5377571528 kalau kita converrt di excel ke date jadinya 6/4/2006 tapi data di mysql = 38872.5377571528.
berikut kode view di _view.php
	<b><?php echo CHtml::encode($data->getAttributeLabel('tanggalinput')); ?>:</b>
	<?php echo CHtml::encode($data->tanggalinput); ?>
	<br />

ana udah coba pake strtotim, date, str_to_date ga ada yang bisa gan.
tulongin donk :)
0

#2 User is offline   junxiong 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 541
  • Joined: 21-June 10

Posted 06 April 2011 - 08:46 PM

View Postepi, on 06 April 2011 - 01:27 AM, said:

hi agan2

gmn sih cara baca varchar date excel di yii ?
jadi ana punya field tanggal yang tipenya varchar dan diisi nilai varchar dari date di excel. misal angka ini 38872.5377571528 kalau kita converrt di excel ke date jadinya 6/4/2006 tapi data di mysql = 38872.5377571528.
berikut kode view di _view.php
	<b><?php echo CHtml::encode($data->getAttributeLabel('tanggalinput')); ?>:</b>
	<?php echo CHtml::encode($data->tanggalinput); ?>
	<br />

ana udah coba pake strtotim, date, str_to_date ga ada yang bisa gan.
tulongin donk :)


mgkin bisa coba pakai rumus ini?
function xl2timestamp($xl_date)
{
$timestamp = ($xl - 25569) * 86400;
return $timestamp;
}

“The most likely way for the world to be destroyed, most experts argue, is by accident. That’s where we come in; we’re computer professionals. We cause accidents.” - Nathaniel Borenstein

Yii Playground : Collaborative demo apps. You can join to improve it too!
My Team's Blog: In Indonesian.
0

#3 User is offline   epi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 06-April 11

Posted 07 April 2011 - 03:30 AM

makasih jawabannya gan,
solusi ini udah ana coba dan tak berhasil.
jadi solusinya begini :
- tambahkan kelas ini di folder components
	<?php
    //Only Microsoft would do something this stupid:
    class ExcelDate{
			public static function excel_date($serial){
				
				// Excel/Lotus 123 have a bug with 29-02-1900. 1900 is not a
				// leap year, but Excel/Lotus 123 think it is...
				if ($serial == 60) {
					$day = 29;
					$month = 2;
					$year = 1900;
					
					return sprintf('%02d/%02d/%04d', $month, $day, $year);
				}
				else if ($serial < 60) {
					// Because of the 29-02-1900 bug, any serial date 
					// under 60 is one off... Compensate.
					$serial++;
				}
				
				// Modified Julian to DMY calculation with an addition of 2415019
				$l = $serial + 68569 + 2415019;
				$n = floor(( 4 * $l ) / 146097);
				$l = $l - floor(( 146097 * $n + 3 ) / 4);
				$i = floor(( 4000 * ( $l + 1 ) ) / 1461001);
				$l = $l - floor(( 1461 * $i ) / 4) + 31;
				$j = floor(( 80 * $l ) / 2447);
				$day = $l - floor(( 2447 * $j ) / 80);
				$l = floor($j / 11);
				$month = $j + 2 - ( 12 * $l );
				$year = 100 * ( $n - 49 ) + $i + $l;
				return sprintf('%02d/%02d/%04d', $month, $day, $year);
			}
	}
?>


lalu access dari _view.php dengan cara ExcelDate::excel_date(38872.5377571528) maka hasilnya akan jadi tanggal.
sepertinya ana salah tanya, ini bukan masalah yii tapi masalah php baca serial excel date :)
maklum newbie gan. hhahhaa...

OK Problem solved. Anyway, thanks everybody !

View Postjunxiong, on 06 April 2011 - 08:46 PM, said:

mgkin bisa coba pakai rumus ini?
function xl2timestamp($xl_date)
{
$timestamp = ($xl - 25569) * 86400;
return $timestamp;
}


0

#4 User is offline   junxiong 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 541
  • Joined: 21-June 10

Posted 07 April 2011 - 09:12 PM

Sep2.. baguslah kalau udah solved :)
“The most likely way for the world to be destroyed, most experts argue, is by accident. That’s where we come in; we’re computer professionals. We cause accidents.” - Nathaniel Borenstein

Yii Playground : Collaborative demo apps. You can join to improve it too!
My Team's Blog: In Indonesian.
0

#5 User is offline   epi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 06-April 11

Posted 11 April 2011 - 12:34 AM

gan, ane bingung nih, cara bacanya di gridview gimana ya ?
nampilin sih bisa kalau divaluenya, tapi untuk searchnya gimana ya ?

ini kode ane di view admin.php:
array(
                    'name'=>'cchbegin',
                    'value'=>'ExcelDate::excel_date($data->cchbegin)',
                ),

ini kode di modelnya :
$criteria->compare('cchbegin',$this->cchbegin,true);


gimana caranya supaya yang dicompare itu tanggalnya bukan valuenya ?

pusing ana ngerjain proyek yang data sebelumnya diisi serial date excel. hhahaha
bantu ya gan.
thanks

View Postjunxiong, on 07 April 2011 - 09:12 PM, said:

Sep2.. baguslah kalau udah solved :)

0

#6 User is offline   junxiong 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 541
  • Joined: 21-June 10

Posted 11 April 2011 - 09:48 PM

ane gak yakin, tapi kalau pakai cara begini di search() jalan gak?
$criteria->compare('cchbegin',ExcelDate::excel_date($this->cchbegin),true);

“The most likely way for the world to be destroyed, most experts argue, is by accident. That’s where we come in; we’re computer professionals. We cause accidents.” - Nathaniel Borenstein

Yii Playground : Collaborative demo apps. You can join to improve it too!
My Team's Blog: In Indonesian.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users