Re: [libreoffice-users] [Calc] Create and export chart

On 14/07/11 03:13, Thorsten Kampe wrote:
> Hi,
>
> I have a Calc file which contains multiple sheets.
>
> - every sheet contains one month of data: from May 2010 to July 2011.
>
> - every sheet has two columes: in column A the days and in column B the
> actual data.
>
> For example:
>
> [July 2011 Sheet]
>    A      B
> 1 07/02  75.6
> 2 07/03
> 3
> 4 07/04  76.4
> 5 07/05  76.3
> 6 07/06  76.2
> 7 07/07
> 8 07/08  75.6
>
> The data entries are sometimes empty (07/07 for example), do not always
> start on the 1st and there are some empty rows in between.
>
> Problem 1: I want to create a chart of all sheets/months. What do I have
> to enter for Date Range in step 2 of the chart wizard (pre-selected is
> $'July 2011'.$A$1:$B$3)?
>
> Problem 2: I'd like to automatically create this chart every day and
> export it to a PNG file. Is that doable via LibreOffice Calc?
>
> Thanks in advance, Thorsten
Thorsten,

This is how I would address this problem

Sheet 1 would include all data (column A & B) for every month.

Sheet 2 will be the monthly summary sheet.

In this sheet we create a stylised month.

Cell A1 and B1 would be a picklist of months or years. Use data > 
validity > Criteria to set up a list or point to a allowable range.

In row A3:AE3 create the numbers 1-31 which represents the day of the month.

Then underneath each day in A4:AE4, use the Vlookup() function to 
extract the data for each day of the month and year selected.

The function would look like this...
=VLOOKUP(DATE($A$1,$B$1,A3),Sheet1.YourRange,2,0)

to get rid of the annoying situation where absent values throw and error 
use the following to capture them

=IF(ISERROR(VLOOKUP(DATE($A$1,$B$1,A3),Sheet1.YourRange,2,0)), "", 
VLOOKUP(DATE($A$1,$B$1,A3),Sheet1.YourRange,2,0))

This will create an idealised month with values populated based on what 
month and year you select.

Create your chart based on this.

In regards to exporting as a PNG, several options exist if you are using 
Windows...

1. use a PDF printer driver that exports to PNG, rather than the inbuild 
export utility like PDFCreator
http://sourceforge.net/projects/pdfcreator/

2. Use an ImageCreating printer driver like ImagePrinter
http://sourceforge.net/projects/imageprinter/

Doing the same in GNU/Linux requires you to install a better PDF Printer 
Driver than allows you to save as an image or post-process the PDF using 
another program.

Once you set this up to work manually create a macro and attach it to a 
button or something.

--
Cheers Simon

   Simon Cropper
   Website Administrator
   http://www.fossworkflowguides.com
   The fossWorkflow Guides
   (c) Simon Cropper CC-BY-SA 3.0 Australia
   http://creativecommons.org/licenses/by-sa/3.0/au/deed.en

--
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted