Random Ramblings on LabVIEW Design

Community Browser
Labels
cancel
Showing results for 
Search instead for 
Did you mean: 

Open Document Spreadsheet Tool

Active Participant

Hello My Sweets,

Way back when I started a project to make a Open Document Tool, some words can be found here.

It came grinding to a halt due to memory leaks in the xpath tool used for navigating content.xml and pressure of work.

Well .... I've only gone and cracked it.

So attached to this article is a project with the following example.

ExampleScreen.png

And the block diagram looks like this.

BlockDiagram.png

And what it does is

  1. opens a template spreadsheet (to get all the formatting, preload data etc etc).
  2. Returns Sheet names
  3. Returns Contents for a selected sheet
  4. Stores the Spreadsheet

There's also couple of methods for replacing and inserting sheets. This is restricted to strings at the mo' but there's no reason we can't add formatting/polymorphism.

This should be all we need create spreadsheets from LabVIEW (obviously we can be adding more methods).

I would like to add some word processor functionality in the coming months as another class.

It's been tested with LibreOffice and OpenOffice and one converted excel file with the help of Jarobit, but this is a pretty early release so expect surprises.

I've used the open G ZLIB tools, but these are embedded in my project and namespace protected in an LVLIB so it should sit in a project and mind its own business.

Now I need to sleep.

night night Lovelies

Late night issue fix (always the recipe for awesome software), issues 1 and 2 sorted SW 09-04-2016

12-04-2016 I think I've sussed the Excel issues, so I've included a Template.ods with some data in it too. (there is a minor untidyness with some the repeat rows of emptyness)

It's now 4M because I've added all the ZLIB stuff, my thinking being that we can trim it back down when we're happy.

13-04-2016 disappointing result on the rebuilding of the ODS file, ZLIB is unzipping empty dirs as 0 value files. This is causing me some angst as I'm struggling to get the file correct for both LibreOffice and Excel.

ODS files are slightly simpler when created by Excel, so it all works OK using the template.ods provided <-- Well Well Well it appears there's an issue with ZLIB Read Compressed File__ogtk.vi where it is extracting an empty directory.

15-04-2016 I've tidied the project and improved the example.

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Comments
Active Participant

I've tried to implement it into my program (the reason I have finally got off my bum and done it) and have noticed a couple of issues. I have video'd the process and will edit and release it next week. I will also update the project after I have sorted it and replace the version here.

Issue 1 - icon is wrong on Insert Sheet method

Issue 2 - it missed some columns on Insert Sheet Method

On the upside, it created a spreadsheet I could open in LibreOffice and pretty fast too.

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

Issue 2 is related to a repeat when, something is up with my logic!

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

WARNING: This is early release, please feed me back...

The design is specifically for my problem, my brain doesn't think in APIs (thoric has already given a load of feedback bless him)

This might be a good group effort, is github a good place to put it and open a NI Group for it?

Chuck use cases at me, it all helps the design effort.

I'll format the feedback into another article.

Implementation Video is uploading.

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

Currently the output doesn't open in Office Excel, it complains of bad content.

When I attempt to open it in Google Sheets it doesn't complain, but there's no content, just a highly zoomed out view of one sheet.

I think Open Spreadsheet is supported in both, so something somewhere isn't quite right yet. Any clues? I'm happy to help you look.

Thoric (CLA, CLED, CTD and LabVIEW Champion)


Active Participant

I'll email you a file I created, if you can do the same for me.

What file did you use as the template file? the template.ods file I provided is a good starting point.

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

To check the contents of an ODS file, change the suffix from .ods to .zip

Extract content.xml

Use an xml editor to have a nosey around. (I use notepad++ with the xml plugin loaded)

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

Yeah, I used template.ods, but I didn't add any content. Inspecting the .xml content shows no issues. I wouldn't expect the file to fail though if it's empty, just show an empty spreadsheet. I'll email you what I created.

