Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 29th, 2003, 07:54 PM
Registered User
 
Join Date: Jul 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default ASP/XLS Format numbers

I to know how to format numbers so they're output as numbers in an xls or csv file (the version where u dont use the EXCEL object). i currently save records to an xls file using the filesystemobject and using char(9) to separate the fields. However all my fields that are long numbers, come out in the format 8764E+8767
formatnumber(mynum,0) changes these numbers to 123,456,789 what i really need is just 123456789 ...and casting it to a string makes no difference!

Any ideas

Reply With Quote
  #2 (permalink)  
Old July 29th, 2003, 08:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

For excel 2000:

In excel, right click on a cell and select format cell. In the select list to the left, select number. See the Use 1000 separator (,) checkbox?

Secondly, the format of the number is only important for those viewing the page. If this is just for the purposes of data storage, it shouldn't matter.

regards
David Cameron
Reply With Quote
  #3 (permalink)  
Old July 29th, 2003, 08:24 PM
Registered User
 
Join Date: Jul 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for the comment David but it needs to be automated, the point is the end user wont tolerate having to format the file- unfortunately.
Reply With Quote
  #4 (permalink)  
Old July 29th, 2003, 08:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well you could open an excel file that is already formatted correctly and use that as a breeder for other files (ie save as to what you want to end up with).

You could also try messing with the Excel API to change the setting for the cell.

regards
David Cameron
Reply With Quote
  #5 (permalink)  
Old July 29th, 2003, 08:50 PM
Registered User
 
Join Date: Jul 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks again for your comment.. what im doing is creating an xls file from values in the db, saving that file, then mailing it on. If I saveas and use the name of an existing xls file, it just overwrites it. as for using excel api or excel object, we cant install anything else on our server and have to stick with fileSaveAs which is fine except for this problem
Reply With Quote
  #6 (permalink)  
Old July 29th, 2003, 08:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I said "save as" rather than "save". In other words save the document to a new location. Post code.

regards
David Cameron
Reply With Quote
  #7 (permalink)  
Old July 29th, 2003, 09:09 PM
Registered User
 
Join Date: Jul 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

vFileName = "MYFILENAME"& ".xls"
      fsBatch = Server.MapPath(".\csv\") & "\" & vFileName
   end if

   set fsObjAll = Server.CreateObject("Scripting.FileSystemObject")
   set fsTxtAll = fsObjAll.OpenTextFile(fsBatch, 8, true)
   fsTxtAll.WriteLine(fsHeaderRow)

do while not adoRS.EOF


    ' ITS THIS CODE THAT ATTEMPTS TO FORMAT FIELDS
    if isnumeric(vProductCode) then
        vProductCode = formatnumber(vProductCode,0)

    end if







      fsRow = adoRS("iCustomerId") _
               & vQQ _
               & vTitle _
               & vQQ _
               & adoRS("vFirstName") _
               & vQQ _
               & adoRS("vLastName") _
               & vQQ _
               & adoRS("vAddress1") _
               & vQQ _
               & adoRS("vAddress2") _
               & vQQ _
               & adoRS("vCity") _
               & vQQ _
               & adoRS("vState") _
               & vQQ _
               & adoRS("vPostcode") _
               & vQQ _
               & adoRS("vCountry") _
               & vQQ _
               & vProduct(0) _
               & vQQ _
               & vProductCode(0) _
               & vQQ _
               & vProduct(1) _
               & vQQ _
               & vProductCode(1) _
               & vQQ _
               & vProduct(2) _
         & vQQ ' ALL THE REST OF THE FIELDS ETC


      adoRS.MoveNext

   loop

   'the loop ends with another fsRow built and waiting to be written - so get it out there
   if fsRow <> "" then
      fsTxtAll.WriteLine(cStr(fsRow))
   end if


   fsTxtAll.Close

' CODE TO MAIL THE FILE
--------------------

thanks again
Reply With Quote
  #8 (permalink)  
Old July 29th, 2003, 09:48 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How about:

Code:
Set oFSO = Server.CreateObject("Scripting.FileSystemObject")

Call oFSO.CopyFile(sBreederDoc, sNewDoc, True)

Set oFSO = Nothing

regards
David Cameron
Reply With Quote
  #9 (permalink)  
Old July 31st, 2003, 04:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: High Wycombe, UK, United Kingdom.
Posts: 344
Thanks: 0
Thanked 1 Time in 1 Post
Default

check out the stuff at http://www.greggriffiths.org/webdev/both/excel/ this should give you a starting point.
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 Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to format numbers with decimal places cyrusds_asp J2EE 2 March 8th, 2013 02:26 AM
ASP: Reading xls without uploading on server kamrag Classic ASP Basics 0 September 25th, 2006 10:26 AM
Adding numbers in #.###,0 format margreta XSLT 6 April 25th, 2006 10:52 AM
download db data to .xls with asp abbylee26 Classic ASP Databases 2 October 17th, 2003 03:18 PM



All times are GMT -4. The time now is 11:42 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.