Export Datatable to Excel

Export Datatable to Excel(Not HTML Tables)

Recently I working with export data to an excel file, but had faced a lot of problems. The main problem of it: The exported file is saved as HTML tables with XLS extension, not the actual XLS format. In this way, I can’t re-import the data using the OleDbConnection.

I have been searching around the net, but no exact solution that suit my situation. Finally, I had found another way to export data into the actual XLS format, by using Excel.Application.

First thing that you need to do is to add Excel dll (Microsoft Excel 11.0 Object Library) as a reference.

Here are the codes.

Private Function ExportToExcel(ByVal dt As System.Data.DataTable)
    Dim xlsApp As New Excel.Application
    Dim xlsWorkbook As Excel.Workbook
    Dim xlsWorksheets As Excel.Worksheets
    Dim xlsWorksheet As Excel.Worksheet
    Dim strhdr As String
    Dim row As Integer
    Dim drow As DataRow
    Dim strFile As String = "test.xls"
    Dim filename As String = Server.MapPath("Doc") & "\" & strFile

    If dt.Rows.Count > 0 Then
        'Create new workbook
        xlsWorkbook =
        xlsApp.Workbooks.Add

        'Get the first worksheet
        xlsWorksheet = CType(xlsWorkbook.Worksheets(1), Excel.Worksheet)

        'Activate current worksheet
        xlsWorksheet.Activate()

        'Set header row to row 1
        row = 1

        'Add table headers to worksheet
        xlsWorksheet.Cells(row,1).Value = "NAME"
        xlsWorksheet.Cells(row, 2).Value = "JOB POSITION"
        xlsWorksheet.Cells(row, 3).Value = "ORIGIN"

        'Format header row (bold, extra row height, autofit
width)
        With xlsWorksheet.Range("A" & row, "C" & row)
            .Font.Bold = True
            .Rows(row).RowHeight = 1.5 * xlsWorksheet.StandardHeight
            .EntireRow.AutoFit()
        End With

        'Freeze the column headers
        With xlsWorksheet.Range("A" & row + 1, "C" & row + 1).Select
            xlsApp.ActiveWindow.FreezePanes = True
        End With

        'Write data to Excel worksheet
        For Each drow In dt.Rows
            row += 1
            If Not IsDBNull(dr.Item("NAME")) Then xlsWorksheet.Cells(row, 1).Value = dr.Item("NAME")
            If Not IsDBNull(dr.Item("JOB POSITION")) Then xlsWorksheet.Cells(row, 2).Value = dr.Item("JOB POSITION")
            If Not IsDBNull(dr.Item("ORIGIN")) Then xlsWorksheet.Cells(row, 3).Value =
dr.Item("ORIGIN")
        Next

        'Format data rows (align to top, autofit width and height)
        With xlsWorksheet.Range("A2", "C" & row)
            .VerticalAlignment = CType(XlVAlign.xlVAlignCenter, Excel.XlVAlign)
            .HorizontalAlignment = CType(XlHAlign.xlHAlignLeft, Excel.XlHAlign)
            .EntireColumn.AutoFit()
            .EntireRow.AutoFit()
        End With

        'Make excel workbook visible to user after all data has been added to worksheet
        xlsApp.DisplayAlerts = False
        xlsWorkbook.Close(True, filename)

        'Export data to client machine
        strhdr = "attachment;filename=" & strFile
        With Response
            .Clear()
            .ContentType = "application/vnd.ms-excel"
            .ContentEncoding = System.Text.Encoding.Default
            .AppendHeader("Content-Disposition", strhdr)
            .WriteFile(filename)
            .Flush()
            .Clear()
            .Close()
        End With
    End If
End Function

Difference between NVARCHAR and VARCHAR in SQL Server

Difference between NVARCHAR and VARCHAR in SQL Server

The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions areVARCHAR and NVARCHAR: what's the difference between the two, and how important is the difference?

VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.

The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character, all the time — and converted to whatever codepage is being used by the database connection on output (typically UTF-8). That said, NVARCHAR strings have the same length restrictions as their VARCHAR cousins — 8,000 bytes. However, since NVARCHARs use two bytes for each character, that means a given NVARCHAR can only hold 4,000 characters (not bytes) maximum. So, the amount of storage needed for NVARCHAR entities is going to be twice whatever you'd allocate for a plain old VARCHAR.

Because of this, some people may not want to use NVARCHAR universally, and may want to fall back on VARCHAR — which takes up less space per row — whenever possible.

