Write value to a range

[S Smith]

Hi guys,

I have some code that unmerges a bunch of cells ....

def FindMergedCells():
    myfile = '02-Sep-2015.xlsx'
    from openpyxl import load_workbook
    wb = load_workbook(filename = myfile)
    sheet = wb.get_active_sheet()
    #print(sorted(sheet.merged_cell_ranges))
    for items in sorted(sheet.merged_cell_ranges):
        print(items)
        itemLength = ((len(items) - 1)/2)
        print (itemLength)
        cell1 = items[0:itemLength]
        print (cell1)
        print sheet[cell1].value
        sheet.unmerge_cells(str(items) )
        row = (items)
        for cell in row:  value = sheet[cell1].value
    wb.save(myfile)
    print(sorted(sheet.merged_cell_ranges))
    print('successful')

for each set of merged cells the code prints out (just for development purposes)

A100:P100 the range of the merged cells
4                length of string from above for slicing
A100         cell containing the merged cells value
Williamson Tea announced a Final dividend of Kes.40.00 on 15-Jun-2015; Books closure 30-Jun-2015.   value in the merged cells

I want to write the value in the merged cells back to the the range of the merged cells once the code has unmerged them.

How do I reference the range to write the values to ?  as you can see I tried

        row = (items)
        for cell in row:  value = sheet[cell1].value

But it doesn't work, any tips ?

Cheers
Stevo

[Charlie Clark]

Am .03.2016, 12:59 Uhr, schrieb S Smith <stm...@gmail.com>:

> But it doesn't work, any tips ?

I don't really understand what you're trying to do. It looks unnecessarily
complicated to me. Can you break it down into chunks?

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[S Smith]

Charlie,

It may be more complicated than it needs to be I'm new to Python and never used openpyxl until today, I'm trying to search the sheet for merged cells, copy the data from them - unmerge the cells and then write the data back to all the cells that were previously merged.

def FindMergedCells():
    myfile = '02-Sep-2015.xlsx'
    from openpyxl import load_workbook
    wb = load_workbook(filename = myfile)
    sheet = wb.get_active_sheet()

    for items in sorted(sheet.merged_cell_ranges):

        itemLength = ((len(items) - 1)/2) # this line returns the number of characters to use in the slice next line down to get the first cell of the merged cells
)
        cell1 = items[0:itemLength]       # this line returns the slice of "A100" from "A100:P100" for example, so cell1 now points to the cell holding the value in the merged cells
      
        sheet.unmerge_cells(str(items) )  # this unmerges the cells

        row = (items)                                  # these two lines were an
        for cell in row:  value = sheet[cell1].value   # attempt to to write the value from the merged cells back to the now unmerged cells in the range A100:P100

    wb.save(myfile)

 Hope this helps to clarify things

[Charlie Clark]

Am .03.2016, 14:42 Uhr, schrieb S Smith <stm...@gmail.com>:

> Charlie,
>
> It may be more complicated than it needs to be I'm new to Python and
> never used openpyxl until today, I'm trying to search the sheet for
> merged cells,
> copy the data from them - unmerge the cells and then write the data back
> to
> all the cells that were previously merged

