Connexion OleDb pour Excel; comment puis-je sélectionner une largeur fixe, sans limite de hauteur?

Je suis en utilisant OleDb pour sélectionner des données de feuilles de calcul excel. Chaque feuille de calcul peut contenir de nombreuses petites tables, et, éventuellement, des meubles comme des titres et des étiquettes. Donc, il pourrait ressembler à ceci, où nous avons deux tables et quelques titres;

 A B C D 
1 . . . . 
2 . . . . 
3 Table1 . . . 
4 Header1 HEADER2 . . 
5 h riri . . 
6 d dewey . . 
7 l loius . . 
8 s scrooge . . 
9 . . . . 
10 . . . . 
11 . . . . 
12 . . . . 
13 . Tableau 2 . . 
14 . HEADER1 HEADER2 HEADER3 
15 . 1 foo x 
16 . 2 bar y 
17 . 3 baz z 
18 . . . . 
19 . . . .

Dans une étape précédente, l'utilisateur a sélectionné les en-têtes de la table, ils sont intéressés; dans ce cas, en regardant le tableau 2, ils auront sélectionné la gamme B14:D14.

Ces paramètres sont enregistrés, et puis j'ai besoin d'interroger la table. Il peut arriver, comme les données de feuille de calcul est mis à jour; plus de lignes peuvent être ajoutées à tout moment, mais les en-têtes sont toujours fixes. Il y a une sentinelle (ligne vide) marquant la fin de données

Pour sélectionner les données dans la table, je suis en train d'écrire une requête comme ceci;

SELECT * FROM [Sheet1$B14:D65535]

pour sélectionner les données dans le tableau 2, puis à vérifier manuellement pour la sentinelle de la ligne, mais cela semble peu satisfaisant. Excel 2003 ne peut lire les lignes 65 535 (uint16), mais excel 2007 peut lire beaucoup plus (uint32), j'ai donc écrire du code qui donne une autre requête pour Excel 2003 et 2007 selon l'extension du fichier (.xls vs .xls?).

Personne ne sait d'une façon d'écrire une requête qui dit;

'tout sélectionner en bas et à droite de B14'?
"sélectionner tout dans les colonnes B->D'
'select B12:D*" où * désigne "tout ce que vous pouvez"

Une feuille de calcul Excel 2003 peut contenir 65 536 lignes, numérotées de 0 à 65535 en interne et 1 à 65536 à l'extérieur.

OriginalL'auteur Steve Cooper | 2009-07-16

c#excel oledb

9

Pré-requis: vous pouvez déterminer facilement dans votre code ce que le nombre maximum nombre de lignes est.

En supposant que (1) il y a une grosse surcharge par SÉLECTIONNER, de sorte que la Sélection d'une ligne à la fois est lente (2) Sélection de 64 ko ou 8M lignes (même si le champ est vide) est lent ... si vous voulez voir si quelque part dans le milieu peut être plus rapide. Essayez ceci:

Sélectionnez CHUNKSIZE (par exemple, 100 ou 1000) lignes à la fois (moins lorsque vous seraient autrement plus-run MAX_ROWS). Analyse chaque bloc de la ligne vide que les marques de fin de données.

Mise à JOUR: en Fait, répondre à des questions explicites:

Q: est-ce que quelqu'un sait d'une manière d'écrire une requête qui dit;

Q1: 'tout sélectionner en bas et à droite de B14'?

A1: select * from [Sheet1$B12:] ne fonctionne pas. Que vous avez à faire ...B12:IV dans Excel 2003 et que ce soit dans Excel 2007. Cependant vous n'en avez pas besoin car vous savez ce que votre colonne la plus à droite est; voir ci-dessous.

T2: 'sélectionner tout dans les colonnes B->D'

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

T3: 'sélectionnez B12:D*" où * signifie "tout ce que vous pouvez'

A3: select * from [Feuil1$B12:D]

Testé avec Python 2.5 en utilisant le code suivant:
```
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()
```
OriginalL'auteur John Machin
1

