Excel Late Binding EntireColumn.NumberFormat

Question

How to set the Range.EntireColumn.NumberFormat using the late binding? I am currently have this code:

 object rg = ws.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, ws, new object[2]{1,iCol});
 object ec = rg.GetType().InvokeMember("EntireColumn", BindingFlags.GetProperty, null, rg, null);
 rg.GetType().InvokeMember("NumberFormat", BindingFlags.SetProperty, null, rg, new object[1] { "DD/MM/YYYY" });

And also for the AutoFit method, I have this:

object rng = ws.GetType().InvokeMember("UsedRange", BindingFlags.GetProperty, null, ws, null);
object cols = rng.GetType().InvokeMember("Columns", BindingFlags.GetProperty, null, rng, null);
cols.GetType().InvokeMember("AutoFit", BindingFlags.InvokeMethod, null, cols, null);

for the AutoFit im not sure if I'm doing it well.

Hi seyren, welcome to SO! I'm from the VBA world and not c#, so this might not work, but give it a try nonetheless. If `ws` or `rg`are defined as Objects but are in fact a worksheet or a range object, you can simply access their methods/properties the same way as with early binding. Thus, try, `ws.Cells(...)`! — Peter Albert, Feb 01 '13 at 07:43
Hi Siddharth - thanks for the answer, ill try it now, my problem is the code just didn't work, it doesn't do what it was supposed to do but looking at your code i think i misunderstand using the latebinding i think its becuase it was my first time using it but thanks :) — seyren windsor, Feb 01 '13 at 07:51
found a solution for the numberFormat just using the code you post earlier so you can now return it, i just loop through each cell and one by one i set their format, the only remaining item is the autoFit Thanks @SiddharthRout !! — seyren windsor, Feb 01 '13 at 08:23

Siddharth Rout · Accepted Answer · 2013-02-01T08:36:17.633

Tried And Tested

private void button1_Click(object sender, EventArgs e)
{
    object xlApp;
    object xlWbCol;
    object xlWb;
    object xlSheet;
    object xlRange;
    object xlWsCol;

    //~~> create new Excel instance
    Type tp;
    tp = Type.GetTypeFromProgID("Excel.Application");
    xlApp = Activator.CreateInstance(tp);

    object[] parameter = new object[1];
    parameter[0] = true;
    xlApp.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, xlApp, parameter);
    xlApp.GetType().InvokeMember("UserControl", BindingFlags.SetProperty, null, xlApp, parameter);

    //~~> Get the xlWb collection
    xlWbCol = xlApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, xlApp, null);

    //~~> Create a new xlWb
    xlWb = xlWbCol.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, xlWbCol, null);

    //~~> Get the worksheet collection
    xlWsCol = xlWb.GetType().InvokeMember("WorkSheets", BindingFlags.GetProperty, null, xlApp, null);

    //~~> Create a new workxlSheet
    xlSheet = xlWb.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, xlWsCol, null);

    //~~> Assign cell to xlRange object
    xlRange = xlSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, xlSheet, new object[2] { 1, 1 });

    //~~> Write a date to cell 
    xlRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, xlRange, new object[] { "1-1-2012" });

    //~~> Get the column
    object cols = xlRange.GetType().InvokeMember("Columns", BindingFlags.GetProperty, null, xlRange, null);

    //~~> Autofit the column
    cols.GetType().InvokeMember("AutoFit", BindingFlags.InvokeMethod, null, cols, null);
    //~~> Format the entire Column
    cols.GetType().InvokeMember("NumberFormat", BindingFlags.SetProperty, null, cols, new object[1] { "DD/MM/YYYY" });

    //~~> Release the object
    //System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
}

Note: Add using System.Reflection; At the top

FOLLOWUP

For Autofit for usedrange this works

private void button1_Click(object sender, EventArgs e)
{
    object xlApp;
    object xlWbCol;
    object xlWb;
    object xlSheet;
    object xlRangeUsdRng;
    object xlRange;
    object xlWsCol;

    //~~> create new Excel instance
    Type tp;
    tp = Type.GetTypeFromProgID("Excel.Application");
    xlApp = Activator.CreateInstance(tp);

    object[] parameter = new object[1];
    parameter[0] = true;
    xlApp.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, xlApp, parameter);
    xlApp.GetType().InvokeMember("UserControl", BindingFlags.SetProperty, null, xlApp, parameter);

    //~~> Get the xlWb collection
    xlWbCol = xlApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, xlApp, null);

    //~~> Create a new xlWb
    xlWb = xlWbCol.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, xlWbCol, null);

    //~~> Get the worksheet collection
    xlWsCol = xlWb.GetType().InvokeMember("WorkSheets", BindingFlags.GetProperty, null, xlApp, null);

    //~~> Create a new workxlSheet
    xlSheet = xlWb.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, xlWsCol, null);

    //~~> Assign cell F5 to xlRange object for testing purpose
    xlRange = xlSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, xlSheet, new object[2] {5, 6 });
    xlRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, xlRange, new object[] { "1-1-2012" });

    //~~> Assign UsedRange
    xlRangeUsdRng = xlSheet.GetType().InvokeMember("UsedRange", BindingFlags.GetProperty, null, xlSheet, null);

    //~~> Get the Columns
    object cols = xlRangeUsdRng.GetType().InvokeMember("EntireColumn", BindingFlags.GetProperty, null, xlRangeUsdRng, null);

    //~~> Autofit
    cols.GetType().InvokeMember("AutoFit", BindingFlags.InvokeMethod, null, cols, null);

}

Excel Late Binding EntireColumn.NumberFormat

1 Answers1