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
30
votes
11 answers

Python - Automatically adjust width of an excel file's columns

Newbie - I have a Python script that adjusts the width of different columns of an excel file, according to the values specified: import openpyxl from string import ascii_uppercase newFile = "D:\Excel Files\abc.xlsx" wb =…
Aditya
  • 615
  • 3
  • 12
  • 26
30
votes
3 answers

Reading Excel file is magnitudes slower using openpyxl compared to xlrd

I have an Excel spreadsheet that I need to import into SQL Server on a daily basis. The spreadsheet will contain around 250,000 rows across around 50 columns. I have tested both using openpyxl and xlrd using nearly identical code. Here's the code…
Ron Johnson
  • 303
  • 1
  • 3
  • 7
29
votes
4 answers

Openpyxl check for empty cell

openpyxl seems to be a great method for using Python to read Excel files, but I've run into a constant problem. I need to detect whether a cell is empty or not, but can't seem to compare any of the cell properties. I tried casting as a string and…
MechEngineer
  • 1,399
  • 3
  • 16
  • 27
29
votes
2 answers

Openpyxl: How to add filters to all columns

I can open a worksheet, how do I add the little filter menus to all columns without turning on any filters? I can do it in xlsxwriter with worksheet.autofilter(0, 0, 0, num_of_col) How do I do it in openpyxl?
azazelspeaks
  • 5,727
  • 2
  • 22
  • 39
29
votes
10 answers

Openpyxl - How to read only one column from Excel file in Python?

I want to pull only column A from my spreadsheet. I have the below code, but it pulls from all columns. from openpyxl import Workbook, load_workbook wb=load_workbook("/home/ilissa/Documents/AnacondaFiles/AZ_Palmetto_MUSC_searchterms.xlsx",…
lelarider
  • 391
  • 1
  • 5
  • 8
29
votes
1 answer

how to write to a new cell in python using openpyxl

I wrote code which opens an excel file and iterates through each row and passes the value to another function. import openpyxl wb = load_workbook(filename='C:\Users\xxxxx') for ws in wb.worksheets: for row in ws.rows: print row …
Priyaranjan
  • 407
  • 3
  • 9
  • 16
29
votes
7 answers

How to upload a file to sharepoint site using python script

Is there a way to upload a file on sharepoint site using python script? I tried installing haufe.sharepoint, but it seems like it failed to fetch ntlm while it was installing, and I can't even use the connector module without having ntlm…
user3590460
  • 371
  • 1
  • 3
  • 7
27
votes
5 answers

Python 3 openpyxl UserWarning: Data Validation extension not supported

So this is my first time that I'm attempting to read from an Excel file and I'm trying to do so with the openpyxl module. My aim is to collate a dictionary with a nested list as its value. However, when I get this warning when I try to run…
Tom_G_99
  • 461
  • 1
  • 5
  • 13
27
votes
5 answers

Apply 'wrap_text' to all cells using openpyxl

I have a Pandas dataframe that I am writing out to an XLSX using openpyxl. Many of the cells in the spreadsheet contain long sentences, and i want to set 'wrap_text' on all the contents of the sheet (i.e. every cell). Is there a way to do this? I…
DerRabe
  • 313
  • 1
  • 3
  • 6
27
votes
2 answers

Openpyxl How to get row from worksheet by index

Using Openpyxl and python3.5, I tried getting the first row from an excel worksheet using a subscript but I an error. # after getting filename # after loading worksheet # to get the first row of the worksheet first_row = worksheet.rows[0] # I get…
Damilola Boiyelove
  • 1,119
  • 1
  • 9
  • 18
27
votes
3 answers

how to create a new xlsx file using openpyxl?

Does anybody knows how to create a new xlsx file using openpyxl in python? filepath = "/home/ubun/Desktop/stocksinfo/yyy.xlsx" wb = openpyxl.load_workbook(filepath) ws = wb.get_active_sheet() What do I need to add?
Newboy11
  • 2,778
  • 5
  • 26
  • 40
27
votes
6 answers

How to save XLSM file with Macro, using openpyxl

I have .xlsm file with a Macro function. I'm loading it using openpyxl and write some data to the file and finally want to save as a different .xlsm file. To save the file as XLSM file I have used below code in my Python…
AnujAroshA
  • 4,623
  • 8
  • 56
  • 99
27
votes
4 answers

Modify an existing Excel file using Openpyxl in Python

I am basically trying to copy some specific columns from a CSV file and paste those in an existing excel file[*.xlsx] using python. Say for example, you have a CSV file like this : col_1 col_2 col_3 col_4 1 2 3 4 5 …
paul suk
  • 271
  • 1
  • 3
  • 3
26
votes
2 answers

How to count the total number of sheets in an Excel file using Python

I am reading a excel file using python. import pandas as pd import os xls = pd.ExcelFile('D:\DirectoryProject\Mapping.xlsx') It has several number of data sheets which I don't know. How can I count the total number of sheets in Mapping.xlsx file…
user4444350
26
votes
4 answers

adding hyperlinks in some cells openpyxl

I have to generate an excel with summary results. The results are included in a list. Some of the elements are values and some links. I managed to generate the excel with the right format but not generate the hyperlink in some of the cells My…
gis20
  • 1,024
  • 2
  • 15
  • 33
1 2
3
…
99 100