Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Excel without OLE unicode


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 7 - Last Post: Oct 8, 2014 5:56 AM Last Post By: Robert Triest
mohamed alayadi

Posts: 17
Registered: 8/26/02
Excel without OLE unicode  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 30, 2014 3:44 AM
Hello,

In Delphi 2007 I am using this class http://users.atw.hu/delphicikk/listaz.php?id=1982&oldal=52 to export to Excel without having MS Office installed.

In XE5 the same unit class gives a result not the same, with only the first letter for string fields showing. Numeric fields show OK

What should be changed to correct?

Thnx
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Excel without OLE unicode  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 30, 2014 4:37 AM   in response to: mohamed alayadi in response to: mohamed alayadi
The link you are accessing has been blocked because it contains spyware. The name of the spyware is: Spyware.Exploit.Misc.MU

If the download contains the sources then see if they already have converted
the source to Unicode (sources for Delphi 2009 upwards) If not then you have to
do the Unicode conversion yourself.
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Excel without OLE unicode  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 30, 2014 9:29 AM   in response to: Robert Triest in response to: Robert Triest
Robert Triest wrote:

The link you are accessing has been blocked because it contains
spyware. The name of the spyware is: Spyware.Exploit.Misc.MU

If the download contains the sources then see if they already have
converted the source to Unicode (sources for Delphi 2009 upwards) If
not then you have to do the Unicode conversion yourself.

I opened the page using Firefox without a problem (I have set firefox
to not open images). The code on the page is written for Delphi prior
D2009 (expects string = ansistring).
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Excel without OLE unicode  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 30, 2014 10:04 AM   in response to: mohamed alayadi in response to: mohamed alayadi
A quick conversion (note this will export ANSI data - I don't know
the format of the xls, thus was unable to extend it to unicode):

unit mathExcel;
 
interface
 
 
uses Windows, SysUtils, DB, Math;
 
//
========================================================================
=====
// TDataSet to Excel without OLE or Excel required
// Mike Heydon Dec 2002
//
========================================================================
=====
 
type
  // TDataSetToExcel
  TDataSetToExcel = class(TObject)
  protected
    procedure WriteToken(AToken: word; ALength: word);
    procedure WriteFont(const AFontName: Ansistring; AFontHeight,
      AAttribute: word);
    procedure WriteFormat(const AFormatStr: Ansistring);
  private
    FRow: word;
    FDataFile: file;
    FFileName: string;
    FDataSet: TDataSet;
  public
    constructor Create(ADataSet: TDataSet; const AFileName: string);
    function WriteFile: boolean;
  end;
 
  //
------------------------------------------------------------------------
-----
implementation
 
const
  // XL Tokens
  XL_DIM = $00;
  XL_BOF = $09;
  XL_EOF = $0A;
  XL_DOCUMENT = $10;
  XL_FORMAT = $1E;
  XL_COLWIDTH = $24;
  XL_FONT = $31;
 
  // XL Cell Types
  XL_INTEGER = $02;
  XL_DOUBLE = $03;
  XL_STRING = $04;
 
  // XL Cell Formats
  XL_INTFORMAT = $81;
  XL_DBLFORMAT = $82;
  XL_XDTFORMAT = $83;
  XL_DTEFORMAT = $84;
  XL_TMEFORMAT = $85;
  XL_HEADBOLD = $40;
  XL_HEADSHADE = $F8;
 
  // ========================
  // Create the class
  // ========================
 
constructor TDataSetToExcel.Create(ADataSet: TDataSet;
  const AFileName: string);
begin
  FDataSet := ADataSet;
  FFileName := ChangeFileExt(AFilename, '.xls');
end;
 
// ====================================
// Write a Token Descripton Header
// ====================================
 
procedure TDataSetToExcel.WriteToken(AToken: word; ALength: word);
var
  aTOKBuffer: array[0..1] of word;
begin
  aTOKBuffer[0] := AToken;
  aTOKBuffer[1] := ALength;
  Blockwrite(FDataFile, aTOKBuffer, SizeOf(aTOKBuffer));
end;
 
// ====================================
// Write the font information
// ====================================
 
procedure TDataSetToExcel.WriteFont(const AFontName: ansistring;
  AFontHeight, AAttribute: word);
var
  iLen: byte;
begin
  AFontHeight := AFontHeight * 20;
  WriteToken(XL_FONT, 5 + length(AFontName));
  BlockWrite(FDataFile, AFontHeight, 2);
  BlockWrite(FDataFile, AAttribute, 2);
  iLen := length(AFontName);
  BlockWrite(FDataFile, iLen, 1);
  BlockWrite(FDataFile, AFontName[1], iLen);
