Messages and limitations of the Excel connector

Corresponding error

• None

Corresponding error

• jxl.read.biff.PasswordException

Corresponding error

• jxl.read.biff.BiffException

Corresponding error

• ZipEntryBrokenException

Corresponding error

• ZipEntryNotFoundException

Corresponding error

• InvalidStartCellAddressException

Corresponding error

• NoDefinitionAreaException

Corresponding error

• IllegalFormatException

Corresponding error

• jxl.write.biff.RowsExceededException

Corresponding error

• InvalidCellAddressException

Corresponding error

• InputDataReadException

Corresponding error

• SheetNotFoundException

Corresponding error

• IndentExceedsMaxException

Specification limits

• Reading values of cells with formulas

  • The result value of a formula in a cell whose format is "Number" will be read up to the number of decimal places set in the format in range within the limit of 15 significant digits as Excel specifications.

    • Digits that exceed the significant digits will be rounded off.

    • Digits under the specified number of decimal places will be rounded off.

    Example:

    Result value of formula (Number of decimal places)	Read value	Read method
    1234567890123456.123 (2 digits)	1234567890123460	Round off the digits that exceed the number of significant digits
    1234567890.1234567 (10 digits)	1234567890.12346	Round off the digits that exceed the number of significant digits
    12345.123 (2 digits)	12345.12	Round off the digits that exceed the specified number of decimal places

  • The result value of a formula in a cell whose format is "General" will be read up to 9 decimal places in range within the limit of 15 significant digits as Excel specifications.

    • Digits that exceed the significant digits will be rounded off.

    • A value whose integer part is not 0 will be rounded off to 9 decimal places.

    • For a value whose integer part is 0, the 9 digits from the first non-zero digit will be counted as significant digits, and the 10th digit will be rounded off.

    Example:

    Result value of formula	Read value	Read method
    1234567890123456.123	1234567890123460	Round off the digits that exceed the number of significant digits
    1234567890.1234567	1234567890.12346	Round off the digits that exceed the number of significant digits
    1.12345678944	1.123456789	Round off the decimal places to 9 digits.
    0.0001234567894444	0.000123456789	The 9 digits from the first non-zero digit will be counted as significant digits, and the value will be rounded off to 9 decimal places

• Date

  • The oldest date that can be handled as date is 1900/01/01.

  • The latest date that can be handled as date is 9999/12/31.

  • Date type data will be read according to the cell format as follows:

    • If date is alone set as format, only date will be read. The data will be output in yyyy-MM-dd, the date format of ISO8601 without timezone.

    • If time is set in addition to date as format, date and time will be read. The data will be output in date-time (yyyy-MM-dd HH:mm:ss) format, similar to ISO8601 without timezone.

• The following settings aren't supported for reading:

  • Settings of rows and columns/phonetic guides/comments/newlines/graphs/clip arts/shapes/pictures/macros/attachments/hyperlinks

  • Cell formats other than General/Number/Date/Text (Custom format, etc.)

  • When multiple rows of a column have different formats, the data will be read only as General or Text type.

    To read the first and second rows in the following example, set column A as General or Text type.

    Example:

    	A	B
    1	2005/4/1 (Date)	Yamada (Text)
    2	1800 (Number)	Suzuki (Text)

• File encrypted with a password

  • You can't read data from an Excel file that is encrypted with a password.

• Read cell formula

  • You can't read formulas from an Excel workbook.

• End condition/Column name

  • When Specify reading end condition is selected, End condition/Column name is set, and Column list/Column name is changed after that, as the column name differs, the end condition won't be used as condition. When changing Column list/Column name, click "Finish" button and close the properties window. Then open the properties windows again and configure Column list/Column name.

  • When multiple columns with the same name are set in Column list/Column name and that name is set in End condition/Column name, the column at the topmost position in columns of Column list/Column name with the same name will be used.

• Cell with formula/comment

  • A cell with formula/comment will be considered to be in the reading range and read even if the cell is empty, with no value.

    To exclude such cell from the reading range, set End condition or clear the formula or comment in Excel.

