Simple static class, that allows adding metadata for excel when creating clipboard data in C#

ExcelClipboard.cs

// Example:
// var amount = 52163215.23m;
// Clipboard.Clear();
// Clipboard.SetText(amount.ToString());
// ExcelClipboard.SetDataNumber(amount)

public static class ExcelClipboard
{
    /// <summary>
    /// Returns if the current value is of numeric type.
    /// WARNING: Enums are also recognized as numeric types, because enum is based on numeric values.
    /// </summary>
    public static bool IsNumericType(this object o)
    {
        switch (Type.GetTypeCode(o.GetType()))
        {
            case TypeCode.Byte:
            case TypeCode.SByte:
            case TypeCode.UInt16:
            case TypeCode.UInt32:
            case TypeCode.UInt64:
            case TypeCode.Int16:
            case TypeCode.Int32:
            case TypeCode.Int64:
            case TypeCode.Decimal:
            case TypeCode.Double:
            case TypeCode.Single:
                return true;

            default:
                return false;
        }
    }

    /// <summary>
    /// Adds number metadata for Excel to current clipboard data.
    /// </summary>
    public static void SetDataNumber(object number)
    {
        if (!number.IsNumericType())
            throw new ArgumentException($@"Only numeric types allowed (not: {number.GetType()})", nameof(number));

        // invariant culture, aby se použila desetinná tečka
        var data = string.Format(NumberFormatInfo.InvariantInfo, NumberTemplate, number);

        // uložím do schránky
        SetXMLSpreadSheetToClipboard(data);
    }

    /// <summary>
    /// Adds date metadata for Excel to current clipboard data.
    /// </summary>
    public static void SetDataDate(DateTime date)
    {
        var data = string.Format(DateTemplate, date);

        // uložím do schránky
        SetXMLSpreadSheetToClipboard(data);
    }

    /// <summary>
    /// Adds date and time metadata for Excel to current clipboard data.
    /// </summary>
    public static void SetDataDateTime(DateTime date)
    {
        var data = string.Format(DateTimeTemplate, date);

        // uložím do schránky
        SetXMLSpreadSheetToClipboard(data);
    }

    /// <summary>
    /// Adds time metadata for Excel to current clipboard data.
    /// </summary>
    public static void SetDataTime(TimeSpan time)
    {
        var data = string.Format(TimeTemplate, time);

        // uložím do schránky
        SetXMLSpreadSheetToClipboard(data);
    }

    #region Templates

    private const string NumberTemplate = @"<?xml version=""1.0""?>
<?mso-application progid=""Excel.Sheet""?>
<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
xmlns:o=""urn:schemas-microsoft-com:office:office""
xmlns:x=""urn:schemas-microsoft-com:office:excel""
xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
xmlns:html=""http://www.w3.org/TR/REC-html40"">
<Styles>
<Style ss:ID=""s62"">
<NumberFormat ss:Format=""Fixed""/>
</Style>
</Styles>
<Worksheet ss:Name=""List1"">
<Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1""
ss:DefaultRowHeight=""15"">
<Column ss:AutoFitWidth=""0"" ss:Width=""72.75""/>
<Row>
<Cell ss:StyleID=""s62""><Data ss:Type=""Number"">{0}</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>";

    private const string DateTemplate = @"<?xml version=""1.0""?>
<?mso-application progid=""Excel.Sheet""?>
<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
xmlns:o=""urn:schemas-microsoft-com:office:office""
xmlns:x=""urn:schemas-microsoft-com:office:excel""
xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
xmlns:html=""http://www.w3.org/TR/REC-html40"">
<Styles>
<Style ss:ID=""s62"">
<NumberFormat ss:Format=""Short Date""/>
</Style>
</Styles>
<Worksheet ss:Name=""List1"">
<Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1""
ss:DefaultRowHeight=""15"">
<Column ss:Width=""53.25""/>
<Row>
<Cell ss:StyleID=""s62""><Data ss:Type=""DateTime"">{0:s}</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>";

    private const string DateTimeTemplate = @"<?xml version=""1.0""?>
<?mso-application progid=""Excel.Sheet""?>
<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
xmlns:o=""urn:schemas-microsoft-com:office:office""
xmlns:x=""urn:schemas-microsoft-com:office:excel""
xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
xmlns:html=""http://www.w3.org/TR/REC-html40"">
<Styles>
<Style ss:ID=""s66"">
<NumberFormat ss:Format=""d/m/yy\ h:mm;@""/>
</Style>
</Styles>
<Worksheet ss:Name=""List1"">
<Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1""
ss:DefaultRowHeight=""15"">
<Column ss:Width=""102""/>
<Row>
<Cell ss:StyleID=""s66""><Data ss:Type=""DateTime"">{0:s}</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>";

