FAQ Database Discussion Community


reading worksheet and preserving conditional formatting

python,excel,openpyxl
I am trying to read an excel worksheet with openpyxl. I think I am losing the conditional formatting information in the sheet when I read it like so: xl = openpyxl.load_workbook(filename) When I read all cells in the file and save it. I get a spreadsheet in which none of...

openpyxl returning empty cell values for formula series

python,openpyxl
Vitals: python 3.4.3 | openpyxl 2.2.3 | Excel 2013 Like everyone knows you can use Excel's fill handle to quickly set up a number series down a column [1,2,3,4,5,6,etc] and this works with formula as well [=sum(B1,C1), =sum(B2,C2), =sum(B3,C3), etc]. However when I attempt to use openpyxl to nab formula...

openpyxl 'NoneType' object has no attribute 'font'

python,fonts,openpyxl,nonetype
I'm writing a function that puts values into a spreadsheet and colors the cell based on the value. Now for some reason I'm getting a weird 'NoneType' error that's completely stumping me: Traceback (most recent call last): File "vix.py", line 168, in <module> printexceldaily(1990, 2015, 'All_Lows', 10, 15, 18, 28)...

Is openpyxl iter_rows() skipping last row?

python,openpyxl,import-from-excel
I have difficulties using openpyx iter_rows. The source excel has rows in the first column (A) : header 1st data row ... 32nd data row The code: start, stop = 0, 1000 for row_index, row in enumerate(source_ws.iter_rows()): if start < row_index < stop: # something meaning here does not visit...

Python module imports Visual Studio

python,visual-studio-2013,ironpython,openpyxl
I have installed openpyxl. Working through the examples in Idle, I encounter no issues. Trying to use my VisualStudio python editor, module imports fail. Does openpyxl need to be added to IronPython for this to work? If so, how?...

Editing workbooks with rich text in openpyxl

python,excel,text,openpyxl
I was wondering if openpyxl can read and/or write rich text into excel. I am aware that this question was asked once before in 2012 linked below, but I am not sure if this has changed. As it stands load_workbook() seems to throw away rich text formatting. As for a...

How to create a hyperlink to a different excel sheet in the same workbook using openpyxl

python,excel,hyperlink,openpyxl
I'm using the module openpyxl for python and am trying to create a hyperlink that will take me to a different tab in the same excel workbook. Doing something similar to the following creates the hyperlink; however, when I click on it, it tells me it can't open the file....

Can't save excel file using openpyxl

python,excel,openpyxl
I'm having an issue with saving an Excel file in openpyxl. I'm trying to create a processing script which would grab data from one excel file, dump it into a dump excel file, and after some tweaking around with formulas in excel, I will have all of the processed data...

Openpyxl - trouble naming workbook

python,xlsx,openpyxl
I have a python script that analyses a file tree and records its findings in an xlsx. Analysis is going fine, but when I try to record my results, I get an error: Traceback (most recent call last): File ".\call_validation.py", line 103, in <module> wb.save(wb_name) File "C:\Python\lib\site-packages\openpyxl\workbook\workbook.py", line 298, i...

How we can draw two series of data in “openpyxl” package of python (line-chart)