end;
 
// ====================================
// Write the format information
// ====================================
 
procedure TDataSetToExcel.WriteFormat(const AFormatStr: ansistring);
var
  iLen: byte;
begin
  WriteToken(XL_FORMAT, 1 + length(AFormatStr));
  iLen := length(AFormatStr);
  BlockWrite(FDataFile, iLen, 1);
  BlockWrite(FDataFile, AFormatStr[1], iLen);
end;
 
// ====================================
// Write the XL file from data set
// ====================================
 
function TDataSetToExcel.WriteFile: boolean;
var
  bRetvar: boolean;
  aDOCBuffer: array[0..1] of word;
  aDIMBuffer: array[0..3] of word;
  aAttributes: array[0..2] of byte;
  i: integer;
  iColNum,
    iDataLen: byte;
  sStrData: string;
  fDblData: double;
  wWidth: word;
  sStrBytes: TBytes;
begin
  bRetvar := true;
  FRow := 0;
  FillChar(aAttributes, SizeOf(aAttributes), 0);
  AssignFile(FDataFile, FFileName);
 
  try
    Rewrite(FDataFile, 1);
    // Beginning of File
    WriteToken(XL_BOF, 4);
    aDOCBuffer[0] := 0;
    aDOCBuffer[1] := XL_DOCUMENT;
    Blockwrite(FDataFile, aDOCBuffer, SizeOf(aDOCBuffer));
 
    // Font Table
    WriteFont('Arial', 10, 0);
    WriteFont('Arial', 10, 1);
    WriteFont('Courier New', 11, 0);
 
    // Column widths
    for i := 0 to FDataSet.FieldCount - 1 do
    begin
      wWidth := (FDataSet.Fields[i].DisplayWidth + 1) * 256;
      if FDataSet.FieldDefs[i].DataType = ftDateTime then
        inc(wWidth, 2000);
      if FDataSet.FieldDefs[i].DataType = ftDate then
        inc(wWidth, 1050);
      if FDataSet.FieldDefs[i].DataType = ftTime then
        inc(wWidth, 100);
      WriteToken(XL_COLWIDTH, 4);
      iColNum := i;
      BlockWrite(FDataFile, iColNum, 1);
      BlockWrite(FDataFile, iColNum, 1);
      BlockWrite(FDataFile, wWidth, 2);
    end;
 
    // Column Formats
    WriteFormat('General');
    WriteFormat('0');
    WriteFormat('###,###,##0.00');
    WriteFormat('dd-mmm-yyyy hh:mm:ss');
    WriteFormat('dd-mmm-yyyy');
    WriteFormat('hh:mm:ss');
 
    // Dimensions
    WriteToken(XL_DIM, 8);
    aDIMBuffer[0] := 0;
    aDIMBuffer[1] := Min(FDataSet.RecordCount, $FFFF);
    aDIMBuffer[2] := 0;
    aDIMBuffer[3] := Min(FDataSet.FieldCount - 1, $FFFF);
    Blockwrite(FDataFile, aDIMBuffer, SizeOf(aDIMBuffer));
 
    // Column Headers
    for i := 0 to FDataSet.FieldCount - 1 do
    begin
//      sStrData := FDataSet.Fields[i].DisplayName;
      sStrBytes :=
TEncoding.ANSI.GetBytes(FDataSet.Fields[i].DisplayName);
      iDataLen := length(sStrBytes);
      WriteToken(XL_STRING, iDataLen + 8);
      WriteToken(FRow, i);
      aAttributes[1] := XL_HEADBOLD;
      aAttributes[2] := XL_HEADSHADE;
      BlockWrite(FDataFile, aAttributes, SizeOf(aAttributes));
      BlockWrite(FDataFile, iDataLen, SizeOf(iDataLen));
      if iDataLen > 0 then
        BlockWrite(FDataFile, sStrBytes[0], iDataLen);
      aAttributes[2] := 0;
    end;
 
    // Data Rows
    while not FDataSet.Eof do
    begin
      inc(FRow);
 
      for i := 0 to FDataSet.FieldCount - 1 do
      begin
        case FDataSet.FieldDefs[i].DataType of
          ftBoolean,
            ftWideString,
            ftFixedChar,
            ftString:
            begin
//              sStrData := FDataSet.Fields[i].AsString;
 
