Using Edit in Excel

If Microsoft Excel is installed, users can edit worksheets and HTML tables in Excel and re-import the edited worksheet or table into Phoenix. Excel can be used to enter formulas into worksheets or change the formatting of HTML tables.

The following topics are discussed in this section:

Editing a worksheet with Microsoft Excel
Editing an HTML table in Microsoft Excel

Note:Copying and pasting from Phoenix to Excel might not retain the same number of digits or trailing zeros, since the tools could have different column formats. An alternative method is to send the data to a Table object and create a table with the specified number of decimals or significant figures for each table column. Then right-click the Table and select Copy to Tables folder. Select the copied table in the Tables folder and right-click to select Edit in Excel, which will open the data in Excel with preserved formatting.

Editing a worksheet with Microsoft Excel

If a worksheet has no dependents, which means it is not mapped or connected to an operational object, and it is located in the Data folder, then it can be edited in Excel. In order to edit a Phoenix object's result worksheet in Excel, it first must be copied to the Data folder by right-clicking the worksheet and selecting Copy to Data Folder.

In the Object Browser, select a worksheet in the Data folder.

Select Edit > Edit in Excel.
Or right-click the worksheet and select Edit in Excel.

A dialog is displayed warning that all editing done in Excel is logged as a single, generic audit entry.

Click OK to open the worksheet in Excel.

Edit the worksheet using the Excel tools.

Note:While a worksheet is being edited in Excel, it cannot be mapped to any operational object.

When finished editing, save the worksheet in Excel using File > Save and close Excel.
Be sure to save the worksheet before closing Excel, or all changes will be lost.

Caution:Because of the way Phoenix handles its interactions with Excel, users cannot use the Save As option in Excel to save the worksheet with a different name or to a different location. The Save option must be used. If the worksheet name or file location is changed, the Excel edits cannot be imported into Phoenix.

In the dialog, click Yes to apply the changes.
An entry is written in the worksheet’s History tab noting that it was edited in Excel.

In the next dialog, click Yes to save formulas in the worksheet.
The worksheet is no longer editable in Phoenix, but it can be edited in Excel and the worksheet can still be used with operational objects.
If formulas are not saved, only the formula results are saved, and the worksheet can be edited in Phoenix. However, the formulas will not be available the next time the worksheet is edited in Excel.

In addition to editing the data in a worksheet, users can edit the units associated with each column. If a worksheet has a units row, edit the units in that row. If a worksheet does not have a units row, entering units adds the units to a row in the worksheet, but not the column headers.

When the worksheet is open in Excel, it is stored in a temp directory located at C:\Users\<user name>\AppData\Local\Temp\Phoenix. The temp directory’s name starts with PhxExcel_, followed by a unique identifier.

Note:For worksheets edited in Excel, with formulas saved, if the Refresh from Source option in the edited right-click menu is used, the worksheet is empty. To get the data back, use the Edit in Excel option again, make a change to the dataset and then save the changes back to Phoenix, the worksheet will be restored.

Editing an HTML table in Microsoft Excel

The Table object in Phoenix creates tables in HTML format. To edit a table in Excel, it first must be copied to the Table folder by right-clicking the table output and selecting Copy to Tables Folder.

In the Object Browser, select a worksheet in the Table folder.

Select Edit > Edit in Excel.
Or right-click the worksheet and select Edit in Excel.

When finished editing in Excel, select File > Save As and change the filename.
Excel cannot publish to the same file that is being edited.

In the Save as type menu, make sure that Web Page (*.htm;*.html) is selected.

Click Publish.

In the next dialog, click Publish.

Close Excel without saving.

When asked to apply the changes to the table, click Yes.
An entry is written in the table’s History tab noting that it was edited in Excel.

In the next dialog, select the renamed HTML file and click Open.
The HTML file’s name is the same one entered in Excel.

The HTML table in the Tables folder now contains the edits made in Excel.

When the table is open in Excel, it is stored as a temp file which, by default, is located in C:\Users\<username>\AppData\Local\Temp\Phoenix.

Rules for editing worksheets and HTML tables in Excel:

Only worksheets and HTML tables in the can be edited in Excel.
Worksheet format edits (fonts, shading, etc.) are not saved when saving back to Phoenix.
HTML table format edits are saved when saving back to Phoenix and the user chooses to apply the changes.
Only formatting available in standard Phoenix tables are supported. For non-standard tables, the HTML is passed to Excel as is.
Multiple worksheets and HTML tables can be edited at once.
If a text value is added to a Numeric-type column, then the column is changed to a Text-type when the changes are applied in Phoenix.
The edited worksheet and table will match any changes that Excel automatically makes to those files.
Formulas cannot be edited in Phoenix. If a worksheet contains formulas and the formulas are saved when the worksheet is imported back into Phoenix, then the worksheet can no longer be edited in Phoenix.
Worksheets that cannot be edited in Phoenix are shaded gray.
Detailed edits made in Excel are not recorded in the worksheet’s or table’s history tab. The History tab only records a single edit entry named “Object Edited in Excel.”