protected void btnupload_Click(object sender, EventArgs e)
{
if (!FileUpload1.HasFile || string.Compare(".xlsx", Path.GetExtension(FileUpload1.FileName), true) != 0)
{
base.DisplayAlert("Please select a proper EXCEL file to upload and try again.");
return;
}
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
GC.Collect();
OleDbConnection objConn = null;
DataTable dt = null;
try
{
DataSet ds = new DataSet();
String connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 12.0;";
objConn = new OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dt == null)
{
return;
}
foreach (DataRow schemaRow in dt.Rows)
{
string sheet = schemaRow["TABLE_NAME"].ToString().Trim();
try
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "] ", objConn);
cmd.CommandType = CommandType.Text;
DataTable outputTable = new DataTable(sheet);
ds.Tables.Add(outputTable);
new OleDbDataAdapter(cmd).Fill(outputTable);
cmd.ExecuteNonQuery();
}