ASP/XLS Format numbers

lou_greally · July 29th, 2003, 06:54 PM

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

David Cameron · July 29th, 2003, 07:01 PM

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

lou_greally · July 29th, 2003, 07:24 PM

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.

David Cameron · July 29th, 2003, 07:26 PM

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

lou_greally · July 29th, 2003, 07:50 PM

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

David Cameron · July 29th, 2003, 07:52 PM

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

regards
David Cameron

lou_greally · July 29th, 2003, 08:09 PM

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

David Cameron · July 29th, 2003, 08:48 PM

How about:

Code:

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

Call oFSO.CopyFile(sBreederDoc, sNewDoc, True)

Set oFSO = Nothing

regards
David Cameron

Greg Griffiths · July 31st, 2003, 03:08 PM

check out the stuff at http://www.greggriffiths.org/webdev/both/excel/ this should give you a starting point.