Operate Excel with Python (2)

Today's purpose

--Get the data written in the opened Excel and save it in another Excel. --Try how to write when you do not know the number of data.

Point-like thing

--Operate Excel (see last time) --How to collect data --Two-dimensional array (list) when the number of elements is not fixed

I will try it for the time being

--How to collect data --Example of data acquisition in cell units: for num in range (maxCellNum) --Example of data acquisition in row units: for row in ws.iter_rows (min_row = startDataRow):

--Two-dimensional array when the number of elements is not fixed ――It seems impossible because it doesn't come out even if you google ... ――It may be better not to design it to be used in the first place. --Combine the source data line by line with commas --If you divide it by commas on the user side, you can use a one-dimensional array (list) (see source).

Summary

--If you want to use a multidimensional array with an indefinite number of elements, it may be better to review the design itself.

reference

--I'll put a stupid code here.

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"

#From A4
startDataRow = 4
startDataColumn = 1

#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
#A two-dimensional array with an undetermined number of elements cannot be prepared ...
datalist = []

#Put the data of the target row into the list from the opened Excel
#I want to pull out the data line by line anyway ...
#Unreasonable
for row in ws.iter_rows(min_row = startDataRow):
    #Wouldn't it be possible to combine each line with commas?
    tempStr = "" 
    for cellVal in row:
        #Combine 2 and subsequent items with commas
        if tempStr == "":
            tempStr = str(cellVal.value)
        else:
            tempStr = tempStr + ',' + str(cellVal.value)
    datalist.append(tempStr)
    print(datalist)


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

#I want to set it in the same position as the original Excel
#I'm going to list the ones that are combined with commas
#If you want to bring it back, you can feel like splitting the elements of the list.
splitList = []
for rowNum in range(len(datalist)):
    splitList = datalist[rowNum].split(",")
    for colNum in range(len(splitList)):
        ws2.cell(startDataRow + rowNum, startDataColumn + colNum).value = splitList[colNum]

#Mortise
wb2.save(newFileName)

bonus

--About path specification

The code I wrote

#Last time
targetExcel = r"../data/Sales.xlsx"
#this time
targetExcel = r"..\data\Sales.xlsx"

--Both slash delimited and backslash delimited are working fine. --- Linux and MacOS are described in the former, and Windows is described in the latter, so it may absorb the difference.

Postscript: The title has been updated.