problems with inserting the correct charts from chartex

[unixguy]

I create a workbook with five sheets. Each sheet has two columns and
an embedded chart. Each chart has a different title, so I can tell
that the correct chart is being inserted into each sheet. But some of
the charts are displaying data from one of the other sheets. I can see
this when I look under chart->source data->series - the values are
coming from another sheet.

It's almost like chartex was lying to me when it told me which chart
was in which bin file.

I'm stuck with version 2.20 of WriteExcel due to this windows
environment.

Are there any known issues that could cause this problem? I noticed
that chartex spit out bogus store_formula statements that looked like
this: $worksheet->store_formula('='Disk Usage'!A1');
Is it confused by the spaces in the sheet names?

[jmcnamara]

On May 13, 1:43 am, unixguy <unix...@fastmail.fm> wrote:
> I create a workbook with five sheets. Each sheet has two columns and
> an embedded chart. Each chart has a different title, so I can tell
> that the correct chart is being inserted into each sheet. But some of
> the charts are displaying data from one of the other sheets.

Hi,

I don't have time for a detailed answer at the moment so here are a
few thoughts.

This might happen if the worksheets/charts in the template file were
rearranged/reordered after they were created.

> I'm stuck with version 2.20 of WriteExcel due to this windows
> environment.

Version 2.21 is available for Windows but that won't help you with
this problem.

>
> Are there any known issues that could cause this problem?

> I noticed
> that chartex spit out bogus store_formula statements that looked like
> this: $worksheet->store_formula('='Disk Usage'!A1');
> Is it confused by the spaces in the sheet names?

It isn't confused by the spaces in the names and that store_formula()
hack generally isn't necessary anyway for embedded charts. However,
that it is a bug (unrelated to your problem) that chartex doesn't emit
something like the following in this case:

$worksheet->store_formula(q{='Disk Usage'!A1});

So in short, try create your template workbook again but maintain the
sheets in the order that they are created in and see if that helps.

John.
--

[unixguy]

Interesting... When I created the workbook, I had sheets created by
an application.
I used drag and drop to put them in my template spreadsheet but found
they were still
referencing the other workbook. I corrected them, but, apparently,
that messed
them up somehow. The charts are a bit complicated as far as their
formatting
is concerned, so I hope I don't have to create them from scratch and
can copy
them, somehow, from the originals. I'll give it another try.

[unixguy]

ok, this is definitely the issue. I tried again, but am still having
trouble getting the correct chart inserted in the proper place.
Would it work better if I put each chart into a seperate
workbook and extracted them each from there? I could
make copies of the workbook and delete all but one
sheet from each copy and then extract each one.

[jmcnamara]

On May 13, 3:31 am, unixguy <unix...@fastmail.fm> wrote:
> ok, this is definitely the issue.  I tried again, but am still having
> trouble getting the correct chart inserted in the proper place.
> Would it work better if I put each chart into a seperate
> workbook and extracted them each from there?  I could
> make copies of the workbook and delete all but one
> sheet from each copy and then extract each one.

Hi,

That wouldn't work. The extracted charts would probably all point back
to first sheet of the new workbook. The important thing to keep in
mind is that the chart binaries use a simple zero based index to refer
to the worksheets that contain the chart data. The target workbook
must mirror the template workbook so that these indices are still
valid.

The best solution would be to create a new template workbook from
scratch with the sheets in the order that you want them in. However,
since you have said that the charts are complicated and you would
prefer not to have to replicate them I would suggest creating a new
template workbook with the required number of sheets. Then copy the
charts from the old template to the new one in the order that you want
them to appear (a simple copy and paste should do it). Then test the
WriteExcel program again with the new template.

John.
--

[unixguy]

It's even a bit more complicated. Let me try to explain. I have a
graphical application that by pushing the buttons and making
selections in pulldowns can be made to produce an excel
workbook with the sheets containing the data and graphs.
I want to produce the same workbooks with my perl script.
But, I also want to change it a bit. The application puts two
different columns of data on one sheet and graphs them
together. I want to make those two separate sheets with
separate graphs. The other sheets all have a single graph
and data column.

But from what you're saying, there is some information which
ties the graph to the data in excel. My approach had been
to copy the graph onto both sheets and delete one series
on one sheet and the other series on the other sheet. It
appears that this approach can't work.

I wonder if I should be trying something else, like creating
openoffice spreadsheets and then converting them to excel
after they are complete.

[unixguy]

I started over and created the graphs from scratch using
the application-generated ones as models, rather than copying
them.

It seems that not all the properties of the extracted graphs are
copied as font size and bolding of chart and axis titles are not
preserved. Is there a list of which properties are preserved
and which are not?

But at least the chart on the sheet is actually graphing the
data contained on the sheet. Yay!