General data utils class with static methods. More...

Public Types
enum	RangeMergeStrategy { NoMerge , MergeColumns , MergeRows }
	Strategy how ranges should be merged. More...

Static Public Member Functions
static string	GetOADateTimeString (DateTime date)
	Method to convert a date or date and time into the internal Excel time format (OAdate).
static double	GetOADateTime (DateTime date, bool skipCheck=false)
	Method to convert a date or date and time into the internal Excel time format (OAdate).
static string	GetOATimeString (TimeSpan time)
	Method to convert a time into the internal Excel time format (OAdate without days).
static double	GetOATime (TimeSpan time)
	Method to convert a time into the internal Excel time format (OAdate without days).
static DateTime	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.
static float	GetInternalColumnWidth (float columnWidth, float maxDigitWidth=7f, float textPadding=5f)
	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.
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.
static float	GetInternalPaneSplitWidth (float width, float maxDigitWidth=7f, float textPadding=5f)
	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.
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.
static float	GetPaneSplitHeight (float internalHeight)
	Calculates the height of a split pane in a worksheet, based on the internal value (calculated by GetInternalPaneSplitHeight(float)).
static float	GetPaneSplitWidth (float internalWidth, float maxDigitWidth=7f, float textPadding=5f)
	Calculates the width of a split pane in a worksheet, based on the internal value (calculated by GetInternalPaneSplitWidth(float, float, float)).
static IReadOnlyList< Range >	MergeRange (List< Range > givenRanges, Range newRange, RangeMergeStrategy strategy=RangeMergeStrategy.MergeColumns)
	Merges a range with a list of given ranges. If there is no intersection between the list and the new range, the range is just added to the given list. If there is an intersection, the range will be merged and the new list of ranges will be returned.
static IReadOnlyList< Range >	SubtractRange (List< Range > givenRanges, Range rangeToRemove, RangeMergeStrategy strategy=RangeMergeStrategy.MergeColumns)
	Subtracts a range form a list of given ranges. If the range to be removed does not intersect any of the given ranges, nothing happens. If the range intersects at least one of the given ranges, the intersection will be removed and the new ranges well be returned.

Static Public Attributes
static readonly double	MinOADateValue = 0d
	Minimum valid OAdate value (1900-01-01). However, Excel displays this value as 1900-01-00 (day zero).
static readonly double	MaxOADateValue = 2958465.999988426d
	Maximum valid OAdate value (9999-12-31).
static readonly DateTime	FirstAllowedExcelDate = new DateTime(1900, 1, 1, 0, 0, 0, DateTimeKind.Unspecified)
	First date that can be displayed by Excel. Real values before this date cannot be processed.
static readonly DateTime	LastAllowedExcelDate = new DateTime(9999, 12, 31, 23, 59, 59, DateTimeKind.Unspecified)
	Last date that can be displayed by Excel. Real values after this date cannot be processed.
static readonly DateTime	FirstValidExcelDate = new DateTime(1900, 3, 1, 0, 0, 0, DateTimeKind.Unspecified)
	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.
static readonly CultureInfo	InvariantCulture = CultureInfo.InvariantCulture
	Constant for number conversions. The invariant culture (represents mostly the US numbering scheme) ensures that no culture-specific punctuations are used when converting numbers to strings, This is especially important for OOXML number values. See also: https://docs.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo.invariantculture?view=net-5.0.

Detailed Description

General data utils class with static methods.

Definition at line 19 of file DataUtils.cs.

Member Enumeration Documentation

◆ RangeMergeStrategy

enum NanoXLSX.Utils.DataUtils.RangeMergeStrategy

Strategy how ranges should be merged.

Enumerator
NoMerge	No merge should be performed.
MergeColumns	Ranges of the same columns should be merged.
MergeRows	Ranges of the same row should be merged.

Definition at line 74 of file DataUtils.cs.

Member Function Documentation

◆ GetDateFromOA()

DateTime NanoXLSX.Utils.DataUtils.GetDateFromOA ( double oaDate )

static

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.

Parameters

oaDate oaDate OA date number

Returns: Converted date

Remarks: 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

Definition at line 175 of file DataUtils.cs.

◆ GetInternalColumnWidth()

