Use Python script to read Excel data in Delphi

Use Python script to read Excel data in Delphi
2007-10-18 17:28:22
Tags: Delphi Excel python
Original works, allow reprint, please be sure to use hyperlinks in the form of the original source of the article, author information and this statement. Otherwise, the legal liability will be investigated. http://seewind.blog.51cto.com/249547/46669
Some time ago, Python was used in a formal project to read data from an Excel table. The specific requirement is that some data in the project database needs to be adjusted according to the data in the Excel table, the function should be relatively simple. In order to learn python, we decided to use Delphi+python to implement it. Delphi uses the Pythonfordelphi control to join the Python engine. It took a large half day to realize the whole function.
Delphi project, you need to modify the package class of the data table, so that it can appear in Python and use, simple operation of the data table. Changed a few places:
Learn the Delphi modules project in the control, introducing the Python engine into the project and adding a Delphi module.
Change the table operation base class to inherit from Tcomponent @[email protected], in order to be able to directly use the published property in the Py script, the new implementation of a wrapper class of the base class, Inherit from Wrapdelphiclasses.tpydelphicomponent, and register to Delphi module.
Add a new menu to invoke the external py script. Greed is simple, just add the code in place of the function, and register the specific table wrapper object before invoking the script.
Python, there are two of their own script, one of which is the use of win32com encapsulated Excel operation pyexcel.py, simple code, enough on the line. The other is to implement the specific function of the script, code into the following:
Import time
From Pyexcel import Pyexcel
Import types
From Delphi Import *
Ldehs = {}
CR = chr # + CHR (13)
CRCR = cr + CR
def addspace (ASTR):
Cr_r = Chr (10)
Spacestr = ' '
Return Astr.replace (Cr_r, CR_R+SPACESTR)
Pass
def SETDEFZ (UKW):
Deb. Edit ()
S1 = Deb. Defz
UGZNR = Addspace (S1.decode (' GBK '))
UGZNR = U ' "Working content"%s '% (UGZNR)

UDEFZ = U '%s%s ' Note description '%s ' erratum record '%s%s ' \
% (Ugznr, CRCR, CRCR, UKW, CRCR)
Deb. DEFZ = Udefz.encode (' GBK ')
Deb. Post ()
Pass
Def Setdefzbyxls ():
Global CR, Ldehs
Col_deh = ' A '
col_kw = ' B '
IRow = 0
Iempty = 0
XL1 = Pyexcel ()
If XL1. Findbook (' Sheet1 '):
while (IEMPTY<10):
IRow + = 1
Try
Sdeh = str (xl1.getrangevalue (Col_deh + str (IROW)))
Pass
Except
Sdeh = None
Pass
If not Sdeh or (sdeh== ") or (sdeh== ' None '):
Iempty + = 1
Elif Deb. Locate (' DEH ', Sdeh):
Ldehs.setdefault (Sdeh, Sdeh)
S2 = Addspace (xl1.getrangevalue (col_kw + str (IROW)))
SETDEFZ (S2)
Def setothers ():
Global Ldehs

Deb. First ()
While not Deb. Eof ():
If not Ldehs.has_key (Deb.deh):
Ldehs.setdefault (Deb.deh, Deb.deh)
Try
SETDEFZ (U ")
Except
Pass

Deb. Next ()
Setdefzbyxls ()
Setothers ()
In writing debugging this script took more than half the time, which encountered some problems:
Coding problems. Fortunately, the meaning of Unicode has just been understood in the previous period, and this problem is now solved in practice. The Chinese information stored in a string in Delphi is usually encoded using the system's default character set. Remove the string from Delphi such as: S1 = Deb. DEFZ,S1 the encoding at this time is the system default character set (GBK). Use code: S1.decode (' GBK ') in the Py script to decode and get a Unicode string. When saving back to Delphi, use GBK encoding for Unicode: Deb. DEFZ = Udefz.encode (' GBK ').
Pythonfordelphi controls, using Delphi's Rtti technology, can directly use some of the published properties in a py script, simplifying a lot of work. However, published methods, procedures, and array properties are not directly available and need to be packaged in the corresponding encapsulation class.
It is convenient to write the script using the Pyscripter tool. However, there seems to be no good way to debug scripts running in this way. Now Pyscripter support remote Debugging function, later have time to find a way to solve this problem.


This article is from the "Seewind" blog, make sure to keep this source http://seewind.blog.51cto.com/249547/46669

Use Python script to read Excel data in Delphi