Package ch.rabanti.nanoxlsx4j

Class Helper

java.lang.Object
ch.rabanti.nanoxlsx4j.Helper
public class Helper extends Object
Class for shared used (static) methods
Author:
Raphael Stoeckli

  • Field Details

    • MIN_OADATE_VALUE

      public static final double MIN_OADATE_VALUE
      Minimum valid OAdate value (1900-01-01). However, Excel displays this value as 1900-01-00 (day zero)
      See Also:

    • MAX_OADATE_VALUE

      public static final double MAX_OADATE_VALUE
      Maximum valid OAdate value (9999-12-31)
      See Also:

    • FIRST_ALLOWED_EXCEL_DATE

      public static final Date FIRST_ALLOWED_EXCEL_DATE
      First date that can be displayed by Excel. Values before this date cannot be processed.

    • LAST_ALLOWED_EXCEL_DATE

      public static final Date LAST_ALLOWED_EXCEL_DATE
      Last date that can be displayed by Excel. Values after this date cannot be processed.

    • FIRST_VALID_EXCEL_DATE

      public static final Date FIRST_VALID_EXCEL_DATE
      All dates before this date are shifted in Excel by -1.0, since Excel assumes wrongly that the year 1900 is a leap year.
      See also: https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year

  • Method Details

    • getOADateString

      public static String getOADateString(Date date)
      Method to calculate the OA date (OLE automation) of the passed date.
      OA Date format starts at January 1st 1900 (actually 00.01.1900). Dates beyond this date cannot be handled by Excel under normal circumstances and will throw a FormatException.
      Excel assumes wrongly that the year 1900 is a leap year. There is a gap of 1.0 between 1900-02-28 and 1900-03-01. This method corrects all dates from the first valid date (1900-01-01) to 1900-03-01. However, Excel displays the minimum valid date as 1900-01-00, although 0 is not a valid description for a day of month.
      In conformance to the OAdate specifications, the maximum valid date is 9999-12-31 23:59:59 (plus 999 milliseconds).
      See also:
      https://docs.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=netcore-3.1
      See also:
      https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year
      Parameters:
      date - Date to convert
      Returns:
      OA date or date and time as number string
      Throws:
      FormatException - Throws a FormatException if the passed date cannot be translated to the OADate format

    • getOADate

      public static double getOADate(Date date)
      Method to calculate the OA date (OLE automation) of the passed date.
      OA Date format starts at January 1st 1900 (actually 00.01.1900). Dates beyond this date cannot be handled by Excel under normal circumstances and will throw a FormatException.
      Excel assumes wrongly that the year 1900 is a leap year. There is a gap of 1.0 between 1900-02-28 and 1900-03-01. This method corrects all dates from the first valid date (1900-01-01) to 1900-03-01. However, Excel displays the minimum valid date as 1900-01-00, although 0 is not a valid description for a day of month.
      In conformance to the OAdate specifications, the maximum valid date is 9999-12-31 23:59:59 (plus 999 milliseconds).
      See also:
      https://docs.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=netcore-3.1
      See also:
      https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year
      Parameters:
      date - Date to convert
      Returns:
      OA date or date and time as number
      Throws:
      FormatException - Throws a FormatException if the passed date cannot be translated to the OADate format

    • getOADate

      public static double getOADate(Date date, boolean skipCheck)
      Method to calculate the OA date (OLE automation) of the passed date.
      OA Date format starts at January 1st 1900 (actually 00.01.1900). Dates beyond this date cannot be handled by Excel under normal circumstances and will throw a FormatException.
      Excel assumes wrongly that the year 1900 is a leap year. There is a gap of 1.0 between 1900-02-28 and 1900-03-01. This method corrects all dates from the first valid date (1900-01-01) to 1900-03-01. However, Excel displays the minimum valid date as 1900-01-00, although 0 is not a valid description for a day of month.
      In conformance to the OAdate specifications, the maximum valid date is 9999-12-31 23:59:59 (plus 999 milliseconds).
      See also:
      https://docs.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=netcore-3.1
      See also:
      https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year
      Parameters:
      date - Date to convert
      skipCheck - If true, the validity check is skipped
      Returns:
      OA date or date and time as number
      Throws:
      FormatException - Throws a FormatException if the passed date cannot be translated to the OADate format

    • getOATimeString

      public static String getOATimeString(Duration time)
      Method to convert a duration into the internal Excel time format (OAdate without days).
      The time is represented by a OAdate without the date component but a possible number of total days
      Parameters:
      time - Time to process
      Returns:
      Time as number string
      Throws:
      FormatException - Throws a FormatException if the passed time is invalid

    • getOATime

      public static double getOATime(Duration time)
      Method to convert a duration into the internal Excel time format (OAdate without days).
      The time is represented by a OAdate without the date component but a possible number of total days
      Parameters:
      time - Time to process
      Returns:
      Time as number
      Throws:
      FormatException - Throws a FormatException if the passed time is invalid

    • getDateFromOA

      public static Date getDateFromOA(double oaDate)
      Method to calculate a common Date from the OA date (OLE automation) format
      OA Date format starts at January 1st 1900 (actually 00.01.1900). Dates beyond this date cannot be handled by Excel under normal circumstances and will throw a FormatException
      Implementation Note:
      Numbers that represents dates before 1900-03-01 (number of days since 1900-01-01 = 60) are automatically modified. Until 1900-03-01 is 1.0 added to the number to get the same date, as displayed in Excel. The reason for this is a bug in Excel. See also:
      https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year
      Parameters:
      oaDate - OA date number
      Returns:
      Converted date

    • parseTime

      public static Duration parseTime(String timeString, String pattern, Locale locale)
      Method to parse a Duration object from a string and a pattern that is allied to a DateTimeFormatter instance
      API Note:
      Supported formatting tokens are all time-related patterns like 'HH', 'mm', 'ss'. To represent the number of days, the pattern 'n' is used. This deviates from the actual definition of 'n' which would be nanoseconds of the second. date-related patterns like 'dd', 'MM' or 'yyyy' are not supported yet for time parsing
      Parameters:
      timeString - String to parse
      pattern - Pattern as string
      locale - Locale of the formatter that is created from the pattern
      Returns:
      Duration object
      Throws:
      FormatException - thrown if the time could not be parsed or of the pattern was invalid

    • parseTime

      public static Duration parseTime(String timeString, DateTimeFormatter formatter)
      Method to parse a Duration object from a string and a DateTimeFormatter instance
      API Note:
      Note that the formatter may consider locales. Parsing may fail e.g. with "AM/PM" when not on Locale.US. Supported formatting tokens are all time-related patterns like 'HH', 'mm', 'ss'. To represent the number of days, the pattern 'n' is used. This deviates from the actual definition of 'n' which would be nanoseconds of the second. date-related patterns like 'dd', 'MM' or 'yyyy' are not supported yet for time parsing
      Parameters:
      timeString - String to parse
      formatter - Formatter to apply the parsing
      Returns:
      Duration object
      Throws:
      FormatException - thrown if the time could not be parsed or of the formatter was invalid

    • getInternalColumnWidth

      public static float getInternalColumnWidth(float columnWidth)
      Calculates the internal width of a column in characters. This width is used only in the XML documents of worksheets and is usually not exposed to the (Excel) end user
      API Note:
      The internal width deviates slightly from the column width, entered in Excel. Although internal, the default column width of 10 characters is visible in Excel as 10.71. The deviation depends on the maximum digit width of the default font, as well as its text padding and various constants.
      In case of the width 10.0 and the default digit width 7.0, as well as the padding 5.0 of the default font Calibri (size 11), the internal width is approximately 10.7142857 (rounded to 10.71).
      Note that the column height is not affected by this consideration. The entered height in Excel is the actual height in the worksheet XML documents.
      This method is derived from the Perl implementation by John McNamara (https://stackoverflow.com/a/5010899)
      See also: ECMA-376, Part 1, Chapter 18.3.1.13
      Parameters:
      columnWidth - Target column width (displayed in Excel)
      Returns:
      The internal column width in characters, used in worksheet XML documents

    • getInternalColumnWidth

      public static float getInternalColumnWidth(float columnWidth, float maxDigitWidth, float textPadding)
      Calculates the internal width of a column in characters. This width is used only in the XML documents of worksheets and is usually not exposed to the (Excel) end user
      API Note:
      The internal width deviates slightly from the column width, entered in Excel. Although internal, the default column width of 10 characters is visible in Excel as 10.71. The deviation depends on the maximum digit width of the default font, as well as its text padding and various constants.
      In case of the width 10.0 and the default digit width 7.0, as well as the padding 5.0 of the default font Calibri (size 11), the internal width is approximately 10.7142857 (rounded to 10.71).
      Note that the column height is not affected by this consideration. The entered height in Excel is the actual height in the worksheet XML documents.
      This method is derived from the Perl implementation by John McNamara (https://stackoverflow.com/a/5010899)
      See also: ECMA-376, Part 1, Chapter 18.3.1.13
      Parameters:
      columnWidth - Target column width (displayed in Excel)
      maxDigitWidth - Maximum digit with of the default font (default is 7.0 for Calibri, size 11)
      textPadding - Text padding of the default font (default is 5.0 for Calibri, size 11)
      Returns:
      The internal column width in characters, used in worksheet XML documents
      Throws:
      FormatException - thrown if the column width is out of range

    • getInternalRowHeight

      public static float getInternalRowHeight(float rowHeight)
      Calculates the internal height of a row. This height is used only in the XML documents of worksheets and is usually not exposed to the (Excel) end user
      API Note:
      The height is based on the calculated amount of pixels. One point are ~1.333 (1+1/3) pixels. After the conversion, the number of pixels is rounded to the nearest integer and calculated back to points.
      Therefore, the originally defined row height will slightly deviate, based on this pixel snap
      Parameters:
      rowHeight - Target row height (displayed in Excel)
      Returns:
      The internal row height which snaps to the nearest pixel
      Throws:
      FormatException - thrown if the row height is out of range

    • getInternalPaneSplitWidth

      public static float getInternalPaneSplitWidth(float width)
      Calculates the internal width of a split pane in a worksheet. This width is used only in the XML documents of worksheets and is not exposed to the (Excel) end user
      API Note:
      The internal split width is based on the width of one or more columns. It also depends on the maximum digit width of the default font, as well as its text padding and various constants.
      See also getInternalColumnWidth(float, float, float) for additional details.
      This method is derived from the Perl implementation by John McNamara (https://stackoverflow.com/a/5010899)
      See also: ECMA-376, Part 1, Chapter 18.3.1.13
      Parameters:
      width - Target column(s) width (one or more columns, displayed in Excel)
      Returns:
      The internal pane width, used in worksheet XML documents in case of worksheet splitting

    • getInternalPaneSplitWidth

      public static float getInternalPaneSplitWidth(float width, float maxDigitWidth, float textPadding)
      Calculates the internal width of a split pane in a worksheet. This width is used only in the XML documents of worksheets and is not exposed to the (Excel) end user
      API Note:
      The internal split width is based on the width of one or more columns. It also depends on the maximum digit width of the default font, as well as its text padding and various constants.
      See also getInternalColumnWidth(float, float, float) for additional details.
      This method is derived from the Perl implementation by John McNamara (https://stackoverflow.com/a/5010899)
      See also: ECMA-376, Part 1, Chapter 18.3.1.13
      The two override parameters maxDigitWidth and textPadding probably don't have to be other than maxDigitWidth = 7f and textPadding = 5f.
      Parameters:
      width - Target column(s) width (one or more columns, displayed in Excel)
      maxDigitWidth - Maximum digit with of the default font (default is 7.0 for Calibri, size 11)
      textPadding - Text padding of the default font (default is 5.0 for Calibri, size 11)
      Returns:
      The internal pane width, used in worksheet XML documents in case of worksheet splitting

    • getInternalPaneSplitHeight

      public static float getInternalPaneSplitHeight(float height)
      Calculates the internal height of a split pane in a worksheet. This height is used only in the XML documents of worksheets and is not exposed to the (Excel) user
      API Note:
      The internal split height is based on the height of one or more rows. It also depends on various constants.
      This method is derived from the Perl implementation by John McNamara (https://stackoverflow.com/a/5010899).
      Negative row heights are automatically transformed to 0.
      Parameters:
      height - Target row(s) height (one or more rows, displayed in Excel)
      Returns:
      The internal pane height, used in worksheet XML documents in case of worksheet splitting

    • getPaneSplitHeight

      public static float getPaneSplitHeight(float internalHeight)
      Calculates the height of a split pane in a worksheet, based on the internal value (calculated by getInternalPaneSplitHeight(float))
      API Note:
      Depending on the initial height, the result value of getInternalPaneSplitHeight(float) may not lead back to the initial value, since rounding is applied when calculating the internal height
      Parameters:
      internalHeight - Internal pane height stored in a worksheet. The minimal value is defined by SPLIT_HEIGHT_POINT_OFFSET
      Returns:
      Actual pane height

    • getPaneSplitWidth

      public static float getPaneSplitWidth(float internalWidth)
      Calculates the width of a split pane in a worksheet, based on the internal value (calculated by getInternalPaneSplitWidth(float, float, float))
      API Note:
      Depending on the initial width, the result value of getInternalPaneSplitWidth(float) or getInternalPaneSplitWidth(float, float, float) may not lead back to the initial value, since rounding is applied when calculating the internal width
      Parameters:
      internalWidth - Internal pane width stored in a worksheet. The minimal value is defined by SPLIT_WIDTH_POINT_OFFSET
      Returns:
      Actual pane width

    • getPaneSplitWidth

      public static float getPaneSplitWidth(float internalWidth, float maxDigitWidth, float textPadding)
      Calculates the width of a split pane in a worksheet, based on the internal value (calculated by getInternalPaneSplitWidth(float, float, float))
      API Note:
      Depending on the initial width, the result value of getInternalPaneSplitWidth(float) or getInternalPaneSplitWidth(float, float, float) may not lead back to the initial value, since rounding is applied when calculating the internal width
      Parameters:
      internalWidth - Internal pane width stored in a worksheet. The minimal value is defined by SPLIT_WIDTH_POINT_OFFSET
      maxDigitWidth - Maximum digit with of the default font (default is 7.0 for Calibri, size 11)
      textPadding - Text padding of the default font (default is 5.0 for Calibri, size 11)
      Returns:
      Actual pane width

    • generatePasswordHash

      public static String generatePasswordHash(String password)
      Method to generate an Excel internal password hash to protect workbooks or worksheets
      This method is derived from the c++ implementation by Kohei Yoshida (http://kohei.us/2008/01/18/excel-sheet-protection-password-hash/)
      WARNING! Do not use this method to encrypt 'real' passwords or data outside from PicoXLSX4j. This is only a minor security feature. Use a proper cryptography method instead.
      Parameters:
      password - Password as plain text
      Returns:
      Encoded password

    • isNullOrEmpty

      public static boolean isNullOrEmpty(String value)
      Method to check a string whether its reference is null or the content is empty
      Parameters:
      value - value / reference to check
      Returns:
      True if the passed parameter is null or empty, otherwise false

    • createDuration

      public static Duration createDuration(int hours, int minutes, int seconds)
      Method to create a Duration object form hours, minutes and second
      Parameters:
      hours - Number of Hours within the day
      minutes - Number of minutes within the hour
      seconds - Number of seconds within the minute
      Returns:
      Duration object
      Throws:
      FormatException - thrown if one of the components is invalid (e.g. negative), or if the number of days exceeds the year 9999

    • createDuration

      public static Duration createDuration(int days, int hours, int minutes, int seconds)
      Method to create a Duration object form days, hours, minutes and second
      Parameters:
      days - Total number of days
      hours - Number of Hours within the day
      minutes - Number of minutes within the hour
      seconds - Number of seconds within the minute
      Returns:
      Duration object
      Throws:
      FormatException - thrown if one of the components is invalid (e.g. negative), or if the number of days exceeds the year 9999