Python Forum
using openpyxl copy the value of a cell, not the formula
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
using openpyxl copy the value of a cell, not the formula
#1
Hi,

I want to copy just the first 7 columns of my excel sheets to a new file, but columns 4,5,6 and 7 contain formulas linked to lots of cells further right. I create the sheets in the target file first. I can successfully copy the data over, but I just want the current value copied, not the formula.

How can I get the value as a number copied over?

Starting row 4, Column 4 and column 6 contain percentages, 5 and 7 just contain numbers. I have this code, which works, but gives me the formulas, not the number values, in the new file. All the values for cells with formulas are zero, because the relevant cells in the target file are not populated, so the formula equals zero.

for sheet in sourceSheetNames:
	sourceFileActiveSheet = sourceFile.get_sheet_by_name(sheet)
	targetFileActiveSheet = targetFile.get_sheet_by_name(sheet)
	maxRowsourceFile = sourceFileActiveSheet.max_row
	for rowNum in range(1, maxRowsourceFile + 1):
            for colNum in range(2, 8): # copy everything up to and including column 7
                value = sourceFileActiveSheet.cell(row=rowNum, column=colNum).value
                targetFileActiveSheet.cell(row=rowNum, column=colNum, value=value)
Reply
#2
You could perhaps create a copy of the worksheet outside of python, without the formulas, as this tutorial suggests.
Reply
#3
You should be able to find a solid answer at https://automatetheboringstuff.com/chapter12/.
Reply
#4
Thanks for the links.

I'll tinker some more. There must be a way to access the value stored in the formula at any given time. Maybe as simple as int(the formula)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Openpyxl manipulate excel write formula SamLiu 0 1,002 Nov-04-2022, 03:00 PM
Last Post: SamLiu
  Python “Formula” Package: How do I parse Excel formula with a range of cells? JaneTan 1 2,641 Jul-12-2021, 11:09 AM
Last Post: jefsummers
  How to append a value to specific excel cell using openpyxl hobbyist 0 4,767 Mar-05-2021, 07:14 PM
Last Post: hobbyist
  P3, openpyxl, csv to xlsx, cell is not number, problem with colorize genderbee 1 2,100 Sep-29-2020, 03:20 PM
Last Post: Larz60+
  Openpyxl: Excel formula & condition formatting removed JaneTan 0 3,561 Sep-25-2020, 07:02 AM
Last Post: JaneTan
  openpyxl - How can I copy some row from an excel file and paste them in another one? aquerci 2 17,301 Jun-07-2020, 04:26 AM
Last Post: buran
  openpyxl, if value in cell then change format genderbee 1 5,181 Nov-05-2019, 01:37 PM
Last Post: genderbee
  Need to copy column of cell values from one workbook to another with openpyxl curranjohn46 3 11,086 Oct-12-2019, 10:57 PM
Last Post: curranjohn46
  How can I copy specif cell from one csv to another csv zoe1111 5 4,413 Jan-13-2019, 11:24 PM
Last Post: ichabod801
  OpenPyxl Cell.value Alignment pcsailor 0 8,781 Sep-10-2018, 01:09 AM
Last Post: pcsailor

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020