Ribbon and MenuBar: different behavior of NumberFormat in Office International (non-UK)

Jan 22, 2013 at 11:10 AM

Hi,

 

During the development of a small my application I have noticed that I obtained a Exception in some cases during use of the NumberFormat property of a cell that contains a date.


So, I wrote a short example ".DNA" that replicates the error (whether it 00:29 with 0.30).The strange behavior is replicable even when the same function is invoked that uses NumberFormat from MenuBar and / or by a button of the Ribbon.


I can not find a way to figure out who is the caller of the function in order to adjust the setting of the NumberFormat so that everything will work properly.

Note that I'm using Excel 2007 which has a user interface in Italian.

Please try the following Code using an Excel Installed with a UILanguage different from UK or US.

 

<DnaLibrary RuntimeVersion="v4.0" Name="Ribbon Tests International" Language="C#">
<Reference AssemblyPath="System.Windows.Forms.dll" />

<![CDATA[

using System;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using ExcelDna.Integration.CustomUI;
using ExcelDna.Integration;

[ComVisible(true)]
public class MyRibbon : ExcelRibbon
{
	public void OnButtonPressed(IRibbonControl control)
	{
		//MessageBox.Show("Hello from control " + control.Id);
		MyAddIn.MacroMyTest("From_Ribbon");
	}
}

public class MyAddIn
{
	public static string ExcelVersion()
	{
		dynamic xlApp = ExcelDnaUtil.Application;
        return xlApp.Version;
	}

    [ExcelCommand(MenuName="Test MenuBar C#4", MenuText="Create Worksheet")]
	public static void CreateWorksheet()
	{
		MacroMyTest("From_MenuBar");
	}
	
	public enum MsoAppLanguageID
    {
        msoLanguageIDInstall = 1,
        msoLanguageIDUI = 2,
        msoLanguageIDHelp = 3,
        msoLanguageIDExeMode = 4,
        msoLanguageIDUIPrevious = 5,
    }
	
	//NOTE: The following function behaves differently in Excel depending on the caller: MenuBar or RibbonButton. (BUG?!?)
	public static void MacroMyTest(String fromWho)
	{
		int testNum = 0;
		try {
			dynamic xlApp = ExcelDnaUtil.Application;	
			
			System.Globalization.CultureInfo nci =
				new System.Globalization.CultureInfo(
					xlApp.LanguageSettings.LanguageID[MsoAppLanguageID.msoLanguageIDUI]);
			
		    dynamic wB = xlApp.Workbooks.Add();
			dynamic sheet2 = wB.Worksheets(1);
			dynamic newSheet = wB.Worksheets.Add(After: sheet2);
			newSheet.Name = "TEST Sheet_"+fromWho;
			dynamic range = newSheet.Range("A1:B4");
			
			range.Value = new object[,] {{1, "Hello"}, {2, fromWho}, {3, nci.DisplayName}, {4, nci.DateTimeFormat.ShortDatePattern}};
			DateTime myNow = DateTime.Now;
			//Double myValue = -1987.98765;
			
			testNum++;
			newSheet.Cells(1, 5).Value = "As Default";
			newSheet.Cells(2, 5).Value = myNow;
			newSheet.Cells(3, 5).Value2 = myNow;
			//newSheet.Cells(4, 5).Value = myValue;
			//newSheet.Cells(5, 5).Value2 = myValue;
			
			MySingleTest(newSheet, ++testNum, 6, myNow, "dd/mm/yyyy");
			MySingleTest(newSheet, ++testNum, 7, myNow, "dd-mm-yyyy");
			MySingleTest(newSheet, ++testNum, 8, myNow, "gg-mm-aaaa"); // For Italian Office UILanguage (1040)
			MySingleTest(newSheet, ++testNum, 9, myNow, "gg/mm/aaaa"); // For Italian Office UILanguage (1040)
			
			// If you see this in the new sheet, then everything is OK!
			newSheet.Cells(10, 2).Value2 = "End of Work!";
		} catch(Exception ex) {
			MessageBox.Show(string.Format("ERROR on Test {0}: {1}", testNum,ex.Message), "ERROR!");
		}
	}
	
	public static void MySingleTest(dynamic ws, int testNum, int column, DateTime myDate, string dateFormat)
	{
		//dynamic app = ExcelDnaUtil.Application;
		//dynamic wb = app.ActiveWorkbook;
		//dynamic ws = app.ActiveSheet;
		try 
		{		
			ws.Cells(1, column).Value = dateFormat;
			ws.Cells(2, column).Value = myDate;
			ws.Cells(3, column).Value2 = myDate;
			ws.Cells(2, column).Interior.Color = 0x0000ff;
			ws.Cells(3, column).Interior.Color = 0x0000ff;
			ws.Cells(2, column).NumberFormat = dateFormat;
			ws.Cells(2, column).Interior.Color = 0x00f000;
			ws.Cells(3, column).NumberFormat = dateFormat;
			ws.Cells(3, column).Interior.Color = 0x00f000;		
		} 
		catch(Exception ex) 
		{
			MessageBox.Show(string.Format("ERROR on Test {0}: {1}", testNum, ex.Message), "ERROR!");
		}
	}
}

]]>
  <!-- Some images that can be used in the Ribbon ui -->
  <Image Name="M" Path="M.png" Pack="true" />
  
  <!-- Below are three CustomUI elements used for different Excel versions.
       Need not have all three - ExcelDna whatever is most appropriate.
  -->
  <CustomUI>
    <!-- Inside here is the exact RibbonX xml passed to Excel -->
    <!-- This will only be loaded for Excel 2010 because of the namespace -->
    <customUI xmlns='http://schemas.microsoft.com/office/2009/07/customui' loadImage='LoadImage'>
      <ribbon>
        <tabs>
          <tab id='CustomTab' label='My 2010 Tab'>
            <group id='SampleGroup' label='Test on 2010'>              
              <button id='Button2' label='Test 2' imageMso="GetExternalDataFromWeb" size='large' onAction='OnButtonPressed'/>
            </group >
          </tab>
        </tabs>
      </ribbon>
    </customUI>
  </CustomUI>
  
  <CustomUI>
    <!-- Inside here is the RibbonX xml passed to Excel -->
    <!-- This can also be loaded by Excel 2007 because of the namespace -->
    <customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' loadImage='LoadImage'>
      <ribbon>
        <tabs>
          <tab id='CustomTab' label='My Tab'>
            <group id='SampleGroup' label='Test on 2007'>             
              <button id='Button2' label='Test 02' imageMso="GetExternalDataFromWeb" size='large' onAction='OnButtonPressed'/>
            </group >
          </tab>
        </tabs>
      </ribbon>
    </customUI>
  </CustomUI>
</DnaLibrary>

On my system configuration I obtain the following result images:

http://i1303.photobucket.com/albums/ag153/lucalandi/ExcelDNA_testIntl001_zps2b2a6fb0.jpg

http://i1303.photobucket.com/albums/ag153/lucalandi/ExcelDNA_testIntl002_zpsceafc3b6.jpg

 

Please Help Me to Find a workaround on this problem.

 

Thanks,

Luca Landi