BoardSurfers: Reading and Writing Excel Files in Allegro SKILL

Allegro SKILL scripting capabilities combined with the familiar interface of Excel spreadsheets create a dynamic platform for PCB design automation. The Allegro SKILL language provides a set of functions to read and write Excel files in Microsoft's XML-based spreadsheet format. This post describes how to use these functions to read and write an Excel spreadsheet. The following image shows a sample Excel worksheet created using Allegro SKILL APIs:

Allegro SKILL functions work with Excel files only in the Microsoft XML spreadsheet format. If you open a spreadsheet in any other format, for example .xlsx, an error message similar to the following message is flagged:

SPMHWB-110):Error importing XML file invalid byte 'h' at position 3 of a 3-byte sequence.
(SPMHWB-110):Error importing XML file excel-test.xlsx.

To work with Allegro SKILL functions, open the Excel file and save it in the XML format, XML Spreadsheet 2003 (*.xml), as shown in the following image:

Writing Excel Files in XML Format Using Allegro SKILL Functions

The process of writing to an Excel file using Allegro SKILL functions consists of the following steps:

1. Initialize the Excel spreadsheet using the axlSpreadsheetInit() function.

2. Define formatting styles for specifying data, such as bold or italicized text or color shading of a cell. Use the axlSpreadsheetSetStyle() and axlSpreadsheetSetStyleProp() functions for this.

3. Set document properties, such as author, last author, date, company, or version, using the axlSpreadsheetSetDocProp() function.

4. Open the worksheet using the axlSpreadsheetSetWorksheet() function.

5. Write data to a cell by providing the row and column number in the format you want, using one of the following functions: axlSpreadsheetDefineCell(), axlSpreadsheetSetCellProp(), or axlSpreadsheetSetCell().

6. Repeat Step 5 for each cell where you want to write data, and follow Step 4 if you want to add another worksheet.

7. Write the spreadsheet using the axlSpreadsheetWrite() function.

8. Close the worksheet using the axlSpreadsheetClose() function.

Writing Data to a Cell

There are two ways to write data in a cell, as explained in the following steps:

1. Select the cell using the axlSpreadsheetSetCell() function.

2. Set the cell property using the axlSpreadsheetSetCellProp() function.

For example,
axlSpreadsheetSetCell(1, 1)
axlSpreadsheetSetCellProp("VALUE", "New Value")

Alternatively, you can use the axlSpreadsheetDefineCell() function to define a single cell in the active worksheet. This function is more efficient than calling axlSpreadsheetSetCell() followed by multiple axlSpreadsheetSetCellProp() calls, as shown in the following example:

axlSpreadsheetDefineCell(1, 1, "Default", "String", "New Value")

If data is written multiple times to the same cell, it will be overwritten without displaying any warning or error. For example:

axlSpreadsheetDefineCell(3 2 "Default" "Number" 999)
axlSpreadsheetDefineCell(3 2 "Default" "Number" 100)

The value in cell (3, 2) will be the number 100, not 999.

Defining Style to Cell

A style defines the appearance of a cell, such as its font, font size, and color. You can define the style only once and then reuse it in the same worksheet. Additionally, styles may be defined at any point when creating a spreadsheet, and referenced after they are defined.

1. To activate a style in the active spreadsheet, use the axlSpreadsheetSetStyle() function.

2. To set a specific style property, use the axlSpreadsheetSetStyleProp() function.

For example, the following code activates the default style in the active spreadsheet and sets its vertical alignment style to top justified:

axlSpreadsheetSetStyle("Default", nil)
axlSpreadsheetSetStyleProp("Alignment", "Vertical", "Top")

Defining the Border of a Cell

The border of a cell is the line that surrounds the cell. To set the cell border properties for an active style definition, use the axlSpreadsheetSetStyleBorder() function. 

For example, the following code defines a thin, continuous border on the left and top edges of the cell:

axlSpreadsheetSetStyleBorder("Left", nil, "Continuous", "2")
axlSpreadsheetSetStyleBorder("Top", nil, "Continuous", "2")

