Instead of exporting raw SQL data to an excel spreadsheet, is it possible to send a combo chart? The boss wants a production report every morning in an excel spreadsheet with data only to be displayed in a combo chart (clustered column with line).
What options are available?
You could create an Excel sheet that pulls in the sql data via a data source connection. Then you could set up your Excel chart to look at that data table?
I would add a button with vba code behind to refresh the data source, so laymen don’t have to find the refresh option from the ribbon
OK, this is something I can do but he specifically wants a new spreadsheet report “emailed” to him every morning with combo charts. Having trouble finding anything online on how to do this. He mentioned you can create a “report” in Access, then set up a macro that sends an email. You can then set an event that runs that macro every day.
You might be able to create a template Excel file, then use one of the Excel python libraries to edit the table of data in the template that the chart uses, and save as a new file?
I would use one of these modules:
I would set up the Excel spreadsheet with the combo chart ( define the data area of the combo chart ).
Then with Ignition I would set up a script that takes the data from the DB and put it into the combo chart’s data area ( you can do this with JAVA POI ). At the end of the script ( or another one ) I’ll put the send email part.
This is where I’m stuck, the combo chart. What is Java POI?
The Apache POI Project’s mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java.
OK, I’ll check this out. Thanks.
I don’t know if you’ll be able to use one of the office libraries to add a chart, and if you can, I suspect it will be more effort than it’s worth, if that’s what you’re looking to do. As we’ve said, create a template Excel spreadsheet with the chart already there reading a table of data, and use ignition to replace the table of data and save as another file.
You should also be able to use Python’s libraries ( like https://openpyxl.readthedocs.io/en/stable/# ).
From what I read it should not have C dependencies. Just check that the version you download is usable with Python 2.7 ( if you are using Ignition 8 )
Does this have to be Excel?
What you’re describing sounds like a report… wouldn’t the Reporting Module handle this easily?
Yes, it has to be Excel and not just raw data which I was able to do with running a tag change python script based on datetime. Before that tho, I ran a demo of the Reporting module and was able to do what was requested, but the company doesn’t want to purchase the additional software module since the main guy that wants it, thinks it can be done using MS Access in conjunction with Excel for free. I just want to simply provide a clean approach to get what he wants.
Main question now is, is there a python script that generates combo charts? I’m assuming not, or at least yet. This would be more of an Excel software related tool and not something that could be developed via python, correct?
See my post and @c.bertulli 's
Well, there are python / Java libraries that generates combo charts but I think it’s an overkill.
You can easily prepare a template file with Excel ( open execel, insert the combo chart and fill it with random data, save it ) then with Python / Java libraries you create a script that opens the Excel template and replace the random data with the DB data.
Yes, this makes the most sense. I’ll see if this is sufficient with the one who is requesting the report. Thanks!
