C# and Excel

[Bhavin Parekh]

Hi
I'm using C# (Framework 2.0) and trying to calling excel function using
below dummy code.
It works fine as long as array size of object type array is under 65500 but
as soon as i increase, it throws an error.

Code:
object[] obj = new object[65500];
long indx = 0;
while (indx<65500)
{
obj[indx] = indx;
indx++;

}
Microsoft.Office.Interop.Excel.Application xl = new
Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.WorksheetFunction wsf = xl.WorksheetFunction;
double result = wsf.Percentile(obj, 0.75);


When i increase the size e.g. 65600, it throws below run time error,
Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

Please anyone can help on this? Is there any workaround to resolve this
issue?
Thanks

 

[Scott M.]

Hi
I'm using C# (Framework 2.0) and trying to calling excel function using
below dummy code.
It works fine as long as array size of object type array is under 65500
but
as soon as i increase, it throws an error.

Code:
object[] obj = new object[65500];
long indx = 0;
while (indx<65500)
{
obj[indx] = indx;
indx++;

}
Microsoft.Office.Interop.Excel.Application xl = new
Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.WorksheetFunction wsf =
xl.WorksheetFunction;
double result = wsf.Percentile(obj, 0.75);


When i increase the size e.g. 65600, it throws below run time error,
Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

Please anyone can help on this? Is there any workaround to resolve this
issue?
Thanks

MS Excel has a limit of 65535 rows, and the percentile function works on a
range. It seems that it treats your array as a set of rows, which
violates the row limit.

Actually, it's 65,536 rows in Excel 2000, 02, and 03. It's 1,048,576 in
Excel 2007.

-Scott

 

[Jeff Johnson]

Actually, it's 65,536 rows in Excel 2000, 02, and 03. It's 1,048,576 in
Excel 2007.

Just for the sake of completeness, it was 65,536 rows long before Excel
2000. And while we're talking about the size increase in 2007, note that the
available columns increased from 256 to 1024.

 

[Scott M.]

Just for the sake of completeness, it was 65,536 rows long before Excel
2000. And while we're talking about the size increase in 2007, note that
the available columns increased from 256 to 1024.

I wasn't sure when Excel went from 16,384 to 65,536, but I did know it
happened before Excel 2000.

I did some checking and found that it went to 65,536 in Excel 97, which was
the immediate Windows predecessor to Excel 2000 - - not "long" before Excel
2000.

-Scott

 

[Jeff Johnson]

I wasn't sure when Excel went from 16,384 to 65,536, but I did know it
happened before Excel 2000.

I did some checking and found that it went to 65,536 in Excel 97, which
was the immediate Windows predecessor to Excel 2000 - - not "long" before
Excel 2000.

Hmmm, coulda sworn it was at least in 95.

 

[Scott M.]

Hmmm, coulda sworn it was at least in 95.

From: http://en.wikipedia.org/wiki/Microsoft_Excel

"Versions of Excel up to 7.0 had a limitation in the size of their data sets
of 16K (2^14=16384) rows. Versions 8.0 through 11.0 could handle 65K
(2^16=65536) rows and 256 columns (2^8 as label 'IV'). Version 12.0 can
handle 1M (2^20=1048576) rows, and 16384 (2^14 as label 'XFD') columns."

Version 8.0 was Excel 97 (Windows) and Excel 98 (Mac).

-Scott

 

[Jeff Johnson]

"Versions of Excel up to 7.0 had a limitation in the size of their data
sets of 16K (2^14=16384) rows. Versions 8.0 through 11.0 could handle 65K
(2^16=65536) rows and 256 columns (2^8 as label 'IV'). Version 12.0 can
handle 1M (2^20=1048576) rows, and 16384 (2^14 as label 'XFD') columns."

Wow, I wonder what made me think the columns went to 1024 when in fact they
went to 16,384. Maybe I'm mixing up Access and Excel. That's what I get for
working with Office 2007 for about a month before upgrading to 2003....

 

[Bhavin Parekh]

Thanks guys,
Your all information are very helpful to diagnose where the problem was.

 

[Harlan Messinger]

Just for the sake of completeness, it was 65,536 rows long before Excel
2000. And while we're talking about the size increase in 2007, note that the
available columns increased from 256 to 1024.

Not on my Excel 2007. Columns still go up only to IV (256), and if I
enter data in that final column, Excel won't then allow me to insert any
more columns.

 

[Scott M.]

Not on my Excel 2007. Columns still go up only to IV (256), and if I enter
data in that final column, Excel won't then allow me to insert any more
columns.

Then, you either don't have Excel 2007 or there is something wrong with your
installation. Excel 2007 should go out to column XFD as was stated.

How many rows do you have?

-Scott

 

[Harlan Messinger]

Then, you either don't have Excel 2007 or there is something wrong with your
installation. Excel 2007 should go out to column XFD as was stated.

How many rows do you have?

The old number, 16384. But, yes, it's Excel 2007, with the ribbon and
everything that goes with it, version 12.0.6504.5001 SP2, with Microsoft
Office version 12.0.6425.1000. And it was a new installation on a new
machine when I got it, not an upgrade from an earlier version. How strange.

 

[Jeff Johnson]

Not on my Excel 2007. Columns still go up only to IV (256), and if I enter
data in that final column, Excel won't then allow me to insert any more
columns.

In a new spreadsheet? Or could you have possibly opened an older spreadsheet
(version 2003 or prior)?

 

[Harlan Messinger]

The old number, 16384. But, yes, it's Excel 2007, with the ribbon and
everything that goes with it, version 12.0.6504.5001 SP2, with Microsoft
Office version 12.0.6425.1000. And it was a new installation on a new
machine when I got it, not an upgrade from an earlier version. How strange.

Never mind. I have Excel set to save documents as .xls by default, for
backwards compatibility.

 

[Scott M.]

Never mind. I have Excel set to save documents as .xls by default, for
backwards compatibility.

I've got my Office applications set to save in the 97-2003 format for
backwards compatibility and I still get the same size spreadsheet we've been
discussing.

-Scott