Skip to main content

Posts

Showing posts from September, 2010

EXTRACT DATA FROM XLS AND XLSX FILE FORMAT

Dear All,
Todays post is about how to load data from excel sheet. This post is emphasized on following file formats:
1) xls (Excel Sheet 1997-2003) 2) xlsx (Excel Sheet 2007)
Both sheets require connection string to extract data from sheet.
Connection String for xls:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0"
Connection String for xlsx:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0"
Below is a small program for your understanding. The program is in ASP.NET.
protectedvoid Page_Load(object sender, EventArgs e) { string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString; // Create the connection object OleDbConnection oledbConn = newOleDbConnection(connString); // Open connection oledbConn.Open(); // Create OleDbCommand object and select data from worksheet Sheet1 OleDbCommand cmd = newOleDbCommand("SELECT * FROM [Sheet1$]", oledbConn); …