Excel und Delphi

[David Martens]

Ich habe einen Excel-Server für den Export geschrieben (mit early binding). Damit kann man aus unserem Programm heraus alle möglichen Änderungen, bis hin zu Pivottabellen und Diagramme erzeugen.

Das Ganze unterstützt mehrere Excel Versionen (97 bis 2007). Nun bin ich aber auf leider auf ein(zwei) Problem(e) gestoßen das sich nicht mehr so einfach lösen lässt:

1. Wichtig:
Seit Version 11 (2003) muß ich die Zahlen, die in die Zellen geschrieben werden, ins !!! englische !!! Format umwandeln. Das währe nicht weiter schlimm, wenn jetzt nicht eine weitere Eigenheit aufgetaucht währe.

Soweit ich jetzt sagen kann, erkennt Excel ab 2003 keine "normalen" Datumswerte mehr. Bei Excel 2000 funktioniert es noch das Datum als "25.01.2010" in die Zelle zu schreiben und Excel erkennt die Eingabe als Datum. Bei Excel 2003 ist es lediglich Text. d.h. keine vernünftige Sortierung, keine Berechnung, nichts.
Als Workaround habe ich das Zellenformat anhepasst, doch her kommt jetzt die Idiotie. Ich muß das deutsche Datumsformat nehmen und das Datum als .AsDateTime (zum Glück ist bei Excel und Delphi die 0 der 1.1.1900) eintragen. Dann erkennt Excel die Werte wieder. Jetzt zum eigentlichen Problem. Das funktioniert aber nur wenn ich das Datumsformat "händisch" eingebe.

Das deutsche Datumsformat ist 'TT.MM.JJJJ', wiekann ich Excel nach "seinem" Datumsformat fragen um das sprachabhänige Format zu bekommen und nicht 'dd.MM.yyyy' .

Mit

Delphi-Quellcode:

GetLocaleInfo(GetUserDefaultLCID, LOCALE_SSHORTDATE, buff, 71)

bekomme ich ja nur das Windowsformat.



2. (noch) unwichtig:
Bis einschließlich Excel 2003 kann ich die "benutzerdefinierten" Diagramme aus der Datei Xl8galry.xls auslesen und bereitstellen. Mit Excel 2007 geht das nicht mehr, da hier das Ganze anders genutzt wird. Die Diagramme liegen jetzt als crtx-Datei unter Userprofile\Anwendungsdaten\Microsoft\Templates\Ch arts.

Es gibt jetzt einer Prozedur TExcelChart.ApplyChartTemplate ABER DIE FUNKTIONERT NICHT.
hat das schon jemand hingekommen?

Danke und Gruß
David

[fishly]

Ich versteh irgendwie dein Problem nicht

Ist das nicht immer so?

Delphi-Quellcode:

Selection.NumberFormat = "dd/mm/yyyy;@"

Ich hab da keinen Unterschied entdeckt, wenn ich zwischen den Gebietsschemata wechsel.

[p80286]

Zitat:

Zitat von David Martens

Soweit ich jetzt sagen kann, erkennt Excel ab 2003 keine "normalen" Datumswerte mehr. Bei Excel 2000 funktioniert es noch das Datum als "25.01.2010" in die Zelle zu schreiben und Excel erkennt die Eingabe als Datum. Bei Excel 2003 ist es lediglich Text. d.h. keine vernünftige Sortierung, keine Berechnung, nichts.

da kann ich Dir nicht ganz zustimmen. Ich habe Excel gerade mit deutschen "Datumsen" gefüttert und es sortiert richtig (Excel 2003 SP2) wenn
"Alles was wie eine Zahl aussieht wie eine Zahl sortieren"
gewählt wird.
Das Excel ziemlich zickig ist und noch nicht einmal ein ISO-Datum versteht lassen wir einmal aussen vor
Gruß
K-H

Edit: So läuft die Fütterung:

Delphi-Quellcode:

  { sg:tStringGrid; }
  { zelle : ansistring; }

  for i:=0 to sg.rowcount-1 do begin
    for j:=0 to sg.colcount-1 do begin
       zelle:=sg.cells[j,i];
       excel.activesheet.cells(i+1,j+1):=zelle;  {cells1..x/1..y }
    end;
  end;

[David Martens]

Also zu 1. nochmal. Ich glaube jetzt habe ich die Antwort, aber wiso das so ist, keine Ahnung?

noch zur Info: damit die Übertragung der Daten schneller geht mache ich folgendes:

Delphi-Quellcode:

