Cell attributes

[bomdaworld]

Hi,

I've been trying call a loop function for cells in a particular row in Excel, specifically trying to grab cells between columns A to Z.

I have approached this using the ASCII numbering system using the fuction chr()

That line is as follows: note (sheet is one worksheet from file A, ws is one worksheet from file B)

    n=16
    q=0
    for x in range(6,int(query)):
        for s in range(65,90):   
            for cell in Cell(sheet,chr(s),x):
                for rows in ws.iter_rows():
                    for cells in rows:

I get an error where 

for cell in Cell(sheet,chr(s),x):
self._shared_date = SharedDate(base_date=worksheet.parent.excel_base_date)
AttributeError: 'function' object has no attribute 'parent'

I am lost as to how to approach this now. I'm trying to copy data from one excel into a new excel file with the idea that my code will methodically go through the two excel files from one cell at a time and copy the data 
to the other. [below is the follow on from the code at the first block]
cell.value = cells.internal_value
if someone can help clarify this for me it would be greatly appreciated. I'm very new to python and openpyxl, and I wish to improve in my python programming skills.

[Charlie Clark]

Am .02.2015, 06:50 Uhr, schrieb bomdaworld <yujun...@gmail.com>:

> Hi,

Hi Yu,

> I've been trying call a loop function for cells in a particular row in
> Excel, specifically trying to grab cells between columns A to Z.

I've seen your post on Stackoverflow but was too busy this week at FOSDEM
to reply.

> I have approached this using the ASCII numbering system using the fuction
> chr()

There is no need for this. There are two functions in openpyxl.util that
allow you to convert between Excel's columns and numerical indices. But in
practice you should almost never need to do this.

> That line is as follows: note (sheet is one worksheet from file A, ws is
> one worksheet from file B)
> n=16
> q=0
> for x in range(6,int(query)):
> for s in range(65,90):
> for cell in Cell(sheet,chr(s),x):
> for rows in ws.iter_rows():
> for cells in rows:
> I get an error where
> for cell in Cell(sheet,chr(s),x):self._shared_date =
> SharedDate(base_date=worksheet.parent.excel_base_date)AttributeError:
> 'function' object has no attribute 'parent'

Client code will almost never need to instantiate cells itself as cells
are always bound to a worksheet. This code example is too short to know
what is what but I suspect that "sheet" is not a worksheet object.

You probably want to have something like:

for s in range(1, 27):
cell = ws1.cell(row=1, column=s)

> I am lost as to how to approach this now. I'm trying to copy data from
> one excel into a new excel file with the idea that my code will
> methodically go through the two excel files from one cell at a time and
> copy the data to the other. [below is the follow on from the code at the
> first block]cell.value = cells.internal_value

If you want to copy from ws2 ("B") then you probably want to read this
worksheet first and then copy to the the one.

You should never assign cell.internal_value directly to another cell as
this will bypass or confuse the mechanisms used to set the relevant
metadata. This is crucial when working with strings or dates.

cell_to_be_copied = ws2.cell(row=1, column=2)
ws1.cell(row=1, column=1, value=cell_to_be_copied.value)

> if someone can help clarify this for me it would be greatly appreciated.
> I'm very new to python and openpyxl, and I wish to improve in my python
> programming skills.

Hope this helps. The screenshot you included looks very extensive.

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

[bomdaworld]

Thank you for that Charlie. It has solved some parts of it, but now I'm trying to find a function within openpyxl which allows me to transfer said data from one file to the dump file. For the new workbook i've used the Workbook() function and get_active_sheet function, but they don't allow me to call on the function

ws2.cell(row=x, column=y, value=cell_to_be_copied)

for that section, my current code is in the attached figures.

I've tried many variants to fill them, but they don't seem to mix well together.

[Charlie Clark]

Am .02.2015, 02:10 Uhr, schrieb bomdaworld <yujun...@gmail.com>:

> Thank you for that Charlie. It has solved some parts of it, but now I'm
> trying to find a function within openpyxl which allows me to transfer
> said
> data from one file to the dump file. For the new workbook i've used the
> Workbook() function and get_active_sheet function, but they don't allow