    private const string TimeTemplate = @"<?xml version=""1.0""?>
<?mso-application progid=""Excel.Sheet""?>
<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
xmlns:o=""urn:schemas-microsoft-com:office:office""
xmlns:x=""urn:schemas-microsoft-com:office:excel""
xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
xmlns:html=""http://www.w3.org/TR/REC-html40"">
<Styles>
<Style ss:ID=""s62"">
<NumberFormat ss:Format=""[$-F400]h:mm:ss\ AM/PM""/>
</Style>
</Styles>
<Worksheet ss:Name=""List1"">
<Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1""
ss:DefaultRowHeight=""15"">
<Row>
<Cell ss:StyleID=""s62""><Data ss:Type=""DateTime"">1899-12-31T{0:hh':'mm':'ss'.'fff}</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>";

    #endregion

    #region Helpers

    // Original solution:
    // https://stackoverflow.com/questions/15764828/c-sharp-add-excel-text-formatted-data-to-clipboard/15766662#15766662
    // Application was sometime error quiting, because of this:
    // https://stackoverflow.com/questions/14082942/copy-result-to-clipboard/24698804#24698804
    // Solved using the Clippy class, you can find below

    private static bool SetXMLSpreadSheetToClipboard(string sheet)
    {
        var result = Clippy.PushDataToClipboard(sheet, "XML SpreadSheet");
        return result.IsSuccess;
    }

    /*
     * Source: https://github.com/kolibridev/clippy/blob/master/Clippy/Clippy.cs
     * Gh61 (2024-02-15):
     *  - refactoring
     *  + clipboard format
     */
    private static class Clippy
    {
        [DllImport("kernel32.dll")]
        private static extern IntPtr GlobalAlloc(uint uFlags, UIntPtr dwBytes);

        [DllImport("kernel32.dll")]
        private static extern uint GetLastError();

        [DllImport("kernel32.dll")]
        private static extern IntPtr LocalFree(IntPtr hMem);

        [DllImport("kernel32.dll")]
        private static extern IntPtr GlobalFree(IntPtr hMem);

        [DllImport("kernel32.dll")]
        private static extern IntPtr GlobalLock(IntPtr hMem);

        [DllImport("kernel32.dll")]
        [return: MarshalAs(UnmanagedType.Bool)]
        private static extern bool GlobalUnlock(IntPtr hMem);

        [DllImport("kernel32.dll", EntryPoint = "CopyMemory", SetLastError = false)]
        private static extern void CopyMemory(IntPtr dest, IntPtr src, uint count);

        [DllImport("user32.dll", SetLastError = true)]
        private static extern uint RegisterClipboardFormat(string lpszFormat);

        [DllImport("user32.dll")]
        [return: MarshalAs(UnmanagedType.Bool)]
        private static extern bool OpenClipboard(IntPtr hWndNewOwner);

        [DllImport("user32.dll")]
        [return: MarshalAs(UnmanagedType.Bool)]
        private static extern bool CloseClipboard();

        [DllImport("user32.dll")]
        private static extern IntPtr SetClipboardData(uint uFormat, IntPtr data);

        [STAThread]
        public static Result PushDataToClipboard(string data, string formatName)
        {
            uint formatID;
            try
            {
                formatID = RegisterClipboardFormat(formatName);
                if (formatID == 0)
                {
                    return new Result(ResultCode.ErrorRegisterFormat, GetLastError());
                }
            }
            catch
            {
                return new Result(ResultCode.ErrorGetLastError);
            }

            return PushStringToClipboardCore(data, formatID, isUnicode: false);
        }

        [STAThread]
        public static Result PushStringToClipboard(string message)
        {
            var isAscii = (message != null && (message == Encoding.ASCII.GetString(Encoding.ASCII.GetBytes(message))));
            if (isAscii)
            {
                return PushUnicodeStringToClipboard(message);
            }
            else
            {
                return PushAnsiStringToClipboard(message);
            }
        }

        [STAThread]
        public static Result PushUnicodeStringToClipboard(string message)
        {
            return PushStringToClipboardCore(message, 13, isUnicode: true);
        }

        [STAThread]
        public static Result PushAnsiStringToClipboard(string message)
        {
            return PushStringToClipboardCore(message, 1, isUnicode: false);
        }

