ExcelClient

Namespace: AVEVA.IntegrationService.DataAPI.SDK

Interface: IExcelClient

Description

ExcelClient is used to communicate with Excel ProductCategory products, providing methods to retrieve and post Excel file data.

Methods

GetExcels

Retrieves all available Excel files info under the configured path.

Signature:

Task<IEnumerable<Excel>> GetExcels(string datasourceName, bool liveData = false)

Example:

using AVEVA.IntegrationService.DataAPI.SDK.Models;

// Create the Excel client

string host = "https://your-server/integration/v1";

string token = "your-access-token";

var excelClient = DataApiClientFactory.CreateDataApiClient<ExcelClient>(

host: host,

authType: AuthenticationType.Connect,

waitingTimeInMinutesForLiveData: 60,

token: token,

hubCancellationToken: null

);

// Get all Excel files

IEnumerable<Excel> excelFiles = await excelClient.GetExcels(

"MyExcelDataSource",

liveData: true

);

foreach (var excel in excelFiles)

{

Console.WriteLine($"Excel File: {excel.Name}, Path: {excel.Path}");

}

GetExcelData

Retrieves specific Excel file data.

Signature:

Task<DataSet> GetExcelData(string datasourceName, string excelName, bool liveData = false)

Example:

DataSet excelData = await excelClient.GetExcelData(

"MyExcelDataSource",

"FinancialReport.xlsx",

liveData: true

);

// Each sheet is a DataTable

foreach (DataTable sheet in excelData.Tables)

{

Console.WriteLine($"Sheet: {sheet.TableName}, Rows: {sheet.Rows.Count}");

}

GetExcelSheetList

Retrieves list of sheets available in a specific Excel file.

Signature:

Task<IEnumerable<string>> GetExcelSheetList(string datasourceName, string excelName, bool liveData = false)

Example:

IEnumerable<string> sheets = await excelClient.GetExcelSheetList(

"MyExcelDataSource",

"FinancialReport.xlsx",

liveData: true

);

foreach (string sheet in sheets)

{

Console.WriteLine($"Sheet: {sheet}");

}

GetExcelSheetData

Retrieves specific sheet data from an Excel file.

Signature:

Task<DataTable> GetExcelSheetData(

string datasourceName,

string excelName,

string sheetName,

string filter = "",

bool liveData = false

)

Example:

// Get specific sheet with optional filter

DataTable sheetData = await excelClient.GetExcelSheetData(

"MyExcelDataSource",

"FinancialReport.xlsx",

"Revenue",

filter: "Year eq 2024",

liveData: true

);

foreach (DataRow row in sheetData.Rows)

{

Console.WriteLine($"Data: {string.Join(", ", row.ItemArray)}");

}

GetExcelDataByAcknowledgementId

Retrieves Excel file data using acknowledgement ID.

Signature:

Task<DataSet> GetExcelDataByAcknowledgementId(string datasourceName, string acknowledgementId)

Example:

DataSet excelData = await excelClient.GetExcelDataByAcknowledgementId(

"MyExcelDataSource",

"ack-12345-67890"

);

PostExcelFileList

Posts list of Excel files information.

Signature:

Task<AcknowledgementResult> PostExcelFileList(

string datasourceName,

DataSet data,

string ackId = "",

string Context = "",

string Topic = "",

int expiryDuration = 0

)

Example:

// Create dataset with Excel file list

DataSet fileList = new DataSet();

DataTable filesTable = new DataTable("ExcelFiles");

filesTable.Columns.Add("Name", typeof(string));

filesTable.Columns.Add("Path", typeof(string));

filesTable.Columns.Add("LastModified", typeof(DateTime));

filesTable.Rows.Add("Report1.xlsx", "C:\\Reports\\Report1.xlsx", DateTime.Now);

filesTable.Rows.Add("Report2.xlsx", "C:\\Reports\\Report2.xlsx", DateTime.Now);

fileList.Tables.Add(filesTable);

AcknowledgementResult result = await excelClient.PostExcelFileList(

"MyExcelDataSource",

fileList,

ackId: "",

Context: "{\"Source\":\"FileSync\"}",

expiryDuration: 48

);

Console.WriteLine($"Posted file list. AckId: {result.AcknowledgementId}");

PostExcelSheetList

Posts sheet list for a specific Excel file.

Signature:

Task<AcknowledgementResult> PostExcelSheetList(

string datasourceName,

string excelFileName,

DataSet data,

string ackId = "",

string Context = "",

string Topic = "",

int expiryDuration = 0

)

Example:

DataSet sheetList = new DataSet();

DataTable sheets = new DataTable("Sheets");

sheets.Columns.Add("SheetName", typeof(string));

sheets.Rows.Add("Summary");

sheets.Rows.Add("Details");

sheetList.Tables.Add(sheets);

AcknowledgementResult result = await excelClient.PostExcelSheetList(

"MyExcelDataSource",

"FinancialReport.xlsx",

sheetList

);

PostExcelData

Posts specific Excel file data.

Signature:

Task<AcknowledgementResult> PostExcelData(

string datasourceName,

string fileName,

DataSet data,

string ackId = "",

string Context = "",

string Topic = "",

int expiryDuration = 0

)

Example:

// Create Excel data with multiple sheets

DataSet excelData = new DataSet();

DataTable sheet1 = new DataTable("Summary");

sheet1.Columns.Add("Item", typeof(string));

sheet1.Columns.Add("Value", typeof(decimal));

sheet1.Rows.Add("Total", 1000.00);

excelData.Tables.Add(sheet1);

DataTable sheet2 = new DataTable("Details");

sheet2.Columns.Add("Date", typeof(DateTime));

sheet2.Columns.Add("Amount", typeof(decimal));

sheet2.Rows.Add(DateTime.Now, 500.00);

excelData.Tables.Add(sheet2);

AcknowledgementResult result = await excelClient.PostExcelData(

"MyExcelDataSource",

"Report.xlsx",

excelData

);

PostExcelSheetData

Posts specific sheet data from an Excel file.

Signature:

Task<AcknowledgementResult> PostExcelSheetData(

string datasourceName,

string fileName,

string sheetName,

DataTable data,

string ackId = "",

string Context = "",

string Topic = "",

int expiryDuration = 0

)

Example:

DataTable sheetData = new DataTable("Revenue");

sheetData.Columns.Add("Year", typeof(int));

sheetData.Columns.Add("Revenue", typeof(decimal));

sheetData.Rows.Add(2024, 1500000.00);

AcknowledgementResult result = await excelClient.PostExcelSheetData(

"MyExcelDataSource",

"FinancialReport.xlsx",

"Revenue",

sheetData

);