var
  Data : OleVariant;
  ...
begin
  ...
  i := 0;
  for iCol := 0 to TDataSource.DataSet.FieldCount - 1 do
  begin
    if TDataSource.DataSet.Fields[iCol].Visible then
      inc(i);
  end;
  ...
  Data := VarArrayCreate([0, TDataSource.DataSet.RecordCount - 1, 0, i - 1], varVariant);
  ...
  while (not TDataSource.DataSet.Eof) do 
  begin  
    ...
    if (FExcelServer.Version.Major >= 11) and          // Excel 2003 und höher
       (iCol = iDatum)                        then     // iDatum ist die Spalte des Datums
    begin
      Data[iRow, iCol - i] := TDataSource.DataSet.Fields.Fields[iCol].AsDateTime
    end
    else
    begin
      Data[iRow, iCol - i] := Trim(TDataSource.DataSet.Fields.Fields[iCol].AsString);
    end;
  end;
  ...
  FExcelServer.WriteRange(FStartRow,
                          FStartCol,
                          TDataSource.DataSet.RecordCount,
                          VisibleFieldCount,
                          Data);
  ...
end;

TDataSource.DataSet.Fields.Fields[iCol].AsDateTime ergibt für heute 40213. Also wird ja eigentlich nur diese Zahl eingetragen, aber Excel scheint es trotzdem zu erkennen.

[David Martens]

@p80286:

Wenn ich Daten vom z.B.: 1.2.2010 bis 1.4.2010 habe und excel die Datumszeile als Text interpretiert, dann ist in der dazugehörigen Pivottabelle die Sortierung:

01.02.2010
01.03.2010
01.04.2010
02.02.2010
02.03.2010
03.02.2010
03.03.2010
...

[David Martens]

Hier zur Anschauung noch die Excel-Dateien

[p80286]

Zitat:

Zitat von David Martens

TDataSource.DataSet.Fields.Fields[iCol].AsDateTime ergibt für heute 40213. Also wird ja eigentlich nur diese Zahl eingetragen, aber Excel scheint es trotzdem zu erkennen.

Jo, und dieses "scheint" kann man nicht oft genug unterstreichen. Excel 2003 interpretiert irgendeinen Zahlenbereich als Datum, ich hab noch nicht herausbekommen welchen. (na gut bisher hab ich mich auch nur geärgert, und dann Text übergeben.)

ich übergebe folgende Strings

Code:

1234567
1236
66/56789

und Excel macht daraus:

Code:

     1234567
  28.05.1976
66/56789

find ich nicht witzig!

Zitat:

... und excel die Datumszeile als Text interpretiert ...

Genau da liegt ja der Hase im Pfeffer.
Als workaround würde ich das ISO-Datum (YYYYMMDD) vorschlagen, das wird als Zahl und als Text immer richtig sortiert.


Gruß
K-H

NochnNachtrag:
damit Die Datumsberechnung funktioniert darf unter Optionen > Berechnung der 1904-Datumswert nicht gesetzt sein!

[Chemiker]

Hallo David Martens,

Dazu folgenden Hinweis:
VBA rechnet intern mit dem amerikanischen Format. Wenn Du also die richtigen Sortierung bekommen willst solltest Du das Datum in amerikanischer Schreibweise übertragen: Monat/Tag/Jahr.

Die Berechnungen und auch Sortierungen in VBA erfolgen im amerikanischen Format. Wogegen die Ausgabe der Daten von der Windows-Systemeinstellung abhängig ist.

Bis bald Chemiker

[Amateurprofi]

Zitat:

Zitat von David Martens

(zum Glück ist bei Excel und Delphi die 0 der 1.1.1900) eintragen.

Bist du sicher ?

Meines Wissens ist das so

Excel : 0 = 00.01.1900, 1 = 01.01.1900
Delphi: 0 = 30.12.1899, 1 = 31.12.1899

[p80286]

Zitat:

Zitat von Chemiker

Hallo David Martens,

Dazu folgenden Hinweis:
VBA rechnet intern mit dem amerikanischen Format. ...

Bist Du Dir da sicher? Ich traue Microsoft zwar vieles zu aber das nicht.
Daß Funktionen das Datum im amerikanischen Format erwarten glaube ich gerne, aber rechnen?

Gruß
K-H

[Chemiker]

Hallo p80286,

wenn Du folgendes Modul in EXCEL anlegst:

Sub DatumZeitSchreiben()
Debug.Print #12/31/2010#
Debug.Print #6:15:00 AM#
Debug.Print #6:15:00 PM#
Debug.Print CDate("31.12.2010")
End Sub

