MoonPoint Support Logo


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals

Advanced Search
Sun Mon Tue Wed Thu Fri Sat
19 20 21 22 23 24
25 26 27 28 29 30 31
Apr May Jun
Jul Aug Sep
Oct Nov Dec

Mon, Feb 19, 2018 11:24 pm

xlrd and hidden worksheets

I use the xlrd module in Python scripts to extract data from Excel workbooks. You can use the Python xlrd module to list the worksheets in a workbook and you can use the xlrd.sheet "visibility" value to determine whether a sheet is hidden and, if it is hidden, whether a user can unhide the sheet. The value should be either 0, 1, or 2 with the numbers having the following meaning:

Visibility of the sheet:

0 = visible
1 = hidden (can be unhidden by user -- Format -> Sheet -> Unhide)
2 = "very hidden" (can be unhidden only by VBA macro)

[ More Info ]

[/languages/python/excel] permanent link

Wed, Feb 14, 2018 9:31 pm

Extracting embedded Microsoft Office files from an Excel spreadsheet

I work with Excel workbooks on my MacBook Pro laptop that have embedded PowerPoint slides on some worksheets. The workbooks, which I need to review, are created by others. When I review them, I extract information from the Excel workbooks to an SQLite database with Python and also have begun extracting information embedded by Object Linking and Embedding (OLE) into files as noted in Extracting the contents of a directory in a zipfile using Python. Some of the embedded files are PowerPoint files, but when they are extracted they have a .bin extension, which I can't open in PowerPoint without changing the filename extension from .bin to .ppt. To automate the renaming process, I created a Python script, that will extract the embedded information to files in an "embedded" directory beneath the current working directory and then rename any .bin files that are PowerPoint files to have a .ppt extension. The script is shown below.

[ More Info ]

[/languages/python/excel] permanent link

Sat, Feb 10, 2018 10:55 pm

Extracting the contents of a directory in a zipfile using Python

A Microsoft Excel file with an .xlsx or .xlsm filename extension is an Office Open XML (OpenXML) zipped, XML-based file. The OpenXML format was developed by Microsoft for spreadsheets, charts, presentations and word processing documents. If you change the file extension to .zip by renaming the file, you can extract the contents of the zip file as you would with any other zip file - see Zipping and unzipping Excel xlsx files. Excel workbooks can contain other documents embedded within them using Object Linking and Embedding (OLE) technology - see Using olefile to obtain metadata from an OLE CDF V2 file. I often need to extract an embedded PowerPoint slide or Visio diagram from Excel .xlsm files, so I've been renaming the files to zip files and unzipping them as I would other zip files, but, since I want to automate the process and extract just specific embedded files for further processing within a Python script, I created the script below to extract the embedded files, which are contained within a xl/embeddings subdirectory within the .xlsm zip files. The script uses the zipfile module to deal with the zip files. Python's OS module is used to check for the existence of the destination directory and create it, if it doesn't yet exist.


import os, zipfile

dirToExtract = "xl/embeddings/"
destinationDir = "embedded"
infile = raw_input("Enter zipfile: ")
archive = zipfile.ZipFile(infile)

if not os.path.exists(destinationDir):

for file in archive.namelist():
    if file.startswith(dirToExtract):
        archive.extract(file, destinationDir)

The script prompts for the file to be unzipped and then extracts just the "xl/embeddings" folder and the files contained within it to a new directory it will create within the current working directory. The new directory will be named "embedded". After extracting the contents of the "xl/embeddings" directory to the newly created "embedded" folder, I had the the files below in the case of the particular .xlsm file I used for this example.

[ More Info ]

[/languages/python/excel] permanent link

Fri, Feb 09, 2018 10:04 pm

Using the Python xlrd module to list the worksheets in a workbook

To view the list of sheets in an Excel spreadsheet, I can use the xlrd module within the Python script below to obtain the list of worksheets within the workbook.


import xlrd as xl

file_name = raw_input("File: ")
workbook = xl.open_workbook(file_name)
print workbook.sheet_names()

If I use the script to display the list of worksheets in a workbook named report.xlsx that has three sheets named alpha, beta, and gamma, I would see the following output:

$ ./
File: report.xlsx
[u'alpha', u'beta', u'gamma']

[ More Info ]

[/languages/python/excel] permanent link

Sat, Feb 03, 2018 10:34 pm

Using PyInstaller to create an executable file from a Python script

If you wish to convert Python scripts to executable files that you can run on systems where Python or all of the needed dependencies for the script are not installed, one program that is available for Linux, Mac OS X, Solaris, AIX, or Microsoft Windows systems is PyInstaller. If you have the pip package manager installed, you can install PyInstaller by running the command below from the root account.

pip install pyinstaller

To then create an executable file that will run on other sytems with that same operating system, e.g., you can create an executable file on one Linux system that will run on another Linux system or create an .exe file on a Microsoft Windows system that can be ported to another Windos system, you can issue the command pyinstaller

