girlswants_me
asked on
Creating excel file using delphi
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.
examples:
IDNO TOTAL AMOUNT
01 1 1000
02 1 500
03 10 2000
I like to save my richedit text as excel file format.
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.Comma Text);//co nvert richedit line to csv format
End;
MyStrings.SaveToFile('C:\M yExcel.csv ');
MyRows.Free;
MyStrings.Free;
end;
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
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.Comma
End;
MyStrings.SaveToFile('C:\M
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.Comma Text);//co nvert richedit line to csv format
End;
MyStrings.SaveToFile('C:\M yExcel.csv ');
MyRows.Free;
MyStrings.Free;
end;
procedure TForm1.Button1Click(Sender
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.Comma
End;
MyStrings.SaveToFile('C:\M
MyRows.Free;
MyStrings.Free;
end;
ASKER
MyRows.Delimiter := ' ';
and
MyRows.DelimitedText := RichEdit1.Lines[i];
"Delimited" is this a property?
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
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
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
ASKER
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'.
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'.
ASKER
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'.
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('Ex cel.Applic ation');
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.Comma Text);//co nvert richedit line to csv format
For j := 0 to MyRows.Count -1 Do
Begin
if j = 0 then
excel.Cells[i+1,j+1].Numbe rFormat := '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:\M yExcel.csv ');
MyRows.Free;
MyStrings.Free;
//example of changing font etc.:
// excel.Cells[1,1].Value:='E xcel - 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:='E xcel - Test 2.';
// excel.Cells[2,1].Font.Ital ic := True; {set italic style}
// excel.Cells[1,1].Font.Size := 15; {set size to 15}
excel.ActiveWorkbook.SaveA s('c:\test 1.xls'); //Save the active workbook as c:\test1.xlx
excel.Quit; //Terminate Excel application
end;
Cheers!
...Snehanshu
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
var
excel:Variant;
MyStrings, MyRows: TStringList;
i, j: integer;
begin
try
excel:=CreateOleObject('Ex
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.Comma
For j := 0 to MyRows.Count -1 Do
Begin
if j = 0 then
excel.Cells[i+1,j+1].Numbe
excel.Cells[i+1,j+1].Value
End;
End;
MyStrings.SaveToFile('C:\M
MyRows.Free;
MyStrings.Free;
//example of changing font etc.:
// excel.Cells[1,1].Value:='E
// excel.Cells[1,1].Font.Bold
// excel.Cells[1,1].Font.Size
// excel.Cells[2,1].Value:='E
// excel.Cells[2,1].Font.Ital
// excel.Cells[1,1].Font.Size
excel.ActiveWorkbook.SaveA
excel.Quit; //Terminate Excel application
end;
Cheers!
...Snehanshu
ASKER
MyRows.Delimiter := ' '; <<------ error
i still got this message: Undeclared identifier: 'Delimiter'
i still got this message: Undeclared identifier: 'Delimiter'
ASKER
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.
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:\M yExcel.csv ');
MyStrings.Free;
end;
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
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);//
End;
MyStrings.SaveToFile('C:\M
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:\M yExcel.csv ');
MyStrings.Free;
end;
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
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);//
End;
MyStrings.SaveToFile('C:\M
MyStrings.Free;
end;
ASKER
when i opened the MyExcel.csv the result of the numbers is this '0002
how should i get the ( ' ) of the ( 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
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
ASKER
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.
ASKER
Is there any more ideas how to write the "0001" in the excel cell?
without getting rid the zero's.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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
ASKER