Browse excel file ---> Read excel file ---> Save data into database.

[VA_er]

Although there is tons of information out there, but I prefer to know how to write code for my specific case.

Thanks.

FileDialog.RestoreDirectory Property (System.Windows.Forms)

private void uploadExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
//Get file path

var filePath = string.Empty;
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel Files|*.xl*"; //Filter for excel file
openFile.FilterIndex = 2; //Don't know what it mean
openFile.RestoreDirectory = true;

if (openFile.ShowDialog() == DialogResult.OK)
{
//Get the path of specified file
filePath = openFile.FileName;
}


//Read excel data into DataTable
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
//filePath should already be open in the above, correct? It seems Open is not correct here?
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
//The excel file has only one sheet, with header, want to import Column A-G into database
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;

//Find last row of column A, which is also last row of data to be imported. Don't look beyong column G, since user may put comments there.

//Read data into DataTable

//Save data into database table XYZ (Insert if new record; Update if record already exists)


}

Continue reading...