Hi,
hear is c# code it's office 2007(dll Version 11).
use this code and let me know.
public DataTable ExcelToDataSet()
{
string Path = Server.MapPath(("~//UploadLead//") + FileUpload1.FileName);
// Initialize the Excel Application class
Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
// Create the workbook object by opening the excel file.
Microsoft.Office.Interop.Excel.Workbook workBook =
app.Workbooks.Open(Path, 0, true, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false,
0, true, 1, 0);
// Get the active worksheet using sheet name or active sheet
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int index = 1;
object rowIndex = 1;
object colIndex1 = 1;
object colIndex2 = 2;
object row = 1;
int z = 1;
#region "DataTable"
ArrayList arr = new ArrayList();
arr.Add("Business_Name");
arr.Add("Business_Owner");
arr.Add("Business_Owner_First_Name");
arr.Add("Business_Owner_Last_Name");
arr.Add("Job_Title");
arr.Add("Daytime_Phone");
arr.Add("Fax_Number");
arr.Add("Email");
arr.Add("Email_Backup_1");
arr.Add("Email_Backup_2");
arr.Add("Business_Address");
arr.Add("City");
arr.Add("State_Territory");
arr.Add("County");
arr.Add("Zip");
arr.Add("Website");
arr.Add("NAICS");
arr.Add("FSC");
arr.Add("PSC");
arr.Add("Year_Established");
arr.Add("Purchase_Card");
arr.Add("Veteran_Owned_Small_Business");
arr.Add("Service-Disabled_Veteran_Owned_Small_Business");
arr.Add("Registered_for_Government_Business");
arr.Add("Registered_for_Non_Government_Business");
arr.Add("Number_of_Employees");
arr.Add("Number_of_Operating_Locations");
arr.Add("Capabilities_Keywords");
arr.Add("Capabilities_Narrative");
DataTable dt = new DataTable();
dt.Columns.Add("Business_Name");
dt.Columns.Add("Business_Owner");
dt.Columns.Add("Business_Owner_First_Name");
dt.Columns.Add("Business_Owner_Last_Name");
dt.Columns.Add("Job_Title");
dt.Columns.Add("Daytime_Phone");
dt.Columns.Add("Fax_Number");
dt.Columns.Add("Email");
dt.Columns.Add("Email_Backup_1");
dt.Columns.Add("Email_Backup_2");
dt.Columns.Add("Business_Address");
dt.Columns.Add("City");
dt.Columns.Add("State_Territory");
dt.Columns.Add("County");
dt.Columns.Add("Zip");
dt.Columns.Add("Website");
dt.Columns.Add("NAICS");
dt.Columns.Add("FSC");
dt.Columns.Add("PSC");
dt.Columns.Add("Year_Established");
dt.Columns.Add("Purchase_Card");
dt.Columns.Add("Veteran_Owned_Small_Business");
dt.Columns.Add("Service-Disabled_Veteran_Owned_Small_Business");
dt.Columns.Add("Registered_for_Government_Business");
dt.Columns.Add("Registered_for_Non_Government_Business");
dt.Columns.Add("Number_of_Employees");
dt.Columns.Add("Number_of_Operating_Locations");
dt.Columns.Add("Capabilities_Keywords");
dt.Columns.Add("Capabilities_Narrative");
#endregion
try
{
int usdRows = ((Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange).Rows.Count;
int usdcolumns = ((Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange).Columns.Count;
//while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2 != null)
for (int ctr = 0; ctr < usdRows; ctr++)
{
int col = 1;
z = z + 1;
DataRow dr = dt.NewRow();
for (int p = 0; p < usdcolumns; p++)
{
try
{
string s = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[row, col]).Value2.ToString();
for (int i = 0; i < arr.Count; i++)
{
string a = arr[i].ToString();
if (a == s)
{
try
{
string Name = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[z, col]).Value2.ToString();
#region "dr"
if (s == "Business_Name")
{
dr["Business_Name"] = Name.ToString();
}
else if (s == "Business_Owner")
{
dr["Business_Owner"] = Name.ToString();
}
else if (s == "Business_Owner_First_Name")
{
dr["Business_Owner_First_Name"] = Name.ToString();
}
else if (s == "Business_Owner_Last_Name")
{
dr["Business_Owner_Last_Name"] = Name.ToString();
}
else if (s == "Job_Title")
{
dr["Job_Title"] = Name.ToString();
}
else if (s == "Daytime_Phone")
{
dr["Daytime_Phone"] = Name.ToString();
}
else if (s == "Email")
{
dr["Email"] = Name.ToString();
}
else if (s == "Email_Backup_1")
{
dr["Email_Backup_1"] = Name.ToString();
}
else if (s == "Email_Backup_2")
{
dr["Email_Backup_2"] = Name.ToString();
}
else if (s == "Business_Address")
{
dr["Business_Address"] = Name.ToString();
}
else if (s == "City")
{
dr["City"] = Name.ToString();
}
else if (s == "State_Territory")
{
dr["State_Territory"] = Name.ToString();
}
else if (s == "County")
{
dr["County"] = Name.ToString();
}
else if (s == "Zip")
{
dr["Zip"] = Name.ToString();
}
else if (s == "Website")
{
dr["Website"] = Name.ToString();
}
else if (s == "NAICS")
{
dr["NAICS"] = Name.ToString();
}
else if (s == "FSC")
{
dr["FSC"] = Name.ToString();
}
else if (s == "PSC")
{
dr["PSC"] = Name.ToString();
}
else if (s == "Year_Established")
{
dr["Year_Established"] = Name.ToString();
}
else if (s == "Purchase_Card")
{
dr["Purchase_Card"] = Name.ToString();
}
else if (s == "Veteran_Owned_Small_Business")
{
dr["Veteran_Owned_Small_Business"] = Name.ToString();
}
else if (s == "Service-Disabled_Veteran_Owned_Small_Business")
{
dr["Service-Disabled_Veteran_Owned_Small_Business"] = Name.ToString();
}
else if (s == "Registered_for_Government_Business")
{
dr["Registered_for_Government_Business"] = Name.ToString();
}
else if (s == "Registered_for_Non_Government_Business")
{
dr["Registered_for_Non_Government_Business"] = Name.ToString();
}
else if (s == "Number_of_Employees")
{
dr["Number_of_Employees"] = Name.ToString();
}
else if (s == "Number_of_Operating_Locations")
{
dr["Number_of_Operating_Locations"] = Name.ToString();
}
else if (s == "Capabilities_Keywords")
{
dr["Capabilities_Keywords"] = Name.ToString();
}
else if (s == "Capabilities_Narrative")
{
dr["Capabilities_Narrative"] = Name.ToString();
}
#endregion
}
catch { }
break;
}
}
col = col + 1;
}
catch { }
}
index++;
rowIndex = index;
//row = index;
dt.Rows.Add(dr);
}
foreach (DataRow dr in dt.Select())
{
if (dr["Business_Name"].ToString() == "" &&
dr["Daytime_Phone"].ToString() == "" &&
dr["Business_Owner"].ToString() == "" &&
dr["Business_Owner_First_Name"].ToString() == "" &&
dr["Business_Owner_Last_Name"].ToString() == "" &&
dr["Job_Title"].ToString() == "" && dr["Fax_Number"].ToString()
== "" && dr["Email"].ToString() == "")
{
dr.Delete();
dt.AcceptChanges();
}
}
app.Quit();
}
catch (Exception ex)
{
string s = ex.ToString();
// Log the exception and quit...
app.Quit();
//Console.WriteLine(ex.Message);
}
return dt;
}