Thread: Excel Dump

    #1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0

    Excel Dump


    I have an ASP page that when run automatically dumps the date into Excel using:

    <%
    Response.ContentType = "application/vnd.ms-excel"
    %>

    This works fine but the person I did it for needs it formatted a certain way so I had to change the table data so that it displayed all address columns in one Excel column and several other columns from the SQL table in one Excel column as well.

    <table width="3000" border="1">
    <tr>
    <th scope="col">Last, First</th>
    <th scope="col">Address</th>
    <th scope="col">County</th>
    <th scope="col">Phone</th>
    <th scope="col">Email</th>
    <th scope="col">Areas of Practice</th>
    <th scope="col">Other Info</th>
    <th scope="col">Certify</th>
    </tr>
    <%
    set rs=server.createobject("ADODB.Recordset")
    sql = "Select * from MY_TABLE"
    rs.open sql, conn, 2, 3

    do while not rs.eof

    %>



    <tr>
    <td><%=rs("LastName")%>, <%=rs("FirstName")%></td>
    <td><%=rs("Add1")%><br />
    <%=rs("Add2")%><br />
    <%=rs("City")%>, <%=rs("State")%> <%=rs("Zip")%></td>
    <td><%=rs("Cnty")%></td>
    <td><%=rs("Phone")%></td>
    <td><%=rs("Email")%></td>
    <td><%=rs("Debtor")%><br />
    <%=rs("Employment")%><br />
    <%=rs("Entitlement")%><br />
    <%=rs("Estate")%><br />
    <%=rs("FEMA")%><br />
    <%=rs("Insurance")%><br />
    <%=rs("Immigration")%><br />
    <%=rs("Landlord")%><br />
    <%=rs("RealProperty")%><br />
    <%=rs("Tax")%><br />
    <%=rs("OtherArea")%><br /></td>
    <td><%=rs("OtherInfo")%><br /></td>
    <td><%=rs("Certify")%></td>
    </tr>


    <%
    rs.movenext
    loop
    %>

    </table>

    The problem is the way it is formatted in Excel. The person that needs this is going to be copying it into another spreadsheet that already has similarly formatted data. She is not very computer literate so it is important to get it to look as close to what she has as possible so her head doesn't explode. I have attached an image of what I am getting in excel.

    Can someone tell me how I can export this so it is already formatted by removing lines if one of the areas of practice were not selected or if there is no address line 2? I would also like it formatted so that everything is aligned to the top left. I know how to do this is excel but it would be great if it could be done automatically for her.


    Thanks,
    Scott
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,592
    Rep Power
    278
    You can save data to CSV file and it will open in Excel by default... in this case everything will be well aligned and easy to put logic for skipping rows, or just make right selection in SQL and use recordset Save method
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

Similar Threads

  1. Replies: 0
    Last Post: October 25th, 2011, 07:05 PM
  2. Replies: 0
    Last Post: April 18th, 2008, 12:54 PM
  3. Excel dump
    By emmim44 in forum ASP Development
    Replies: 2
    Last Post: August 23rd, 2007, 10:03 AM
  4. SQL "dump" from an Excel file
    By chabuhi in forum ASP Development
    Replies: 4
    Last Post: January 6th, 2006, 08:55 AM
  5. dump results from web to excel
    By asp_calav in forum ASP Development
    Replies: 7
    Last Post: December 9th, 2004, 08:54 PM

IMN logo majestic logo threadwatch logo seochat tools logo