With openpyxl for python3 to access excel 2007

Goal:

    We want to transfer the data from the data sheet to the Excel files, which we call Alias files.  We will use the Alias files to automatically generate documentation, code, and IDE support.

Example:

Here’s the SFR definition for ADC0CF from the data sheet.

Here is the same information entered into the Excel File:

Special Notes from the SFR definition

10) The SFR address and page information does not need to be stored in the Excel file.   When we generate documentation and IDE support files, the addresses will come from a different file.

11) The SFR Definition number is not stored in the Excel file.

12) We have a special mechanism for documenting functions in the Description field.  For now, you can simply do an approximation of the equation in the text.

Special Notes from the Excel file

13) Module Name.  Choose any name that is short and appropriate for now.  Use names similar to the corresponding 32-bit peripheral if available.

14) Offset.  In the Excel file, each register must have a unique offset.  Start at 0x00 and increment by 1 for each register.

15)   In this example, the reset value for the AD0SC bits is 11111b, so the reset value is 31, which gets entered into each row.

Consideration:

From above requirements, we can see that is hard work with copy and paste by hand. We need to think about make a code to automatically get peripheral register contents and write into excel files with proper style requirement. However, we can see some fields need human intelligence to name the field. That is Okay, we can leave them there and fill by hand later. And most of fields can be done by code.

Language:

Python is popular in company, so Python3 is our prefer script language.

Parse data sheet:

Since data sheet is PDF file. I have studied background knowledge of python parse PDF file. And finally I found this blog, http://chuckin-py.blogspot.com/2010/03/hacking-pdf-with-python.html. The author planed to do similar thing as I want. But he found that is hard to achieve it. I didn’t know PDF file even has not table. So I need other file type. And we have .txt file, it is easy to access.  We will have a look in it in detail later.

Access Excel 2007 file

Compared several 3^rd party tools, I choose openpyxl since it supports excel 2007 and python Here are the steps to install it in your PC, of course you already installed Python 3.

1.       Get distribut0.6.28

http://pypi.python.org/pypi/distribute

2.       Get openpyxl 1.5.8 for python 3

https://bitbucket.org/hjunes/openpyxl/

3.       Install distribut0.6.28

$ setup.py install

4.       Install openpyxl 1.5.8 for python3

$ setup.py install

Creating Excel file

Excel file requirement:

1.       Two sheets.

2.       Font Calibri, size 11

3.       First row is freeze pane, and Font is “bold”

4.       Each register first row is filled with yellow color

Achievement by openpyxl:

1.       Two sheets.

 wb = Workbook()

 ws = wb.worksheets[0]

 ws.title = 'version'

 ws = wb.create_sheet()

 ws.title = "Base_alias"

2.       Font Calibri, size 11

For excel 2007, default font is Calibri, and default font size is 11.

3.       First row is freeze pane, and Font is “Bold”

 ws._freeze_panes = 'A2'

 ws.cell('%s%s'%(col, 1)).style.font.bold = True

4.       Each register first row is filled with yellow color

ws.cell('%s%s'%(col, row)).style.fill.fill_type = Fill.FILL_SOLID

ws.cell('%s%s'%(col, row)).style.fill.start_color.index = Color.YELLOW

5.       Write data into cell

ws.cell('%s%s'%(col, row)).value = row_data[i]

Parse .txt file

.txt file overlook

Here is the example opened by notepad++ , it looks like:

It is not so regular, isn’t it?

Analysis txt file

The arrow in the snapshot is tab(‘\t’), and CRLF is ‘\n’.

1.       Every  register description start with "SFR Definition"

2.       Register bit type field start with 'Type\t'.

3.       Register bit reset field start with 'Reset\t'

4.       Register description start with 'Bit            Name    Function'

5.       We have several situations in description part.

a.       “7\t”

b.      “7:0\t”

c.       “1x:” and “01:”

d.      Description row

e.      Double “\n” indicates reach end of this register.

Handle data in txt file

With Python3 internal function we can deal with all string relate context.

Source code
https://github.com/MarkDing/Txt2xlsx.git