Newest 'openpyxl' Questions - Stack Overflow

Questions tagged [openpyxl]

Openpyxl is a Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files.

0
votes
0answers
5 views

Openpyxl FileNotFoundError

I have been trying to get openpyxl working with pycharm but the excel documents appear with a question mark, and when I try to run code it says filenotfounderror import openpyxl as xl wb = xl....
0
votes
0answers
23 views

How can I fix the 07002 too few parameters in this program?

Trying to write a simple program to parse Excel sheets then store the data in a Access database. I'm not entirely sure what the issue is, I've tried renaming parameters, double checking spelling, etc....
0
votes
0answers
9 views

for loop for openpyxl multiple chart creation

I'm trying to create a for loop to create multiple line charts in openpyxl, all at once. Certain indices in an array would be the bookends for the data the chart would draw data from. Is this possible ...
0
votes
0answers
42 views

Python does not work but gives no error message

I am new to programming/scripting in general and just becoming to understand the basics of the workings of python. Anyways, I have here a script I have been working on to randomly find a keyword in a ...
0
votes
1answer
31 views

Use for loop to search columns based on keywords to determine which column to extract data from

i have the following data in a dataframe. JAN FEB MAR 2019 MAR YTD 2018 MAR YTD 99.8 98.6 98.7 99.0 99.1 100.0 99.0 97.9 98.8 99.2 99.5 98.0 99.7 99.2 99.9 100.0 ...
1
vote
0answers
17 views

Copying data from Excel workbook to another workbook, specific rows and columns need to be selected

I have been able to open up the workbook and save it, but I can't seem to copy and paste specific rows and columns. I would like to be able to use this for multiple sheets and append the data to data ...
0
votes
1answer
26 views

Creating a line chart with openpyxl - “The reference is not valid”

I'm trying to create a line chart with openpyxl. The two axes should be time and battery voltage. Creating the chart with openpyxl sets one axis as battery voltage and the other as the row number. ...
0
votes
1answer
9 views

How to auto-expand excel-workbook containing multiple sheets using python

I'm trying to auto-expand the Excel file using the python script but it's throwing some error. Requesting your help here. I have tried with below code. import openpyxl from ...
-1
votes
0answers
33 views

regex sub for column of values

I'm in an excel spreadsheet where column B has times in hh:mm:ss format. After 24 hours, the cells say "1 day, hh:mm:ss". I'm trying to remove the "1 day, " portion. import re import openpyxl wb = ...
0
votes
0answers
20 views

Openpyxl fails to read row when reference attribute is missing

I'm using openpyxl to read an excel worksheet. It throws a KeyError when iterating and parsing rows because the openxml row element doesn't have a reference attribute ('r'). It only has a style index (...
0
votes
1answer
32 views

Copy column to another sheet in Python

I've been trying to copy a variable length column to another sheet through openpyxl. What I'm looking to do is copy, for example, column B from row 2 up to row = sheet.max_row and paste it into ...
0
votes
0answers
28 views

Python openpyxl save have TypeError: __init__() takes at least 3 arguments (3 given)

I using openpyxl(Version: 2.6.2), on ubuntu16.04 the code can execute success: wb.save('filename.xlsx') but on win10(same openpyxl version), some error like below: File "C:\Python27\lib\site-...
0
votes
0answers
26 views

How to automatically change relative cell formula in excel after deleting columns in python?

I want to delete some columns in excel and need cell formula getting automatically updated with reference to new column. I have large sets of data, each in an excel column like below. A B C ...
-1
votes
2answers
27 views

How to return first and last values in a column using openpyxl and change the title of the excel file to them?

I'm trying to change the title of five files to show the first and last values in a column in that file separated by a "_" For example, in a column if I have values 0001,0002,0003,0004, I want to ...
-1
votes
2answers
49 views

Datetime writes 1899-12-30: How to remove it?

I'm writing a python script to grab certain times from an excel spreadsheet and print them out to the console when the script is run. Here are the first few cells of the spreadsheet: A B 1 # ...
0
votes
4answers
35 views

if statement through openpyxl

I'm learning Python and openpyxl for data analysis on a large xlsx workbook. I have a for loop that can iterate down an entire column. Here's some example data: ROW: VALUE: 1 1 2 2 3 ...
0
votes
1answer
29 views

How to change the number formatting of certain columns in an excel spreadsheet?

I want to remove the dollar sign formatting, $###,##0.00 and replace it with #,##0.00 number formatting. I have tried changing the format of just one cell in the sheet, but I have no idea if it is ...
-1
votes
1answer
18 views

How to get the value of a cells instead of <Cell 'Sheet1'.A2>' with openpyxl

Basically I'm unable to get the value of a cell from a Excel file with openpyxl. import openpyxl book = openpyxl.load_workbook('Inputs.xlsx') sheet = book.active print(sheet['A2']) The column 'A' ...
0
votes
0answers
18 views

Django objects and openpyxl cells. How to set cell value like a value of django object field?

I have to write a block of code wich generate xlsx report from django dataset( few models). Frame of the report have more difficult structure than usual x rows y columns (some cells was united). It ...
0
votes
1answer
31 views

Can't write data into Excel-Sheet using openpyxl Workbook

I'm trying to write to specific cells in a Excel-Sheet using openpyxl Workbook. At first I couldn't write to cells that already had data in them and now I can't write at all (or I'm just going crazy). ...
0
votes
0answers
10 views

