Microsoft.ACE.OLEDB connection timeout

Competetor

7y1.7k.NET

I am using excel template to export/import data to sql server table in asp.net. My code is working fine in my local system. I can write data to excel template and read data from the template to save to sql server table even if the number of rows increases. My application is hosted in "Windows Server 2017 Standard Evaluation". There is no problem on writing and reading data to excel template from the online application hosted in the server if the number of rows is less. But if the number of rows increases, I cannot write to excel file and only empty excel template is downloaded. And also I cannot read data from excel if the number of rows increases. Moreover, no error is generated to check the issue. Can anyone help me to solve my issue? It may be due to connection timeout. I have already tried some solutions but could not solve my problem. I would like to thanks in advance for any working solution for my problem.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Competetor7y ago
Thank you Leon. I have already used other library for this purpose.
Leon D7y ago
You can consider using a library such as free spire.xls (available on nuget.org) to do the data import/export. There is no difference no matter you host the application in server or local system.

Excel to DataTable:
Workbook workbook = new Workbook();
workbook.LoadFromFile(@”..\FandH.xlsx”);

Worksheet sheet = workbook.Worksheets[0];

DataTable dt = sheet.ExportDataTable();
DataTable to Excel:
Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

sheet.InsertDataTable(dt, true, 1, 1);

workbook.SaveToFile("insertTableToExcel.xlsx",ExcelVersion.Version2013);