string connectionString = "";
if (FileUpload1.HasFile)
{
string fileName = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
string fileExtension = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName);
string fileLocation = Server.MapPath("/Upload")+"sample" + fileExtension;
FileUpload1.SaveAs(fileLocation);
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
}
else if((!fileExtension.Contains(".xls")) ||(!fileExtension.Contains(".xlsx")))
{
lblStatus.Visible = true;
lblStatus.Text = "Uploaded filetype should be .xls or .xlsx";
lblStatus.ForeColor = System.Drawing.Color.Red;
return;
}
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
con.Close();
dtExcelRecords = dtExcelRecords.AsEnumerable().Where(row => !row.ItemArray.All(f => f is System.DBNull || String.IsNullOrEmpty(f.ToString()))).CopyToDataTable();
for (int col = dtExcelRecords.Columns.Count - 1; col >= 0; col--)
{
bool removeColumn = true;
foreach (DataRow row in dtExcelRecords.Rows)
{
if (!row.IsNull(col))
{
removeColumn = false;
break;
}
}
if (removeColumn)
dtExcelRecords.Columns.RemoveAt(col);
}