/// <summary> /// Export charge to XLS /// </summary> /// <param name="filePath">File path to use</param> /// <param name="orders">Orders</param> public void ExportChargeToXls(string filePath, List<Charge> charges) { using (ExcelHelper excelHelper = new ExcelHelper(filePath)) { excelHelper.Hdr = "YES"; excelHelper.Imex = "0"; Dictionary<string, string> tableDefinition = new Dictionary<string, string>(); tableDefinition.Add("ChargeID", "int"); tableDefinition.Add("Amount", "decimal"); tableDefinition.Add("IsInvoiceCharge", "bit"); excelHelper.WriteTable("Charge", tableDefinition); foreach (var charge in charges) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [Charge] (ChargeID, Amount, IsInvoiceCharge) VALUES ("); sb.Append(charge.ChargeID); sb.Append(","); sb.Append('"'); sb.Append(charge.Amount); sb.Append('"'); sb.Append(","); sb.Append(charge.IsInvoiceCharge); sb.Append(")"); excelHelper.ExecuteCommand(sb.ToString()); } } }
/// <summary> /// Export customer list to XLS /// </summary> /// <param name="FilePath">File path to use</param> /// <param name="customers">Customers</param> public static void ExportCustomersToXLS(string FilePath, CustomerCollection customers) { using (ExcelHelper excelHelper = new ExcelHelper(FilePath)) { excelHelper.HDR = "YES"; excelHelper.IMEX = "0"; Dictionary<string, string> tableDefinition = new Dictionary<string,string>(); tableDefinition.Add("CustomerID", "int"); tableDefinition.Add("CustomerGUID", "uniqueidentifier"); tableDefinition.Add("Email", "nvarchar(255)"); tableDefinition.Add("Username", "nvarchar(255)"); tableDefinition.Add("PasswordHash", "nvarchar(255)"); tableDefinition.Add("SaltKey", "nvarchar(255)"); tableDefinition.Add("AffiliateID", "int"); tableDefinition.Add("BillingAddressID", "int"); tableDefinition.Add("ShippingAddressID", "int"); tableDefinition.Add("LastPaymentMethodID", "int"); tableDefinition.Add("LastAppliedCouponCode", "nvarchar(255)"); tableDefinition.Add("LanguageID", "int"); tableDefinition.Add("CurrencyID", "int"); tableDefinition.Add("TaxDisplayTypeID", "int"); tableDefinition.Add("IsTaxExempt", "nvarchar(5)"); tableDefinition.Add("IsAdmin", "nvarchar(5)"); tableDefinition.Add("IsGuest", "nvarchar(5)"); tableDefinition.Add("IsForumModerator", "nvarchar(5)"); tableDefinition.Add("TotalForumPosts", "int"); tableDefinition.Add("Signature", "nvarchar(255)"); tableDefinition.Add("AdminComment", "nvarchar(255)"); tableDefinition.Add("Active", "nvarchar(5)"); tableDefinition.Add("Deleted", "nvarchar(5)"); tableDefinition.Add("RegistrationDate", "datetime"); tableDefinition.Add("TimeZoneID", "nvarchar(200)"); tableDefinition.Add("AvatarID", "int"); tableDefinition.Add("Gender", "nvarchar(100)"); tableDefinition.Add("FirstName", "nvarchar(100)"); tableDefinition.Add("LastName", "nvarchar(100)"); tableDefinition.Add("Company", "nvarchar(100)"); tableDefinition.Add("StreetAddress", "nvarchar(100)"); tableDefinition.Add("StreetAddress2", "nvarchar(100)"); tableDefinition.Add("ZipPostalCode", "nvarchar(100)"); tableDefinition.Add("City", "nvarchar(100)"); tableDefinition.Add("PhoneNumber", "nvarchar(100)"); tableDefinition.Add("FaxNumber", "nvarchar(100)"); tableDefinition.Add("CountryID", "int"); tableDefinition.Add("StateProvinceID", "int"); tableDefinition.Add("ReceiveNewsletter", "nvarchar(5)"); excelHelper.WriteTable("Customers", tableDefinition); foreach (Customer customer in customers) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [Customers] (CustomerID, CustomerGUID, Email, Username, PasswordHash, SaltKey, AffiliateID, BillingAddressID, ShippingAddressID, LastPaymentMethodID, LastAppliedCouponCode, LanguageID, CurrencyID, TaxDisplayTypeID, IsTaxExempt, IsAdmin, IsGuest, IsForumModerator, TotalForumPosts, Signature, AdminComment, Active, Deleted, RegistrationDate, TimeZoneID, AvatarID, Gender, FirstName, LastName, Company, StreetAddress, StreetAddress2, ZipPostalCode, City, PhoneNumber, FaxNumber, CountryID, StateProvinceID, ReceiveNewsletter) VALUES ("); sb.Append(customer.CustomerID); sb.Append(","); sb.Append('"'); sb.Append(customer.CustomerGUID); sb.Append("\","); sb.Append('"'); sb.Append(customer.Email.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(customer.Username); sb.Append("\","); sb.Append('"'); sb.Append(customer.PasswordHash.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(customer.SaltKey.Replace('"', '\'')); sb.Append("\","); sb.Append(customer.AffiliateID); sb.Append(","); sb.Append(customer.BillingAddressID); sb.Append(","); sb.Append(customer.ShippingAddressID); sb.Append(","); sb.Append(customer.LastPaymentMethodID); sb.Append(","); sb.Append('"'); sb.Append(customer.LastAppliedCouponCode.Replace('"', '\'')); sb.Append("\","); sb.Append(customer.LanguageID); sb.Append(","); sb.Append(customer.CurrencyID); sb.Append(","); sb.Append(customer.TaxDisplayTypeID); sb.Append(','); sb.Append('"'); sb.Append(customer.IsTaxExempt); sb.Append("\","); sb.Append('"'); sb.Append(customer.IsAdmin); sb.Append("\","); sb.Append('"'); sb.Append(customer.IsGuest); sb.Append("\","); sb.Append('"'); sb.Append(customer.IsForumModerator); sb.Append("\","); sb.Append(customer.TotalForumPosts); sb.Append(','); sb.Append('"'); sb.Append(customer.Signature.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(customer.AdminComment.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(customer.Active); sb.Append("\","); sb.Append('"'); sb.Append(customer.Deleted); sb.Append("\","); sb.Append('"'); sb.Append(customer.RegistrationDate); sb.Append("\","); sb.Append('"'); sb.Append(customer.TimeZoneID); sb.Append("\","); sb.Append(customer.AvatarID); sb.Append(','); //custom properties sb.Append('"'); sb.Append(customer.Gender); sb.Append("\","); sb.Append('"'); sb.Append(customer.FirstName); sb.Append("\","); sb.Append('"'); sb.Append(customer.LastName); sb.Append("\","); sb.Append('"'); sb.Append(customer.Company); sb.Append("\","); sb.Append('"'); sb.Append(customer.StreetAddress); sb.Append("\","); sb.Append('"'); sb.Append(customer.StreetAddress2); sb.Append("\","); sb.Append('"'); sb.Append(customer.ZipPostalCode); sb.Append("\","); sb.Append('"'); sb.Append(customer.City); sb.Append("\","); sb.Append('"'); sb.Append(customer.PhoneNumber); sb.Append("\","); sb.Append('"'); sb.Append(customer.FaxNumber); sb.Append("\","); sb.Append(customer.CountryID); sb.Append(','); sb.Append(customer.StateProvinceID); sb.Append(','); sb.Append('"'); sb.Append(customer.ReceiveNewsletter); sb.Append("\""); sb.Append(")"); excelHelper.ExecuteCommand(sb.ToString()); } } }
/// <summary> /// Export customer list to XLS /// </summary> /// <param name="filePath">File path to use</param> /// <param name="customers">Customers</param> public void ExportCustomersToXls(string filePath, List<Customer> customers) { using (ExcelHelper excelHelper = new ExcelHelper(filePath)) { excelHelper.Hdr = "YES"; excelHelper.Imex = "0"; Dictionary<string, string> tableDefinition = new Dictionary<string,string>(); tableDefinition.Add("CustomerId", "int"); tableDefinition.Add("CustomerGuid", "uniqueidentifier"); tableDefinition.Add("Email", "nvarchar(255)"); tableDefinition.Add("Username", "nvarchar(255)"); tableDefinition.Add("PasswordHash", "nvarchar(255)"); tableDefinition.Add("SaltKey", "nvarchar(255)"); tableDefinition.Add("AffiliateId", "int"); tableDefinition.Add("LanguageId", "int"); tableDefinition.Add("CurrencyId", "int"); tableDefinition.Add("TaxDisplayTypeId", "int"); tableDefinition.Add("IsTaxExempt", "nvarchar(5)"); tableDefinition.Add("IsAdmin", "nvarchar(5)"); tableDefinition.Add("IsGuest", "nvarchar(5)"); tableDefinition.Add("IsForumModerator", "nvarchar(5)"); tableDefinition.Add("TotalForumPosts", "int"); tableDefinition.Add("Signature", "nvarchar(255)"); tableDefinition.Add("AdminComment", "nvarchar(255)"); tableDefinition.Add("Active", "nvarchar(5)"); tableDefinition.Add("Deleted", "nvarchar(5)"); tableDefinition.Add("RegistrationDate", "decimal"); tableDefinition.Add("TimeZoneId", "nvarchar(200)"); tableDefinition.Add("AvatarId", "int"); tableDefinition.Add("Gender", "nvarchar(100)"); tableDefinition.Add("FirstName", "nvarchar(100)"); tableDefinition.Add("LastName", "nvarchar(100)"); tableDefinition.Add("Company", "nvarchar(100)"); tableDefinition.Add("VatNumber", "nvarchar(100)"); tableDefinition.Add("VatNumberStatus", "int"); tableDefinition.Add("StreetAddress", "nvarchar(100)"); tableDefinition.Add("StreetAddress2", "nvarchar(100)"); tableDefinition.Add("ZipPostalCode", "nvarchar(100)"); tableDefinition.Add("City", "nvarchar(100)"); tableDefinition.Add("PhoneNumber", "nvarchar(100)"); tableDefinition.Add("FaxNumber", "nvarchar(100)"); tableDefinition.Add("CountryId", "int"); tableDefinition.Add("StateProvinceId", "int"); tableDefinition.Add("ReceiveNewsletter", "nvarchar(5)"); excelHelper.WriteTable("Customers", tableDefinition); string decimalQuoter = (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator.Equals(",") ? "\"" : String.Empty); foreach (var customer in customers) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [Customers] (CustomerId, CustomerGuid, Email, Username, PasswordHash, SaltKey, AffiliateId, LanguageId, CurrencyId, TaxDisplayTypeId, IsTaxExempt, IsAdmin, IsGuest, IsForumModerator, TotalForumPosts, Signature, AdminComment, Active, Deleted, RegistrationDate, TimeZoneId, AvatarId, Gender, FirstName, LastName, Company, VatNumber, VatNumberStatus, StreetAddress, StreetAddress2, ZipPostalCode, City, PhoneNumber, FaxNumber, CountryId, StateProvinceId, ReceiveNewsletter) VALUES ("); sb.Append(customer.CustomerId); sb.Append(","); sb.Append('"'); sb.Append(customer.CustomerGuid); sb.Append("\","); sb.Append('"'); sb.Append(customer.Email.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(customer.Username); sb.Append("\","); sb.Append('"'); sb.Append(customer.PasswordHash.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(customer.SaltKey.Replace('"', '\'')); sb.Append("\","); sb.Append(customer.AffiliateId); sb.Append(","); sb.Append(customer.LanguageId); sb.Append(","); sb.Append(customer.CurrencyId); sb.Append(","); sb.Append(customer.TaxDisplayTypeId); sb.Append(','); sb.Append('"'); sb.Append(customer.IsTaxExempt); sb.Append("\","); sb.Append('"'); sb.Append(customer.IsAdmin); sb.Append("\","); sb.Append('"'); sb.Append(customer.IsGuest); sb.Append("\","); sb.Append('"'); sb.Append(customer.IsForumModerator); sb.Append("\","); sb.Append(customer.TotalForumPosts); sb.Append(','); sb.Append('"'); sb.Append(customer.Signature.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(customer.AdminComment.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(customer.Active); sb.Append("\","); sb.Append('"'); sb.Append(customer.Deleted); sb.Append("\","); sb.Append(decimalQuoter); sb.Append(customer.RegistrationDate.ToOADate()); sb.Append(decimalQuoter); sb.Append(","); sb.Append('"'); sb.Append(customer.TimeZoneId); sb.Append("\","); sb.Append(customer.AvatarId); sb.Append(','); //custom properties sb.Append('"'); sb.Append(customer.Gender); sb.Append("\","); sb.Append('"'); sb.Append(customer.FirstName); sb.Append("\","); sb.Append('"'); sb.Append(customer.LastName); sb.Append("\","); sb.Append('"'); sb.Append(customer.Company); sb.Append("\","); sb.Append('"'); sb.Append(customer.VatNumber); sb.Append("\","); sb.Append(((int)customer.VatNumberStatus).ToString()); sb.Append(','); sb.Append('"'); sb.Append(customer.StreetAddress); sb.Append("\","); sb.Append('"'); sb.Append(customer.StreetAddress2); sb.Append("\","); sb.Append('"'); sb.Append(customer.ZipPostalCode); sb.Append("\","); sb.Append('"'); sb.Append(customer.City); sb.Append("\","); sb.Append('"'); sb.Append(customer.PhoneNumber); sb.Append("\","); sb.Append('"'); sb.Append(customer.FaxNumber); sb.Append("\","); sb.Append(customer.CountryId); sb.Append(','); sb.Append(customer.StateProvinceId); sb.Append(','); sb.Append('"'); sb.Append(customer.ReceiveNewsletter); sb.Append("\""); sb.Append(")"); excelHelper.ExecuteCommand(sb.ToString()); } } }
/// <summary> /// Export products to XLS /// </summary> /// <param name="filePath">File path to use</param> /// <param name="products">Products</param> public void ExportProductsToXls(string filePath, List<Product> products) { using (ExcelHelper excelHelper = new ExcelHelper(filePath)) { excelHelper.Hdr = "YES"; excelHelper.Imex = "0"; Dictionary<string, string> tableDefinition = new Dictionary<string, string>(); tableDefinition.Add("Name", "ntext"); tableDefinition.Add("ShortDescription", "ntext"); tableDefinition.Add("FullDescription", "ntext"); tableDefinition.Add("TemplateId", "int"); tableDefinition.Add("ShowOnHomePage", "nvarchar(5)"); tableDefinition.Add("MetaKeywords", "ntext"); tableDefinition.Add("MetaDescription", "ntext"); tableDefinition.Add("MetaTitle", "ntext"); tableDefinition.Add("AllowCustomerReviews", "nvarchar(5)"); tableDefinition.Add("AllowCustomerRatings", "nvarchar(5)"); tableDefinition.Add("Published", "nvarchar(5)"); tableDefinition.Add("SKU", "ntext"); tableDefinition.Add("ManufacturerPartNumber", "ntext"); tableDefinition.Add("IsGiftCard", "nvarchar(5)"); tableDefinition.Add("GiftCardType", "int"); tableDefinition.Add("IsDownload", "nvarchar(5)"); tableDefinition.Add("DownloadId", "int"); tableDefinition.Add("UnlimitedDownloads", "nvarchar(5)"); tableDefinition.Add("MaxNumberOfDownloads", "int"); tableDefinition.Add("DownloadActivationType", "int"); tableDefinition.Add("HasSampleDownload", "nvarchar(5)"); tableDefinition.Add("SampleDownloadId", "int"); tableDefinition.Add("HasUserAgreement", "nvarchar(5)"); tableDefinition.Add("UserAgreementText", "ntext"); tableDefinition.Add("IsRecurring", "nvarchar(5)"); tableDefinition.Add("CycleLength", "int"); tableDefinition.Add("CyclePeriod", "int"); tableDefinition.Add("TotalCycles", "int"); tableDefinition.Add("IsShipEnabled", "nvarchar(5)"); tableDefinition.Add("IsFreeShipping", "nvarchar(5)"); tableDefinition.Add("AdditionalShippingCharge", "decimal"); tableDefinition.Add("IsTaxExempt", "nvarchar(5)"); tableDefinition.Add("TaxCategoryId", "int"); tableDefinition.Add("ManageInventory", "int"); tableDefinition.Add("StockQuantity", "int"); tableDefinition.Add("DisplayStockAvailability", "nvarchar(5)"); tableDefinition.Add("DisplayStockQuantity", "nvarchar(5)"); tableDefinition.Add("MinStockQuantity", "int"); tableDefinition.Add("LowStockActivityId", "int"); tableDefinition.Add("NotifyAdminForQuantityBelow", "int"); tableDefinition.Add("Backorders", "int"); tableDefinition.Add("OrderMinimumQuantity", "int"); tableDefinition.Add("OrderMaximumQuantity", "int"); tableDefinition.Add("DisableBuyButton", "nvarchar(5)"); tableDefinition.Add("CallForPrice", "nvarchar(5)"); tableDefinition.Add("Price", "decimal"); tableDefinition.Add("OldPrice", "decimal"); tableDefinition.Add("ProductCost", "decimal"); tableDefinition.Add("CustomerEntersPrice", "nvarchar(5)"); tableDefinition.Add("MinimumCustomerEnteredPrice", "decimal"); tableDefinition.Add("MaximumCustomerEnteredPrice", "decimal"); tableDefinition.Add("Weight", "decimal"); tableDefinition.Add("Length", "decimal"); tableDefinition.Add("Width", "decimal"); tableDefinition.Add("Height", "decimal"); tableDefinition.Add("CreatedOn", "decimal"); excelHelper.WriteTable("Products", tableDefinition); string decimalQuoter = (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator.Equals(",") ? "\"" : String.Empty); foreach (var p in products) { var productVariants = IoC.Resolve<IProductService>().GetProductVariantsByProductId(p.ProductId, true); foreach (var pv in productVariants) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [Products] (Name, ShortDescription,FullDescription,TemplateId,ShowOnHomePage,MetaKeywords,MetaDescription,MetaTitle,AllowCustomerReviews,AllowCustomerRatings,Published,SKU,ManufacturerPartNumber,IsGiftCard,GiftCardType,IsDownload,DownloadId,UnlimitedDownloads,MaxNumberOfDownloads,DownloadActivationType,HasSampleDownload,SampleDownloadId,HasUserAgreement,UserAgreementText,IsRecurring,CycleLength,CyclePeriod,TotalCycles,IsShipEnabled,IsFreeShipping,AdditionalShippingCharge,IsTaxExempt,TaxCategoryId,ManageInventory,StockQuantity,DisplayStockAvailability,DisplayStockQuantity,MinStockQuantity,LowStockActivityId,NotifyAdminForQuantityBelow,Backorders,OrderMinimumQuantity,OrderMaximumQuantity,DisableBuyButton,CallForPrice,Price,OldPrice,ProductCost,CustomerEntersPrice,MinimumCustomerEnteredPrice,MaximumCustomerEnteredPrice,Weight, Length, Width, Height, CreatedOn) VALUES ("); sb.Append('"'); sb.Append(p.Name.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(p.ShortDescription.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(p.FullDescription.Replace('"', '\'')); sb.Append("\","); sb.Append(p.TemplateId); sb.Append(","); sb.Append('"'); sb.Append(p.ShowOnHomePage); sb.Append("\","); sb.Append('"'); sb.Append(p.MetaKeywords.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(p.MetaDescription.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(p.MetaTitle.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(p.AllowCustomerReviews); sb.Append("\","); sb.Append('"'); sb.Append(p.AllowCustomerRatings); sb.Append("\","); sb.Append('"'); sb.Append(p.Published); sb.Append("\","); sb.Append('"'); sb.Append(pv.SKU.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(pv.ManufacturerPartNumber.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(pv.IsGiftCard); sb.Append("\","); sb.Append('"'); sb.Append(pv.GiftCardType); sb.Append("\","); sb.Append('"'); sb.Append(pv.IsDownload); sb.Append("\","); sb.Append(pv.DownloadId); sb.Append(","); sb.Append('"'); sb.Append(pv.UnlimitedDownloads); sb.Append("\","); sb.Append(pv.MaxNumberOfDownloads); sb.Append(","); sb.Append(pv.DownloadActivationType); sb.Append(","); sb.Append('"'); sb.Append(pv.HasSampleDownload); sb.Append("\","); sb.Append(pv.SampleDownloadId); sb.Append(","); sb.Append('"'); sb.Append(pv.HasUserAgreement); sb.Append("\","); sb.Append('"'); sb.Append(pv.UserAgreementText.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(pv.IsRecurring); sb.Append("\","); sb.Append(pv.CycleLength); sb.Append(","); sb.Append(pv.CyclePeriod); sb.Append(","); sb.Append(pv.TotalCycles); sb.Append(","); sb.Append('"'); sb.Append(pv.IsShipEnabled); sb.Append("\","); sb.Append('"'); sb.Append(pv.IsFreeShipping); sb.Append("\","); sb.Append(decimalQuoter); sb.Append(pv.AdditionalShippingCharge); sb.Append(decimalQuoter); sb.Append(',');//decimal sb.Append('"'); sb.Append(pv.IsTaxExempt); sb.Append("\","); sb.Append(pv.TaxCategoryId); sb.Append(","); sb.Append(pv.ManageInventory); sb.Append(","); sb.Append(pv.StockQuantity); sb.Append(","); sb.Append('"'); sb.Append(pv.DisplayStockAvailability); sb.Append("\","); sb.Append('"'); sb.Append(pv.DisplayStockQuantity); sb.Append("\","); sb.Append(pv.MinStockQuantity); sb.Append(","); sb.Append(pv.LowStockActivityId); sb.Append(","); sb.Append(pv.NotifyAdminForQuantityBelow); sb.Append(","); sb.Append(pv.Backorders); sb.Append(","); sb.Append(pv.OrderMinimumQuantity); sb.Append(","); sb.Append(pv.OrderMaximumQuantity); sb.Append(","); sb.Append('"'); sb.Append(pv.DisableBuyButton); sb.Append("\","); sb.Append('"'); sb.Append(pv.CallForPrice); sb.Append("\","); sb.Append(decimalQuoter); sb.Append(pv.Price); sb.Append(decimalQuoter); sb.Append(',');//decimal sb.Append(decimalQuoter); sb.Append(pv.OldPrice); sb.Append(decimalQuoter); sb.Append(',');//decimal sb.Append(decimalQuoter); sb.Append(pv.ProductCost); sb.Append(decimalQuoter); sb.Append(',');//decimal sb.Append('"'); sb.Append(pv.CustomerEntersPrice); sb.Append("\","); sb.Append(decimalQuoter); sb.Append(pv.MinimumCustomerEnteredPrice); sb.Append(decimalQuoter); sb.Append(',');//decimal sb.Append(decimalQuoter); sb.Append(pv.MaximumCustomerEnteredPrice); sb.Append(decimalQuoter); sb.Append(',');//decimal sb.Append(decimalQuoter); sb.Append(pv.Weight); sb.Append(decimalQuoter); sb.Append(',');//decimal sb.Append(decimalQuoter); sb.Append(pv.Length); sb.Append(decimalQuoter); sb.Append(',');//decimal sb.Append(decimalQuoter); sb.Append(pv.Width); sb.Append(decimalQuoter); sb.Append(',');//decimal sb.Append(decimalQuoter); sb.Append(pv.Height); sb.Append(decimalQuoter); sb.Append(',');//decimal sb.Append(decimalQuoter); sb.Append(pv.CreatedOn.ToOADate()); sb.Append(decimalQuoter); sb.Append(")"); excelHelper.ExecuteCommand(sb.ToString()); } } } }
/// <summary> /// Export orders to XLS /// </summary> /// <param name="filePath">File path to use</param> /// <param name="orders">Orders</param> public void ExportOrdersToXls(string filePath, List<Order> orders) { using (ExcelHelper excelHelper = new ExcelHelper(filePath)) { excelHelper.Hdr = "YES"; excelHelper.Imex = "0"; Dictionary<string, string> tableDefinition = new Dictionary<string, string>(); tableDefinition.Add("OrderId", "int"); tableDefinition.Add("OrderGuid", "uniqueidentifier"); tableDefinition.Add("CustomerId", "int"); tableDefinition.Add("OrderSubtotalInclTax", "decimal"); tableDefinition.Add("OrderSubtotalExclTax", "decimal"); tableDefinition.Add("OrderSubTotalDiscountInclTax", "decimal"); tableDefinition.Add("OrderSubTotalDiscountExclTax", "decimal"); tableDefinition.Add("OrderShippingInclTax", "decimal"); tableDefinition.Add("OrderShippingExclTax", "decimal"); tableDefinition.Add("PaymentMethodAdditionalFeeInclTax", "decimal"); tableDefinition.Add("PaymentMethodAdditionalFeeExclTax", "decimal"); tableDefinition.Add("TaxRates", "nvarchar(255)"); tableDefinition.Add("OrderTax", "decimal"); tableDefinition.Add("OrderTotal", "decimal"); tableDefinition.Add("RefundedAmount", "decimal"); tableDefinition.Add("OrderDiscount", "decimal"); tableDefinition.Add("OrderSubtotalInclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderSubtotalExclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderSubTotalDiscountInclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderSubTotalDiscountExclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderShippingInclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderShippingExclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("PaymentMethodAdditionalFeeInclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("PaymentMethodAdditionalFeeExclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("TaxRatesInCustomerCurrency", "nvarchar(255)"); tableDefinition.Add("OrderTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderTotalInCustomerCurrency", "decimal"); tableDefinition.Add("OrderDiscountInCustomerCurrency", "decimal"); tableDefinition.Add("CustomerCurrencyCode", "nvarchar(5)"); tableDefinition.Add("OrderWeight", "decimal"); tableDefinition.Add("AffiliateId", "int"); tableDefinition.Add("OrderStatusId", "int"); tableDefinition.Add("PaymentMethodId", "int"); tableDefinition.Add("PaymentMethodName", "nvarchar(100)"); tableDefinition.Add("PurchaseOrderNumber", "nvarchar(100)"); tableDefinition.Add("PaymentStatusId", "int"); tableDefinition.Add("BillingFirstName", "nvarchar(100)"); tableDefinition.Add("BillingLastName", "nvarchar(100)"); tableDefinition.Add("BillingPhoneNumber", "nvarchar(50)"); tableDefinition.Add("BillingEmail", "nvarchar(255)"); tableDefinition.Add("BillingFaxNumber", "nvarchar(50)"); tableDefinition.Add("BillingCompany", "nvarchar(100)"); tableDefinition.Add("BillingAddress1", "nvarchar(100)"); tableDefinition.Add("BillingAddress2", "nvarchar(100)"); tableDefinition.Add("BillingCity", "nvarchar(100)"); tableDefinition.Add("BillingStateProvince", "nvarchar(100)"); tableDefinition.Add("BillingZipPostalCode", "nvarchar(100)"); tableDefinition.Add("BillingCountry", "nvarchar(100)"); tableDefinition.Add("ShippingStatusId", "int"); tableDefinition.Add("ShippingFirstName", "nvarchar(100)"); tableDefinition.Add("ShippingLastName", "nvarchar(100)"); tableDefinition.Add("ShippingPhoneNumber", "nvarchar(50)"); tableDefinition.Add("ShippingEmail", "nvarchar(255)"); tableDefinition.Add("ShippingFaxNumber", "nvarchar(50)"); tableDefinition.Add("ShippingCompany", "nvarchar(100)"); tableDefinition.Add("ShippingAddress1", "nvarchar(100)"); tableDefinition.Add("ShippingAddress2", "nvarchar(100)"); tableDefinition.Add("ShippingCity", "nvarchar(100)"); tableDefinition.Add("ShippingStateProvince", "nvarchar(100)"); tableDefinition.Add("ShippingZipPostalCode", "nvarchar(100)"); tableDefinition.Add("ShippingCountry", "nvarchar(100)"); tableDefinition.Add("ShippingMethod", "nvarchar(100)"); tableDefinition.Add("ShippingRateComputationMethodId", "int"); tableDefinition.Add("VatNumber", "nvarchar(100)"); tableDefinition.Add("CreatedOn", "decimal"); excelHelper.WriteTable("Orders", tableDefinition); string decimalQuoter = (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator.Equals(",") ? "\"" : String.Empty); foreach (var order in orders) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [Orders] (OrderId, OrderGuid, CustomerId, OrderSubtotalInclTax, OrderSubtotalExclTax, OrderSubTotalDiscountInclTax, OrderSubTotalDiscountExclTax, OrderShippingInclTax, OrderShippingExclTax, PaymentMethodAdditionalFeeInclTax, PaymentMethodAdditionalFeeExclTax, TaxRates, OrderTax, OrderTotal, RefundedAmount, OrderDiscount, OrderSubtotalInclTaxInCustomerCurrency, OrderSubtotalExclTaxInCustomerCurrency, OrderSubTotalDiscountInclTaxInCustomerCurrency, OrderSubTotalDiscountExclTaxInCustomerCurrency, OrderShippingInclTaxInCustomerCurrency, OrderShippingExclTaxInCustomerCurrency, PaymentMethodAdditionalFeeInclTaxInCustomerCurrency, PaymentMethodAdditionalFeeExclTaxInCustomerCurrency, TaxRatesInCustomerCurrency, OrderTaxInCustomerCurrency, OrderTotalInCustomerCurrency, OrderDiscountInCustomerCurrency, CustomerCurrencyCode, OrderWeight, AffiliateId, OrderStatusId, PaymentMethodId, PaymentMethodName, PurchaseOrderNumber, PaymentStatusId, BillingFirstName, BillingLastName, BillingPhoneNumber, BillingEmail, BillingFaxNumber, BillingCompany, BillingAddress1, BillingAddress2, BillingCity, BillingStateProvince, BillingZipPostalCode, BillingCountry, ShippingStatusId, ShippingFirstName, ShippingLastName, ShippingPhoneNumber, ShippingEmail, ShippingFaxNumber, ShippingCompany, ShippingAddress1, ShippingAddress2, ShippingCity, ShippingStateProvince, ShippingZipPostalCode, ShippingCountry, ShippingMethod, ShippingRateComputationMethodId, VatNumber, CreatedOn) VALUES ("); sb.Append(order.OrderId); sb.Append(","); sb.Append('"'); sb.Append(order.OrderGuid); sb.Append("\","); sb.Append(order.CustomerId); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderSubtotalInclTax); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderSubtotalExclTax); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderSubTotalDiscountInclTax); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderSubTotalDiscountExclTax); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderShippingInclTax); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderShippingExclTax); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.PaymentMethodAdditionalFeeInclTax); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.PaymentMethodAdditionalFeeExclTax); sb.Append(decimalQuoter); sb.Append(","); sb.Append('"'); sb.Append(order.TaxRates.Replace('"', '\'')); sb.Append("\","); sb.Append(decimalQuoter); sb.Append(order.OrderTax); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderTotal); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.RefundedAmount); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderDiscount); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderSubtotalInclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderSubtotalExclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderSubTotalDiscountInclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderSubTotalDiscountExclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderShippingInclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderShippingExclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.PaymentMethodAdditionalFeeInclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.PaymentMethodAdditionalFeeExclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append('"'); sb.Append(order.TaxRatesInCustomerCurrency.Replace('"', '\'')); sb.Append("\","); sb.Append(decimalQuoter); sb.Append(order.OrderTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderTotalInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append(decimalQuoter); sb.Append(order.OrderDiscountInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(","); sb.Append('"'); sb.Append(order.CustomerCurrencyCode.Replace('"', '\'')); sb.Append("\","); sb.Append(decimalQuoter); sb.Append(order.OrderWeight); sb.Append(decimalQuoter); sb.Append(","); sb.Append(order.AffiliateId); sb.Append(","); sb.Append(order.OrderStatusId); sb.Append(","); sb.Append(order.PaymentMethodId); sb.Append(","); sb.Append('"'); sb.Append(order.PaymentMethodName.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.PurchaseOrderNumber.Replace('"', '\'')); sb.Append("\","); sb.Append(order.PaymentStatusId); sb.Append(","); sb.Append('"'); sb.Append(order.BillingFirstName.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingLastName.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingPhoneNumber.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingEmail.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingFaxNumber.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingCompany.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingAddress1.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingAddress2.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingCity.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingStateProvince.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingZipPostalCode.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingCountry.Replace('"', '\'')); sb.Append("\","); sb.Append(order.ShippingStatusId); sb.Append(","); sb.Append('"'); sb.Append(order.ShippingFirstName.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingLastName.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingPhoneNumber.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingEmail.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingFaxNumber.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingCompany.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingAddress1.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingAddress2.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingCity.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingStateProvince.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingZipPostalCode.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingCountry.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingMethod.Replace('"', '\'')); sb.Append("\","); sb.Append(order.ShippingRateComputationMethodId); sb.Append(","); sb.Append('"'); sb.Append(order.VatNumber.Replace('"', '\'')); sb.Append("\","); sb.Append(decimalQuoter); sb.Append(order.CreatedOn.ToOADate()); sb.Append(decimalQuoter); sb.Append(")"); excelHelper.ExecuteCommand(sb.ToString()); } } }
public void ExportInvoiceToXls(string filePath, List<Invoice> invoices) { using (ExcelHelper excelHelper = new ExcelHelper(filePath)) { excelHelper.Hdr = "YES"; excelHelper.Imex = "0"; Dictionary<string, string> tableDefinition = new Dictionary<string, string>(); tableDefinition.Add("InvoiceID", "int"); tableDefinition.Add("Vendor", "string"); tableDefinition.Add("Amount", "decimal"); tableDefinition.Add("InvoiceDate", "DateTime"); excelHelper.WriteTable("Invoice", tableDefinition); foreach (var invoice in invoices) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [Invoice] (InvoiceID, Vendor, Amount,InvoiceDate) VALUES ("); sb.Append(invoice.InvoiceID); sb.Append(","); sb.Append('"'); sb.Append(invoice.Vendor.CompanyName); sb.Append('"'); sb.Append(","); sb.Append('"'); sb.Append(invoice.Amount); sb.Append('"'); sb.Append(","); sb.Append('"'); sb.Append(invoice.CreatedOn); sb.Append('"'); sb.Append(")"); excelHelper.ExecuteCommand(sb.ToString()); } } }
/// <summary> /// Import products from XLS file /// </summary> /// <param name="FilePath">Excel file path</param> public static void ImportProductsFromXLS(string FilePath) { using (ExcelHelper excelHelper = new ExcelHelper(FilePath)) { excelHelper.HDR = "YES"; excelHelper.IMEX = "1"; DataTable dt = excelHelper.ReadTable("Products"); foreach (DataRow dr in dt.Rows) { string Name = dr["Name"].ToString(); string ShortDescription = dr["ShortDescription"].ToString(); string FullDescription = dr["FullDescription"].ToString(); int ProductTypeID = Convert.ToInt32(dr["ProductTypeID"]); int TemplateID = Convert.ToInt32(dr["TemplateID"]); bool ShowOnHomePage = Convert.ToBoolean(dr["ShowOnHomePage"]); string MetaKeywords = dr["MetaKeywords"].ToString(); string MetaDescription = dr["MetaDescription"].ToString(); string MetaTitle = dr["MetaTitle"].ToString(); bool AllowCustomerReviews = Convert.ToBoolean(dr["AllowCustomerReviews"]); bool AllowCustomerRatings = Convert.ToBoolean(dr["AllowCustomerRatings"]); bool Published = Convert.ToBoolean(dr["Published"]); string SKU = dr["SKU"].ToString(); string ManufacturerPartNumber = dr["ManufacturerPartNumber"].ToString(); bool IsDownload = Convert.ToBoolean(dr["IsDownload"]); int DownloadID = Convert.ToInt32(dr["DownloadID"]); bool UnlimitedDownloads = Convert.ToBoolean(dr["UnlimitedDownloads"]); int MaxNumberOfDownloads = Convert.ToInt32(dr["MaxNumberOfDownloads"]); bool HasSampleDownload = Convert.ToBoolean(dr["HasSampleDownload"]); int SampleDownloadID = Convert.ToInt32(dr["SampleDownloadID"]); bool IsShipEnabled = Convert.ToBoolean(dr["IsShipEnabled"]); bool IsFreeShipping = Convert.ToBoolean(dr["IsFreeShipping"]); decimal AdditionalShippingCharge = Convert.ToDecimal(dr["AdditionalShippingCharge"]); bool IsTaxExempt = Convert.ToBoolean(dr["IsTaxExempt"]); int TaxCategoryID = Convert.ToInt32(dr["TaxCategoryID"]); bool ManageInventory = Convert.ToBoolean(dr["ManageInventory"]); int StockQuantity = Convert.ToInt32(dr["StockQuantity"]); int MinStockQuantity = Convert.ToInt32(dr["MinStockQuantity"]); int LowStockActivityID = Convert.ToInt32(dr["LowStockActivityID"]); int NotifyAdminForQuantityBelow = Convert.ToInt32(dr["NotifyAdminForQuantityBelow"]); int OrderMinimumQuantity = Convert.ToInt32(dr["OrderMinimumQuantity"]); int OrderMaximumQuantity = Convert.ToInt32(dr["OrderMaximumQuantity"]); bool DisableBuyButton = Convert.ToBoolean(dr["DisableBuyButton"]); decimal Price = Convert.ToDecimal(dr["Price"]); decimal OldPrice = Convert.ToDecimal(dr["OldPrice"]); decimal Weight = Convert.ToDecimal(dr["Weight"]); decimal Length = Convert.ToDecimal(dr["Length"]); decimal Width = Convert.ToDecimal(dr["Width"]); decimal Height = Convert.ToDecimal(dr["Height"]); DateTime CreatedOn = Convert.ToDateTime(dr["CreatedOn"]); Product product = ProductManager.InsertProduct(Name, ShortDescription, FullDescription, string.Empty, ProductTypeID, TemplateID, ShowOnHomePage, MetaKeywords, MetaDescription, MetaTitle, string.Empty, AllowCustomerReviews, AllowCustomerRatings, 0, 0, Published, false, CreatedOn, DateTime.Now); ProductVariant productVariant = ProductManager.InsertProductVariant(product.ProductID, string.Empty, SKU, string.Empty, string.Empty, ManufacturerPartNumber, IsDownload, DownloadID, UnlimitedDownloads, MaxNumberOfDownloads, HasSampleDownload, SampleDownloadID, IsShipEnabled, IsFreeShipping, AdditionalShippingCharge, IsTaxExempt, TaxCategoryID, ManageInventory, StockQuantity, MinStockQuantity, (LowStockActivityEnum)LowStockActivityID, NotifyAdminForQuantityBelow, OrderMinimumQuantity, OrderMaximumQuantity, 0, DisableBuyButton, Price, OldPrice, Weight, Length, Width, Height, 0, null, null, true, false, 1, CreatedOn, DateTime.Now); } } }
/// <summary> /// Import customer list from XLS file /// </summary> /// <param name="filePath">Excel file path</param> public static void ImportCustomersFromXls(string filePath) { using (ExcelHelper excelHelper = new ExcelHelper(filePath)) { excelHelper.Hdr = "YES"; excelHelper.Imex = "1"; DataTable dt = excelHelper.ReadTable("Customers"); foreach (DataRow dr in dt.Rows) { int customerId = Convert.ToInt32(dr["CustomerId"]); Guid customerGuid = new Guid(dr["CustomerGuid"].ToString()); string email = dr["Email"].ToString(); string username = dr["Username"].ToString(); string passwordHash = dr["PasswordHash"].ToString(); string saltKey = dr["SaltKey"].ToString(); int affiliateId = Convert.ToInt32(dr["AffiliateId"]); int billingAddressId = 0; int shippingAddressId = 0; int lastPaymentMethodId = 0; string lastAppliedCouponCode = string.Empty; int languageId = Convert.ToInt32(dr["LanguageId"]); int currencyId = Convert.ToInt32(dr["CurrencyId"]); int taxDisplayTypeId = Convert.ToInt32(dr["TaxDisplayTypeId"]); bool isTaxExempt = Convert.ToBoolean(dr["IsTaxExempt"]); bool isAdmin = Convert.ToBoolean(dr["IsAdmin"]); bool isGuest = Convert.ToBoolean(dr["IsGuest"]); bool isForumModerator = Convert.ToBoolean(dr["IsForumModerator"]); int totalForumPosts = Convert.ToInt32(dr["TotalForumPosts"]); string signature = dr["Signature"].ToString(); string adminComment = dr["AdminComment"].ToString(); bool active = Convert.ToBoolean(dr["Active"]); bool deleted = Convert.ToBoolean(dr["Deleted"]); DateTime registrationDate = DateTime.FromOADate(Convert.ToDouble(dr["RegistrationDate"])); string timeZoneId = dr["TimeZoneId"].ToString(); int avatarId = Convert.ToInt32(dr["AvatarId"]); //custom properties //string gender = dr["Gender"].ToString(); string title = dr["Title"].ToString(); string firstName = dr["FirstName"].ToString(); string lastName = dr["LastName"].ToString(); string company = dr["Company"].ToString(); string vatNumber = dr["VatNumber"].ToString(); VatNumberStatusEnum vatNumberStatus = (VatNumberStatusEnum)Convert.ToInt32(dr["VatNumberStatus"]); string streetAddress = dr["StreetAddress"].ToString(); string streetAddress2 = dr["StreetAddress2"].ToString(); string zipPostalCode = dr["ZipPostalCode"].ToString(); string city = dr["City"].ToString(); string phoneNumber = dr["PhoneNumber"].ToString(); string faxNumber = dr["FaxNumber"].ToString(); int countryId = Convert.ToInt32(dr["CountryId"]); int stateProvinceId = Convert.ToInt32(dr["StateProvinceId"]); bool receiveNewsletter = Convert.ToBoolean(dr["ReceiveNewsletter"]); var customer = CustomerManager.GetCustomerByEmail(email); if (customer == null) { //no customers found customer = CustomerManager.AddCustomerForced(customerGuid, email, username, passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode, string.Empty, string.Empty, languageId, currencyId, (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneId, avatarId, null); } else { if (!customer.IsGuest) { //customer is not a guest customer = CustomerManager.UpdateCustomer(customer.CustomerId, customer.CustomerGuid, email, username, passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode, string.Empty, string.Empty, languageId, currencyId, (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneId, avatarId, customer.DateOfBirth); } else { //customer is a guest customer = CustomerManager.GetCustomerByGuid(customerGuid); if (customer == null) { customer = CustomerManager.AddCustomerForced(customerGuid, email, username, passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode, string.Empty, string.Empty, languageId, currencyId, (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneId, avatarId, null); } else { customer = CustomerManager.UpdateCustomer(customer.CustomerId, customer.CustomerGuid, email, username, passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode, string.Empty, string.Empty, languageId, currencyId, (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneId, avatarId, customer.DateOfBirth); } } } //if (CustomerManager.FormFieldGenderEnabled) // customer.Gender = gender; customer.Title = customer.FirstName = firstName; customer.LastName = lastName; if (CustomerManager.FormFieldCompanyEnabled) customer.Company = company; if (CustomerManager.FormFieldStreetAddressEnabled) customer.StreetAddress = streetAddress; if (CustomerManager.FormFieldStreetAddress2Enabled) customer.StreetAddress2 = streetAddress2; if (CustomerManager.FormFieldPostCodeEnabled) customer.ZipPostalCode = zipPostalCode; if (CustomerManager.FormFieldCityEnabled) customer.City = city; if (CustomerManager.FormFieldPhoneEnabled) customer.PhoneNumber = phoneNumber; if (CustomerManager.FormFieldFaxEnabled) customer.FaxNumber = faxNumber; if (CustomerManager.FormFieldCountryEnabled) customer.CountryId = countryId; //if (CustomerManager.FormFieldStateEnabled) // customer.StateProvinceId = stateProvinceId; if (CustomerManager.FormFieldNewsletterEnabled) customer.ReceiveNewsletter = receiveNewsletter; if (TaxManager.EUVatEnabled) { customer.VatNumber = vatNumber; customer.VatNumberStatus = vatNumberStatus; } } } }
/// <summary> /// Import customer list from XLS file /// </summary> /// <param name="FilePath">Excel file path</param> public static void ImportCustomersFromXLS(string FilePath) { using (ExcelHelper excelHelper = new ExcelHelper(FilePath)) { excelHelper.HDR = "YES"; excelHelper.IMEX = "1"; DataTable dt = excelHelper.ReadTable("Customers"); foreach (DataRow dr in dt.Rows) { int customerID = Convert.ToInt32(dr["CustomerID"]); Guid customerGUID = new Guid(dr["CustomerGUID"].ToString()); string email = dr["Email"].ToString(); string username = dr["Username"].ToString(); string passwordHash = dr["PasswordHash"].ToString(); string saltKey = dr["SaltKey"].ToString(); int affiliateID = Convert.ToInt32(dr["AffiliateID"]); int billingAddressID = Convert.ToInt32(dr["BillingAddressID"]); int shippingAddressID = Convert.ToInt32(dr["ShippingAddressID"]); int lastPaymentMethodID = Convert.ToInt32(dr["LastPaymentMethodID"]); string lastAppliedCouponCode = dr["LastAppliedCouponCode"].ToString(); int languageID = Convert.ToInt32(dr["LanguageID"]); int currencyID = Convert.ToInt32(dr["CurrencyID"]); int taxDisplayTypeID = Convert.ToInt32(dr["TaxDisplayTypeID"]); bool isTaxExempt = Convert.ToBoolean(dr["IsTaxExempt"]); bool isAdmin = Convert.ToBoolean(dr["IsAdmin"]); bool isGuest = Convert.ToBoolean(dr["IsGuest"]); bool isForumModerator = Convert.ToBoolean(dr["IsForumModerator"]); int totalForumPosts = Convert.ToInt32(dr["TotalForumPosts"]); string signature = dr["Signature"].ToString(); string adminComment = dr["AdminComment"].ToString(); bool active = Convert.ToBoolean(dr["Active"]); bool deleted = Convert.ToBoolean(dr["Deleted"]); DateTime registrationDate = Convert.ToDateTime(dr["RegistrationDate"]); string timeZoneID = dr["TimeZoneID"].ToString(); int avatarID = Convert.ToInt32(dr["AvatarID"]); //custom properties string gender = dr["Gender"].ToString(); string firstName = dr["FirstName"].ToString(); string lastName = dr["LastName"].ToString(); string company = dr["Company"].ToString(); string streetAddress = dr["StreetAddress"].ToString(); string streetAddress2 = dr["StreetAddress2"].ToString(); string zipPostalCode = dr["ZipPostalCode"].ToString(); string city = dr["City"].ToString(); string phoneNumber = dr["PhoneNumber"].ToString(); string faxNumber = dr["FaxNumber"].ToString(); int countryID = Convert.ToInt32(dr["CountryID"]); int stateProvinceID = Convert.ToInt32(dr["StateProvinceID"]); bool receiveNewsletter = Convert.ToBoolean(dr["ReceiveNewsletter"]); Customer customer = CustomerManager.GetCustomerByEmail(email); if (customer == null) { //no customers found customer = CustomerManager.AddCustomerForced(customerGUID, email, username, passwordHash, saltKey, affiliateID, billingAddressID, shippingAddressID, lastPaymentMethodID, lastAppliedCouponCode, languageID, currencyID, (TaxDisplayTypeEnum)taxDisplayTypeID, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneID, avatarID); } else { if (!customer.IsGuest) { //customer is not a guest customer = CustomerManager.UpdateCustomer(customer.CustomerID, customer.CustomerGUID, email, username, passwordHash, saltKey, affiliateID, billingAddressID, shippingAddressID, lastPaymentMethodID, lastAppliedCouponCode, languageID, currencyID, (TaxDisplayTypeEnum)taxDisplayTypeID, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneID, avatarID); } else { //customer is a guest customer = CustomerManager.GetCustomerByGUID(customerGUID); if (customer == null) { customer = CustomerManager.AddCustomerForced(customerGUID, email, username, passwordHash, saltKey, affiliateID, billingAddressID, shippingAddressID, lastPaymentMethodID, lastAppliedCouponCode, languageID, currencyID, (TaxDisplayTypeEnum)taxDisplayTypeID, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneID, avatarID); } else { customer = CustomerManager.UpdateCustomer(customer.CustomerID, customer.CustomerGUID, email, username, passwordHash, saltKey, affiliateID, billingAddressID, shippingAddressID, lastPaymentMethodID, lastAppliedCouponCode, languageID, currencyID, (TaxDisplayTypeEnum)taxDisplayTypeID, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneID, avatarID); } } } customer.Gender = gender; customer.FirstName = firstName; customer.LastName = lastName; customer.Company = company; customer.StreetAddress = streetAddress; customer.StreetAddress2 = streetAddress2; customer.ZipPostalCode = zipPostalCode; customer.City = city; customer.PhoneNumber = phoneNumber; customer.FaxNumber = faxNumber; customer.CountryID = countryID; customer.StateProvinceID = stateProvinceID; customer.ReceiveNewsletter = receiveNewsletter; } } }
/// <summary> /// Import products from XLS file /// </summary> /// <param name="filePath">Excel file path</param> public static void ImportProductsFromXls(string filePath) { using (ExcelHelper excelHelper = new ExcelHelper(filePath)) { excelHelper.Hdr = "YES"; excelHelper.Imex = "1"; DataTable dt = excelHelper.ReadTable("Products"); foreach (DataRow dr in dt.Rows) { string Name = dr["Name"].ToString(); string ShortDescription = dr["ShortDescription"].ToString(); string FullDescription = dr["FullDescription"].ToString(); int TemplateId = Convert.ToInt32(dr["TemplateId"]); bool ShowOnHomePage = Convert.ToBoolean(dr["ShowOnHomePage"]); string MetaKeywords = dr["MetaKeywords"].ToString(); string MetaDescription = dr["MetaDescription"].ToString(); string MetaTitle = dr["MetaTitle"].ToString(); bool AllowCustomerReviews = Convert.ToBoolean(dr["AllowCustomerReviews"]); bool AllowCustomerRatings = Convert.ToBoolean(dr["AllowCustomerRatings"]); bool Published = Convert.ToBoolean(dr["Published"]); string SKU = dr["SKU"].ToString(); string ManufacturerPartNumber = dr["ManufacturerPartNumber"].ToString(); bool IsGiftCard = Convert.ToBoolean(dr["IsGiftCard"]); int GiftCardType = Convert.ToInt32(dr["GiftCardType"]); bool IsDownload = Convert.ToBoolean(dr["IsDownload"]); int DownloadId = Convert.ToInt32(dr["DownloadId"]); bool UnlimitedDownloads = Convert.ToBoolean(dr["UnlimitedDownloads"]); int MaxNumberOfDownloads = Convert.ToInt32(dr["MaxNumberOfDownloads"]); bool HasSampleDownload = Convert.ToBoolean(dr["HasSampleDownload"]); int DownloadActivationType = Convert.ToInt32(dr["DownloadActivationType"]); int SampleDownloadId = Convert.ToInt32(dr["SampleDownloadId"]); bool HasUserAgreement = Convert.ToBoolean(dr["HasUserAgreement"]); string UserAgreementText = dr["UserAgreementText"].ToString(); bool IsRecurring = Convert.ToBoolean(dr["IsRecurring"]); int CycleLength = Convert.ToInt32(dr["CycleLength"]); int CyclePeriod = Convert.ToInt32(dr["CyclePeriod"]); int TotalCycles = Convert.ToInt32(dr["TotalCycles"]); bool IsShipEnabled = Convert.ToBoolean(dr["IsShipEnabled"]); bool IsFreeShipping = Convert.ToBoolean(dr["IsFreeShipping"]); decimal AdditionalShippingCharge = Convert.ToDecimal(dr["AdditionalShippingCharge"]); bool IsTaxExempt = Convert.ToBoolean(dr["IsTaxExempt"]); int TaxCategoryId = Convert.ToInt32(dr["TaxCategoryId"]); int ManageInventory = Convert.ToInt32(dr["ManageInventory"]); int StockQuantity = Convert.ToInt32(dr["StockQuantity"]); bool DisplayStockAvailability = Convert.ToBoolean(dr["DisplayStockAvailability"]); bool DisplayStockQuantity = Convert.ToBoolean(dr["DisplayStockQuantity"]); int MinStockQuantity = Convert.ToInt32(dr["MinStockQuantity"]); int LowStockActivityId = Convert.ToInt32(dr["LowStockActivityId"]); int NotifyAdminForQuantityBelow = Convert.ToInt32(dr["NotifyAdminForQuantityBelow"]); int Backorders = Convert.ToInt32(dr["Backorders"]); int OrderMinimumQuantity = Convert.ToInt32(dr["OrderMinimumQuantity"]); int OrderMaximumQuantity = Convert.ToInt32(dr["OrderMaximumQuantity"]); bool DisableBuyButton = Convert.ToBoolean(dr["DisableBuyButton"]); bool CallForPrice = Convert.ToBoolean(dr["CallForPrice"]); decimal Price = Convert.ToDecimal(dr["Price"]); decimal OldPrice = Convert.ToDecimal(dr["OldPrice"]); decimal ProductCost = Convert.ToDecimal(dr["ProductCost"]); bool CustomerEntersPrice = Convert.ToBoolean(dr["CustomerEntersPrice"]); decimal MinimumCustomerEnteredPrice = Convert.ToDecimal(dr["MinimumCustomerEnteredPrice"]); decimal MaximumCustomerEnteredPrice = Convert.ToDecimal(dr["MaximumCustomerEnteredPrice"]); decimal Weight = Convert.ToDecimal(dr["Weight"]); decimal Length = Convert.ToDecimal(dr["Length"]); decimal Width = Convert.ToDecimal(dr["Width"]); decimal Height = Convert.ToDecimal(dr["Height"]); DateTime CreatedOn = DateTime.FromOADate(Convert.ToDouble(dr["CreatedOn"])); var productVariant = ProductManager.GetProductVariantBySKU(SKU); if (productVariant != null) { var product = ProductManager.GetProductById(productVariant.ProductId); product = ProductManager.UpdateProduct(product.ProductId, Name, ShortDescription, FullDescription, product.AdminComment, TemplateId, ShowOnHomePage, MetaKeywords, MetaDescription, MetaTitle, product.SEName, AllowCustomerReviews, AllowCustomerRatings, product.RatingSum, product.TotalRatingVotes, Published, product.Deleted, CreatedOn, DateTime.UtcNow); productVariant = ProductManager.UpdateProductVariant(productVariant.ProductVariantId, productVariant.ProductId, productVariant.Name, SKU, productVariant.Description, productVariant.AdminComment, ManufacturerPartNumber, IsGiftCard, GiftCardType, IsDownload, DownloadId, UnlimitedDownloads, MaxNumberOfDownloads, productVariant.DownloadExpirationDays, (DownloadActivationTypeEnum)DownloadActivationType, HasSampleDownload, SampleDownloadId, HasUserAgreement, UserAgreementText, IsRecurring, CycleLength, CyclePeriod, TotalCycles, IsShipEnabled, IsFreeShipping, AdditionalShippingCharge, IsTaxExempt, TaxCategoryId, ManageInventory, StockQuantity, DisplayStockAvailability, DisplayStockQuantity, MinStockQuantity, (LowStockActivityEnum)LowStockActivityId, NotifyAdminForQuantityBelow, Backorders, OrderMinimumQuantity, OrderMaximumQuantity, productVariant.WarehouseId, DisableBuyButton, CallForPrice, Price, OldPrice, ProductCost, CustomerEntersPrice, MinimumCustomerEnteredPrice, MaximumCustomerEnteredPrice, Weight, Length, Width, Height, productVariant.PictureId, productVariant.AvailableStartDateTime, productVariant.AvailableEndDateTime, productVariant.Published, productVariant.Deleted, productVariant.DisplayOrder, CreatedOn, DateTime.UtcNow); } else { var product = ProductManager.InsertProduct(Name, ShortDescription, FullDescription, string.Empty, TemplateId, ShowOnHomePage, MetaKeywords, MetaDescription, MetaTitle, string.Empty, AllowCustomerReviews, AllowCustomerRatings, 0, 0, Published, false, CreatedOn, DateTime.UtcNow); productVariant = ProductManager.InsertProductVariant(product.ProductId, string.Empty, SKU, string.Empty, string.Empty, ManufacturerPartNumber, IsGiftCard, GiftCardType, IsDownload, DownloadId, UnlimitedDownloads, MaxNumberOfDownloads, null, (DownloadActivationTypeEnum)DownloadActivationType, HasSampleDownload, SampleDownloadId, HasUserAgreement, UserAgreementText, IsRecurring, CycleLength, CyclePeriod, TotalCycles, IsShipEnabled, IsFreeShipping, AdditionalShippingCharge, IsTaxExempt, TaxCategoryId, ManageInventory, StockQuantity, DisplayStockAvailability, DisplayStockQuantity, MinStockQuantity, (LowStockActivityEnum)LowStockActivityId, NotifyAdminForQuantityBelow, Backorders, OrderMinimumQuantity, OrderMaximumQuantity, 0, DisableBuyButton, CallForPrice, Price, OldPrice, ProductCost, CustomerEntersPrice, MinimumCustomerEnteredPrice, MaximumCustomerEnteredPrice, Weight, Length, Width, Height, 0, null, null, true, false, 1, CreatedOn, DateTime.UtcNow); } } } }
/// <summary> /// Import customer list from XLS file /// </summary> /// <param name="filePath">Excel file path</param> public static void ImportCustomersFromXls(string filePath) { using (ExcelHelper excelHelper = new ExcelHelper(filePath)) { excelHelper.Hdr = "YES"; excelHelper.Imex = "1"; DataTable dt = excelHelper.ReadTable("Customers"); foreach (DataRow dr in dt.Rows) { int customerId = Convert.ToInt32(dr["CustomerId"]); Guid customerGuid = new Guid(dr["CustomerGuid"].ToString()); string email = dr["Email"].ToString(); string username = dr["Username"].ToString(); string passwordHash = dr["PasswordHash"].ToString(); string saltKey = dr["SaltKey"].ToString(); int affiliateId = Convert.ToInt32(dr["AffiliateId"]); int billingAddressId = 0; int shippingAddressId = 0; int lastPaymentMethodId = 0; string lastAppliedCouponCode = string.Empty; int languageId = Convert.ToInt32(dr["LanguageId"]); int currencyId = Convert.ToInt32(dr["CurrencyId"]); int taxDisplayTypeId = Convert.ToInt32(dr["TaxDisplayTypeId"]); bool isTaxExempt = Convert.ToBoolean(dr["IsTaxExempt"]); bool isAdmin = Convert.ToBoolean(dr["IsAdmin"]); bool isGuest = Convert.ToBoolean(dr["IsGuest"]); bool isForumModerator = Convert.ToBoolean(dr["IsForumModerator"]); int totalForumPosts = Convert.ToInt32(dr["TotalForumPosts"]); string signature = dr["Signature"].ToString(); string adminComment = dr["AdminComment"].ToString(); bool active = Convert.ToBoolean(dr["Active"]); bool deleted = Convert.ToBoolean(dr["Deleted"]); DateTime registrationDate = DateTime.FromOADate(Convert.ToDouble(dr["RegistrationDate"])); string timeZoneId = dr["TimeZoneId"].ToString(); int avatarId = Convert.ToInt32(dr["AvatarId"]); //custom properties string gender = dr["Gender"].ToString(); string firstName = dr["FirstName"].ToString(); string lastName = dr["LastName"].ToString(); string company = dr["Company"].ToString(); string vatNumber = dr["VatNumber"].ToString(); VatNumberStatusEnum vatNumberStatus = (VatNumberStatusEnum)Convert.ToInt32(dr["VatNumberStatus"]); string streetAddress = dr["StreetAddress"].ToString(); string streetAddress2 = dr["StreetAddress2"].ToString(); string zipPostalCode = dr["ZipPostalCode"].ToString(); string city = dr["City"].ToString(); string phoneNumber = dr["PhoneNumber"].ToString(); string faxNumber = dr["FaxNumber"].ToString(); int countryId = Convert.ToInt32(dr["CountryId"]); int stateProvinceId = Convert.ToInt32(dr["StateProvinceId"]); bool receiveNewsletter = Convert.ToBoolean(dr["ReceiveNewsletter"]); var customer = CustomerManager.GetCustomerByEmail(email); if (customer == null) { //no customers found customer = CustomerManager.AddCustomerForced(customerGuid, email, username, passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode, string.Empty, string.Empty, languageId, currencyId, (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneId, avatarId, null); } else { if (!customer.IsGuest) { //customer is not a guest customer = CustomerManager.UpdateCustomer(customer.CustomerId, customer.CustomerGuid, email, username, passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode, string.Empty, string.Empty, languageId, currencyId, (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneId, avatarId, customer.DateOfBirth); } else { //customer is a guest customer = CustomerManager.GetCustomerByGuid(customerGuid); if (customer == null) { customer = CustomerManager.AddCustomerForced(customerGuid, email, username, passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode, string.Empty, string.Empty, languageId, currencyId, (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneId, avatarId, null); } else { customer = CustomerManager.UpdateCustomer(customer.CustomerId, customer.CustomerGuid, email, username, passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode, string.Empty, string.Empty, languageId, currencyId, (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest, isForumModerator, totalForumPosts, signature, adminComment, active, deleted, registrationDate, timeZoneId, avatarId, customer.DateOfBirth); } } } if (CustomerManager.FormFieldGenderEnabled) { customer.Gender = gender; } customer.FirstName = firstName; customer.LastName = lastName; if (CustomerManager.FormFieldCompanyEnabled) { customer.Company = company; } if (CustomerManager.FormFieldStreetAddressEnabled) { customer.StreetAddress = streetAddress; } if (CustomerManager.FormFieldStreetAddress2Enabled) { customer.StreetAddress2 = streetAddress2; } if (CustomerManager.FormFieldPostCodeEnabled) { customer.ZipPostalCode = zipPostalCode; } if (CustomerManager.FormFieldCityEnabled) { customer.City = city; } if (CustomerManager.FormFieldPhoneEnabled) { customer.PhoneNumber = phoneNumber; } if (CustomerManager.FormFieldFaxEnabled) { customer.FaxNumber = faxNumber; } if (CustomerManager.FormFieldCountryEnabled) { customer.CountryId = countryId; } if (CustomerManager.FormFieldStateEnabled) { customer.StateProvinceId = stateProvinceId; } if (CustomerManager.FormFieldNewsletterEnabled) { customer.ReceiveNewsletter = receiveNewsletter; } if (TaxManager.EUVatEnabled) { customer.VatNumber = vatNumber; customer.VatNumberStatus = vatNumberStatus; } } } }
/// <summary> /// Export products to XLS /// </summary> /// <param name="FilePath">File path to use</param> /// <param name="products">Products</param> public static void ExportProductsToXLS(string FilePath, List<Product> products) { using (ExcelHelper excelHelper = new ExcelHelper(FilePath)) { excelHelper.HDR = "YES"; excelHelper.IMEX = "0"; Dictionary<string, string> tableDefinition = new Dictionary<string, string>(); int maxStringLength = 200; tableDefinition.Add("Name", string.Format("nvarchar({0})",maxStringLength)); tableDefinition.Add("ShortDescription", string.Format("nvarchar({0})", maxStringLength)); tableDefinition.Add("FullDescription", string.Format("nvarchar({0})", maxStringLength)); tableDefinition.Add("ProductTypeID", "int"); tableDefinition.Add("TemplateID", "int"); tableDefinition.Add("ShowOnHomePage", "nvarchar(5)"); tableDefinition.Add("MetaKeywords", string.Format("nvarchar({0})", maxStringLength)); tableDefinition.Add("MetaDescription", string.Format("nvarchar({0})", maxStringLength)); tableDefinition.Add("MetaTitle", string.Format("nvarchar({0})", maxStringLength)); tableDefinition.Add("AllowCustomerReviews", "nvarchar(5)"); tableDefinition.Add("AllowCustomerRatings", "nvarchar(5)"); tableDefinition.Add("Published", "nvarchar(5)"); tableDefinition.Add("SKU", string.Format("nvarchar(200)", maxStringLength)); tableDefinition.Add("ManufacturerPartNumber", string.Format("nvarchar({0})", maxStringLength)); tableDefinition.Add("IsDownload", "nvarchar(5)"); tableDefinition.Add("DownloadID", "int"); tableDefinition.Add("UnlimitedDownloads", "nvarchar(5)"); tableDefinition.Add("MaxNumberOfDownloads", "int"); tableDefinition.Add("HasSampleDownload", "nvarchar(5)"); tableDefinition.Add("SampleDownloadID", "int"); tableDefinition.Add("IsShipEnabled", "nvarchar(5)"); tableDefinition.Add("IsFreeShipping", "nvarchar(5)"); tableDefinition.Add("AdditionalShippingCharge", "decimal"); tableDefinition.Add("IsTaxExempt", "nvarchar(5)"); tableDefinition.Add("TaxCategoryID", "int"); tableDefinition.Add("ManageInventory", "nvarchar(5)"); tableDefinition.Add("StockQuantity", "int"); tableDefinition.Add("MinStockQuantity", "int"); tableDefinition.Add("LowStockActivityID", "int"); tableDefinition.Add("NotifyAdminForQuantityBelow", "int"); tableDefinition.Add("OrderMinimumQuantity", "int"); tableDefinition.Add("OrderMaximumQuantity", "int"); tableDefinition.Add("DisableBuyButton", "nvarchar(5)"); tableDefinition.Add("Price", "decimal"); tableDefinition.Add("OldPrice", "decimal"); tableDefinition.Add("Weight", "decimal"); tableDefinition.Add("Length", "decimal"); tableDefinition.Add("Width", "decimal"); tableDefinition.Add("Height", "decimal"); tableDefinition.Add("CreatedOn", "datetime"); excelHelper.WriteTable("Products", tableDefinition); foreach (Product p in products) { ProductVariantCollection productVariants = p.ProductVariants; foreach (ProductVariant pv in productVariants) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [Products] (Name, ShortDescription,FullDescription,ProductTypeID,TemplateID,ShowOnHomePage,MetaKeywords,MetaDescription,MetaTitle,AllowCustomerReviews,AllowCustomerRatings,Published,SKU,ManufacturerPartNumber,IsDownload,DownloadID,UnlimitedDownloads,MaxNumberOfDownloads,HasSampleDownload,SampleDownloadID,IsShipEnabled,IsFreeShipping,AdditionalShippingCharge,IsTaxExempt,TaxCategoryID,ManageInventory,StockQuantity,MinStockQuantity,LowStockActivityID,NotifyAdminForQuantityBelow,OrderMinimumQuantity,OrderMaximumQuantity,DisableBuyButton,Price,OldPrice,Weight, Length, Width, Height, CreatedOn) VALUES ("); string name = p.Name; if (name.Length > maxStringLength) name = name.Substring(0, maxStringLength); sb.Append('"'); sb.Append(name.Replace('"', '\'')); sb.Append("\","); string shortDescription = p.ShortDescription; if (shortDescription.Length > maxStringLength) shortDescription = shortDescription.Substring(0, maxStringLength); sb.Append('"'); sb.Append(shortDescription.Replace('"', '\'')); sb.Append("\","); string fullDescription = p.FullDescription; if (fullDescription.Length > maxStringLength) fullDescription = fullDescription.Substring(0, maxStringLength); sb.Append('"'); sb.Append(fullDescription.Replace('"', '\'')); sb.Append("\","); sb.Append(p.ProductTypeID); sb.Append(","); sb.Append(p.TemplateID); sb.Append(","); sb.Append('"'); sb.Append(p.ShowOnHomePage); sb.Append("\","); string metaKeywords = p.MetaKeywords; if (metaKeywords.Length > maxStringLength) metaKeywords = metaKeywords.Substring(0, maxStringLength); sb.Append('"'); sb.Append(metaKeywords.Replace('"', '\'')); sb.Append("\","); string metaDescription = p.MetaDescription; if (metaDescription.Length > maxStringLength) metaDescription = metaDescription.Substring(0, maxStringLength); sb.Append('"'); sb.Append(metaDescription.Replace('"', '\'')); sb.Append("\","); string metaTitle = p.MetaTitle; if (metaTitle.Length > maxStringLength) metaTitle = metaTitle.Substring(0, maxStringLength); sb.Append('"'); sb.Append(metaTitle.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(p.AllowCustomerReviews); sb.Append("\","); sb.Append('"'); sb.Append(p.AllowCustomerRatings); sb.Append("\","); sb.Append('"'); sb.Append(p.Published); sb.Append("\","); string SKU = pv.SKU; if (SKU.Length > maxStringLength) SKU = SKU.Substring(0, maxStringLength); sb.Append('"'); sb.Append(SKU.Replace('"', '\'')); sb.Append("\","); string manufacturerPartNumber = pv.ManufacturerPartNumber; if (manufacturerPartNumber.Length > maxStringLength) manufacturerPartNumber = manufacturerPartNumber.Substring(0, maxStringLength); sb.Append('"'); sb.Append(manufacturerPartNumber.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(pv.IsDownload); sb.Append("\","); sb.Append(pv.DownloadID); sb.Append(","); sb.Append('"'); sb.Append(pv.UnlimitedDownloads); sb.Append("\","); sb.Append(pv.MaxNumberOfDownloads); sb.Append(","); sb.Append('"'); sb.Append(pv.HasSampleDownload); sb.Append("\","); sb.Append(pv.SampleDownloadID); sb.Append(","); sb.Append('"'); sb.Append(pv.IsShipEnabled); sb.Append("\","); sb.Append('"'); sb.Append(pv.IsFreeShipping); sb.Append("\","); sb.Append(pv.AdditionalShippingCharge); sb.Append(","); sb.Append('"'); sb.Append(pv.IsTaxExempt); sb.Append("\","); sb.Append(pv.TaxCategoryID); sb.Append(","); sb.Append('"'); sb.Append(pv.ManageInventory); sb.Append("\","); sb.Append(pv.StockQuantity); sb.Append(","); sb.Append(pv.MinStockQuantity); sb.Append(","); sb.Append(pv.LowStockActivityID); sb.Append(","); sb.Append(pv.NotifyAdminForQuantityBelow); sb.Append(","); sb.Append(pv.OrderMinimumQuantity); sb.Append(","); sb.Append(pv.OrderMaximumQuantity); sb.Append(","); sb.Append('"'); sb.Append(pv.DisableBuyButton); sb.Append("\","); sb.Append(pv.Price); sb.Append(","); sb.Append(pv.OldPrice); sb.Append(","); sb.Append(pv.Weight); sb.Append(","); sb.Append(pv.Length); sb.Append(","); sb.Append(pv.Width); sb.Append(","); sb.Append(pv.Height); sb.Append(","); sb.Append('"'); sb.Append(pv.CreatedOn); sb.Append("\""); sb.Append(")"); excelHelper.ExecuteCommand(sb.ToString()); } } } }
/// <summary> /// Export charge types to XLS /// </summary> /// <param name="filePath">File path to use</param> /// <param name="orders">Orders</param> public void ExportChargeTypeToXls(string filePath, List<ChargeType> charges) { using (ExcelHelper excelHelper = new ExcelHelper(filePath)) { excelHelper.Hdr = "YES"; excelHelper.Imex = "0"; Dictionary<string, string> tableDefinition = new Dictionary<string, string>(); tableDefinition.Add("ChargeTypeID", "int"); tableDefinition.Add("Name", "nvarchar(50)"); tableDefinition.Add("Description", "nvarchar(255)"); tableDefinition.Add("IsActive", "bit"); excelHelper.WriteTable("ChargeType", tableDefinition); foreach (var charge in charges) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [ChargeType] (ChargeTypeID, Name, Description,IsActive) VALUES ("); sb.Append(charge.ChargeTypeID); sb.Append(","); sb.Append('"'); sb.Append(charge.Name); sb.Append('"'); sb.Append(","); sb.Append('"'); sb.Append(charge.Description); sb.Append('"'); sb.Append(","); sb.Append(charge.IsActive); sb.Append(")"); excelHelper.ExecuteCommand(sb.ToString()); } } }
/// <summary> /// Export orders to XLS /// </summary> /// <param name="FilePath">File path to use</param> /// <param name="orders">Orders</param> public static void ExportOrdersToXLS(string FilePath, OrderCollection orders) { using (ExcelHelper excelHelper = new ExcelHelper(FilePath)) { excelHelper.HDR = "YES"; excelHelper.IMEX = "0"; Dictionary<string, string> tableDefinition = new Dictionary<string, string>(); tableDefinition.Add("OrderID", "int"); tableDefinition.Add("OrderGUID", "uniqueidentifier"); tableDefinition.Add("OrderSubtotalInclTax", "decimal"); tableDefinition.Add("OrderSubtotalExclTax", "decimal"); tableDefinition.Add("OrderShippingInclTax", "decimal"); tableDefinition.Add("OrderShippingExclTax", "decimal"); tableDefinition.Add("PaymentMethodAdditionalFeeInclTax", "decimal"); tableDefinition.Add("PaymentMethodAdditionalFeeExclTax", "decimal"); tableDefinition.Add("OrderTax", "decimal"); tableDefinition.Add("OrderTotal", "decimal"); tableDefinition.Add("OrderDiscount", "decimal"); tableDefinition.Add("OrderSubtotalInclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderSubtotalExclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderShippingInclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderShippingExclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("PaymentMethodAdditionalFeeInclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("PaymentMethodAdditionalFeeExclTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderTaxInCustomerCurrency", "decimal"); tableDefinition.Add("OrderTotalInCustomerCurrency", "decimal"); tableDefinition.Add("CustomerCurrencyCode", "nvarchar(5)"); tableDefinition.Add("OrderWeight", "decimal"); tableDefinition.Add("AffiliateID", "int"); tableDefinition.Add("OrderStatusID", "int"); tableDefinition.Add("PaymentMethodID", "int"); tableDefinition.Add("PaymentMethodName", "nvarchar(100)"); tableDefinition.Add("PurchaseOrderNumber", "nvarchar(100)"); tableDefinition.Add("PaymentStatusID", "int"); tableDefinition.Add("BillingFirstName", "nvarchar(100)"); tableDefinition.Add("BillingLastName", "nvarchar(100)"); tableDefinition.Add("BillingPhoneNumber", "nvarchar(50)"); tableDefinition.Add("BillingEmail", "nvarchar(255)"); tableDefinition.Add("BillingFaxNumber", "nvarchar(50)"); tableDefinition.Add("BillingCompany", "nvarchar(100)"); tableDefinition.Add("BillingAddress1", "nvarchar(100)"); tableDefinition.Add("BillingAddress2", "nvarchar(100)"); tableDefinition.Add("BillingCity", "nvarchar(100)"); tableDefinition.Add("BillingStateProvince", "nvarchar(100)"); tableDefinition.Add("BillingZipPostalCode", "nvarchar(100)"); tableDefinition.Add("BillingCountry", "nvarchar(100)"); tableDefinition.Add("ShippingStatusID", "int"); tableDefinition.Add("ShippingFirstName", "nvarchar(100)"); tableDefinition.Add("ShippingLastName", "nvarchar(100)"); tableDefinition.Add("ShippingPhoneNumber", "nvarchar(50)"); tableDefinition.Add("ShippingEmail", "nvarchar(255)"); tableDefinition.Add("ShippingFaxNumber", "nvarchar(50)"); tableDefinition.Add("ShippingCompany", "nvarchar(100)"); tableDefinition.Add("ShippingAddress1", "nvarchar(100)"); tableDefinition.Add("ShippingAddress2", "nvarchar(100)"); tableDefinition.Add("ShippingCity", "nvarchar(100)"); tableDefinition.Add("ShippingStateProvince", "nvarchar(100)"); tableDefinition.Add("ShippingZipPostalCode", "nvarchar(100)"); tableDefinition.Add("ShippingCountry", "nvarchar(100)"); tableDefinition.Add("ShippingMethod", "nvarchar(100)"); tableDefinition.Add("ShippingRateComputationMethodID", "int"); tableDefinition.Add("CreatedOn", "datetime"); excelHelper.WriteTable("Orders", tableDefinition); foreach (Order order in orders) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [Orders] (OrderID, OrderGUID, OrderSubtotalInclTax, OrderSubtotalExclTax, OrderShippingInclTax, OrderShippingExclTax, PaymentMethodAdditionalFeeInclTax, PaymentMethodAdditionalFeeExclTax, OrderTax, OrderTotal, OrderDiscount, OrderSubtotalInclTaxInCustomerCurrency, OrderSubtotalExclTaxInCustomerCurrency, OrderShippingInclTaxInCustomerCurrency, OrderShippingExclTaxInCustomerCurrency, PaymentMethodAdditionalFeeInclTaxInCustomerCurrency, PaymentMethodAdditionalFeeExclTaxInCustomerCurrency, OrderTaxInCustomerCurrency, OrderTotalInCustomerCurrency, CustomerCurrencyCode, OrderWeight, AffiliateID, OrderStatusID, PaymentMethodID, PaymentMethodName, PurchaseOrderNumber, PaymentStatusID, BillingFirstName, BillingLastName, BillingPhoneNumber, BillingEmail, BillingFaxNumber, BillingCompany, BillingAddress1, BillingAddress2, BillingCity, BillingStateProvince, BillingZipPostalCode, BillingCountry, ShippingStatusID, ShippingFirstName, ShippingLastName, ShippingPhoneNumber, ShippingEmail, ShippingFaxNumber, ShippingCompany, ShippingAddress1, ShippingAddress2, ShippingCity, ShippingStateProvince, ShippingZipPostalCode, ShippingCountry, ShippingMethod, ShippingRateComputationMethodID, CreatedOn) VALUES ("); sb.Append(order.OrderID); sb.Append(","); sb.Append('"'); sb.Append(order.OrderGUID); sb.Append("\","); sb.Append(order.OrderSubtotalInclTax); sb.Append(","); sb.Append(order.OrderSubtotalExclTax); sb.Append(","); sb.Append(order.OrderShippingInclTax); sb.Append(","); sb.Append(order.OrderShippingExclTax); sb.Append(","); sb.Append(order.PaymentMethodAdditionalFeeInclTax); sb.Append(","); sb.Append(order.PaymentMethodAdditionalFeeExclTax); sb.Append(","); sb.Append(order.OrderTax); sb.Append(","); sb.Append(order.OrderTotal); sb.Append(","); sb.Append(order.OrderDiscount); sb.Append(","); sb.Append(order.OrderSubtotalInclTaxInCustomerCurrency); sb.Append(","); sb.Append(order.OrderSubtotalExclTaxInCustomerCurrency); sb.Append(","); sb.Append(order.OrderShippingInclTaxInCustomerCurrency); sb.Append(","); sb.Append(order.OrderShippingExclTaxInCustomerCurrency); sb.Append(","); sb.Append(order.PaymentMethodAdditionalFeeInclTaxInCustomerCurrency); sb.Append(","); sb.Append(order.PaymentMethodAdditionalFeeExclTaxInCustomerCurrency); sb.Append(","); sb.Append(order.OrderTaxInCustomerCurrency); sb.Append(","); sb.Append(order.OrderTotalInCustomerCurrency); sb.Append(","); sb.Append('"'); sb.Append(order.CustomerCurrencyCode.Replace('"', '\'')); sb.Append("\","); sb.Append(order.OrderWeight); sb.Append(","); sb.Append(order.AffiliateID); sb.Append(","); sb.Append(order.OrderStatusID); sb.Append(","); sb.Append(order.PaymentMethodID); sb.Append(","); sb.Append('"'); sb.Append(order.PaymentMethodName.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.PurchaseOrderNumber.Replace('"', '\'')); sb.Append("\","); sb.Append(order.PaymentStatusID); sb.Append(","); sb.Append('"'); sb.Append(order.BillingFirstName.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingLastName.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingPhoneNumber.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingEmail.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingFaxNumber.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingCompany.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingAddress1.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingAddress2.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingCity.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingStateProvince.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingZipPostalCode.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingCountry.Replace('"', '\'')); sb.Append("\","); sb.Append(order.ShippingStatusID); sb.Append(","); sb.Append('"'); sb.Append(order.ShippingFirstName.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingLastName.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingPhoneNumber.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingEmail.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingFaxNumber.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingCompany.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingAddress1.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingAddress2.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingCity.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingStateProvince.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingZipPostalCode.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.BillingCountry.Replace('"', '\'')); sb.Append("\","); sb.Append('"'); sb.Append(order.ShippingMethod.Replace('"', '\'')); sb.Append("\","); sb.Append(order.ShippingRateComputationMethodID); sb.Append(","); sb.Append('"'); sb.Append(order.CreatedOn); sb.Append("\""); sb.Append(")"); excelHelper.ExecuteCommand(sb.ToString()); } } }