Python Forum
Excel tables and move rows - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Excel tables and move rows (/thread-27982.html)

Excel tables and move rows - jdos - Jun-30-2020

Hey guys again,

so lets say we have an excel workbook with 12 worksheets. Each worksheet has two tables in it as you can see in the image below
[Image: capture-5]

Question one: Is it possible to get a row from the UNPAID table and move it to the PAID table?
Question two: If the former is possible, is it also possible to move the row from worksheet to worksheet?

This is what I have right now to just save. But I can't seem to be able to find the answer.
    writer= pd.ExcelWriter('Some.xlsx',engine='openpyxl', index=False)
    writer.book = load_workbook('Some.xlsx')
    writer.sheets=dict((ws.title,ws) for ws in writer.book.worksheets)
    reader = pd.read_excel(r'Some.xlsx',sheet_name=minas)
    print_together.insert(0,len(reader)+1)
    data = pd.DataFrame(data =print_together)
    data=data.transpose()
    data.to_excel(writer,sheet_name=minas,header=False, index = None,startrow=len(reader)+1)
    writer.save()


RE: Excel tables and move rows - Larz60+ - Jun-30-2020

Sure you can, read the docs: https://openpyxl.readthedocs.io/en/stable/
you can have more that one worksheet open at a time (which you are doing on line 3)
Since you're buildng a dictionary (writer.sheets) to contain all worksheets,
you extract the sheet by title.
It would be easier (if only two sheets were involved, to open each with separate names)
using the attached simple spreadsheet, copy sheet 1 A1 to sheet2 B1:

from openpyxl import Workbook, load_workbook, cell
import os

os.chdir(os.path.abspath(os.path.dirname(__file__)))

def A1_to_B1():
    # move sheet 1 cell a1 to sheet2 cell b1 
    wb = load_workbook('Sheettest.xlsx')
    wb1 = sheet_object = wb.worksheets[0]
    wb2 = sheet_object = wb.worksheets[1]
    wb2.cell(row=1, column=2).value = wb1.cell(row=1, column=1).value
    wb.save('Sheettest.xlsx')

A1_to_B1()


RE: Excel tables and move rows - jdos - Jun-30-2020

I get that, but is it possible to move it from table to table? As you can see in the image in the first post, I want to be able to move them through worksheets and tables, that is the tricky part.

for example you have worksheets[january,february,march,april,may,etc]. Each worksheet has two tables in it, table "Paid" and table "Waiting Payment".
In the first part of the program I can easily add new lines. But I want to be able to move them from table to table and that is the problem.

RE: Excel tables and move rows - Larz60+ - Jun-30-2020

Try it!
open worksheet 1 spreadsheet 1
open worksheet 1 spreadsheet 2
move from spreadsheet 1 to 2

RE: Excel tables and move rows - jdos - Jul-01-2020

I did mate, the thing is that I can't specify the exact row that I need to move each line.

Also it's not a matter of just moving one row from spreadsheet1 to spreadsheet2 but also from spreadsheet 1/table 1 to spreadsheet1/table 2.

For example move row16 (or 2rd row of table 2) to the next available row on table 1. (It's not my call, if it was I would do it using an easier way...)
[Image: capture1]

RE: Excel tables and move rows - Larz60+ - Jul-01-2020

you can calculate the last line of second spreadsheet (assume worksheet name ws2) using
last_row = ws2.max_row
then obviously next row is last_row + 1
so that will give you copy destination.

This will append row 1 sheet 1 to sheet2
example:
from openpyxl import Workbook, load_workbook, cell
import os

os.chdir(os.path.abspath(os.path.dirname(__file__)))

def append_row():
    # append sheet 1 row 1 to wb2 at end
    wb1 = load_workbook('Sheettest.xlsx')
    wb2 = load_workbook('Sheet1test.xlsx')    
    ws1 = wb1.worksheets[1]
    ws2 = wb2.worksheets[1]
    ws2_lastrow = ws2.max_row
    ws1_num_columns = ws1.max_column
    print(ws2.max_row, ws1_num_columns)

    for cellno in range(1, ws1_num_columns):
        ws2.cell(row=ws2_lastrow+1, column = cellno).value = ws1.cell(row=1, column=cellno).value

    wb2.save('Sheet1test.xlsx')

append_row()


RE: Excel tables and move rows - jdos - Jul-02-2020

I think that this will just add the row to line 18 of the example table and not to the 6th line. I have this here that does that. 
data.to_excel(writer,sheet_name=minas,header=False, index = None,startrow=len(reader)+1)