Save Plot in Excel

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Save Plot in Excel

Anh Quân Trần Hùng
Dear administrators of octave,
I have a question about output data of octave. I want to export plot images from octave to Microsoft Excel but I haven't found a function to help me.
image.png
Is there any way for me to do that?
Thank you!

-Best wishes-
Tran Quan


Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

Andreas Weber-6
Dear Tran Quan,

Am 03.07.20 um 10:24 schrieb Anh Quân Trần Hùng:
> I want to export plot
> images from octave to Microsoft Excel but I haven't found a function to
> help me.

Is "File"-> "Save as" -> "foo.png" and the include it in Excel a
feasible way?

Or what exactly are you looking for?

-- Andy


Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

DigitalHermit
In reply to this post by Anh Quân Trần Hùng
> I have a question about output data of octave. I want to export plot images from octave to Microsoft Excel but I haven't found a function to help me.

Here's a quick hack with a Python script... There may be a cleaner
octave way with one of the Excel modules.


<save-to-excel.py>
    #!/usr/bin/env python
    import sys
    import xlsxwriter
    image_name = sys.argv[1]
    excel_file = sys.argv[2]
    workbook = xlsxwriter.Workbook(excel_file)
    worksheet = workbook.add_worksheet()
    worksheet.set_column('A:A', 20)
    worksheet.insert_image('A1', image_name)
    workbook.close()

<octave_example.m.
    #!/usr/bin/octave
    image_file = 'test.png'
    excel_file = 'out.xlsx'
    out = figure()
    x = -10:0.1:10;
    plot (sin(x));
    print(out, image_file)
    command = [ "./save-to-excel.py", " ",  image_file, " ", excel_file ]
    system(command)

--
"Don't be lazy. Do the thing. Do it properly."  - Simone Giertz


Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

nrjank
Administrator
In reply to this post by Anh Quân Trần Hùng
On Fri, Jul 3, 2020 at 4:24 AM Anh Quân Trần Hùng <[hidden email]> wrote:
Dear administrators of octave,
I have a question about output data of octave. I want to export plot images from octave to Microsoft Excel but I haven't found a function to help me.

So, there are functions that create Excel spreadsheets from Octave data.  Are you asking if there is a way to create an Excel spreadsheet that contains an Excel plot of the data, and not just an image of an Octave plot of the data?  I'm not sure Octave or Matlab have functions to do that. Plots within Excel are notoriously cumbersome to work with. i'm sure there are some visual basic routines within Excel that can script plot creation, but I'm unaware of any external functions to do that.  

Personally, having worked with large unwieldy spreadsheets with many repeated plots, I think an octave driven function to script excel plot creation would be a godsend.  If anyone has workable solutions, I'm all ears. 


Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

DigitalHermit
On Fri, Jul 3, 2020 at 11:37 AM Nicholas Jankowski <[hidden email]> wrote:

>> I have a question about output data of octave. I want to export plot images from octave to Microsoft Excel but I haven't found a function to help me.
>>
> So, there are functions that create Excel spreadsheets from Octave data.  Are you asking if there is a way to
> create an Excel spreadsheet that contains an Excel plot of the data, and not just an image of an Octave plot
> of the data?  I'm not sure Octave or Matlab have functions to do that. Plots within Excel are notoriously
> cumbersome to work with. i'm sure there are some visual basic routines within Excel that can script plot
> creation, but I'm unaware of any external functions to do that.
>
> Personally, having worked with large unwieldy spreadsheets with many repeated plots, I think an octave driven
> function to script excel plot creation would be a godsend.  If anyone has workable solutions, I'm all ears.

It doesn't look like the native Octave functions can write image data
directly, though they do seem to support writing
tables and other data.

Peeking into the XLSX created programmatically via Python, it doesn't
look horribly onerous but would not be trivial.

The XLSX is a zipfile containing the following structure:
.
├── [Content_Types].xml
├── docProps
│   ├── app.xml
│   └── core.xml
├── _rels
└── xl
    ├── drawings
    │   ├── drawing1.xml
    │   └── _rels
    │       └── drawing1.xml.rels
    ├── media
    │   └── image1.png
    ├── _rels
    │   └── workbook.xml.rels
    ├── styles.xml
    ├── theme
    │   └── theme1.xml
    ├── workbook.xml
    └── worksheets
        ├── _rels
        │   └── sheet1.xml.rels
        └── sheet1.xml


The file (xl/drawings/_rels/drawing1.xml.rels) contains the reference
to the PNG image file:

<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship
Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
Target="../media/image1.png"/></Relationships>

It's all XML so would be possible to construct something with existing
Octave tooling.

There is an existing C++ XLSX library (OpenXLSX) so possibly an
oct-file approach may work if this is more cumbersome natively in
Octave.


--
"Don't be lazy. Do the thing. Do it properly."  - Simone Giertz


Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

nrjank
Administrator
The file (xl/drawings/_rels/drawing1.xml.rels) contains the reference
to the PNG image file:

<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship
Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
Target="../media/image1.png"/></Relationships>

It's all XML so would be possible to construct something with existing
Octave tooling.

There is an existing C++ XLSX library (OpenXLSX) so possibly an
oct-file approach may work if this is more cumbersome natively in
Octave.


If I'm following, that lets us embed an image file, but not a 'plot' in the Excel sense that can be later manipulated.


Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

