代码之家 › 专栏 › 技术社区 › Sergey Stadnik

从windows脚本宿主手动操作excel文件

wsh ado vbscript excel

Sergey Stadnik · 技术社区 · 16 年前

有没有一种快速的方法来操作 现有的 来自windows脚本主机的xls文件?

我们有一个客户提供的excel模板。我们的任务是用从Oracle数据库中获取的数据填充这些模板。

当前的方法是使用windows脚本主机和vbscript:

使用ADODB从Oracle获取数据:

Set db = CreateObject("ADODB.Connection")
SQL = "SELECT ..."
Set rs=db.execute(SQL)

使用vbscript在windows脚本宿主中创建excel对象:

Set objExcel = CreateObject("Excel.Application")  
Set objWorkbook = objExcel.Workbooks.Open(xls_final)  
Set objSheet = objWorkBook.Sheets(1)

然后按如下方式逐个单元格填写模板:

If rs.EOF = False Then
   rs.MoveFirst
   Do Until rs.EOF
      objSheet.Cells(RowNumber, 1).Value = rs("COLUMN1")
      objSheet.Cells(RowNumber, 2).Value = rs("COLUMN2")
      objSheet.Cells(RowNumber, 3).Value = rs("COLUMN3")
      rs.MoveNext
   Loop
End If
objWorkbook.Save 
rs.Close

问题是其中一些文件包含大量数据,像这样填充它们需要几个小时。有更快的方法吗?

4 回复 | 直到 16 年前

Mike Woodhouse 16 年前

我觉得你在这里很好:

Set db = CreateObject("ADODB.Connection")
SQL = "SELECT ..."
Set rs=db.execute(SQL)

Set objExcel = CreateObject("Excel.Application")  
Set objWorkbook = objExcel.Workbooks.Open(xls_final)  
Set objSheet = objWorkBook.Sheets(1)

但剩下的将是惊人的缓慢,正如你所发现的。与工作表的交互有很高的开销,您要为每行的每一列支付开销。有办法解决这个问题。

最简单的是

objSheet.Cells(1,1).CopyFromRecordset rs

我建议你先试试。

Tester101 16 年前

看看这个。
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
如果您使用adodb连接到excel,而不是操纵excel,那么它也可能有帮助。如果你需要帮助阅读这篇文章。
http://support.microsoft.com/kb/257819

paxdiablo 16 年前

一种可能是将其分为两个阶段,但这取决于瓶颈在哪里。

如果是excel,那么只需将记录集行转换为csv类型的文件,完成后,创建excel对象并将整个文件导入到一个固定位置。

这可能比一个细胞一个细胞的操作要快。

如果不能将csv导入到工作表的固定位置(或者单元格不在连续的行或列中),我会将csv导入到新工作表中,然后从该工作表向模板工作表进行批量复制。

移动范围也应该比逐个单元的操作快。

批量导入和批量复制应该会给您带来一些很好的改进。我有一些工作表处理单个单元格,当您使用更复杂的excel功能时,这些单元格的速度提高了10倍(考虑使用=sum(a1..a999),而不是在vba中将每个单元格相加并将该值放在某个位置)。

至于如何从vba导入,我总是依赖于 "Record Macro" 获取可以修改的基线(对于那些我不太熟悉的人)。这个是进口的 c:\x.csv 在当前工作表中 C7 :

With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\x.csv", _
    Destination:= Range("C7"))
    .Name = "x"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

现在我确信里面的大部分垃圾都可以删除,但你最好一次一个地删除,以确保不会出现问题。

您还可以使用类似于以下内容的修改来使用不同的工作表。

dim ws as worksheet
dim savealert as boolean
set ws = Sheets.Add
ws.select
' Put all that other code above in here. '
' Move all that data just loaded into a real sheet. '
savealert = Application.DisplayAlerts
Application.DisplayAlerts = False
ws.delete
Application.DisplayAlerts = savealert

AnonJr 16 年前

您可以通过OLEDB连接访问它,而且速度会更快。

下面是我用来将数据从电子表格导入数据库的脚本中的一些代码。很明显,您会想要更改游标类型和锁类型,但是您已经知道了。

strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & objFile.Path & ";Extended Properties=""Excel 8.0;HDR=Yes"""
strSQL = "SELECT * FROM [RegistrationList$] ORDER BY DateToRegister DESC"

objExcel.Open strSQL, strExcelConn, adOpenForwardOnly, adLockReadOnly, adCmdText