Sunday, October 05, 2008

Accessing Data in an Excel File using .Net

In this article I will show how you can open Microsoft Excel files using .Net.
You can use OLE objects to access data in Excel files as of you are accessing SQL Server data using SQL objects.

// Create an OLEDBConnection to connect to the Excel file.
// I'm getting the required file by using a file dialog.
// The @ symbol makes the string to contain any special characters inside the string without breaking the string.
OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName.ToString()+ @";Extended Properties=""Excel 8.0;HDR=Yes;""");
// Open the connection.
dbConnection.Open();
// Create a command object to work on the data.
// Note that I have given the sheet name as [Sheet1$] to retrieve data from that named sheet in the particular Excel file.
OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", dbConnection);
// Creating a data reader to read data.
OleDbDataReader dbReader = dbCommand.ExecuteReader();
// Get the position of the column Desc 1.
int SearchingItem = dbReader.GetOrdinal("Desc 1");
// Read through the data.
while (dbReader.Read())
{
// Traverse through all the data columns.
for (int i = 0; i <>
{
ExcelSheet.Cells[CurrentRowCount, i + 1] = dbReader.GetValue(i).ToString();
}
}

Sunday, September 21, 2008

Creating a Web Part to use with SharePoint

Creating a web part for SharePoint consists of several steps.
1. Create a user control.
2. Create the web part DLL to hold the user control.
3. Add user control and web part to SharePoint and make the web part safe.
4. Add the web part to the web parts gallery.
5. Finally add the web part to the page.
Lots of steps ha, don’t worry we’ll do step by step.

1. Create a user control.
a. Start Visual Studio and click on File -> New -> Web Site.

b. Select ASP.NET Web Site as the template, select C# as the language, and give a file system location and a name (prjFirst) for the project before pressing Ok. c. Now we need to add a page to write our code. To do that, right click on top of the project and click on Add New Item....
d. Select Web User Control as the template, select the language as C#, give a name for the user control (ucFirst) and ensure that Place code in separate file check box is checked before pressing Ok. e. Now you need to use the required web controls and write the code required in the appropriate files. For this demonstration I will put a label and a button only. When the button is clicked I will display a greeting message in the label.
f. After creating the controls and naming them as required I need to import Microsoft.SharePoint.dll since I am going to access SharePoint object model. To do this first we need to refer this dll. Right click on the project and click on Add Reference..., select the Browse tab and go to folder C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI and select Microsoft.SharePoint.dll to add the reference to the project. If your machine doesn’t have a SharePoint installation then you need to copy this file from a SharePoint installed machine to your machine and refer it.
g. After coding for the button click my code looks like this. h. Now build and see if the page is having any errors. If no errors step one is done.

2. Create the web part DLL to hold the user control.
a. Start another instance of Visual Studio and click File -> New -> Project....

b. Select Class Library as the template under C# project type and give a location and a name (clFirstWebPart) before clicking Ok. c. Right click and delete the Class1.cs file since we would like to create a new class with a meaningful name.
d. Now right click on the project and click on Add -> New Item..., then select Class as the template and give an appropriate name (csFirstWebPart.cs) and click Add. e. Now you need to refer System.Web namespace. To do so right click on project and click ok Add Reference..., then select the System.Web and click Ok. f. After inheriting the class from the WebPart override the CreateChildControls method to create a user control and load the user control we create earlier in step 1.
We are loading the user control file (ucFirst.ascx) from the SharePoint UserControls folder which we are to copy later. g. Now build the project by right clicking on project and selecting Build. If no errors occur then that means we have completed step 2 as well.
3. Add user control and web part to SharePoint and make the web part safe.
1. Now we have a user control and a web part dll which loads the user control. In order to use this in SharePoint we need to add these to SharePoint. This has 3 tasks to be done in the SharePoint Server machine.
a. Adding User Control files.

i. Go to the location that you created the user control project in step 1 and copy the 2 files of the user control (ucFirst.ascx and ucFirst.ascx.cs). ii. Go to the root folder of the web site (C:\Inetpub\wwwroot). Now find the folder named UserControls and paste the copied two files there. Notes –
· I’m trying to put the newly created web part to the default web application. So I am using C:\Inetpub\wwwroot\UserControls folder. If you need to use this web part then you needs to use the appropriate folder. For example if I am to use this in the web application http://ironone-ms-t01:16173/ then I have to create UserControls folder in C:\Inetpub\wwwroot\wss\VirtualDirectories\16173. To find the correct location of your web application use IIS Manager web site properties page. · If the UserControls folder doesn’t exist you can create it there.
b. Adding Web Part DLL.
i. Go to the location that you created the web part DLL project in step 2 and copy the output DLL file (clFirstWebPart.dll) of it. ii. Go to the root folder of the web site (C:\Inetpub\wwwroot). Now find the folder named bin and paste the copied file there. Notes –
· I’m trying to put the newly created web part to the default web application. So I am using C:\Inetpub\wwwroot\UserControls folder. If you need to use this web part then you needs to use the appropriate folder. For example if I am to use this in the web application http://ironone-ms-t01:16173/ then I have to create bin folder in C:\Inetpub\wwwroot\wss\VirtualDirectories\16173. To find the correct location of your web application use IIS Manager web site properties page. · If the bin folder doesn’t exist you can create it there.
c. Making the web part a trusted web part.
In order to use the web part we need to make the web part a trusted web part. To do this there are two ways,
1. Put an entry to the web.config file of the application.
2. Give the dll a fully trusted name and putting it to GAC.
I will be using the first method. Make a backup of the web.config file located in the C:\Inetpub\wwwroot and open it. Then add an entry to the SafeControls section. The easiest way is to copy the last line and paste it at the end and changing the Assembly and the Namespace to the dlls’ name (clFirstWebPart). Save the config file and to be in safe side do an IIS reset by typing IISReset in the Run Window.


4. Add the web part to the web parts gallery.
a. Open an Internet Explorer and browse to the site which you need to add the web part. Then click on Site Actions -> Site Settings -> Modify All Site Settings.
b. Click on Web Parts under Galleries in the Site Settings page to open the Web Part Gallery. c. In the web part gallery page you will see all the added web parts. But not our one. So we need to add that here. To do this click on New button. Note –
The files with .dwp extension are Windows SharePoint Services 2.0 web parts.
The files with .webpart are the new Windows SharePoint Services 3.0 web parts.
d. This list will have the newly added web part (clFirstWebPart.clFirstWebPart). Check the check box in front of it and click on Populate Gallery. e. Now our new web part will appear in the Web Part Gallery. This is enough but it is always better to change some properties of the web part. So open up the web part property page by clicking on the Edit button. Change the Title, Description and the Group of the web part and press Ok. f. Remember that we need to test the web part before we add it to a web page. To do this simply click on the web part. g. If you are also having the default settings then you will definitely get an error of request for the permission of type.... This is because we didn’t change the SharePoint trust level. To change this I will use the easy way of changing the trust level in the configuration file to full though the best way is to create a trust file.
Open the web.config file at C:\Inetpub\wwwroot and find the entry and change the trust level to Full. Do an IISReset and refresh the internet explorer page. Now the web part will be displayed to you.

5. Finally add the web part to the page.
Now our newly created web part is available for us to use. We’ll now add it to a page.
a. Open the required web page in the internet explorer and click Site Actions -> Edit Page to start editing mode.

b. Now on any web part zone click ‘Add a Web Part’ button. c. Among all the web parts listed select the web part that we have created (First Web Part), select the check box in front of it and click the Add button. You will see the web part appearing on the page.
d. To finalize the page click Publish button on the page. e. Click on ‘Click Me’ button and see the output. Even though adding a new web part was bit of a process, after adding doing changes to it is not that difficult. What you have to do is change the ascx and ascx.cs files and copy the new ones to the UserControls folder. I have simply changed the message to a new one using this method.

Friday, September 19, 2008

Microsoft Live Mesh

Have you seen Microsoft's latest Live product? Its named as Live Mesh.
Live mesh is all about seamlessly connecting to people, information, devices and programs that you care about from wherever you are.
Have a Live Mesh tour to get to know more about the product by using the following link.
Interested about it? Then you better visit the Live Mesh site.

Tuesday, September 16, 2008

How to login to more than one Google Talk Session

Did you ever had the requirement to login to more than one Google accounts using Google Talk simultaneously?
I had since our office used Google accounts and I needed to be online in the official chat as well as in my personal account, and I found a way how to do it. So I thouoght it is better to share it with you.
This can be achieved by simply passing a parameter while initiating Google Talk (GTalk).

1. If you don't have a shortcut for GTalk create one by actions such as right clicking and clicking on Send To -> Desktop (create shortcut).
2. Right click on top of the created shortcut and click on Properties.
3. Type /nomutex after the googletalk.exe path in the Target field as shown below.
4. Press Ok to save the change.
5. Now start GTalk by double clicking on top of the shortcut. Now you will be able to open up many GTalk sessions and login to different accounts simultaneously.

Sunday, September 14, 2008

Windows Server 2008

 
The next version of Windows Server is here. Windows Server 2008. Check what it offers for you. By visiting Microsoft.
You can even download a trial from Microsoft.

Monday, August 11, 2008

HTML Meta Tags

Do you know what can be done with HTML Meta tags?
If not then you will also struggle when trying to do a page refresh or a redirect.
To get more details on how to please refer the following web site.
http://webdesign.about.com/od/metataglibraries/a/aa080300a.htm

Thursday, July 31, 2008

Implementing a File Download in ASP.NET

Recently I gave some help to a colleague of mine implementing a file download. So I thought of sharing it with you.
If you need to make a web site user to download a file from your site you have to use HTTPResponse.Page.Response object. By using this you will be able to make the user download numerous kinds of files to their machines. The code required is as follows.

// This defines the type of the file that you are allowing to download.
Response.ContentType = "application/vnd.ms-excel";
// Path to the file to be downloaded.
string FilePath = MapPath("Xcel.xlsx");
// Initiating the file download.
Response.WriteFile(FilePath);
Response.End();

The above code will let the user download an Excel file named Xcel. Aprt from Excel you can use other file types as well by changin the Response.ContentType to the required file type.
A list of available content types are as follows.

Wednesday, July 16, 2008

SQL Server Native Client Cannot be Found

Recently when I try to install SQL Server 2005 I got the following error saying that Microsoft SQL Server Native Client cannot be found. A screen shot of the error is as follows.This error is due if the SQL Server Native Client is already installed using a different edition of SQL Server.
The resolution for this is to uninstall all the SQL related stuff (specially SQL Native Client) which are installed in the computer and try the SQL installation again after doing a system restart.

Wednesday, July 02, 2008

Creating a Crossover Cable

A crossover cable is a special cable we use when we needs to connect two computers together directly. But the thing is when creating the crossover cable you have to create it properly. As of the name suggests we have to cross some cables when clipping the network cable.

The following image will show how to do the cross.

If you have additional time you can visit the following site which is a good site explaining different color code standards.
http://www.patraswireless.net/tutorial/basic%20tutorial/tut-equipemt/cable_utp.htm

If you already have an existing network cable and if you would like to make it a crossover cable, the following site will be useful since there you can change the wires (by changing the color drop down controls) as it is clipped in one end and can find out the way the other end needs to be clipped.

http://www.davidj.org/tools/cabler/cabler.html

Tuesday, June 24, 2008

Configuring Usage Reporting in SharePoint

SharePoint has a nice feature to trace the activity happening around the site. This is called Usage Reporting. Initially this is been disabled in SharePoint. To use it first you needs to enable this from several levels as follows.
1. Enable Usage Reporting from Windows SharePoint Services (WSS) level.
2. Enable Usage Reporting from Microsoft Office SharePoint Services (MOSS) level.
3. Activate Usage Reporting.
4. View Usage Reports.

1. Enable Usage Reporting from Windows SharePoint Services (WSS) level.
1)On the Central Administration home page, click Operations.
2)On the Operations page, in the Logging and Reporting section, click Usage analysis processing.
3)On the Usage Analysis Processing page, in the Logging Settings section, select Enable logging.
4)Keep the default or type a log file location and number of log files to create.
5)In the Processing Settings section, select Enable usage analysis processing, and then select a time to run usage processing.
6)Click OK.

