处理Excel [英] Dealing with Excel
问题描述
我需要从Oracle中提取数据并将其填入Excel
电子表格中。你曾经用过什么模块与Excel接口和
你会推荐它吗？
罗伯特
I need to pull data out of Oracle and stuff it into an Excel
spreadsheet. What modules have you used to interface with Excel and
would you recommend it?
Robert
推荐答案
Robert Hicks写道：
Robert Hicks wrote:
我需要从Oracle中提取数据并将其填入Excel电子表格中。你曾经使用过什么模块与Excel接口，你会推荐它吗？
I need to pull data out of Oracle and stuff it into an Excel
spreadsheet. What modules have you used to interface with Excel and
would you recommend it?
可以直接从Python代码控制Excel（你做
不需要在Excel中编写Excel宏）。它对我来说完美无瑕
。
我的代码例如：
import win32api
来自win32com.client import Dispatch
xlApp = Dispatch（" Excel.Application"）
xlApp.Visible = 0
xlApp.Workbooks。添加（）
..
..
=== snip ===
在Internet或Excel文档中找到VBA（Visual Basic for Applications）的价值是有用的。
常量和这个
常量值指定为Python常量相同代码中的名称为
VBA。例如：
xlToLeft = 1
xlToRight = 2
xlUp = 3
xlDown = 4
xlThick = 4
xlThin = 2
xlEdgeBottom = 9
比你可以使用与您的Excel宏完全相同的代码
（包括格式等）。
=== snip ===
xlApp.Range （xlApp.Selection，xlApp.Selection.End（xlToRight））。选择（）
xlApp.Range（xlApp.Selection，xlApp.Selection.End（xlDown））。选择（）
xlApp.Selection.NumberFormat =" ### 0"
xlApp.Selection.HorizontalAlignment = xlRight
xlApp.Selection.IndentLevel = 1
=== snip ===
HTH
Petr Jakes
It is possible to control Excel directly from the Python code (you do
not need to write Excel macros within the Excel). It works flawlessly
for me.
My code goes for example:
import win32api
from win32com.client import Dispatch
xlApp = Dispatch("Excel.Application")
xlApp.Visible=0
xlApp.Workbooks.Add()
..
..
=== snip ===
It is helpful to find values of VBA (Visual Basic for Applications)
constants on the Internet or in the Excel documentation and this
constants values assign as Python constants with the same names as in
VBA in the code. For example:
xlToLeft = 1
xlToRight = 2
xlUp = 3
xlDown = 4
xlThick = 4
xlThin = 2
xlEdgeBottom=9
Than you can use exactly the same code as in your Excel macros
(including formating etc.).
=== snip ===
xlApp.Range(xlApp.Selection, xlApp.Selection.End(xlToRight)).Select()
xlApp.Range(xlApp.Selection, xlApp.Selection.End(xlDown)).Select()
xlApp.Selection.NumberFormat = "# ##0"
xlApp.Selection.HorizontalAlignment = xlRight
xlApp.Selection.IndentLevel = 1
=== snip ===
HTH
Petr Jakes
我只想成为，也许我没有正确地阅读您的回复。我是在谈论从Oracle中读取大量行并将它们写入excel文件，而不是使用宏来编写这些行。
Robert
I just want to be and maybe I am not reading your response right. I am
talking about reading in bunch of rows out of Oracle and writing them
to an excel file, not using macros.
Robert
" Robert Hicks" < SI ***** @ gmail.com>在消息中写道
news：11 ********************** @ g47g2000cwa.googlegr oups.com ...
"Robert Hicks" <si*****@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
我需要从Oracle中提取数据并将其填入Excel电子表格中。您曾经使用过什么模块与Excel接口？
您会推荐它吗？
I need to pull data out of Oracle and stuff it into an Excel
spreadsheet. What modules have you used to interface with Excel and
would you recommend it?
使用什么来将Microsoft库绑定到Python？
我认为这将是win32com并且我承认没有使用它。
最好的办法是使用微软ADODB库和Excels自己的
CopyFromRecordset函数。使用ADODB，您可以轻松地为Oracle服务器创建连接
。您可以使用它来填充ADODB.Recordset对象
查询结果。一旦你的记录集塞满了查询结果
，你就可以将它传递给ExcelCopyFromRecordset。功能：
工作表（无论如何）。单元格（1,1）.CopyFromRecordse t {记录集对象}
和wham！ - 你把它放在工作表的表格中。
Thomas Bartkus
What does one use to bind Microsoft libraries to Python?
I think it would be "win32com" and I confess to not having used it.
Best bet would be to use Microsofts ADODB library together with Excels own
CopyFromRecordset function. Using ADODB, you can easily create a connection
to an Oracle server. You would use this to stuff an ADODB.Recordset object
with query results. Once you have your recordset stuffed with query results
you can pass it to the Excel "CopyFromRecordset" function:
Worksheets("Whatever").Cells(1,1).CopyFromRecordse t {recordset object}
and wham! - You have it in a table on a worksheet.
Thomas Bartkus
这篇关于处理Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
