Solved

Creating excel file using delphi

Posted on 2003-11-12
22
1,550 Views
Last Modified: 2010-04-05
How can i create an excel file using delphi
examples:

 IDNO    TOTAL    AMOUNT
    01        1           1000
    02        1            500
    03       10          2000

I like to save my richedit text as excel file format.
0
Comment
Question by:girlswants_me
  • 10
  • 10
  • +1
22 Comments
 

Author Comment

by:girlswants_me
ID: 9737499
anybody can help?
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9737526
girlswants_me,
  There are components that would let you make excel files, but what I usually do for such simple conversions is to save the contents as a csv file. Excel happily opens csv files.
  So, if that is acceptable, here's the code:

procedure TForm1.Button1Click(Sender: TObject);
Var
  MyStrings, MyRows: TStringList;

Begin
  MyStrings := TStringList.Create;
  MyRows := TStringList.Create;
  MyRows.Delimiter := ' ';
  For i := 0 to RichEdit1.Lines.Count -1 Do
  Begin
    MyRows.DelimitedText := RichEdit1.Lines[i];
    MyStrings.Add(MyRows.CommaText);//convert richedit line to csv format
  End;
  MyStrings.SaveToFile('C:\MyExcel.csv');
  MyRows.Free;
  MyStrings.Free;

end;
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9737529
OK, copy-paste mistake:
procedure TForm1.Button1Click(Sender: TObject);
Var
  MyStrings, MyRows: TStringList;
  i: integer;//forgot this in earlier post.
Begin
  MyStrings := TStringList.Create;
  MyRows := TStringList.Create;
  MyRows.Delimiter := ' ';
  For i := 0 to RichEdit1.Lines.Count -1 Do
  Begin
    MyRows.DelimitedText := RichEdit1.Lines[i];
    MyStrings.Add(MyRows.CommaText);//convert richedit line to csv format
  End;
  MyStrings.SaveToFile('C:\MyExcel.csv');
  MyRows.Free;
  MyStrings.Free;

end;
0
 

Author Comment

by:girlswants_me
ID: 9737560
MyRows.Delimiter := ' ';
and
MyRows.DelimitedText := RichEdit1.Lines[i];

"Delimited" is this a property?
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9737575
Delimiter, DelimitedText, CommaText are properties of TStrings.

From Delphi help:

Use Delimiter to get or set the delimiter used by the DelimitedText property. DelimitedText represents all of the strings in the TStrings object as a single string, with individual strings separated by the character that is the value of Delimiter.

Use QuoteChar to get or set the quote character that is used to enclose individual strings in the DelimitedText property.

Use DelimitedText to get or set all the strings in the TStrings object in a single string.

When reading DelimitedText, the resulting value delimits individual strings in two ways: each string is surrounded (before and after) by the quote character specified by the QuoteChar property. In addition, individual strings are separated by the character specified by the Delimiter property.

When writing DelimitedText, individual strings must be separated using QuoteChar at both ends, using Delimiter as a separator, or using both these methods.

Note:      CommaText is the same as the DelimitedText property when Delimiter is ',' and QuoteChar is '"'.


:-)
...Snehanshu
0
 
LVL 1

Expert Comment

by:JackNaif
ID: 9741850
If you really want to do it with Excel, you can do it through COM/Automation.

It's not that complicated.

A small demo programme that show almost everything you can do with Excel through Delphi can be found at:

http://www.phidels.com/php/zip/oleexcel.zip

Sources and captions are pretty self explanatory but in French...

If your French is enough to understand "ouvrir" (open), "lire" (read), and "fermer" (close) you should pretty much understand all of it.

Another good source of info is Deborah Pate's site:
http://www.djpate.freeserve.co.uk/Automation.htm  

Hope it helps:

Jack

 
0
 

Author Comment

by:girlswants_me
ID: 9744152
I already save the text by (sample.csv), when i open excel it should be like this

IDNO    TOTAL    AMOUNT
  0001        1           1000
  0002        1            500
  0003       10          2000

