Editing_a_worksheet_with_Microsoft

Editing a worksheet with Microsoft Excel

If a worksheet has no dependents (i.e., it is not mapped or connected to an operational object) and is in the Data folder, then it can be edited in Excel.

Right-click the output worksheet and select Copy to Data Folder.

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

Select Edit > Edit in Excel.

Right-click the worksheet and select Edit in Excel.

In the information dialog, press OK to open the worksheet in Excel.

Edit the worksheet using the Excel tools.

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.

Note: Because of the way Phoenix handles its interactions with Excel, the Save option in Excel must be used. If the Save As option is used to change the worksheet name or location, the Excel edits cannot be imported into Phoenix.

In the next dialog, indicate whether Excel formulas are to be saved in the worksheet.

If Yes is chosen, the worksheet is no longer editable in Phoenix but can be edited in Excel and used with operational objects.

If No is chosen, only the formula results are saved. The worksheet can be edited in Phoenix but, if edited in Excel, the formulas will not be available.

In the dialog, press Yes to apply the changes.

An entry is written in the worksheet’s History tab noting that it was edited in Excel.

In addition to editing the data in a worksheet, the units associated with each column can also be edited. If a worksheet has a units row as part of the column header, edit the units in that row. If a worksheet does not have a units row, entering units adds them as another row of data in the worksheet, not as part of the column header.

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, selecting Refresh from Source from the edited worksheet’s right-click menu may display an empty worksheet. To retrieve the data, 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.

Points to consider when editing worksheets in Excel

Format edits (fonts, shading, etc.) are not saved when saving back to Phoenix.

Multiple worksheets can be edited at the same time.

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 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 History tab. The History tab only records a single edit entry named “Object Edited in Excel.”