Execl in Python to implement automatic table filling and pythonexecl

Execl in Python to implement automatic table filling and pythonexecl

Task Description:

Table 1 is a simple data table with 110 rows and 25 columns. The first row is the header, such as "owner", "item", and "term. Rows 3-2nd are the corresponding data, such as "Zhang San", "moving bricks", and "3 days ".

The table (table 2) You want to create is 110 tables (just like your resume and put it in an excel file). Each table contains 9 rows in a fixed format, for example, "name", "item", and "term", the data is the same as that in Table 1, but the format and sequence are different and are classified by individual.

The current task is to copy the corresponding data from table 1 and fill it in table 2. If you copy and paste the data manually, a total of 110x25 = 2750 data records, it would be a waste of tears.

Therefore, I wrote a program using Python and used the xlrd module (used to read Table 1) xlwt module (used to generate table 2) to complete this task.

Because it involves row number judgment (1-9 rows are the first table, 10-18 rows are the second table, and 19-27 rows are the third table ......), The switch-case statement is the most suitable, but this control statement does not exist in Python. You can use a dictionary (dict) to complete this process control, but you are not familiar with it. In order to quickly complete the task, the if-else statement is used directly (as a result, up to 8 if-else nesting ...)

Therefore, paste the rough code written in a rush. If you have a good method to replace the switch-case statement, I hope you will not be enlightened.

#-*-Coding: cp936-*-import xlrdimport xlwtxls1 = xlrd. open_workbook ("d :\\ xls \ 1.xls") mysheet1 = xls1.sheet _ by_name (" Sheet1 ") # locate the worksheet named sheet1. Case Sensitive print ("Table 1 has % d rows and % d columns. "% (Mysheet1.nrows, mysheet1.ncols) xls2 = xlwt. workbook () mysheet2 = xls2.add _ sheet ('sheetb') # create xls number 2 and write data to SheetB # obtain Table 1 header titlelist = mysheet1.row _ values (0) # operations on each row. generate 110 sub-tables title0 = 'xxxxx sub-tables 'titlex = 'xxxx flowchart (xxxx) 'mysheet2. write (0, 0, title0.decode ('gbk') mysheet2.write (0, 8, titlex. decode ('gbk') for x in range (0,110): # Table 1 contains 110 rows of Data titlelist1 = mysheet1.row _ values (x + 1) # The x + 1 row of data is obtained for each loop. X = x * 9 print x for row in range (x + 1, x + 9): # Every 9 acts a group, so the x of the last row is multiplied by 9. If row = x + 1: # if it is the first row of each group for I in range (0, 3 ): # [enter the actual number of columns in row x + 1 (that is, the first row in each group)] mysheet2.write (row, I * 2, titlelist [I]) # mysheet2.write (row, I * 2 + 1, titlelist1 [I]) # else of data written to row x + 1 of table 1 in an even column: if row = x + 2: # if the second row of each group is mysheet2.write (row, 0, titlelist [3]) # because the second row only has two grids, there is no loop, write only 0th columns and 1st columns mysheet2.write (row, 1, titlelist1 [3]) else: if row = x + 3: # if it is the third row of each group mysheet2.write (row, 0, titlelist [4]) # There are only two null rows in the third row. No Loops are required. Only 0th columns and 1st columns are written. Mysheet2.write (row, 1, titlelist1 [4]) else: if row = x + 4: for I in range (0, 4): mysheet2.write (row, I * 2, titlelist [I + 5]) mysheet2.write (row, I * 2 + 1, titlelist1 [I + 5]) else: if row = x + 5: for I in range (): mysheet2.write (row, I * 2, titlelist [I + 9]) # for each write, the number of columns is pushed back to 4. For example, 5, 9, 13, 17, 21... Mysheet2.write (row, I * 2 + 1, titlelist1 [I + 9]) # Because "for I in range ():", write four columns of else at a time: if row = x + 6: for I in range (0, 4): mysheet2.write (row, I * 2, titlelist [I + 13]) mysheet2.write (row, I * 2 + 1, titlelist1 [I + 13]) else: if row = x + 7: for I in range (0, 4): mysheet2.write (row, I * 2, titlelist [I + 17]) mysheet2.write (row, I * 2 + 1, titlelist1 [I + 17]) else: if row = x + 8: for I in range (): # If the eighth row of each group is mysheet2.write (row, I * 2, title List [I + 21]) # The number of columns is pushed 4 after each write. For example, 5, 9, 13, 17, 21... Mysheet2.write (row, I * 2 + 1, titlelist1 [I + 21]) xls2.save('auto.xls ') Save the final result of filling as auto.xls