Forum

C# MS Excel

Hello,

I’m trying to code a node to read datas from an excel workbook.
Make some trials in Visual Studio : no problem to have an array of values in string format.

The same code in a dynamic template give me more headaches.

The conversion of the object values doesn’t work the same way.

in Visual “Cells.Value.ToString()” is ok but not in vvvv.

It seems also that the quit command doesn’t work in vvvv.

Thank you if you take a look to my code and sorry for the heresy in it : I’m a real beginner.

- region usings
using System;
using System.ComponentModel.Composition;
using System.Linq;
using System.Collections;
using System.Threading.Tasks;

using MsXl = Microsoft.Office.Interop.Excel;

using VVVV.PluginInterfaces.V1;
using VVVV.PluginInterfaces.V2;
using VVVV.Utils.VColor;
using VVVV.Utils.VMath;

using VVVV.Core.Logging;
- endregion usings




namespace VVVV.Nodes
{
	#region PluginInfo
	[PluginInfo(Name = "ExcelReader1", Category = "Value", Help = "I read your fuckin XLS file", Tags = "")](PluginInfo(Name = "ExcelReader1", Category = "Value", Help = "I read your fuckin XLS file", Tags = ""))
	#endregion PluginInfo
	
	
	
	
	public class ValueExcelReader1Node : IPluginEvaluate
	{
		#region fields & pins
		
		[Input("workbook",StringType = StringType.Filename ,IsSingle = true)](Input("workbook",StringType = StringType.Filename ,IsSingle = true))
		public ISpread<string> xlfile;
		
		[Input("Sheet", DefaultValue = 1.0)](Input("Sheet", DefaultValue = 1.0))
		public ISpread<int> xlsheet;
		
		[Input("Row", DefaultValue = 1.0)](Input("Row", DefaultValue = 1.0))
		public ISpread<int> row;
		
		[Input("Clmn", DefaultValue = 1.0)](Input("Clmn", DefaultValue = 1.0))
		public ISpread<int> clmn;
		
		[Input("Read", DefaultValue = 0)](Input("Read", DefaultValue = 0))
		public ISpread<bool> xlread;

		[Output("Output")](Output("Output"))
		public ISpread<string> FOutput;

		[Import()](Import())
		public ILogger FLogger;
		#endregion fields & pins
	
		
			
		//called when data for any output pin is requested
		public void Evaluate(int SpreadMax)
		{
			FOutput.SliceCount = SpreadMax;
			string filename = @xlfile[0](0);
			
			
			if(xlread[0](0))
			{
			MsXl.Application apxl = new MsXl.Application();
            MsXl.Workbook xlwb = apxl.Workbooks.Open(filename, ReadOnly: true);
           	MsXl.Worksheet xlws = (MsXl.Worksheet) xlwb.ActiveSheet;
			
			object[]() FromXl = new object[SpreadMax](SpreadMax);
			
            for (int i = 0; i < SpreadMax; i++)
            {
            	FromXl[i](i) = xlws.Cells[row[i](row[i), clmn[i](i)];
            	
                if (FromXl[i](i).Value2 == null)
                {
                    FOutput[i](i) = null;                    
                }
                else
                {
                    FOutput[i](i) = FromXl[i](i).Value.ToString();
                	 
                }
            }
            xlwb.Close();                      
            apxl.Quit();            
			}
						
			//FLogger.Log(LogType.Debug, "hi tty!");
		}
		
		
	}
}

the code looks pretty complete… i have no idea why it should not work.

i would open the MsXl.Application and MsXl.Workbook on read and store them as a field of the node class.

are there any errors displayed? also open a Renderer (TTY) to see whether there are runtime errors.

Hello,
Thanks for your answer.

Yes there is an error saying the object Cells1,1 doesn’t accept the .Value or .Value2.
… but it works in Visual Studio

I’ve found another way :dll assembly with this code and just calling it from vvvv.

I’ve posted this new solution in the contributions with all codes and explanations. If someone want to check it or upgrade it.

you defined

object[]() FromXl

which is an object array. so you have to write

[Cell)FromXl[i](i](https://vvvv.org/documentation/Cell)FromXl[i](i).Value.ToString()

or whatever type it is…

or:

Cell[]() FromXl = new Cell[SpreadMax](SpreadMax);

thank you… no text …

Check this one…C# Excel Tutorial

http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

William

Thank you

I’ve made this, few weeks ago : excel-reader