[ More Info ]

[/languages/python] permanent link

Fri, Feb 02, 2018 11:16 pm

Extracting information from a .msg file with Python

I received a .msg file attachment to an email message I received with Microsoft Outlook for Mac, which is part of Microsoft Office 2016 on my MacBook Pro laptop. When I double-clicked on the attachment in Outlook to view the contents of the file, I saw "There is no application specified to open the document Re_ Netbond.msg."

Msg - Open 

And also a window giving me an option to "Search App Store" with the message "Search the App Store for an application that can open this document, or choose an existing application on your computer."

[ More Info ]

[/languages/python] permanent link

Wed, Jan 31, 2018 11:48 pm

Using olefile to obtain metadata from an OLE CDF V2 file

Microsoft's Object Linking and Embedding (OLE) technology allows embedding and linking to documents and other objects. OLE allows the addition of different kinds of data to a document from different applications, such as a text editor and an image editor. This creates a Compound File Binary Format (CFBF), aka a Compound File, Compound Document format, or Composite Document File V2 (CDF V2) document.

While using my MacBook Pro laptop, which is currently running the OS X El Capitan (10.11.6) operating system, I often need to extract embedded documents from an Excel .xlsm file. I do that by renaming the file to have a .zip rather than a .xlsm file extension. I can then extract the files contained within the .zip file just as I would any zip file. Within the directory structure created by unzipping the zip file there is an xl/embeddings subdirectory with .bin files within it.

$ ls xl/embeddings
Microsoft_Visio_Drawing1.vsdx	oleObject2.bin
Microsoft_Visio_Drawing2.vsdx	oleObject3.bin

[ More Info ]

[/languages/python] permanent link

Tue, Jan 30, 2018 11:22 pm

Using Python to extract data from a spreadsheet and put it in a database

I need to review spreadsheets related to work requests on a daily basis. The Excel workbooks have multiple worksheets within them. I have been manually copying data from specific cells in one of the worksheets and pasting it into a SQLite database. I use DB Browser for SQLite to paste the information into the database. I wanted to automate the process of extracting the data from the .xlsm or .xlsx workbook files and inserting it into the SQLite database using a Python script. So I initially created a script that I could run from a Teminal window on my MacBook Pro laptop, as noted in Extracting data from cells in an Excel workbook with Python, that would just print the information to the Terminal window's command-line interface. I had another script to import data from a text file into an SQLite database with Python, so I combined code from that script with the one to read data from the spreadsheets to add the data to a table in the database.

[ More Info ]

[/languages/python/excel] permanent link

Fri, Jan 26, 2018 10:51 pm

Extracting data from cells in an Excel workbook with Python

I've been using xlrd to extract a column from an Excel spreadsheet with a Python script on my MacBook Pro laptop. With xlrd, you can read data from cells in a workbook by specifying a specific sheet and the cells from which you wish to extract the data in spreadsheet programs, such as Microsoft Excel, Apache OpenOffice Calc, or LibreOffice Cale.

With spreadsheet applications you normally have rows sequenced by numbers and columns sequenced by alphabetical characters. With xlrd, though, both rows and columns are specified by numbers with the first row starting at 0 and the first column starting at 0 as well.

[ More Info ]

[/languages/python/excel] permanent link

Sun, Oct 29, 2017 10:10 pm

Decoding base64-encoded text in a .eml file with Python

I received an email message from someone that contained an attachment that had a .eml filename extension. When I viewed that file, I found the usual email header fields, i.e., "from", "to", "cc", and "subject", but for the body of the message I saw the following:

Content-Type: multipart/alternative;
MIME-Version: 1.0

Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64

<text snipped>


I needed to decode the base64 encoded text. Fortunately, Python has a base64 module that can be used for that purpose. So I created the following Python script to decode the encoded portion of the .eml file. The script expects the name of the input file to be provided on the command line and will print an error message and terminate if the file name isn't provided. If the filename is provided, the script will read the file line by line looking for the "Content-Transfer-Encoding: base64" which indicates that after one following blank line the encoded text will commence. The output will be displayed on the screen but can be redirected to a file.


import base64, sys

except IndexError:
   print "Error - missing input file name! Usage", sys.argv[0], "infile"
   fileName = sys.argv[1]

base64_marker = "Content-Transfer-Encoding: base64"
block_of_lines = ""

with open(fileName) as input_data:
    # Skips text before the beginning of the base64 encoded block:
    for line in input_data:
        if line.strip() == 'Content-Transfer-Encoding: base64':
    for line in input_data:  # Skip blank line
    # Reads text until the end of the block:
    for line in input_data:  # Append lines to block_of_lines
        block_of_lines = block_of_lines + line
    print base64.b64decode(block_of_lines)

[/languages/python] permanent link

Once You Know, You Newegg AliExpress by

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo