2.3.1 Sending Data To Worksheet

Introduction

This example sets the XY designations for worksheet, then fills columns with data by using SetData method.

A dialog is created when the script executes. Please enter the information for Output Worksheet name, the number of rows, and X/Y column format, then click Send to run. The results in the X column will be row number plus the row index, and the values in the Y column will be random integers between 20 and 30.

from tkinter import *
from tkinter.ttk import *
import PyOrigin
import random
colformats = ['Numeric', 'Text', 'Time', 'Date', 'Month', 'Day of Week', 'Text & Numeric']
fmts = [PyOrigin.COLFORMAT_NUMERIC, PyOrigin.COLFORMAT_TEXT, PyOrigin.COLFORMAT_TIME, PyOrigin.COLFORMAT_DATE, PyOrigin.COLFORMAT_MONTH, PyOrigin.COLFORMAT_WEEKDAY, PyOrigin.COLFORMAT_TEXT_NUMERIC]
def SendWorksheet(wksName, rows, xfmt, yfmt):
    wks = PyOrigin.FindWorksheet(wksName)
    if wks.IsValid():
        colx = wks.Columns(0)

        if xfmt > -1:
            colx.Format = fmts[xfmt]
        coly = wks.Columns(1)
        if yfmt > -1:
            coly.Format = fmts[yfmt]
        nOffset = wks.GetRowCount()
        x = list(range(rows))
        y = list(range(rows))
        for i in range(rows):
            x[i] = i + nOffset
            if  fmts[xfmt] == PyOrigin.COLFORMAT_DATE:
                x[i] = x[i] + PyOrigin.JULIANDATEOFFSET
            y[i] = random.randrange(20,30)
            if  fmts[yfmt] == PyOrigin.COLFORMAT_DATE:
                y[i] = y[i] + PyOrigin.JULIANDATEOFFSET        
        xyData=[x,y]
        wks.SetData(xyData, -1)
        print('Send Data Done!!!')
    else:
        print('Target Worksheet doesn\'t exist!!!')

            
class OPyGUI(Frame):
    def __init__(self, parent=None):
        print(parent.__class__)
        parent.title('Send Data')
        Frame.__init__(self, parent)
        row = Frame(self)
        lab = Label(row, text='Target Worksheet:')
        lab.pack(side=LEFT, anchor=W)
        self.wks = Entry(row)
        self.wks.insert(0,'[Book1]Sheet1!')
        self.wks.pack(side=RIGHT, anchor=E)
        row.pack(side=TOP)

        row = Frame(self)
        lab = Label(row, text='Number of Rows:')
        lab.pack(side=LEFT, anchor=W)
        self.rows = Entry(row)
        self.rows.insert(0,'20')
        self.rows.pack(side=RIGHT, anchor=E)
        row.pack(side=TOP)

        row = Frame(self)
        lab = Label(row, text='X Column Format:')
        lab.pack(side=LEFT, anchor=W)
        self.xcb = Combobox(row, values=colformats)
        self.xcb.insert(0, 'Numeric')
        self.xcb.pack(side=RIGHT)
        row.pack(side=TOP)
        
        row = Frame(self)
        lab = Label(row, text='Y Column Format:')
        lab.pack(side=LEFT, anchor=W)
        self.ycb = Combobox(row, values=colformats)
        self.ycb.insert(0, 'Numeric')
        self.ycb.pack(side=RIGHT)
        row.pack(side=TOP)
        
        row = Frame(self)
        button = Button(row, text='Send', command=self.OnOK)
        button.pack(side=LEFT)
        button = Button(row, text='Close', command=self.Quit)
        button.pack(side=RIGHT)
        row.pack(side=TOP)
    def OnOK(self):
        targetWksName = self.wks.get()
        nrows = eval(self.rows.get())
        xfmt = self.xcb.current()
        yfmt = self.ycb.current();

        SendWorksheet(targetWksName, nrows, xfmt, yfmt)

        
    def Quit(self):
        tk.destroy()
    

if __name__ == '__main__':

    tk = Tk()
    tk.geometry('300x150+400+400')
    MyWin = OPyGUI(tk)
    tk.resizable(0, 0)
    MyWin.pack()
    #MyWin.grab_set_global()
    MyWin.mainloop()