Re: Unicode strings for Excel 2003 workaround ?

[Govert van Drimmelen]

Hi Kristof,

Excel-DNA uses the native Excel C API, which did not support long or
unicode strings in versions before Excel 2007.
There are other ways of making UDFs for Excel that might not have this
limitation, but will frustrate you in other ways. You can make an
'Automation Add-In' (I described how to do that here:
http://www.codeproject.com/Articles/7753/Create-an-Automation-Add-In-for-Excel-using-NET,
before making Excel-DNA), or you can expose your .NET code as a COM
server and create VBA wrapper functions for your code.

Other workarounds have been suggested here, which mix COM access to
your cell into the Excel-DNA functions, to try to extract the strings.
I doubt you'll get to an acceptable solution in that way.

The only real solution is for the Excel C API to be upgraded to
support long and Unicode strings properly. Fortunately Microsoft
already did this six years ago!

Regards,
Govert


On Oct 1, 1:06 pm, Kristof <kristof.vanher...@gmail.com> wrote:
> Hi,
>
> I developed an Add-In using Excel-DNA which needs to be used by our
> suppliers.
> The install base is quite extensive (Win XP/Vista/7 with Excel 2003->2010).
>
> Everything works OK, except for suppliers which use e.g. Russian language
> using Excel2003.
> In the Add-In all I get is '????' iso the string because the string is in
> unicode.
> I've read that unicode strings are not supported by ExcelDNA icw Excel
> 2003, but are there any workarounds ?
> Can I use the native interface (Excel. ) or something else ? We can not
> demand that our suppliers upgrade their Excel installation :-(
>
> Thanks in advance,
> Kristof

[Naju Mancheril]

Hi Kristof,

If you need to write to a large, contiguous block of cells, COM will let you do this with one write operation (just select the Range and assign an object[,]) to it's Value2 property.

Also, remember to disable the calculation stuff before you write back. Otherwise, Excel will recalculate the sheet before letting you write more values. Here is an IDisposable class which let's you wrap that disable stuff in a using statement.


  // http://pyxr.sourceforge.net/PyXR/c/python24/lib/site-packages/win32/lib/winerror.py.html
  public static class ExcelCOMErrorCodes {
    public const uint RPC_E_CANTCALLOUT_INASYNCCALL=0x800AC472;
  }


  public class DisableAutoCalc : IDisposable {
    private readonly _Application app;
    private readonly XlCalculation? calc;
    private readonly bool? screenUpdates;

    public DisableAutoCalc(_Application app) {
      this.app=app;
      this.calc=app.Calculation;
      this.screenUpdates=app.ScreenUpdating;
      MultipleRetry<XlCalculation?>(() => app.Calculation=XlCalculation.xlCalculationManual, null);
      MultipleRetry<bool?>(() => app.ScreenUpdating=false, null);
    }

    protected void Dispose(bool disposing) {
      if(disposing && (app!=null)) {
        if(calc.HasValue) {
          app.Calculation=calc.Value;
        }
        if(screenUpdates.HasValue) {
          app.ScreenUpdating=screenUpdates.Value;
        }
      }
    }

    public void Dispose() {
      Dispose(true);
    }

    public void Close() {
      Dispose(true);
    }

    ~DisableAutoCalc() {
      Dispose(false);
    }

    private const int MaxNumComRetries=8;

    public static T MultipleRetry<T>(Func<T> func, T onError) {
      for(var i=0; i<MaxNumComRetries; i++) {
        try {
          return func();
          // the action succeeded--return
        } catch(COMException c) {
          if(c.ErrorCode!=ExcelCOMErrorCodes.RPC_E_CANTCALLOUT_INASYNCCALL) {
            throw;
          }
        }
      }
      return onError;
    }
  }