1

I'm still fairly new to coding, so I'm sure that there are easier or prettier ways to write the following script. The script runs and the new sheets are created within the workbook, however, the data is not copying from 'sheet1' to the second sheet

I've tried googling and reading other threads on Stack Overflow, but none seem to answer the question

import os, csv, glob, shutil, pandas as pd, numpy as np, openpyxl as opyx

path_to_combined_file = c:\\somefilepath here\\

filepath = path_to_combined_file + 'NSW.xlsx'

unaided_brand_awareness = pd.read_excel(filepath)

from openpyxl import load_workbook
wb = load_workbook(filepath)
wb.create_sheet('unaided_brand_awareness')
wb.create_sheet('aided_brand_awareness')
wb.create_sheet('favourite_stations')

worksheet1 = wb['Sheet1']
worksheet2 = wb['unaided_brand_awareness']

for i in range (1,2000):
    for j in range(1, worksheet1.max_column + 1):
        worksheet2.cell(row = i, column = j).value = worksheet1.cell(row = i, column = j).value

wb.save(filepath)

The code SHOULD create the following sheets 'unaided brand awareness', 'aided brand awareness' and 'favourite stations' and then copy the data from the sheet titled 'Sheet1' to the sheet titled 'unaided brand awareness'

Ideally, it would be great to have the data from 'Sheet1' copied to all the sheets within the workbook.

Also, i probably should note that the number of cells contained within 'Sheet1' will differ from case to case.

BossHard
  • 39
  • 6
  • read this https://medium.com/aubergine-solutions/working-with-excel-sheets-in-python-using-openpyxl-4f9fd32de87f . U can actually make copy of the sheet just rename 'Sheet1' to 'unaided_brand_awareness' , instead of copying data again, unless you have some reason to copy data individually. – arunp9294 Oct 18 '19 at 08:07
  • use `pd.ExcelFile()` or `pd.read_excel()` [refer to this post](https://stackoverflow.com/questions/26521266/using-pandas-to-pd-read-excel-for-multiple-worksheets-of-the-same-workbook) – Joe Oct 18 '19 at 13:38
  • @joe - I'm not really sure why you posted that thread. It has more to do with opening files than anything else. Thanks though. – BossHard Oct 20 '19 at 23:52
  • Thanks for the feedback, but neither of these answers help. @anrunp9294 - I need to duplicate the tab repeatedly, hence the need to copy and paste the original data to multiple sheets. Also, I had already come across this site, and the instructions are outdated. The 'get_sheet_by_name' command has been changed to wb.[sheetname] in the above example – BossHard Oct 20 '19 at 23:58
  • Well you'll obviously want to read the file before doing anything about it in python right? From the way I see it, copying a dataframe and storing it as a sheet in excel can be done within pandas it's just slicing. Access the excel with the sheet name you mentioned then "overight" the 'unaided brand awareness' with 'sheet1' – Joe Oct 21 '19 at 10:35

0 Answers0