Dann werden die Daten so ausgegeben:

31.12.2010
06:15:00
18:15:00
31.12.2010

Bis bald Chemiker

[p80286]

hallo Chemiker,
entschuldige das ich nochmals widerspreche,
du übergibst Zeit-Werte in einem bestimmten Format (US-amerikanisch?) aber die interne Berechnung sollte wohl nicht in diesem Format erfolgen. Wenn ich der Excel-Hilfe folge, können belibige numerische Werte als Datum angezeigt (interpretiert) werden:

Zitat:

Code:

Format Beschreibung 
General Date  Zeigt ein Datum und/oder eine Zeit an. Bei reellen Zahlen werden Datum und Uhrzeit angezeigt
             (zum Beispiel 4.3.93 05:34). Werden keine Nachkommastellen angegeben, so wird nur ein Datum
             (zum Beispiel 4.3.93) angezeigt. Enthält der Wert ausschließlich Nachkommastellen, so wird nur eine
              Uhrzeit ausgegeben (zum Beispiel 05:34).
              Die Anzeige von Datum und Zeit wird durch die Systemeinstellungen festgelegt. 
Long Date     Zeigt ein Datum im langen Datumsformat entsprechend den Systemeinstellungen an. 
Medium Date   Zeigt ein Datum im mittleren Datumsformat an, das von der Sprachversion der Host-Anwendung
              bestimmt wird. 
Short Date    Zeigt ein Datum im kurzen Datumsformat entsprechend den Systemeinstellungen an. 
Long Time     Zeigt eine Zeit entsprechend der Einstellung für das lange Zeitformat an, einschließlich
              Stunden, Minuten und Sekunden. 
Medium Time   Zeigt eine Zeit im 12-Stunden-Format mit Stunden, Minuten und einer AM/PM-Kennung an. 
Short Time    Zeigt eine Zeit im 24-Stunden-Format an (zum Beispiel 17:45).

Man muß Excel also nur dazu bringen die übergebenen Werte richtig zu interpretieren.
Gruß
K-H

[bit4bit]

Hallo David,

sehr interessant Dein Projekt, sowas könnte ich auch gebrauchen.

Meines Wissens wandelt Excel die eingegebenen bzw. aus csv-Dateien importierten Werte entweder implizit oder explizit um. Excel-intern wird dabei immer mit Real-Zahlen (Tage und Bruchteile davon seit dem Basisdatum) gearbeitet.

Implizit: Kann aus der Schreibweise des Wertes auf ein Datum/Uhrzeit oder beides geschlossen werden, wird der Wert in eine Tageszahl umgerechnet und das Format der Zelle auf Datum/Uhrzeit gesetzt.

Explizit: Wird eine Spalte komplett als Datum definiert, werden alle Werte als Datum/Uhrzeit interpretiert, soweit dabei ein gültiger Wert ermittelt werden kann.

Fazit: Da man nie sicher sein kann, welches Datum als Nullpunkt definiert ist, sollte man Datumswerte und Uhrzeiten immer als in internationaler Schreibweise/ISO 8601 formatierten Wert angeben (z.B. "2010-02-08 20:30") und vorher der entsprechenden Spalte das gewünschte Datumsformat zuweisen. Funktioniert mit Excel-V7 (1995) genauso wie mit Open Office V3 .

Können wir Deine Funktion dann irgendwann mal als Unit bewundern?

bit4bit

[p80286]

ich war so frei und hab ein wenig getestet:

Delphi-Quellcode:

....
   { Datumsformat "erzwingen" }
  excel.activesheet.Cells.NumberFormat := 'TT.MM.JJJJ';
   { Werte schreiben }
  excel.activesheet.cells(ez+1,j+1):=zelle;  {cells1..x/1..y }
....

folgende Werte wurden übergeben:

Memo1
0001
19770602
20170
22.03.1955
123456

und das hat Excel daraus gemacht:

Code:

Memo1                           <-- Text
    01.01.1900                  <-- Datum
#########################       <-- negativer Datumswert
    22.03.1955                  <-- Datum
22.03.1955                      <-- Text
    03.01.2238                  <-- Datum

Wenn man erreichen will, das Excel ein Datum als Datum erkennt, sollte man es tunlichst vermeiden, es in einem "Datumsformat" zu übergeben.

Gruß
K-H

[David Martens]

