I download the data from COM_Address table using kentico API in C#

I have used below code to download the content for the order item's. For that you need to create the string builder text and then you can write it as bellow. This is also dynamically create the header according to the column name.

DataSet ds = addresses.Result;
DataTable dt = ds.Tables[0];
string filePath = "FILE PATH";
StringBuilder sb = new StringBuilder();
List<string> CsvRow = new List<string>();

//ADD HEADER

foreach (DataColumn c in dt.Columns)
{
    CsvRow.Add(c.ColumnName);
}

sb.AppendLine(string.Join(",", CsvRow));

//ADD DATA

foreach (DataRow r in dt.Rows)
{
    CsvRow.Clear();
    foreach (DataColumn c in dt.Columns)
    {
        CsvRow.Add(r[c].ToString());
    }
    sb.AppendLine(string.Join(",", CsvRow));
}

File.WriteAllText(filePath, sb.ToString());

Hi,

You can use already existing helpers that Kentico provides to store it in excel file. Here is code sample how to achieve that:

var addresses = AddressInfoProvider.GetAddresses().Columns("AddressName", "AddressLine1", "AddressLine2", "AddressCity", "AddressZip", "AddressPhone");
var fileName = "addresses-upload-template.xlsx";
var exportFilePath = FileHelper.GetFullFilePhysicalPath(ImportExportHelper.GetSiteUtilsFolder()) + fileName;

var dataExportHelper = new DataExportHelper(addresses.Result);
using (System.IO.FileStream output = new System.IO.FileStream(exportFilePath, FileMode.Create))
{
     dataExportHelper.ExportData(DataExportFormatEnum.XLSX, output);
}

Prerequisite is that 'CMSSiteUtils' folder exist in your solution.

Best regards, Dragoljub

I need join below three tables. com_Address, com_Customerustomer and cms_user in the 3 tables common column is com_address.CustomerID, com_customer.CustomerID and cms_user.userID. How to use join and where condition in Kentico API.

var userQuery = UserInfoProvider.GetUsers().Columns("UserID"); var customeraddress = CustomerInfoProvider.GetCustomers() .Columns("CustomerID", "CustomerFirstName", "CustomerLastName", "CustomerCompany", "CustomerUserID");

var addresses = AddressInfoProvider.GetAddresses() .Columns("AddressID", "AddressName", "AddressLine1", "AddressLine2", "AddressCity", "AddressZip", "AddressPhone", "Email", "CompanyName");

You have to use Data Query.

Example : var data = new DataQuery().From(new QuerySource("CMS_Users") .LeftJoin("CustomTable_TableName","CMS_Users.UserID","CustomTable_TableName.UserID")) .Where("CustomTable_TableName.UserId", QueryUnaryOperator.IsNull);

refer this link

The above code is working fine, Thanks. I download the data from com_Address table to excel file, in the table country and states are in ID format only. I need to download the excel in country name and state name.

var addresses = UserInfoProvider.GetUsers() .Source(sourceItem => sourceItem.Join

You have to add column condition which you want in query '.Column("COLUMNAME")'

Example :

var data = new DataQuery().From(new QuerySource("CMS_Users")    .LeftJoin("CustomTable_TableName","CMS_Users.UserID","CustomTable_TableName.UserID"))   .Where("CustomTable_TableName.UserId", QueryUnaryOperator.IsNull).Column("COLUMNAME1","COLUMNAME2");