Read Excel file from sharepoint folder to Microsoft dynamics D365FO

Finance and Operations/AX
1

How to read Excel file from sharepoint folder into Microsoft dynamics D365FO by x++ code.

2

To read an Excel file from a SharePoint folder into Microsoft Dynamics D365FO using X++ code, you can follow these steps:

  1. Create a new file in Visual Studio and connect it to the Dynamics D365FO environment.
  2. Use the WebClient class to access the SharePoint file location and download the Excel file to a local folder in D365FO.
  3. Use the ExcelApplicationClass and Workbook classes to open the Excel file from the local folder and read the data from the worksheets.
  4. Use the DataTable class to store the data from the Excel file.
  5. Use the TableData class to insert the data from the DataTable into a table in Dynamics D365FO.

Here’s an example code snippet:

WebClient webClient = new WebClient();
webClient.Credentials = new NetworkCredential(“username”, “password”); // provide credentials for SharePoint access
webClient.DownloadFile(“https://sharepointurl/folder/excelfilename.xlsx”, @“C:\temp\excelfilename.xlsx”); // download the Excel file to a local folder

ExcelApplicationClass excel = new ExcelApplicationClass();
Workbook workbook = excel.Workbooks.Open(@“C:\temp\excelfilename.xlsx”); // open the Excel file

Worksheet worksheet = workbook.Worksheets[1]; // select the first worksheet
Range range = worksheet.UsedRange;
DataTable dt = new DataTable();

for (int row = 1; row <= range.Rows.Count; row++)
{
DataRow dr = dt.NewRow();
for (int col = 1; col <= range.Columns.Count; col++)
{
dr[col - 1] = ((Range)range.Cells[row, col]).Value2;
}
dt.Rows.Add(dr);
}

TableData tableData = new TableData(tableName); // replace tableName with the name of the table to insert data into
tableData.insertRecord(dt);

workbook.Close(false);
excel.Quit();

Note: Make sure to handle any exceptions that may occur while accessing the SharePoint file or reading from the Excel file. Also, replace the placeholders in the code with the actual values for your environment.

3

Hi Jonathon,

Thanks for the inputs.

Step 1 : which format file should be created from Visual studio and how to connect with D365FO ?

file
file1336×736 27.9 KB

Step 2 : While using ExcelApplicationClass and WebClient classes as mentioned

It gives an error as “doesnot denote class, a table or an Extended data type.”

Do I need to add any module reference to my Model ?