Today's purpose

--Get the data written in the opened Excel and save it in another Excel.

Folder structure

root/ 　├ code/ │ └ Execution sources 　└ data/ └ Excel you want to open

Point-like thing

--Operate Excel

import

openpyxl

I will try it for the time being

--Excel operation --Open Excel --Existing file example: wb = openpyxl.load_workbook (targetExcel, data_only = True) --New file example: wb2 = openpyxl.Workbook () --targetExcel is the target Excel (with path) --Enter data_only = True because you want the calculation result in Excel. --Save --Example: wb2.save (newFileName) --newFileName is the file name --The same applies to saving an existing file. If it has the same name, it will be overwritten, and if it has a different name, it will be saved as a different name.

--Access Sheet in Excel. --Example 1: ws = wb [targetSheetName] --Example 2: ws = wb.worksheets [0] --targetSheetName is the sheet name --Even if the sheet name is unknown, you can access it by specifying the sheet number as shown in Example 2.

Summary

――It looks like you will have a hard time until you get used to it. .. I'll do my best.

reference

--I'll put an excel image and a straightforward code.

--"Sales.xlsx" image エクセルイメージ.png

`The code I wrote`

import sys
import os
import openpyxl

#It is not actual sales data.
#I don't want to write directly.
targetExcel = r"../data/Sales.xlsx"
targetSheetName = "Last month sales"
newFileName = "new.xlsx"
newSheetName = "test"
targetRow = 5
maxCellNum = 6

#Excel open
# data_only=True gives the result of the expression. Without it, the formula itself can be taken.
wb = openpyxl.load_workbook(targetExcel, data_only=True)

#Access data in excel
ws = wb[targetSheetName]

#An empty list
datalist = []

#Put the data of the target row into the list from the opened Excel
for num in range(maxCellNum):
    datalist.append(ws.cell(targetRow, num+1).value)
    print(datalist[num])

#Good new excel
wb2 = openpyxl.Workbook()
ws2 = wb2.worksheets[0]
ws2.title = newSheetName

#Set in the same position as the original excel
for num in range(maxCellNum):
    ws2.cell(targetRow, num+1).value = datalist[num]

#Mortise
wb2.save(newFileName)

Postscript: The title has been updated.

Operate Excel with Python (1)