Read in Excel with Python and generate random numbers written in Excel

Today is my first time to blog, on the application of Python in Excel as my first article.

The specific requirements are: Read the student's number and name in a known Excel table, then put the data into the new Excel table in the first column and the second column, and then generate a random number as the student's test results.

The first database to be used is: xlwt,xlrd,random these three databases.

The command is as follows:

Import XLWT

Import xlrd

Import Random

A list of existing forms such as:

Now we need to extract the B1-c14.

(Hint: When working on this spreadsheet, use the address of the spreadsheet, which is where the table is stored.) ）

Excel=xlrd.open_workbook (G:\python\ new folder \ Table 1.xlsx ') #打开并读取表格

Sheet=excel.sheets () [0] #在 The original table 1 extracts the data from the first page. When you work with Excel, the row and column pages are calculated starting from 0
WB=XLWT. Workbook () #创立一个新的exce L table
WS =wb.add_sheet (' transcript ') #第一页命名为成绩单
(If you're working on a multi-page operation, you can do this by placing the name of the number in the two pages of the Excel table:

Ws1=wb.add_sheet (' 1 class transcripts ')

Ws2=wb.add_sheet (' 2 class transcripts ')

）

Create a list of two:

A=[]

B=[]

(These two lists are used for temporary storage of school numbers and names)

For I in Range (1,14): Extract name and number from #在大二下学期15级成绩汇总. xlsx table
A. Append (Sheet.row_values (i,1,2)) #提取第i行的第1个数, that is, this command is drawn from the table of the study number column
B. Append (Sheet.row_values (i,2,3)) #提取第i行的第2个数 that this command extracts the Name column from the table

In this special note: Excel rows and columns are calculated starting from 0, that is, the 1th row and 1th column in the table is the No. 0 row No. 0 column when processing is run, and the 2nd row 2nd column in the table is the 1th row 1th column when processing is run. The table sheet is also starting from 0.

To explain: A. Append (Sheet.row_values (i,1,2)) After several errors I have found that I refers to line I, and these two are a range. The 1th value is taken, as noted above, the value of the second box in the table.

If you have questions about this, try changing one of the values to try.

For n in range: #将学号与姓名写入新建的表格中, and written on page 1th, with a population of 13 people.
Ws.write (N,0,a[n][0])
Ws.write (N,1,b[n][0])

Reminder: Why use a[n][0] instead of a[n]? This is why I have a limit on my own level. Because the data in the fetch table is placed in a, B, two list, their form is: a=[[111],[112],[113] ... form, for this solution I have not thought out, so can only rely on a[n][0] such a form to solve. Because the list cannot be placed in the entire box while the new table is being written, it can only be placed in text or in other formats allowed by the box. If you have any better ways to come up with us to exchange learning, grateful.

For q in range: #对1班所有人的成绩进行随机抽取数据
Ran=random.randint (60,91)
If ran<=70:
Ws1.write (q,2, ' Pass ({0}) '. Format (RAN))
If Ran>70 and ran<=80:
Ws1.write (q,2, ' Medium ({0}) '. Format (RAN))
If Ran>80 and ran<=90:
Ws1.write (q,2, ' Good ({0}) '. Format (RAN))

Wb.save (' 15-1, 2-class geographic Information system internship results. xls ') #将新建的表格保存为 ' 15 Capital Ring 1, 2 class GIS internship results. xls ' File

The following code is calculated from two classes of data.

The original table is:

1 ImportXlwt#introduction of the XLWT database for writing data to Excel documents2 ImportXlrd#introduction of XLRD database for reading data from Excel3 ImportRandom#introduction of random Database4 5Excel=xlrd.open_workbook ('G:\python\ new folder \ Table 1.xlsx')#extract the corresponding number and name from an Excel table with an existing number and name6 7sheet=excel.sheets () [0]8WB=XLWT. Workbook ()#Create a new Excel table9Ws1=wb.add_sheet ('1 class Transcripts')#The first page is named 1 class transcriptsTenWs2=wb.add_sheet ('2 class Transcripts')#The first page is named 2 class transcripts One  A  -A1=[]#Table 1 of the study number column -A2=[]#Table 2 of the study number column theB1=[]#the Name column in table 1 -B2=[]#the Name column in table 2 -  -  forIinchRange (1,14):#table 1.xlsx extracts the name and number of Class 1 +A1.append (Sheet.row_values (i,1,2)) -B1.append (Sheet.row_values (i,2,3)) +  A        at  forJinchRange (14,33):#extract the name and number of Class 1 in table 1.xlsx -A2.append (Sheet.row_values (j,1,2)) -B2.append (Sheet.row_values (j,2,3)) -        -  forNinchRange (13):#Write the 1 class number and name to the new form, and write it on page 1th. 1 class number is 13 people - Ws1.write (n,0,a1[n][0]) inWs1.write (n,1, b1[n][0]) -  forMinchRange (19):#Write the 2 class number and name to the new form, and write it on page 2nd. 2 class number is 19 people to Ws2.write (m,0,a2[m][0]) +Ws2.write (m,1, b2[m][0]) -  the  forQinchRange (13):#random data on the results of all 1 classes *Ran=random.randint (60,91)#score is between 60-90 $       ifRan<=70:Panax NotoginsengWs1.write (q,2,'Pass ({0})'. Format (RAN))#The following represent the levels in each fractional segment -       ifRan>70 andRan<=80: theWs1.write (q,2,'medium ({0})'. Format (RAN)) +       ifRan>80 andRan<=90: AWs1.write (q,2,'good ({0})'. Format (RAN)) the  +  forDinchRange (19):#random data on the results of all 2 classes -Ran=random.randint (60,91) $       ifRan<=70: $Ws2.write (d,2,'Pass ({0})'. Format (RAN)) -       ifRan>70 andRan<=80: -Ws2.write (d,2,'medium ({0})'. Format (RAN)) the       ifRan>80 andRan<=90: -Ws2.write (d,2,'good ({0})'. Format (RAN))Wuyi  the        -  WuWb.save ('15 Capital Ring 1, 2 classes of geographical information system internship results. xls')#Save the newly created table as ' 15-1, 2-class geographic Information system internship results. xls ' File -       

The results are as follows:

Read in Excel with Python and generate random numbers written in Excel