Add a Drop Down List into Excel File using C# .NET

Here I will demonstrate to you how to add a drop down list into an Excel File using C# .NET. Before you adding this codes, make sure you have add the Microsoft Excel Object Library as reference into your project.

Here are the codes:

Excel.Application xlsApp = new Excel.ApplicationClass();
Excel.Workbook xlsWorkbook;
Excel.Worksheet xlsWorksheet;
object oMissing = System.Reflection.Missing.Value;

//Create new workbook
xlsWorkbook = xlsApp.Workbooks.Add(true);

//Get the first worksheet
xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

string[] ddl_item ={"Answers","Autos","Finance","Games","Groups","HotJobs","Maps","Mobile Web","Movies","Music","Personals","Real Estate","Shopping","Sports","Tech","Travel","TV","Yellow Pages"};

Range xlsRange;
xlsRange = xlsWorksheet.get_Range("A1","A1");

Excel.DropDowns xlDropDowns;
Excel.DropDown xlDropDown;
xlDropDowns = ((Excel.DropDowns)(xlsWorksheet.DropDowns(oMissing)));
xlDropDown=xlDropDowns.Add((double)xlsRange.Left,(double)xlsRange.Top,(double)xlsRange.Width,(double)xlsRange.Height,true);

//Add item into drop down list
for (int i=0;i<ddl_item.length;i++) {
    xlDropDown.AddItem(ddl_item[i],i+1);
}

xlsApp.DisplayAlerts = false;
xlsWorkbook.Close(true, filename, null);
xlsApp.Quit();

xlsWorksheet = null;
xlsWorkbook = null;
xlsApp = null;

Cheers!

Add a Project or Solution to Source Control

You can add projects and solutions to source control through Solution Explorer in Visual Studio. Follow the steps as below:

1. On the Tools menu, click Options. In the Options dialog box, select Source Control. Select Visual Studio Team Foundation Server from the Current source control plug-in list.

2. In Solution Explorer, right-click the solution, choose Add Solution to Source Control.


3. In the Add Solution to Source Control dialog box, navigate to where you want to add the project or solution. You can either accept default values, or click Make New Folder and enter the desired folder name.

4. Click OK.

15 Date Formats in SQL Server 2000

--'YYYYMMDD'
SELECT CONVERT(CHAR(8), GETDATE(), 112)
--'YYYY-MM-DD'
SELECT CONVERT(CHAR(10), GETDATE(), 23)
--'YYYY-MMM-DD'
SELECT STUFF(CONVERT(CHAR(10), GETDATE(), 23), 6, 2, LEFT(DATENAME(m, GETDATE()), 3))
--'YYMMDD'
SELECT CONVERT(VARCHAR(8), GETDATE(), 12)
--'YY-MM-DD'
SELECT STUFF(STUFF(CONVERT(VARCHAR(8), GETDATE(), 12), 5, 0, '-'), 3, 0, '-')
--'YY-MMM-DD'
SELECT STUFF(STUFF(STUFF(CONVERT(VARCHAR(8), GETDATE(), 12), 3, 2, LEFT(DATENAME(m, GETDATE()), 3)), 6, 0, '-'), 3, 0, '-')
--'MM-DD-YY'
SELECT CONVERT(CHAR(8), GETDATE(), 10)
--'MMDDYY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 10), '-', SPACE(0))
--'MM/DD/YY'
SELECT CONVERT(CHAR(8), GETDATE(), 1)
--'MM/DD/YYYY'
SELECT CONVERT(CHAR(10), GETDATE(), 101)
--'DD-MM-YY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', '-')
--'DD-MMM-YY'
SELECT STUFF(REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', '-'), 4, 2, LEFT(DATENAME(m, GETDATE()), 3))
--'DDMMYY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', SPACE(0))
--'DD/MM/YY'
SELECT CONVERT(CHAR(8), GETDATE(), 3)
--'DD/MM/YYYY'
SELECT CONVERT(CHAR(10), GETDATE(), 103)