Not like this

IDNO    TOTAL    AMOUNT
  0001        1           1000
  0002        1            500
  0003       10          2000

If you may see the values of the IDNO the number values dont have anymore '0' on it.
how would i make this as '0001'.

0
 

Author Comment

by:girlswants_me
ID: 9744161
I already save the text by (sample.csv), when i open excel it should be like this

IDNO    TOTAL    AMOUNT
  0001        1           1000
  0002        1            500
  0003       10          2000

Not like this

IDNO    TOTAL    AMOUNT
  1        1           1000
  2        1            500
  3       10          2000

If you may see the values of the IDNO the number values dont have anymore '0' on it.
how would i make this as '0001'.
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9745300
girlswant_me
  For changing the format in which numbers are displayed, you could
1) Add a quote (') before the text to display it as it is for a CSV file, which means you would need to convert all 0 columns to 4 digit, but this is not a very good idea.
2) Create an xls file and change the number format of the cells.

#1 is simple, and here's the code for #2 (you would need to add ComObj in the uses clause:

procedure TForm1.Button1Click(Sender: TObject);
var
  excel:Variant;
  MyStrings, MyRows: TStringList;
  i, j: integer;

begin
  try
    excel:=CreateOleObject('Excel.Application');
  except
    ShowMessage('Error...');
    exit;
  end;
  excel.Visible:=true;
  excel.Workbooks.Add;

  MyStrings := TStringList.Create;
  MyRows := TStringList.Create;
  MyRows.Delimiter := ' ';
  For i := 0 to RichEdit1.Lines.Count -1 Do
  Begin
    MyRows.DelimitedText := RichEdit1.Lines[i];
    MyStrings.Add(MyRows.CommaText);//convert richedit line to csv format
    For j := 0 to MyRows.Count -1 Do
    Begin
      if j = 0 then
        excel.Cells[i+1,j+1].NumberFormat := '0000';//adds leading zeros for first column
      excel.Cells[i+1,j+1].Value := MyRows[j];//i+1 because excel rows/columns begin from 1

    End;
  End;
  MyStrings.SaveToFile('C:\MyExcel.csv');
  MyRows.Free;
  MyStrings.Free;

//example of changing font etc.:
//  excel.Cells[1,1].Value:='Excel - Test 1.';
//  excel.Cells[1,1].Font.Bold := true; {set bold style}
//  excel.Cells[1,1].Font.Size := 20; {set size to 20}
//  excel.Cells[2,1].Value:='Excel - Test 2.';
//  excel.Cells[2,1].Font.Italic := True; {set italic style}
//  excel.Cells[1,1].Font.Size := 15; {set size to 15}
  excel.ActiveWorkbook.SaveAs('c:\test1.xls'); //Save the active workbook as c:\test1.xlx
  excel.Quit; //Terminate Excel application
end;


Cheers!
...Snehanshu
0
 

Author Comment

by:girlswants_me
ID: 9745405
MyRows.Delimiter := ' '; <<------ error
i still got this message: Undeclared identifier: 'Delimiter'
0
 

Author Comment

by:girlswants_me
ID: 9745421
I am using Delphi 3, and i dont like the idea of opening the excel application.
saving the text file with .cvs is good but i want to have the first '000' to appear on every cell.
when im going to open in my excel application.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 5

Expert Comment

by:snehanshu
ID: 9745758
girlswants_me
  I don't know whether or not delphi 3 supports delimiter property: It does so in my D6 and D7.
  But if you do want to go ahead with the CSV thigy, then here's what you could do:
1) Write your own function that replaces spaces by comma in the string (or use StringReplace if that is supported: you may need to add "SysUtils" in uses if it is not already there)
2) Don't use MyRows. Instead, for example, do something like:

procedure TForm1.Button1Click(Sender: TObject);
Var
  MyStrings: TStringList;
  MyCSVStr, MyFirst: String;
  i, num, oldlen: integer;//forgot this in earlier post.