float NanoXLSX.Utils.DataUtils.GetInternalColumnWidth	(	float	columnWidth,
		float	maxDigitWidth = 7f,
		float	textPadding = 5f )

static

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.

Remarks: 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

Exceptions

FormatException Throws a FormatException if the column width is out of range

Definition at line 201 of file DataUtils.cs.

◆ GetInternalPaneSplitHeight()

float NanoXLSX.Utils.DataUtils.GetInternalPaneSplitHeight ( float height )

static

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.

Remarks: 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

Definition at line 286 of file DataUtils.cs.

◆ GetInternalPaneSplitWidth()

float NanoXLSX.Utils.DataUtils.GetInternalPaneSplitWidth	(	float	width,
		float	maxDigitWidth = 7f,
		float	textPadding = 5f )

static

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.

Remarks: 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 optional parameters maxDigitWidth and textPadding probably don't have to be changed ever. Negative column widths are automatically transformed to 0.

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

Definition at line 259 of file DataUtils.cs.

◆ GetInternalRowHeight()

float NanoXLSX.Utils.DataUtils.GetInternalRowHeight ( float rowHeight )

static

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.

Remarks: 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

Exceptions

FormatException Throws a FormatException if the row height is out of range

Definition at line 230 of file DataUtils.cs.

◆ GetOADateTime()

double NanoXLSX.Utils.DataUtils.GetOADateTime	(	DateTime	date,
		bool	skipCheck = false )

static

Method to convert a date or date and time into the internal Excel time format (OAdate).

Parameters

skipCheck	Optional flag to skip the validity check if set to true
date	Date to process

Returns: Date or date and time as number

Exceptions

NanoXLSX.Exceptions.FormatException Throws a FormatException if the passed date cannot be translated to the OADate format

Remarks: 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

Definition at line 126 of file DataUtils.cs.

◆ GetOADateTimeString()

string NanoXLSX.Utils.DataUtils.GetOADateTimeString ( DateTime date )

static

Method to convert a date or date and time into the internal Excel time format (OAdate).

Parameters

date	Date to process

Returns: Date or date and time as number string

Exceptions

NanoXLSX.Exceptions.FormatException Throws a FormatException if the passed date cannot be translated to the OADate format

Remarks: 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

Definition at line 105 of file DataUtils.cs.

◆ GetOATime()

double NanoXLSX.Utils.DataUtils.GetOATime ( TimeSpan time )

static

Method to convert a time into the internal Excel time format (OAdate without days).

Parameters

time	Time to process. The date component of the timespan is converted to the total numbers of days

Returns: Time as number

Remarks: The time is represented by a OAdate without the date component but a possible number of total days

Definition at line 159 of file DataUtils.cs.

◆ GetOATimeString()

string NanoXLSX.Utils.DataUtils.GetOATimeString ( TimeSpan time )

static

Method to convert a time into the internal Excel time format (OAdate without days).

Parameters

time	Time to process. The date component of the timespan is converted to the total numbers of days

Returns: Time as number string

Remarks: The time is represented by a OAdate without the date component but a possible number of total days

Definition at line 147 of file DataUtils.cs.

◆ GetPaneSplitHeight()

float NanoXLSX.Utils.DataUtils.GetPaneSplitHeight ( float internalHeight )

static

Calculates the height of a split pane in a worksheet, based on the internal value (calculated by GetInternalPaneSplitHeight(float)).

Parameters

internalHeight Internal pane height stored in a worksheet. The minimal value is defined by SPLIT_HEIGHT_POINT_OFFSET

Returns: Actual pane height

Remarks: 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

Definition at line 302 of file DataUtils.cs.

◆ GetPaneSplitWidth()

float NanoXLSX.Utils.DataUtils.GetPaneSplitWidth	(	float	internalWidth,
		float	maxDigitWidth = 7f,
		float	textPadding = 5f )

static

Calculates the width of a split pane in a worksheet, based on the internal value (calculated by GetInternalPaneSplitWidth(float, float, float)).

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

Remarks: Depending on the initial width, the result value of GetInternalPaneSplitWidth(float,float,float) may not lead back to the initial value, since rounding is applied when calculating the internal width

Definition at line 323 of file DataUtils.cs.

◆ MergeRange()

IReadOnlyList< Range > NanoXLSX.Utils.DataUtils.MergeRange	(	List< Range >	givenRanges,
		Range	newRange,
		RangeMergeStrategy	strategy = RangeMergeStrategy::MergeColumns )

