OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

How can I get one row from excel file and add it to other rows in a particular place?

  • Thread starter Thread starter Pauline Rumyantseva
  • Start date Start date
P

Pauline Rumyantseva

Guest
I'm learning to work with openpyxl and need to get information from particular cells and rearrange them to make JSON string from it. I tried different approaches, many of them work, but when I'm dealing with large excel files I get really long scripts and want to try something shorter. I found a nice short script that works just fine, but I need to add another row to my result wich is a little difficult for me because I need this row to be in a particular place and separate from other data. I can't undestrand how to put that row in the right place.

Here's my excel file:

excelfile

I need to get information from first column (exept words "Place" and "All"), skip all rows and columns that belong to "Year" and get all columns from "1 part", "3 part" and "4 part", skipping "2 part". Also I don't need row № 4 at all. And I need to get information from row № 3.

Result I'm trying to get:

Code:
"1 part": "1_Pl": 4, 5, 6, "3 part": "1_Pl": 10, 11, 12, "4_Part": "1_Pl": 13, 14, 15, "1 part": "2_Pl": 19, 20, 21, "3 part": "2_Pl": 25, 26, 27

And so on.

Here's my code:

Code:
import openpyxl

START_ROW = 5 # First row with data.
END_ROW = 15 # End of data (row after the last).
TITLE_COL = 1 # Column with title
START_COL = 5 # First column where quarter data starts.
COLS_PER_QUARTER = 3 # Colums per quarter.
QUARTERS_COUNT = 4 # Quarters per year.

wb = openpyxl.load_workbook('C:\\directory\\input.xlsx')
filename='input.xlsx',
read_only=True,
data_only=True
ws = wb.active

entries = []
for row in range(START_ROW, END_ROW):
    def cell(col):
        return ws.cell(row, col).value

    title = cell(TITLE_COL)
    for quarter in range(QUARTERS_COUNT):
        quarter_col = START_COL + quarter * COLS_PER_QUARTER
        entry = f'"{title}": {cell(quarter_col)}, {cell(quarter_col + 1)}, {cell(quarter_col + 2)}'
        entries.append(entry)

I need to get row № 3 and place it before other cells as shown in my result. I also need to remove information from columns that belong to "2 part". Can anyone tell me how to do it? It must be easy, though I just can't get it. Also I know that pandas is much better to achieve my goal, but I want to use openpyxl.
<p>I'm learning to work with openpyxl and need to get information from particular cells and rearrange them to make JSON string from it. I tried different approaches, many of them work, but when I'm dealing with large excel files I get really long scripts and want to try something shorter. I found a nice short script that works just fine, but I need to add another row to my result wich is a little difficult for me because I need this row to be in a particular place and separate from other data. I can't undestrand how to put that row in the right place.</p>
<p>Here's my excel file:</p>
<p><a href="https://i.sstatic.net/tCrl25Ly.png" rel="nofollow noreferrer">excelfile</a></p>
<p>I need to get information from first column (exept words "Place" and "All"), skip all rows and columns that belong to "Year" and get all columns from "1 part", "3 part" and "4 part", skipping "2 part". Also I don't need row № 4 at all. And I need to get information from row № 3.</p>
<p>Result I'm trying to get:</p>
<pre><code>"1 part": "1_Pl": 4, 5, 6, "3 part": "1_Pl": 10, 11, 12, "4_Part": "1_Pl": 13, 14, 15, "1 part": "2_Pl": 19, 20, 21, "3 part": "2_Pl": 25, 26, 27
</code></pre>
<p>And so on.</p>
<p>Here's my code:</p>
<pre><code>import openpyxl

START_ROW = 5 # First row with data.
END_ROW = 15 # End of data (row after the last).
TITLE_COL = 1 # Column with title
START_COL = 5 # First column where quarter data starts.
COLS_PER_QUARTER = 3 # Colums per quarter.
QUARTERS_COUNT = 4 # Quarters per year.

wb = openpyxl.load_workbook('C:\\directory\\input.xlsx')
filename='input.xlsx',
read_only=True,
data_only=True
ws = wb.active

entries = []
for row in range(START_ROW, END_ROW):
def cell(col):
return ws.cell(row, col).value

title = cell(TITLE_COL)
for quarter in range(QUARTERS_COUNT):
quarter_col = START_COL + quarter * COLS_PER_QUARTER
entry = f'"{title}": {cell(quarter_col)}, {cell(quarter_col + 1)}, {cell(quarter_col + 2)}'
entries.append(entry)
</code></pre>
<p>I need to get row № 3 and place it before other cells as shown in my result. I also need to remove information from columns that belong to "2 part". Can anyone tell me how to do it? It must be easy, though I just can't get it.
Also I know that pandas is much better to achieve my goal, but I want to use openpyxl.</p>
 

Latest posts

Top