        [STAThread]
        private static Result PushStringToClipboardCore(string message, uint format, bool isUnicode = false)
        {
            try
            {
                try
                {
                    if (message == null)
                    {
                        return new Result(ResultCode.ErrorInvalidArgs);
                    }

                    if (!OpenClipboard(IntPtr.Zero))
                    {
                        return new Result(ResultCode.ErrorOpenClipboard, GetLastError());
                    }

                    try
                    {
                        uint sizeOfChar = (uint)(isUnicode ? 2 : 1);
                        var characters = (uint)message.Length;
                        uint bytes = (characters + 1) * sizeOfChar;

                        // ReSharper disable once InconsistentNaming
                        const int GMEM_MOVABLE = 0x0002;
                        // ReSharper disable once InconsistentNaming
                        const int GMEM_ZEROINIT = 0x0040;
                        // ReSharper disable once InconsistentNaming
                        const int GHND = GMEM_MOVABLE | GMEM_ZEROINIT;

                        // IMPORTANT: SetClipboardData requires memory that was acquired with GlobalAlloc using GMEM_MOVABLE.
                        var hGlobal = GlobalAlloc(GHND, (UIntPtr)bytes);
                        if (hGlobal == IntPtr.Zero)
                        {
                            return new Result(ResultCode.ErrorGlobalAlloc, GetLastError());
                        }

                        try
                        {
                            // IMPORTANT: Marshal.StringToHGlobalUni allocates using LocalAlloc with LMEM_FIXED.
                            //            Note that LMEM_FIXED implies that LocalLock / LocalUnlock is not required.
                            IntPtr source = isUnicode
                                ? Marshal.StringToHGlobalUni(message)
                                : Marshal.StringToHGlobalAnsi(message);

                            try
                            {
                                var target = GlobalLock(hGlobal);
                                if (target == IntPtr.Zero)
                                {
                                    return new Result(ResultCode.ErrorGlobalLock, GetLastError());
                                }

                                try
                                {
                                    CopyMemory(target, source, bytes);
                                }
                                finally
                                {
                                    _ = GlobalUnlock(target);
                                }

                                if (SetClipboardData(format, hGlobal).ToInt64() != 0)
                                {
                                    // IMPORTANT: SetClipboardData takes ownership of hGlobal upon success.
                                    hGlobal = IntPtr.Zero;
                                }
                                else
                                {
                                    return new Result(ResultCode.ErrorSetClipboardData, GetLastError());
                                }
                            }
                            finally
                            {
                                // Marshal.StringToHGlobalUni actually allocates with LocalAlloc, thus we should theoretically use LocalFree to free the memory...
                                // ... but Marshal.FreeHGlobal actually uses a corresponding version of LocalFree internally, so this works, even though it doesn't
                                //  behave exactly as expected.
                                Marshal.FreeHGlobal(source);
                            }
                        }
                        catch (OutOfMemoryException)
                        {
                            return new Result(ResultCode.ErrorOutOfMemoryException, GetLastError());
                        }
                        catch (ArgumentOutOfRangeException)
                        {
                            return new Result(ResultCode.ErrorArgumentOutOfRangeException, GetLastError());
                        }
                        finally
                        {
                            if (hGlobal != IntPtr.Zero)
                            {
                                _ = GlobalFree(hGlobal);
                            }
                        }
                    }
                    finally
                    {
                        CloseClipboard();
                    }
                    return new Result(ResultCode.Success);
                }
                catch (Exception)
                {
                    return new Result(ResultCode.ErrorException, GetLastError());
                }
            }
            catch (Exception)
            {
                return new Result(ResultCode.ErrorGetLastError);
            }
        }

        public enum ResultCode
        {
            Success = 0,

            ErrorRegisterFormat,
            ErrorOpenClipboard,
            ErrorGlobalAlloc,
            ErrorGlobalLock,
            ErrorSetClipboardData,
            ErrorOutOfMemoryException,
            ErrorArgumentOutOfRangeException,
            ErrorException,
            ErrorInvalidArgs,
            ErrorGetLastError,
        }

        public class Result
        {
            public Result(ResultCode code, uint lastError = 0)
            {
                ResultCode = code;
                LastError = lastError;
            }

            public ResultCode ResultCode { get; }

            public uint LastError { get; }

            // ReSharper disable once RedundantNameQualifier
            public bool IsSuccess => Clippy.ResultCode.Success == ResultCode;
        }
    }

    #endregion
}