Export Safety Analysis Manager Link Information to Excel

This example shows how to programmatically export link information in Safety Analysis Manager spreadsheets to Microsoft® Excel®. After creating and saving link information, you export the information to Excel in two formats, where one file contains the spreadsheet and link information in two Excel sheets, and the other combines them in a single sheet.

Open the Spreadsheet

Open the Safety Analysis Manager spreadsheet myLinkTable.mldatx and retrieve the Spreadsheet object. The table contains a group of links between cells.

safetyAnalysisMgr.openManager
fileName = "myLinkTable.mldatx";
spreadsheetObject = safetyAnalysisMgr.openDocument(fileName);

Retrieve Cell Values and Link Information

First, you must retrieve the cell and link information and store it in a MATLAB® table. Create cell arrays with the same dimensions as the Safety Analysis Manager spreadsheet for the cell values and the links.

spreadsheetValuesCells = cell(spreadsheetObject.Rows,...
    spreadsheetObject.Columns);
spreadsheetLinksCells = cell(spreadsheetObject.Rows,...
    spreadsheetObject.Columns);

To set the table headings to the values in the spreadsheet, retrieve them with the getColumnLabels function.

columnLabels = getColumnLabels(spreadsheetObject);

Retrieve the cell values and link information by using the getCell and getLinks functions, and store them in a cell array with the same dimensions as the Safety Analysis Manager spreadsheet.

for row = 1:spreadsheetObject.Rows
    for col = 1:spreadsheetObject.Columns
        spreadsheetCellObject = ...
            getCell(spreadsheetObject,row,col);
        spreadsheetValuesCells{row,col} = ...
            spreadsheetCellObject.Value;
        spreadsheetLinksCells{row,col} = ...
            getLinks(spreadsheetCellObject);
    end
end

Next, convert the cell array for the Safety Analysis Manager spreadsheet cell values, spreadsheetValues, to a table.

spreadsheetValues = cell2table(spreadsheetValuesCells,...
    VariableNames=columnLabels);

Format the information in the cell array. Apply the formatLinksInfo helper function to each cell in the cell array that contains the link information. Convert the formatted cell array to a table.

spreadsheetLinksInfoCells = cellfun(@(x) formatLinksInfo(x),...
    spreadsheetLinksCells);
spreadsheetLinksInfo = array2table(spreadsheetLinksInfoCells,...
    VariableNames=columnLabels);

Export Spreadsheet Values and Links to Separate Sheets

Write the spreadsheet values to the first sheet and the link information to the second sheet. Create the Excel file and write to the sheets by using the writetable function. Save the Excel file with the name demoSpreadsheetSeparate.xlsx. Because you add a sheet to the Excel file, MATLAB generates a warning. Turn off the warning before saving the tables to the Excel file.

warning("off","MATLAB:xlswrite:AddSheet");
writetable(spreadsheetValues,...
    "demoSpreadsheetSeparate.xlsx",Sheet=1);
writetable(spreadsheetLinksInfo,...
    "demoSpreadsheetSeparate.xlsx",Sheet=2,AutoFitWidth=true);

When you open the Excel file, the file has two sheets. The first sheet contains the Safety Analysis Manager spreadsheet cell values and the column names.

The second sheet contains the link information of each corresponding cell. Each empty cell indicates that the associated cell in the Safety Analysis Manager does not have links.

Export Spreadsheet Values and Links to One Sheet

You can also export the values and links to one sheet. In this example, you export each column in the Safety Analysis Manager spreadsheet to a column that describes the link information. First, create different columns for the fault and link information by initializing the output cell array to have the same number of rows as the original Safety Analysis Manager spreadsheet, but double the number of columns.

outputNumRows = spreadsheetObject.Rows;
outputNumCols = spreadsheetObject.Columns*2;
spreadsheetValuesAndLinksCells = cell(outputNumRows,outputNumCols);
outputColConfig = cell(1,outputNumCols);

Next, assign the data to the columns. Assign the cell values to the odd columns and the link information to the even columns.

for col = 1:length(spreadsheetValuesAndLinksCells)
    if rem(col,2) == 1 % Cell value columns
        inputCol = (col+1)/2;
        spreadsheetValuesAndLinksCells(:,col) = ...
            spreadsheetValuesCells(:,inputCol);
        outputColConfig{col} = columnLabels{inputCol};
    else
        inputCol = col/2; % Link value columns
        spreadsheetValuesAndLinksCells(:,col) = ...
            spreadsheetLinksInfoCells(:,inputCol);
        outputColConfig{col} = append(columnLabels{inputCol},' Links');
    end
end

Then, convert the cell array to a table with the column names.

spreadsheetValuesAndLinks = cell2table(...
    spreadsheetValuesAndLinksCells,VariableNames=outputColConfig);

Finally, write the table to an Excel file named demoSpreadsheetTogether.xlsx.

writetable(spreadsheetValuesAndLinks,...
    "demoSpreadsheetTogether.xlsx",AutoFitWidth=true);

When you open the Excel file, the file has one sheet that contains both the cell values and link information. For example, the Function Name column shows the cell values, and the Function Name Links column shows the link information of the cell.

The table repeats this pattern for each cell in the Safety Analysis Manager spreadsheet.

Helper Function

The formatLinksInfo function reads the links in cellLinks and outputs the link information as a cell array of character vectors.

function linkInfo = formatLinksInfo(cellLinks)
    inLinks = cellLinks.inLinks;
    outLinks = cellLinks.outLinks;
    linkInfo = "";
    for inLinkID = 1:numel(inLinks)
        if inLinkID > 1
            linkInfo = append(linkInfo,newline);
        end
        linkInfo = linkInfo + inLinks(inLinkID).Type + ...
            " " + inLinks(inLinkID).Description;
    end
    for outlinkID = 1:numel(outLinks)
        if numel(inLinks) + outlinkID > 1
            linkInfo = append(linkInfo,newline);
        end
        linkInfo = linkInfo + outLinks(outlinkID).Type + ...
            " " + outLinks(outlinkID).Description;
    end
    linkInfo = cellstr(linkInfo);
end