> me to call on the function.

There is no easy way to copy data between different files as it is spread
in different data structures. For example, a cell with a string value will
depend upon the shared_strings of the workbook and a cell with a date is
only a date with the right style.

I don't know if it's relevant but there has been some discussion on this
in the past:
https://bitbucket.org/openpyxl/openpyxl/issue/171/copy-worksheet-function

> ws2.cell(row=x, column=y, value=cell_to_be_copied)
> for that section, my current code is in the attached figures.
> I've tried many variants to fill them, but they don't seem to mix well
> together.

I can't really make much sense of the screenshots, some simple code would
be easier to work with. You should have a simple script that will allow

However, do not assign the internal_value from one cell to the value of
another as this may have unexpected results.

[bomdaworld]

Hi Charlie,

Thank you for your help up to now. I will have a look at the link and try to work my way around this. I will update this thread in the near future with what I have done from this point forwards.

Regards,

Yujun

[bomdaworld]

Hi Charlie,

Upon asking around and experimenting, I have found that it is possible for me to make a list of lists, which is then 'copied' from data into dump. the code is as follows:

    for row in ws.iter_rows:

         dumpe.append([])

    for cell in row:

        dumpe[-1].append(cell.value)

    count = -1

    for row in ws.iter_rows:

        count+=1

        for cell in row:

            cell.value = dumpe[count]

But i think this forces the script to go into an infinte loop. Attached is the script i'm writing.

Could you please advise on where i should go from here onwards?

[Charlie Clark]

Hi YuJun,

Am .02.2015, 05:10 Uhr, schrieb bomdaworld <yujun...@gmail.com>:

> Hi Charlie,

> Upon asking around and experimenting, I have found that it is possible
> for me to make a list of lists, which is then 'copied' from data into
> dump.

You can copy row by row if you want

the code is as follows:
> for row in ws.iter_rows:
> dumpe.append([])
> for cell in row:
> dumpe[-1].append(cell.value)
> count = -1
> for row in ws.iter_rows:
> count+=1
> for cell in row:
> cell.value = dumpe[count]

> But i think this forces the script to go into an infinte loop. Attached
> is the script i'm writing.

I can't see this creating an infinite loop but if it does Python will
break and tell you.

Your code seems very ambitious but the comments don't seem to match what's
happening. The two loops will edit the existing worksheet but the nested
loops look like they will produce garbage. Specifically

cell.value = dumpe[count]

Will assign the value of one cell a whole list. This should raise an
exception as Excel has no concept of a list in a cell.

> Could you please advise on where i should go from here onwards?

Less is more. If you break your code down into functions and only try and
process one file it will be easier to understand. You should also not try
and work around the library by creating Cells yourself or assigning a
dictionary of cells to a worksheet.

new_cells[coordinate] = Cell(sheet, column_letter, row, cell.value)

is better as
new_sheet.cell(column, row, cell.value) # You don't need to calculate the
column letter yourself

for x in range(6,int(query)):
sheet['B'+str(x)]="=(((A"+str(x)+"/60)/60)/24)+DATE(1970,1,1)+(10/24)"

for x in range(6, int(query)): # you should probably convert to an int in
the try: except: clause at the top of the file
value = "=(((A{0}/60)/60)/24)+DATE(1970,1,1)+(10/24)".format(x)
sheet.cell("B", x, value)

[bomdaworld]

Hi Charlie,

Thank you once again for those tips. Upon your suggestion, I have decided not to work with two workbooks, but just create two sheets in one workbook. The only problem I have with that is that the amount of data i need to manipulate. At 35mbs, i have about 190k rows of data about 35 columns each row.

From the tutorials once again I've tried to use Worksheet function, which worked out fine. However, I kept on gettnig a non attribute error with ws which is a get_sheet_by_name function

The code is as below

data = Workbook(filename)

newsheet = worksheet.Worksheet(data, 'Calculations')

ws = data.get_sheet_by_name('17270115')

for col_idx in range(1,40):

    col = get_column_letter(col_idx)

    for row in range(6,query):

        newsheet.append([ws.cell('%s%s'%(col,row)).value])

