Geek Answers Handbook

OleDb connection with Excel; How to choose a fixed width, unlimited height?

I use OleDb to select data from Excel spreadsheets. Each table can contain many small tables and possibly furniture, for example, headings and labels. So it might look like where we have two tables and some headers;

            Abcd
    1 . . . .
    2. . . .
    3 Table1. . .
    4 Header1 HEADER2. .
    5 h huey. .
    6 d dewey. .
    7 l loius. .
    8 s scrooge. .
    nine . . . .
    10 . . . .
    eleven . . . .
    12 . . . .
    13 . Table 2. .
    fourteen . HEADER1 HEADER2 HEADER3
    fifteen . 1 foo x
    16 . 2 bar y
    17. 3 baz z
    eighteen . . . .
    19 . . . .

In the previous step, the user selected the headers of the table in which they are interested; in this case, looking at table 2, they will select a range B14:D14.

, . , ; , . ( ),

, :

SELECT * FROM [Sheet1$B14:D65535]

2, , . Excel 2003 65535 (uint16), excel 2007 (uint32), , Excel 2003 2007 (.xls vs. XLS?).

- , :

  • ' B14'?
  • ' B- > D'
  • ' B12: D *' * ", "
+5
5

: .

, (1) SELECT, SELECTing row (2) SELECTing 64K 8M ( ) ... , - . :

CHUNKSIZE (, 100 1000) (, MAX_ROWS). , .

: , :

: - , :

Q1: ' B14'?

A1: select * from [Sheet1$B12:] . ...B12:IV Excel 2003 , Excel 2007. , , ; . .

Q2: ' B- > D'

A2: select * from [Sheet1$B:D]

Q3: ' B12: D *', * ", "

A3: * [Sheet1 $B12: D]

Python 2.5, :

import win32com.client
import sys
filename, sheetname, range = sys.argv[1:4]
DSN= """
    PROVIDER=Microsoft.Jet.OLEDB.4.0;
    DATA SOURCE=%s;
    Extended Properties='Excel 8.0;READONLY=true;IMEX=1';
    """ % filename
conn = win32com.client.Dispatch("ADODB.Connection")
conn.Open(DSN)
rs = win32com.client.Dispatch("ADODB.Recordset")
sql = (
    "SELECT * FROM [Excel 8.0;HDR=NO;IMEX=1;Database=%s;].[%s$%s]"
    % (filename, sheetname, range)
    )
rs.Open(sql, conn)
nrows = 0
while not rs.EOF:
    nrows += 1
    nf = rs.Fields.Count
    values = [rs.Fields.Item(i).Value for i in xrange(nf)]
    print nrows, values
    if not any(value is not None for value in values):
        print "sentinel found"
        break
    rs.MoveNext()
rs.Close()
conn.Close()
+9

:

  • , .
  • Excel ( Excel 2007, , " ..." ), "Sheet1 $B14: D65535",.

, .

:

, , (, Sql Server), ...

SELECT COUNT (*) FROM NameOfServer... Sheet1 $

... , @UsedRowCount, , . , @UsedRowCount = LastRowUsed - InitialBlankRows.

, "65535" @UsedRowCount + @InitialBlankRows. @InitialBlankRows ( 3, 4).

+1

, . , , ? , , .

0

( 1000 ).

Excel, OLE.

I recorded a simple macro in Excel that selects the last cell in the current table.


Sub Macro2()
    Range("B14").Select
    Selection.End(xlDown).Select
    //MsgBox ActiveCell.Address, vbOKOnly
End Sub

Now you just need to translate this into C # and read the address of the active cell.

0
source

We read the entire spreadsheet (that is: SELECT * FROM [Sheet1 $]) and process everything else in our application code. It’s easy enough to go through the resulting OleDbDataReader to go to the starting point of your data and begin processing.

It may not be the fastest way to suck data from Excel, but it is reliable.

0
source

More articles:


All Articles