Bottom Page

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
İmage


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()
Quote
#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: 7)
Quote
#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.
Quote
#4
Try it!
open worksheet 1 spreadsheet 1
open worksheet 1 spreadsheet 2
move from spreadsheet 1 to 2
Quote
#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...)
İmage
Quote
#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()
Quote
#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)
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Write tables from Word (.docx) to Excel (.xlsx) using xlsxwriter jackie 1 158 May-27-2020, 11:47 PM
Last Post: mcmxl22
  Merging excel worksheets with balnk rows sauravksingh85 1 139 May-19-2020, 12:46 PM
Last Post: sauravksingh85
  Reading specific rows from CVS to Excel DavidTheGrockle 3 366 Nov-06-2019, 04:49 PM
Last Post: DavidTheGrockle
  Comparing the count of rows from the tables that are present in two different databas krt5 6 741 Feb-15-2019, 03:20 PM
Last Post: krt5

Forum Jump:


Users browsing this thread: 1 Guest(s)