PHP: Numeric to Excel column header

Self-turning personal blog: wide flying http://www.rexcao.net/archives/169

A period of time to upgrade the Excel export of a project, this time the number of more than 60, in the process of finding a problem, the original well-done digital to Excel column head function now only to the AZ column to the end, it is obviously not enough ah, and then carefully review, found that The original AZ column after the content displayed to the AAA column above, and then looked at the original code to find that the original logic is wrong!

My original error logic is this: A-z,z down is Aa,aa-az,az down is AAA, down is aaaa in turn ... But excel in Az down is bz! Once you're aware of this, start modifying your original code.

Stage One

The number to Excel column header, the goal is to provide any number, it through a fixed method to Excel column header, in this, I am going to 26-digit as the critical value of rounding, take the remainder to judge, and later found that some around, simply as 26 into the processing.

Phase II

As 26 binary to deal with, toss for a while and find a new problem, for Excel column header, its unit of calculation is a-Z, with the decimal analogy, the decimal number is the 0-9,excel column head Z of the next one is AA, and the next one in decimal 9 is 10, if a represents 0 , Excel column Head Z is equivalent to the largest decimal unit 9, but the decimal 10 of the upper is 1, it represents not yet have a value, and look at the Excel column header AA of the upper, is a,a represents 0, that means the upper no value? Obviously unreasonable, so I found that this can not be treated purely as 26 binary.

Stage Three

After the above two stages of tossing, I decided to adopt another approach, the general idea is as follows:

1. Instead of calculating the column header letters corresponding to each number, calculate the specified number of column headers (for example, 2 is a, B, 3 is a, B, C, and so on).

2, after each plus 1, the current letter is also added 1 (equivalent to 1+1=2,a+1=b).

3. The result of the calculation is the stitching result of multiple letters (a stitching a=aa,a stitching b=ab).

4, low and 1 of the result is greater than Z, the upward position into one, after the upper if also greater than Z, continue upward into a ... (Bubbles up)

Results

Now that the idea is clear, you can look at the implementation.

Header (' content-type:text/html; Charset=utf-8 '); Echo ' Convert number to title of excel.<br> '; function Showarr ($    ARR) {echo ' <pre> ';    Print_r ($arr); Echo ' </pre><br> ';} $ea = new Excelassistant (), $test 1 = $ea->getexceltit, Showarr ($test 1);/** * Excel Assistant * @author Rexcao 2015-01-08 */cl  excelassistant{Private $carr = Array ();//result array, initial value is a private $curlet = ' a ';//current end letter, initialized to A private $CURLETPI = 0;//the current string from right-to-left digits for upper recursion plus 1 processing (starting from 0) Private $tmpar = Array (' A ');//temporary array that stores characters for each bit on the result string. The initial value is ' a ' private static $a = Array (' A ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G ', ' H ', ' I ', ' J ', ' K ', ' L ', ' M ', ' N ', ' O ', ' P ', ' Q ', ' R ', ' S ', ' T ', '    U ', ' V ', ' W ', ' X ', ' Y ', ' Z '); private static $b = Array (' A ' =>0, ' B ' =>1, ' C ' =>2, ' D ' =>3, ' E ' =>4, ' F ' =>5, ' G ' =>6, ' H ' =>7, ' I ' = 8, ' J ' =>9, ' K ' =>10, ' L ' =>11, ' M ' =>12, ' N ' =>13, ' O ' =>14, ' P ' =>15, ' Q ' =>16, ' R ' =>17, ' S ' =>18        , ' T ' =>19, ' U ' =>20, ' V ' =>21, ' W ' =>22, ' X ' =>23, ' Y ' =>24, ' Z ' =>25); /** * Get exCel column header * @param $num total number of columns */Public Function Getexceltit ($num) {$i = 0; while ($i < $num) {$this-&GT;CURLETPI = 0;//does not handle the upper position, only the current bit $this->tmpar[count ($this->tmpar) is processed-            1] = $this->curlet;            $this->carr[] = implode ("', $this->tmpar);                        $this->curlet = $this->getnextletter ($this->curlet);                if ($this->curlet = = ' a ') {//description over a lap, the upward bit is recursively added by 1 $this->curletpi++;//start with the one on the left side of the current bit.            $this->recursiveaddup ();        } $i + +;    } return $this->carr;        }/** * Gets the lower letter according to the letter * A-Z loop */Public Function Getnextletter ($l) {$k = self:: $b [$l];//Current Alphabetical Index        $k ++;//Lower Alphabetical index if ($k = =) {$l = ' A ';//invert}else{$l = self:: $a [$k];    } return $l;  }/** * Recursion up position plus a * here, only once the result of a calculation is a, will add 1, * but not each bit added to the upper bubble, so you cannot traverse each bit * @author Rexcao */  Public Function Recursiveaddup () {//update the right-most letter first $this->tmpar[count ($this->tmpar)-1] = ' A ';        if ($this->curletpi+1>count ($this->tmpar)) {$this->tmpar = array_merge (Array (' A '), $this->tmpar); }else{$cl = $this->tmpar[count ($this->tmpar)-$this->curletpi-1];//The letter of the current bit $CL = $thi            S->getnextletter ($CL);                if ($cl = = ' a ') {$this->tmpar[count ($this->tmpar)-$this->curletpi-1] = ' a ';//to deal with the higher, first update the standard            $this->curletpi++;//another $this->recursiveaddup ();            }else{//Update the current bit with a new letter can be $this->tmpar[count ($this->tmpar)-$this->curletpi-1] = $CL; }        }    }}

Interested to see the results of the operation, you can go to my personal blog to see ('? ') ). Wide http://www.rexcao.net/archives/169

PHP: Numeric to Excel column header