使用ASP.NET MVC的Excel上传 [英] Excel Upload using ASP.NET MVC

问题描述

大家好，

我想从Excel上载1Lac帐户到sql DB.它应该快速上载并且应该没有任何性能问题.所以请告诉我我该怎么做才能提高从Excel读取的性能?

代码:

Hi All,

I want to upload 1Lac accounts from Excel to sql DB.It should upload fast and there should not be any performance issue.So please tell me what can i do to improve my performance for reading from Excel ?

Code:

DataSet ds = new DataSet();
            if (Request.Files["file"].ContentLength > 0)
            {
                string fileExtension =
                                     System.IO.Path.GetExtension(Request.Files["file"].FileName);

                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                    string fileLocation = Path.Combine(Server.MapPath("~/Input"), Path.GetFileName(FilePath.FileName));
                    if (System.IO.File.Exists(fileLocation))
                    {

                        System.IO.File.Delete(fileLocation);
                    }
                    // File is saving into Input Folder 

                    Request.Files["file"].SaveAs(fileLocation);

                    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

                    //connection String for xls file format.
                    if (fileExtension == ".xls")
                    {
                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    }

                    //connection String for xlsx file format.
                    else if (fileExtension == ".xlsx")
                    {
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }

                    //Create Connection to Excel work book and add oledb namespace
                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                    excelConnection.Open();

                    string query = string.Format("Select " + FieldName + " from [Sheet1$]");                   

                    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection))
                    {
                        dataAdapter.Fill(ds);
                    }

数据库:

DB:

ALTER PROCEDURE [dbo].[usp_insert_data_to_ATB_Report]
	
	@ATB ATBReport READONLY, // ATBReport is a Table Variable
	@STATUS INT OUTPUT
	
AS
BEGIN

 BEGIN TRY	
	
	
	INSERT INTO TB_IMPORT_MASTER 
	SELECT * FROM @ATB
	
	SELECT @STATUS = 1
	
 END TRY
 
 BEGIN CATCH
 
 SELECT @STATUS = 0
 
 END CATCH;
	
END

推荐答案

")； 使用(OleDbDataAdapter dataAdapter = 新 OleDbDataAdapter(查询，excelConnection)) { dataAdapter.Fill(ds); }

"); using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection)) { dataAdapter.Fill(ds); }

数据库:

DB:

ALTER PROCEDURE [dbo].[usp_insert_data_to_ATB_Report]
	
	@ATB ATBReport READONLY, // ATBReport is a Table Variable
	@STATUS INT OUTPUT
	
AS
BEGIN

 BEGIN TRY	
	
	
	INSERT INTO TB_IMPORT_MASTER 
	SELECT * FROM @ATB
	
	SELECT @STATUS = 1
	
 END TRY
 
 BEGIN CATCH
 
 SELECT @STATUS = 0
 
 END CATCH;
	
END

这篇关于使用ASP.NET MVC的Excel上传的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！