BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Problem: Excel insists on transferring Double type Goto page 1, 2  Next          BorlandTalk.com Forum Index -> Delphi OLE Automation View previous topic :: View next topic   Author Message Martin B Guest Posted: Mon Sep 12, 2005 2:25 pm    Post subject: Problem: Excel insists on transferring Double type Hello, I am reading values from Excel that are supposed to be string. The file is automatically generated by an ERP system, and Excel insists on treating the strings as numbers. The strings have length 16 and consist of numbers. I have tried all kinds of cell formatting before reading the values into Delphi, but I always get a scientific-type number (of variant type varDouble), like 4.33e+16. It would not help to convert the values to some big number, because the values can be 50 characters long. How can I force Excel to return STRING values instead of Doubles; there must be some way of formatting the column. Regards, Martin Back to top Patrick Guest Posted: Mon Sep 12, 2005 2:44 pm    Post subject: Re: Problem: Excel insists on transferring Double type Quote: I have tried all kinds of cell formatting before reading the values into Delphi, but I always get a scientific-type number (of variant type varDouble), like 4.33e+16. How are you getting the values? If you're using an OleVariant, doe this return 4.33e+16?: VarToStr(oExcel.Cells.Item[1,i]) (oExcel : OleVariant;) Quote: How can I force Excel to return STRING values instead of Doubles; there must be some way of formatting the column. You could try checking/setting the NumberFormat property. Back to top Mike Shkolnik Guest Posted: Mon Sep 12, 2005 3:11 pm    Post subject: Re: Problem: Excel insists on transferring Double type Martin, just use the FormatFloat function to convert the double into string instead VarToStr -- With best regards, Mike Shkolnik EMail: [email]mshkolnik (AT) scalabium (DOT) com[/email] http://www.scalabium.com "Martin B" <mb (AT) mb (DOT) com> wrote Quote: Hello, I am reading values from Excel that are supposed to be string. The file is automatically generated by an ERP system, and Excel insists on treating the strings as numbers. The strings have length 16 and consist of numbers. I have tried all kinds of cell formatting before reading the values into Delphi, but I always get a scientific-type number (of variant type varDouble), like 4.33e+16. It would not help to convert the values to some big number, because the values can be 50 characters long. How can I force Excel to return STRING values instead of Doubles; there must be some way of formatting the column. Regards, Martin Back to top Martin B Guest Posted: Mon Sep 12, 2005 7:12 pm    Post subject: Re: Problem: Excel insists on transferring Double type I have tried to set various format of the Excel cell before reading it as a variant. Does not work. Quote: You could try checking/setting the NumberFormat property. Back to top Martin B Guest Posted: Mon Sep 12, 2005 7:37 pm    Post subject: Re: Problem: Excel insists on transferring Double type Almost worked. I tried the extreme format: s:=formatfloat('##################0.0##################',svar); but the floating point values are not precise, so a text value of 47307030100200100 gets formatted to 47307030100200096,0 "Mike Shkolnik" <mshkolnik2002 (AT) ukr (DOT) net> wrote Quote: Martin, just use the FormatFloat function to convert the double into string instead VarToStr Back to top Patrick Guest Posted: Mon Sep 12, 2005 8:03 pm    Post subject: Re: Problem: Excel insists on transferring Double type Quote: I have tried to set various format of the Excel cell before reading it as a variant. Does not work. Bummer. I guess if I was in your shoes I'd try to get Excel to view the data imported as text from your ERP system. I haven't anything like that since DDE and serial data was the standard, so I might be suggesting the impossible. On an aside, I can barely get Excel to treat data a certian way with the GUI, so I'm at my limit with OLE COM suggestions. Back to top Mike Shkolnik Guest Posted: Mon Sep 12, 2005 8:30 pm    Post subject: Re: Problem: Excel insists on transferring Double type 1. Use the #0.# as format string 2. try to use the FloatToStr instead -- With best regards, Mike Shkolnik E-mail: [email]mshkolnik (AT) scalabium (DOT) com[/email] WEB: http://www.scalabium.com "Martin B" <mb (AT) mb (DOT) com> wrote Quote: Almost worked. I tried the extreme format: s:=formatfloat('##################0.0##################',svar); but the floating point values are not precise, so a text value of 47307030100200100 gets formatted to 47307030100200096,0 "Mike Shkolnik" <mshkolnik2002 (AT) ukr (DOT) net> wrote in message news:43259a0a (AT) newsgroups (DOT) borland.com... Martin, just use the FormatFloat function to convert the double into string instead VarToStr Back to top Martin B Guest Posted: Tue Sep 13, 2005 9:21 am    Post subject: Re: Problem: Excel insists on transferring Double type The variant comes with the double value: 4,73070301002001e+16 The Excel field value is 47307030100200100 (with General format) When the Excel field contains more that 15 digits it is converted to scientific format. ad 1. formatfloat with #0.# returns '47307030100200096' ad 2. FloatToStr (with formatsettings loaded with autodetected and user modifiable settings) returns '4,73070301002001E16' Solutions: 1) Ask the customer to start text fields with a quote ( ' ). 2) Read the entire range with properties into RangeMatrix: RangeMatrix:=ExcelApplication.Range['A1',ExcelApplication.Cells.Item[datarows,datacols]]; Then read each formula: RangeMatrix.Cells.Item[row,col].Formula If the formula contains '=' as the first character (don't trim), then read .Value2 The problem is that you will still get a bad value if the formula is, e.g., "=C3" where C3 contains the string. 3) Paste Special as Values and read values from the Excel with .Formula Select the entire worksheet, Copy and Paste Special as Values to remove formulas and then read .Formula (then clear the paste buffer) RangeMatrix:=ExcelApplication.Range['A1',ExcelApplication.Cells.Item[datarows,datacols]].Formula; This works but requires some RAM on the user's computer. Links: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q214233 text to unintended number format http://www.erlandsendata.no/english/index.php?d=envbadacrs2ws see first comment "Mike Shkolnik" <mshkolnik2002 (AT) ukr (DOT) net> wrote Quote: 1. Use the #0.# as format string 2. try to use the FloatToStr instead -- With best regards, Mike Shkolnik E-mail: [email]mshkolnik (AT) scalabium (DOT) com[/email] WEB: http://www.scalabium.com "Martin B" <mb (AT) mb (DOT) com> wrote in message news:4325d9c3 (AT) newsgroups (DOT) borland.com... Almost worked. I tried the extreme format: s:=formatfloat('##################0.0##################',svar); but the floating point values are not precise, so a text value of 47307030100200100 gets formatted to 47307030100200096,0 "Mike Shkolnik" <mshkolnik2002 (AT) ukr (DOT) net> wrote in message news:43259a0a (AT) newsgroups (DOT) borland.com... Martin, just use the FormatFloat function to convert the double into string instead VarToStr Back to top Martin B Guest Posted: Tue Sep 13, 2005 10:04 am    Post subject: Re: Problem: Excel insists on transferring Double type ..Text still returns scientifically formatted Double values when the cell contains a string with 12 or more digits. So .Text does not work. Using .Formula works (preferably with PasteSpecial as Values first). See my other post. "Chris Cheney" <cjc1@nospam%ucs.cam.ac.uk%no%spam%please> wrote Quote: "Martin B" <mb (AT) mb (DOT) com> wrote in news:4325d3bb (AT) newsgroups (DOT) borland.com: When you obtain the value using Automation, you get just that, the VALUE that the cell display represents. If you want the text that is displayed in the cell, you should use the Text property (of the Range object that represents the cell) - see the Excel Visual Basic help. Back to top Oliver Townshend Guest Posted: Tue Sep 13, 2005 10:11 am    Post subject: Re: Problem: Excel insists on transferring Double type Quote: I have tried all kinds of cell formatting before reading the values into Delphi, but I always get a scientific-type number (of variant type varDouble), like 4.33e+16. Have you tried formatting to text? Something like: Range("C5:C18").NumberFormat = "@" (yes it's VBA, I nicked it from a macro I wrote two weeks ago) Oliver Townshend Back to top Martin B Guest Posted: Tue Sep 13, 2005 10:27 am    Post subject: Re: Problem: Excel insists on transferring Double type Yes, '@' and '#' and various other. Quote: Have you tried formatting to text? Something like: Range("C5:C18").NumberFormat = "@" Back to top Martin B Guest Posted: Tue Sep 13, 2005 12:00 pm    Post subject: Re: Problem: Excel insists on transferring Double type Another problem, a little off this topic, is that Excel puts zeros in numbers at position 16 and forward, when you input a number with 16 digits or more. So our customer will have to make the ERP-system enclose the field values in " or put an apostrophe ( ' ) in front. Back to top John Herbster Guest Posted: Tue Sep 13, 2005 2:26 pm    Post subject: Re: Problem: Excel insists on transferring Double type "Martin B" <mb (AT) mb (DOT) com> wrote Quote: Almost worked. I tried the extreme format: s:=formatfloat('##################0.0##################',svar); but the floating point values are not precise, so a text value of 47307030100200100 gets formatted to 47307030100200096,0 Well actually, if you have made sure that your PC is running the with the correct FPU extended precision by inserting Set8087CW($1332); after the begin in the DPR file, the text value, "47307030100200100" will get converted to exactly 47307030100200100 which on being stored in a double will be rounded to 47307030100200096 which on being run through your formatfloat('##################0.0##################',svar); get converted to "47307030100200096.0" Regards, JohnH Back to top Martin B Guest Posted: Tue Sep 13, 2005 8:18 pm    Post subject: Re: Problem: Excel insists on transferring Double type Actually, there is nothing to do about it. Excel can't handle numbers with 16 or more digits and puts zeros in the formula. Excel returns the wrong numbers, and there is no way to get the original (intended) value. Back to top Oliver Townshend Guest Posted: Tue Sep 13, 2005 9:15 pm    Post subject: Re: Problem: Excel insists on transferring Double type Quote: Actually, there is nothing to do about it. Excel can't handle numbers with 16 or more digits and puts zeros in the formula. Excel returns the wrong numbers, and there is no way to get the original (intended) value. I just formatted a cell in Excel to Text and then typed in a large number and transferred it. It stays as text. Somehow I think the system that your ERP system is the cause of the problem. Oliver Townshend Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi OLE Automation All times are GMT Goto page 1, 2  Next Page 1 of 2   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.