Here's an example of how to mix and match the use of the two types. Let's say we have a community website where people log in with a username, but can also set a public "friendly" name to be more easily identified by other users. The login name can be a VARCHAR, which means it must be 8-bit ASCII (and it can be constrained further to conventional alphanumerics with a little more work, typically on the front end). The friendly name can be an NVARCHAR to allow Unicode entities. This way you're allowing support for Unicode, but only in the place where it matters most — both for the users, and where the extra storage space is going to be put to the best possible use.

VARCHAR and NVARCHAR in SQL Server 2005

One fairly major change to both VARCHAR and NVARCHAR in SQL Server 2005 is the creation of the VARCHAR(MAX) and NVARCHAR(MAX) data types. If you create a VARCHAR(MAX) column, it can hold up to 2^31 bytes of data, or 2,147,483,648 characters; NVARCHAR(MAX) can hold 2^30 bytes, or 1,073,741,823 characters.

These new data types are essentially replacements for the Large Object or LOB data types such as TEXT and NTEXT, which have a lot of restrictions. They can't be passed as variables in a stored procedure, for instance. The(MAX) types don't have those restrictions; they just work like very large string types. Consequently, if you're in the process of re-engineering an existing data design for SQL Server 2005, it might make sense to migrate some (although not all!) TEXT / NTEXT fields to VARCHAR(MAX) / NVARCHAR(MAX) types when appropriate.

The big difference between VARCHAR and NVARCHAR is a matter of need. If you need Unicode support for a given data type, either now or soon enough, go with NVARCHAR. If you're sticking with 8-bit data for design or storage reasons, go with VARCHAR. Note that you can always migrate from VARCHAR to NVARCHAR at the cost of some room -- but you can't go the other way 'round. Also, because NVARCHAR involves fetching that much more data, it may prove to be slower depending on how many table pages must be retrieved for any given operation.

SQL2005 String functions

SQL 2005 String Functions

String Functions are used for manipulating string expression. 

(Pass strings using single quotes)

Len('') - Returns length of string.

select Len("Urgent Work") will return 11

Lower('') - Convert all characters to lowercase characters.

select Lower('Urgent Work') will return urgent work

Upper('') - Convert all characters to uppercase characters.

select Upper('Urgent Work') will return URGENT WORK

LTrim('') - Removes spaces from given character strings on left.

select LTrim(' Urgent Work') will return Urgent Work

LTrim & RTRIM & Trim donot remove tab or line feed character.

RTrim('') - Removes space from given character strings on right.

select LTrim('Urgent Work ')  returns Urgent Work

Trim('') - Removes spaces from given character strings from both left and right.

select LTrim(' Urgent Work ')  returns Urgent Work

SubString('') - Returns a part of string from original string.

SubString(character_expression, position, length)

position - specifies where the substring begins.

length - specifies the length of the substring as number of characters.

Example: select SubString('Urgent Work',1,6)  return "Urgent"

Replace('') - Replace the desired string within the original string.

Replace(character_expression, searchstring, replacementstring)

SearchString - string which you want to replace.

ReplaceString - new string which you want to replace with

select replace('Think Big To Achieve Big', 'Big', 'Small')

Original - Think Big To Achieve Big

Result - Think SmallTo Achieve Small

Right('') - extract particular characters from right part of given string expression.

select right('Think High To Achieve High',15) will return "To Achieve High"

This function will be helpful when you want particular characters from right part.

Example: Let say i have social security nos. and i want to extract last 4 digit of it.

select right('111-11-1111',4) will return 1111

select right('222-22-2222',4) will return 2222

select right('333-33-3333',4) will return 3333

select right('444-44-4444',4) will return 4444

Complete List of Web 2.0 APIs

Complete List of Web 2.0 APIs

http://programmableweb.com/apilist/bycat

Very useful.  Hope helps!

sql2000 delete rows having duplicate (identical) records

Suppose, we have identical records like :-

name age

***********

RAM 30

RAM 30

we have to keep one entry each for ram:30  and ram:40 and delete duplicate entries:-

set rowcount 1 

(this will remove top 1 row)

select * from temp where name = 'ram'

delete temp where name = 'ram'

set rowcount 0

