Openpyxl copy and paste as values in new workbook

Question

I am trying to copy the first 100 rows in a source file to a new destination file with openpyxl. My source file has formulas, but I want to copy and paste as values in the new workbook. When I add data_only=True, (see code below), it copies only the values of my source sheet and therefore not the data in the formula cells - these are just empty in the destination file. How do I copy everything and paste as values in the destination sheet?

WB1 = load_workbook("sample_book.xlsx")
WB1_WS1 = WB1["Ark2"]
WB2 = Workbook()

#Create new worksheet in new workbook
for i in range(1,2):
    WB2.create_sheet(f"WS{i}")
    
#Delete first sheet
WB2.remove(WB2.worksheets[0])

#Define the ranges and sheets 
copy_ranges = [100]
copy_to_sheets = ["WS1"]

# Copy the values from the rows in WB1 to WB2
for i in range (len(copy_ranges, data_only=True)):
    #Set the sheet to compy to
    ws = WB2[copy_to_sheets[i]]
    #initialize row offset 
    offset = 1
    for s in range (i):
        offset+=copy_ranges[s]
        
    #copy the row and append 
    for j in range(offset, offset + copy_ranges[i]):
        #if j==0:
        #  continue 
        for row in WB1_WS1.iter_rows(min_row=j,max_row=j,min_col=1,max_col=WB1_WS1.max_column):
            values_row = [cell.value for cell in row]
        ws.append(values_row)

#save
WB2.save("WB2.xlsx")

Does this answer your question? [Read Excel cell value and not the formula computing it -openpyxl](https://stackoverflow.com/questions/28517508/read-excel-cell-value-and-not-the-formula-computing-it-openpyxl) — Tomerikoo, May 19 '21 at 15:16
Unfortunately not. If I open as data-only, it does not read the data at all.. Any idea on how to change that? — Christian, May 21 '21 at 14:06

score 1 · Answer 1 · answered Jul 31 '21 at 03:36

You are using Len() incorrectly. Len() returns the length of a list. copy_ranges is a 1-item list, so Len(copy_ranges) = 1. If you want to access the first item in the list, you need to use an index: Len(copy_ranges[0]) = 100

I don't quite follow the 'offset' code part, and there is an issue with

offset = 1
    for s in range (i):
        offset+=copy_ranges[s]

On any iteration where i > 1, s will be > 1, which means offset+=copy_ranges[s] will throw an IndexError because copy_ranges is a 1-item list and you are trying to access a non-existing element.

Here are two ways to copy the first 100 rows:

if you want the formula in WB2, don't pass in the data_only parameter.

## VERSION 1: Output will have formulas from WB1

WB1 = load_workbook('int_column.xlsx')
WB1_WS1 = WB1['Sheet']

WB2 = Workbook()
WB2_WS1 = WB2.active # get the active sheet, so you don't need to create then delete one

# copy rows
for x, row in enumerate(WB1_WS1.rows):
    if x < 100: # only copy first 100 rows
        num_cells_in_row = len(row)
        for y in range(num_cells_in_row):
            WB2_WS1.cell(row=x + 1, column=y + 1).value = WB1_WS1.cell(row=x + 1, column=y + 1).value

WB2.save('copied.xlsx')

if you set data_only=True, the displayed value of the cell in WB1 will be copied to WB2.

## VERSION 2: Output will have value displayed in cells in WB1

WB1 = load_workbook('int_column.xlsx', data_only=True)
WB1_WS1 = WB1['Sheet']

WB2 = Workbook()
WB2_WS1 = WB2.active # get the active sheet, so you don't need to create then delete one

# copy rows
for x, row in enumerate(WB1_WS1.rows):
    if x < 100: # only copy first 100 rows
        num_cells_in_row = len(row)
        for y in range(num_cells_in_row):
            WB2_WS1.cell(row=x + 1, column=y + 1).value = WB1_WS1.cell(row=x + 1, column=y + 1).value

WB2.save('copied.xlsx')

Openpyxl copy and paste as values in new workbook

1 Answers1