• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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.
1 Solution
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))
      set rs = nothing
      set conn = nothing
      dim fs
      set fs = server.CreateObject("scripting.filesystemobject")
      call fs.CreateTextFile(server.MapPath("data") & "\" & filename,8).Write(strExcel)
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 sqlQ, oConn, adOpenDynamic
        oRS.Fields(0).value = vData

Open in new window


Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now