Create classes from worksheet

[tca]

Hi,

I'm looking for a way to create classes from a worksheet.

We have an extensive program for a large hospital project. We've created a list of classes we would like to use to structure that program in VW.

The list is currently in excel but could easily be pasted in a VW worksheet were a script could pick it up and generate classes with the names from the worksheet.

Unfortunately I'm not able to script this myself.

I've attached the VW worksheet.

any ideas?

[michaelk]

This should do it.

Open the worksheet. Select the range of cells you want to use to create classes. Run the script.

I'll also attach the file in 2015 and 2016.

hth

mk

Procedure CreateClasssesFromWorksheet;
{Badly scripted by Michael Klaers}
{Open a worksheet.  Select a cell or range of cells.  Run this script.}
{This script will create a class using the contents of the cells as names.}

VAR

WSHand		: Handle; 				{Handle to Worksheet}

topRow,leftColumn,bottomRow,rightColumn  :INTEGER;

currentCellRow,currentCellColumn,topRangeRow,leftRangeColumn,topRangeSubrow,bottomRangeRow,rightRangeColumn,bottomRangeSubrow   :INTEGER;

LoopColumn,LoopRow	:INTEGER;

ClassNameinWorksheet  :STRING;

BEGIN
WSHand:=GetTopVisibleWS;

GetWSSelection(WSHand,currentCellRow,currentCellColumn,topRangeRow,leftRangeColumn,topRangeSubrow,bottomRangeRow,rightRangeColumn,bottomRangeSubrow);


For    LoopColumn := leftRangeColumn TO rightRangeColumn
DO	BEGIN

		FOR LoopRow :=	topRangeRow TO bottomRangeRow
		DO	BEGIN


			GetWSCellString(WSHand,LoopRow,LoopColumn,ClassNameinWorksheet);
			If ClassNameinWorksheet <> ''
			THEN
			NameClass(ClassNameinWorksheet);

			END;
	END;

END;


RUN(CreateClasssesFromWorksheet);

 

classes_v2015.vwx

classes_v2016.vwx

[tca]

wow great! Thanks for the effort!

[michaelk]

It was mostly luck :-)

I'm a pretty bad scripter, but I already had a script that queried every selected cell in a worksheet. From there it only takes 2 lines of script to get the text from the cell and create a class.

Wish they were all that easy.

I'm glad it works for what you need.

mk

[Matteo New]

is there any possibility to add the description that way, too?

[michaelk]

This script should do it.

 

Put the class names you want in column A.  Put the descriptions in column B.  

 

Leave the worksheet open.  Run the script.

 

PROCEDURE CreateClasssesFromWorksheet;
{Badly scripted by Michael Klaers}
{Open a worksheet.  Run this script.}
{This script will create a class using the contents of the cells in Column A as names.}
{The contents of Column B will be the desciption for the class in Column A of the same row.}


VAR
	
	WSHand,ClassHandle									:HANDLE; 				

	LoopRow												:INTEGER;

	ClassNameinWorksheet,ClassDescriptioninWorksheet	:STRING;
	
	BResult, IsThisACell								:BOOLEAN;

BEGIN
	WSHand:=GetTopVisibleWS;
		
		IsThisACell := TRUE;
		LoopRow := 1;
            
			WHILE (IsThisACell) 
			DO	BEGIN
				
				
				GetWSCellString(WSHand,LoopRow,1,ClassNameinWorksheet);
				GetWSCellString(WSHand,LoopRow,2,ClassDescriptioninWorksheet);
				If ClassNameinWorksheet <> ''
					THEN
					NameClass(ClassNameinWorksheet);
					ClassHandle := GetObject(ClassNameinWorksheet);
					BResult := SetDescriptionText(ClassHandle, ClassDescriptioninWorksheet);
					IsThisACell:=IsValidWSCell(WSHand,LoopRow + 1,1);
					LoopRow := LoopRow + 1;
				
				END;
		
	
END;


RUN(CreateClasssesFromWorksheet);

 

 

[Matteo New]

wow, great. that definitely does help!!! thank you!

[RubenH]

Hello, 

Fantastic thread!

Is there a way to assign the attributes at creation in the methods described above. All from a table?

 

Any clue is welcomed.

 

[michaelk]

I haven't tried it, but I wonder if it would be possible to make the worksheet entries for the class attributes human readable.  Perhaps.

 

Check out this thread:  

 

 

At the end of the thread is a script that uses the graphic attributes of a selected object to create a class with the objects attributes and one to edit the graphic attributes of a class.

[loic.jourdan]

This has been really helpful so far - is it possible to, instead of editing the description, edit the tags so it shows in my class organisation pop up window. 

 

I've tried to edit the script myself but can't find the VS page to tell me the function name (I tried using "BResult := SetTagsText(ClassHandle, ClassTagsinWorksheet);" but had no luck).

[michaelk]

Great question.  

 

I'll have to play around with it.  

 

It is probably something like Result := SetObjectTags(ClassHandle, __________); where the blank is an array (?).

 

I would probably set it up with column C being the tags separated by semicolons.

 