• Cell with validation

  • In an Excel 2003 workbook, if validation is set for a cell and "Input message" or "Error message" of the validation is set as null, ArrayIndexOutOfBoundsException may be thrown.

    In that case, you can prevent it by opening the "Validation" dialog for the cell in workbook, clicking "OK" button, and then saving it.

  • In an Excel 2003 workbook, when you read a sheet where "Custom" is selected for validation of a cell and a function is used in formula of the cell, an error may occur.

• Handling large Excel files

  • When a large Excel file is specified in File, OutOfMemoryError may occur, or it might take some time to analyze the file contents and operations in the screen may be delayed.

    In that case, you can prevent this issue by specifying a variable such as script variable in File.

Specification limits

• Reading values of cells with formulas

  • The result value of a formula in a cell whose format is "Number" will be read up to the number of decimal places set in the format in range within the limit of 15 significant digits as Excel specifications.

    • Digits that exceed the significant digits will be rounded off.

    • Digits under the specified number of decimal places will be rounded off.

    Example:

    Result value of formula (Number of decimal places)	Read value	Read method
    1234567890123456.123 (2 digits)	1234567890123460	Round off the digits that exceed the number of significant digits
    1234567890.1234567 (10 digits)	1234567890.12346	Round off the digits that exceed the number of significant digits
    12345.123 (2 digits)	12345.12	Round off the digits that exceed the specified number of decimal places

  • The result value of a formula in a cell whose format is "General" will be read up to 9 decimal places in range within the limit of 15 significant digits as Excel specifications.

    • Digits that exceed the significant digits will be rounded off.

    • A value whose integer part is not 0 will be rounded off to 9 decimal places.

    • For a value whose integer part is 0, the 9 digits from the first non-zero digit will be counted as significant digits, and the 10th digit will be rounded off.

    Example:

    Result value of formula	Read value	Read method
    1234567890123456.123	1234567890123460	Round off the digits that exceed the number of significant digits
    1234567890.1234567	1234567890.12346	Round off the digits that exceed the number of significant digits
    1.12345678944	1.123456789	Round off the decimal places to 9 digits.
    0.0001234567894444	0.000123456789	The 9 digits from the first non-zero digit will be counted as significant digits, and the value will be rounded off to 9 decimal places

• Date

  • The oldest date that can be handled as date is 1900/01/01.

  • The latest date that can be handled as date is 9999/12/31.

  • Date type data will be read according to the cell format as follows:

    • If date is alone set as format, only date will be read. The data will be output in yyyy-MM-dd, the date format of ISO8601 without timezone.

    • If time is set in addition to date as format, date and time will be read. The data will be output in date-time (yyyy-MM-dd HH:mm:ss) format, similar to ISO8601 without timezone.

• The following settings aren't supported for reading:

  • Settings of rows and columns/phonetic guides/comments/newlines/graphs/clip arts/shapes/pictures/macros/attachments/hyperlinks

  • Cell formats other than General/Number/Date/Text (Custom format, etc.)

• Named range set with multiple ranges

  • When multiple ranges are set for one named range, only the range that initially appeared will be read.

• Print area (Print_Area)

  • When print area (Print_Area) is defined in multiple sheets, you will get the print range of the last sheet in the workbook.

• File encrypted with a password

  • You can't read data from an Excel file that is encrypted with a password.

• Read cell formula

  • You can't read formulas from an Excel workbook.

• Handling large Excel files

  • When a large Excel file is specified in File, OutOfMemoryError may occur, or it might take some time to analyze the file contents and operations in the screen may be delayed.

    In that case, you can prevent this issue by specifying a variable such as script variable in File.

Specification limits

