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;
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");
}
Submit an article or tip