Using "OpenEditAndSaveSpreadsheetExample" to add data to a cell also appears to require adding a cell data type string. What should we be putting in there?

Thoric (CLA, CLED, CTD and LabVIEW Champion)


Active Participant

Video for implementation is here

I fixed the loading a duff template eating all your memory error, email me if you want it.

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

If you put nothing it will default to a string, it should add data OK.

I've put the type data in so that we can use the template to set the cell format etc. Not really played with that much yet, but appears to work with currency OK. The clever trick is to make a polymorphic data entry method and set the formatting from that.

Didn't need it for my project.

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Member

Getting some bad results with MS Excel. I used your template.ods file and extracted on index 0 OK but Excel complains about the extracted file being corrupted but still shows a blank sheet. I've also tried using a single sheet Excel ODS file but the extracted file was not able to be loaded by Excel.

I remember playing about with this before and having similar problems so just stuck with a basic Exel XML type report.

Active Participant

Excel doesn't follow the standard (it also sticks some very strange repeat declarations in), I don't have it here so can you email me the ODS

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

An interesting exercise would be to compare how LibreOffice converts excel and vice-versa. Any chance you can email me the xls file and the resulting ODS file. I can then compare the method. I know initially they didn't compress one file (the cynic would say they did it just to cock up the standard!)

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

My example file works fine in Google Docs, I guess I need to back-save and compare xml tho'

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Member

Microsoft Excel for Mac (v15.20) does open your template.ods correctly (not showing any contents, as there aren't any). A file generated from your example (now contents added or changed) doesn't open.

The only difference I could find is some tabs inserted towards the end of the copied file (Template-Copy/content.xml)...

diff.png

Can you perhaps supply a template file with actual contents?

Joerg Hampel, CLA
Active Participant

Hi Joerg

just use the Set Sheet method to put in a 2d array of text using the sheet name "Sheet1".

I've not tried adding no data, so it's possibly a bug.

First of all I want to check Excel can load it.

If I update the zip file attached to the article it will dump it into moderation again!

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Member

It looks more or less the same. The content.xml does show the 2d array I added, but also has the additional tabs in it.

I don't really know how to re-archive the whole thing after manually removing those tab stops from content.xml to test if these tabs are the only thing preventing Excel from opening the ods file.

Joerg Hampel, CLA
Active Participant

I wrote this so I wouldn't need Excel!, now I'm going to have to install it. Grrrrrr.

On the upside I can print from libreoffice from the command line, I just found out.

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

Loaded Excel 2013 and it does format the files differently, I need to play with the content.xml to see where it's going wrong.

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Member

So it didn't help just getting rid of those tabs? As mentioned, my version of excel did load your (empty) Template.ods quite fine...

Joerg Hampel, CLA
Active Participant

There's a slight gotcha when fault finding ods files by unzipping them, when you zip them up, the mime type file needs to remain unzipped. 7 zip>>open archive and drag content.xml in is a better option. I'm busy today, but got close last night to sussing the Excel issue out.

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

Now this is interesting https://odf-validator.rhcloud.com/

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

My current theory is to do with ordering. Excel requires the mimetype to be the first file in the archive, which frankly is shite!

By taking the template and extracting it to a tmp dir, I can then rebuild the ODS with a content.xml that was erroring and it loads OK. Now I need to change the API to suit, hopefully be ready by end of the week.

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Member

This sounds like the problem I ran into a few years ago when I was trying to get a multiple sheet report from an app on a server that did not have MS Office. I settled on a really basic Excel xml file type which was more or less hand coded and is still working. One file has to be first and another has to be uncompressed

Active Participant

One comment I saw that made me smile was

"If the program needs the file contents in a particular order, then perhaps it is the program that is broken, not the file!"

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

So now it works with Excel, but it's not formatted correctly for Open Doc Format. I'll fix it shortly....nearly there

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png
Active Participant

Should all be OK now, if someone can confirm for me?

 photo 4be9e774-656d-44c1-9a8d-1db1a15cdd42_zps6445ae67.png