I don't have time this week to work on it, but in a few hours someone else might have it working 🙂 .

[Pat Stanford]

All of the tags must be read and set at the same time.

 

GetObjectTags will get an array of all the existing tags.

Add the tag you want into the array and then use SetObjectTags to store it to the class.

 

The following script adds a tag named "My New Tag" to every Class in the file.  Note the extra step to clear the tag array between each class.

 

Procedure Test;
CONST	MaxTags=10;

VAR		H1:			Handle;
		L1, L2, L3:	LongInt;
		N1, N2:		Integer;
		A1:			DynArray[] of String;
		B1:			Boolean;
		S1:			String;
				
Begin
	Allocate A1[1..MaxTags];
	L1:=ClassNum;
	For L2:=1 to L1 DO
		BEGIN
			S1:=ClassList(L2);
			B1:=GetObjectTags(GetObject(S1), A1);
			N1:=1;
			While A1[N1]<>'' DO N1:=N1+1;
			A1[N1]:='My New Tag';
			B1:=SetObjectTags(GetObject(S1), A1);
			For L3:=1 to MaxTags DO A1[L3]:='';
		End;
End;

Run(Test);

 

[loic.jourdan]

Thanks @michaelk and @Pat Stanford for you speedy replies! 

 

Pat, I've given the script you wrote a go and it works perfectly for renaming the class tags to a specific string (My classes now all have the "My New Tag" tag attached), but is it possible to then, using what Michael has suggested, to use data from my worksheet to set that tag? I tried to use Michaels advice of setting up a column with my desired tags, each with a semicolon at the end and then adding it into your script so that it can take my tags and apply them instead of the default one you created but I'm not having any luck with it, I will try again in the morning but is there any easy way of adding the ability to take data from my worksheet into your script? I will try again in the morning, but I think I'm a little out of my depth!

 

Thanks again for your help so far! 

[Pat Stanford]

How are you entering the data into the worksheet?  Is this using a Record.Field value in a database section? Or are you manually typing the class name and desired tags into spreadsheet cells?

 

Either will work, but it will take a very different script to get the data from the two different versions.

 

Maybe best if you post a file with your worksheet so we can take a look.

[loic.jourdan]

 

Hi Pat, 

 

I have attached a blank file below with my worksheet in it. The excel file was provided to me by one of our ecologists and I have imported it into VW as a worksheet. 

 

For context, column A is a code (ecoha1, ecoha2... = Ecological Habitat Area 1 / ecol1, ecohl2... = Ecological Habitat Line 1) and column B is that codes actual name. The reason we have to use these codes is due to our GIS software (which was developed internally) isn't very good at recognising long class/layer names, using this coding system allows us to avoid any importing issues when taking our file from VW to our GIS software. 

 

I would like to create a class for each code with the actual name as the tag so it shows in my class drop down menu to avoid confusion later. 

 

Hope that makes sense! BNG Class Naming File.vwx

[Pat Stanford]

OK, Try this script.  Since you are creating the Class, you know there are not any tags already so all we have to do is add the full name as a tag.

 

Open the worksheet for editing and run the script. Then check the Classes pane of the Organization dialog.

 

PROCEDURE CreateClasssesFromWorksheet;
{Badly scripted by Michael Klaers}
{Open a worksheet.  Run this script.}
{This script will create a class using the contents of the cells in Column A as names.}
{The contents of Column B will be the desciption for the class in Column A of the same row.}

{Poorly modified by Pat Stanford 4-9-24 to add tag instead of description}

VAR
	
	WSHand,ClassHandle									:HANDLE; 				

	LoopRow												:INTEGER;

	ClassNameinWorksheet,ClassDescriptioninWorksheet	:STRING;
	
	BResult, IsThisACell								:BOOLEAN;
	
	A1													:Array[1..1] of String; {Added PS}

BEGIN
	WSHand:=GetTopVisibleWS;
		
		IsThisACell := TRUE;
		LoopRow := 1;
            
			WHILE (IsThisACell) 
			DO	BEGIN
				
				
				GetWSCellString(WSHand,LoopRow,1,ClassNameinWorksheet);
				GetWSCellString(WSHand,LoopRow,2,ClassDescriptioninWorksheet);
				If ClassNameinWorksheet <> ''
					THEN
					NameClass(ClassNameinWorksheet);
					ClassHandle := GetObject(ClassNameinWorksheet);
					A1[1]:=ClassDescriptioninWorksheet;			{Added PS}
				{	BResult := SetDescriptionText(ClassHandle, ClassDescriptioninWorksheet); } {Removed PS}
					BResult := SetObjectTags(ClassHandle, A1); {Added PS}
					IsThisACell:=IsValidWSCell(WSHand,LoopRow + 1,1);
					LoopRow := LoopRow + 1;
				
				END;
		
	
END;


RUN(CreateClasssesFromWorksheet);

 

[loic.jourdan]

Perfect! 

 

Thanks @Pat Stanford that is so helpful, thanks also to @michaelk for your contributions to this script, I will likely be using both at different points in the future 🙂