Python operation execl for automated form filling

Mission Brief:

Table 1 is a simple data table with a total of 110 rows and 25 columns. The 1th line is the header, such as "responsible person", "matter", "deadline" and so on. Line 2nd-line 110th is the corresponding data, such as "Zhang San", "Moving Bricks", "3 days".

The table you want to do (table 2) is 110 tables (like a resume, placed in an Excel file), each table a total of 9 lines, there is a fixed format, such as "name" "Matter" "term" and so on, the data and table 1, but the format and the order of different, according to individual classification.

The current task is to copy the corresponding data from table 1 and fill in table 2. If manual copy and paste, total 110x25=2750 data, simply tired of crying.

So using Python to write a program, with the XLRD module (used to read table 1) XLWT module (used to generate table 2) to do this work.

Because the row count is involved (1-9 rows are the first table, 10-18 rows are the second table, and 19-27 rows are the third table ...). , it is most suitable to use the Switch-case statement, but Python does not have this control statement, the dictionary (dict) can also complete this process control, but not very familiar. In order to quickly complete the task, it is directly using the If-else statement (resulting in the result is up to 8 if-else nesting ... ）

So paste this paragraph hastily write the rough code, if there is a good way to replace the Switch-case statement, but also hope that you do not hesitate to enlighten.

#-*-coding:cp936-*-ImportxlrdImportXLWTXLS1=xlrd.open_workbook ("D:\\xls\\1.xls") Mysheet1=xls1.sheet_by_name ("Sheet1")#find a worksheet named Sheet1. Case SensitivePrint("Table 1total%d rows,%d columns. "%(mysheet1.nrows,mysheet1.ncols)) Xls2=XLWT. Workbook () Mysheet2= Xls2.add_sheet ('Sheetb')#build 2nd xls and write data to Sheetb#get the table header for table 1titlelist=mysheet1.row_values (0)#the actions of each row. Generating 110 sub-tablesTitle0='XXXXX Matters Sub-table'Titlex='xxxx flowchart (xxxx)'Mysheet2.write (0,0,title0.decode ('GBK')) mysheet2.write (0,8,titlex.decode ('GBK')) forXinchRange (0,110):#Table 1 Total 110 rows of dataTitlelist1=mysheet1.row_values (x+1)#each cycle is given the x+1 row of data. X=x*9Printx forRowinchRange (X+1,X+9):#x multiplied by 9 for each row of the 9 behavior group.                 ifRow==x+1:#if it is the first row of each group                     forIinchRange (0,3):#"Fill in the actual number of columns for line x+1 (that is, the first row of each group)"Mysheet2.write (Row,i*2,titlelist[i])#even-numbered columns write to table header dataMysheet2.write (Row,i*2+1,titlelist1[i])#The odd sequence of data written to row x+1 of table 1                Else:                    ifRow==x+2:#If this is the second row of each groupMysheet2.write (Row,0,titlelist[3])#because there are only two squares in the second row, no loops, only No. 0 and 1th columns are writtenMysheet2.write (row,1,titlelist1[3])                    Else:                        ifRow==x+3:#If this is the third row of each groupMysheet2.write (Row,0,titlelist[4])#the third line is only two empty, no loops, write only columns No. 0 and 1thMysheet2.write (row,1,titlelist1[4])                                                    Else:                            ifRow==x+4:                                 forIinchRange (0,4): Mysheet2.write (Row,i*2,titlelist[i+5]) mysheet2.write (row,i*2+1,titlelist1[i+5])                            Else:                                ifRow==x+5:                                     forIinchRange (0,4): Mysheet2.write (Row,i*2,TITLELIST[I+9])#each time you write, the number of columns pushes backwards by 4. such as 5,9,13,17,21 ...Mysheet2.write (Row,i*2+1,titlelist1[i+9])#Write 4 columns at a time because "for I in Range (0,4):"                                Else:                                    ifRow==x+6:                                         forIinchRange (0,4): Mysheet2.write (Row,i*2,titlelist[i+13]) mysheet2.write (row,i*2+1,titlelist1[i+13])                                    Else:                                        ifRow==x+7:                                             forIinchRange (0,4): Mysheet2.write (Row,i*2,titlelist[i+17]) mysheet2.write (row,i*2+1,titlelist1[i+17])                                        Else:                                            ifRow==x+8:                                                 forIinchRange (0,4):#If this is the eighth row of each groupMysheet2.write (row,i*2,titlelist[i+21])#each time you write, the number of columns pushes backwards by 4. such as 5,9,13,17,21 ...Mysheet2.write (row,i*2+1,titlelist1[i+21]) Xls2.save ("/ c4>'Auto.xls')#Save the filled result as Auto.xls

Python operation execl for automated form filling