Stack Overflow
Stack Overflow

Questions tagged [openpyxl]

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

Openpyxl is a Python library to read/write Excel 2010 xlsx, xlsm, xltx, and xltm files.

It was born from lack of an existing library to read/write natively from Python the Office Open XML format.

All kudos to the PHPExcel team as openpyxl was initially based on PHPExcel.

Features:

  1. Tables
  2. Data validation
  3. Charts

About security:

By default Openpyxl does not guard against quadratic blowup or billion laughs XML attacks. To guard against these attacks, install defusedxml.

Project documentation can be found at openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files.

5440 questions
1
vote
0 answers

openpyxl: 'MergedCell' object attribute 'value' is read-only error

I was trying to copy data from one excel sheet to another using openpyxl but I am getting ('MergedCell' object attribute 'value' is read-only) error. It is working fine when I am copying data from datasheets which doesn't contain merged cells.How…
Rohan
  • 11
  • 3
1
vote
1 answer

Openpyxl: use excel theme colors in chart

I'm trying to use Excel's theme colors in a chart created by openpyxl. I want to have theme colors so that the entire workbook's colors can be updated when the user loads a new theme. Openpyxl has functionality to color the series bars in a bar…
deseosuho
  • 958
  • 3
  • 10
  • 28
1
vote
2 answers

Auto-size column width

Is there way to auto-size (auto-fit) the width based on cell contents for the entire worksheet. sheet.column_dimensions['A'].width=number I am not looking for the above, for which you have to specify the column. Is there a way to set it up with…
Jay
  • 17
  • 2
1
vote
1 answer

I can't seem to get my openpyxl code working

My current challenge is to write some code which does the following: Takes user input which will be designated as batchNumber Takes user input which will be designated as batchLocation Look through each row in a .xlsx file named customerData Match…
Norcofox
  • 11
  • 1
1
vote
1 answer

openpyxl pivot table and pandas pivot table

How create pivot table using openxlpy library which is equivalent to the following pandas pivot table function? import pandas as pd import numpy as np # creating a dataframe df = pd.DataFrame({'A': ['John', 'Boby', 'Mina', 'Peter', 'Nicky'],…
sahil makandar
  • 131
  • 12
1
vote
4 answers

Iterate through each row in an account-number-column and use those to read other excel files

I need to perform some data analysis on some excel files (that are saved as account numbers of respective customers). I also have a master sheet with all the account numbers in a column. I need to iterate over the "Account Number" column in…
Kishore Kunal
  • 9
  • 2
1
vote
1 answer

How can I customize Bar Chart labels?

I wrote the code chart2_values = Reference(sheet, min_col=6, min_row=1, max_col=10, max_row=13) chart2_labels = Reference(sheet, min_col=5, min_row=2, max_row=3) chart2 = BarChart(gapWidth=50,) chart2.add_data(chart2_values,…
Rafii1
  • 29
  • 5
1
vote
1 answer

How to Create/Add multiple charts in ChartSheet using openpyxl?

There is option in excel sheet which allows user to export charts to chartsheet. Manually one can add any number of charts but while using openpyxl module I could only add one chart, when I try to add more than one chart its not showing up in the…
uttam hm
  • 11
  • 3
1
vote
2 answers

Comma separated strings to excel cell with python

I'd like to push contents from the string to xls contents are abc,[1,2],abc/er/t_y,def,[3,4],def/er/t_d,ghi,ghi/tr/t_p,jkl,[5],jkl/tr/t_m_n,nop,nop/tr/t_k this is my sample code (using xlwt) workbook = xlwt.Workbook() sh =…
AmRey
  • 27
  • 1
  • 6
1
vote
2 answers

Calculate formula downwards with openpyxl

I have a a spreadsheet with column A containing dates. I am using a formula to see the difference in days between A1 and A2, A1 and A3 etc, using this excel formula in column B. =datedif(A1,$A1$1,"D") When dragging down manually in excel it…
Lord Beerus
  • 69
  • 1
  • 1
  • 5
1
vote
1 answer

How to input multiple items from a list of values into a spreadsheet

I'm attempting to have list items stored in a variable populate cells of a spreadsheet until the list runs out of items (so it works on a list of any size). I have tried a variety of for loops and slices with no luck. import openpyxl wb =…
Max Maher
  • 13
  • 2
1
vote
1 answer

Find and replace using Openpyxl

I am trying to replace anything in column C that has -1 as a value to 0. Here is what I have tried so far. I am new to python and openpyxl so please be patient with me. import openpyxl excelFile =…
ThisGuyUsesPowershell
  • 51
  • 2
  • 9
1
vote
1 answer

How do I loop through each source file and copy a specific column into a new workbook with each new "paste" shifting to the adjacent column?

I have 3 Excel files with a column of data in cells A1 to A10 (the "Source Cells") in each workbook (on sheet 1 in each workbook). I would like to copy the data from the Source Cells into a new workbook, but the data must shift into a new column…
Bandit King
  • 183
  • 10
1
vote
1 answer

How to give font color to a range of cells (columns and rows) in excel worksheet using openpyxl and python3?

I want to set color red to a series of cells(one column to other) in an excel sheet and using openpyxl. I was able to find how to give cells a certain color from the official documentation but I was unable to figure out how to give a…
Aviral Srivastava
  • 4,058
  • 8
  • 29
  • 81
1
vote
1 answer

Progress bar when loading a workbook with openpyxl

I'm wondering if it's possible for me to show a progress bar (or anything like that) of the workbook I'm loading, so the user can know how long it would take. I'm using openpyxl to load the workbook. Code sample: from openpyxl import…
Breno
  • 11
  • 2
1 2 3
…
99 100