"Object type cannot be converted to target type" error when trying to call SaveExcel dynamically

Posted by: kozu on 8 September 2017, 12:29 pm EST

  • Posted 8 September 2017, 12:29 pm EST

    Hi,
    I need to be able to save a CSV file to Excel by dynamically accessing the FarPoint Dlls.
    Here's a code snippet:
    try
                {
                    Assembly assembly = Assembly.LoadFrom(@"c:\program files\farpoint technologies\spread.winforms.v25\v2.5.1002\bin\farpoint.win.spread.dll");

                    object obj = assembly.CreateInstance("FarPoint.Win.Spread.FpSpread");
               
                    Type tp = assembly.GetType("FarPoint.Win.Spread.FpSpread");
                   
                    object[] parms = new string[]{"c:\\test.csv"};
                   
                    //open CSV
                    MethodInfo[] mi = tp.GetMethods();
                    foreach(MethodInfo mo in mi)
                    {
                        if (mo.Name.StartsWith("Open"))
                        {
                            ParameterInfo[] pas = mo.GetParameters();
                            mo.Invoke(obj, parms); //I know the first one gets a string
                            break;
                        }
                    }

                    //save XLS
                    foreach(MethodInfo ms in mi)
                    {
                        if (ms.Name.StartsWith("SaveExcel"))
                        {
                            ParameterInfo[] pas = ms.GetParameters();
                            if (pas.Length == 1)//I know the first one gets a Stream
                            {
                                StreamWriter writer = new StreamWriter("c:\\test.xls");
                                object[] save = new object[]{writer};
                                ms.Invoke(obj, save);
                                break;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    //display error
                }
    The exception I'm getting is "Object type cannot be converted to target type", when calling ms.Invoke(obj, save);
    Could somebody tell me what am I doing wrong?
    thanx
  • Replied 8 September 2017, 12:29 pm EST

    Ok. I finally got it to work.
    Here's the code, in case anybody needs something similar :-)
    One question though. I'd like to load the dll from the GAC instead of passing the full path, but
    calling

    Assembly assembly = Assembly.Load("FarPoint.Win.Spread, Version=2.5.1002.2002,Culture=neutral, PublicKeyToken=[327c3516b1b18457]");

    throws the following error:
    "File or assembly name FarPoint.Win.Spread, or one of its dependencies, was not found."
    Any ideas why?

    try
                {
                    Assembly assembly = Assembly.LoadFrom(@"c:\program files\farpoint technologies\spread.winforms.v25\v2.5.1002\bin\farpoint.win.spread.dll");

                    //create the FPSpread & SheetView objects
                    object spreadObject = assembly.CreateInstance("FarPoint.Win.Spread.FpSpread");
                    object sheetView = assembly.CreateInstance("FarPoint.Win.Spread.SheetView");
                    Type spreadType = assembly.GetType("FarPoint.Win.Spread.FpSpread");
                    Type sheetType = assembly.GetType("FarPoint.Win.Spread.SheetView");

                   
                    MethodInfo[] methods = sheetType.GetMethods();
                    MethodInfo openFile = null;
                    foreach (MethodInfo info in methods)
                    {
                        if (info.Name.ToUpper() == "LOADTEXTFILE")
                        {
                            ParameterInfo[] infoParams = info.GetParameters();
                            if (infoParams.Length == 6 && infoParams[0].ParameterType == typeof(string))
                                openFile = info;
                        }
                    }

                    if (openFile != null)
                    {
                        //I need to add a sheetview
                        MethodInfo getSheets = spreadType.GetMethod("get_Sheets");
                        if (getSheets != null)
                        {
                            object sheets = getSheets.Invoke(spreadObject, null);
                            Type collectionType = assembly.GetType("FarPoint.Win.Spread.SheetViewCollection");
                            MethodInfo add = collectionType.GetMethod("Add");
                            if (add != null)
                            {
                                add.Invoke(sheets, new object[]{sheetView});
                            }
                        }

                        //now try to open the file
                        Type includeHeaders = assembly.GetType("FarPoint.Win.Spread.Model.IncludeHeaders");
                        FieldInfo header =  includeHeaders.GetField("None");
                       
                        object[] loadParms = new object[]{"c:\\test.csv",true, header.GetValue(includeHeaders),"\r\n", ",", ""};
                        openFile.Invoke(sheetView, loadParms);
                    }

                   
                    //save to XLS
                    MethodInfo[] mi = spreadType.GetMethods();
                                   
                    foreach(MethodInfo ms in mi)
                    {
                        if (ms.Name.StartsWith("SaveExcel"))
                        {
                            ParameterInfo[] pas = ms.GetParameters();
                            if (pas.Length == 1 && pas[0].ParameterType == typeof(Stream))
                            {
                                FileStream writer = new FileStream("c:\\test.xls", FileMode.OpenOrCreate);
                                object[] save = new object[]{writer};
                                ms.Invoke(spreadObject, save);
                                writer.Close();
                                break;
                            }
                        }
                    }

                    MessageBox.Show("Done");
                }
                catch (Exception ex)
                {
                    //display error
                }
  • Replied 8 September 2017, 12:29 pm EST

    Hello,


    This code looks correct. I am not sure why it will not let you load the Assembly from the GAC.

  • Replied 8 September 2017, 12:29 pm EST

    Scott,
    Does the call
    Assembly assembly = Assembly.Load("FarPoint.Win.Spread,
    Version=2.5.1002.2002,Culture=neutral,
    PublicKeyToken=[327c3516b1b18457]");
    work for you?
  • Replied 8 September 2017, 12:29 pm EST

    Hello,


    Yes. You need to remove the brackets around the public key token and then the code will work.

  • Replied 8 September 2017, 12:29 pm EST

    Hello,


    I found one error and one question in your code. The question is what is the csv file? The Open method you are using is expecting to open a spreadsheet xml file. If the csv file is not in this format, then the Open method will fail.


    The error you are getting is due to the parameter, writer, you are passing into the Invoke method. This parameter is suppose to be a Stram and you are passing in a StreamWriter. You can use code like the following instead for the creation of the parameter.


    FileStream writer = new FileStream("c:\\dummy\\test.xls", FileMode.OpenOrCreate);


    Finally, I would sugget getting the latest maintenance release of Spread.

  • Replied 8 September 2017, 12:29 pm EST

    Hi Scott,
    You are right, I was using the wrong param for the call to SaveExcel. Now it's saving, but there's a problem with the save.
    My csv file looks something like this: 1,2,3
    But  after the save,  test.xls has 1,2,3 in the same column, as opposed to  3 columns.

    I get the same result even when I do this statically:
    FarPoint.Win.Spread.FpSpread spread = new FarPoint.Win.Spread.FpSpread();
    spread.Open("c:\\test.csv");
    spread.SaveExcel("c:\\test.xls");
    If the values in the CSV are tab separated it works fine. Is there a different API I should call if they are comma separated?
  • Replied 8 September 2017, 12:29 pm EST

    Hello,


    I am not sure how you are getting the Open method to work. It is used to load our Spreadsheet XML formatted data. If you just have a delimited text file, you should use the LoadTextFile method instead.

  • Replied 8 September 2017, 12:29 pm EST

    Scott,
    Thanks so much for you help! I got it working now.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels