Suche Datensenitive ExportToExcel-Funktion

[Jumpy]

Hallo,

ich hab eine Funktion geschrieben, die Inhalte einer ADO-Query nach Excel exportiert via OLE usw. Beispiele dafür hab ich hier einige gefunden und mich daran orientiert. Knackstelle ist beim Schreiben in eine Excel-Zelle (sinngemäß):

Code:

while not query.EOF do
  begin
  for j=0 to query.Fields.Count-1 do
    Excel.Workbook.Worksheet.Cells[i+1,j+1].value=query.Fields[j].AsString;
  Inc(i);
  query.Next;
  end;

Problem ist das Excel diese Daten fröhlich umformatiert. Beispiel ein String mit Personalnummer 001234. Excel schneidet die führenden Nullen ab und interpretiert das als Zahl 1234.
Double oder Currency Werte bekommen in Excel amerikanische Zahlenformate (da wohl die Daten so in der DB sind) usw. usw.

Hat jemand vllt. eine Funktion, die das abfängt und z.B. nach den Datentypen der Query guckt und dann entsprechend irgendwie reagiert?

[shmia]

Du könntest das Property NumberFormat (aus Interface Range) setzen.
Für einen String ist das NumberFormat = '@'.

[p80286]

Delphi-Quellcode:

if forcestring then 
      excel.Activesheet.Cells.NumberFormat := '@';

Das funktioniert für alle Felder,
eine andere Möglichkeit wäre

Delphi-Quellcode:

