使用C＃实体FrameWork将Excel文件导入SQL [英] Importing Excel File To SQL With C# Entity FrameWork
本文介绍了使用C＃实体FrameWork将Excel文件导入SQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我做错了什么或者这是正常的吗？
这里是foreach循环从我的代码路径是路径到excel文件：
foreach（路径中的var项目）
{
Microsoft.Office.Interop.Excel.Application xlApp =新的Microsoft.Office。 Interop.Excel.Application（）;
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open（item.Value）;
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets [1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
try
{
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
string GenDateString = xlRange.Cells [3,6] .Value2.ToString（）;
string GenDate = GenDateString.Substring（0，10）;
string Client = xlRange.Cells [3，1] .Value2.ToString（）;
string User = xlRange.Cells [3，2] .Value2.ToString（）;
string error;
if（IsCorrectDate（item.Key，xlRange））
{
CultureInfo provider = CultureInfo.InvariantCulture;
Import import = new Import（）
{
Month = month，
year = year，
EndDate = item.Key [1]，
GenerateDate = DateTime.ParseExact（GenDate，dd.MM.yyyy，provider），
StartDate = item.Key [0]，
FileName = item.Value.ToString（）
};
if（db.Imports.Any（i => i.StartDate == import.StartDate || i.EndDate == import.EndDate））
{
try
{
progressbar.ReleaseBar（）;
}
catch（异常）
{
}
SAPbouiCOM.Framework.Application.SBO_Application.SetStatusBarMessage（ასეთიფაილიუკვემშპპპპპრრუიიი，SAPbouCOM.BoMessageTime.bmt_Short，真正）;
//清理
GC.Collect（）;
GC.WaitForPendingFinalizers（）;
//释放com对象的经验法则：
//从不使用两个点，所有COM对象必须被单独引用和释放
// ex：[somthing]。 [某事] [某事]是坏
//发布com对象以完全从后台运行excel进程
Marshal.ReleaseComObject（xlRange）;
Marshal.ReleaseComObject（xlWorksheet）;
//关闭并释放
xlWorkbook.Close（）;
Marshal.ReleaseComObject（xlWorkbook）;
//退出并释放
xlApp.Quit（）;
Marshal.ReleaseComObject（xlApp）;
return;
}
for（int i = 8; i <= rowCount; i ++）
{
Debug.WriteLine（i）;
var bpCode = xlRange.Cells [i，1] .Value2.ToString（）;
if（bpCode ==რპპპპისდდააი）
break;
i ++;
Mediator mediator = new Mediator（）
{
BPCode = bpCode，
Import = import，
};
for（int j = 0; j< clicks.Count（）; j ++）
{
progressbar.Up（）;
rowsc--;
var clickName = xlRange.Cells [i + j，1] .Value2.ToString（）;
统计SmeStatistic = new Statistic（）
{
ClickNameID = clicks.FirstOrDefault（c => c.Name == clickName）.ID，
ReqPerson = int.Parse xlRange.Cells [i + j，2] .Value2.ToString（）），
ReqBussiness = int.Parse（xlRange.Cells [i + j，3] .Value2.ToString（）），
ResPerson = int.Parse（xlRange.Cells [i + j，5] .Value2.ToString（）），
ResBussiness = int.Parse（xlRange.Cells [i + j，6] .Value2.ToString（） ），
Mediator = mediator，
};
// var xxx = clicks.FirstOrDefault（c => c.Name == clickName）.ID;
db.Statistics.Add（SmeStatistic）;
}
i + = clicks.Count（） - 1;
}
}
{
// int iReturnValue = SAPbouiCOM.Framework.Application.SBO_Application.MessageBox（item.Value +ფაილისთარიღაააააააააააიიი，，，，， 1，& Ok）;
try
{
progressbar.ReleaseBar（）;
}
catch（异常）
{
}
SAPbouiCOM.Framework.Application.SBO_Application.SetStatusBarMessage（ფაილისთარიღებიარილივარჩეულპეუოოო，SAPbouCOM.BoMessageTime。 bmt_Short，true）;
//清理
GC.Collect（）;
GC.WaitForPendingFinalizers（）;
//释放com对象的经验法则：
//从不使用两个点，所有COM对象必须被单独引用和释放
// ex：[somthing]。 [某事] [某事]是坏
//发布com对象以完全从后台运行excel进程
Marshal.ReleaseComObject（xlRange）;
Marshal.ReleaseComObject（xlWorksheet）;
//关闭并释放
xlWorkbook.Close（）;
Marshal.ReleaseComObject（xlWorkbook）;
//退出并释放
xlApp.Quit（）;
Marshal.ReleaseComObject（xlApp）;
return;
}
}
catch（Exception ex）
{
SAPbouiCOM.Framework.Application.SBO_Application.MessageBox（ex.Message）;
}
finally
{
//清理
GC.Collect（）;
GC.WaitForPendingFinalizers（）;
//释放com对象的经验法则：
//从不使用两个点，所有COM对象必须被单独引用和释放
// ex：[somthing]。 [某事] [某事]是坏
//发布com对象以完全从后台运行excel进程
Marshal.ReleaseComObject（xlRange）;
Marshal.ReleaseComObject（xlWorksheet）;
//关闭和释放//
//xlWorkbook.Close（）;
Marshal.ReleaseComObject（xlWorkbook）;
//退出并释放
//xlApp.Quit（）;
//Marshal.ReleaseComObject(xlApp）;
}
}
解决方案
每次循环都会创建一个新的Excel应用程序对象（xlApp） - 这将很慢。尝试在循环之前创建它，以便可以重用它。
可能还有其他一些事情可以加快，但我倾向于同意Panagiotis的评论 - 不要在这里使用Interop。我也去了这个兔子洞，它比使用JET OLEDB要复杂得多，而且比使用JET OLEDB要慢。
这是一个粗略的指导，你可以如何使用jet？只需填写一个数据表即可使用循环中的某个地方：
using System.Data.OleDb;
foreach（路径中的var项目）
{
string excelConnectionString =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ item +;扩展属性= \Excel 12.0; HDR = Yes; IMEX = 2\;
using（OleDbConnection excelConnection = new OleDbConnection（excelConnectionString））
{
excelConnection.Open（）;
DataTable dt = new DataTable（）; //excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables，new Object [] {null，null，null，TABLE}）;
string query = sheetname.Contains（）？ string.Format（select * from ['{0} $']，sheetname）：string.Format（Select * from [{0} $]，sheetname）;
using（OleDbDataAdapter dataAdapter = new OleDbDataAdapter（query，excelConnection））
{
dataAdapter.Fill（dt）;
}
//在这里执行数据
excelConnection.Close（）;
}
}
I need to import excel file with 3000 rows into MS SQL Server Database, i am using C# with entity framework, thing is operation takes too long about 15- 20 minutes. Am i doing something wrong or is this normal?
Here is foreach loop from my code where paths is path to excel file:
foreach (var item in paths)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(item.Value);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
try
{
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
string GenDateString = xlRange.Cells[3, 6].Value2.ToString();
string GenDate = GenDateString.Substring(0, 10);
string Client = xlRange.Cells[3, 1].Value2.ToString();
string User = xlRange.Cells[3, 2].Value2.ToString();
string error;
if (IsCorrectDate(item.Key, xlRange))
{
CultureInfo provider = CultureInfo.InvariantCulture;
Import import = new Import()
{
Month = month,
year = year,
EndDate = item.Key[1],
GenerateDate = DateTime.ParseExact(GenDate, "dd.MM.yyyy", provider),
StartDate = item.Key[0],
FileName = item.Value.ToString()
};
if (db.Imports.Any(i => i.StartDate == import.StartDate || i.EndDate == import.EndDate))
{
try
{
progressbar.ReleaseBar();
}
catch (Exception)
{
}
SAPbouiCOM.Framework.Application.SBO_Application.SetStatusBarMessage("ასეთი ფაილი უკვე შეიმპორტებულია", SAPbouiCOM.BoMessageTime.bmt_Short, true);
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
return;
}
for (int i = 8; i <= rowCount; i++)
{
Debug.WriteLine(i);
var bpCode = xlRange.Cells[i, 1].Value2.ToString();
if (bpCode == "რეპორტის დასასრული")
break;
i++;
Mediator mediator = new Mediator()
{
BPCode = bpCode,
Import = import,
};
for (int j = 0; j < clicks.Count(); j++)
{
progressbar.Up();
rowsc--;
var clickName = xlRange.Cells[i + j, 1].Value2.ToString();
Statistic SmeStatistic = new Statistic()
{
ClickNameID = clicks.FirstOrDefault(c => c.Name == clickName).ID,
ReqPerson = int.Parse(xlRange.Cells[i + j, 2].Value2.ToString()),
ReqBussiness = int.Parse(xlRange.Cells[i + j, 3].Value2.ToString()),
ResPerson = int.Parse(xlRange.Cells[i + j, 5].Value2.ToString()),
ResBussiness = int.Parse(xlRange.Cells[i + j, 6].Value2.ToString()),
Mediator = mediator,
};
//var xxx = clicks.FirstOrDefault(c => c.Name == clickName).ID;
db.Statistics.Add(SmeStatistic);
}
i += clicks.Count() - 1;
}
}
else
{
//int iReturnValue = SAPbouiCOM.Framework.Application.SBO_Application.MessageBox(item.Value + " ფაილის თარიღი არასწორია.", 1, "&Ok");
try
{
progressbar.ReleaseBar();
}
catch (Exception)
{
}
SAPbouiCOM.Framework.Application.SBO_Application.SetStatusBarMessage("ფაილის თარიღები არ ემთხვევა არჩეულ პერიოდს", SAPbouiCOM.BoMessageTime.bmt_Short, true);
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
return;
}
}
catch (Exception ex)
{
SAPbouiCOM.Framework.Application.SBO_Application.MessageBox(ex.Message);
}
finally
{
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release //
//xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
//xlApp.Quit();
//Marshal.ReleaseComObject(xlApp);
}
}
解决方案
You are creating a new Excel application object (xlApp) every time the loop circles - this will be slow. Try creating it before the loop so you can reuse it.
There may be other things you can do to speed it up, but I'm inclined to agree with Panagiotis' comment - don't use Interop here. I went down this rabbit hole too, and it is hugely more complicated and slower than using JET OLEDB.
Here is a rough guide to how you might use jet instead - I just fill a data table to use somewhere in the loop:
using System.Data.OleDb;
foreach (var item in paths)
{
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + item + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
{
excelConnection.Open();
DataTable dt = new DataTable(); //excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
string query = sheetname.Contains(" ") ? string.Format("Select * from ['{0}$']", sheetname) : string.Format("Select * from [{0}$]", sheetname);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection))
{
dataAdapter.Fill(dt);
}
//do something with the data here
excelConnection.Close();
}
}
这篇关于使用C＃实体FrameWork将Excel文件导入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
