T-SQL Try-Catch Template

–TRY/CATCH TEMPLATE
BEGIN TRY

–check to see if .Net initiated a SqlTransaction before calling this proc
IF @@TRANCOUNT = 0
BEGIN
–.NET did not initiate an explicit SqlTransaction outside of the procedure call;
— as a result, we initiate an explicit T-SQL transaction here.
BEGIN TRAN;
END

/* PUT SCRIPT CODE RIGHT HERE */

— PRINT ‘>> COMMITING TRANSACTION’ — optional
— no error yet, so commit the transaction
— if query is select only; this statement is meaningless.
IF @@TRANCOUNT = 1 COMMIT TRAN;

END TRY

BEGIN CATCH

/*
There will be either a .Net SqlTransaction or T-SQL transaction here to rollback;
we need to roll back that transaction before handling the exception.

Test XACT_STATE for 0, 1, or -1.
1 = the transaction is COMMITTABLE (we are going to roll back anyways).
-1 = the transaction is UNCOMMITTABLE and should be rolled back.
0 = there is no transaction and a commit or rollback operation would generate an error.

Test whether the transaction is uncommittable.
*/

IF (XACT_STATE() <> 0)
BEGIN
PRINT ‘>> ROLLING BACK TRANSACTION’
ROLLBACK TRANSACTION;
END

—- OPTIONAL EXCEPTION PROPERTIES TO CATCH AS NEEDED.
–@ErrorNo INT = ERROR_NUMBER(),
–@ErrorProcedure NVARCHAR(200) = ERROR_PROCEDURE(),
–@ErrorLine AS INT = ERROR_LINE()

DECLARE @ErrorMessage NVARCHAR(4000) ,
@ErrorSeverity INT ,
@ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE();

— Use RAISERROR inside the CATCH block to return error information
— about the original error that caused execution to jump to the CATCH block.
PRINT ‘>> RAISING ERROR’
https://technet.microsoft.com/en-us/library/ms177497(v=sql.105).aspx
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState );

END CATCH

END CATCH

dbo.tvf_TallyTable

IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = object_id (N’dbo.tvf_TallyTable’)
AND type IN ( N’FN’, N’IF’, N’TF’) )
BEGIN
DROP FUNCTION dbo.tvf_TallyTable;
END
GO
CREATE FUNCTION dbo.tvf_TallyTable
(
@Low INT,
@High INT
)
RETURNS TABLE WITH SCHEMABINDING
AS
/*
*******************************************************************************
Created By: Barry Blessing (b567123)
——————————————————————————-
Date Created:
——————————————————————————-
Purpose: “Inline” CTE-driven “Tally Table” produces values from 1 to 10,000
Database:
——————————————————————————-
Parameters:
——————————————————————————-
Performance Statistics:
*******************************************************************************
Date Ran:

Test Statements ran:
SELECT * FROM dbo.tvf_TallyTable( 2, 13);
SELECT * FROM dbo.tvf_TallyTable( 0, 1);
SELECT * FROM dbo.tvf_TallyTable( 1, 2);
SELECT * FROM dbo.tvf_TallyTable( 0, CAST( ISNULL(DATEDIFF( month, ‘2016-09-01’, ‘2016-09-30’ ), 1) AS INT) );

Run time in seconds: 0
*******************************************************************************
Maintenance Log:
*******************************************************************************
Date Version PCM Author Comments
——————————————————————————-

*******************************************************************************
*/
RETURN
WITH t1(IndexNo) AS ( SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0) , /* 10E+1 or 10 rows */

t2(IndexNo) AS ( SELECT 0 FROM t1 AS a, t1 AS b ) , /* 10E+2 or 100 rows */

t3(IndexNo) AS ( SELECT 0 FROM t2 AS a, t2 AS b ) , /* 10E+4 or 10,000 rows max */

cteTally(IndexNo) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 )

SELECT IndexNo
FROM cteTally
WHERE IndexNo >= @Low AND IndexNo <= @High;
GO

dbo.fn_IsEmptyField

