locked
EXCEL,SQLSERVER 2005 and asp.net 2005,VB ? RRS feed

  • Question

  • User410767947 posted

    I have a Sqlserver 2005 table in the below format .

    PROJECTID LINEID ITEMDESCRIPTION SUBHEADID SUBHEAD UNITOFMEASURE QTY UNITPRICE
    1 A Site Clearance including the removal and disposal of vegetation, undergrowth, bushes, hedges, trees or the like, if any SH1 Site Preparation sqm 4100  
    1 D Extra over for excavation in rock including disposal. SH1 Excavation cum 380  
    1 F Disposal of materials arising from excavation and backfill over site to make up levels SH1 Disposal cum 250  
    1 G -Ditto- to an outside suitable location (approved by statutory authority) SH2 Disposal cum 1650  

     

    i have a button to export to excel . WHen the user clicks that button . I need the generate the excel in the below format .

     

    LINEID ITEM DESCRIPTION QTY UNIT RATE AMOUNT                       RO.
    Words Figures
      SUBSTRUCTURE (ALL PROVISIONAL)          
      CIVIL WORKS          
      Site Preparation          
    A Site Clearance including the removal and disposal of vegetation, undergrowth, bushes, hedges, trees or the like, if any 4100 sqm      
                 
      Excavation          
    D Extra over for excavation in rock including disposal. 380 cum      
                 
      Disposal          
    F Disposal of materials arising from excavation and backfill over site to make up levels 250 cum      
                 
    G -Ditto- to an outside suitable location (approved by statutory authority) 1650 cum      
                 

     

    How to get the excel in the above format . After getting the excel sheet . user will enter some fields in the Excel sheet and again he uploads the Excel to the system . The system should take the values and upload in the sqlserver .

     

    How to do it ?  

     

    Saturday, December 6, 2008 10:25 AM

Answers

  • User-319574463 posted

     First of all I would suggest that you mock up the Excel spreadsheet, get its format approved and then save it as HTML. Clean up the resultant HTML and abstract all the styling to CSS.

    The HTML will probably need tidying so that can see the structure clearly.

    Now you can query your database, and build up the report in a StringBuilder variable. When complete just assign it the InnerHtml property of a control on a page such as a span or a table cell.

    There is a simple tweak that make the page OLE with the contents as an Excel spreadsheet. From memory, it invloves setting the Mime type.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 10, 2008 9:12 AM

All replies

  • User-319574463 posted

     First of all I would suggest that you mock up the Excel spreadsheet, get its format approved and then save it as HTML. Clean up the resultant HTML and abstract all the styling to CSS.

    The HTML will probably need tidying so that can see the structure clearly.

    Now you can query your database, and build up the report in a StringBuilder variable. When complete just assign it the InnerHtml property of a control on a page such as a span or a table cell.

    There is a simple tweak that make the page OLE with the contents as an Excel spreadsheet. From memory, it invloves setting the Mime type.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 10, 2008 9:12 AM
  • User410767947 posted

    Can u please send some articals related to it .

    Saturday, January 3, 2009 8:30 AM
  • User-1171043462 posted

    Hi Tatworth

    I really liked the technique It would be great if you suugest some links to do the same

    Saturday, January 3, 2009 8:41 AM
  • User-319574463 posted

     >I really liked the technique It would be great if you suugest some links to do the same

    It is a technique, I learnt the hard way by just it. At some point I will write it up. For now, please have a look at HTML Text Writer at http://www.codeplex.com/HtmlTextWriterUtil

     

    Saturday, January 3, 2009 9:06 AM
  • User-319574463 posted

     >I really liked the technique It would be great if you suggest some links to do the same

    HTML text writer at http://www.codeplex.com/HtmlTextWriterUtil/Release/ProjectReleases.aspx?ReleaseId=8251 helps with part of the process.

     

    Saturday, January 3, 2009 9:08 AM
  • User-1171043462 posted

    I have used String builder to create HTML and bind it to div using innerHTML but I never knew there is tool for doing that

    Thanks

    Saturday, January 3, 2009 9:21 AM
  • User-319574463 posted

     I have consider doing a rewrite of HTMLWriter to:

    • Automatically drop blank lines.
    • Option to emit VB.NET code instead of C#
    Saturday, January 3, 2009 9:46 AM
  • User-1171043462 posted

    I have consider doing a rewrite of HTMLWriter to:

    • Automatically drop blank lines.
    • Option to emit VB.NET code instead of C#

    Is HTML writer developed by you??

    Saturday, January 3, 2009 10:04 AM
  • User-319574463 posted

     No I am not the author of HTML Writer. I have sent an email to the author, so far without any response. Do you need a VB.NET code generator option?

    Saturday, January 3, 2009 10:09 AM
  • User-1171043462 posted

    Currently I can manage But in future if its there it would help a lot. As I code in Both

    Saturday, January 3, 2009 10:17 AM
  • User-319574463 posted

     I have contacted the author of HtmlTextWriter for his permission to produce a version that is switchable C#/VB.NET

    Saturday, January 3, 2009 1:54 PM
  • User-1171043462 posted

    So is he ready with to add the VB Functionality To be added??

    Saturday, January 3, 2009 2:02 PM
  • User-319574463 posted

     I have asked again about adding VB.NET as an option. As an alternative, I have asked if I can take on the project to do the enhancement.

    Saturday, January 3, 2009 2:22 PM
  • User-1171043462 posted

    If he agrees it would be a good addon enhancement

    Saturday, January 3, 2009 2:26 PM
  • User-1171043462 posted

    I want to ask about this. Many people say building html markup using string builder is not good instead use dynamic controls. Is this correct

    Saturday, January 3, 2009 2:30 PM
  • User-319574463 posted

     If there isa control to do the job then fine! However many times the work involved in using a regular control is grossly underestimated and it turns out that generating the markup directly is quicker.

    Saturday, January 3, 2009 3:15 PM
  • User-1171043462 posted

    Thanks. When I newly started posting around 20 days back on this site. I had suggested dynamic creation of html table markup and then binding it to div using div.innerHTML. As soon as I posted I had to enter a long debate which lasted 3 days. Hence I asked you about this.

    Saturday, January 3, 2009 3:26 PM
  • User-319574463 posted

     I have now recieved clearance to produce a enhanced version of HTML Text Writer - this will include:

    • VB.NET option
    • Dropping blank lines
    • Adjustable column spacing
    Monday, January 5, 2009 1:14 PM
  • User-1171043462 posted

    Good. Please mail me the link when you have done so. By the way is it developed in .Net??

    Monday, January 5, 2009 1:19 PM
  • User-319574463 posted

     The current project is developed in VS2005 - I may well produce a VS2008 version at a later stage.

    Monday, January 5, 2009 1:48 PM
  • User-319574463 posted

     >Good. Please mail me the link when you have done so. By the way is it developed in .Net??

    This has now been done - please see the annoucement at http://forums.asp.net/p/1378614/2907047.aspx#2907047 

    Monday, February 2, 2009 3:17 AM
  • User-1171043462 posted

     >Good. Please mail me the link when you have done so. By the way is it developed in .Net??

    This has now been done - please see the annoucement at http://forums.asp.net/p/1378614/2907047.aspx#2907047 

    Thanks

    Monday, February 2, 2009 3:21 AM