Wednesday, June 24, 2009

Read Excel files using C-Sharp windows application

This article explains how to read excel files using c sharp in windows applications.

using System.Data.OleDb;

private void btnRead_Click(object sender, EventArgs e)
{
try
{
string connStr = connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\Courier.xls;Extended Properties=ImportMixedTypes=Text;Excel 8.0;HDR=Yes;IMEX=1;";

//You must use the $ after the object you reference in the spreadsheet
OleDbDataAdapter adap = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connStr);

DataSet ds = new DataSet();
adap.Fill(ds, "Courier");
dataGridView1.DataSource = ds.Tables["Courier"].DefaultView;

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

Explanation of the above code:
Here I used OleDb to read the excel file. The excel file resides with exe file as Courier.xls.

Sheet1$ is the name of excel sheet.
Courier is the name of file so i am using the same name in filling the DataSet.

Now at last the data is filled into dataGridView.

Now if you are not able to read the integer value or any value other then text then
include the
Extended Properties=ImportMixedTypes=Text;Excel 8.0;HDR=Yes;IMEX=1 property in connection string.

The above code can also be used in a web application, for this you need to replace the
Application.StartupPath with Server.MapPath

HaPpY CoDiNg...........

Tuesday, June 23, 2009

URL rewriting using Global.asax, Error: The resource cannot be found.

Today I implemented my first application having URL rewriting enabled.
The application is created in Asp.net, C# and MS Sql 2005.

The url rewriting logic was working on the my localhost but it didn't worked on production server.

I was getting an error: The resource cannot be found. while accessing the newly created URL pages.

I searched on Google about this and read lot, but nothing worked.

Finally I re-published the website and again uploaded all the files to production server and now everything worked like magic. Here is the URL: http://theplayclan.com/blog


Somewhere I read to check the permissions on server's IIS, this method is explained below:

1) Open IIS manager.
2) Right click on your website's virtual directory, select Properties.
3) Now, click on Configuration (in front of execute permissions option).
4) In the new popped-up window a list comes up as Application Extensions.
5) select .aspx option and click on edit.
6) Make sure that the Verify That File Exists is unchecked. This option explicitly check for the existence of file.
7) Save the settings and try again.

Somewhere it was mention to remove the *.* and regular expression extension mappings from the IIS may also work. The procedure of this is mentioned above.