USE [insert_database_name];
GO
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = object_id (N'dbo.fn_IsEmptyField')
AND type IN ( N'FN', N'IF', N'TF') )
BEGIN
DROP FUNCTION dbo.fn_IsEmptyField
END
GO
CREATE FUNCTION dbo.fn_IsEmptyField
(
@InputObject AS SQL_VARIANT
)
RETURNS BIT
AS
/*
*****************************************************************************
Created By: Barry Blessing
-------------------------------------------------------------------------------
Date Created: 12/01/2016
-------------------------------------------------------------------------------
Purpose: This function evaluates input for NULL-ness or empty-state,
i.e. String.Empty or NULL or ' ', etc.
Database: CMU
-------------------------------------------------------------------------------
Parameters: @@InputObject: Type SQL_VARIANT can represent any SQL or .NET type.
-------------------------------------------------------------------------------
Performance Statistics
*******************************************************************************
Date Ran: 12/01/2016

Test Statements ran:

-- THESE ARE NULL/EMPTY
SELECT dbo.fn_IsEmptyField(NULL) AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(NULL) AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(' ') AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('') AS 'RETURNED', 1 as 'SHOULD BE'

-- THESE ARE ***NOT*** NULL/EMPTY
SELECT dbo.fn_IsEmptyField('John Q Public Co.') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField('0') AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('1') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField(0) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(1) AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField('False') AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('True') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField(GETDATE()) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('12/2/2016') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField(12.32) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(123456.65) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('12,3456.65') AS 'RETURNED', 0 as 'SHOULD BE'

Run time in seconds: 0
*******************************************************************************
Maintenance Log
*******************************************************************************
Date Ver PCM Author Comments
-------------------------------------------------------------------------------
12/01/2016 1.00 xxxxxx Barry Blessing Created in advance of need.
*******************************************************************************
*/
BEGIN
DECLARE @string AS VARCHAR(8000) = CAST(@InputObject AS VARCHAR(8000));
DECLARE @length AS BIGINT = LEN(LTRIM(RTRIM(@string)));
DECLARE @return AS BIT;

If @InputObject IS NULL OR @InputObject = NULL RETURN 1; -- IsNull

SET @return = CASE WHEN @length > 0 AND NOT @string IS NULL AND
NOT UPPER(LTRIM(RTRIM(@string))) = 'NULL' THEN 0
ELSE 1 -- = String.Empty or IsNull
END;

RETURN @return;
END
GO

EXEC dbo.sp_UpdateFunctionDescription @Function = 'fn_IsEmptyField',
@Description = 'Returns Boolean if String-value of input object is neither equivalent to NULL or String.Empty.'
GO

dbo.fn_CleanPhoneNo

USE <database_name>;
GO
IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = object_id (N'dbo.fn_CleanPhoneNo')
AND type IN ( N'FN', N'IF', N'TF') )
BEGIN
DROP FUNCTION dbo.fn_CleanPhoneNo;
END
GO
CREATE FUNCTION dbo.fn_CleanPhoneNo
(
@sPhoneNo VARCHAR(40),
@iDefaultAreaCode INT = NULL
-- worst case input scenario is something like 1-212-456-7890 extension 654967
-- worst case output scenario is something like 212-456-7890 x654967
-- seven-digit or greater extension numbers should not exist
)
RETURNS VARCHAR(20)
AS
/*
*****************************************************************************
Created By: Barry Blessing
-------------------------------------------------------------------------------
Date Created: 2/24/2016
-------------------------------------------------------------------------------
Purpose: Homogenize US-only phone number data, including numbers with extension no's
Database: Yardi
-------------------------------------------------------------------------------
Parameters: @sPhoneNo -- any string representing a US phone number.

Assumptions: AREA CODE IS FIRST SIGNIFICANT THREE INTEGERS,
ASIDE FROM A LEADING ONE OR ZERO

Ideally, extensions would be in a separate table column, but if
the phone number is stored as a string, this is almost never the case.
-------------------------------------------------------------------------------
Performance Statistics:
*******************************************************************************
Date Ran: 2/24/2017

Test Statements ran:

SELECT dbo.fn_CleanPhoneNo('0 249 7890 ', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('1 249 7890 ', '712')
SELECT dbo.fn_CleanPhoneNo(' 249 7890 ', '202')
SELECT dbo.fn_CleanPhoneNo(' 249 7890 ', '618')
SELECT dbo.fn_CleanPhoneNo('0 249 7890 x 345 ', '605')
SELECT dbo.fn_CleanPhoneNo('1 249 7890 x 345 ', '319')

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 654967', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 654967', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 654967', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x654967', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 654967', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 65496', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 65496', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 65496', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x65496', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 65496', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 6549', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 6549', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 6549', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x6549', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 6549', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 654', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 654', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 654', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x654', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 654', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 65', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 65', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 65', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x65', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 65', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 6', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 6', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 6', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x6', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 6', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 ', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890 ', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 249 7890 ', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 6549678', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 6549678', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 6549678', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x6549678', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 6549678', DEFAULT)

Run time in seconds: 0, per SELECT statement above
9100 non-null rows in 0.00xx seconds; only 1146 of which had actual phone numbers
27978 rows in 0.00xx seconds; only 1146 of which had actual phone numbers
*******************************************************************************
Maintenance Log:
*******************************************************************************
Date Version PCM Author Comments
-------------------------------------------------------------------------------
2/24/2017 1.00 Barry Blessing Initial Version
*******************************************************************************
*/
BEGIN