@Amateurprofi:
Das habe ich bei beiden getestet: heutiges Datum angeben -> im "Delphiformat" and Exfel übergeben -> in Excel steht das heutige Datum.
Bei beiden die 0 angegeben -> bei beiden steht das gleiche Datum
____________________________
Ja Excel und Delphi haben den gleichen Stichtag

@bit4bit:
Das explizite Angeben des Formates mach ich schon und habe damit ab Version 2003 mein Problem. Aber ich probier mal die ISO8601 schreibweise, leuchtet ein.
Noch kurz zum Projekt: Der Export-teil ist ja nur ein ganz Kleiner. Wenn ich mehr Zeit hätte, würde ich den "Excel-Server" neu schreiben. Momentan ist ALLES in einer Klasse. Viel eleganter wäre ein Aufteilung in die Excel-OLE-Klassen, aber darauf habe ich sowas von keine Lust, weil MS NULL Information darüber rausgibt. Siehe mein 2. Problem.
Aber wenn du interesse hast, PM an mich.

@all:
Es geht auch eigentlich nicht um die interne Berechnung, sondern vielmehr um die "Sprache" der OLE-Schnittstelle. Als Indiz habe ich ja die Zahlen mit angeführt, aber ich kann mir nicht sicher sein, ob das italienische Excel das auch hat, oder ob das nur ein Bug der deutschen Version ist.

@Chemiker und p80286:
VBA rechnet intern nicht mit dem amerikanischen Format, wohl aber die OLE-Schnittstelle seit Version 2003. Intern wird logischer weise mit Zahlen (Tage seit Stichtag) gerechnet. Weil Excel da einen Unterschied macht kommt man mit VBA da auch nicht mehr weiter.

[Chemiker]

Hallo David Martens,

ich habe da meine eigenen Erfahrungen. Wenn nach einem Datum filtriert wird, muss das US-Datumsformat berücksichtigt werden, sonst kommt da Unsinn raus.

Wenn an Excel Daten übertragen werden sollen, sollte man Excel auch mitteilen um welche Daten es sich handelt, und nicht wundern wenn Text übertragen wird das Excel auch Text in den Zellen rein schreibt.

Mit diesem Code lassen sich Problemlos die Daten aus einem StringGrid nach EXCEL übertragen:

Delphi-Quellcode:

procedure TForm1.btnExcelExportClick(Sender: TObject);
var i, j:integer;
    Text: String;
    Zahl : Extended;
    Datum: TDateTime;
begin
  for i:=0 to StringGrid1.rowcount-1 do
  begin
    for j:=0 to StringGrid1.colcount-1 do
    begin
      Text:=StringGrid1.cells[j,i];
      if TryStrToFloat(Text, Zahl) then
      begin
        olevEXCEL.cells[i+1,j+1].Value:= Zahl
      end
      else
      begin
        if TryStrToDate(Text,Datum) then
        begin
          olevEXCEL.cells[i+1,j+1].Value := Date;
        end
        else
        begin
          olevEXCEL.cells[i+1,j+1].Value := Text;
        end;
      end;
     end;
  end;
end;

Bis bald Chemiker

[David Martens]

@Chemiker:

1. Ich benutze early binding um wenigstens etwas Kontrolle zu behalten. Dein Beispiel ist late binding.

2. Im Endeffekt mach ich auch genau das was du vorgeschlagens hast:

Delphi-Quellcode:

Data := VarArrayCreate([0, TDataSource.DataSet.RecordCount - 1, 0, i - 1], varVariant);
...
Data[iRow, iCol - i] := TDataSource.DataSet.Fields.Fields[iCol].AsDateTime;

Data wird dann direkt an Excel übergeben. Wenn ich jede Zelle einzeln nach Excel schreiben würde dauert das bei 10000 Werten mal locker 5 Minuten (abhänig von System). Das ist einfach inakzeptabel.

Hast du deinen Code mal mit Excel 2003 aufwärs probiert. Ich glaube da hast du auch ein Problem.

Gruß David

[Chemiker]

Hallo David Martens,

1. Dir ist aber bewusst das manche Befehle dann unter verschieden EXCEL-Versionen unterschiedliche Parameteranzahl haben und nicht ohne weiteres kompatibel sind.

2. Wenn es sich um 10000 Werte handelt würde ich sie in eine BIFF-Datei ablegen und anschließend mit EXCEL laden(selber übertrage ich damit ca. 100.000 Datensätze aus einer Firebird Datenbank in eine EXCEL-Datei täglich).

3. Der Code ist mit Excel 2007 und 2010 getestet dort gibt es damit keine Probleme.

Bis bald Chemiker