[openpyxl] Increment cells being pasted into Template

[Kristenl2784]

Hello,

How would I increment ws2 cells? ws cells can remain the same because when the script loops through my files it will copy data from the same cells of each file. ws2 cells are inside my template so if I don't increment them the data is just written over. For instance under the template section it says ws2['A2']=ws['A1'] this means the data will write to ws2 cell A2 but the next loop will need to write to ws2 cell A3 so the data isn't written over.

import openpyxl as xl; 
import os
  
input_dir = 'C:\\work\\comparison\\NMN'
template = 'C:\\work\\comparison\\template.xlsx'
newFile = 'NNM_Comparison.xlsx'
 
 
 
 
files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith(".xlsx")]
 
for file in files:
    input_file =  os.path.join(input_dir, file)
    wb1=xl.load_workbook(input_file)
    ws=wb1.worksheets[0]

    wb2 = xl.load_workbook(template) 
    ws2 = wb2.worksheets[0] 
    ws2['A2']=ws['A1']
    ws2['D2']=ws['B4']
    ws2['E2']=ws['D4']
    ws2['I2']=ws['B5666']
    ws2['J2']=ws['D5666']
     
     
    output_file = (newFile)
    wb2.save(output_file)

[Yoriz]

Using enumerate with a start value of 2 to change the row number

import openpyxl as xl
import os

input_dir = 'C:\\work\\comparison\\NMN'
template = 'C:\\work\\comparison\\template.xlsx'
newFile = 'NNM_Comparison.xlsx'


files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith(".xlsx")]

wb2 = xl.load_workbook(template)
ws2 = wb2.worksheets[0]

for index, file in enumerate(files, 2):
    input_file = os.path.join(input_dir, file)
    wb1 = xl.load_workbook(input_file)
    ws = wb1.worksheets[0]

    ws2[f'A{index}'] = ws['A1'].value
    ws2[f'D{index}'] = ws['B4'].value
    ws2[f'E{index}'] = ws['D4'].value
    ws2[f'I{index}'] = ws['B5666'].value
    ws2[f'J{index}'] = ws['D5666'].value


wb2.save(newFile)

[Kristenl2784]

Hello,

Thanks for the reply everything seems to be working this way, it's a little different then what you had suggested. I'm now stuck on trying to figure out how to look at ws1 column F starting at row 4 and count how many times the value 0 occurs. And then take how many time that value occurs and write it to ws3 column M row 2.

import openpyxl as xl; 
import os
 
input_dir = 'C:\\work\\comparison\\NNM'
template = 'C:\\work\\comparison\\template.xlsx'
summary = 'C:\\work\\comparison\\Summary.xlsx'
newFile = 'Comparison.xlsx'




files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith(".xlsx")]


wb3 = xl.load_workbook(template) 
ws3 = wb3.worksheets[0] 

i=0
ii=0


for file in files: 
   input_file =  os.path.join(input_dir, file)
   wb1=xl.load_workbook(input_file)
   ws1=wb1.worksheets[0]

    
   wb2 = xl.load_workbook(summary) 
   ws2 = wb2.worksheets[1]

    
   ws3[f'A{i+2}']=ws1['A1'].value[28:]
   ws3[f'D{i+2}']=ws1['B4'].value
   ws3[f'E{i+2}']=ws1['D4'].value
   ws3[f'I{i+2}']=ws1['B'][-1].value
   ws3[f'J{i+2}']=ws1['D'][-1].value
   ws3[f'O{i+2}']=ws1['E'][-1].value 
   ws3[f'N{i+2}']=ws2[f'I{ii+6}'].value   
   i += 1
   ii +=1          
   
   

   wb3.save(newFile)

[palladium]

Hmm, maybe try using pandas to convert the range of cells you are interested in into a series, and then use the value_count() function to take it from there? Something like:

import pandas as pd

series = pd.read_excel(filename, sheetname, skiprows = [0:5],usecols = "F", squeeze = True) # I'll let you enter the filename and sheetname. Make sure squeeze is True so that you end up with a Series, not a dataframe. 
counts = series.value_counts()
z = counts[0]

...and then write z to the cell you want with openpyxl.

More info: https://pandas.pydata.org/pandas-docs/st...excel.html, https://pandas.pydata.org/pandas-docs/st...ounts.html

Credits: https://stackoverflow.com/questions/1220...e-variable

[Kristenl2784]

Hmm, maybe try using pandas to convert the range of cells you are interested in into a series, and then use the value_count() function to take it from there? Something like:

import pandas as pd

series = pd.read_excel(filename, sheetname, skiprows = [0:5],usecols = "F", squeeze = True) # I'll let you enter the filename and sheetname. Make sure squeeze is True so that you end up with a Series, not a dataframe. 
counts = series.value_counts()
z = counts[0]

...and then write z to the cell you want with openpyxl.

More info: https://pandas.pydata.org/pandas-docs/st...excel.html, https://pandas.pydata.org/pandas-docs/st...ounts.html

Credits: https://stackoverflow.com/questions/1220...e-variable

Hello,

This code doesn't work I end up this error message

counts = series.value_counts()

AttributeError: 'collections.OrderedDict' object has no attribute 'value_counts'