DECLARE @sExtension VARCHAR(7) = NULL;
DECLARE @sExtensionStartPos INT = 0;

IF @sPhoneNo IS NULL
OR LTRIM(RTRIM(@sPhoneNo)) = ''
OR LEN(LTRIM(RTRIM(@sPhoneNo))) <= 7
BEGIN
RETURN LTRIM(RTRIM(ISNULL(@sPhoneNo, '')));
END

SET @sPhoneNo = LOWER(LTRIM(RTRIM(@sPhoneNo)));

/* 'x' will be our 'extension' delimiter */
SET @sPhoneNo = REPLACE(@sPhoneNo, 'extension', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'extnsn', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'extens', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'exten', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'xtsn', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'extn', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'xtn', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'ext', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'ex', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'x', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, ',', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, '-', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, '.', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, '/', ''); /* usually a ten-key error */
SET @sPhoneNo = REPLACE(@sPhoneNo, '\', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, '|', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, '(', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, ')', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, ' ', ' ');
SET @sPhoneNo = REPLACE(@sPhoneNo, ' ', ''); /* usually intentional */
SET @sPhoneNo = LTRIM(RTRIM(@sPhoneNo));

SET @sExtensionStartPos = CHARINDEX('x', @sPhoneNo);

/* grab the extension and save it */
SET @sExtension = CASE WHEN @sExtensionStartPos > 0 THEN
LTRIM(RTRIM(
SUBSTRING( @sPhoneNo,
@sExtensionStartPos,
(LEN(LTRIM(RTRIM(@sPhoneNo))) - @sExtensionStartPos + 1)
)
))
ELSE
NULL
END;

/* rid the phone number of the extension */
SET @sPhoneNo = CASE WHEN @sExtensionStartPos > 0 THEN
LTRIM(RTRIM(SUBSTRING(@sPhoneNo, 1, @sExtensionStartPos - 1)))
ELSE
LTRIM(RTRIM(REPLACE(@sPhoneNo, 'x', '')))
END;

/* strip all non-integers out of the remaining phone number string */
WHILE PATINDEX('%[^0-9]%', @sPhoneNo) > 0
BEGIN
SET @sPhoneNo = LTRIM(RTRIM(
STUFF(
@sPhoneNo,
PATINDEX(
'%[^0-9]%',
@sPhoneNo),
1,
'')
));
END;

/* rid the extension number-string of the 'x' */
SET @sExtension = LTRIM(RTRIM(REPLACE(@sExtension, 'x','')));

/* trim off leading ones or zeros from the main phone number string */
WHILE SUBSTRING(LTRIM(RTRIM(@sPhoneNo)), 1, 1) = '0' OR
SUBSTRING(LTRIM(RTRIM(@sPhoneNo)), 1, 1) = '1'
BEGIN
SET @sPhoneNo = LTRIM(RTRIM(
SUBSTRING( LTRIM(RTRIM(@sPhoneNo)),
2,
LEN(LTRIM(RTRIM(@sPhoneNo)))
)
));
END;

/* assume an area code for all phone numbers entered without an area-code */
SET @sPhoneNo = CASE WHEN @iDefaultAreaCode IS NOT NULL
AND LTRIM(RTRIM(@iDefaultAreaCode)) <> 0
AND @iDefaultAreaCode IS NOT NULL
AND LEN(@sPhoneNo) = 7
AND LEN(@iDefaultAreaCode) = 3 THEN

CAST(@iDefaultAreaCode AS VARCHAR(3)) + @sPhoneNo

WHEN @iDefaultAreaCode IS NULL
OR LTRIM(RTRIM(@iDefaultAreaCode)) = 0
OR LEN(@iDefaultAreaCode) <= 2 THEN

--CASE WHEN LEN(@sPhoneNo) < 10 THEN
-- '515' + @sPhoneNo
-- ELSE
-- @sPhoneNo
--END

@sPhoneNo
ELSE
@sPhoneNo
END;

/* grab an extension where it was entered without an 'x|ext'-style delimiter; where not already parsed out */

SET @sExtension = CASE WHEN (@sExtension IS NULL OR @sExtension = '') AND LEN(@sPhoneNo) > 10 THEN
SUBSTRING( @sPhoneNo,
11,
LEN(@sPhoneNo) - 10
)
ELSE
@sExtension
END;

/* format the phone number return string (which will not contain an extension #), including the extension where applicable */
SET @sPhoneNo = CASE WHEN LEN(@sPhoneNo) = 7
AND LTRIM(RTRIM(@sPhoneNo)) <> ''
AND @sPhoneNo IS NOT NULL
AND ISNUMERIC(@sPhoneNo) = 1 THEN
CASE WHEN @sExtension IS NOT NULL
AND @sExtension <> '' THEN
SUBSTRING(@sPhoneNo, 1, 3) + '-' + SUBSTRING(@sPhoneNo, 4, 4) + ' x' + @sExtension
ELSE
SUBSTRING(@sPhoneNo, 1, 3) + '-' + SUBSTRING(@sPhoneNo, 4, 4)
END
WHEN LEN(@sPhoneNo) BETWEEN 10 AND 16
AND LTRIM(RTRIM(@sPhoneNo)) <> ''
AND @sPhoneNo IS NOT NULL
AND ISNUMERIC(@sPhoneNo) = 1 THEN
CASE WHEN @sExtension IS NOT NULL
AND @sExtension <> '' THEN
SUBSTRING(@sPhoneNo, 1, 3) + '-' + SUBSTRING(@sPhoneNo, 4, 3) + '-' + SUBSTRING(@sPhoneNo, 7, 4) + ' x' + @sExtension
ELSE
SUBSTRING(@sPhoneNo, 1, 3) + '-' + SUBSTRING(@sPhoneNo, 4, 3) + '-' + SUBSTRING(@sPhoneNo, 7, 4)
END
END;

RETURN @sPhoneNo;
END
GO

SQL Server – error 64 : Server-name no longer available

I ran across a crazy issue this am that I’ve never seen before. I was setting up SQL Server Express 2008R2 on a smaller server at home, the likes of which I have done many times before with no issues. Then I did the following, per my usual config routine:

  • Opened ports 1433 and 1434 on this server for “listening” for SQL requests
  • Opened a hole in the firewall for SQLBrowser.exe
  • Enabled TCP/IP listening for those same ports in SQLServerManager10.msc (running on Windows 10 Professional operating system).
  • Enabled remote connections to the Server process
  • Set up a login account for Administrator with full rights to the SQL server processes

So then I went to test the setup from my laptop, fired up SQL Server Management Studio, and connected to the SQL Server.  Connection made – check.  Opened up a known scalar function to modify – check.  Run the alter script – check.  Run it again to double check the persistence of the network connection – ERROR 64.

SQL_Server_Cannot_Connect

I had never seen that one before, but I knew it was network or network protocol related.

I remoted into this particular server and started double-checking all settings that would seem to have any relevance to that issue.

After A LOT of Googling and matching snippets of information together, I finally found resolution with the following regedit modification for the local registry.

  • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters
  • Create a DWORD “Sesstimeout” key with a decimal value of 360.

reg_Edit

It seems the session-layer of the connection was not persistent enough for remote connections.  Why doesn’t Microsoft take care of this by default with a config script? We might never live long enough to know…

Problem solved, for now.  If I see it again, I will update this post.

A couple of gotchas with the ClosedXML library

OpenXML is the file-structure standard upon which modern MS Office documents are created.  ClosedXML is an open-source .NET code library built to more easily implement that standard and allow .NET developers an abstraction against that standard for which they can more easily and efficiently code.

I like the OpenXML standard for Microsoft Office kinds of documents.  However, the open-source library ClosedXML that supports development of spreadsheets to the OpenXML standard needs a little tweaking and more developer contributions.

OPENXML ClosedXML_small3

 

 

A clarification is in order in case you’re confused (and rightly so):  OpenXML is the file-structure standard upon which modern MS Office documents are created.  ClosedXML is an open-source .NET code library (on GitHub) built to more easily implement that standard and allow .NET developers an abstraction against that standard for which they can more easily and efficiently code.  ClosedXML – probably not the best name for the library/project, but it is what was chosen.

A couple of things:

  • Error Handling:  If you generate a formula-string for a cell, and then ascribe the formula-string to the value of that cell, but somehow leave the style of the cell to .Number (because the formula will generate a number), like:
    • worksheet.Cell(rowCount, columnCount - 3).DataType = XLCellValues.Number;
  • …then ClosedXML will not be able to handle it and stack-overflow. Of course, the error handling library/code you implement should can handle this and let you know about it, but that’s not my point.
  • Reference Documentation / Intellisense:  It’s practically non-existent, except a few articles and lots of forum discussion on GitHub.  I find that plain inadequate, especially on the Intellisense side of things.

Another random note on ClosedXML:  If you put a formula-string into a cell, separate Excel function parameters (if applicable to your situation) using commas, not semi-colons (like would be standard in a normal Excel function call). You’ll thank me later. You’re welcome in advance.

So I am putting my money where my mouth is and starting to contribute to work on the ClosedXML library, because I use it fairly frequently and want to make it better, especially so I can use it with less frustration.

Is EPPlus (a competing OpenXML library for spreadsheets) any better on these fronts?  I used it a long time ago, but don’t know the progression of the library.  Start a discussion in the comments and let me know.