"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,<br>I need to be able to save a CSV file to Excel by dynamically accessing the FarPoint Dlls.<br>Here's a code snippet:<br>try<br>            {<br>                Assembly assembly = Assembly.LoadFrom(@"c:\program files\farpoint technologies\spread.winforms.v25\v2.5.1002\bin\farpoint.win.spread.dll");<br><br>                object obj = assembly.CreateInstance("FarPoint.Win.Spread.FpSpread");<br>            <br>                Type tp = assembly.GetType("FarPoint.Win.Spread.FpSpread");<br>                <br>                object[] parms = new string[]{"c:\\test.csv"};<br>                <br>                //open CSV<br>                MethodInfo[] mi = tp.GetMethods();<br>                foreach(MethodInfo mo in mi)<br>                {<br>                    if (mo.Name.StartsWith("Open"))<br>                    {<br>                        ParameterInfo[] pas = mo.GetParameters();<br>                        mo.Invoke(obj, parms); //I know the first one gets a string<br>                        break;<br>                    }<br>                }<br><br>                //save XLS<br>                foreach(MethodInfo ms in mi)<br>                {<br>                    if (ms.Name.StartsWith("SaveExcel"))<br>                    {<br>                        ParameterInfo[] pas = ms.GetParameters();<br>                        if (pas.Length == 1)//I know the first one gets a Stream<br>                        {<br>                            StreamWriter writer = new StreamWriter("c:\\test.xls");<br>                            object[] save = new object[]{writer};<br>                            ms.Invoke(obj, save);<br>                            break;<br>                        }<br>                    }<br>                }<br>            }<br>            catch (Exception ex)<br>            {<br>                //display error<br>            }<br>The exception I'm getting is "Object type cannot be converted to target type", when calling ms.Invoke(obj, save);<br>Could somebody tell me what am I doing wrong?<br>thanx<br>
  • Replied 8 September 2017, 12:29 pm EST

    Ok. I finally got it to work.<br>Here's the code, in case anybody needs something similar :-)<br>One question though. I'd like to load the dll from the GAC instead of passing the full path, but<br>calling<br><br>Assembly assembly = Assembly.Load("FarPoint.Win.Spread, Version=2.5.1002.2002,Culture=neutral, PublicKeyToken=[327c3516b1b18457]");<br><br>throws the following error:<br>"File or assembly name FarPoint.Win.Spread, or one of its dependencies, was not found."<br>Any ideas why?<br><br>try<br>            {<br>                Assembly assembly = Assembly.LoadFrom(@"c:\program files\farpoint technologies\spread.winforms.v25\v2.5.1002\bin\farpoint.win.spread.dll");<br><br>                //create the FPSpread & SheetView objects<br>                object spreadObject = assembly.CreateInstance("FarPoint.Win.Spread.FpSpread");<br>                object sheetView = assembly.CreateInstance("FarPoint.Win.Spread.SheetView");<br>                Type spreadType = assembly.GetType("FarPoint.Win.Spread.FpSpread");<br>                Type sheetType = assembly.GetType("FarPoint.Win.Spread.SheetView");<br><br>                <br>                MethodInfo[] methods = sheetType.GetMethods();<br>                MethodInfo openFile = null;<br>                foreach (MethodInfo info in methods)<br>                {<br>                    if (info.Name.ToUpper() == "LOADTEXTFILE")<br>                    {<br>                        ParameterInfo[] infoParams = info.GetParameters();<br>                        if (infoParams.Length == 6 && infoParams[0].ParameterType == typeof(string))<br>                            openFile = info;<br>                    }<br>                }<br><br>                if (openFile != null)<br>                {<br>                    //I need to add a sheetview<br>                    MethodInfo getSheets = spreadType.GetMethod("get_Sheets");<br>                    if (getSheets != null)<br>                    {<br>                        object sheets = getSheets.Invoke(spreadObject, null);<br>                        Type collectionType = assembly.GetType("FarPoint.Win.Spread.SheetViewCollection");<br>                        MethodInfo add = collectionType.GetMethod("Add");<br>                        if (add != null)<br>                        {<br>                            add.Invoke(sheets, new object[]{sheetView});<br>                        }<br>                    }<br><br>                    //now try to open the file<br>                    Type includeHeaders = assembly.GetType("FarPoint.Win.Spread.Model.IncludeHeaders");<br>                    FieldInfo header =  includeHeaders.GetField("None");<br>                    <br>                    object[] loadParms = new object[]{"c:\\test.csv",true, header.GetValue(includeHeaders),"\r\n", ",", ""};<br>                    openFile.Invoke(sheetView, loadParms);<br>                }<br><br>                <br>                //save to XLS<br>                MethodInfo[] mi = spreadType.GetMethods();<br>                                <br>                foreach(MethodInfo ms in mi)<br>                {<br>                    if (ms.Name.StartsWith("SaveExcel"))<br>                    {<br>                        ParameterInfo[] pas = ms.GetParameters();<br>                        if (pas.Length == 1 && pas[0].ParameterType == typeof(Stream))<br>                        {<br>                            FileStream writer = new FileStream("c:\\test.xls", FileMode.OpenOrCreate);<br>                            object[] save = new object[]{writer};<br>                            ms.Invoke(spreadObject, save);<br>                            writer.Close();<br>                            break;<br>                        }<br>                    }<br>                }<br><br>                MessageBox.Show("Done");<br>            }<br>            catch (Exception ex)<br>            {<br>                //display error<br>            }<br>
  • Replied 8 September 2017, 12:29 pm EST

    <P>Hello,</P>
    <P>This code looks correct. I am not sure why it will not let you load the Assembly from the GAC.</P>
  • Replied 8 September 2017, 12:29 pm EST

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

    <P>Hello,</P>
    <P>Yes. You need to remove the brackets around the public key token and then the code will work.</P>
  • Replied 8 September 2017, 12:29 pm EST

    <P>Hello,</P>
    <P>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.</P>
    <P>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.</P><FONT size=2>
    <P></FONT><FONT color=#008080 size=2>FileStream</FONT><FONT size=2> writer = </FONT><FONT color=#0000ff size=2>new</FONT><FONT size=2> </FONT><FONT color=#008080 size=2>FileStream</FONT><FONT size=2>(</FONT><FONT color=#800000 size=2>"c:\\dummy\\test.xls"</FONT><FONT size=2>, </FONT><FONT color=#008080 size=2>FileMode</FONT><FONT size=2>.OpenOrCreate);</FONT></P>
    <P><FONT size=2><FONT size=3>Finally, I would sugget getting the latest maintenance release of Spread.</FONT></P></FONT>
  • Replied 8 September 2017, 12:29 pm EST

    Hi Scott,<br>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.<br>My csv file looks something like this: 1,2,3<br>But  after the save,  test.xls has 1,2,3 in the same column, as opposed to  3 columns.<br><br>I get the same result even when I do this statically:<br>FarPoint.Win.Spread.FpSpread spread = new FarPoint.Win.Spread.FpSpread();<br>spread.Open("c:\\test.csv");<br>spread.SaveExcel("c:\\test.xls"); <br>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?<br>
  • Replied 8 September 2017, 12:29 pm EST

    <P>Hello,</P>
    <P>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.</P>
  • Replied 8 September 2017, 12:29 pm EST

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

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

Learn More

Forum Channels