WRITE EXCEL

WRITE EXCEL

Support Command

Writes variables (vectors) to an Excel file.

Spreadsheet programs are a popular method for saving data. Most spreadsheet programs support the Microsoft Excel file format.

Dataplot does not natively support reading or writing to Excel format files. The WRITE EXCEL command works as follows

1. Dataplot writes the name of the Excel file to line 1 of the file "dpst5f.dat". It writes the name of the Excel sheet to line 2 of "dpst5f.dat". The default sheet name is "Sheet1". To change the sheet name, enter the command
   SET EXCEL SHEET <sheet-name>

2. Dataplot writes the contents of the variables to the file "dpst1f.dat".

3. Dataplot then invokes a Python script to read the variables saved in "dpst1f.dat" and write them to the Excel file listed in "dpst5f.dat".

   The Python script, "write_excel.py", is located in the "scripts" subdirectory of the Dataplot auxiliary directory. This script uses the Pandas function "pandas.read_csv" to read the data from "dpst1f.dat". It then uses the Pandas function "dataframe.to_excel" to write the data to Excel.

This command assumes that Python and the Python package Pandas are already installed on your local platform. Dataplot does not check if Python is installed and it does not initiate the Python installation if it is not already installed.

As Python is used by many popular applications on Linux platforms, most Linux platforms will already have Python installed. However, this is not the case for Windows and MacOS platforms.

If you need to install Python, there are a number of Python distributions (see https://wiki.python.org/moin/PythonDistributions). However, the most common are ActivePython from ActiveState and Anaconda from Continuum Analytics. Dataplot does not depend on a specific Python distribution and we make no recommendation for the preferred distribution.

If you infrequently need to write Excel files and do not already have Python installed on your local platform, it may be simpler to write the Dataplot files to an ASCII file with the WRITE command and then import the ASCII file within the spreadsheet program. However, if you anticipate the need for frequently writing Dataplot generated data to a spreadsheet, then going through the Python installation is probably worth the effort.

WRITE EXCEL <fname> <variable list>
                        <SUBSET/EXCEPT/FOR qualification>
where <fname> is the name of the Excel file to write to;
            <variable list> is a list of variables to write;
and where the <SUBSET/EXCEPT/FOR qualification> is optional.

WRITE EXCEL FILE.XLSX Y1 Y2 Y3 X
WRITE EXCEL FILE.XLSX Y1 Y2 Y3 X SUBSET X > 0

Excel (or some other spreadsheet program) does not need to be installed on your local platform. For example, you can create the Excel file on a Linux platform and then move the Excel file to a Windows platform to view in Excel.

If Python is not installed on your default path, you can specify it using the SET PYTHON PATH command. For example, the following is for the Anaconda installation of Python 3 under Windows (where Anaconda is installed for the single user heckert)
set python path c:\Users\heckert\AppData\Local\Continum\anaconda3\

There are several different Python distributions. The appropriate Python path will depend on the specific distribution you used to install Python and whether you choose to install it for a single user or for all users.

On Windows platforms, the "write_excel.py" script will be copied to the current directory.

None

None

READ EXCEL	=	Read variables from an Excel file.
PYTHON	=	Run a user specified Python script from within Dataplot.
WRITE	=	Write variables, strings, parameters and matrices.

Data Output

2020/02

 
rm dpzchf.dat
.
SET CONVERT CHARACTER ON
READ IMONTH VALUE1 VALUE2 VALUE3
January    21205    1  100
February   19867    2  200
March      24991    3  300
April      16523    4  400
May        17341    5  500
June       27912    6  600
July       29105    7  700
August     28766    8  800
September  23332    9  900
October    20211   10 1000
November   18298   11 1100
December   13112   12 1200
END OF DATA
.
write excel test.xlsx  imonth value1 value2 value3
list excel test.xlsx