Back to Top

phpexcel – converting cell to an excel address string by row and column number

This is for users who, for whatever reason cannot or don’t want to use the PHPExcel_Cell::stringFromColumnIndex($startColumn) function in phpexcel.. alternative method.

(1) PHPexcel built in function (static object method)

// use echo addressbyrowcol(10,2); would return B10

function addressbyrowcol($row,$col) {
return PHPExcel_Cell::stringFromColumnIndex($col).$row;

(2) Alternative code

Useful functions to quickly convert a cell by row and column number to an absolute excel address:

// use echo addressbyrowcol(10,2); would return B10

function addressbyrowcol($row,$col) {
return getColFromNumber($col).$row;

function getColFromNumber($num) {
    $numeric = ($num - 1) % 26;
    $letter = chr(65 + $numeric);
    $num2 = intval(($num - 1) / 26);
    if ($num2 > 0) {
        return getColFromNumber($num2) . $letter;
    } else {
        return $letter;

Posted in ExcelPHP |

Calculating gamma and cumulative gamma distributions in PHP

PHP code to calculate gamma distributions and cumul gamma distributions – similar to excel’s gammadist() functions.

function getgamma($x, $k = 1, $theta = 1) {
		return pow($x, $k - 1)*exp(-$x/$theta)/(gamma($k)*pow($theta, $k));

function getcumulgamma($x, $k = 1, $theta = 1) {
		return lowerGamma($k, $x/$theta)/gamma($k);

function lowerGamma($s, $x) {
		//Special thanks to for this algorithm.
		if ($x == 0) return 0;
		$t = exp($s*log($x)) / $s;
		$v = $t;
		for ($k = 1; $k < 150; ++$k) {
			$t = -$t * $x * ($s + $k - 1) / (($s + $k) * $k);
			$v += $t;
			if (abs($t) < 0.00000000001) break;
		return $v;

function gamma($x) {
		if ($x <= 0) {
		return NAN;
		} else {
			return sqrt(2*M_PI/$x)*pow((1/M_E)*($x+(1/(12*$x - 1/(10*$x)))), $x);

Posted in PHP |

Replacing youtube object embeds

Since google/ youtube pulled object/embed support for embedded youtube videos, here is a wordpress filter for attempting to replace object embeds with video specifiers for use in other plugins (code could be adjusted for direct iframes too), it checks to see if an iframe or youtube reference already exists as it builds up an array of youtube urls – only uses first youtube url found. It assumes any youtube urls are in . Maybe of use to somebody.

function replaceyoutubeobject($string) {
    if(stripos($string,'iframe')===false) {
    if(stripos($string,'+<\/object>/smi", "", $string); // remove old objects
    $match = $matches[0];
    $url = $match[0];
    if(stripos($url,"/v/")!==false) {
    } else if(stripos($url,"v=")!==false) {
    $string = "
\n".$string; }}} return $string; } add_filter( 'the_content', 'replaceyoutubeobject' );
Posted in Wordpress |

Fixing wordpress admin ‘white/blank dashboards’

If you log into admin dashboard via wordpress and are faced with blank/white dashboard areas, try the following fix.

Open file blog/wp-admin/includes/screen.php in your favorite text editor.
On line 706 or close by (depending on version) find the following PHP statement: echo self::$this->_help_sidebar;
Replace it with the statement: echo $this->_help_sidebar;
Save your changes.
Go to a dashboard page.
Posted in Wordpress |

Setting Excel font text color to be readable by background color

Below is a small snippet of VBA code for setting cell background color and cell text color based on RGB values (R – red color, 0 to 255, B – blue color, 0 to 255, and G – green color, 0 to 255) helps make cells text readable, based on background color eg avoids dark text on say red background cells.. especially for gantt chart makers.. you know who you are… 🙂

Brightness = ((0.241 * (R * R)) + (0.691 * (G * G)) + (0.068 * (B * B)))
Worksheets(1).Cells(row, col).Interior.Color = RGB(R, G, B)
If Brightness < 16900 Then
Worksheets(1).Cells(row, col).Font.Color = RGB(255, 255, 255)
Worksheets(1).Cells(row, col).Font.Color = RGB(0, 0, 0)
End If

Note: row is the cell row number, and col is the cell column number.

Posted in Excel VBA |

Redirecting yahoo emails for contract form 7 plugin

Sometimes yahoo blocks server smtp email checks, so emails aren’t sent out to none yahoo addresses. This can be solved using the code below (tested for wordpress 3.9, and latest version of contact form 7 plugin):

if (!function_exists('cf7_redirect_yahoo')) {

function cf7_redirect_yahoo($cf7)

       $domain = get_option("siteurl");
       $domain = str_ireplace("http://","",$domain);
       $domain = str_ireplace("www.","",$domain);
       $mail = $cf7->prop('mail');

       $submission = WPCF7_Submission::get_instance();
       if($submission) {
       $posted_data = $submission->get_posted_data();
       foreach ($posted_data as $keyval => $posted) {
       if(stripos($keyval,'email')!==false) {
       if(stripos($posted,'yahoo')!==false) {
       $mail['sender'] = $nmail;
       $cf7->set_properties( array( 'mail' => $mail ) );

       return $cf7;
Posted in Wordpress |

Fixing wpcf7_before_send_mail() hooks – Contact Form 7 Plugin

In wordpress Contact Form Plugin version 3.9 onwards the structure of the plugin changed, and this has had a major impact on the wpcf7_before_send_mail() hook usage. If you now want to access the email posted data – keys and post value, below shows the old method and then the new method. Hope it helps someone. Note that the posted_data object property no longer exists.

Old Method:

add_action("wpcf7_before_send_mail", "my_function");

function my_function(&$cf7)

foreach ($cf7->posted_data as $keyval => $posted) {
// use $keyval and $posted as elements in email forms


New Method:

add_action("wpcf7_before_send_mail", "my_function");

function my_function($cf7)
       $submission = WPCF7_Submission::get_instance();
       if($submission) {
       $posted_data = $submission->get_posted_data();
       foreach ($posted_data as $keyval => $posted) {
       // use $keyval and $posted as elements in email forms
Posted in Wordpress |

Alternative to robots.txt

Many web spiders and especially ‘dodgy’ content bots do not respect the robots.txt file.. below is some code which can be added to your .htaccess file which will help block bots which use user_agents. Banning via ip address although useful is a bit of a losing battle, as originator can just switch to another proxy.

## Bot Protection
RewriteCond %{HTTP_USER_AGENT} (Access|appid) [NC,OR]
RewriteCond %{HTTP_USER_AGENT} (Capture|Client|Copy|crawl) [NC,OR]
RewriteCond %{HTTP_USER_AGENT} (Data|devSoft|Domain|download) [NC,OR]
RewriteCond %{HTTP_USER_AGENT} (Engine|fetch|filter|genieo) [NC,OR]
RewriteCond %{HTTP_USER_AGENT} (Jakarta|Java|Library|link|wsr-agent|MJ12bot|SeznamBot) [NC,OR]
RewriteCond %{HTTP_USER_AGENT} (AhrefsBot|MJ12bot|nutch|Preview|Proxy|Publish|Kraken|Baiduspider) [NC,OR]
RewriteCond %{HTTP_USER_AGENT} (scraper|spider) [NC,OR]
RewriteCond %{HTTP_USER_AGENT} (Win32|WinHttp) [NC]
RewriteRule .* - [F] 
## End Bot Protection
Posted in Firewall |