Begin
  MyStrings := TStringList.Create;
  For i := 0 to RichEdit1.Lines.Count -1 Do
  Begin
    MyCSVStr := Trim(RichEdit1.Lines[i]);
    MyCSVStr := StringReplace(MyCSVStr, ' ', ',', [rfReplaceAll]);

//for multiple spaces:

    Repeat
      oldlen := length(MyCSVStr);
      MyCSVStr := StringReplace(MyCSVStr, ',,', ',', [rfReplaceAll]);
    until oldlen = length(MyCSVStr);

    MyFirst := Copy(MyCSVStr, 1, pos(',',MyCSVStr)-1);
    If TryStrToInt(myFirst, num) Then
    begin
      MyCSVStr := StringReplace(MyCSVStr, MyFirst, Format('''%.4d',[num]), []);//replace only the first
    End;

    MyStrings.Add(MyCSVStr);//convert richedit line to csv format

  End;
  MyStrings.SaveToFile('C:\MyExcel.csv');
  MyStrings.Free;

end;
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9745997
girlswants_me,
  I downloaded Delphi 3 help files and it seems that StringReplace and TryStrToInt are not supported.
  Now, here's the code for these functions copy-pasted for you. Now, it should work :-)


procedure TForm1.Button2Click(Sender: TObject);
type
  TMyReplaceFlags = set of (rfReplaceAll, rfIgnoreCase);

Var
  MyStrings: TStringList;
  MyCSVStr, MyFirst: String;
  i, num, oldlen: integer;//forgot this in earlier post.

function MyTryStrToInt(const S: string; out Value: Integer): Boolean;
var
  E: Integer;
begin
  Val(S, Value, E);
  Result := E = 0;
end;

function MyStringReplace(const S, OldPattern, NewPattern: string;
  Flags: TMyReplaceFlags): string;

var
  SearchStr, Patt, NewStr: string;
  Offset: Integer;
begin
  if rfIgnoreCase in Flags then
  begin
    SearchStr := AnsiUpperCase(S);
    Patt := AnsiUpperCase(OldPattern);
  end else
  begin
    SearchStr := S;
    Patt := OldPattern;
  end;
  NewStr := S;
  Result := '';
  while SearchStr <> '' do
  begin
    Offset := AnsiPos(Patt, SearchStr);
    if Offset = 0 then
    begin
      Result := Result + NewStr;
      Break;
    end;
    Result := Result + Copy(NewStr, 1, Offset - 1) + NewPattern;
    NewStr := Copy(NewStr, Offset + Length(OldPattern), MaxInt);
    if not (rfReplaceAll in Flags) then
    begin
      Result := Result + NewStr;
      Break;
    end;
    SearchStr := Copy(SearchStr, Offset + Length(Patt), MaxInt);
  end;
end;


Begin
  MyStrings := TStringList.Create;
  For i := 0 to RichEdit1.Lines.Count -1 Do
  Begin
    MyCSVStr := Trim(RichEdit1.Lines[i]);
    MyCSVStr := MyStringReplace(MyCSVStr, ' ', ',', [rfReplaceAll]);

//for multiple spaces:

    Repeat
      oldlen := length(MyCSVStr);
      MyCSVStr := MyStringReplace(MyCSVStr, ',,', ',', [rfReplaceAll]);
    until oldlen = length(MyCSVStr);

    MyFirst := Copy(MyCSVStr, 1, pos(',',MyCSVStr)-1);
    If MyTryStrToInt(myFirst, num) Then
    begin
      MyCSVStr := MyStringReplace(MyCSVStr, MyFirst, Format('''%.4d',[num]), []);
    End;

    MyStrings.Add(MyCSVStr);//convert richedit line to csv format

  End;
  MyStrings.SaveToFile('C:\MyExcel.csv');
  MyStrings.Free;

end;
0
 

Author Comment

by:girlswants_me
ID: 9746121
when i opened the MyExcel.csv the result of the numbers is this '0002
how should i get the ( ' ) of the  ( 0002 )?

0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9746175
girlswants_me,
  If you remove ' then you cannot see the leeding zeros in the CSV file.
  So, the choice is yours:
1) You can use excel and change the numberformat
 or
2) Stick with CSV and
  2.1) Either have '  along with leading zeros
  2.2) Or forget the leading zeros.