Formatting spreadsheet as Table using either Openpyxl or ExcelWriter

I'm trying to see if there is something I can do programmatically with Python either with Openpyxl or ExcelWriter or something else that would allow me to change the sheet to a table. The manual ...
-1
votes
0answers
13 views

Getting error while trying to fetch the column values using python openpyxl

I have written a python script which should return columns values from a spreadsheet but getting error while trying to iterate. Referred openpyxl doc. #Accessing MS Updates Excel File wb = ...
-1
votes
0answers
18 views

Copy data of a cell from workbook to another by python

I have added a formula on a excel sheet using openpyxl for e.g. I have added SUM of column A2 : maxrow in column A Now the addition result is present in cell A10 I need to copy the cell value of ...
0
votes
1answer
14 views

Cannot retrieve cell data in Openpyxl using variable as location

I want to be able to get the value of a cell via openpyxl using a for loop and using a string and the string of an integer counter as a location. eg. "A" + str(Counter) = A1 get the value from ...
0
votes
1answer
14 views

OpenPyXL - How to set GraphicalProperties to chart's border

I would like to know whether or not there is any way of setting graphical properties to chart's borders. I have been looking similar threads and I found out the next one: openpyxl - Ability to ...
0
votes
1answer
14 views

How can I add advanced styles to my Excel Line Chart Using Python & Openpyxl

Below is the code and the resulting Excel chart generated using Python's Openpyxl Package. Also below is the chart I would like the result to look like How can I add the following things to the graph? ...
0
votes
1answer
17 views

Inputting text from .txt into Workbook

I am trying to write a program that will take text from a .txt and input them in to cells of a Workbook. I am running into an issue though where when i'm trying to for loop through the cells and its ...
0
votes
1answer
27 views

What am I doing wrong in this split for excel rows loop?

I wrote this little code to split domains from lists of urls in excel workbook. But the problem is i can't get it to write in the actual workbookt or even create a new workbook with the domains. #...
-1
votes
1answer
27 views

openpyxl - Check if worksheet is hidden

I have a large number of excel files and I only want to work with sheets which are not hidden and I want to ignore all hidden sheets. Currently my python script loops through every sheet regardless ...
-2
votes
1answer
12 views

I was trying to make an excel sheet but it seems that it's not working

