Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel tables and move rows
#1
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()
Reply
#2
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()

Attached Files

.xlsx   Sheettest.xlsx (Size: 8.12 KB / Downloads: 136)
Reply
#3
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.
Reply
#4
Try it!
open worksheet 1 spreadsheet 1
open worksheet 1 spreadsheet 2
move from spreadsheet 1 to 2
Reply
#5
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]
Reply
#6
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()
Reply
#7
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)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  tables in Excel MaartenRo 3 479 Jan-06-2024, 03:46 PM
Last Post: deanhystad
  How to properly format rows and columns in excel data from parsed .txt blocks jh67 7 1,802 Dec-12-2022, 08:22 PM
Last Post: jh67
  Deleting rows based on cell value in Excel azizrasul 11 2,480 Oct-19-2022, 02:38 AM
Last Post: azizrasul
  The code I have written removes the desired number of rows, but wrong rows Jdesi1983 0 1,602 Dec-08-2021, 04:42 AM
Last Post: Jdesi1983
  Slittping table into Multiple tables by rows drunkenneo 1 2,002 Oct-06-2021, 03:17 PM
Last Post: snippsat
  Pandas DataFrame combine rows by column value, where Date Rows are NULL rhat398 0 2,083 May-04-2021, 10:51 PM
Last Post: rhat398
  Indexing [::-1] to Reverse ALL 2D Array Rows, ALL 3D, 4D Array Columns & Rows Python Jeremy7 8 6,963 Mar-02-2021, 01:54 AM
Last Post: Jeremy7
  Python script to summarize excel tables, then output a composite table? i'm a total n surfer349 1 2,290 Feb-05-2021, 04:37 PM
Last Post: nilamo
  Write tables from Word (.docx) to Excel (.xlsx) using xlsxwriter jackie 1 3,139 May-27-2020, 11:47 PM
Last Post: mcmxl22
  Merging excel worksheets with balnk rows sauravksingh85 1 1,297 May-19-2020, 12:46 PM
Last Post: sauravksingh85

Forum Jump:

User Panel Messages

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