python,canopy,openpyxl
Suppose that we have this code: from openpyxl import Workbook wb = Workbook() ws = wb.active for row in range(1,10): value = ws.cell(row=row,column=1).value = row+5 for row in range(1,10): value2 = ws.cell(row=row,column=2).value = row wb.save("SampleChart.xlsx") from openpyxl.charts import Reference, Series,LineChart values = Reference(ws, (1, 1), (9, 1)) series = Series(values,...

Writing pandas DataFrame to Excel with different formats for different columns

python,excel,pandas,openpyxl
I am trying to write a pandas DataFrame to an .xlsx file where different numerical columns would have different formats. For example, some would show only two decimal places, some would show none, some would be formatted as percents with a "%" symbol, etc. I noticed that DataFrame.to_html() has a...

What causes “UserWarning: Discarded range with reserved name” - openpyxl

python-2.7,openpyxl,reserved
can anyone help me with this? I have a simple EXCEL-sheet with names of cities in column A and I want ot extract them and put them in a list: def getCityfromEXCEL(): wb = load_workbook(filename='test.xlsx', read_only=True) ws = wb['Sheet1'] cityList = [] for i in range(2, ws.get_highest_row()+1): acell = "A"+str(i)...

Resize columns in Excel document with Python

python,excel,python-2.7,openpyxl
I am currently working with creating an Excel document in Python. I create the excel document but I'm not sure what is wrong with the code that it is not resizing the columns correctly. Does anyone have any ideas? def writerow(self, vals): ws = self.workbook.active this_row = self.numrows this_col =...

Identifying cell in Openpyxl

python,excel,openpyxl
I've been working on a project, in which I search an .xlsx document for a cell containing a specific value "x". I've managed to get so far, but I can't extract the location of said cell. This is the code I have come up with: from openpyxl import load_workbook wb...

Change excel cell number format “General” to “Text”, using openpyxl 1.8.6

openpyxl
How to change excel cell number format General to Text irrespective of data in cell? I am using openpyxl 1.8.6....

how to draw line chart with date fomat on excel using openpyxl of python

python,excel,openpyxl
I draw a line chart on excel using openpyxl In excel sheet: date value "2015-05-01" | 0.1 "2015-05-02" | 0.2 "2015-05-03" | 0.3 "2015-05-04" | 0.4 But it gives integer format for date data like "45612"... How could I draw line chart with date format? (date is x-axis, and value...

Openpyxl Unicode Values

python,unicode,openpyxl
I am using openpyxl to read in cell values from a Excel Spreadsheet. One of the cells has values that are separated by a newline. I want to split the string using the newline character as the delimiter. However it seems that openpyxl is serializing the carriage return into a...

Writing data into Excel-Sheet using openpyxl isn't working

python,excel,openpyxl
Using openpyxl, I am trying to read data from an Excel-Workbook and write data to this same Excel-Workbook. Getting data from the Excel-Workbook works fine, but writing data into the Excel-Workbook does not work. With the code below I get the value from Cell A1 in Sheet1 and print it....

Python: openpyxl how to read a cell font color

python,openpyxl
I have tried to print some_cell.font.color.rgb and got various results. For some I got what I want (like "FF000000"), but for others it gives me Value must be type 'basetring'. I assume that the latter is because I haven't actually defined the font color for these cells. I'm using openpyxl...

Adding print titles to a worksheet using openpyxl

python,excel,openpyxl
I'm unable to add print titles to my Excel worksheet using openpyxl. The named range is created properly but it doesn't properly become the named range for the print titles. The problem seems to be that the scope of the print titles named range is incorrect (at least, if I...

python - fill cells with colors using openpyxl

python,openpyxl
I am currently using openpyxl v2.2.2 for Python 2.7 and i wanted to set colors to cells. I have used the following imports import openpyxl, from openpyxl import Workbook from openpyxl.styles import Color, PatternFill, Font, Border from openpyxl.styles import colors from openpyxl.cell import Cell and the following is the code...

Locating merged cell ranges in pyopenxl

python,pandas,openpyxl
I'm working on extracting some data from a .xlsx file using pyopenxl and Pandas. I can't find a cell property (or indeed any other information) that indicates where I can find out which cells are merged in the spreadsheets. How do I know which cells are merged together?...

How to get header and data from Models in Python?

python,django,model,xlsx,openpyxl
Following this example (for openpyxl): from openpyxl.workbook import Workbook header = [u'Name', u'Email', u'Mobile', u'Current location',] new_data = [[u'name1', u'[email protected]', 9929283421.0, u'xxxx'], [u'name2', u'[email protected]', 9994191988.0, u'xxxx']] wb = Workbook() dest_filename = 'empty_book.xlsx' ws1 = wb.active ws1.title = "range names" ws1.append(header) for row in new_data: ws1.append(row) wb.save(filename = dest_filename) I have...

Python selenium using openpyxl to write result to excel file but there is only case is written to excel file

python,excel,selenium,openpyxl
I just start learning about Selenium using Python last week, i write a test case with 4 step, at step 3 and 4 if this pass/fail then it will be write result to a excel file. But when i run the test case, there is only one step can write...