• Reading values of cells with formulas

  • The result value of a formula in a cell whose format is "Number" will be read up to the number of decimal places set in the format in range within the limit of 15 significant digits as Excel specifications.

    • Digits that exceed the significant digits will be rounded off.

    • Digits under the specified number of decimal places will be rounded off.

    Example:

    Result value of formula (Number of decimal places)	Read value	Read method
    1234567890123456.123 (2 digits)	1234567890123460	Round off the digits that exceed the number of significant digits
    1234567890.1234567 (10 digits)	1234567890.12346	Round off the digits that exceed the number of significant digits
    12345.123 (2 digits)	12345.12	Round off the digits that exceed the specified number of decimal places

  • The result value of a formula in a cell whose format is "General" will be read up to 9 decimal places in range within the limit of 15 significant digits as Excel specifications.

    • Digits that exceed the significant digits will be rounded off.

    • A value whose integer part is not 0 will be rounded off to 9 decimal places.

    • For a value whose integer part is 0, the 9 digits from the first non-zero digit will be counted as significant digits, and the 10th digit will be rounded off.

    Example:

    Result value of formula	Read value	Read method
    1234567890123456.123	1234567890123460	Round off the digits that exceed the number of significant digits
    1234567890.1234567	1234567890.12346	Round off the digits that exceed the number of significant digits
    1.12345678944	1.123456789	Round off the decimal places to 9 digits.
    0.0001234567894444	0.000123456789	The 9 digits from the first non-zero digit will be counted as significant digits, and the value will be rounded off to 9 decimal places

• Date

  • The oldest date that can be handled as date is 1900/01/01.

  • The latest date that can be handled as date is 9999/12/31.

  • Date type data will be read according to the cell format as follows:

    • If date is alone set as format, only date will be read. The data will be output in yyyy-MM-dd, the date format of ISO8601 without timezone.

    • If time is set in addition to date as format, date and time will be read. The data will be output in date-time (yyyy-MM-dd HH:mm:ss) format, similar to ISO8601 without timezone.

• The following settings aren't supported for reading:

  • Formulas/settings of rows and columns/phonetic guides/comments/newlines/graphs/clip arts/shapes/pictures/macros/attachments/hyperlinks

  • Cell formats other than General/Number/Date/Text (Custom format, etc.)

• File encrypted with a password

  • You can't read data from an Excel file that is encrypted with a password.

• Cell with validation

  • In an Excel 2003 workbook, if validation is set for a cell and "Input message" or "Error message" of the validation is set as null, ArrayIndexOutOfBoundsException may be thrown.

    In that case, you can prevent it by opening the "Validation" dialog for the cell in workbook, clicking "OK" button, and then saving it.

  • In an Excel 2003 workbook, when you read a sheet where "Custom" is selected for validation of a cell and a function is used in formula of the cell, an error may occur.

• Handling large Excel files

  • When a large Excel file is specified in File, OutOfMemoryError may occur, or it might take some time to analyze the file contents and operations in the screen may be delayed.

    In that case, you can prevent this issue by specifying a variable such as script variable in File.

Specification limits

• File encrypted with a password

  • You can't read data from an Excel file that is encrypted with a password.

Specification limits

• When null is input in column element of input schema, the operation is as follows:

  • When Specify format/comment in Mapper to write isn't selected

    • If there's a value in the destination cell, it won't be written.

    • If there's no value in the destination cell, an empty string with "General" format will be written to the cell.

  • When Specify format/comment in Mapper to write is selected

    • As the input schema is XML type, an empty string will be written to the cell.

• When writing string type input data to a cell with "Date" format, use data formatted with the following date format:

  Format	Example
  yyyy-MM-dd'T'HH:mm:ss.SSSZZ	2007-05-10T15:32:12.708+0900
  yyyy-MM-dd'T'HH:mm:ss.SSS	2007-05-10T15:32:12.708
  yyyy-MM-dd'T'HH:mm:ssZZ	2007-05-10T15:32:12+0900
  yyyy-MM-dd'T'HH:mm:ss	2007-05-10T15:32:12
  yyyy-MM-dd'T'HH:mm	2007-05-10T15:32
  yyyy-MM	2007-05
  yyyy/M/d K:m a	2007/5/10 3:32 PM
  yyyy/M/d H:m:s	2007/05/10 15:32:12
  yyyy/M/d H:m	2007/05/10 15:32
  yyyy/M/d	2007/05/10

• When Use format of target cell is selected in Column list/Style and the date format of destination cell is one of the following, the format won't be "Date" even if the date type data is written.

  Type of date format	Example	Remarks
  [$-411]ge.m.d;@	h13.3.14
  [$-409]yyyy/m/d h:mm AM/PM;@	13/3/14 1:30 PM	When the destination cell contains any data, it can be written as date.

  Note

  Custom formats aren't tested.

