[PYTHON] Memorandum (in openpyxl ① copy and paste from another book ② refer to the comparison table)

Overview

The second step in the previous article, "Add setting 1 / setting 2 to each row using the comparison table in Table 1" was implemented for the time being, so I will summarize it. https://qiita.com/wellwell3176/items/7dba981b479c5933bf5f

Outcome

Succeeded in automatically generating the results shown in Fig. 2 from the raw data and comparison table shown in Fig. 1. Figure 1 Left: Raw data Right: Comparison table Figure 2 Results

program

import openpyxl
wb1=openpyxl.load_workbook('/content/drive/My Drive/Colab Notebooks/data3.xlsx')
ws1=wb1.active

#Prepare a separate comparison table as a master. I want to pull the numerical value from the master side instead of the formula, so data_only=Open with true
wb2=openpyxl.load_workbook('/content/drive/My Drive/Colab Notebooks/table.xlsx',data_only=True)
ws2=wb2.active

for i in range(12):
  for j in range(3):
    copy = ws2.cell(row = i+1, column = j+3).value
    ws1.cell(row = i+1, column =j+10,value=copy)

#The reference from another book did not go well, so copy the comparison table once
#range is i=It starts from 0, but the rows and columns start from 1, so adjust

min_row=2
max_row=ws1.max_row #Get the number of lines because processing is applied from the second line to the last line

ws1["H1"].value="Setting 1"
ws1["I1"].value="Setting 2" #The heading was faster to enter

for i in range(min_row, max_row):
  ws1.cell(row=i, column=7).value ="=D{}&E{}".format(i,i)
  #Enter category + business in the 7th column
  ws1.cell(row=i, column=8).value ="=INDEX($J$1:$L$12,match(G{},$J$1:$J$12,0),2)".format(i) 
  ws1.cell(row=i, column=9).value ="=INDEX($J$1:$L$12,match(G{},$J$1:$J$12,0),3)".format(i) 
  #Use INDEX to query the lookup table and column 7. Enter the result

wb1.save('/content/drive/My Drive/Colab Notebooks/data4.xlsx')

problem

-I want to delete the G column, J column, K column, and L column in Fig. 2 because they are unnecessary in the subsequent process, but I cannot delete them because I am using a reference.

⇨ As a brute force solution, you can open data4 with data_only = true and copy and paste the columns other than unnecessary columns into a new file, but I feel that it is useless.

・ I don't know how to refer to another book

This time I copied the contents of table.xlsx to data4.xlsx and then used the INDEX function, It is better to refer to table.xlsx from the beginning ... but I couldn't find a description method. It will be the next task.