Excel import

Export of Excel file on eBA Server to Detail Table

---

The eBA application Server must have Microsoft Access Database Engine 2016 installed.

---

As soon as the form is opened, it is intended to transfer the excel file content in the windows folder on the server where the windows application is installed to the detail table object. In the onLoadData event, excel is read and transferred to the datatable, and the data in the datatable is printed to the detail table with the external function.

---

The number of 'Maximum Row Count' in the detail table should be taken into account.

---

Reference DLL to be added to the project

%SystemPath%\Common\eBADB.dll

---

using System;

using System.Collections;

using System.Text;

using System.Data;

using eBAControls;

using eBAControls.eBABaseForm;

using eBAFormData;

using System.Web.UI.WebControls;

using System.Data.OleDb;

using System.Data.SqlClient;

namespace Excel

{

public partial class Form

{

public void OnLoadData()

{

string FileName = @"C:\BimserSolution\eBA\DMFiles\Sample.xlsx";

string sheetName = "sheet1$"; Sheet to read in Excel

DataTable dtResult = new DataTable();

using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FileName + "; Extended Properties='Excel 12.0; HDR=YES; IMEX=1;';"))

{

objConn.Open();

OleDbCommand cmd = new OleDbCommand();

OleDbDataAdapter oleda = new OleDbDataAdapter();

cmd.Connection = objConn;

cmd.CommandType = CommandType.Text;

cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

oleda = new OleDbDataAdapter(cmd);

oleda. Fill(dtResult);

objConn.Close();

}

SaveDataDetailsGrid(dtResult);

}

public void SaveDataDetailsGrid(DataTable dtExcel)

{

Try

{

if (dtExcel.Rows.Count > 0)

{

for (int i = 0; i < dtExcel.Rows.Count; i++)

{

DetailTable1.CurrentRowCount++;

((TextBox)DetailTable1.GetRowObject(i, "txtID")). Text = dtExcel.Rows[i]["ID"]. ToString();

((TextBox)DetailTable1.GetRowObject(i, "txtName")). Text = dtExcel.Rows[i]["Name"]. ToString();

((TextBox)DetailTable1.GetRowObject(i, "txtGender")). Text = dtExcel.Rows[i]["Gender"]. ToString();

}

}

}

catch (Exception ex)

{

throw new Exception(ex. Message);

}

}

}

}