Export has special configuration options that allow greater control over the conversion of spreadsheet files.
Normally, Export does not convert hidden text from a Microsoft Excel spreadsheet because it is assumed that the text should not be exposed. You can change this default behavior and convert text in hidden rows, columns, and sheets by adding the following lines to the formats_e.ini
file:
[Options] gethiddeninfo=1
Normally, Export does not convert headers and footers from Microsoft Excel 2003 spreadsheets. You can change this default behavior and convert headers and footers by adding the following lines to the formats_e.ini
file:
[Options] ShowHeaderFooter=1
System date and time format information is not stored in Microsoft Excel files. On Windows systems, you can specify a locale setting to determine the date and time format. However, on UNIX systems, the date and time format is set to the U.S. short date format by default (mm/dd/yyyy). To change the format, you must use a formats_e.ini
option.
To specify the system date and time format on UNIX systems
In the formats.ini
file, specify the following options:
SysDateTime SysLongDate SysShortDate SysTime
These values cannot contain spaces.
For example, if you specify SysDateTime=%d/%m/%Y
, dates and times are extracted in the following format:
28/02/2008
The format arguments are the same as those for the strftime()
function. Refer to the following web page for more information.
You can now export numbers in Microsoft Excel files and write them to the output without formatting. By default, numbers are exported in the format specified by the Excel file (for example, General, Currency, and Date). Spreadsheets might contain cells that have very large numbers in them. Excel displays the numbers in a scientific notation that rounds or truncates the numbers.
To export numbers without formatting, add the following lines to the formats_e.ini
file:
[Options] ignoredefnumformats=1
Normally, the actual value of a formula is extracted from an Excel spreadsheet; the formula from which the value is derived is not included in the output. However, KeyView enables you to include the value as well as the formula in the output. For example, if Export is configured to extract the formula and the formula value, the output might look like this:
245 = SUM(B21:B26)
The calculated value from the cell is 245
and the formula from which the value is derived is SUM(B21:B26)
.
To set the extraction option for formulas, add the following lines to the formats_e.ini
file:
[Options]
getformulastring=option
where option
is one of the following.
Option |
Description |
---|---|
|
Extract the formula value only. This is the default. Set this option if formula extraction is enabled, and you want to return to the default. |
|
Extract the formula only. |
|
Extract the formula and the formula value. |
When formula extraction is enabled, Export can extract Microsoft Excel formulas that contain the functions listed in the following table:
Supported Microsoft Excel Functions
=ABS()
|
=ACOS()
|
=AND()
|
=AREAS()
|
=ASIN()
|
=ATAN2()
|
=ATAN2()
|
=AVERAGE()
|
=CELL()
|
=CHAR()
|
=CHOOSE()
|
=CLEAN()
|
=CODE()
|
=COLUMN()
|
=COLUMNS()
|
=CONCATENATE()
|
=COS()
|
=COUNT()
|
=COUNTA()
|
=DATE()
|
=DATEVALUE()
|
=DAVERAGE()
|
=DAY()
|
=DCOUNT()
|
=DDB()
|
=DMAX()
|
=DMIN()
|
=DOLLAR()
|
=DSTDEV()
|
=DSUM()
|
=DVAR()
|
=EXACT()
|
=EXP()
|
=FACT()
|
=FALSE()
|
=FIND()
|
=FIXED()
|
=FV()
|
=GROWTH()
|
=HLOOKUP()
|
=HOUR()
|
=ISBLANK()
|
=IF()
|
=INDEX()
|
=INDIRECT()
|
=INT()
|
=IPMT()
|
=IRR()
|
=ISERR()
|
=ISERROR()
|
=ISNA()
|
=ISNUMBER()
|
=ISREF()
|
=ISTEXT()
|
=LEFT()
|
=LEN()
|
=LINEST()
|
=LN()
|
=LOG()
|
=LOG10()
|
=LOGEST()
|
=LOOKUP()
|
=LOWER()
|
=MATCH()
|
=MAX()
|
=MDETERM()
|
=MID()
|
=MIN()
|
=MINUTE()
|
=MINVERSE()
|
=MIRR()
|
=MMULT()
|
=MOD()
|
=MONTH()
|
=N()
|
=NA()
|
=NOT()
|
=NOW()
|
=NPER()
|
=NPV()
|
=OFFSET()
|
=OR()
|
=PI()
|
=PMT()
|
=PPMT()
|
=PRODUCT()
|
=PROPER()
|
=PV()
|
=RATE()
|
=REPLACE()
|
=REPT()
|
=RIGHT()
|
=ROUND()
|
=ROUND()
|
=ROW()
|
=ROWS()
|
=SEARCH()
|
=SECOND()
|
=SIGN()
|
=SIN()
|
=SLN()
|
=SQRT()
|
=STDEV()
|
=SUBSTITUTE()
|
=SUM()
|
=SYD()
|
=T()
|
=TAN()
|
=TEXT()
|
=TIME()
|
=TIMEVALUE()
|
=TODAY()
|
=TRANSPOSE()
|
=TREND()
|
=TRIM()
|
=TRUE()
|
=TYPE()
|
=UPPER()
|
=VALUE()
|
=VAR()
|
=VLOOKUP()
|
=WEEKDAY()
|
=YEAR()
|
|