From several references online where people have used the code, it should work, but the error i'm getting is this

newsheet.append([ws.cell('%s%s'%(col,row)).value])

AttributeError: 'NoneType' object has no attribute 'cell'

It seems like it's the ws is only an instance? Not specifically referring to the actual values inside the cell?

So I tried to use the load_workbook function

 data = load_workbook(filename, use_iterators = True)

and the traceback error was this:

newsheet.append([ws.cell('%s%s'%(col,row)).value])

AttributeError: 'NoneType' object has no attribute 'value'

So I'm not sure whether it's the way I'm trying to access the values or is it the type of data within it? The file is a .tst file that is opened in Excel (delimited) and then saved as an Excel workbook. All data are numbers depicting specific readings.

At this point I'm only working on copying raw data into a 'manipulation' worksheet. The end manipulation would be creating columns in between each raw data for manipulation. I believe applying the formulae into those cells is the easy part of this script. It's just the transfer of data and the insertion of the column that might be a bit hard to script out.

[Charlie Clark]

Hi YuJun,

Am .02.2015, 04:48 Uhr, schrieb bomdaworld <yujun...@gmail.com>:

> Hi Charlie,

> Thank you once again for those tips. Upon your suggestion, I have decided
> not to work with two workbooks, but just create two sheets in one
> workbook.

> The only problem I have with that is that the amount of data i need to
> manipulate. At 35mbs, i have about 190k rows of data about 35 columns
> each row.

That shouldn't really be a problem for openpyxl

> From the tutorials once again I've tried to use Worksheet function, which
> worked out fine. However, I kept on gettnig a non attribute error with ws
> which is a get_sheet_by_name function
> The code is as below

> data = Workbook(filename)
> newsheet = worksheet.Worksheet(data, 'Calculations')

This is wrong. Use:

newsheet = data.create_sheet(title="Calculations")

> ws = data.get_sheet_by_name('17270115')
> for col_idx in range(1,40):
> col = get_column_letter(col_idx)
> for row in range(6,query):
> newsheet.append([ws.cell('%s%s'%(col,row)).value])

No need to use get_column_letter

newsheet.append([ws.cell(column=col_idx, row=row).value)])

> From several references online where people have used the code, it should
> work, but the error i'm getting is this
> newsheet.append([ws.cell('%s%s'%(col,row)).value])
> AttributeError: 'NoneType' object has no attribute 'cell'

Then data doesn't have a sheet called 17270115
get_sheet_by_name() returns None if it cannot find a sheet

I prefer to use key access
ws = data['17270115']
because it's compacter and will raise an exception if the sheet does not
exist.

> It seems like it's the ws is only an instance? Not specifically referring
> to the actual values inside the cell?

ws should be a worksheet

> So I tried to use the load_workbook function

> data = load_workbook(filename, use_iterators = True)
> and the traceback error was this:
> newsheet.append([ws.cell('%s%s'%(col,row)).value])
> AttributeError: 'NoneType' object has no attribute 'value'

Depending upon the version of openpyxl None can be returned where there is
no cell. Recent versions always return what we can an EMPTY_CELL so client
code doesn't have to worry about the check.

> So I'm not sure whether it's the way I'm trying to access the values or
> is
> it the type of data within it? The file is a .tst file that is opened in
> Excel (delimited) and then saved as an Excel workbook. All data are
> numbers
> depicting specific readings.

Difficult to say without the file. If the text file is linked as a data
source then openpyxl won't see anything. You can import a CSV file
directly into openpyxl and use the Workbook(guess_types=True) argument to
automatically convert numerical values to right type in Excel.

> At this point I'm only working on copying raw data into a 'manipulation'
> worksheet. The end manipulation would be creating columns in between each
> raw data for manipulation. I believe applying the formulae into those
> cells
> is the easy part of this script. It's just the transfer of data and the
> insertion of the column that might be a bit hard to script out.

You are aware that openpyxl never evaluates formulae? We leave that up to
whichever application then reads the file.

If you want to add columns then it's best to do this when you're creating
the rows as manipulating rows and columns in openpyxl is not possible with
the library due to the way cells are stored.