Quelques solutions possibles:
1. Mettre vos tables sur des feuilles de calcul distinctes, puis il suffit d'interroger l'ensemble de la feuille de calcul.
2. Donner à chaque tableau dans Excel un nom (dans Excel 2007, sélectionnez le tableau, cliquez avec le bouton droit et de choisir le Nom d'une gamme...), puis dans votre requête, utilisez ce nom au lieu de "Sheet1$B14:D65535".
Espère que ça aide.

MODIFIER

Voici un troisième idée:

Je ne suis pas sûr de ce que vous êtes en utilisant pour interroger votre base de données, mais si votre moteur de recherche prend en charge les variables (comme Sql Server, par exemple) vous pouvez stocker le résultat de...

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

...dans une variable appelée @UsedRowCount, qui vous donnera le nombre de lignes utilisées dans la feuille de calcul. Donc, @UsedRowCount = LastRowUsed - InitialBlankRows.

Vous pourriez être en mesure d'utiliser la concaténation de chaîne à remplacer "65535" avec @UsedRowCount + @InitialBlankRows. Vous devez définir @InitialBlankRows à une constante (dans votre exemple, il serait de 3, depuis la ligne d'en-tête de la première table est situé à la Ligne 4).

Merci, Dan. Malheureusement, je n'ai pas de contrôler les feuilles, elles sont arbitrairement en provenance des utilisateurs et je viens d'avoir à traiter avec ce que j'ai donné.
Gotcha. J'ai juste ajouté une troisième idée de ma réponse.

OriginalL'auteur devuxer
0

Vous dire que, dans une étape précédente, les utilisateurs ont sélectionné les en-têtes. Qui est-à-dire qu'en dessous de la région d'intérêt actuel il n'y a pas quelques lignes vides suivi par un autre tableau? Je suggère que vous obtenez à sélectionnez l'ensemble de la gamme qu'ils sont intéressés à, ça devrait corriger ces deux problèmes.

Couper détail supplémentaire est assurée par l'application; j'ai lu les lignes de la table de données jusqu'à ce que les données s'épuise; c'est à dire, je obtenir une ligne vide. Puis-je résilier le lire moi-même. Malheureusement, je ne peux toujours demander à l'utilisateur de sélectionner l'ensemble de la gamme. Dans certains cas, l'utilisateur de sélectionner un en-tête pour une feuille de calcul où les données peuvent changer, mais le format ne peut pas. Par exemple, ils pourraient avoir des cours de la bourse fixe avec des en-têtes (la société, le prix d'ouverture, de clôture, de la date), mais le nombre réel de dossiers de changer au fil du temps. S'ils choisissent de 50 lignes, thn lorsque le nombre de lignes > 50, l'importation de donner trop peu de données.
Éclaircissements nécessaires à votre question (merci de modifier): (1) "sélectionnez une fois, exécuter un grand nombre de" pas "(sélectionnez ensuite l'exécuter) * de nombreuses". (2) il y a une sentinelle (ligne vide) marquant la fin de données (3) Quand vous dites "j'ai lu les lignes de la table de données jusqu'à ce que les données s'épuise" est-ce à dire "j'ai lu le résultat de SELECT * FROM [Sheet1$B14:D65535] jusqu'à ce que" ou "je ne un ligne Sélectionne jusqu'à ce que"? (4) Comment facilement pouvez-vous déterminer dans votre code si le nombre maximum de lignes est de 2^16 ou 2^20?

OriginalL'auteur John Machin
0

Je voudrais aller avec la solution de John ( lecture de 1000 lignes à la fois ).

Si Excel est installé, vous pouvez également utiliser OLE automation.

J'ai enregistré une macro simple dans Excel qui sélectionner la dernière cellule dans la table courante.
```
Sub Macro2()
    Range("B14").Select
    Selection.End(xlDown).Select
    //MsgBox ActiveCell.Address, vbOKOnly
End Sub
 
```
Maintenant, vous avez juste besoin de les traduire en C# et de lire l'adresse de la cellule active.

OriginalL'auteur ErvinS
0

Nous lire l'intégralité de la feuille de calcul (ex: SELECT * from [Feuil1$]) et de gérer tout le reste dans notre code de l'application. Il est assez facile pour la course à travers la résultante OleDbDataReader à obtenir le point de départ de vos données et de lancer le traitement.

Il peut ne pas être absolument le moyen le plus rapide pour sucer des données à partir d'Excel, mais il est fiable.

OriginalL'auteur Phil

Vous devez vous connecter pour publier un commentaire.