Import Data in Excel to Database

Sample code to Import Data in Excel to Database


Create an Excel app object and open the uploaded file:

Microsoft.Office.Interop.Excel.Application xlsApp = new ApplicationClass();

Workbook wb = xlsApp.Workbooks.Open(_filePath,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing);

wb.Unprotect(TemplatePassword);
Save the upload file as a temporary file. Then close the uploaded file and open the temporary file as follows:

string tempFileName = _filePath.ToLower().Replace(".xls","_Temp.xls");
wb.Unprotect(TemplatePassword);
wb.SaveCopyAs(tempFileName);
Verify the data in the template again in the web application:
Collapse
ReadDataSource((Worksheet)wb.Worksheets[DataSourceSheet],
out branchID, out planDate, out startDate, out endDate,
out iChecked, out templatetype);

if(!(iChecked == 1))
{
returnVal = "Please verify the data before upload to the server!";
throw new Exception(returnVal);
}
if(templatetype.ToUpper() != _templatetype.ToUpper())
{
returnVal = "The version is not corrected,
please verify the document and uploaded again";
throw new Exception(returnVal);
}
……
private void ReadDataSource(Worksheet ws, out string branchID,
out string planDate, out DateTime startDate,out DateTime endDate,
out int iChecked, out string templatetype)
{
string check = ws.get_Range("A1", System.Type.Missing).Text.ToString();
branchID = ws.get_Range("A2", System.Type.Missing).Text.ToString();
string sDate = ws.get_Range("A4", System.Type.Missing).Text.ToString();
string eDate = ws.get_Range("A5", System.Type.Missing).Text.ToString();
planDate = ws.get_Range("A6", System.Type.Missing).Text.ToString();
templatetype = ws.get_Range("A7", System.Type.Missing).Text.ToString();
startDate = DateTime.Parse(sDate);
endDate = DateTime.Parse(eDate);
try
{
iChecked = Convert.ToInt16(check);
}
catch
{
iChecked = 0;
}
}


Comments

Author: Viji RAJKUMAR25 Nov 2009 Member Level: Gold   Points : 1

Deepika,

Where do you import the data into database?

In the ReadSource Function, you are reading the cell data into different variables..

After the validation where do you import the excel data into database.

Which database you have used?

Author: Dhirendra Kumar Shrivastava10 Mar 2011 Member Level: Bronze   Points : 1

I tried to understand the code. I want help in understanding the concept.So can you help me creating and developing an Interface which will handle a excell worksheet in a Grid of form control in C#.
Reply me at sienotech@gmail.com
thanks
Dhirendra Shrivastava



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: