create an excel from a dataset

A simple way to convert dataset to excel sheet…

 Create a C# class file with the name WorkbookEngine.cs. Add the following code into it

public static void CreateWorkbook(DataSet ds, String path, string fileNam)

{

XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);

XslTransform xt = new XslTransform();

StreamReader reader =newStreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));

XmlTextReader xRdr = new XmlTextReader(reader);

xt.Load(xRdr, null, null);StringWriter sw = newStringWriter();

xt.Transform(xmlDataDoc, null, sw, null);

StreamWriter myWriter = newStreamWriter(path + fileNam);

myWriter.Write (sw.ToString());

myWriter.Close ();

}

 

The following code should be available in a file named Excel.xsl

 <xsl:stylesheet version=”1.0″
    xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
    xmlns:xsl=”http://www.w3.org/1999/XSL/Transform
 xmlns:msxsl=”urn:schemas-microsoft-com:xslt”
 xmlns:user=”urn:my-scripts”
 xmlns:o=”urn:schemas-microsoft-com:office:office”
 xmlns:x=”urn:schemas-microsoft-com:office:excel”
 xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet” > 
<xsl:template match=”/”>
  <Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
    xmlns:o=”urn:schemas-microsoft-com:office:office”
    xmlns:x=”urn:schemas-microsoft-com:office:excel”
    xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
    xmlns:html=”http://www.w3.org/TR/REC-html40“>
    <xsl:apply-templates/>
  </Workbook>
</xsl:template>
<xsl:template match=”/*”>
  <Worksheet>
  <xsl:attribute name=”ss:Name”>
  <xsl:value-of select=”local-name(/*/*)”/>
  </xsl:attribute>
    <Table x:FullColumns=”1″ x:FullRows=”1″>
      <Row>
        <xsl:for-each select=”*[position() = 1]/*”>
          <Cell><Data ss:Type=”String”>
          <xsl:value-of select=”local-name()”/>
          </Data></Cell>
        </xsl:for-each>
      </Row>
      <xsl:apply-templates/>
    </Table>
  </Worksheet>
</xsl:template>
<xsl:template match=”/*/*”>
  <Row>
    <xsl:apply-templates/>
  </Row>
</xsl:template>
<xsl:template match=”/*/*/*”>
  <Cell><Data ss:Type=”String”>
    <xsl:value-of select=”.”/>
  </Data></Cell>
</xsl:template>
</xsl:stylesheet>

Add this file also to the solution.

 

On the solution explorer right click on the Excel.xsl file and select properties.

In build action choose “Embedded Resource”.

 

 

In our coding class add the following line to invoke all this.

WorkbookEngine.CreateWorkbook(<our Dataset name>, “Our file path to store the excel sheet”, \\OurExcelFileName.xls);

 

Save all the files and run the code.

Our excel file will be created!

Useful things in jquery

Auto suggest and auto complete in .NET using jquery

(Along with master and content pages implementation).

Master page code:

<head runat="server">
     <link href="StyleSheet.css" rel="stylesheet" runat="server" type="text/css" />
     <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.js"></script>
     <asp:contentplaceholder id="LocalScripts" runat="server"></asp:contentplaceholder>
 </head>
 <body id="body" runat="server">
     <form id="form1" runat="server" defaultbutton="SignOutLB">
         <asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server">
         </asp:ContentPlaceHolder>
     </form>
 </body>

Content page:

<asp:Content ID="ScriptContent" ContentPlaceHolderID="localScripts" runat="server">
 <script type="text/javascript">
     function suggest(inputString)
     {
         if(inputString.length == 0)
         {
             $('#suggestions').fadeOut();
         }
         else
         {
             $('#AAATB').addClass('load');
             $.post("AutoSuggest.aspx?ST=" + inputString, function(data){
                 if(data.length >0)
                 {
                     $('#suggestions').fadeIn();
                     $('#suggestionsList').html(data);
                     $('#AAATB').removeClass('load');
                 }
             });
         }
     }
     function fill(thisValue)
     {
         $("#<%= ProjNoTB.ClientID %>").val(thisValue);
         setTimeout("$('#suggestions').fadeOut();", 600);
     }
 </script>
</asp:Content>

ASP.NET code:

<asp:TextBox ID="AAATB" runat="server" Width="90px" MaxLength="6" onkeypress="return IsNumberKey(event)" CssClass="textboxStyle" ValidationGroup="projNoValGp" onkeyup="suggest(this.value);" onblur="fill();" />
<div class="suggestionsBox" id="suggestions" style="display: none;">
     <img src="Images/arrow.png" style="position: relative; top: -2px; left: 50px;" alt="" />
     <div class="suggestionList" id="suggestionsList"> &nbsp; </div>
 </div>

sample AutoSuggest.aspx code:

protected void Page_Load(object sender, EventArgs e)
{
     string[] arr = new string[] {
                         "1234",
                         "1111",
                         "1333"
     };
     for (int i = 0; i < 3; i++)
     {
         Response.Write("<li onClick=\"fill(\'" + arr[i] + "\');\">" + arr[i] + "</li>");
     }
}

And my stylesheet will have the following

/* For autocomplete textbox — start */

#AAATB

{

padding:3px;

border:1px#CCCsolid;font-size:17px;

}

.suggestionsBox

{

position: absolute;

left: 130px;

top:42px;

margin: 26px0px0px0px;

width: 100px;

padding:0px;

background-color: #FFEDDD;

border-top: 3pxsolid#000;

color: #000;

border-radius: 3px;

}

.suggestionList

 {

margin: 0px;

padding: 0px;

}

.suggestionList ul li

{

list-style:none;

margin: 0px;

padding: 6px;

border-bottom:1px dotted #666;

cursor: default;

}

.suggestionList ul li:hover

{

background-color: #FC3;

color:#000;

}

ul

 {

font-family:Arial,Helvetica,sans-serif;

font-size:11px;

color:#FFF;

padding:0;

margin:0;

}

.load

{

background-image:url(loader.gif);

background-position:right;

background-repeat:no-repeat;

}

#suggest

 {

position:relative;

}

/* For autocomplete textbox — end */

Now this will bring up the list of values as soon as 1 is typed onto the textbox. It would look as follows

autosuggest img