jxycms
3/19/2017 - 11:36 PM
Export data to excel using kentico builtin excel export function in customtable. treeprovider, customtableprovider cycletoursglobal
Export data to excel using kentico builtin excel export function in customtable. treeprovider, customtableprovider cycletoursglobal
public static bool ExcelOutput()
{
try
{
//get user id
int userId = CMSContext.CurrentUser.UserID;
//get user guid
UserInfo ui = UserInfoProvider.GetUserInfo(userId);
Guid userOperatorNodeGUID = ValidationHelper.GetGuid(ui.GetValue("UserOperatorNodeGUID"), new Guid());
//get treenode accoring to user guid
TreeProvider treeProvider = new TreeProvider(CMSContext.CurrentUser);
TreeNode nodeOperator = treeProvider.SelectSingleNode(userOperatorNodeGUID, CMSContext.CurrentSite.DefaultVisitorCulture, CMSContext.CurrentSiteName);
if (nodeOperator != null && CSHelper.Node.IsOperatorNode(nodeOperator) && CSHelper.IsNodeOwner(nodeOperator, CMSContext.CurrentUser))
{
//create excel columns
DataTable newDT = new DataTable();
newDT.Columns.Add("Operator_Name");
//newDT.Columns.Add("OwnRef");
newDT.Columns.Add("Status");
newDT.Columns.Add("Tour_Name");
newDT.Columns.Add("Sub-Heading");
newDT.Columns.Add("Display_Status");
newDT.Columns.Add("Expiry_Date");
newDT.Columns.Add("Description");
newDT.Columns.Add("Tour_URL");
newDT.Columns.Add("Special_Deal_Type");
newDT.Columns.Add("Special_Deal_Details");
newDT.Columns.Add("Special_Deal_Expiry");
newDT.Columns.Add("Tour_Photo");
//newDT.Columns.Add("TourImageURL");
newDT.Columns.Add("Tour_Video_URL");
newDT.Columns.Add("Country");
newDT.Columns.Add("Region");
newDT.Columns.Add("Start_Location");
newDT.Columns.Add("End_Location");
newDT.Columns.Add("Cost");
newDT.Columns.Add("Currency");
newDT.Columns.Add("Duration");
newDT.Columns.Add("Days_or_Hours");
newDT.Columns.Add("Guiding");
newDT.Columns.Add("Support");
newDT.Columns.Add("Bike Options");
newDT.Columns.Add("Level");
newDT.Columns.Add("Terrain");
newDT.Columns.Add("Tour_Type");
newDT.Columns.Add("Tour_Theme");
newDT.Columns.Add("Accommodation");
newDT.Columns.Add("Single_Supplement");
newDT.Columns.Add("Booking_Conditions");
newDT.Columns.Add("Group_Discount");
newDT.Columns.Add("Inclusions");
newDT.Columns.Add("Exclusions");
newDT.Columns.Add("Options");
newDT.Columns.Add("Tour_Language");
newDT.Columns.Add("Distance");
newDT.Columns.Add("Kilometres_or_Miles");
newDT.Columns.Add("Number_Riding_Days");
newDT.Columns.Add("Month(s)");
newDT.Columns.Add("Departure(s)");
foreach (TreeNode nodeLoop in nodeOperator.Children)
{
TreeNode nodeTour = treeProvider.SelectSingleNode(nodeLoop.NodeID);
if (nodeTour != null && CSHelper.Node.IsTourNode(nodeTour) && CSHelper.IsNodeOwner(nodeTour, CMSContext.CurrentUser) && nodeTour.GetValue("TourStatus").ToString() == "1")
{
StringBuilder sbRegion = new StringBuilder();
CustomTableItemProvider customProvider = new CustomTableItemProvider(CMSContext.CurrentUser);
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourRegion"), String.Empty)))
{
var TourRegion = ValidationHelper.GetString(nodeTour.GetValue("TourRegion"), String.Empty);
var TourRegionArray = TourRegion.Split(';');
for (int i = 0; i < TourRegionArray.Length; i++)
{
int itemIdRegion = ValidationHelper.GetInteger(TourRegionArray[i], 0);
CustomTableItem customTableItemRegion = null;
if (itemIdRegion != 0)
{
customTableItemRegion = customProvider.GetItem(itemIdRegion, "CTG.Region");
}
if (customTableItemRegion != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemRegion.GetValue("RegionName"), String.Empty)))
{
sbRegion.Append(customTableItemRegion.GetStringValue("RegionName", ""));
if (i != TourRegionArray.Length - 1)
{
sbRegion.Append("|");
}
}
}
}
//special deal
StringBuilder sbTourSpecialDeal = new StringBuilder();
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourSpecialDeal"), String.Empty)))
{
var TourSpecialDeal = ValidationHelper.GetString(nodeTour.GetValue("TourSpecialDeal"), String.Empty);
//split into an array ;
var TourSpecialDealArray = TourSpecialDeal.Split(';');
//loop array
for (int i = 0; i < TourSpecialDealArray.Length; i++)
{
int itemIdTourSpecialDeal = ValidationHelper.GetInteger(TourSpecialDealArray[i], 0);
CustomTableItem customTableItemTourSpecialDeal = null;
if (itemIdTourSpecialDeal != 0)
{
customTableItemTourSpecialDeal = customProvider.GetItem(itemIdTourSpecialDeal, "CTG.TourSpecialDeals");
}
if (customTableItemTourSpecialDeal != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourSpecialDeal.GetValue("SpecialDeal"), String.Empty)))
{
sbTourSpecialDeal.Append(customTableItemTourSpecialDeal.GetStringValue("SpecialDeal", ""));
if (i != TourSpecialDealArray.Length - 1)
{
sbTourSpecialDeal.Append("|");
}
}
}
}
//TourDurationDescription
var TourDurationDescription = "";
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourDurationDescription"), String.Empty)) && ValidationHelper.GetString(nodeTour.GetValue("TourDurationDescription"), String.Empty) != "0")
{
var TourDurationDescriptionID = ValidationHelper.GetString(nodeTour.GetValue("TourDurationDescription"), String.Empty);
int itemIdTourDurationDescription = ValidationHelper.GetInteger(TourDurationDescriptionID, 0);
CustomTableItem customTableItemTourDurationDescription = null;
if (itemIdTourDurationDescription != 0)
{
customTableItemTourDurationDescription = customProvider.GetItem(itemIdTourDurationDescription, "CTG.DurationDescription");
}
if (customTableItemTourDurationDescription != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourDurationDescription.GetValue("DurationDescriptionName"), String.Empty)))
{
TourDurationDescription = ValidationHelper.GetString(customTableItemTourDurationDescription.GetValue("DurationDescriptionName"), String.Empty);
}
}
//TourGuide
StringBuilder sbTourGuide = new StringBuilder();
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourGuide"), String.Empty)))
{
var TourGuide = ValidationHelper.GetString(nodeTour.GetValue("TourGuide"), String.Empty);
//split into an array ;
var TourGuideArray = TourGuide.Split(';');
//loop array
for (int i = 0; i < TourGuideArray.Length; i++)
{
int itemIdTourGuide = ValidationHelper.GetInteger(TourGuideArray[i], 0);
CustomTableItem customTableItemTourGuide = null;
if (itemIdTourGuide != 0)
{
customTableItemTourGuide = customProvider.GetItem(itemIdTourGuide, "CTG.Guide");
}
if (customTableItemTourGuide != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourGuide.GetValue("GuideName"), String.Empty)))
{
sbTourGuide.Append(customTableItemTourGuide.GetStringValue("GuideName", ""));
if (i != TourGuideArray.Length - 1)
{
sbTourGuide.Append("|");
}
}
}
}
//TourSupport
StringBuilder sbTourSupport = new StringBuilder();
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourSupport"), String.Empty)))
{
var TourSupport = ValidationHelper.GetString(nodeTour.GetValue("TourSupport"), String.Empty);
//split into an array ;
var TourSupportArray = TourSupport.Split(';');
//loop array
for (int i = 0; i < TourSupportArray.Length; i++)
{
int itemIdTourSupport = ValidationHelper.GetInteger(TourSupportArray[i], 0);
CustomTableItem customTableItemTourSupport = null;
if (itemIdTourSupport != 0)
{
customTableItemTourSupport = customProvider.GetItem(itemIdTourSupport, "CTG.TourSupport");
}
if (customTableItemTourSupport != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourSupport.GetValue("SupportName"), String.Empty)))
{
sbTourSupport.Append(customTableItemTourSupport.GetStringValue("SupportName", ""));
if (i != TourSupportArray.Length - 1)
{
sbTourSupport.Append("|");
}
}
}
}
//TourMoreOptions
StringBuilder sbTourMoreOptions = new StringBuilder();
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourMoreOptions"), String.Empty)))
{
var TourMoreOptions = ValidationHelper.GetString(nodeTour.GetValue("TourMoreOptions"), String.Empty);
//split into an array ;
var TourMoreOptionsArray = TourMoreOptions.Split(';');
//loop array
for (int i = 0; i < TourMoreOptionsArray.Length; i++)
{
int itemIdTourMoreOptions = ValidationHelper.GetInteger(TourMoreOptionsArray[i], 0);
CustomTableItem customTableItemTourMoreOptions = null;
if (itemIdTourMoreOptions != 0)
{
customTableItemTourMoreOptions = customProvider.GetItem(itemIdTourMoreOptions, "CTG.Bikeptions");
}
if (customTableItemTourMoreOptions != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourMoreOptions.GetValue("OptionsName"), String.Empty)))
{
sbTourMoreOptions.Append(customTableItemTourMoreOptions.GetStringValue("OptionsName", ""));
if (i != TourMoreOptionsArray.Length - 1)
{
sbTourMoreOptions.Append("|");
}
}
}
}
//TourLevel
StringBuilder sbTourLevel = new StringBuilder();
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourLevel"), String.Empty)))
{
var TourLevel = ValidationHelper.GetString(nodeTour.GetValue("TourLevel"), String.Empty);
//split into an array ;
var TourLevelArray = TourLevel.Split(';');
//loop array
for (int i = 0; i < TourLevelArray.Length; i++)
{
int itemIdTourLevel = ValidationHelper.GetInteger(TourLevelArray[i], 0);
CustomTableItem customTableItemTourLevel = null;
if (itemIdTourLevel != 0)
{
customTableItemTourLevel = customProvider.GetItem(itemIdTourLevel, "CTG.Level");
}
if (customTableItemTourLevel != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourLevel.GetValue("LevelName"), String.Empty)))
{
sbTourLevel.Append(customTableItemTourLevel.GetStringValue("LevelName", ""));
if (i != TourLevelArray.Length - 1)
{
sbTourLevel.Append("|");
}
}
}
}
//TourTerrain
StringBuilder sbTourTerrain = new StringBuilder();
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourTerrain"), String.Empty)))
{
var TourTerrain = ValidationHelper.GetString(nodeTour.GetValue("TourTerrain"), String.Empty);
//split into an array ;
var TourTerrainArray = TourTerrain.Split(';');
//loop array
for (int i = 0; i < TourTerrainArray.Length; i++)
{
int itemIdTourTerrain = ValidationHelper.GetInteger(TourTerrainArray[i], 0);
CustomTableItem customTableItemTourTerrain = null;
if (itemIdTourTerrain != 0)
{
customTableItemTourTerrain = customProvider.GetItem(itemIdTourTerrain, "CTG.Terrain");
}
if (customTableItemTourTerrain != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourTerrain.GetValue("TerrainName"), String.Empty)))
{
sbTourTerrain.Append(customTableItemTourTerrain.GetStringValue("TerrainName", ""));
if (i != TourTerrainArray.Length - 1)
{
sbTourTerrain.Append("|");
}
}
}
}
//TourSpeciality
StringBuilder sbTourSpeciality = new StringBuilder();
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourSpeciality"), String.Empty)))
{
var TourSpeciality = ValidationHelper.GetString(nodeTour.GetValue("TourSpeciality"), String.Empty);
//split into an array ;
var TourSpecialityArray = TourSpeciality.Split(';');
//loop array
for (int i = 0; i < TourSpecialityArray.Length; i++)
{
int itemIdTourSpeciality = ValidationHelper.GetInteger(TourSpecialityArray[i], 0);
CustomTableItem customTableItemTourSpeciality = null;
if (itemIdTourSpeciality != 0)
{
customTableItemTourSpeciality = customProvider.GetItem(itemIdTourSpeciality, "CTG.Speciality");
}
if (customTableItemTourSpeciality != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourSpeciality.GetValue("SpecialityName"), String.Empty)))
{
sbTourSpeciality.Append(customTableItemTourSpeciality.GetStringValue("SpecialityName", ""));
if (i != TourSpecialityArray.Length - 1)
{
sbTourSpeciality.Append("|");
}
}
}
}
//TourTheme
StringBuilder sbTourTheme = new StringBuilder();
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourTheme"), String.Empty)))
{
var TourTheme = ValidationHelper.GetString(nodeTour.GetValue("TourTheme"), String.Empty);
//split into an array ;
var TourThemeArray = TourTheme.Split(';');
//loop array
for (int i = 0; i < TourThemeArray.Length; i++)
{
int itemIdTourTheme = ValidationHelper.GetInteger(TourThemeArray[i], 0);
CustomTableItem customTableItemTourTheme = null;
if (itemIdTourTheme != 0)
{
customTableItemTourTheme = customProvider.GetItem(itemIdTourTheme, "CTG.TourTheme");
}
if (customTableItemTourTheme != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourTheme.GetValue("ThemeName"), String.Empty)))
{
sbTourTheme.Append(customTableItemTourTheme.GetStringValue("ThemeName", ""));
if (i != TourThemeArray.Length - 1)
{
sbTourTheme.Append("|");
}
}
}
}
//TourAccommodation
StringBuilder sbTourAccommodation = new StringBuilder();
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourAccommodation"), String.Empty)))
{
var TourAccommodation = ValidationHelper.GetString(nodeTour.GetValue("TourAccommodation"), String.Empty);
//split into an array ;
var TourAccommodationArray = TourAccommodation.Split(';');
//loop array
for (int i = 0; i < TourAccommodationArray.Length; i++)
{
int itemIdTourAccommodation = ValidationHelper.GetInteger(TourAccommodationArray[i], 0);
CustomTableItem customTableItemTourAccommodation = null;
if (itemIdTourAccommodation != 0)
{
customTableItemTourAccommodation = customProvider.GetItem(itemIdTourAccommodation, "CTG.Accommodation");
}
if (customTableItemTourAccommodation != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourAccommodation.GetValue("AccommodationType"), String.Empty)))
{
sbTourAccommodation.Append(customTableItemTourAccommodation.GetStringValue("AccommodationType", ""));
if (i != TourAccommodationArray.Length - 1)
{
sbTourAccommodation.Append("|");
}
}
}
}
//TourLanguage
StringBuilder sbTourLanguage = new StringBuilder();
if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourLanguage"), String.Empty)))
{
var TourLanguage = ValidationHelper.GetString(nodeTour.GetValue("TourLanguage"), String.Empty);
//split into an array ;
var TourLanguageArray = TourLanguage.Split(';');
//loop array
for (int i = 0; i < TourLanguageArray.Length; i++)
{
int itemIdTourLanguage = ValidationHelper.GetInteger(TourLanguageArray[i], 0);
CustomTableItem customTableItemTourLanguage = null;
if (itemIdTourLanguage != 0)
{
customTableItemTourLanguage = customProvider.GetItem(itemIdTourLanguage, "CTG.Language");
}
if (customTableItemTourLanguage != null && !String.IsNullOrEmpty(ValidationHelper.GetString(customTableItemTourLanguage.GetValue("LanguageName"), String.Empty)))
{
sbTourLanguage.Append(customTableItemTourLanguage.GetStringValue("LanguageName", ""));
if (i != TourLanguageArray.Length - 1)
{
sbTourLanguage.Append("|");
}
}
}
}
//Get domain name in code
//TourImageURL
//string imageUrl = "";
//string domainName = URLHelper.GetCurrentDomain();
//if (!String.IsNullOrEmpty(ValidationHelper.GetString(nodeTour.GetValue("TourImage"), String.Empty))) {
// imageUrl = String.Format("{1}/CMSPages/GetFile.aspx?guid={0}", ValidationHelper.GetString(nodeTour.GetValue("TourImage"), String.Empty), domainName);
//}
//TourImageFileName
var imageFileName = "";
DataTable summaryTourDataTable = customProvider.GetItems("CTG.SummaryTour", String.Format("NodeGUID = '{0}'", nodeLoop.NodeGUID), "").Tables[0];
if(summaryTourDataTable.Rows.Count > 0){
imageFileName = summaryTourDataTable.Rows[0]["TourImage"].ToString();
}
//TourDistanceDescription
string TourDistanceDescription = "";
switch (ValidationHelper.GetString(nodeTour.GetValue("TourDistanceDescription"), String.Empty))
{
case "1":
TourDistanceDescription = "Kilometers";
break;
case "2":
TourDistanceDescription = "Miles";
break;
default:
TourDistanceDescription = "";
break;
}
//add rows to excel
DataRow newRow = newDT.NewRow();
newRow["Operator_Name"] = nodeOperator.NodeName;
//newRow["OwnRef"] = nodeOperator.NodeID;
newRow["Status"] = ValidationHelper.GetString(nodeTour.GetValue("TourStatusByOperator"), String.Empty) == "0" ? "UNPUBLISHED" : "PUBLISHED";
newRow["Tour_Name"] = ValidationHelper.GetString(nodeTour.GetValue("TourName"), String.Empty);
newRow["Sub-Heading"] = ValidationHelper.GetString(nodeTour.GetValue("TourTagLine"), String.Empty);
newRow["Description"] = ValidationHelper.GetString(nodeTour.GetValue("TourDescription"), String.Empty);
newRow["Tour_URL"] = ValidationHelper.GetString(nodeTour.GetValue("TourURL"), String.Empty);
newRow["Special_Deal_Type"] = sbTourSpecialDeal;
newRow["Special_Deal_Details"] = ValidationHelper.GetString(nodeTour.GetValue("TourNote"), String.Empty);
newRow["Special_Deal_Expiry"] = ValidationHelper.GetDateTime(nodeTour.GetValue("TourSpecialDealExpiry"), new DateTime()) == new DateTime() ? "" : nodeTour.GetValue("TourSpecialDealExpiry");
newRow["Tour_Photo"] = imageFileName;
//newRow["TourImageURL"] = imageUrl;
newRow["Tour_Video_URL"] = ValidationHelper.GetString(nodeTour.GetValue("TourVideo"), String.Empty);
newRow["Country"] = ValidationHelper.GetString(nodeTour.GetValue("TourCountry"), String.Empty).Replace(';', '|');
newRow["Region"] = sbRegion.ToString();
newRow["Start_Location"] = ValidationHelper.GetString(nodeTour.GetValue("TourStartPoint"), String.Empty);
newRow["End_Location"] = ValidationHelper.GetString(nodeTour.GetValue("TourEndPoint"), String.Empty);
newRow["Cost"] = ValidationHelper.GetDouble(nodeTour.GetValue("TourCost"), 0) != 0 ? nodeTour.GetValue("TourCost") : "";
newRow["Currency"] = ValidationHelper.GetString(nodeTour.GetValue("TourCostCurrencyCode"), String.Empty);
newRow["Duration"] = ValidationHelper.GetDouble(nodeTour.GetValue("TourDuration"), 0) != 0 ? nodeTour.GetValue("TourDuration") : "";
newRow["Days_or_Hours"] = TourDurationDescription;
newRow["Guiding"] = sbTourGuide;
newRow["Support"] = sbTourSupport;
newRow["Bike Options"] = sbTourMoreOptions;
newRow["Level"] = sbTourLevel;
newRow["Terrain"] = sbTourTerrain;
newRow["Tour_Type"] = sbTourSpeciality;
newRow["Tour_Theme"] = sbTourTheme;
newRow["Accommodation"] = sbTourAccommodation;
newRow["Single_Supplement"] = ValidationHelper.GetString(nodeTour.GetValue("TourSupplement"), String.Empty);
newRow["Booking_Conditions"] = ValidationHelper.GetString(nodeTour.GetValue("TourBookingConditions"), String.Empty);
newRow["Group_Discount"] = ValidationHelper.GetString(nodeTour.GetValue("TourGroupDiscount"), String.Empty);
newRow["Inclusions"] = ValidationHelper.GetString(nodeTour.GetValue("TourInclusions"), String.Empty);
newRow["Exclusions"] = ValidationHelper.GetString(nodeTour.GetValue("TourExclusions"), String.Empty);
newRow["Options"] = ValidationHelper.GetString(nodeTour.GetValue("TourOptions"), String.Empty);
newRow["Tour_Language"] = sbTourLanguage;
newRow["Distance"] = ValidationHelper.GetDouble(nodeTour.GetValue("TourDistance"), 0) != 0 ? nodeTour.GetValue("TourDistance"): "" ;
newRow["Kilometres_or_Miles"] = TourDistanceDescription;
newRow["Number_Riding_Days"] = ValidationHelper.GetInteger(nodeTour.GetValue("TourNumberOfRidingDays"), 0) != 0 ? nodeTour.GetValue("TourNumberOfRidingDays") : "";
newRow["Month(s)"] = ValidationHelper.GetString(nodeTour.GetValue("TourMonth"), String.Empty);
newRow["Departure(s)"] = ValidationHelper.GetString(nodeTour.GetValue("TourDateNote"), String.Empty);
newRow["Expiry_Date"] = ValidationHelper.GetDateTime(nodeTour.GetValue("TourPremiumExpiryDate"), new DateTime()) == new DateTime() ? "" : nodeTour.GetValue("TourPremiumExpiryDate");
newRow["Display_Status"] = ValidationHelper.GetInteger(nodeTour.GetValue("TourDisplayClass"), 0) != 0 ? "PREMIUM TOUR" : "FREE TOUR";
newDT.Rows.Add(newRow);
}
}
//DataView dv = newDT.DefaultView;
//dv.Sort = "TourDisplayClass desc, TourUserDateUpdated asc";
//DataTable sortedDT = dv.ToTable();
DataSet TourSet = new DataSet();
TourSet.Tables.Add(newDT);
ExportData(DataExportFormatEnum.CSV, TourSet);
}
EventLogProvider.LogInformation("Export tour information to excel", "Success", "Success export tour information to excel");
return true;
}
catch (Exception ex)
{
EventLogProvider.LogException("Export tour information to excel", "Fail", ex);
return false;
}
}
//export dataset to excel in kentico
private static void ExportData(DataExportFormatEnum format, DataSet ds)
{
DataExportHelper eh = new DataExportHelper(ds);
eh.FileName = "CycleToursGlobal";
HttpResponse Response = HttpContext.Current.Response;
eh.ExportData(format, Response);
}