String manipulations

        Dim strSample As String = "Left Center Right"

        'left

        strSample = strSample.Substring(0, 4)

        'center

        strSample = strSample.Substring(5, 6)

        'right

        strSample = strSample.Substring(12)

        'split

        Dim SplitString As String = "Cat, Dog, Hen, Zebra"

        Dim SplitArr() As String

        Dim i As Integer

        SplitArr = SplitString.Split(","c)

        For i = 0 To SplitArr.Length - 1

            console.writeline(SplitArr(i))

        Next

        'Join

        Dim JoinArr(2) As String

        Dim JoinString As String

        JoinArr(0) = "Give"

        JoinArr(1) = "Take"

        JoinArr(2) = "Apply"

        JoinString = String.Join(",", JoinArr)

        Console.WriteLine(JoinString)

        'insert

        Dim strTemp As String = "This is My Stng"

        strSample = strTemp.Insert(13, "ri")

        Console.WriteLine(strSample)    'OUTPUT: "This is My String".

        'Remove

        Dim RemoveString As String = "This is my Sam@o@o@ple"

        'strSample = "This is my Sample"

        strSample = RemoveString.Remove(14, 5)

        Console.WriteLine(strSample)

        'replace

        Dim ReplaceString = "This is My String"

        ' strSample = "This is Another String"

        strSample = ReplaceString.Replace("My", "Another")

        Console.WriteLine(strSample)

        'Trim Spaces

        Dim SpaceString As String = "        This string will have the spaces removed        "

        Console.WriteLine(SpaceString)

        Dim TrimmedString As String

        TrimmedString = Trim(SpaceString)

        Console.WriteLine(TrimmedString)

        'Trim Other

        Dim HashString As String = "#####Testing!######"

        TrimmedString = HashString.Trim("#")

        Console.WriteLine(TrimmedString)

        'Left Trim ( TrimStart)

        Dim SpaceString1 As String = "        This string will have the leading spaces removed        "

        Console.WriteLine(SpaceString)

        TrimmedString = SpaceString1.TrimStart(" ")

        Console.WriteLine(TrimmedString)

        'Right Trim ( TrimEnd)

        Dim SpaceString2 As String = "        This string will have the trailing spaces removed        "

        Console.WriteLine(SpaceString)

        TrimmedString = SpaceString.TrimEnd(" ")

        Console.WriteLine(TrimmedString)

        'Pad Left

        Dim MainString As String = "This will be padded on the left"

        Dim PaddedString As String

        PaddedString = MainString.PadLeft(36, "@") '36 = new length of string with 5 new chars

        Console.WriteLine(PaddedString)

        'Pad Right

        Dim MainString1 As String = "This will be padded on the right"

        PaddedString = MainString1.PadRight(36, "@") '36 = new length of string with 5 new chars

        Console.WriteLine(PaddedString)

        'UPPER CASE

        Dim MixedString As String = "UpPeR oR LoWeR cAsE"

        Dim UPPERString As String

        ' UPPERString = "UPPER OR LOWER CASE"

        UPPERString = MixedString.ToUpper

        Console.WriteLine(UPPERString)

        'lower case

        Dim MixedString1 As String = "UpPeR oR LoWeR cAsE"

        Dim lowerString As String

        'lowerString = "upper or lower case"

        lowerString = MixedString1.ToLower

        Console.WriteLine(lowerString)

        'TitleCase

        Dim TitleString As String = "UPPER lower Title"

        TitleString = TitleString.ToLower()  ' mainly because of UPPER not changing to title

        TitleString = System.Globalization.CultureInfo.InstalledUICulture.TextInfo.ToTitleCase(TitleString)

        Console.WriteLine(TitleString)

        'Format

        Dim UnFormattedDateTime As Date = #1/27/2001 5:04:23 PM#

        Dim strFormattedString As String

        ' Returns current system time in the system-defined long time format.

        strFormattedString = Format(Now(), "Long Time")

        ' Returns current system date in the system-defined long date format.

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(Now(), "Long Date")

        Console.WriteLine(strFormattedString)

        ' Also returns current system date in the system-defined long date 

        ' format, using the single letter code for the format.

        strFormattedString = Format(Now(), "D")

        Console.WriteLine(strFormattedString)

        ' Returns the value of UnFormattedDateTime in user-defined date/time formats.

        strFormattedString = Format(UnFormattedDateTime, "h:m:s")   ' Returns "5:4:23".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "hh:mm:ss tt")   ' Returns "05:04:23 PM".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "dddd, MMM d yyyy")   ' Returns "Saturday,

        ' Jan 27 2001".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "HH:mm:ss")   ' Returns "17:04:23"

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(23)   ' Returns "23".

        Console.WriteLine(strFormattedString)

        ' User-defined numeric formats.

        strFormattedString = Format(5459.4, "##,##0.00")   ' Returns "5,459.40".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(334.9, "###0.00")   ' Returns "334.90".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(5, "0.00%")   ' Returns "500.00%".

        Console.WriteLine(strFormattedString)

        'Concat

        Dim ConcatString As String

        Dim aString As String = "A"

        Dim bString As String = "B"

        Dim cString As String = "C"

        Dim dString As String = "D"

        ' ConcatString = "ABCD"

        ConcatString = String.Concat(aString, bString, cString, dString)

        Console.WriteLine(ConcatString)

        'IndexOf

        Dim MainString2 As String = "ABCDE"

        Dim Result As Integer

        Result = MainString2.IndexOf("D")  ' result = 3

        Console.WriteLine("D is Character number : " & Result.ToString)

        'Chars

        Dim MainString3 As String = "ABCDE"

        Dim ResultChar As Char

        ResultChar = MainString3.Chars(3) ' resultChar = "D"

        Console.WriteLine("Character 3 is : " & ResultChar.ToString)

        'LastIndexOf

        Dim MainString4 = "A\B\C\D\E\F\G\H\I\J"

        Result = MainString.LastIndexOf("\")

        Console.WriteLine("Last occurence of ' \ ' is at Position : " & Result.ToString())

        'Compare

        Dim FirstString As String = "Alphabetical"

        Dim SecondString As String = "Order"

        Dim ThirdString As String = "Order"

        Dim FourthString As String = "Alphabetical"

        Dim Result1 As Integer

        Dim Result2 As Integer

        Result1 = String.Compare(FirstString, SecondString)

        Console.WriteLine("Result = " & Result1.ToString & Environment.NewLine & "First String Is Smaller Than Second")

        Result2 = String.Compare(ThirdString, FourthString)

        Console.WriteLine("Result = " & Result2.ToString & Environment.NewLine & "Third String Is Greater Than Fourth")

        'Length

        Dim MainString5 As String = "String To Test The Length"

        Dim Result3 As Integer = MainString.Length

        Console.WriteLine(Result.ToString & " Characters")

        'Copy

        Dim EmptyString As String

        Dim CopyString As String = "This Will Be Copied Into An Empty String"

        EmptyString = String.Copy(CopyString)

        Console.WriteLine(EmptyString)

String manipulations

        Dim strSample As String = "Left Center Right"

        'left

        strSample = strSample.Substring(0, 4)

        'center

        strSample = strSample.Substring(5, 6)

        'right

        strSample = strSample.Substring(12)

        'split

        Dim SplitString As String = "Cat, Dog, Hen, Zebra"

        Dim SplitArr() As String

        Dim i As Integer

        SplitArr = SplitString.Split(","c)

        For i = 0 To SplitArr.Length - 1

            console.writeline(SplitArr(i))

        Next

        'Join

        Dim JoinArr(2) As String

        Dim JoinString As String

        JoinArr(0) = "Give"

        JoinArr(1) = "Take"

        JoinArr(2) = "Apply"

        JoinString = String.Join(",", JoinArr)

        Console.WriteLine(JoinString)

        'insert

        Dim strTemp As String = "This is My Stng"

        strSample = strTemp.Insert(13, "ri")

        Console.WriteLine(strSample)    'OUTPUT: "This is My String".

        'Remove

        Dim RemoveString As String = "This is my Sam@o@o@ple"

        'strSample = "This is my Sample"

        strSample = RemoveString.Remove(14, 5)

        Console.WriteLine(strSample)

        'replace

        Dim ReplaceString = "This is My String"

        ' strSample = "This is Another String"

        strSample = ReplaceString.Replace("My", "Another")

        Console.WriteLine(strSample)

        'Trim Spaces

        Dim SpaceString As String = "        This string will have the spaces removed        "

        Console.WriteLine(SpaceString)

        Dim TrimmedString As String

        TrimmedString = Trim(SpaceString)

        Console.WriteLine(TrimmedString)

        'Trim Other

        Dim HashString As String = "#####Testing!######"

        TrimmedString = HashString.Trim("#")

        Console.WriteLine(TrimmedString)

        'Left Trim ( TrimStart)

        Dim SpaceString1 As String = "        This string will have the leading spaces removed        "

        Console.WriteLine(SpaceString)

        TrimmedString = SpaceString1.TrimStart(" ")

        Console.WriteLine(TrimmedString)

        'Right Trim ( TrimEnd)

        Dim SpaceString2 As String = "        This string will have the trailing spaces removed        "

        Console.WriteLine(SpaceString)

        TrimmedString = SpaceString.TrimEnd(" ")

        Console.WriteLine(TrimmedString)

        'Pad Left

        Dim MainString As String = "This will be padded on the left"

        Dim PaddedString As String

        PaddedString = MainString.PadLeft(36, "@") '36 = new length of string with 5 new chars

        Console.WriteLine(PaddedString)

        'Pad Right

        Dim MainString1 As String = "This will be padded on the right"

        PaddedString = MainString1.PadRight(36, "@") '36 = new length of string with 5 new chars

        Console.WriteLine(PaddedString)

        'UPPER CASE

        Dim MixedString As String = "UpPeR oR LoWeR cAsE"

        Dim UPPERString As String

        ' UPPERString = "UPPER OR LOWER CASE"

        UPPERString = MixedString.ToUpper

        Console.WriteLine(UPPERString)

        'lower case

        Dim MixedString1 As String = "UpPeR oR LoWeR cAsE"

        Dim lowerString As String

        'lowerString = "upper or lower case"

        lowerString = MixedString1.ToLower

        Console.WriteLine(lowerString)

        'TitleCase

        Dim TitleString As String = "UPPER lower Title"

        TitleString = TitleString.ToLower()  ' mainly because of UPPER not changing to title

        TitleString = System.Globalization.CultureInfo.InstalledUICulture.TextInfo.ToTitleCase(TitleString)

        Console.WriteLine(TitleString)

        'Format

        Dim UnFormattedDateTime As Date = #1/27/2001 5:04:23 PM#

        Dim strFormattedString As String

        ' Returns current system time in the system-defined long time format.

        strFormattedString = Format(Now(), "Long Time")

        ' Returns current system date in the system-defined long date format.

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(Now(), "Long Date")

        Console.WriteLine(strFormattedString)

        ' Also returns current system date in the system-defined long date 

        ' format, using the single letter code for the format.

        strFormattedString = Format(Now(), "D")

        Console.WriteLine(strFormattedString)

        ' Returns the value of UnFormattedDateTime in user-defined date/time formats.

        strFormattedString = Format(UnFormattedDateTime, "h:m:s")   ' Returns "5:4:23".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "hh:mm:ss tt")   ' Returns "05:04:23 PM".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "dddd, MMM d yyyy")   ' Returns "Saturday,

        ' Jan 27 2001".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "HH:mm:ss")   ' Returns "17:04:23"

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(23)   ' Returns "23".

        Console.WriteLine(strFormattedString)

        ' User-defined numeric formats.

        strFormattedString = Format(5459.4, "##,##0.00")   ' Returns "5,459.40".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(334.9, "###0.00")   ' Returns "334.90".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(5, "0.00%")   ' Returns "500.00%".

        Console.WriteLine(strFormattedString)

        'Concat

        Dim ConcatString As String

        Dim aString As String = "A"

        Dim bString As String = "B"

        Dim cString As String = "C"

        Dim dString As String = "D"

        ' ConcatString = "ABCD"

        ConcatString = String.Concat(aString, bString, cString, dString)

        Console.WriteLine(ConcatString)

        'IndexOf

        Dim MainString2 As String = "ABCDE"

        Dim Result As Integer

        Result = MainString2.IndexOf("D")  ' result = 3

        Console.WriteLine("D is Character number : " & Result.ToString)

        'Chars

        Dim MainString3 As String = "ABCDE"

        Dim ResultChar As Char

        ResultChar = MainString3.Chars(3) ' resultChar = "D"

        Console.WriteLine("Character 3 is : " & ResultChar.ToString)

        'LastIndexOf

        Dim MainString4 = "A\B\C\D\E\F\G\H\I\J"

        Result = MainString.LastIndexOf("\")

        Console.WriteLine("Last occurence of ' \ ' is at Position : " & Result.ToString())

        'Compare

        Dim FirstString As String = "Alphabetical"

        Dim SecondString As String = "Order"

        Dim ThirdString As String = "Order"

        Dim FourthString As String = "Alphabetical"

        Dim Result1 As Integer

        Dim Result2 As Integer

        Result1 = String.Compare(FirstString, SecondString)

        Console.WriteLine("Result = " & Result1.ToString & Environment.NewLine & "First String Is Smaller Than Second")

        Result2 = String.Compare(ThirdString, FourthString)

        Console.WriteLine("Result = " & Result2.ToString & Environment.NewLine & "Third String Is Greater Than Fourth")

        'Length

        Dim MainString5 As String = "String To Test The Length"

        Dim Result3 As Integer = MainString.Length

        Console.WriteLine(Result.ToString & " Characters")

        'Copy

        Dim EmptyString As String

        Dim CopyString As String = "This Will Be Copied Into An Empty String"

        EmptyString = String.Copy(CopyString)

        Console.WriteLine(EmptyString)