Cell text cannot be greater than 1024 chars

May 14, 2013 at 2:57 PM
Hi all,

I've written an app which pulls data from a web service and displays it in excel.

This part isn't important. It works great.

What does not work, is when the data I'm pulling is greater than 1024 chars. The data pulls from the web service just fine, but when I call ".SetValue(ft);" and the data is greater than 1024 chars I get an error.

In this example I have about 501 words which equates to somewhere around 8000~ characters. - If I truncate this to 1024 chars, the program works as expected.

Has anyone been able to get around the 1024 char limit?

BTW, I did upgrade my project to use ExcelDna-0.30. I'm not sure if this helps, but its an ExcelCommand and not a macro. This app required an Add-In with a Menu.

I hope we can do this w/out posting code. I think I've explained this well enough so others who have experienced this can help out.

Thanks in advance!
Coordinator
May 14, 2013 at 3:02 PM
Hi,

What version of Excel are you using?

-Govert
May 14, 2013 at 3:21 PM
Office 2010 - Specifically, Version 14.0.6129.5 32 Bit

Thanks for the swift reply!
Coordinator
May 14, 2013 at 4:13 PM
Hi,

Please try this code:
    public static class Class1
    {

        [ExcelCommand(MenuName = "Test", MenuText = "Put Long String")]
        public static void SetString()
        {
            try
            {
                var xlRef = new ExcelReference(1, 1, 1, 1);
                var str = new string('w', 1024) + "xyz";
                xlRef.SetValue(str);
            }
            catch (Exception ex)
            {
                Console.Beep();
                Debug.WriteLine(ex);
            }
        }
    }
Then click the menu, and notice that cell B2 has been set. In another cell, enter the formula "=LEN(B2)" to show the length of the string pasted.

I can increase this to
var str = new string('w', 8199) + "xyz";
for a string length of 8202 and still get no error.

I'm testing both under Excel 2013 and Excel 2010 version 14.0.4760.1000

I'm not sure why the limit of 8202 characters would be there for the xlSet C API call. UDFs that return a string seem to be fine up to the expected limit of 32767 characters. I note that 1/4 of 32767 = 8191.75. I doubt this limit has anything to do with Excel-DNA.

Can you try it and post back what you find?

Cheers,
Govert
May 14, 2013 at 5:31 PM
Your test worked great.

Sorry, I must have been confused by several other posts on the 1024, but there definitely is a limit and I'm hitting it with my program.

In your test, I set it to 8202 - all works as provided/expected.

When running your test and its set to 10,000 chars I receive this (same as what I receive in my production program):

Exception of type 'ExcelDna.Integration.XlCallException' was thrown.

ExcelDna.Integration
System.Collections.ListDictionaryInternal
at ExcelDna.Integration.XlCall.Excel(Int32 xlFunction, Object[] parameters)
at ExcelDna.Integration.ExcelReference.SetValue(Object value)
at ExcelFieldLengthTest.ExcelFieldLengthTest.SetString() in C:\Users\rjp\Documents\Visual Studio 2010\Projects\ExcelFieldLengthTest\ExcelFieldLengthTest\ExcelFieldLengthTest.cs:line 20

Am I limited to just truncation as a solution?
Coordinator
May 14, 2013 at 5:39 PM
Hi,

It looks like this limitation is not there when you use the COM Automation interface to talk to Excel. From inside an Excel-DNA macro you can get hold of the root Application object by calling ExcelDnaUtil.Application, then follow the object model from there. In C# 4 'dynamic' works well for this, or you can reference an interop assembly.

So you can try:
                dynamic xlApp = ExcelDnaUtil.Application;
                var range = xlApp.Range["B5"];
                range.Value = new string('w', 10000) + "xyz";
-Govert
May 14, 2013 at 7:44 PM
Edited May 14, 2013 at 7:46 PM
That definitely works in the test solution!

How would you go about applying it for this solution - where I'm using an object to overwrite the selected cells?
//create object to store webservice data
object[,] ft = new object[sObjectSchemaList.Count + 1, headers.Count()];
                    
//Start Excel
 ExcelReference exRefSel = new ExcelReference(exRefSel.RowFirst,
                               exRefSel.RowFirst + sObjectSchemaList.Count - 1,
                               exRefSel.ColumnFirst,
                               exRefSel.ColumnFirst + headers.Count() - 1
                               );

//removed code loop to set object values (headers & cell data) from web service

//Set cells
exRefSel.SetValue(ft);