Nicklas Karlsson
In reply to this post by Anh Quân Trần Hùng
> Dear administrators of octave,
> I have a question about output data of octave. I want to export plot images
> from octave to Microsoft Excel but I haven't found a function to help me.
> [image: image.png]
> Is there any way for me to do that?
> Thank you!
>
> -Best wishes-
> Tran Quan

The saveas(...) function could save to quite many different formats.


Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

DigitalHermit
In reply to this post by nrjank
On Fri, Jul 3, 2020 at 2:15 PM Nicholas Jankowski <[hidden email]> wrote:

>> There is an existing C++ XLSX library (OpenXLSX) so possibly an
>> oct-file approach may work if this is more cumbersome natively in
>> Octave.
>>
>
> If I'm following, that lets us embed an image file, but not a 'plot' in the Excel sense that can be later manipulated.

Yes.. just a bitmap and not a manipulatable plot.  I'm not familiar
enough with Excel plots, but perhaps it uses a similar mechanism.

--
"Don't be lazy. Do the thing. Do it properly."  - Simone Giertz


Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

nrjank
Administrator
In reply to this post by nrjank


On Fri, Jul 3, 2020, 9:01 PM Anh Quân Trần Hùng <[hidden email]> wrote:
I actually work with not just images but also numbers. With MATLAB, I found a function to do it automatically, which could create multiple data sheets on the same excel file. But it cannot run when using GNU Octave. So I am looking for functions that help me print image data into excel software similar to MATLAB


Please always reply all to include the help list. I'm not familiar enough with the functions to help.  But it sounds like it would help A LOT of you could better describe what exactly you are able to do in Matlab but not octave.  A lot of us have been guessing but we may be wandering far from what it seems you're looking for.





Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

yamane
In reply to this post by Anh Quân Trần Hùng
Hi,

If you are able to plot the graphic, so you have the values stored in your code.

Just use the csvwrite to export these data.

Kind regards,
Renato

Em sex, 3 de jul de 2020 09:24, Anh Quân Trần Hùng <[hidden email]> escreveu:
Dear administrators of octave,
I have a question about output data of octave. I want to export plot images from octave to Microsoft Excel but I haven't found a function to help me.
image.png
Is there any way for me to do that?
Thank you!

-Best wishes-
Tran Quan



Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

PhilipNienhuis
In reply to this post by Anh Quân Trần Hùng
Anh Quân Trần Hùng wrote
> Dear administrators of octave,
> I have a question about output data of octave. I want to export plot
> images
> from octave to Microsoft Excel but I haven't found a function to help me.
>
> Is there any way for me to do that?

First of all the xlsread / xlswrite functions in the io package are intended
for data I/O only. Anything beyond that, e.g., formatting and embedding
graphs etc., is not supported.
As io package maintainer I'm not enthusiastic to include such functionality
in the functions, as it would greatly increase the maintenance burden for
just a few use cases; in addition Matlab has started to advise using Table
I/O for spreadsheet I/O so additional investment in xlsread / xlswrite seems
a bad idea.

That said, I can help getting you started but you'll have to find the way
largely yourself. Maybe there are examples Out There, e.g. on Stackoverflow
or Matlab Central.
Generally it is wise to use higher-level functions to adapt the spreadsheet
file rather than building it manually yourself.

The easiest is probably using Excel itself using the COM spreadsheet I/O
interface, just load the windows package to get that. Once you've opened the
excel file using the xlsopen function, the references to the COM object
representing the spreadsheet are in the output struct. From there on it's
just invoking Visual Basic.
Some guidance is in Excel itself. Open a spreadsheet, click on "Developer"
then '"Visual Basic". Once there you can click on "Help" or tap the F2 key
to get the object browser. There you should find enough info to get further.

An intermediate way is to use one of the Java-based interfaces. From those,
Apache POI is probably the best choice. The Javadocs are online. I'm sure
you can find examples on how to get together what you want.

The hardest way is to try to build the archive yourself. The OCT interface
supplies similar pointers in the output struct returned by xlsopen, but from
experience I know it is a big puzzle to get the various references and
cross-references in the xml files in the archive set up correctly.
Hint: I found that LibreOffice is usually much more verbose as regards
internal file errors while reading than Excel.

Hopefully this helps a little.
Good luck,

Philip




--
Sent from: https://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


Reply | Threaded
Open this post in threaded view
|

Re: Save Plot in Excel

apjanke-floss


On 7/4/20 3:36 PM, PhilipNienhuis wrote:

> Anh Quân Trần Hùng wrote
>> Dear administrators of octave,
>> I have a question about output data of octave. I want to export plot
>> images
>> from octave to Microsoft Excel but I haven't found a function to help me.
>>
>> Is there any way for me to do that?
>
> [...]
>
> An intermediate way is to use one of the Java-based interfaces. From those,
> Apache POI is probably the best choice. The Javadocs are online. I'm sure
> you can find examples on how to get together what you want.

I've written a Matlab binding layer on top of Apache POI to make it
easier to produce Excel spreadsheets with complex formatting in Matlab:

https://github.com/apjanke/janklab
https://github.com/apjanke/janklab/tree/master/Mcode/classes/%2Bjl/%2Boffice/%2Bexcel
https://github.com/apjanke/janklab/blob/master/doc/UserGuide-OfficeIO.md

Wouldn't be _too_ hard to port it to Octave.

Cheers,
Andrew