Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 29th, 2003, 06: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

 
Old July 29th, 2003, 07: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
 
Old July 29th, 2003, 07: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.
 
Old July 29th, 2003, 07: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
 
Old July 29th, 2003, 07: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
 
Old July 29th, 2003, 07: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
 
Old July 29th, 2003, 08: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
 
Old July 29th, 2003, 08: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
 
Old July 31st, 2003, 03: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.




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 09:26 AM
Adding numbers in #.###,0 format margreta XSLT 6 April 25th, 2006 09:52 AM
download db data to .xls with asp abbylee26 Classic ASP Databases 2 October 17th, 2003 02:18 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.