format excel sheet cell in ASP

I am using the rs.Getstring() method to write my database values into an excel sheet.
How do i format the data in that excel sheet from my asp code.Some of the values are too
long and exceed into the next column.
I need the row height to increase when the values are too long to fit in the cells.
prafusaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

prafusaAuthor Commented:

here is my code


call rs.MoveFirst()
      strExcel = "Prefix,First Name,Middle,Regions,Topics,Date Registered - GMT" & chr(13) & chr(10) & rs.GetString(,,",",chr(13)&chr(10))
      rs.Close()
      set rs = nothing
      set conn = nothing
      dim fs
      set fs = server.CreateObject("scripting.filesystemobject")
      call fs.CreateTextFile(server.MapPath("data") & "\" & filename,8).Write(strExcel)
0
Robberbaron (robr)Commented:
the code you posted creates a text file, effectively CSV format.    Which can contain only data, no excel formatting.


to do anything with the excel formatting, you need to use an Excel object. Which does not work all that well on a server.

Another way is to write to an already created and formatted excel file using it as a database. You then read and write to defined ranges as tables and fields.




'---from an app in use----' 
   'Open the ADO connection to the Excel workbook'
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & svname & ";" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""
               
               
    'Add values to individual cells'
    'oConn.Execute "Insert into First_Name (F1) Values ('Nancy')"'
    'oConn.Execute "Insert into Last_Name (F1) Values ('Davolio')"'
    'oConn.Execute "Insert into Title (F1) Values ('Sales Manager')"'
    'oConn.Execute "Insert into Hire_Date (F1) Values (#11/1/00#)"'
    'oConn.Execute "Insert into Comments (F1) Values ('This is a line of long text that will wrap in the cell.This is a line of long text that will wrap in the cell.')"'


    'sqlQ = "Insert into " & RangeName & " (F1) Values (" & vdata & ")"'
    sqlQ = "SELECT * FROM " & RangeName
    Set oRS = New ADODB.Recordset
        oRS.ActiveConnection = oConn
        oRS.CursorType = adOpenDynamic        'Static cursor.
        oRS.LockType = 2                      'Pessimistic Lock.
        'oRS.EditMode = 3
        oRS.Source = sqlQ
        oRS.Open
        
    'oRS.Open sqlQ, oConn, adOpenDynamic
        oRS.MoveFirst
        oRS.Fields(0).value = vData
        oRS.Update
    oRS.Close

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.