/// <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 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());
}
}
}
/// <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 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>
/// 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());
}
}
}