• Input data formatted in hours:minutes:seconds will be written as not "Date" but "General" format.

• After writing, cell format may be indeterminate.

• You can't write data to an Excel file that is encrypted with a password.

• When a large Excel file is specified in File, OutOfMemoryError may occur, or it might take some time to analyze the file contents and operations in the screen may be delayed.

  In that case, you can prevent this issue by specifying a variable such as script variable in File.

• Writing formula isn't supported.

• Writing to an Excel file with digital signature added isn't supported.

• When writing to an Excel file with sheet protection set, data will be written, and if a password is set, the password will be maintained.

• When specifying Background color in "Generate Excel Format" logic, items other than background color will be set as default value.

• In an Excel 2003 workbook, when writing a number to a cell of "General" format, that number will be written as string and an error check will be displayed.

  Even in a cell of "Number" format, if the writing target cell contains no value, an error check may be displayed.

  In these cases, you can write it as number by putting a number in the destination cell in advance.

• In an Excel 2003 workbook, when writing to a cell which contains no value, a value may be written with "Arial" font and "10" font size as the cell's font information can't be acquired.

  In that case, you can write it using the font information of the destination cell by putting a value in the cell in advance.

• In an Excel 2003 workbook, when all of the following four conditions are satisfied, even if a format is specified with "Generate Excel Format" logic, it won't be reflected in the destination cell.

  • Any of the following conditions is satisfied:

    • Use format of target cell or Use format of write starting row is selected in Column list/Style

    • Insert is selected

  • Comment is inserted to the destination cell

  • No value is contained in the destination cell

  • No format is set to the destination cell

• When writing to an Excel 2003 workbook, if a password to modify is set to the workbook, writing will be performed and the password will be deleted.

• When writing to an Excel 2003 workbook, the following formats of all cells in the file specified in File will be changed to the format of whole cell.

  • Font name

  • Font style

  • Font size

  • Underline

  • Font color

  • Effects

• When writing to an Excel 2003 workbook, the following settings of the file specified in File disappear.

  • Text box

  • AutoShape

  • PivotTable

  • Label

  • Group box

  • Check box

  • Option button

  • List box

  • Scroll bar

  • Spin button

  • WordArt

  • AutoFilter

  • Diagonal border specified in cell format

  • Tab color

  • Allow users to edit ranges

• When writing to an Excel 2003 workbook, when "Center across selection" is set to a cell in the file specified in File, the format will be changed to "General".

• When writing to an Excel 2003 workbook, data can't be inserted beyond the maximum number of rows (65536 rows).

• When writing to an Excel workbook, if a password to modify is set to the workbook, writing will be performed and the password will be retained.

• When writing to an Excel workbook, if a string is written to a cell, the format of that cell will be "Text".

• When writing to an Excel workbook, you need to specify columns in Column list to execute with Insert column names in the first row being selected.

• When writing to an Excel workbook and if a number starting with "+" (plus symbol), it will be written as string and the workbook will be corrupted.

Specification limits

• When null is input in column element of input schema, the operation is as follows:

  • When Specify format/comment in Mapper to write isn't selected

    • It won't be written.

  • When Specify format/comment in Mapper to write is selected

    • As the input schema is XML type, an empty string will be written to the cell.

• When input data has multiple rows, the data in the first row will be written.

• When writing string type input data to a cell with "Date" format, use data formatted with the following date format:

  Format	Example
  yyyy-MM-dd'T'HH:mm:ss.SSSZZ	2007-05-10T15:32:12.708+0900
  yyyy-MM-dd'T'HH:mm:ss.SSS	2007-05-10T15:32:12.708
  yyyy-MM-dd'T'HH:mm:ssZZ	2007-05-10T15:32:12+0900
  yyyy-MM-dd'T'HH:mm:ss	2007-05-10T15:32:12
  yyyy-MM-dd'T'HH:mm	2007-05-10T15:32
  yyyy-MM	2007-05
  yyyy/M/d K:m a	2007/5/10 3:32 PM
  yyyy/M/d H:m:s	2007/05/10 15:32:12
  yyyy/M/d H:m	2007/05/10 15:32
  yyyy/M/d	2007/05/10

