1

I have an excel file linked to my illustrator file using the Variable Importer script found here: https://github.com/Silly-V/Adobe-Illustrator/blob/master/Variable%20Importer/VariableImporter.jsx

My data is set up like this: a screenshot of part of my excel spreadsheet

Without splitting up the spreadsheet, I would like to achieve the following result using a bar graph in Illustrator: a screenshot of the graph in my illustrator file
My first idea was to try linking the variable HC_current within the Illustrator chart (so rather than showing "328" it would say "HC_current" and the data would cycle through). This did not work.

Has anyone come across this before? Any ideas on how to link text data to a chart in Illustrator? Your help is appreciated!

1

So here's another different way to create a data-driven single-bar graph inside of Illustrator. In summary, it uses an art structure of strategically-grouped text frames along with text-wrap and a monospace font, and added appearance effects to create a decimalized single-bar graph.

Here is what the structure looks like: Appearance view

Layer View: Layer View

Outline View: Outline View

Let me describe what's going on here.

Structure

This is a system composed of grouped text frames. The text frames are the exact same size, and in my example were a result of arbitrary choice in my font width of a single character which ended up making for an embarrassingly long text frame width.

The hierarchy is such that the textframe called "hundreds" is topmost in the stacking order and is also most deeply nested in the grouping; yet another containing group contains the afore-mentioned group, adding on the 'tens' textframe. Finally, the 'ones' textframe is contained in the outermost containing group (in my example called "Single Bar") with the hundreds and tens group just previously described. The reason for this setup is to enable the use of Text Wrap to nudge monospace characters rightward across their textframes - which are all the same width and stacked on top of each other in the system.

Text Wrap

This very old-school feature is seeing great resurgence in my experimentations. In this case, it's the backbone of how it works. The groups which contain the 'hundreds' text frame as well as the one which has the 'hunds wrap' group plus the 'tens' textframe both have Text Wrap applied to them via the Object > Text Wrap > Make menu item.

Now when text is entered into one of these frames which is not the 'ones' textframe, it should nudge over the text inside the text frames which are below it in the stacking order.

Since the three very wide textframes are positioned exactly on top of each other in their xy coordinate location, it can be very tedious to enter new text into all of them to preview the graph. That's why it would be necessary to make use of the variable data feature early on by importing a whole spreadsheet of text-states and then be able to cycle through the datasets inside Illustrator.

Text Width & Monospace Font

This technique uses a monospace font, which has the feature of having all its characters take up the same space and would serve as a helpful visual aid when constructing the specific data format to be used as the input for this project. The magic is all in how added text characters will grow the text by a factor of some amount and how much more readable a monospace font would make when writing data like "123456" to stand for number "6" rather than six of any one character such as "******".

Sample data

Here is the sample data I used for testing.

Since this example involves three decimal denominations, the hundreds, tens and ones, each of the text frames have their character's width set to the appropriate amount so as to reflect the relationship of one's given decimalization to another's. The width for the characters of the 'tens' text frame are to be 1/10 the width of the 'hundreds' textframe, etc.

Character Scaling

Appearance Styling

To create a solid bar appearance on top of the text, an added fill is present on all the groups as well as the 'ones' textframe which have the "Outline Object" and "Convert to Shape" effects placed on them with any added width or height so as to make sure all characters are concealed and the space between characters disappears.

The Outline Object effect will cause the fill of the characters inside the text frame to be recognized as 'having mass' by Illustrator and the characters in adjacent textframes are nudged by the mass of the characters rather than the bounding box of the text frame or other factors.

Textframe effects

This way it's possible to work within a completely relative measurement system without having to worry about precise numbers when doing the setup. In this example, I have created the containing visual area for the bar graph at a certain position, width and height, but created the variable decimalized fields from another arbitrary unit. This was not an issue because an added fill to the parent "Single Bar" group with a Transform effect scaled the relatively-sized bar to the landing area.

The Process

Process gif animation

If the text character widths are properly assigned and the entire structure is properly grouped & stacked, entering varying amount of (monospace) characters will make the text-based 'bars' grow and shrink from the left side.

The reason it's not simply just textframes on top of one another and grouping is required is due to Illustrator's awkward spacing issues with text-wrap when grouping isn't used. If they aren't grouped at all, or not in the same kind of hierarchy as in this example, Illustrator is bound to create some gap space in the textframes in either all or some situations, such as when a field is empty.

To avoid such issues, use the same layer structure as in the above screenshots.

Special Illustrator Issue

