I am building an MVC app. I created a feature to upload and save an excel file, but I can't figure out the best way to save the data to my sql server database. My research has shown 4 or 5 different technologies, and I can't decide on the best one for
my app.
I am only accepting .xlsx files. The excel file can have many sheets, but in every file one of the sheets will be named something common, so I will need to open the file and search the sheet names for my sheet. The sheet will have headers, and they should
be uniform but I cant guarantee it, so I need to be able to search the headers on the found sheet. I then need to iterate through and save each row to the database. Oh, and during the iteration process I need to compare the Itemid column on the excel sheet
to the itemid column in the database so no duplicates get saved.
I have looked at NPOI, OpenXML & Interop. I have found various code snippets for the various libraries, but I can't find a full tutorial.
As for this issue, when you import data from excel to SQL server, at first, you need to create data table according to the excel sheets. Here are some articles about import
data from Excel file to Database table in MVC. Please refer to them.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
185 Points
228 Posts
How to save excel data to sql server
Jan 22, 2015 01:18 PM|teahou|LINK
I am building an MVC app. I created a feature to upload and save an excel file, but I can't figure out the best way to save the data to my sql server database. My research has shown 4 or 5 different technologies, and I can't decide on the best one for my app.
I am only accepting .xlsx files. The excel file can have many sheets, but in every file one of the sheets will be named something common, so I will need to open the file and search the sheet names for my sheet. The sheet will have headers, and they should be uniform but I cant guarantee it, so I need to be able to search the headers on the found sheet. I then need to iterate through and save each row to the database. Oh, and during the iteration process I need to compare the Itemid column on the excel sheet to the itemid column in the database so no duplicates get saved.
I have looked at NPOI, OpenXML & Interop. I have found various code snippets for the various libraries, but I can't find a full tutorial.
interop mvc excel NPOI
All-Star
45469 Points
7008 Posts
Microsoft
Re: How to save excel data to sql server
Jan 22, 2015 10:11 PM|Zhi Lv - MSFT|LINK
Hi teahou,
As for this issue, when you import data from excel to SQL server, at first, you need to create data table according to the excel sheets. Here are some articles about import data from Excel file to Database table in MVC. Please refer to them.
http://www.codeproject.com/Tips/752981/Import-Data-from-Excel-File-to-Database-Table-in-A
http://www.code-sample.com/2014/07/aspnet-c-mvc-4-import-data-from-excel.html
http://forums.asp.net/t/1693674.aspx?asp+net+mvc+c+best+way+to+import+from+excel+to+sql+server+
Best Regards,
Dillion
interop mvc excel NPOI
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.