import openpyxl import pathlib a = pathlib.Path("G:/myCourse_mhanned/19. SUPERHERO LEVEL Automate Excel Tasks with Python 3/1.1 Employees.xlsx.xlsx") workbook = openpyxl.load_workbook(a) print(...
0
votes
0answers
12 views

Change dates format in one column (replace, insert column, append), any way to update? ..it must be simple

Need to change dates format in Excel column. I can get into single cell but in case to update whole column with "proper_date" I am stuck wb = load_workbook(...) ws = wb['Lista'] daty_wystawienia = ...
0
votes
0answers
16 views

How to edit an existing excel cell value into a date

This excel file returns the cell values (numbers from 1-30) as strings. I convert them to integers because if it's above 16, it indicates it's a day in June. If it's below 17 it's July (this is due ...
1
vote
1answer
23 views

Is it possible to apply a style to a whole sheet with openpyxl?

I am learning to use the openpyxl module and I am trying to make a standard style in a sheet. I have been searching for an answer in documentation and other people questions but I don't find it. I ...
0
votes
1answer
15 views

How to set or change default height of table in openpyxl

I want to change table height by openpyxl,and I wish a more large height value as default at first,then I can set auto wrap line to make my table more beautiful,but I don't know How To Change Default ...
-1
votes
0answers
13 views

How to insert copied columns/rows in Excel worksheet by using openpyxl

I have an Excel worksheet. It needs to implement the following logic: Copy the column and paste in the desired position of the column as a new column or new columns. Same with the string. The copied ...
0
votes
0answers
19 views

Openpyxl How to get value from worksheet by index

This has been asked a few times, but none of the old answers are working for me. I traverse the first row in a sheet, but my method hits a 'None' value before it gets to the first empty cell. So it ...
0
votes
2answers
23 views

Sorting a list by multiple non-standard criteria

I'm trying to set up a certain data structure in my program that takes the data from an excel file. It needed to be sorted by the following criteria: Accending order by index 5. If two structures ...
0
votes
1answer
38 views

AGGREGATE formula not automatically calculating when written to results spreadsheet

I have a python 3.7 script that has been developed using the OPENPYXL (v2.5.10) library to take data from a number of excel workbooks, to process that data and then to write to a separate excel ...
0
votes
1answer
19 views

With Python openpyxl how do you change the font size in a chart legend?

With Python openpyxl how do you change the font size in a chart legend. #Create Chart chart1 = BarChart() chart1.type = "col" chart1.style = 10 chart1.title = "Rolling 4 weeeeks" chart1.legend....
0
votes
0answers
25 views

How to calculate a formula in Excel and pull the calculated value with OpenPyXL

I am trying to copy/paste quantities from one excel form into another excel form (that i use as a template) that calculates COGS, and then take the sum of the COGS and assign it to a variable. After ...
-1
votes
0answers
21 views

How to fix:TypeError: string indices must be integers in Python [duplicate]

I'm fixing the code to use it regarding another data than one of my colleague. Everything should be right, but I have an error in the first Loop of my code: After the last line in the code provided ...
-2
votes
1answer
32 views

Searching through multiple Excel files with openpyxl

So I am trying to search through multiple excel files using openpyxl. I've seen ways to do it where you have 3 maybe 4 files, however I am looking to do it with roughly 30 files. The list of files ...
-1
votes
0answers
26 views

How do I use complex formulas in OpenPyXl

I have a list of data in .xlsx The data is in column W the data includes 1. ACCEPTABLE 2. UNACCEPTABLE I want to count the number of ACCEPTABLE and total of both ( ACC and UNACC ) and the formula ...
-1
votes
0answers
36 views

Using formulas in openpyxl

The column I want perform the addition is in "V" The excel sheet layout would be similar but the row length would always change therefore I want the formula to be applied from V2 (as V1 would be the ...
0
votes
2answers
38 views

How to change the date format while writing it to excel file using openpyxl

I want to convert the date format from dd-mm-yy to mm-yy while writing it into excel file. I tried all the methods but to no success. I am trying to copy data from one excel file and paste it into ...
1
vote
1answer
25 views

I need help sending excel attachment from memory Mailgun

Im trying to send an email with an Excel attachment using Python and the Mailgun Rest API. The idea is to create the excel file using openpyxl and then send the excel workbook that is stored in ...
0
votes
0answers
23 views

Obtain a different line chart type when creating an excel chart within Python

I want to create a line chart within Python of a specific type. I am using the library openpyxl. The piece of code used gives a different chart type to the one I am looking for and do not know how to ...
1
vote
0answers
18 views

openpyxl : how to get a cell column by it's value

when we want to find a cell row , we can type like for cell in list(ws.rows)[0]: print(cell.row) but it seems cell has no attribute column, because when you type cell.column, it will come a ...
0
votes
1answer
36 views

openpyxl : I can't open excel file with password

I am a student. I want to read protected excel file with openpyxl, but I can't. This is my code. wb = load_workbook(file_path) # Error returned in this line. wb.security.workbook_password =...
0
votes
1answer
33 views

None values after reading more than 10000 rows of .xlsx file in openpyxl

I'm working on a application that can read out a large .xlsx (more than 20000 rows) with Openpyxl and can make some basic calculations with this data. I try to read out 20000 rows in the example below....