Excel data to construct illustrator object

[Willis77019b]

Problem:

How to read real number data from selected columns in an Excel spreadsheet into an illustrator javascript array variable, so that then I can use this information to construct an illustrator polygon. 

Further information:

I am new to javascript (and not much of a programmer in the best case, but do have some basic knowledge using ancient languages like FORTRAN 88). I am sure I am just missing an important step here despite all morning browsing with Google for answers (something to do with variable definition I assume?). I have some survey data defining the outline of features in map view listed in a string of point (x,y) values in adjacent columns in excel (these x,y point values calculated from GPS data using a commercial program to converted 3D position to a 2D x,y by geoid projection, and then in excel to re-scale point locations to illustrator’s artboard).  What I am trying to do is select columns of x,y value pairs in an excel spreadsheet, copy these values to the clipboard, and then develop an IA JavaScript to draw a polygon through the specified set of x,y points. It seems easy enough to script illustrator to draw the polygon once I get the values from the clipboard are in a javascript array (lots a good tips here http://scriptographer.org/tutorials/), but pulling in the clipboard data seems to be beyond me. I have tried populating a defined array with various combinations of array declarations and app.paste(); or windows.clipboardData.getData('text/plain'); with no satisfaction. I then had the idea to try to pull in the clipboard to one long continuous string, place that in a text box (see see its structure), andthen read and split up the text in a loop to populate the JavaScript array…but that deposited a complex illustrator table object into my text box, and several attempts to force the clipboard to paste text did not work (again probably having problems understanding variable type declaration).

Thanks very much for any suggests….and to be clear, I would be happy to find any method of scripting the pull of the selected on an excel spreadsheet for use in an Illustrator script…so if I am making this all harder than it needs, be I would be grateful to know that also!

[clrx01]

You can open Excel documents via the XML object. Excel docs are really just xml. If you rename a workbook.xlsx file to workbook.zip and unzip you will see all the worksheets as xml files. Open them up in your favorite editor and you will see the structure of the file.

http://msdn.microsoft.com/en-us/library/hh180830(v=office.14).aspx

Also now that you know excel = xml, revise your google searches and look for how to bring in xml and how to access elements within the tree and you should be well on your way. If none of the expert scripters around here respond by tomorrow, I'll see if I can put a basic script that will get you started, but for now I need bed!

[John Hawkinson]

I would tend to suggest you just export the relevant piece of the Excel workbook as a CSV file and read and open the CSV file in JavaScript. Clipboard is a pain, and digging inside Excel XML is even more of a pain.

[pixxxelschubser]

@Willis77019b,

At first you need a CSV-File or a tab-separated TXT (like in the follow example)

Now you read this xyCoords.text and create your shape:

// ShapeFromExcelSheet.jsx
// regards pixxelschubser
var aFile = File ("~/Desktop/TestFolder/xyCoords.txt");
aFile.open("r");
var coordFile = aFile.read();
aFile.close();
var xyList = coordFile.split("\n");
var coords = new Array (xyList.length-1);
for (i=0; i<xyList.length-1; i++) {
    xy = xyList.split( "\t");
    xy[0] = Number(xy[0]);
    xy[1] = Number(xy[1]);
    coords = xy;
}
// the following code based on:
// TrapezPathCreate.jsx
// http://forums.adobe.com/thread/1307731
var aDoc = app.activeDocument;
var aPath = aDoc.pathItems.add();
aPath.name = "Trapez";
//aPath.setEntirePath(Array([0,0],[50,100],[200,100],[400,0],[0,0])); //       this was the original code
aPath.setEntirePath(coords);
aPath.close = true;
aPath.filled= true;
aPath.stroked= false;
var itemColor = new CMYKColor();
itemColor.cyan = 0;
itemColor.magenta = 100;
itemColor.yellow = 100;
itemColor.black = 0;
aPath.fillColor = itemColor;
redraw();

Note that coordinates are used differently in the different versions of Illustrator.

Have fun.

[Willis77019b]

Many thanks for the input pixxxel schubser. Following your lead I did get it to paste, but only by an indirect copy route. Anyone else interested in this, my work flow was to transform map coordinates to illustrator coordinates in excel (a simple ratio calculation), copy from excel to notepad (to remove all but text), then copy from notepad and run the modified version of the script below. Copying it twice is an extra step, but at least it saves the time sorting out the files for each line segment.

var aDoc = app.activeDocument;

paste();

var coordpaste =app.activeDocument.selection[0];

var coordtext = coordpaste.contents;

var xyList = coordtext.split("\r");

var coords = new Array (xyList.length-1);for (i=0; i<xyList.length-1; i++) {

    xy = xyList.split( "\t");

    xy[0] = Number(xy[0]);

    xy[1] = Number(xy[1]);

    coords = xy;}

var aPath = aDoc.pathItems.add();

aPath.setEntirePath(coords);

aPath.stroked= true;

aPath.close = false;

aPath.filled= false;

coordpaste.remove();

redraw();

[yasinkkhan]

THANK YOU !!! Totally solved a different excel related issue for me. I was trying to copy a whole bunch of rows of data (single column) into a single text box in illustrator, instead of many multiple ones. Copied from excel to text editor, then to illustrator and got it exactly how I wanted.