...Snehanshu
0
 

Author Comment

by:girlswants_me
ID: 9746415
That is what my problem is. The one who is going to use the .cvs file is just going to copy paste the whole cell so if the ( ' ) exists. He/she would going to get rid of that (') and it will take time then.
0
 

Author Comment

by:girlswants_me
ID: 9746430
Is there any more ideas how to write the "0001" in the excel cell?
without getting rid the zero's.
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9746545
girlswants_me,
  It is not about Ideas. CSV is only "suppported" by excel and not a standard format. So, if you want to use excel features, save your file as xls otherwise, use the limited functionality that Excel can provide for using CSV.
  Your original question any way was "I like to save my richedit text as excel file format.". So, why not use excel?

  Here are a few similar threads from the Excel TA which ended without conclusion:
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_20758186.html
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_20761927.html

...Snehanshu
0
 
LVL 2

Expert Comment

by:ZolikaP
ID: 9746632
0
 
LVL 5

Accepted Solution

by:
snehanshu earned 125 total points
ID: 9746929
Hey girlswants_me,
  I cracked the CSV thing for you! Perhaps I should also let the excel guys know!
:-)

function MyStringReplace(const S, OldPattern, NewPattern: string;
  Flags: TMyReplaceFlags): string;

var
  SearchStr, Patt, NewStr: string;
  Offset: Integer;
begin
  if rfIgnoreCase in Flags then
  begin
    SearchStr := AnsiUpperCase(S);
    Patt := AnsiUpperCase(OldPattern);
  end else
  begin
    SearchStr := S;
    Patt := OldPattern;
  end;
  NewStr := S;
  Result := '';
  while SearchStr <> '' do
  begin
    Offset := AnsiPos(Patt, SearchStr);
    if Offset = 0 then
    begin
      Result := Result + NewStr;
      Break;
    end;
    Result := Result + Copy(NewStr, 1, Offset - 1) + NewPattern;
    NewStr := Copy(NewStr, Offset + Length(OldPattern), MaxInt);
    if not (rfReplaceAll in Flags) then
    begin
      Result := Result + NewStr;
      Break;
    end;
    SearchStr := Copy(SearchStr, Offset + Length(Patt), MaxInt);
  end;
end;


Begin
  MyStrings := TStringList.Create;
  For i := 0 to RichEdit1.Lines.Count -1 Do
  Begin
    MyCSVStr := Trim(RichEdit1.Lines[i]);
    MyCSVStr := MyStringReplace(MyCSVStr, ' ', ',', [rfReplaceAll]);

//for multiple spaces:

    Repeat
      oldlen := length(MyCSVStr);
      MyCSVStr := MyStringReplace(MyCSVStr, ',,', ',', [rfReplaceAll]);
    until oldlen = length(MyCSVStr);

    MyFirst := Copy(MyCSVStr, 1, pos(',',MyCSVStr)-1);
    If MyTryStrToInt(myFirst, num) Then
    begin
      MyCSVStr := MyStringReplace(MyCSVStr, MyFirst, Format('"=text(%d,""0000"")"',[num]), []);
    End;

    MyStrings.Add(MyCSVStr);//convert richedit line to csv format

  End;
  MyStrings.SaveToFile('C:\MyExcel.csv');
  MyStrings.Free;

end;
0
 

Author Comment

by:girlswants_me
ID: 9760972
snehanshu,

 You are the God of all God of Delphi! Thank you very very much for the help.
You really are a Genius. What nationality are you i am just curious?

Thank you very much for the help.
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9761161
girlswants_me,
  Thou shalt be thanked! I thank thee for thy kind words.
:-)
  Well, I am from India, but I am surely not even close to the Gods of Delphi yet. I am glad I could help: it was a good learning experience for me too.
  Cheers!
...Shu
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now