SED navigation bar go to SED home page go to Dataplot home page go to NIST home page SED Home Page SED Staff SED Projects SED Products and Publications Search SED Pages
Dataplot Vol 1 Vol 2

WRITE EXCEL

Name:
    WRITE EXCEL
Type:
    Support Command
Purpose:
    Writes variables (vectors) to an Excel file.
Description:
    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.

Syntax:
    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.
Examples:
    WRITE EXCEL FILE.XLSX Y1 Y2 Y3 X
    WRITE EXCEL FILE.XLSX Y1 Y2 Y3 X SUBSET X > 0
Note:
    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.
Note:
    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.

Note:
    On Windows platforms, the "write_excel.py" script will be copied to the current directory.
Default:
    None
Synonyms:
    None
Related Commands:
    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.
Applications:
    Data Output
Implementation Date:
    2020/02
Program:
     
    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
        

Privacy Policy/Security Notice
Disclaimer | FOIA

NIST is an agency of the U.S. Commerce Department.

Date created: 02/20/2020
Last updated: 02/20/2020

Please email comments on this WWW page to alan.heckert@nist.gov.