OleDbConnection conn = new OleDbConnection(); OleDbCommand cmd = new OleDbCommand(); OleDbDataAdapter da = new OleDbDataAdapter(); DataSet ds = new DataSet(); System.Data.DataTable dt = null; //string query = null; string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;"""; protected void Page_Load(object sender, EventArgs e) { //ConfigurationSettings.AppSettings[FileExtension]; } protected void Button1_Click(object sender, EventArgs e) { string validFileExtensions = Config.Get("ValidFileExtension"); int uploadSize = Convert.ToInt32( Config.Get("DocUploadValidationSize")); string validationMEssage = Config.Get("DocUploadValidationMessage"); connectionString = string.Format(connectionString, @"d:\R and D\Book1.xlsx"); if (FileUpload1.HasFile) { string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName); if (validFileExtensions.IndexOf(fileExt)>=0) { try { if (FileUpload1.PostedFile.ContentLength >= uploadSize) { Literal1.Text = validationMEssage; return; } FileUpload1.SaveAs(@"C:\Users\user5\Desktop" + FileUpload1.FileName); Label1.Text = "File name: " + FileUpload1.PostedFile.FileName + "<br>" + FileUpload1.PostedFile.ContentLength + " kb<br>" + "Content type: " + FileUpload1.PostedFile.ContentType; using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); OleDbCommand cmd = conn.CreateCommand(); dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String[] excelSheets = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } // Loop through all of the sheets if you want too... for (int j = 1; j < excelSheets.Length; j++) { if (j == 1) { cmd.CommandText = "SELECT ID,UserName,Designation FROM [" + excelSheets[1] + "]"; } else if (j == 2) { cmd.CommandText = "SELECT ID,CITY,STATE FROM [" + excelSheets[2] + "]"; } else if (j == 3) { cmd.CommandText = "SELECT ID,Client,City FROM [" + excelSheets[3] + "]"; } //cmd.CommandText = "SELECT ID,City,State FROM [WorkSheet2$]"; //cmd.CommandText = "SELECT ID,City,State FROM [WorkSheet3$]"; //using (OleDbCommand cmd = conn.CreateCommand()) // { using (OleDbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader["WorkSheet1"].ToString()); Console.WriteLine(reader["WorkSheet2"].ToString()); Console.WriteLine(reader["WorkSheet3"].ToString()); } } } Console.Read(); //} } } catch (Exception ex) { Label1.Text = "ERROR: " + ex.Message.ToString(); } } else { Label1.Text = "Upload Valid File!"; } } else { Label1.Text = "You have not specified a file."; } } }
while (reader.Read()) { Console.WriteLine(reader["WorkSheet1"].ToString()); Console.WriteLine(reader["WorkSheet2"].ToString()); Console.WriteLine(reader["WorkSheet3"].ToString()); } } }
try/catch
Console.WriteLine(reader.GetString(reader["WorkSheet1"]));
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)