static

Merges a range with a list of given ranges. If there is no intersection between the list and the new range, the range is just added to the given list. If there is an intersection, the range will be merged and the new list of ranges will be returned.

Parameters

givenRanges	List of given ranges
newRange	The range to be merged
strategy	Strategy for the range recalculation. Depending on the value, the resulting ranges are either merged along rows, along columns (default), or not merged at all

Returns: List of resulting ranges after merging.

Definition at line 345 of file DataUtils.cs.

◆ SubtractRange()

IReadOnlyList< Range > NanoXLSX.Utils.DataUtils.SubtractRange	(	List< Range >	givenRanges,
		Range	rangeToRemove,
		RangeMergeStrategy	strategy = RangeMergeStrategy::MergeColumns )

static

Subtracts a range form a list of given ranges. If the range to be removed does not intersect any of the given ranges, nothing happens. If the range intersects at least one of the given ranges, the intersection will be removed and the new ranges well be returned.

Parameters

givenRanges	List of given ranges
rangeToRemove	The range to be removed
strategy	Strategy for the range recalculation. Depending on the value, the resulting ranges are either merged along rows, along columns (default), or not merged at all

Returns: List of resulting ranges after subtraction and recalculation

Definition at line 429 of file DataUtils.cs.

Member Data Documentation

◆ FirstAllowedExcelDate

readonly DateTime NanoXLSX.Utils.DataUtils.FirstAllowedExcelDate = new DateTime(1900, 1, 1, 0, 0, 0, DateTimeKind.Unspecified)

static

First date that can be displayed by Excel. Real values before this date cannot be processed.

Definition at line 36 of file DataUtils.cs.

◆ FirstValidExcelDate

readonly DateTime NanoXLSX.Utils.DataUtils.FirstValidExcelDate = new DateTime(1900, 3, 1, 0, 0, 0, DateTimeKind.Unspecified)

static

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.

Definition at line 47 of file DataUtils.cs.

◆ InvariantCulture

readonly CultureInfo NanoXLSX.Utils.DataUtils.InvariantCulture = CultureInfo.InvariantCulture

static

Constant for number conversions. The invariant culture (represents mostly the US numbering scheme) ensures that no culture-specific punctuations are used when converting numbers to strings, This is especially important for OOXML number values. See also: https://docs.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo.invariantculture?view=net-5.0.

Definition at line 55 of file DataUtils.cs.

◆ LastAllowedExcelDate

readonly DateTime NanoXLSX.Utils.DataUtils.LastAllowedExcelDate = new DateTime(9999, 12, 31, 23, 59, 59, DateTimeKind.Unspecified)

static

Last date that can be displayed by Excel. Real values after this date cannot be processed.

Definition at line 40 of file DataUtils.cs.

◆ MaxOADateValue

readonly double NanoXLSX.Utils.DataUtils.MaxOADateValue = 2958465.999988426d

static

Maximum valid OAdate value (9999-12-31).

Definition at line 32 of file DataUtils.cs.

◆ MinOADateValue

readonly double NanoXLSX.Utils.DataUtils.MinOADateValue = 0d

static

Minimum valid OAdate value (1900-01-01). However, Excel displays this value as 1900-01-00 (day zero).

Definition at line 27 of file DataUtils.cs.

The documentation for this class was generated from the following file:

DataUtils.cs

Public Types

Static Public Member Functions

Static Public Attributes

Detailed Description

Member Enumeration Documentation

◆ RangeMergeStrategy

Member Function Documentation

◆ GetDateFromOA()

◆ GetInternalColumnWidth()

◆ GetInternalPaneSplitHeight()

◆ GetInternalPaneSplitWidth()

◆ GetInternalRowHeight()

◆ GetOADateTime()

◆ GetOADateTimeString()

◆ GetOATime()

◆ GetOATimeString()

◆ GetPaneSplitHeight()

◆ GetPaneSplitWidth()

◆ MergeRange()

◆ SubtractRange()

Member Data Documentation

◆ FirstAllowedExcelDate

◆ FirstValidExcelDate

◆ InvariantCulture

◆ LastAllowedExcelDate

◆ MaxOADateValue

◆ MinOADateValue