Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Microsoft Office to OpenOffice Migration Guide
Previous Page Home Next Page

Import, export and sharing issues in spreadsheets

Form fields

Combo boxes, list boxes and checkboxes with links to spreadsheet cells all import and export properly, although due to font handling the number of rows displayed in list boxes may be slightly different. List boxes in Calc can either have multi-selection or not, whereas Excel has two multi-selection modes: multi and extend. In Excel a link from a multi-selection listbox to a cell is ignored (at least in Excel 2000; the author has not checked Excel XP). In Calc the link works, but if more than one item is selected, the cell’s value is set to #N/A.

Option buttons import into Calc, but the link with cells works differently. In Calc each option button has its own link to a cell, setting its value to true or false depending on whether the option is selected. In Excel the cell link returns the number of the selected option button. On exporting option buttons to Excel, the option buttons lose their radio operation; in other words, they are not grouped so that selecting one “unselects” the others.

Scroll bars and spinners are now implemented in Calc, but are not imported yet.

Array constants

Array constants are not available in OpenOffice.org. The workaround is to have the constant values in cells on a sheet and refer to them. Array constants used as parameters to a function in Excel are omitted when imported into Calc.

Optional parameters in formulas

Some formulas have more than one optional parameter at the end of the parameter list. In Excel it is possible to leave an optional parameter blank and specify a later parameter, but this is not possible in Calc. Formulas with optional parameters are not correctly imported into Calc.

Functions

Statistical, engineering and financial functions

In Excel some of the financial functions accept string parameters for dates; in OpenOffice.org they require a serial number, and thus they are not correctly imported into Calc.

Analysis ToolPak Functions

The function EFFECT provided by the Analysis ToolPak for Excel is converted to EFFECT_ADD in Calc and returns the same results.

Other functions not implemented / imported

Only three other functions are not imported: INFO, GETPIVOTDATA, and HYPERLINK. Hyperlinks are supported but not as a function; use the Hyperlink button on the Function bar.

DataPilot—Pivot Table

DataPilots are Calc’s equivalent to Excel’s Pivot Tables. However there are a number of limitations. Pivot tables import from Excel into Calc (although the cells do not have the PivotTable shading) but as soon as they are worked on these limitations become apparent. The limitations are:

  • There is no PivotChart facility, but a DataPilot can be used as the data source for a chart.

  • The user interface allows a maximum of 8 fields in the data, row or column areas. (An alternative interface which allows more is available from: https://homepages.paradise.net.nz/hillview/OOo/ in a document called MyDataPilot.sxc.

  • There is some ability to group data (for example, a date field grouping by week or quarter but not by month). The workaround is to create a new column with a formula for calculating the grouping, e.g. =Month().

  • You can not define different formats for different fields.

  • Calc can have formulas based on things like “Difference From” or “% of”, but can not have own custom fields (work around create new columns to calculate the desired values).

  • The Application Program Interface specification is incomplete for writing macros that work with DataPilots—for example, the ability to control through another program, such as OpenOffice.org’s own macro language, BASIC, whether the tables have grand totals, or having access to DataPilots created from external data.

AutoFilter

AutoFilter imports and exports correctly with the required rows being hidden. However, there are a number of differences between Excel and Calc:

  • There is only one AutoFilter active for a spreadsheet document at a time. The rows remain hidden but the drop-downs in the first row disappear on applying AutoFilter elsewhere.

  • The remaining visible row numbers do not change color to warn that there is a filter in place.

  • Error values do not show as an option in the drop down lists when cells in the column have errors.

  • In the drop down lists, the equivalent to Excel’s “Custom” is called “Standard”.

Charts

Chart types that Excel provides but Calc does not:

  • Bar of pie converts to a 2D – columns – normal.

  • Pie of pie converts to a 2D – columns – normal.

  • Radar filled converts to 3D – columns deep.

  • Radar with markers – converts to 2D – net – normal but counterclockwise rather than Excel’s clockwise.

Other charting issues include:

  • There are fewer axis options, such as no option for having an inverted y axis (negative at top towards positive at bottom).

  • The data must be contiguous, with the labels in the first row or column.

Number formats

There is no “accounting” format (with the currency symbol at the left edge of the cell). A workaround would be to have another column to the left that contains the currency symbol.

There is no predefined option for bracketing negative numbers. Use a custom format such as “$#,##0.00_);[RED]($#,##0.00)”.

Grid lines

In Excel grid lines are a property of a worksheet; in Calc it is an application-wide option:

Tools > Options > OpenOffice.org Calc > View

Microsoft Office to OpenOffice Migration Guide
Previous Page Home Next Page


 
 
  Published under the terms of the GNU General Public License Design by Interspire