0

I'm a beginner in Python and I'm developing a program that take some data of a .xlsx and put it into an other .xlsx. To do so decided to use openpyxl. Here is the beginning of my code :

path1 = "sourceFile.xlsx"
path2 = "targetFile.xlsx"
sheet1 = openpyxl.load_workbook(path1, data_only=True)
sheet2 = openpyxl.load_workbook(path2)

As you can see I use the "data_only=True" to only take the data of my source file. My problem is that with this solution, "None" is returned for few cells of the source file. When I delete the "data_only=True" parameter, the formula is returned, "=B28" in these case. It's not what I want by the way that B28 cell of the target file has not the same value as B28 cell of source file.

I already search for solutions but surprisedly found nothing. If you have any idea you're welcomed !

MelvinSDRS
  • 1

1 Answers1

0

If B28's value in the original file is different than the output file, then the issue is likely with the code you're using to copy the cells. When asked how you're extracting the cells, you gave code for extracting the value of a single cell. How are you extracting ALL the cells? For-loop? If you shared that code, we can further analyze this problem.

I'm including code which copies values from one file to another, you should be able to tweak this to your needs.

from openpyxl import load_workbook, Workbook

## 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')

## 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')

Please post more code if you need further assistance.

JamesHalpert
  • 86
  • 5