With this setup you will observe that it's possible to produce accurate data representation using the three decimalized textframes for any number 1-1000. However there's a catch when switching to a dataset that has both the hundreds and the ones fields in use.

It becomes apparent that upon switching, the 'ones' field gets sent all the way to the left side, as if the Text Wrap is no longer acting upon it. This is a way of Illustrator telling the eager variable-data user that it is still in control and has a mind of its own, and it doesn't much appreciate being hacked in this way to do the user's bidding.

To solve this, use a workaround to appease Illustrator by pushing more of its buttons - specifically you have to hide and show (in the Layers panel) the group called "hunds wrap". Once it gets hidden and shown, the faulty Text Wrap wherever it is, becomes restored.

Of course pushing extra buttons won't do when exporting a variable document set, so an Action will need to be created to select the "hunds wrap" group (via Attribute notes - these will need to be added to this group) and hide-then-show it. This should make the 'ones' text snap back to the so-called 'front of the line'.

  • +1 For the Lulz. At what point does ot become easier to just write a EPS file to do this for you? – joojaa Nov 21 '18 at 6:03
  • This solution, ponderous as it is, still is a native process - but even more insane are some other such projects: the art structure for variable re-patterning & re-coloring and action set for non-script conditional text-sizing procedure using only actions. I saw that although possible, it would be much easier + more beneficial to just learn & use javascript as effort to build this action set for the latter project turned out to be disproportionate! – Silly-V Nov 21 '18 at 8:41
  • The problem with javascript is that its an action, it does something and goes away. To update you need to redo it. But instead i prefer to use eps for this (its still native at its core illustrator is an eps compiler) because then atleast its clear that the code owns the geometry. Untill you release it. So if i change code or data then the shape changes, i dont manipulate some global state. So what illustrator would need is for javascript to own a live object. But for me eps does much the same. – joojaa Nov 21 '18 at 16:34
  • Anyway one could make some effort to make new effects with C. So being able to do an effect that pics first text from group and pass it as a variable to another effect. Combined with a scale, subpath, delete indexed object, color object based on text variable etc effect could do this in a very succit and efficient platform. But then 3D software allready do this. so then one would want full fledged nodes... – joojaa Nov 21 '18 at 16:38
  • Maybe this next year I'll break past the starting screen on my XCode, messing around with the SDK. – Silly-V Nov 21 '18 at 16:51
0

Sorry, the only way charts work, and as Adobe has now itself implemented the VariableImporter methodology on populating variable graphs, is to have all your charts in separate .txt files.

This may seem like a lot of work, but in modern spreadsheet programs there are usually macro/scripting capabilities which would aid in transforming your concise data into the expanded format needed for the Illustrator variable data procedure.

The setup effort for such a tool would pay off especially when similar projects are expected in the future.

In your case you may want to research or ask on extensibility forums for Excel regarding a routine to take each row of an active sheet and save it as a separate .txt file within some directory.

I myself am curious about such needs from users, as such users are rare and I'm interested in taking note of their workflow procedures and spreadsheet formats when it comes to the need of taking one sheet or Excel workbook and creating the separate graph states as individual text files. Originally I thought this would be mostly a case of people wanting to export all Excel graphs to .txt, but maybe it turns out that users are more interested in taking sections of one sheet to create a multitude of graphs.

  • I'm so amused that it's your script and the OP happened to come here to ask about it xD – WELZ Nov 20 '18 at 1:07
  • This is fascinating, I'm typing up a cool new answer which will make for a fun LinkedIn Article! XD – Silly-V Nov 20 '18 at 1:08
  • Uhoh! I tried to put the idea I had into real practice and it seems that it's a little more complicated and unstable than I first thought. I may come back to this when I get a better handle on this puzzle - but I was going to use auto-sized rotated single-character width area text to push other area texts to the right via text-wrap and have 3 decimal denomination text-boxes. – Silly-V Nov 20 '18 at 8:32
  • But using a slightly different technique, I figured it out! The problem is, it will take a while to document all this junk due to Illustrator's many text quirks. However the bulk of the problem is solved by not using auto-sized vertical text that is rotated, as I first tried - this leaves a funky block that skews all proportions - but by using simple horizontal text frames and a monospace font, along with text wrap. I'll post on this later! – Silly-V Nov 20 '18 at 8:35
  • @Silly-V thank you for your response. Given that you know the functionality of your own code, I will mark this as the correct answer. You're a stellar human! – Valkiri Nov 20 '18 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.