.Cells(x,y):=''''+MeineDaten;

oder

Delphi-Quellcode:

.Cells(x,y):='"'+MeineDaten;

Hierbei hat Excel aber schon mal Schluckauf bekommen, sprich, doch wieder falsch formatiert.

Gruß
K-H

P.S. ist nicht auf meinem Mist gewachsen, da hat es vor 2(?)Jahren schon einmal einen Thread zu gegeben.

[Jumpy]

Ich hatte da auch schon an ein =CHR(39)+Query....AsString gedacht.

shmias Tip muss ich auch mal ausprobieren. Dazu vllt. beim Schreiben der Headline auch mal auf die Datentypen der Felder gucken und die Excel-Spalten entsprechend vorformatieren. Teste ich gleich morgen mal, muss jetzt erstmal das Schulzeugniss abholen, juhu (erstmal) nie wieder Schule.8-)

[Iwo Asnet]

Das man per EXCEL direkt an aus einer DB Daten lesen kann, ist bekannt?

[p80286]

Zitat:

Zitat von Iwo Asnet (Beitrag 1173287)

Das man per EXCEL direkt an aus einer DB Daten lesen kann, ist bekannt?

Nichts für ungut, aber das ist so als würdest Du vorschlagen mit dem Fahrrad von Hamburg nach Lübeck zu fahren. Natürlich geht das, aber alleine die 64kSatzgrenze ist da schon ein Hindernis. Und dann zwickt es hier und da. Das sind zumindestens meine Erfahrungen mit älteren Excel-Versionen.

Und Du schleppst immer die Datenabfrage in Deiner Excel-Datei mit Dir herum. Gebraucht wird die Datei aber nur als Datencontainer.

Gruß
K-H

[Iwo Asnet]

Zitat:

Zitat von p80286 (Beitrag 1173297)

Zitat:

Zitat von Iwo Asnet (Beitrag 1173287)

Das man per EXCEL direkt an aus einer DB Daten lesen kann, ist bekannt?

Nichts für ungut, aber das ist so als würdest Du vorschlagen mit dem Fahrrad von Hamburg nach Lübeck zu fahren. Natürlich geht das, aber alleine die 64kSatzgrenze ist da schon ein Hindernis.

Ich verwende keine Datenmengen > 64k für Reports, weil die keiner lesen kann...

Zitat:

Und dann zwickt es hier und da. Das sind zumindestens meine Erfahrungen mit älteren Excel-Versionen.

Erfahrungen von früher sind aber eher Erinnerungen. Frisch das doch mal auf.

Zitat:

Und Du schleppst immer die Datenabfrage in Deiner Excel-Datei mit Dir herum. Gebraucht wird die Datei aber nur als Datencontainer.

Schlimm?

Übrigens: EXCEL + OLE = Schnecke (vs. 'Fahrrad')

Ich erstelle kleine Reporttemplates mit EXCEL und wenn der DAU doppelklickt, hat er alles, was er braucht. Immer aktuell, ohne sich selbst drum zu kümmern. Das ist dann kein Fahrrad, sondern eher ein Luxusdoppeldecker mit komplettem Innenausbau, Dusche, HiFi-Anlage, Küche und Butler. Nur die Geschwindigkeit... nun ja, Tempo 80. Aber der DAU hat Zeit. Und ich auch, weil ich mir keinen abgebrochen habe, seinem Wunsch nachzukommen.

Will ich EXCEL-Reports aus einer Anwendung heraus erstellen, die auch ohne Live-DB Anbindung funktioniert, verwende ich Komponenten von Axolot-Data oder DevExpress. Damit ist die Formatierung gegessen.

Aber das ist alles OT (Sry).

[omata]

Zitat:

Zitat von Iwo Asnet (Beitrag 1173301)

Aber das ist alles OT (Sry).

@Iwo Asnet: Ist es nicht! Sondern ein Hinweis, wie man effektiv arbeiten kann. Danke für deine tollen Tipps (ich mache es ähnlich, für z.B. Statistiken + DAUs --> einfach unschlagbar!)

[Iwo Asnet]

Zitat:

Zitat von omata (Beitrag 1173305)

Zitat:

Zitat von Iwo Asnet (Beitrag 1173301)

Aber das ist alles OT (Sry).

@Iwo Asnet: Ist es nicht!

Na ja. Die Frage ging eher in Richtung "Formatierung mit OL(L)E EXCEL"

[omata]

Zitat:

Zitat von Iwo Asnet (Beitrag 1173319)

Na ja. Die Frage ging eher in Richtung "Formatierung mit OL(L)E EXCEL"

Ich weiß, aber man kann ja auch mal über den Tellerrand schauen und deshalb fand ich Deinen Hinweis trotzdem sinnvoll. Dein Wegducken am Ende war nicht nötig und das wollte ich nochmal zum Ausdruck bringen :hi:

[Jumpy]

Stimme als TE omata i. Iwo Asnet zu. Der Einwurf ist gerechtfertigt. Wir haben hier u.a. auch ein komplexes Framework für Excel, mit dem man komfortabel Reports generieren kann. Nur passt das für diese Anwendung nicht so ganz. Es handelt sich um ein internes Programm, mit dem man SQL-Statements zusammenbauen und speichern und verwalten kann. Hier kam der Wunsch auf, dass man gelegentlich die durch das gerade gebaute Statement erstellte Datenmenge (via Grid angezeigt) auch mal exporieren können sollte. CSV-Export war schnell umgesetzt und läuft super schnell. Dagegen ist der Excel-Ole-Export wirklich eine Schnecke, aber man will die Daten ja in Excel haben. Daher geh ich jetzt den o.g. Weg und schau jetzt mal, wie ich es formatiert kriege.

Ich denke aber auch mal drüber nach, ob man unser Excel-Framework so erweitern kann, dass man einem vorbereiteten Report ein SQL-Statement übergibt und Excel von da aus weiterarbeitet.

Experimetiere jetzt mal mit dem Formatieren weiter und meld mich dann mal, was bei rausgekommen ist.

[tgvoelker]

Das Problem liegt in der Implementierung: Excel erwartet für Range.Value eine Variante. Wenn Du einen String übergibst, dann wandelt den die Excel-Logik um - und das führt zu den beschriebenen Problemen.

Ich hatte das Problem bei TJVGExportExcel aus JVCL Globus, da war genau so vorgegangen worden.

Die lösung war, die Source zu ändern:

Code:

            if ForceTextFormat or
               (Dataset.Fields[I].Datatype in [ftString,ftMemo,ftFmtMemo,ftFixedChar,ftWideString,ftGuid
                                             ,ftFixedWideChar,ftWideMemo,ftBoolean,ftVariant,ftUnknown
                                             ,ftBytes,ftVarBytes,ftBlob,ftGraphic,ftParadoxOle
                                             ,ftDBaseOle,ftTypedBinary,ftCursor,ftADT,ftArray,ftReference
                                             ,ftDataSet,ftOraBlob,ftOraClob,ftInterface,ftIDispatch,ftOraInterval
                                             ,ftConnection,ftParams,ftStream])or
               (Dataset.Fields[I].IsNull) then Sheet.Cells[RecNo, ColNo + I] := GetFieldValue(DataSet.Fields[I])
            Else Sheet.Cells[RecNo, ColNo + I].Value :=GetFieldValueVar(DataSet.Fields[I]);

In der ursprünglichen Implementierung wurde nur die Funktion GetFieldValue aufgerufen:

Code:

function TJvgCommonExport.GetFieldValue(const Field: TField): string;
begin
  if jeoOutputFormattedStrings in Options then
    Result := Field.DisplayText
  else
    Result := Field.AsString;
  if Assigned(FOnExportField) then
    FOnExportField(Self, Field, Result);

  if FTransliterateRusToEng then
    Result := Transliterate(Result, True);

  if (FMaxFieldSize > 0) and (Field.DataType in [ftString, ftMemo, ftFmtMemo]) then
    if Length(Result) > FMaxFieldSize then
      Result := Copy(Result, 1, FMaxFieldSize) + '...';
end;

Konkret tritt das Problem bei Gleitkommazahlen und BCD auf, weil bei Field.AsString ein Punkt ausgegeben wird, anstelle eines Komma. Die Funktion GetFieldValueVar übergibt daher eine Variante:

Code:

function TJvgCommonExport.GetFieldValueVar(const Field: TField): Variant;
//Erweiterung zum korrekten export von Nicht-Zeichenkettendaten.
var i:Int64;d:DWord;
begin
  if jeoOutputFormattedStrings in Options then Begin
    Result := Field.DisplayText;
    Exit;
  End;
  if Field.DataType in [ftSmallint,ftInteger,ftWord,ftAutoInc,ftShortint,ftByte] then Result:=Field.AsInteger
  Else if Field.Datatype=ftLargeint Then Result:=Field.Value
  Else if Field.DataType=ftLongWord Then Begin
    d:=Field.Value;
    i:=d;
    Result:=i;
  End Else if Field.DataType in [ftFloat,ftExtended] Then Result:=Field.AsExtended
  Else if Field.DataType = ftCurrency Then Result:=Field.AsCurrency
  Else if Field.DataType in [ftBCD,ftFMTBcd] Then Result:=BcdToDouble(Field.AsBCD)
  Else if Field.DataType in [ftDate,ftTime,ftDateTime,ftTimeStamp,ftOraTimeStamp] Then Result:=Field.AsDateTime
  Else Result:=Null;
//  if Assigned(FOnExportField) then FOnExportField(Self, Field, Result); //Abgeschaltet, da hier zwingend ein String erwartet wird.
end;

Allerdings ist hier anzumerken, daß der export von 16T Tupeln mit 60 Spalten ewig dauerte - nach 2 Stunden habe ich den abgebrochen. Verwende nun die Native Excel Suite, da ist der Export dieser Datenmenge nach 20 Sekunden fertig.

[Furtbichler]

Wer in Gottes Namen benötigt 16 Tausend Zeilen mit je 60 Werten?

[tgvoelker]

Rechnungsdaten aus einem Geschäftsjahr.

[Jumpy]

Eine Frage zu #12:

Einmal wird der Zelle etwas zugewiesen via:

Sheet.Cells[RecNo, ColNo + I] :=

im anderen Fall via

Sheet.Cells[RecNo, ColNo + I].value :=

Warum?
Afaik ist .value die Default-Property des Range-Objectes, somit sind die beiden Zuweisungen (auf der linken Seite!) doch gleichwertig, oder?

[p80286]

Rein theoret. hast du recht, aber in der Praxis empfehle ich .Value da Excel und Word sich manchmal nicht an die Theorie halten.


Gruß
K-H

[tgvoelker]

Theoretisch schon. Mein erster Ansatz war daher auch, die "Sonderbehandlung" String/sonstiges nicht durchzuführen, sondern grundsätzlich als Variant zu übergeben. Dabei hatte ich aber das Problem, daß die Zellen, an die Strings übergeben wurden, einfach leer blieben. Im nächsten Schritt habe ich dann den alten Code wieder reingenommen und eine Sonderbehandlung für alles gemacht, das ich nicht als String übergeben wollte. Ich erinnere mich auch dunkel, daß ich zunächst versucht hatte, die Varianten an die Range zuzuweisen und das nicht funktionierte - weshalb ich dann .value verwendete.

Wäre das Zeitverhalten akzeptabler gewesen, dann hätte ich weitergemacht damit, die nächsten Schritte wären dann gewesen, daß Zeichenkettenfelder grundsätzlich im Excel hart als Zeichenkette formatiert worden wären (damit z.B. keine führenden Nullen weggemacht werden) und nur eine Funktion zu verwenden (schätze mal, das Excel mit dem Stringformat, welches ich ursprünglich in der Variante übergeben hatte, nicht klarkam - das wäre also ebenso anzupassen). Und natürlich, um das Zeitverhalten zu verbessern, die Übergabe das Daten Zeilenweise in einem varianten Array. Aber wie schon gesagt war das Ding so langsam, daß ich mich umorientieren mußte und das auch tat.

Edit: KH, das ist sogar ein Feature: in Abhängigkeit der Ausprägung des Range-Objekts variiert die default property. Auch wenn "Daily dose of Excel" hier: http://www.dailydoseofexcel.com/arch...trange-object/

schreibt, das läge am VBA-Parser, so bin ich eher der Ansicht, daß das implementiert wurde (warum auch immer) und zudem versionsabhängig ist.

[p80286]

Zitat:

Zitat von tgvoelker (Beitrag 1173498)

.. und zudem versionsabhängig ist.

Das kann man im Zusammenhang mit Word und Excel nicht laut genug betonen.

Gruß
K-H

[Iwo Asnet]

Es scheint sowohl eine Diskussion um Formatierung und dieses leidige "EXCEL ist klüger als Du und daher interpretiert es für dich die Daten" zu sein.

Ich empfehle die Verwendung von nativen BIFF-Writerklassen. Einige sind Freeware, andere kosten etwas. Prüfe doch, ob die Freewarekomponenten nicht ausreichend sind, deine Bedürfnisse zu erfüllen. Einge XLS-Writer basieren jedoch auf OLE, die kannst Du gleich vergessen.

Wenn man vor dem Problem steht, 2 Stunden auf die Erzeugung einer großen EXCEL-Datei zu warten (z.B. als Basis für BI-Auswertungen), dann wäre z.B. folgende Vorgehensweise relativ schnell umzusetzen und dürfte performant genug sein:
1. CSV-Datei erzeugen
2. EXCEL als OLE starten, CSV einlesen und formatieren
3. Speichern und fertig

Das ist ratzfatz umgesetzt und sollte brauchbare Ergebnisse liefern

Klar, ein Sheet mit 16k Daten als BI-Rechenbasis für die Geschäftsleitung muss schon nach was aussehen, aber so richtig interessiert sich kaum jemand für die einzelnen Daten.

Ich würde ein EXCEL Template erstellen, das alle Auswertungen bereitstellt, die Rohdaten (CSV) auf Sheet999 ;-) per OLE einlesen, hübsch machen (per Makro) und den Klotz speichern. Im Template kann man nachträglich das 'Hübschmachenmakro' erweitern, verändern etc.