Delphi Pages Forums  

Go Back   Delphi Pages Forums > Delphi Forum > General

Lost Password?

Reply
 
Thread Tools Display Modes
  #1  
Old 01-14-2015, 04:52 AM
rmt rmt is offline
Senior Member
 
Join Date: Jun 2013
Posts: 285
Default export DataSet to Excel from Delphi5 application

Dear Expert,

I am having problem while exporting some text value from DataSet to Excel. Eg. If the dataset value is '0001' then in Excel it shows like '1'. Kindly suggest the solution.
__________________
Rmt
Reply With Quote
  #2  
Old 01-14-2015, 05:51 AM
Jasser Jasser is offline
Moderator
 
Join Date: Jan 2005
Location: Saudi Arabia
Posts: 5,059
Default

You need to format the cells as a text. In your case Excel is assuming that the cell/column is a number. Therefore, Excel is truncating the zeroes.
__________________
Regards,
Abdulaziz Jasser
Reply With Quote
  #3  
Old 01-14-2015, 06:12 AM
rmt rmt is offline
Senior Member
 
Join Date: Jun 2013
Posts: 285
Default

Indeed that is the problem. But here, I am using StringList to get the ClientDataSet records and saving this StringList to *.CSV file as below:

StringListName.SaveToFile(SaveDialog1.FileName);

where SaveDialog1.FileName is a CSV file.

Here, I am not doing any customization in CSV file. So how shall I format the Cells.
__________________
Rmt
Reply With Quote
  #4  
Old 01-14-2015, 06:57 AM
Jasser Jasser is offline
Moderator
 
Join Date: Jan 2005
Location: Saudi Arabia
Posts: 5,059
Default

See the below code:




Code:
var
          oExcel: TExcelApplication;
          WS : _Worksheet;
begin
          oExcel := TExcelApplication.Create(Self);
          oExcel.DisplayAlerts[0] := False;
          oExcel.Connect;
          oExcel.Workbooks.Add(NULL, 0);

          WS := oExcel.WorkBooks[1].Worksheets[1] as _Worksheet;


          //To format the first column as TEXT.
          WS.Range['A1', 'A1'].EntireColumn.NumberFormat :=  '@';


          //Now write to the cell.
          WS.Cells.Item[1,1] := '0001';


          oExcel.ActiveWorkbook.SaveCopyAs('D:\Test.xlsx',0);

          oExcel.Disconnect;
          oExcel.Quit;
          oExcel := Nil;
__________________
Regards,
Abdulaziz Jasser
Reply With Quote
  #5  
Old 01-14-2015, 10:53 AM
Jasser Jasser is offline
Moderator
 
Join Date: Jan 2005
Location: Saudi Arabia
Posts: 5,059
Default

Another way if you want use TStringlist is to add single bract at the beginning and at then end of each text before saving them to a file. Example:

Code:
for i:=0 to StringListName.Count-1 do
    StringListName[i] := '[' + StringListName[i] + ']';
__________________
Regards,
Abdulaziz Jasser

Last edited by Jasser; 01-14-2015 at 11:05 AM.
Reply With Quote
  #6  
Old 01-15-2015, 10:30 AM
Jasser Jasser is offline
Moderator
 
Join Date: Jan 2005
Location: Saudi Arabia
Posts: 5,059
Default

Rmt,

Do you still need help?
__________________
Regards,
Abdulaziz Jasser
Reply With Quote
  #7  
Old 06-03-2018, 11:16 AM
rmt rmt is offline
Senior Member
 
Join Date: Jun 2013
Posts: 285
Default

Dear Jasser,

Sorry, I am replying after a long time. Actually all is well while exporting to excel. But while exporting to CSV, same problem e.g if i export the value '1234.80' to CSV, it shows 1234.8.

I tried the solution you provided i.e by adding '['+'1234.80'+']', but in CSV, it is showing like [1234.80]. Now I do not want to show square bracket but only 1234.80.

Kindly suggest.
Thank you so much in advance.
__________________
Rmt
Reply With Quote
  #8  
Old 06-05-2018, 06:53 AM
Norrit Norrit is offline
Moderator
 
Join Date: Aug 2001
Location: Landgraaf
Posts: 7,307
Default

Can you spot the brackets?

Code:
s := '['+'1234.80'+']'
So what o what would could you do to remove them...
Yes, don't add them

Code:
s := '1234.80'
But I guess you didn't have enough Coffee at that moment ...
Reply With Quote
  #9  
Old 06-05-2018, 07:28 AM
rmt rmt is offline
Senior Member
 
Join Date: Jun 2013
Posts: 285
Default

Dear Norrit,

I think you need coffee this time. The problem I mentioned here is when I export the value '1234.80' to CSV, it shows 1234.8 in CSV. So I took this approach using [1234.80] , it definitely shows the required value 1234.80 but with brackets. This problem i want to overcome in CSV. However, it is working in EXCEL. If any suggestion, please do provide.

Thanks.
__________________
Rmt
Reply With Quote
  #10  
Old 06-05-2018, 02:46 PM
Norrit Norrit is offline
Moderator
 
Join Date: Aug 2001
Location: Landgraaf
Posts: 7,307
Default

You mentioned that the .csv displays [1234.80] with brackets, and that's obvious since you provide it that way.
Quote:
but in CSV, it is showing like [1234.80]
And this cannot be converted to columns by the excell text-to-columns. You could try to doublequote it "1234.80", but I think excell interprets it still as double field. But that's easy for you to test
This is something that you cannot overcome in .csv, since there's an interpreter layer where you specify the text-to-columns, and that's out of your reach, that's excell...
When you do the text-to-columns there is an option where you can overrule the assumption (select the column and set it's type), but you should do that each time you press that text-to-columns
So I hope for you the doublequoted value works

Other thing you might try is to pass '1234.80, when you type a value into a cell in excell and you want it to be a text you type '1234.80, where excell removes the display of '
But again, that's a longshot and all depends on the excell interpreter of csv (or manually text-to-columns)
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump


All times are GMT. The time now is 03:08 AM.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2018, vBulletin Solutions, Inc.