Now we have to enable in MOSS.
2. Enable Usage Reporting from Microsoft Office SharePoint Services (MOSS) level.
1)On the Shared Services Provider (SSP) home page, in the Office SharePoint Usage Reporting section, click Usage reporting.
2)On the Configure Advanced Usage Analysis Processing page, in the Processing Settings section, click Enable advanced usage analysis processing.
3)In the Search Query Logging section, select Enable search query logging.
4)Click OK.

3. Now Activate Usage Reporting.
1)On the Site Actions menu, click Site Settings.
2)On the Site Settings page, in the Site Collection Administration section, click Site collection features.
3)On the Site Collection Features page, click the Activate button for the Reporting feature.

Now everything done. You just have to wait and see the reports when they are available.
Reports can be viewed in several places,
1)Site administrators, including administrators of the SSP administration site, can view usage reporting for their site by clicking Site usage reports in the Site Administration section of the Site Settings page.
2)Site collection administrators can view usage reporting by clicking Site collection usage reports in the Site Collection Administration section of the Site Settings page.
3)Site collection administrators for the SSP administration site can view a usage summary by clicking Usage summary in the Site Collection Administration section of the Site Settings page.
4)SSP administrators for search can view search usage reports by clicking Search usage reports in the Search section of the SSP home page.

If you would like you can go through the Microsoft TechNet site which I too referred by visiting the following link.
http://technet.microsoft.com/en-us/library/cc262541(TechNet.10).aspx

Monday, June 23, 2008

SoftLogic -> IronOne Technologies

I thought to get a bit of a change in my Carrier. So thought of leaving SoftLogic and joining IronOne Technologies.
Goodbye my SoftLogic friends, Please keep in touch.

Wednesday, May 07, 2008

Know More On Finding Stuff

Recently I came up to see an article showing how you can improve the searching capabilities while using Google. It is more towards finding things on your GMail. But definitely you will be able to use some of the skills you get on Google search as well.
Thought it would be helpful to you.
http://gmailblog.blogspot.com/2008/05/how-to-find-any-email-with-gmail-search.html