Creating excel file using delphi

anybody can help?

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;

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;

MyRows.Delimiter := ' ';
and
MyRows.DelimitedText := RichEdit1.Lines[i];

"Delimited" is this a property?

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

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

 

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'.

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'.

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

MyRows.Delimiter := ' '; <<------ error
i still got this message: Undeclared identifier: 'Delimiter'

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.

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;

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;

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

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

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.

Is there any more ideas how to write the "0001" in the excel cell?
without getting rid the zero's.

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:
https://www.experts-exchange.com/questions/20758186/Column-width-in-CSV-or-txt-file.html
https://www.experts-exchange.com/questions/20761927/Formatting.html

...Snehanshu

http://www.djpate.freeserve.co.uk/AutoExcl.htm

regards,
  Zoltan

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.

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