Returning arrays from a COM Addin

So, you want to return an array from a COM addin, written in C#? Its easy. Just make your function have an object return type, and it will 'just work'

Converting from an array of string data to an array of excel data

This isn't pretty but works quite reliably

public class ExcelConvert
	{
		public int toExcelDate(int nDay, int nMonth, int nYear) {
			// Excel/Lotus 123 have a bug with 29-02-1900. 1900 is not a
			// leap year, but Excel/Lotus 123 think it is...
			if (nDay == 29 && nMonth == 02 && nYear==1900)
				return 60;

			// DMY to Modified Julian calculation with an extra substraction of 2415019.
			long nSerialDate = 	(int)(( 1461 * ( nYear + 4800 + (int)(( nMonth - 14 ) / 12) ) ) / 4) +
						(int)(( 367 * ( nMonth - 2 - 12 * ( ( nMonth - 14 ) / 12 ) ) ) / 12) -
						(int)(( 3 * ( (int)(( nYear + 4900 + (int)(( nMonth - 14 ) / 12) ) / 100) ) ) / 4) +
						nDay - 2415019 - 32075;

			if (nSerialDate < 60) {
				// Because of the 29-02-1900 bug, any serial date 
				// under 60 is one off... Compensate.
				nSerialDate--;
			}

			return (int)nSerialDate;
		}

		public object convertArray(object anarray) {
			object[,] ary = (object[,])anarray;
			
			int rows = ary.GetUpperBound(0) + 1;
			int cols = ary.GetUpperBound(1) + 1;

			for ( int row = 0 ; row < rows ; row++ ) {
				for ( int col = 0 ; col < cols ; col++ ) {
					object result;
					string value = (string) ary[row,col];
					try {
						result = Convert.ToDouble(value);
					}
					catch ( FormatException e ) {
						// Try Date 	
						try {
							DateTime dt = DateTime.Parse(value);
							result = toExcelDate(dt.Day, dt.Month, dt.Year);						
						}
						catch ( FormatException e2 ) {
							result = value;
						}
					}
				
					ary[row,col] = result;
				}
			}

			return ary;
		}
	}

The toExcelDate is from http://www.codeproject.com/datetime/exceldmy.asp?df=100&forumid=4548&exp=0&select=464292

Returning arrays from a COM Addin

Converting from an array of string data to an array of excel data

Browse Space

Explore Confluence

Your Account

Other Features

Add Content