Click here to Skip to main content
15,910,277 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have wrote the code to import the excel data into databse how to check the column names also.
if same columns then insert should be happend not matching means we need to insert a column thru .net and then insert into database should done.
Posted
Updated 26-Jul-13 21:43pm
v2

By Using ExtremeML dll we can import data from excel sheet to database..
here i am giving code in mvc4 with entity frame work and database is my sql..

using ExtremeML.Packaging;

[HttpPost]
public ActionResult Upload()
{
HttpPostedFileBase flExcell = Request.Files["flExcell"];
if (flExcell.ContentLength != 0)
{
string Extenstion = Path.GetExtension(flExcell.FileName);
string ExcelName = MethodFactory.GenerateUniqueFileName("Excel") + Extenstion.ToString();
string FilePath = "~/UploadExcel/" + ExcelName;
flExcell.SaveAs(Server.MapPath(FilePath));


using (var package = SpreadsheetDocumentWrapper.Open(@FilePhysicalAddress + ExcelName))
{
var part = package.WorkbookPart.WorksheetParts["Sheet1"];
fpc_topic Topic = new fpc_topic();
fpc_question TQuestion = new fpc_question();
fpc_answer TAnswer = new fpc_answer();

foreach (var row in part.Worksheet.SheetData.Rows)
{
if (row.RowIndex > 1 && row.RowIndex < part.Worksheet.SheetData.Rows.Count())
{

string Module = row.GetCellValue<string>(0);
string TopicContent = row.GetCellValue<string>(1);
string TGroup = row.GetCellValue<string>(2);
string TGroupComplexity = row.GetCellValue<string>(3);
string TopicInstructions = row.GetCellValue<string>(4);
string QGroupID = row.GetCellValue<string>(5);
string PatternID = row.GetCellValue<string>(6);
string QGInstructions = row.GetCellValue<string>(7);
string Question = row.GetCellValue<string>(8);
string Options = row.GetCellValue<string>(9);
string CorrectAnswer = row.GetCellValue<string>(10);

fpeoplecareerEntities Entities1 = new fpeoplecareerEntities();
Topic.Complexity = Convert.ToInt32(TGroupComplexity);
Topic.Content = TopicContent;
Topic.CreatedBy = SessionManagement.AdminID;
Topic.CreatedOn = DateTime.Now;
Topic.Instructions = TopicInstructions;
Topic.Group = Convert.ToInt32(TGroup);
Topic.ModuleID = Convert.ToInt64(Module);
var InsertTopic = Entities1.fpc_topic.Add(Topic);
Entities1.SaveChanges();

fpeoplecareerEntities Entities2 = new fpeoplecareerEntities();
TQuestion.CreatedBy = SessionManagement.AdminID;
TQuestion.CreatedOn = DateTime.Now;
TQuestion.GroupID = Convert.ToInt32(QGroupID);
TQuestion.Instructions = QGInstructions;
TQuestion.PatternID = Convert.ToInt64(PatternID);
TQuestion.Question = Question;
TQuestion.TopicID = InsertTopic.ID;
var InsertQuestion = Entities2.fpc_question.Add(TQuestion);
Entities2.SaveChanges();

fpeoplecareerEntities Entities3 = new fpeoplecareerEntities();
TAnswer.Answer = Options + "$#$" + CorrectAnswer;
TAnswer.QuestionID = InsertQuestion.ID;
Entities3.fpc_answer.Add(TAnswer);
Entities3.SaveChanges();


}
}

}
MethodFactory.ClearFolder(@FilePhysicalAddress);
Message = "Successfully uploaded";
}
else
{
MethodFactory.ClearFolder(@FilePhysicalAddress);
Message = "Please Upload Excelsheet";
}
return RedirectToAction("UploadExcel");
}




}
 
Share this answer
 
v2
you can visit on following link for more solution..........



https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server---10-steps-to-follow/[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900