Geek asks and answers

Excel Late Binding EntireColumn.NumberFormat

How to set Range.EntireColumn.NumberFormat using last binding? I 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 the following:

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 AutoFit im not sure if I am doing this well.

+5
source share
1 answer

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;To Top

Followup

For autofit for usedrangeit 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);

}
+4
source

More articles:


All Articles