hello friends i want to give user facility of uploading excel sheet in both .xls and .xlsx format .On Local ,My code is working fine for .xlsx file , but giving error for .xls file .
But on server it is giving error for both formats.
This is my code
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
public void ShowStatus()
{
DsOrders = ObjStatus.ShowOrderStatus();
if (DsOrders.Tables.Count > 0)
{
if (DsOrders.Tables[0].Rows.Count > 0)
{
Grdorders.DataSource = DsOrders;
Grdorders.DataBind();
}
else
{
lblstatus.Text = "There is not record ";
}
}
}
public void saveCustomers(string DirPath, string filename, string FileExtnsn)
{
string sSQLTable = "PaymentReport";
string sExcelFileName = filename;
string sWorkbook = "[Sheet1$]";
string extnsn = FileExtnsn;
string sExcelConnectionString;
if (extnsn == ".xlsx")
{
sExcelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DirPath + sExcelFileName +
";Extended Properties='Excel 12.0 Xml;HDR=YES'";
}
else
{
sExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DirPath + sExcelFileName +
";Extended Properties='Excel 8.0;HDR=YES;'";
}
OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
OleDbCommand OleDbCmd = new OleDbCommand(("Select [Way Bill No],[Stage Description],[Reasion Description],[Remarks],[Updated Location] from " + sWorkbook), OleDbConn);
OleDbConn.Open();
bool customerExists = false;
string notAddedIds = " Following User's were not added as the User ID's already exist.<br /> Please try adding these users with different id's. <br />";
int rcode = 0;
string stremp = "";
OleDbDataReader dr = OleDbCmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
ObjStatus.intwaybillno = getVal(Convert.ToInt32(dr["Way Bill No"]));
ObjStatus.strStageDescription = getVal2((dr["Stage Description"]).ToString());
ObjStatus.strReasionDescription = getVal3((dr["Reasion Description"]).ToString());
ObjStatus.strremarks = getVal4((dr["Remarks"]).ToString());
ObjStatus.strUpdatedLocation = getVal5((dr["Updated Location"]).ToString());
rcode = ObjStatus.Insertexceldataintoorderstatus();
if (rcode == 1)
{
lblmsg.Text = "Status has been added successfully";
}
else
{
}
}
dr.Close();
OleDbConn.Close();
}
}
public int getVal(int myVal)
{
if (myVal == 0)
{
return (0);
}
else
{
return myVal;
}
}
public string getVal2(string myVal)
{
if (myVal == System.DBNull.Value.ToString())
{
return ("");
}
else
{
myVal = myVal.Trim();
return (myVal);
}
}
public string getVal3(string myVal)
{
if (myVal == System.DBNull.Value.ToString())
{
return ("");
}
else
{
myVal = myVal.Trim();
return (myVal);
}
}
public string getVal4(string myVal)
{
if (myVal == System.DBNull.Value.ToString())
{
return ("");
}
else
{
myVal = myVal.Trim();
return (myVal);
}
}
public string getVal5(string myVal)
{
if (myVal == System.DBNull.Value.ToString())
{
return ("");
}
else
{
myVal = myVal.Trim();
return (myVal);
}
}
protected void BtnSbmit_Click(object sender, EventArgs e)
{
try
{
string DirPath = Server.MapPath("ExcelSheet\\");
string filepath = null;
string filename = null;
string Fextnsn = null;
if (UploadExcel.PostedFile.ContentLength != 0)
{
if (!Directory.Exists(DirPath))
{
Directory.CreateDirectory(DirPath);
}
filepath = UploadExcel.PostedFile.FileName;
FileInfo fInfo = new FileInfo(filepath);
filename = fInfo.Name;
Fextnsn = Path.GetExtension(UploadExcel.FileName).ToLower();
UploadExcel.PostedFile.SaveAs(DirPath + filename);
try
{
saveCustomers(DirPath, filename, Fextnsn);
lblmsg.Text = "Status has been added successfully ";
}
catch (Exception ex)
{
}
if (File.Exists(DirPath + filename))
{
File.Delete(DirPath + filename);
}
}
else
{
}
ShowStatus();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
It is showing error :-The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine with .xlsx file.
Error
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine with .xlsx file.
please help me ,thanks in advance friends.