Visual Web part for read data from Excel Sheet and Insert into Data base

Step of reading data from excel sheet and insert into database.

Step 1: Open .Net and Select New Project=> and Select SharePoint 2010=>Visual Web Part.

Step 2: Open the .cs file and put this code.

This code is for generate Xml for insert in databse:-

private string GetXMLFromExcel(System.Web.HttpPostedFile file)
{
string savePath = string.Empty;
string xmlData = string.Empty;
string dirPath = string.Empty;
dirPath = Path.Combine(Request.PhysicalApplicationPath, "BAUBUG");
savePath = Path.Combine(dirPath, file.FileName);
if (!Directory.Exists(dirPath))
{
Directory.CreateDirectory(dirPath);
}

FUExcelSheet.PostedFile.SaveAs(savePath);
xmlData = ReadDataFromExcel(savePath, file.FileName);
System.IO.File.Delete(savePath);
return xmlData;
}

This code return xml String code of Excel:-

public string ReadDataFromExcel(string filePath, string fileName)
{
DataSet dataSet = null;
OleDbConnection oledbConn = null;
OleDbCommand command = null;
OleDbDataAdapter dataAdapter = null;
string strConnectionString = null;
try
{

strConnectionString = ExcelProvider(fileName).ToString() + "Data Source=" + filePath + ExtendedProperties(fileName);
oledbConn = new OleDbConnection(strConnectionString);
// Opens OleDb Connection
oledbConn.Open();
string excelSheetName = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)
.Rows[0]["TABLE_NAME"].ToString().Replace("'", "");
// Create OleDbCommand object and select data from worksheet Sheet1
command = new OleDbCommand("SELECT * FROM [" + excelSheetName + "]", oledbConn);
// Create new OleDbDataAdapter
dataAdapter = new OleDbDataAdapter(command);
// Create a DataSet which will hold the data extracted from the worksheet.
dataSet = new DataSet("Root");
// Fill the DataSet from the data extracted from the worksheet.
dataAdapter.Fill(dataSet, "Detail");
//foreach (DataRow dr in dataSet.Tables[0].Rows)
//{

// string path = dr[13].ToString();
// dataSet.Tables[0].AcceptChanges();
//}
}
catch (Exception)
{
// Throws the exception
throw;
}
finally
{
oledbConn.Close();
command.Dispose();
dataAdapter.Dispose();
}
//return the value
return dataSet.GetXml();
}
/// <summary>
/// Excel provider
/// </summary>
/// <param name="FileName"></param>
/// <returns></returns>
private string ExcelProvider(string FileName)
{
if (null != IsOpenXMLFormat(FileName))
{
if (Convert.ToBoolean(IsOpenXMLFormat(FileName)))
{
return "Provider=Microsoft.ACE.OLEDB.12.0;";
}
else
{
return "Provider=Microsoft.Jet.OLEDB.4.0;";
}
}
else
{
return null;
}
}
/// <summary>
///
/// </summary>
/// <param name="FileName">Check Excel file Type</param>
/// <returns></returns>
private bool? IsOpenXMLFormat(string FileName)
{
string[] splitByDots = FileName.Split(new char[1] { '.' });
//Excel 97-2003 file
if (splitByDots[splitByDots.Length - 1] == "xls")
return false;
//Excel 2007 file
if (splitByDots[splitByDots.Length - 1] == "xlsx")
return true;
//Not an Excel Sheet
return null;
}
private string ExtendedProperties(string FileName)
{
if (null != IsOpenXMLFormat(FileName))
{
if (Convert.ToBoolean(IsOpenXMLFormat(FileName)))
{
return ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
}
else
{
return ";Extended Properties=Excel 8.0;";
}
}
else
{
return null;
}
}
/// <summary>
/// Bind Bug Details Grid
/// </summary>
public void DsGetBugDetails()
{
try
{
gvBugDetails.DataSource = ExecuteDataSet("CusTableBAUIssue", "SpGetBugDetails");
gvBugDetails.DataBind();
}
catch (Exception Ex)
{
throw;
}
}
#region DataBaseOperation
/// <summary>
/// Open Sql conncetion
/// </summary>
private void OpenConnection()
{
try
{
connectionString = "Data Source=P2010;Initial Catalog=DataBaseName;integrated security=true;";
connection = new SqlConnection(connectionString);
if (connection.State != ConnectionState.Open) connection.Open();
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message + "OpenDBConnection");
}
}
/// <summary>
/// Close the connection
/// </summary>
public void ClosedbConnection()
{
try
{
connection.Dispose();
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message + " CloseDBConnection");
}
}
/// <summary>
/// Return DataSet
/// </summary>
/// <param name="tableName"></param>
/// <param name="procedureName"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string tableName, string procedureName)
{
DataSet dsReturn;
try
{
OpenConnection();
sqlComm = new SqlCommand();
sqlComm.Parameters.Clear();
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.CommandText = procedureName;
dsReturn = new DataSet();
sqlComm.Connection = connection;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlComm);
sqlDataAdapter.Fill(dsReturn, tableName);
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
finally
{
ClosedbConnection();
}
return dsReturn;
}
#endregion