sStrBytes:=TEncoding.ANSI.GetBytes(FDataSet.Fields[i].AsString);
              iDataLen := length(sStrBytes);
              WriteToken(XL_STRING, iDataLen + 8);
              WriteToken(FRow, i);
              aAttributes[1] := 0;
              BlockWrite(FDataFile, aAttributes, SizeOf(aAttributes));
              BlockWrite(FDataFile, iDataLen, SizeOf(iDataLen));
              if iDataLen > 0 then
                BlockWrite(FDataFile, sStrBytes[0], iDataLen);
            end;
 
          ftAutoInc,
            ftSmallInt,
            ftInteger,
            ftWord,
            ftLargeInt:
            begin
              fDblData := FDataSet.Fields[i].AsFloat;
              iDataLen := SizeOf(fDblData);
              WriteToken(XL_DOUBLE, 15);
              WriteToken(FRow, i);
              aAttributes[1] := XL_INTFORMAT;
              BlockWrite(FDataFile, aAttributes, SizeOf(aAttributes));
              BlockWrite(FDataFile, fDblData, iDatalen);
            end;
 
          ftFloat,
            ftCurrency,
            ftBcd:
            begin
              fDblData := FDataSet.Fields[i].AsFloat;
              iDataLen := SizeOf(fDblData);
              WriteToken(XL_DOUBLE, 15);
              WriteToken(FRow, i);
              aAttributes[1] := XL_DBLFORMAT;
              BlockWrite(FDataFile, aAttributes, SizeOf(aAttributes));
              BlockWrite(FDataFile, fDblData, iDatalen);
            end;
 
          ftDateTime:
            begin
              fDblData := FDataSet.Fields[i].AsFloat;
              iDataLen := SizeOf(fDblData);
              WriteToken(XL_DOUBLE, 15);
              WriteToken(FRow, i);
              aAttributes[1] := XL_XDTFORMAT;
              BlockWrite(FDataFile, aAttributes, SizeOf(aAttributes));
              BlockWrite(FDataFile, fDblData, iDatalen);
            end;
 
          ftDate:
            begin
              fDblData := FDataSet.Fields[i].AsFloat;
              iDataLen := SizeOf(fDblData);
              WriteToken(XL_DOUBLE, 15);
              WriteToken(FRow, i);
              aAttributes[1] := XL_DTEFORMAT;
              BlockWrite(FDataFile, aAttributes, SizeOf(aAttributes));
              BlockWrite(FDataFile, fDblData, iDatalen);
            end;
 
          ftTime:
            begin
              fDblData := FDataSet.Fields[i].AsFloat;
              iDataLen := SizeOf(fDblData);
              WriteToken(XL_DOUBLE, 15);
              WriteToken(FRow, i);
              aAttributes[1] := XL_TMEFORMAT;
              BlockWrite(FDataFile, aAttributes, SizeOf(aAttributes));
              BlockWrite(FDataFile, fDblData, iDatalen);
            end;
 
        end;
      end;
 
      FDataSet.Next;
    end;
 
    // End of File
    WriteToken(XL_EOF, 0);
    CloseFile(FDataFile);
  except
    bRetvar := false;
  end;
 
  Result := bRetvar;
end;
 
end.
mohamed alayadi

Posts: 17
Registered: 8/26/02
Re: Excel without OLE unicode  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 2, 2014 3:33 AM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Have you changed somthing?
It does no function yet.

If someone could indicate what should be changed..

Lajos Juhasz wrote:
A quick conversion (note this will export ANSI data - I don't know
the format of the xls, thus was unable to extend it to unicode):
mohamed alayadi

Posts: 17
Registered: 8/26/02
Re: Excel without OLE unicode  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 8, 2014 3:11 AM   in response to: mohamed alayadi in response to: mohamed alayadi
Noone can help on this issue?
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Excel without OLE unicode  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 8, 2014 3:56 AM   in response to: mohamed alayadi in response to: mohamed alayadi
As I said in my reply: If the download contains the sources then see if they already have converted
the source to Unicode (sources for Delphi 2009 upwards) If not then you have to do the Unicode
conversion yourself. (or find someone that want to do it for you..)
In the code above you will see that Lajos changed Col headers and the string type of the FDataSet.FieldDefs[i].DataType ,
but you have to see if this is the only code that needs modification..

http://www.embarcadero.com/kr/rad-in-action/migration-upgrade-center
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Excel without OLE unicode  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 8, 2014 5:56 AM   in response to: mohamed alayadi in response to: mohamed alayadi
OK, I just exported a table from a database to an Excel file with the code above and it's working good.
But my data table only contains VarChar fields without any Unicode. Probably you have Unicode fields
in the database.

var XL : TDataSetToExcel;
begin
  XL := TDataSetToExcel.Create(Query1,'c:\temp\test.xls');
  XL.WriteFile;
  XL.Free;
end;
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02