Hope this will help. Cheers!

Differences between VARCHAR and NVARCHAR

VARCHAR is an abbreviation for variable-length character string with a maximum limit of 8000 bytes.

NVARCHAR are the Unicode equivalents of VARCHAR. Unicode uses two bytes per character, which allows the storage of multilingual characters with a maximum length of 4000 bytes.

The most common use of NVARCHAR is to store character data that is a mixture of English and non-English symbols, such as English and Chinese. Other languages have an extended set of character codes that need to be saved and this datatype allows for this extension. If your database will not be storing multilingual data you should use the VARCHAR datatype instead. This is because to store the extended character codes for other languages, NVARCHAR requires twice as much space as VARCHAR.

VARCHAR and NVARCHAR in SQL SERVER 2005
One major change to both of the datatypes in SQL SERVER 2005 is the creation of VARCHAR(MAX) and NVARCHAR(MAX) which allows you to stored up to 2GB in a single variable. And it also allows you to use these data types as parameters in your stored procedure, internal variables etc.

How to get IP Address of a computer in C#

To get the IP address of the local machine in C#, system.net namespace is needed. From the IPHostEntry class, we can get the address list in a string array.

using System.Net;
private string GetIP() {
     string strHostName = "";
     strHostName = System.Net.Dns.GetHostName();
     IPHostEntry ipEntry = System.Net.Dns.GetHostEntry(strHostName);
     IPAddress[] addr = ipEntry.AddressList;

     return addr[addr.Length-1].ToString();
}

ASP .NET File Upload Problem

When I upload a large file (> 4MB), I started getting error - "The page cannot be displayed". It took me 15 minutes before I realized that maxRequestLength is causing this problem.

By default, the maxRequestLength will be 4MB. If you are uploading a file which is larger than 4MB, please be sure to edit your web.config file by using httpRuntime attribute.

httpRuntime attribute has several properties, but the attributes that you need to take note are executionTimeout and maxRequestLength. The executionTimeout is the maximum time in seconds a request is allowed to execute before being shut down by ASP .NET automatically, default is 110s. While maxRequestLength is maximum file length that will be uploaded, in kilobytes, default is 4MB.

To set this in your web.config, it should look like as below:

<httpRuntime executionTimeout="600" maxRequestLength="1024000000" />

Hope this can help someone. Cheers!

Filtering and Sorting in ADO .NET

There are many ways to filter data. One way is to filter data by using a WHERE clause on your database query. In ADO .NET, there is some additional functionality that you can use to filter data in a dataset. Two fundamental approaches of it:

(i) DataTable Select Method
(ii) DataView Object

Filtering with Select Method
Imagine that a dataset contains data about Students and Grades tables. To filter on those data with the Grade of A, you can use Select method, which will return an array of rows.

VB
Dim ds_Student As New DataSet
Dim drows() As DataRow
drows = ds_Student.Tables(0).Select("Grade = 'A'")

C#
DataSet ds_Student = new DataSet();
DataRow[] drows;
drows = ds_Student.Tables(0).Select("Grade = 'A'");

Select method does not return filtered tables object as expected, it returns an array of DataRow objects instead. Namely, the returned object cannot be bind directly to a datagrid or other data bound controls.

You can iterate through the array using the foreach statement or for loop.

VB
For i = 0 To drows.Length – 1
     // your process here
Next

C#
for (i = 0; i <= drows.Length - 1; i++) {
     // your process here
}

Filtering and Sorting with DataViews
The DataSet.Table[0].DefaultView property is the DataView associated with a dataset.

A filter can be setup by using RowFilter property. A sort can be setup by using Sort property. See examples as below:

ds_Student.Table(0).DefaultView.Sort = "StudentID";
ds_Student.Table(0).DefaultView.RowFilter = "Grade = 'A'";

DataGrid1.DataSource = ds_Student.Table(0).DefaultView;

Note that a DataView can be bound to datagrid more easily, as shown above.