Use Excel to format diploma numbers

Every year when the college students graduate, we must according to the provincial Education Department's request, according to the unified format to report the Graduate registration form. Now, most schools use Excel to carry out the daily statistics of student data, students will be graduated when the subtotal, but the layout of the Diploma in Excel can not achieve, how to do?

The problems that exist

Here we take "Liaoning Province adult Higher education graduates record registration form" as an example. In "Liaoning Province adult Higher education graduate student achievement Registration Form", the graduation certificate's number request must be the 17-digit sequential code. Many people naturally think that you can use the Fill function of the automatic sequence in Excel, but when we enter the 17-digit diploma in the cell, it is displayed as "1.17795E+16", because in Excel, the numeric data is expressed exponentially when it exceeds 12 digits.

We then defined the data type of the entire column of cells as text and entered two consecutive diploma numbers in two consecutive cells. But when we drag the fill handle, the diploma number is not encoded as we would like it to be, but the two consecutive diploma numbers appear alternately. This is a miserable, more than 2000 graduates of the diploma number to one input? Before the manual operation of the time we are concentrated in the school to write a better person a write. One of the purposes of using computers is to improve efficiency, there must be a way, but we have not found.

Solving method

We take this idea, after repeated pondering, repeatedly tried, finally found a simple way. This method is summarized for your reference.

1. In Excel 2000, edit the registration form in addition to the diploma number. The header section does not merge cells and is arranged in a single field name. Then save the worksheet as a "*.dbf" file. In this example we save the file in D:byzs and the filename is zsbh.dbf. In VFP 6.0 open this table, the "diploma number" is defined as a character, 20 width.

2. In VFP 6.0, the following small programs are programmed:

Use d:byzszsbh.dbf '打开.dbf文件
M=117795200 '按标准格式的要求，证书编号的前9位值是固定不变的
N=20600647 '第一个证书编号
Go 1
Do while .not.eof() '使用循环语句，逐条记录替换
Repl 毕业证书号with str(m,9)+str(n,8) '将数值型数据转化成字符型
N=N+1
Skip
Enddo


Run the program, and then open the table to view, we are pleasantly surprised to find that the certificate number has been encoded according to our requirements. In this program, we cut the diploma number into two paragraphs and represented it in two variables. If a variable is used to indicate that the program can be changed to the following form, the arrangement of the diploma number becomes the form of scientific notation. So in the actual layout of the diploma number, according to the number of changes in the number decomposition.

Use d:byzszsbh.dbf
M=11779520020600647
Go 1
Do while .not.eof()
Repl zsbh with str(m,9)
m=m+1
Skip
Enddo

3. In VFP 6.0, select the "Export" command in the File menu, export "*.dbf", and export the type "Microsoft Excel 5.0 (XLS)" file. Saved under D:byzs, the file name is Djb.xls.

4. In Excel 2000, open the Excel file Djb.xls exported in VFP 6.0. Edit the table head according to the requirements of Provincial Education Department.

Using both Excel 2000 and VFP 6.0 software, you can not only arrange your diploma number. Other numbers such as the ticket number arrangement can also be implemented in accordance with the above method.