Try this (untested but shouldn't require much work)

from openpyxl import load_workbook

def FindMergedCells(filename):
wb = load_workbook()
sheet = wb.active

for cell_range in sheet.merged_cell_ranges:
coord = cell_range.split(":")[0]
value = ws[coord].value
sheet.unmerge_cells(cell_range)
for row in ws[cell_range]:
for cell in row:
cell.value = value

wb.save(filename)

FindMergedCells('02-Sep-2015.xlsx')

[S Smith]

Charlie,

Couple of tweaks and it works great, thanks ... where is best to find some stuff for beginners to learn about openpyxl ?

I can see I'm going to be coming here a lot !!

Cheers
Stevo

[Charlie Clark]

Am .03.2016, 15:28 Uhr, schrieb S Smith <stm...@gmail.com>:

> Charlie,

> Couple of tweaks and it works great, thanks

Glad to know it's working now.

... where is best to find some stuff for beginners to learn about
openpyxl ?

Well, there's the documentation. In addition, Al Sweigart has dedicated a
whole chapter of his book "Automate the boring stuff" to using openpyxl:

https://automatetheboringstuff.com/chapter12/

Covers the basics but is already slightly out of date.

However, I think people have all kinds of different requirements depending
on whether they're reading, writing or editing files.

[S Smith]

Charlie,

Is there any reason that your script might not unmerge a cell ?  I tried it on a different work book and it didnt unmerge all of the cells, it appears to have found them but not unmerged them.
I dont seem to be able to attach the file on here for you to have a look, I suspected possibly locked cells ?
But using the code below doesnt seem to help the problem.

Update as I was rechecking things before posting I noticed that if I run it repeatedly it unmerges more cells each time its repeated ... after 5 or 6 cycles it has unmerged all the cells.  Any idea whats going on ?

def unlock_Cells(myfile):

    wb = load_workbook(filename = myfile)

    ws = wb.active
    ws.protection.sheet = False    
   
    for row in ws.iter_rows('A1:T50'):
        for cell in row:  
            if cell.style.protection.locked:
                protection = Protection(locked=False, hidden=False)  


def findMergedCells(myfile):

    wb = load_workbook(filename = myfile)

    #wb = load_workbook()
    ws = wb.active

    for cell_range in ws.merged_cell_ranges:
        print (ws.merged_cell_ranges)

        coord = cell_range.split(":")[0]
        value = ws[coord].

value
        ws.unmerge_cells(cell_range)

        for row in ws[cell_range]:
            for cell in row:
                cell.value =

value

    wb.save(myfile)

Cheers
Stevo

[Charlie Clark]

Am .03.2016, 10:19 Uhr, schrieb S Smith <stm...@gmail.com>:

> Is there any reason that your script might not unmerge a cell ?

No.

> I tried it on a different work book and it didnt unmerge all of the
> cells, it appears to have found them but not unmerged them.

> I dont seem to be able to attach the file on here for you to have a
> look, I suspected possibly locked cells ?

No, openpyxl preserves but ignores cell protection

> But using the code below doesnt seem to help the problem.
> Update as I was rechecking things before posting I noticed that if I run
> it repeatedly it unmerges more cells each time its repeated ... after 5
> or 6
> cycles it has unmerged all the cells. Any idea whats going on ?

The only thing I can think of would be related to a typical Python when
looping over and changing lists. You can check for this easily with some
logging:

print(ws.merged_cell_ranges)

and then print(cell_range) within the loop.

This will show if elements are being skipped.

It's possible that the list of merged cell ranges is being updated as you
loop over and unmerge. If this is the case, you should simply use a copy
of it for the loop:

for cell_range in ws.merged_cell_ranges[:]:

[S Smith]

Charlie,

OK, it just struck me as a little unusual, I fixed it by just repeating the loop -

def findMergedCells(myfile):
    wb = load_workbook(filename = myfile)

    ws = wb.active
   
    while ws.merged_cell_ranges != []:

        for cell_range in ws.merged_cell_ranges:
            print (ws.merged_cell_ranges)
            coord = cell_range.split(":")[0]
            value = ws[coord].value
            ws.unmerge_cells(cell_range)
            for row in ws[cell_range]:
                for cell in row:
                    cell.value = value
    wb.save(myfile)
       

Cheers
Stevo

[Charlie Clark]

------- Weitergeleitete Nachricht -------
Von: "S Smith" <stm...@gmail.com>
An: openpyxl-users <openpyx...@googlegroups.com>
Kopie:
Betreff: Re: [openpyxl-users] Re: Write value to a range
Datum: Wed, 02 Mar 2016 12:11:33 +0100

OK, it just struck me as a little unusual, I fixed it by just repeating the

loop.

Don't. Remove the while loop, it's a bad pattern in Python

Try the following to see what's happening.

vals = list(range(10))
for idx,a in enumerate(vals):
print(a)
vals.pop(idx)

We could investigate on ensuring that all changes to the list of merged
cell ranges are atomic but I'm not convinced this is really necessary.

[S Smith]

Charlie,

As I worked more on this I realised that it was easier to convert the spreadsheet to a csv and then work on it so its no longer an issue. Its much easier to manipulate the data as a list.

Cheers
Steve