Create Excel file also use various formulas with in it

In one project has requirement that need to create excel file through coding and need to use various excel formals for it.Below is code for it.

FileStream fs = new FileStream(@"D:\Test\TestExcel.xls", FileMode.Create);
        StreamWriter sr = new StreamWriter(fs);
        Response.ContentType = "application/ms-excel";
        StringBuilder sb = new StringBuilder();
        sb.Append("<table>");
        sb.Append("<tr><td><font face='Arial' color='black' size='2'><b>Name</b></font></td>");
        sb.Append("<td><font face='Arial' color='black' size='2'><b>Amount</b></font></td></tr>");
        for (int i = 0; i < 10; i++)
        {
            sb.Append("<tr>");
            sb.Append("<td align='center' vertical-align='middle'>" + "<font face='Arial' color='black' size='2'>Name12</font></td>");
            sb.Append("<td align='center' vertical-align='middle'>" + "<font face='Arial' color='black' size='2'>" + i.ToString() + "</font></td>");
            sb.Append("</tr>");

        }
        sb.Append("<tr>");
        sb.Append("<td>Total</td>");
        sb.Append("<td>=SUM(B1:B11)</td></tr>");
        sb.Append("</table>");
        sr.WriteLine(sb.ToString());
        sr.Close();
        fs.Dispose();

Response.Clear();
Response.AddHeader("content-disposition", @"attachment; filename=Test.xls");
Response.Write(sb.ToString());

Happy coding ..

Syntax highlighter

Blogger needs some kind of syntax-highlighting plug-in before posting source code on a blog.

Today i am looking for it and i get great post through which i have learned syntax-highlighting.

Here are the steps:-

1) In th back end click on "NEW POST" or "CUSTOMIZE" then Go to "Layout" tab and click on "EDIT HTML" sub-tab.


2) Go to http://syntaxhighlighter.googlecode.com/svn/trunk/Styles/SyntaxHighlighter.css, then perform a "select all" and "copy". The css information is now in the clipboard.


3) Paste the css information at the end of the css section of your blogger html template before </b:skin>

4)Before the </head> tag, paste the following:

<!-- Add-in CSS for syntax highlighting -->
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shCore.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushCpp.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushCSharp.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushCss.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushDelphi.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushJava.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushJScript.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushPhp.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushPython.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushRuby.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushSql.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushVb.js' type='text/javascript'></script>
<script src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushXml.js' type='text/javascript'></script>

remove lines for languages you'll never use


5) Before the </body> tag, insert the following:

<!-- Add-in Script for syntax highlighting -->
<script language='javascript'>
dp.SyntaxHighlighter.BloggerMode();
dp.SyntaxHighlighter.HighlightAll('code');
</script>

6) Use the "Preview" button to make sure your website is correct, then click "Save Template".

7) When composing a blog entry that contains source code, click the "Edit Html" tab and put your source code (with html-escaped characters) between these tags:

<pre name="code" class="cpp">
...Your html-escaped code goes here...
</pre>

substitute cpp with if you are using below languages

Language Aliases
C++ cpp, c, c++
C# c#, c-sharp, csharp
CSS css
Delphi delphi, pascal
Java java
Java Script js, jscript, javascript
PHP php
Python py, python
Ruby rb, ruby, rails, ror
Sql sql
VB vb, vb.net
XML/HTML xml, html, xhtml, xslt


You can do html-escaping by this link: http://www.accessify.com/tools-and-wizards/developer-tools/quick-escape/default.php


Source: http://heisencoder.net/2009/01/adding-syntax-highlighting-to-blogger.html

Dell laptop to switch functionality of function key

This drove me crazy for about 2 hours today. I swear I saw something in the control panel to switch between function keys and multimedia keys but alas I couldn't find it. Here's what you have to do: As the system in booting hit F2 to go to Setup. From there click 'System Configuration' then 'Function Key Behavior'. Switch to 'Fuction Key First' then hit the 'Apply' box, then 'Exit'. Now the function keys will work normaly and you can hold 'Fn' to use the multimedia fuctions.

Seriously, I searched through offline help for an hour and a half, then these forums for half an hour and I'm amazed at how hard it is to find out how to do something this simple. I'm just glad I've been messing around with computers since the days of DOS so I thought to look in the BIOS.

replace forward slash (\) using javascript

var strReplace = "C:\Temp\Temp1\Temp2\text.jpeg";

strReplace = strReplace.replace(new RegExp(/\\/g),"/");

Get total no of columns and rowcount for each table of database

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Auto complete Extender with Dropdown Like binding

<ajaxtoolkit:autocompleteextender behaviorid="AutoCompleteEx" id="autoComplete1" onclientitemselected="IAmSelected" runat="server" servicemethod="GetCompletionListKeyValuePair" servicepath="~/Services/AutoComplete.asmx" targetcontrolid="myTextBox">
</ajaxtoolkit:autocompleteextender>


function IAmSelected( source, eventArgs ) {
alert( " Key : "+ eventArgs.get_text() +" Value : "+eventArgs.get_value());
}

[WebMethod]

public string[] GetCompletionList(string prefixText, int count)
{

if (count == 0)
{

count = 10;

}

if (prefixText.Equals("xyz"))
{

return new string[0];

}

Random random = new Random();

List items = new List(count);

for (int i = 0; i < count; i++)
{

char c1 = (char)random.Next(65, 90);

char c2 = (char)random.Next(97, 122);

char c3 = (char)random.Next(97, 122);

items.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(prefixText + c1 + c2 + c3, i.ToString()));

}

return items.ToArray();

}

Insert Query Generator -- Give name of table as paramter and will generate insert quey

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[InsertGenerator]
(@tableName varchar(100))
as
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(max) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0 begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')BEGIN
--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+replace(' +@colName+','''''''','''''''''''')+'''''+''''',''NULL'')+'',''+'
print @stringData
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE IF @dataType='datetime'BEGIN
--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+' --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+' -- 'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE IF @dataType='image' BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+' --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END
SET @string=@string+@colName+','FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(Max)
PRINT substring(@stringData,0,len(@stringData)-2)
print substring(@string,0,len(@string))
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2) +'''+'')'' FROM '+@tableName
exec sp_executesql @query--select @query
PRINT @query
CLOSE cursCol
DEALLOCATE cursCol