• When Use format of target cell is selected in Cell settings/Style and the date format of destination cell is one of the following, the format won't be "Date" even if the date type data is written.

  Type of date format	Example	Remarks
  [$-411]ge.m.d;@	h13.3.14
  [$-409]yyyy/m/d h:mm AM/PM;@	13/3/14 1:30 PM	When the destination cell contains any data, it can be written as date.

  Note

  Custom formats aren't tested.

• Input data formatted in hours:minutes:seconds will be written as not "Date" but "General" format.

• After writing, cell format may be indeterminate.

• You can't write data to an Excel file that is encrypted with a password.

• Writing to an Excel file with digital signature added isn't supported.

• When writing to an Excel file with sheet protection set, data will be written, and if a password is set, the password will be maintained.

• When specifying Background color in "Generate Excel Format" logic, items other than background color will be set as default value.

• In an Excel 2003 workbook, when writing a number to a cell of "General" format, that number will be written as string and an error check will be displayed.

  Even in a cell of "Number" format, if the writing target cell contains no value, an error check may be displayed.

• In an Excel 2003 workbook, when writing to a cell which contains no value, a value may be written with "Arial" font and "10" font size as the cell's font information can't be acquired.

  In that case, you can write it using the font information of the destination cell by putting a value in the cell in advance.

• In an Excel 2003 workbook, when all of the following four conditions are satisfied, even if a format is specified with "Generate Excel Format" logic, it won't be reflected in the destination cell.

  • Use format of target cell is selected in Cell settings/Style

  • Comment is inserted to the destination cell

  • No value is contained in the destination cell

  • No format is set to the destination cell

• When writing to an Excel 2003 workbook, if a password to modify is set to the workbook, writing will be performed and the password will be deleted.

• When writing to an Excel 2003 workbook, the following formats of all cells in the file specified in File will be changed to the format of whole cell.

  • Font name

  • Font style

  • Font size

  • Underline

  • Font color

  • Effects

• When writing to an Excel 2003 workbook, the following settings of the file specified in File disappear.

  • Text box

  • AutoShape

  • PivotTable

  • Label

  • Group box

  • Check box

  • Option button

  • List box

  • Scroll bar

  • Spin button

  • WordArt

  • AutoFilter

  • Diagonal border specified in cell format

  • Tab color

  • Allow users to edit ranges

• When writing to an Excel 2003 workbook, when "Center across selection" is set to a cell in the file specified in File, the format will be changed to "General".

• When writing to an Excel workbook, if a password to modify is set to the workbook, writing will be performed and the password will be retained.

• When writing to an Excel workbook and if a number starting with "+" (plus symbol), it will be written as string and the workbook will be corrupted.

Specification limits

• Only Excel workbook is supported.

• When Set password to modify and Protect contents of worksheet and locked cells aren't selected, the Excel file specified in File won't be processed.

• When Select locked cells is selected in Operation to allow all users of worksheet, Select unlocked cells also need to be selected.

• When password to modify is set to the Excel file specified in File, it will be overwritten with the values specified in User name and Password to modify.

• When the sheet specified in Sheet name is protected, it will be overwritten with the values specified in Password to unprotect sheet and Operation to allow all users of worksheet.

• The characters allowed in Password to modify and Password to unprotect sheet are the following:

  • Alphabets

  • Numbers

  • "!" "@" "#" "$" "%" "^" "&" "*" "(" ")" "+" "="

• You can't protect an Excel book encrypted with password.

Specification limits

• Only Excel workbook is supported.

• In the visible/hidden settings for rows, when multiple settings are performed for the same sheet, the settings will be sorted in the ascending order of Row list/Start row. When the value of Row list/Start row is same, it will be sorted in the ascending order of Row list/End row.

• In the visible/hidden settings for columns, when multiple settings are performed for the same sheet, the settings will be sorted in the ascending order of Column list/Start column. When the value of Column list/Start column is same, it will be sorted in the ascending order of Column list/End column.

• When a column for which the column width isn't set is shown or hidden, the column width will be different from the default value.

• You can't show/hide an Excel workbook encrypted with password.

Specification limits

• Only Excel workbook is supported.

