Get column names of Excel worksheet with OpenPyXL in readonly mode

0 votes

How could I retrieve:

  1. the column names (values of the cells in the first row) in an openpyxl Read-only worksheet?
    • City, Population, Country in the below example worksheet
  2. all column names in an openpyxl Read-only workbook?
    • City, Population, Country, frames from worksheet 1, and the other column names from all other worksheets

Example Excel worksheet:

| City       | Population  |    Country   |
| -----------|------------ | ------------ |
| Madison    |   252,551   |     USA      |
| Bengaluru  | 10,178,000  |    India     |
| ...        |       ...   |     ...      |

Example code:

from openpyxl import load_workbook

wb = load_workbook(filename=large_file.xlsx, read_only=True)
sheet = wb.worksheets[0]

... (not sure where to go from here)
Oct 21 in Others by Kithuzzz
• 20,660 points
354 views

1 answer to this question.

0 votes

This will print every thing from row 1;

list_with_values=[]
for cell in ws[1]:
    list_with_values.append(cell.value)

If for some reason you want to get a list of the column letters that are filled in you can just:

column_list = [cell.column for cell in ws[1]]

For your 2nd question; Assuming you have stored the header values in a list called: "list_with_values"

from openpyxl import Workbook
wb = Workbook()
ws = wb['Sheet']
#Sheet is the default sheet name, you can rename it or create additional ones with wb.create_sheet()
ws.append(list_with_values)
wb.save('OutPut.xlsx')
answered Oct 22 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How search data in excel with openpyxl?

The method iter_rows in the library has changed, refer ...READ MORE

answered Oct 2 in Others by narikkadan
• 37,660 points
224 views
0 votes
1 answer

How to get rid of a #value error in Excel?

Changing the format to "Number" doesn't actually ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
81 views
0 votes
1 answer
0 votes
1 answer

How to get sum of all matches of HLOOKUP in Excel?

Consider: =SUMPRODUCT((A1:E1="apple")*(A2:E2)) To include more ...READ MORE

answered Oct 16 in Others by narikkadan
• 37,660 points
71 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
397 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 3,138 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11 in Others by narikkadan
• 37,660 points
36 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP