在C＃中读取Excel数据的最快方法 [英] Fastest way to read excel data in C#
本文介绍了在C＃中读取Excel数据的最快方法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的Excel工作表中有4列和3000行。我需要读取Row1col1，Row1Col2，Row1Col3和Row1Col4等数据。从Row1Col1读取名称后，我需要从oracle表中获取其id。类似地，然后读取Row1Col2并从oracle表中获取其Id，依此类推，直到Row1Col4。在获得第一行的所有列的所有Id之后，我需要检查oracle表中是否存在该匹配。如果该匹配存在，则保留其他插入该记录。为此，目前我正在使用Microsoft.Office.Interop.Excel逐行读取Excel数据。目前大约需要20分钟才能读取3508条过长的记录。我想知道这是否是最好的方法，或者是否有更好/更有效的方式（或者更智能的方式 - 也许是Linq /本地.Net提供商）来代替使用？
任何帮助我会很感激。
我尝试过：
[ HttpPost]
public ActionResult ReadExcel（HttpPostedFileBase excelFile）
{
TempData [ SuccessMsg] = ;
int count = 0 ;
int rowCount = 0 ;
bool writesFilename = false ;
if （excelFile == null || excelFile.ContentLength == 0 ）
{
TempData [ ErrorMsg] = 请选择文件;
return 查看（ UploadData）;
}
其他
{
如果（excelFile.FileName.EndsWith（ xls）|| excelFile.FileName.EndsWith（< span class =code-string> xlsx））
{
string fileName = excelFile.FileName;
string extension = Path.GetExtension（Request.Files [ excelFile]文件名）。
string path = Server.MapPath（ 〜/ Content / + excelFile.FileName）;
if （System.IO.File.Exists（path））
{
System.IO.File.Delete（path） ;
}
excelFile.SaveAs（path）;
var desktopFolder = Environment.GetFolderPath（Environment.SpecialFolder.DesktopDirectory）;
var fullFileName = Path.Combine（desktopFolder，fileName + ） > TXT;
if （System.IO.File.Exists（fullFileName））
{
System.IO.File.Delete（fullFileName） ;
}
MyExcel.Application xlApp;
MyExcel.Workbook xlWorkBook;
MyExcel.Worksheet xlWorkSheet;
MyExcel.Range范围;
string ExcelAgencyName，ExcelAgencyGroupName，ExcelSource，ExcelFunctionalArea;
decimal AgencyKey;
int rCnt = 0 ;
int cCnt = 0 ;
xlApp = new MyExcel.Application（）;
xlWorkBook = xlApp.Workbooks.Open（路径， 0 ， true ， 5 ， ， ， true ，Microsoft.Office.Interop.Excel.XlPlatform。 xlWindows， \t， false ， false ， 0 ， true ， 1 ， 0 ）;
xlWorkSheet =（MyExcel.Worksheet）xlWorkBook.Worksheets.get_Item（ 1 ）;
range = xlWorkSheet.UsedRange;
rowCount = range.Rows.Count - 1 ;
// Parallel.ForEach（xlWorkSheet.Rows.Cast< MyExcel.Range>（） ，currentRow =>
// {
for （rCnt = 2 ; rCnt < = range.Rows.Count; rCnt ++）
{
for （cCnt = 1 ; cCnt < = range.Columns.Count; cCnt + = 5 ）
{
decimal dbAgencyGroupKey = 0 ;
decimal dbSourceKey;
decimal dbFunctionalKey;
BarcDataContext bc = new BarcDataContext（）;
ExcelAgencyName =（range.Cells [rCnt，cCnt] as MyExcel.Range）.Text.ToString（）;
ExcelAgencyGroupName =（range.Cells [rCnt，cCnt + 1 ] as MyExcel.Range） .Text.ToString（）;
ExcelSource =（range.Cells [rCnt，cCnt + 2 ] as MyExcel.Range） .Text.ToString（）;
ExcelFunctionalArea =（range.Cells [rCnt，cCnt + 3 ] as MyExcel.Range） .Text.ToString（）;
dbSourceKey = bc.REF_SRC_SUB_AREA.Where（m = > m.SRC_SUB_AREA == ExcelSource.Trim（））。FirstOrDefault（）。 SRC_SUB_KEY;
dbFunctionalKey = bc.REF_SRC_FUNC_AREA.Where（m = > m.SRC_FUNC_AREA == ExcelFunctionalArea.Trim（））。FirstOrDefault（）。SRC_FUNC_KEY;
DIM_AGENCY objAgencyKey = bc.DIM_AGENCY.Where（m = > m.AGENCY_NAME.ToUpper（）== ExcelAgencyName。 ToUpper（）&& m.SRC_SUB_KEY == dbSourceKey）.FirstOrDefault（）;
if （objAgencyKey！= null && dbFunctionalKey！= < span class =code-digit> 0 ）
{
AgencyKey = objAgencyKey.AGENCY_KEY;
}
else
{
StreamWriter sw = null ;
sw = new StreamWriter（fullFileName， true ）;
sw.WriteLine（ExcelAgencyName）;
sw.Close（）;
writesFilename = true ;
break ;
}
DIM_AGENCY_GROUP objAgencyGroup = bc.DIM_AGENCY_GROUP.Where（m = > m.AGENCY_GROUP_NAME.ToUpper（）== ExcelAgencyGroupName .ToUpper（）&& m.SRC_FUNC_KEY == dbFunctionalKey）.FirstOrDefault（）;
if （objAgencyGroup！= null ）
{
dbAgencyGroupKey = objAgencyGroup.AGENCY_GROUP_KEY;
}
if （dbAgencyGroupKey == 0 ）
{
decimal agencyGrpKey = AgencyGroupRepository.InsertAgencyGroup（ExcelAgencyGroupName，dbFunctionalKey）;
if （agencyGrpKey！= 0 ）
{
XREF_AGENCY_TAGGING objXrefTagging = bc.XREF_AGENCY_TAGGING.Where（m = > m.AGENCY_KEY == AgencyKey&& m.SRC_FUNC_KEY == dbFunctionalKey）.FirstOrDefault（）;
if （objXrefTagging！= null ）
{
if （objXrefTagging.AGENCY_GROUP_KEY == agencyGrpKey）
{
}
else
{
decimal agencyTaggingSrNo = AgencyGroupRepository.UpdateTaggingAgencyAgencyGroup（AgencyKey，agencyGrpKey，dbFunctionalKey）;
count = count + 1 ;
}
}
else
{
decimal agencyTaggingSrNo = AgencyGroupRepository.TagAgencyAgencyGroup（AgencyKey，agencyGrpKey，dbFunctionalKey）;
count = count + 1 ;
}
}
}
其他
{
XREF_AGENCY_TAGGING objXrefTagging = bc.XREF_AGENCY_TAGGING.Where（m = > m.AGENCY_KEY == AgencyKey&& m.SRC_FUNC_KEY == dbFunctionalKey）.FirstOrDefault（）;
if （objXrefTagging！= null ）
{
if （objXrefTagging.AGENCY_GROUP_KEY == dbAgencyGroupKey）
{
}
else
{
decimal agencyTaggingSrNo = AgencyGroupRepository.UpdateTaggingAgencyAgencyGroup（AgencyKey，dbAgencyGroupKey，dbFunctionalKey）;
count = count + 1 ;
}
}
else
{
decimal agencyTaggingSrNo = AgencyGroupRepository.TagAgencyAgencyGroup（AgencyKey，dbAgencyGroupKey，dbFunctionalKey）;
count = count + 1 ;
}
}
}
}
// }）;
}
else
{
TempData [ ErrorMsg] = 文件类型无效。仅上传xlx或xlsx文件类型。;
return 查看（ UploadData）;
}
}
int totalCount = rowCount - count;
if （writesFilename == true && count > 0 ）
{
TempData [ SuccessMsg] = Out of of + + rowCount + + ， + + totalCount + + 在数据库中找不到代理商名称。请查看保存在桌面上的文本文件以获取代理商名称。;
}
else if （count == 0 && writesFilename == true ）
{
TempData [ ErrorMsg] = 数据库中找不到任何代理商名称。请查看保存在桌面上的文本文件以获取代理商名称。;
}
else if （count > 0 && writesFilename == false ）
{
TempData [ ErrorMsg] = 超出 + + rowCount + + ， + + totalCount + + 代理组密钥已更新。;
}
return 查看（ UploadData）;
}
解决方案
阅读excel数据
string con =
@ Provider = Microsoft.Jet.OLEDB.4.0;数据源= D：\ temp \ test.xls; +
@ 扩展属性='Excel 8.0; HDR =是;';
使用（OleDbConnection connection = new OleDbConnection（con））
{
connection.Open（）;
OleDbCommand command = new OleDbCommand（ select *来自[Sheet1
，连接）;
使用（OleDbDataReader dr = command.ExecuteReader（））
{
while （dr.Read（））
{
var row1Col0 = dr [ 0 < /跨度>];
Console.WriteLine（row1Col0）;
}
}
}
来源
.net - 如何使用c＃从excel文件中读取数据 - DeveloperQ - 开发者问题网 - 海量问题解决方案 [ ^ ]
这不仅仅是读取Excel文件，还可以访问数据库并执行操作。
首先要做的是运行一个分析器来查看程序的每个部分需要多长时间。占用时间最多的部分可能就是你可以获得最大收益的部分。
除以2分钟只占10％的部分所需的时间只能节省5％。 />
节省2/3的75％的部分可以节省50％的费用。
你需要知道哪个操作需要时间嘲笑你的代码。
分析（计算机编程） - 维基百科，免费的百科全书 [ ^ ]
i have 4 columns and 3000 rows in my excel sheet. I need to read data like Row1col1 , Row1Col2 , Row1Col3 and Row1Col4. After reading the name from Row1Col1 , i need to fetch its id from oracle table. Similarly then read Row1Col2 and fetch its Id from oracle table and so on till Row1Col4. After getting all the Id's of all the columns of the first row , i need to check in one of the oracle table for that match exists or not. If that match exists then leave it else insert that record. For doing so, Currently i am using Microsoft.Office.Interop.Excel to read excel data row by row. Currently it is taking around 20 minutes to read 3508 records that is too long. I am wondering if this is the best way to do it or if there are better / more efficent ways (or just more intelligent ways - Maybe Linq / native .Net providers) to use instead?
Any help would be veru appreciated.
What I have tried:
[HttpPost]
public ActionResult ReadExcel(HttpPostedFileBase excelFile)
{
TempData["SuccessMsg"] = "";
int count = 0;
int rowCount = 0;
bool writesFilename = false;
if (excelFile == null || excelFile.ContentLength == 0)
{
TempData["ErrorMsg"] = "Please select File";
return View("UploadData");
}
else
{
if (excelFile.FileName.EndsWith("xls") || excelFile.FileName.EndsWith("xlsx"))
{
string fileName = excelFile.FileName;
string extension = Path.GetExtension(Request.Files["excelFile"].FileName);
string path = Server.MapPath("~/Content/" + excelFile.FileName);
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
excelFile.SaveAs(path);
var desktopFolder = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
var fullFileName = Path.Combine(desktopFolder, fileName + ".txt");
if (System.IO.File.Exists(fullFileName))
{
System.IO.File.Delete(fullFileName);
}
MyExcel.Application xlApp;
MyExcel.Workbook xlWorkBook;
MyExcel.Worksheet xlWorkSheet;
MyExcel.Range range;
string ExcelAgencyName, ExcelAgencyGroupName, ExcelSource, ExcelFunctionalArea;
decimal AgencyKey;
int rCnt = 0;
int cCnt = 0;
xlApp = new MyExcel.Application();
xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (MyExcel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
rowCount = range.Rows.Count - 1;
//Parallel.ForEach(xlWorkSheet.Rows.Cast<MyExcel.Range>(), currentRow =>
//{
for (rCnt = 2; rCnt <= range.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt += 5)
{
decimal dbAgencyGroupKey = 0;
decimal dbSourceKey;
decimal dbFunctionalKey;
BarcDataContext bc = new BarcDataContext();
ExcelAgencyName = (range.Cells[rCnt, cCnt] as MyExcel.Range).Text.ToString();
ExcelAgencyGroupName = (range.Cells[rCnt, cCnt + 1] as MyExcel.Range).Text.ToString();
ExcelSource = (range.Cells[rCnt, cCnt + 2] as MyExcel.Range).Text.ToString();
ExcelFunctionalArea = (range.Cells[rCnt, cCnt + 3] as MyExcel.Range).Text.ToString();
dbSourceKey = bc.REF_SRC_SUB_AREA.Where(m => m.SRC_SUB_AREA == ExcelSource.Trim()).FirstOrDefault().SRC_SUB_KEY;
dbFunctionalKey = bc.REF_SRC_FUNC_AREA.Where(m => m.SRC_FUNC_AREA == ExcelFunctionalArea.Trim()).FirstOrDefault().SRC_FUNC_KEY;
DIM_AGENCY objAgencyKey = bc.DIM_AGENCY.Where(m => m.AGENCY_NAME.ToUpper() == ExcelAgencyName.ToUpper() && m.SRC_SUB_KEY == dbSourceKey).FirstOrDefault();
if (objAgencyKey != null && dbFunctionalKey != 0)
{
AgencyKey = objAgencyKey.AGENCY_KEY;
}
else
{
StreamWriter sw = null;
sw = new StreamWriter(fullFileName, true);
sw.WriteLine(ExcelAgencyName);
sw.Close();
writesFilename = true;
break;
}
DIM_AGENCY_GROUP objAgencyGroup = bc.DIM_AGENCY_GROUP.Where(m => m.AGENCY_GROUP_NAME.ToUpper() == ExcelAgencyGroupName.ToUpper() && m.SRC_FUNC_KEY == dbFunctionalKey).FirstOrDefault();
if (objAgencyGroup != null)
{
dbAgencyGroupKey = objAgencyGroup.AGENCY_GROUP_KEY;
}
if (dbAgencyGroupKey == 0)
{
decimal agencyGrpKey = AgencyGroupRepository.InsertAgencyGroup(ExcelAgencyGroupName, dbFunctionalKey);
if (agencyGrpKey != 0)
{
XREF_AGENCY_TAGGING objXrefTagging = bc.XREF_AGENCY_TAGGING.Where(m => m.AGENCY_KEY == AgencyKey && m.SRC_FUNC_KEY == dbFunctionalKey).FirstOrDefault();
if (objXrefTagging != null)
{
if (objXrefTagging.AGENCY_GROUP_KEY == agencyGrpKey)
{
}
else
{
decimal agencyTaggingSrNo = AgencyGroupRepository.UpdateTaggingAgencyAgencyGroup(AgencyKey, agencyGrpKey, dbFunctionalKey);
count = count + 1;
}
}
else
{
decimal agencyTaggingSrNo = AgencyGroupRepository.TagAgencyAgencyGroup(AgencyKey, agencyGrpKey, dbFunctionalKey);
count = count + 1;
}
}
}
else
{
XREF_AGENCY_TAGGING objXrefTagging = bc.XREF_AGENCY_TAGGING.Where(m => m.AGENCY_KEY == AgencyKey && m.SRC_FUNC_KEY == dbFunctionalKey).FirstOrDefault();
if (objXrefTagging != null)
{
if (objXrefTagging.AGENCY_GROUP_KEY == dbAgencyGroupKey)
{
}
else
{
decimal agencyTaggingSrNo = AgencyGroupRepository.UpdateTaggingAgencyAgencyGroup(AgencyKey, dbAgencyGroupKey, dbFunctionalKey);
count = count + 1;
}
}
else
{
decimal agencyTaggingSrNo = AgencyGroupRepository.TagAgencyAgencyGroup(AgencyKey, dbAgencyGroupKey, dbFunctionalKey);
count = count + 1;
}
}
}
}
//});
}
else
{
TempData["ErrorMsg"] = "Invalid file type. Upload only xlx or xlsx file type.";
return View("UploadData");
}
}
int totalCount = rowCount - count;
if (writesFilename == true && count > 0)
{
TempData["SuccessMsg"] = "Out of" + " " + rowCount + " " + "," + " " + totalCount + " " + "Agency names not found in database. Please view the text file saved on your desktop to get the agency names.";
}
else if (count == 0 && writesFilename == true)
{
TempData["ErrorMsg"] = "None of the agency names found in database. Please view the text file saved on your desktop to get the agency names.";
}
else if (count > 0 && writesFilename == false)
{
TempData["ErrorMsg"] = "Out of" + " " + rowCount + " " + "," + " " + totalCount + " " + "Agency group keys got updated.";
}
return View("UploadData");
}
解决方案
To read excel data
string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;" + @"Extended Properties='Excel 8.0;HDR=Yes;'"; using(OleDbConnection connection = new OleDbConnection(con)) { connection.Open(); OleDbCommand command = new OleDbCommand("select * from [Sheet1
", connection); using(OleDbDataReader dr = command.ExecuteReader()) { while(dr.Read()) { var row1Col0 = dr[0]; Console.WriteLine(row1Col0); } } }
Source
.net - How to read data from excel file using c# - Stack Overflow[^]
This is not just reading an Excel file, you also access a data base and do stuff.
First thing to do is to run a profiler to see how much time it takes for each part of the program. The part that take the most time is probably where you can gain the most.
Dividing by 2 the time it takes for a part that only take 10% of the whole will only save 5%.
Saving 2/3 of a part that take 75% is saving 50% of the whole.
You need to know which operation takes time before teaking you code.
Profiling (computer programming) - Wikipedia, the free encyclopedia[^]
这篇关于在C＃中读取Excel数据的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
