Step1:Upload your spreadsheet in some temporary folder
private void btnUpload_Click(object sender, EventArgs e)
{
string sourceFile = fileDlg.FileName;
string[] fileName = sourceFile.Split('\\');
if (!Directory.Exists(TempDirectory))
{
Directory.CreateDirectory(TempDirectory);
}
string destinationFile = TempDirectory + fileName[fileName.Length - 1];
if (File.Exists(sourceFile) && !File.Exists(destinationFile))
{
File.Copy(sourceFile, destinationFile);
MessageBox.Show("File copied...", "Notification");
}
else if (!File.Exists(destinationFile))
{
MessageBox.Show("Select a file...", "Error");
}
else
{
MessageBox.Show("File already exist...", "Notification");
}
txtBoxFile.Text = string.Empty;
GetAvailableFiles();
}
private void GetAvailableFiles()
{
cbAllFiles.Items.Clear();
cbSheets.Items.Clear();
cbAllFiles.Text = "Select";
cbSheets.Text = "Select";
string[] allfiles = Directory.GetFiles(TempDirectory);
var fileList = (from f in allfiles select f.Split('\\')[f.Split('\\').Length - 1]).ToArray<string>();
var extList = new List<string>() { ".xls", ".xlsx" };
fileList = fileList.Where(f => extList.Contains(Path.GetExtension(f)) && !f.StartsWith("~")).ToArray<string>();
cbAllFiles.Items.AddRange(fileList);
}
N.B:
cbAllfiles is ID of a combobox
cbSheets is ID of another combobox which tracks the sheet name
For getting the sheet name from cbAllFileName selected index changed event:
private void cbAllFiles_SelectedIndexChanged(object sender, EventArgs e)
{
if (((ComboBox)sender).SelectedIndex != -1)
{
string DB_Path = string.Format("{0}{1}", TempDirectory, cbAllFiles.SelectedItem.ToString());
string fileExt = Path.GetExtension(DB_Path);
if (fileExt == ".xlsx")
ExcelConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DB_Path + ";Extended Properties=Excel 12.0;";
else
ExcelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DB_Path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
try
{
OleDbConnection connExcel = new OleDbConnection(ExcelConnection);
connExcel.Open();
DataTable schemaTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String sheetName;
List<string> lstsheetNames = new List<string>();
foreach (DataRow row in schemaTable.Rows)
{
sheetName = row.Field<string>("TABLE_NAME").Trim();
if (sheetName.Length > 1 && sheetName.EndsWith("$"))
{
lstsheetNames.Add(sheetName.Substring(0, sheetName.Length - 1));
}
cbSheets.Items.Clear();
cbSheets.Items.AddRange(lstsheetNames.ToArray());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + " StackTrace: " + ex.StackTrace, "Exception");
}
}
}
Step-2:
string excelName = Path.GetFileNameWithoutExtension(cbAllFiles.SelectedItem.ToString());
string sheetName = cbSheets.SelectedItem.ToString();
//This is where you can get all the rows details in a datatable
DataTable dt = DataManager.GetSheetData(sheetName, ExcelConnection);
public static DataTable GetSheetData(string sheetName, string excelConn)
{
DBAccess db = new DBAccess(excelConn);
string cmdText = "Select * from [" + sheetName + "$]";
db.CommandText = cmdText;
return db.ExecuteExcelDataSet();
}
If you feel this is a right solution then tick on green color button please.
Thanks,