Examples of Writing an Excel File

Example 1

The following SKILL code creates an Excel workbook, example.xml, with one worksheet, First, and writes Hello in cell (1,1):

axlSpreadsheetInit() axlSpreadsheetSetWorksheet("First") axlSpreadsheetDefineCell(1, 1, "Default", "String", "Hello") axlSpreadsheetWrite("example.xml") axlSpreadsheetClose() axlSpreadsheetClose()

Example 2

The following SKILL code creates a style, Red, and uses it in the active worksheet:

; Define styles. axlSpreadsheetSetStyle("Red" "Red Cell") axlSpreadsheetSetStyleParent("Default") axlSpreadsheetSetStyleProp("Fill" "Color" axlSpreadsheetGetRGBColorString(255 0 0)) axlSpreadsheetSetStyleProp("Fill" "Pattern" "Solid") ;Use the Style axlSpreadsheetDefineCell(2 1 "Default" "String" "Default 2 cell") axlSpreadsheetDefineCell(2 2 "Red" "String" "Red background cell") .

Reading Excel File Using Allegro SKILL Functions

The following steps describe how to read an Excel file using Allegro SKILL APIs:

1. Read the Excel spreadsheet using the axlSpreadsheetRead () function.

2. Open the Excel worksheet using the axlSpreadsheetSetWorksheet() function.

3. Read the data from each cell using the axlSpreadsheetGetCell() function.

4. Close the worksheet using the axlSpreadsheetClose() function.

Note that the row and column indexing in the Allegro SKILL APIs starts from 1. To read a cell in the first row and first column, use axlSpreadsheetGetCell(1,1). Similarly, to read the cell in the second row and third column, use axlSpreadsheetGetCell(2, 3).

The value returned by axlSpreadsheetGetCell(1,1) includes information, such as a row, color, style, data type, and data itself. For example,

g_cell = axlSpreadsheetGetCell(1 1)
g_cell->??
==> (column 4 row 1 data "a" type "String" style "Default" 

For details on the APIs, refer to the Allegro SKILL Reference user guide.

Tip: Use axlSpreadsheetReadDelimited function if you have text-delimited files.

Examples of Reading  Excel File

The following Allegro SKILL code shows how to read an Excel file, example.xml, created earlier in this post:

axlSpreadsheetRead("example.xml")
workSheetsList = axlSpreadsheetGetWorksheets()
(foreach curWorkSheet workSheetsList
axlSpreadsheetSetWorksheet(curWorkSheet)
; Get the number of rows in the Excel worksheet.
worksheet_size = axlSpreadsheetGetWorksheetSize()
num_rows = car(worksheet_size)
num_cols = nth(1 worksheet_size)
printf("Worksheet %L size is rows %L cols %L\n" curWorkSheet num_rows num_cols)
; Iterate over each row in the Excel worksheet.
row = 1
(while row <= num_rows
; Get the cell value in column A.
cell_value = axlSpreadsheetGetCell(row, 1)
; Print the cell value
printf("cell_value is %L\n" cell_value->??)
row = row + 1
)
)

Conclusion

By embracing the automation and customization capabilities of Allegro SKILL language and Excel, you can reduce manual data entry and repetitive tasks. This paves the way for innovative solutions and groundbreaking designs. It reduces human error, saves time, and increases the accuracy of your designs. The Allegro SKILL language offers an extensive set of functions for reading and creating Excel spreadsheets, writing data to a cell, defining styles, and setting cell border properties that can be used to enhance productivity.

Contact Us

For any feedback or any topics you want us to include in our blogs, write to us at pcbbloggers@cadence.com.

Subscribe to stay updated about our upcoming blogs. 

About BoardSurfers

The BoardSurfers series provides solutions to the various tasks related to the creation and management of PCB design using the Allegro platform products. The name and logo of this series are designed to resonate with the vision of making the design and manufacturing tasks enjoyable, just like surfing the waves. Regular, new blog posts by experts cover every aspect of the PCB design process, such as library management, schematic design, constraint management, stackup design, placement, routing, artwork, verification, and much more.