• When there are multiple settings in Sheet list, the operation is carried out sequentially.

  • Example: If there are two sheets "Sheet 1" and "Sheet 2", you can rename in the following order.

    1. "Sheet2" -> "Sheet3"

    2. "Sheet1" -> "Sheet2"

    On the other hand, if you set it in the following order, "Sheet 2" will get overlapped with the first operation, resulting in an error.

    1. "Sheet1" -> "Sheet2"

    2. "Sheet2" -> "Sheet3"

Specification limits

• Only Excel workbook is supported.

• Copying Macros isn't supported. However, you can copy an AutoShape to which Macros are assigned, as it is. Therefore, if a Macro with same name exists in copy destination, there is a possibility that the Macro can be executed from AutoShape.

• Copying PivotTable isn't supported. Excel workbook may be corrupted.

• Copying external references (links) isn't supported. Excel workbook may be corrupted.

• When there are multiple settings in Sheet list, the operation is carried out sequentially.

• All the strings in the Excel workbook are stored in the memory. Therefore, you need to increase the memory space when accessing the Excel workbook containing large data.

Specification limits

• Only Excel workbook is supported.

• When there are multiple settings in Sheet list, the operation is carried out sequentially.

• You can't delete all the sheets. You need to retain one or more visible sheets.

Specification limits

• Microsoft Office Excel of the version contained in supported version need to be installed to the OS on which HULFT Square Designer operates.

• If multiple versions of Excel applications are installed, Wizard starts the last installed version of Excel application.

• The Excel file started with Excel Read Wizard is temporarily copied from the HULFT Square Storage.

  Therefore, even when the contents of the Excel file started with Excel Wizard is modified, the Excel file on HULFT Square Storage isn't updated.

• If the Excel application starting from Excel Wizard opened other Excel files, the files are closed when the Wizard ends.

• When empty cell is selected in the "Select range of reading" dialog, the format of the cell may not be retrieved and the Type of Column list may be set different from the type of the target cell. In that case, please set Type manually.

Notes

• "Excel Read Wizard" is an Excel add-in using VBA. If you fail to start the wizard, Excel VBA may not be installed.

  = Remarks =

  For details about how to install Excel VBA add-ins, refer to the Excel documents.

• The "Excel Read Wizard" add-in refers to Microsoft XML, v6.0 (MSXML 6). If you see the Microsoft Visual Basic Compile error, MSXML 6 may not be installed.

  = Remarks =

  For details about how to install MSXML 6, refer to the Microsoft downloading site.

• After Cancel button is pressed in "Excel Read Wizard" setting screen, the screen can be shown again by the following operation.

  • For 2021/2019/2016/2013:

    Press "Start Excel Read Wizard" from "Excel Wizard" of "Add-ins" tab.

    

Specification limits

• Microsoft Office Excel of the version contained in supported version need to be installed to the OS on which HULFT Square Designer operates.

• If multiple versions of Excel applications are installed, Wizard starts the last installed version of Excel application.

• The Excel file started with Excel Write Wizard is temporarily copied from the HULFT Square Storage.

  Therefore, even when the contents of the Excel file started with Excel Wizard is modified, the Excel file on HULFT Square Storage isn't updated.

• If the Excel application starting from Excel Wizard opened other Excel files, the files are closed when the Wizard ends.

• When empty cell is selected in the "Select range of writing" dialog, the format of the cell may not be retrieved and the Type of Column list may be set different from the type of the target cell. In that case, please set Type manually.

Notes

• "Excel Write Wizard" is an Excel add-in using VBA. If you fail to start the wizard, Excel VBA may not be installed.

  = Remarks =

  For details about how to install Excel VBA add-ins, refer to the Excel documents.

• The "Excel Write Wizard" add-in refers to Microsoft XML, v6.0 (MSXML 6). If you see the Microsoft Visual Basic Compile error, MSXML 6 may not be installed.

  = Remarks =

  For details about how to install MSXML 6, refer to the Microsoft downloading site.

• After pressing Cancel button of the "Excel Write Wizard" setting screen, the setting screen can be shown again with the following operations.

  • For 2021/2019/2016/2013:

    Press "Start Excel Write Wizard" from "Excel Wizard" of "Add-ins" tab.