public void entitiesNameHeader_testDisciplinesNames()
{
exp.lsnCntMult = 6;
exp.Shedule = loadTestingShedule("schedule1.xml");
exp.View = View.Group;
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
int i = 8;
foreach (string name in exp.Shedule.DisciplinesNames)
{
exp.FillEntitiesData(sheet, name, i);
i++;
}
i = 8;
foreach (string name in exp.Shedule.DisciplinesNames)
{
Assert.AreEqual(name, sheet[i, 0].Value);
i++;
}
}
public void hoursFilling_testTwoWeeks()
{
exp.lsnCntMult = 6;
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
exp.FillHours(sheet, 1);
exp.FillHours(sheet, 6 * exp.lsnCntMult * 5 + 1);
string[] hours = new string[6] {
"1-2", "3-4", "5-6", "7-8", "9-10", "11-12"
};
for (int i = 1, j = 0; i <= 6 * exp.lsnCntMult * 5; i += 5, j++)
{
Assert.AreEqual(hours[j], sheet[7, i].Value);
if (j == 5)
{
j = -1;
}
}
for (int i = 1; i <= 6 * exp.lsnCntMult * 5; i += 5)
{
Assert.AreEqual(sheet[7, i].Value, sheet[7, i + 6 * exp.lsnCntMult * 5].Value);
}
}
public void testTextHeaderTableSetting()
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
exp.SetTextHeaderTable(sheet);
Assert.AreEqual("День", sheet[6, 0].Value);
Assert.AreEqual("Часы", sheet[7, 0].Value);
}
public void monthHeaderSetting_testWrongStartMonthNum()
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
exp.SetMonthsHeader(sheet, 13, 0, 1, 4);
for (int i = 0; i < 4; i++)
{
Assert.AreEqual(@"MOUTH N\A", sheet[i + 1, 0].Value);
}
}
public void monthHeaderSetting_testWrongClmnNumber()
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
exp.SetMonthsHeader(sheet, 3, 0, 4, 1);
for (int i = 0; i < 4; i++)
{
Assert.AreEqual(null, sheet[i + 1, 0].Value);
}
}
public void dayNameSetting_testWrongStartDay()
{
exp.lsnCntMult = 6;
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
exp.SetDaysTable(sheet, 8, 6, 1, 6);
for (int i = 0; i < 6; i++)
{
Assert.AreEqual(@"DAY N\A", sheet[6, 0 * exp.lsnCntMult * 5 + 1].Value.ToString());
}
}
public void Export()
{
lsnCntMult = shedule.Setting.CountLessonsOfDay;
book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
sheet.FreezeTopRows = 1;
sheet.FreezeLeftColumns = 8;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
AlignmentOptionsBase alOpt = (AlignmentOptionsBase)sheet.Alignment;
alOpt.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
alOpt.ShrinkToFit = true;
alOpt.Vertical = VerticalAlignment.Center;
alOpt.WrapText = true;
FontOptionsBase font = (FontOptionsBase)sheet.Font;
font.Name = "Arial";
SetTextHeaderTable(sheet);
SetWeeksHeaderTable(sheet);
SetDaysTable(sheet, 1, 6, 1, 6);
SetDaysTable(sheet, 1, 6, 6 * lsnCntMult * lessonHorizontalMult + 1, 12);
int monthStart = shedule.FirstDaySem.Month;
FillDays(sheet, monthStart, 1, Week.FirstWeek, Week.TreeWeek);
FillDays(sheet, monthStart, 6 * lsnCntMult * lessonHorizontalMult + 1, Week.SecondWeek, Week.FourWeek);
FillHours(sheet, 1);
FillHours(sheet, 6 * lsnCntMult * lessonHorizontalMult + 1);
int counter = 0;
foreach (string name in nameElements)
{
FillEntitiesData(sheet, name, 8 + counter);
counter += 2;
}
SetColumnSize(sheet);
SaveToFile(book);
}
public void monthHeaderSetting_testFirstSem()
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
string[] monthes = new string[10] {
"сентябрь", "октябрь", "ноябрь", "декабрь", "январь", "февраль", "март", "апрель", "май", "июнь"
};
exp.SetMonthsHeader(sheet, 9, 0, 1, 4);
for (int i = 0; i < 4; i++)
{
Assert.AreEqual(monthes[i], sheet[i + 1, 0].Value);
}
}
public static void SaveProductsToXml(string filename)
{
var book = new ExcelXmlWorkbook {
Properties = { Author = "AdvantShop.Net", LastAuthor = "AdvantShop.Net" }
};
//-----------------------------------------------
// Properties
//-----------------------------------------------
var style = new XmlStyle {
Font = { Name = "Calibri", Size = 11 }
};
book.DefaultStyle = style;
GenerateProductWorksheet(book[0], ProductService.GetProducts());
book.Export(filename);
}
public void testEntitiesDataFilling()
{
exp.lsnCntMult = 6;
exp.Shedule = loadTestingShedule("schedule1.xml");
exp.View = View.Group;
int monthStart = exp.Shedule.FirstDaySem.Month;
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
int column = 8;
foreach (string name in exp.Shedule.GroupsNames)
{
List <ScheduleLesson> tmp = exp.Shedule.GetLessonsByView(exp.View, name).ToList();
exp.FillLessons(sheet, monthStart, 1, column, tmp, Week.FirstWeek, Week.TreeWeek);
column += 2;
}
Assert.AreEqual("Организация ЭВМ", sheet[8, 1].Value);
Assert.AreEqual("(Лекция)", sheet[8, 2].Value);
Assert.AreEqual("01.09., 29.09., 27.10., 24.11., 22.12., ", sheet[8, 3].Value);
Assert.AreEqual("Андреев А.Е.", sheet[8, 4].Value);
Assert.AreEqual("В-1301", sheet[8, 5].Value);
Assert.AreEqual("Организация ЭВМ", sheet[9, 1].Value);
Assert.AreEqual("(Практика)", sheet[9, 2].Value);
Assert.AreEqual("15.09., 13.10., 10.11., 08.12., 05.01., ", sheet[9, 3].Value);
Assert.AreEqual("Забалуева А.Ф.", sheet[9, 4].Value);
Assert.AreEqual("В-1301", sheet[9, 5].Value);
Assert.AreEqual("Организация ЭВМ", sheet[10, 1].Value);
Assert.AreEqual("(Лекция)", sheet[10, 2].Value);
Assert.AreEqual("01.09., 29.09., 27.10., 24.11., 22.12., ", sheet[10, 3].Value);
Assert.AreEqual("Андреев А.Е.", sheet[10, 4].Value);
Assert.AreEqual("В-1301", sheet[10, 5].Value);
Assert.AreEqual(null, sheet[11, 1].Value);
Assert.AreEqual(null, sheet[11, 2].Value);
Assert.AreEqual(null, sheet[11, 3].Value);
Assert.AreEqual(null, sheet[11, 4].Value);
Assert.AreEqual(null, sheet[11, 5].Value);
}
public void dayFilling_testFirstWeekMonday()
{
exp.lsnCntMult = 6;
exp.Shedule = loadTestingShedule("schedule1.xml");
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
int monthStart = 9;
exp.FillDays(sheet, monthStart, 1, Week.FirstWeek, Week.TreeWeek);
List <List <string> > firstMonDates = new List <List <string> >();
List <string> septMon = new List <string> {
"1", "15", "29"
};
List <string> octMon = new List <string> {
"13", "27"
};
List <string> novMon = new List <string> {
"10", "24"
};
List <string> decMon = new List <string> {
"8", "22"
};
firstMonDates.Add(septMon);
firstMonDates.Add(octMon);
firstMonDates.Add(novMon);
firstMonDates.Add(decMon);
Assert.AreEqual("1", sheet[1, 1].Value);
int j = 1;
foreach (List <string> daysOfMonth in firstMonDates)
{
for (int i = 0; i < daysOfMonth.Count; i++)
{
Assert.AreEqual(daysOfMonth[i], sheet[j, i + 1].Value);
}
j++;
}
}
public void dayNameSetting_testFirstWeek()
{
exp.lsnCntMult = 6;
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
string[] daysOfWeek = new string[6]
{
"понедельник", "вторник", "среда", "четверг", "пятница", "суббота"
};
exp.SetDaysTable(sheet, 1, 6, 1, 6);
for (int i = 0; i < 6; i++)
{
Assert.AreEqual(daysOfWeek[i], sheet[6, i * exp.lsnCntMult * 5 + 1].Value.ToString());
}
}
public void dayFilling_testSecondWeekTuesday()
{
exp.lsnCntMult = 6;
exp.Shedule = loadTestingShedule("schedule1.xml");
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
int monthStart = 9;
exp.FillDays(sheet, monthStart, 6 * exp.lsnCntMult * 5 + 1, Week.SecondWeek, Week.FourWeek);
List <List <string> > secondTueDates = new List <List <string> >();
List <string> septTue = new List <string> {
"9", "23"
};
List <string> octTue = new List <string> {
"7", "21"
};
List <string> novTue = new List <string> {
"4", "18"
};
List <string> decTue = new List <string> {
"2", "16", "30"
};
secondTueDates.Add(septTue);
secondTueDates.Add(octTue);
secondTueDates.Add(novTue);
secondTueDates.Add(decTue);
int j = 1;
foreach (List <string> daysOfMonth in secondTueDates)
{
for (int i = 0; i < daysOfMonth.Count; i++)
{
Assert.AreEqual(daysOfMonth[i], sheet[j, i + 211].Value);
}
j++;
}
}
public BaseSmallEngineExport(ExportInfo exportInfo, IEnumerable <Collector> collectors)
{
if (collectors == null)
{
throw new ArgumentNullException("Collectors is null");
}
this.collectors = collectors;
this.exportInfo = exportInfo;
// создание экземпляра
book = new ExcelXmlWorkbook();
// добавление свойств
book.Properties.Author = "MiShutka";
// получение первого рабочего листа
sheet = book[0];
}
public void hoursFilling_testFirstDay()
{
exp.lsnCntMult = 6;
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
exp.FillHours(sheet, 1);
exp.FillHours(sheet, 6 * exp.lsnCntMult * 5 + 1);
string[] hours = new string[6]
{
"1-2", "3-4", "5-6", "7-8", "9-10", "11-12"
};
for (int i = 0; i < 6; i++)
{
Assert.AreEqual(hours[i], sheet[7, i * 5 + 1].Value);
}
}
void SaveToFile(ExcelXmlWorkbook book)
{
book.Export(fileName);
Process.Start(fileName);
}
private void ProcessExcel()
{
var isNativeFormat = true;
Workbook book = null;
// Open native excel file format
try
{
book = Workbook.getWorkbook(_fullPath);
}
catch (Exception)
{
isNativeFormat = false;
}
try
{
if (isNativeFormat)
{
var worksheet = book.getSheet(0);
const int intRegHead = 1;
if (worksheet.getColumn(1).Length > 0 && chboxDisableProducts.Checked)
{
ProductService.DisableAllProducts();
}
ImportStatistic.TotalRow = worksheet.getColumn(1).Length - 1;
// Step by rows
for (int i = intRegHead; i <= worksheet.getColumn(1).Length - 1; i++)
{
if (!ImportStatistic.IsRun)
{
return;
}
var artNo = Convert.ToString(worksheet.getCell(0, i).Value);
var product = ProductService.GetProduct(artNo);
var name = Convert.ToString(worksheet.getCell(1, i).Value);
if (!string.IsNullOrEmpty(name))
{
product.Name = name;
}
else
{
LogInvalidData(name, 1, i);
continue;
}
string enabled = Convert.ToString(worksheet.getCell(4, i).Value);
product.Enabled = !string.IsNullOrEmpty(enabled) && enabled.Trim().Equals("+");
var discount = Convert.ToString(worksheet.getCell(9, i).Value);
try
{
product.Discount = (string.IsNullOrEmpty(discount)) ? 0 : (Convert.ToDecimal(discount));
}
catch (Exception)
{
LogInvalidData(discount, 9, i);
continue;
}
var weight = Convert.ToString(worksheet.getCell(11, i).Value);
try
{
product.Weight = (string.IsNullOrEmpty(weight)) ? 0 : (Convert.ToDecimal(weight));
}
catch (Exception)
{
LogInvalidData(weight, 11, i);
continue;
}
product.Size = GetSizeForBdFormat(Convert.ToString(worksheet.getCell(12, i).Value));
product.BriefDescription = Convert.ToString(worksheet.getCell(13, i).Value);
product.Description = Convert.ToString(worksheet.getCell(14, i).Value);
var offr = new Offer {
OfferListId = CatalogService.DefaultOfferListId
};
var price = Convert.ToString(worksheet.getCell(5, i).Value);
try
{
offr.Price = (string.IsNullOrEmpty(price)) ? 0 : Convert.ToDecimal(price);
}
catch (Exception)
{
LogInvalidData(price, 5, i);
continue;
}
var sypplyprice = Convert.ToString(worksheet.getCell(6, i).Value);
try
{
offr.SupplyPrice = (string.IsNullOrEmpty(sypplyprice)) ? 0 : Convert.ToDecimal(sypplyprice);
}
catch (Exception)
{
LogInvalidData(sypplyprice, 6, i);
continue;
}
var shippingPrice = Convert.ToString(worksheet.getCell(10, i).Value);
try
{
offr.ShippingPrice = (string.IsNullOrEmpty(shippingPrice)) ? 0 : (Convert.ToDecimal(shippingPrice));
}
catch (Exception)
{
LogInvalidData(shippingPrice, 10, i);
continue;
}
var amount = Convert.ToString(worksheet.getCell(7, i).Value);
try
{
offr.Amount = (string.IsNullOrEmpty(amount)) ? 0 : (Convert.ToInt32(amount));
}
catch (Exception)
{
LogInvalidData(amount, 7, i);
continue;
}
offr.Unit = Convert.ToString(worksheet.getCell(8, i).Value);
product.Offers = new List <Offer> {
offr
};
string rewurl = Convert.ToString(worksheet.getCell(2, i).Value);
//var synonym = (string.IsNullOrEmpty(rewurl)) ? product.ArtNo : rewurl;
//product.Synonym = synonym;
// --- New Fix Code - XML
if (string.IsNullOrEmpty(rewurl)) // пустая строка
{
rewurl = product.ArtNo;
}
else
{
// Не пустая строка, Используется или нет этот синомим вообще ?
int productId = ProductService.GetProductId(product.ArtNo);
if (!UrlService.IsAvalibleUrl(productId, ParamType.Product, rewurl)) // RouteService.GetParamBySynonym(rewurl) != null
{
//TODO не компилится
// Ок, Используется, не у нашего ли товара?
//UrlSynonym synonym = RouteService.GetUrlSynonymByParamValue(productId, ParamType.Product);
//if (synonym != null && synonym.Synonym != rewurl)
//{
// // Нет не унашего. Значит он занят, чтобы небыло дубликата,
// // ставим Артикул как синоним
// rewurl = product.ArtNo;
//}
}
}
product.UrlPath = rewurl;
UpdateInsertProduct(product);
//if (string.IsNullOrEmpty(rewurl) || RouteService.GetParamBySynonym(rewurl) != null) rewurl = product.ArtNo;
//RouteService.UpdateParamSynonym(ParamType.Product, product.ProductId,
//RouteService.InsertUpdateParam(rewurl, ParamType.Product, product.ProductId.ToString());
var parentCategory = Convert.ToString(worksheet.getCell(3, i).Value);
if (!string.IsNullOrEmpty(parentCategory))
{
try
{
CategoryService.SubParseAndCreateCategory(parentCategory, product.ProductId);
}
catch (Exception)
{
LogInvalidData(parentCategory, 3, i);
}
}
else
{
continue;
}
}
}
else
{
ExcelXmlWorkbook xmlbook = ExcelXmlWorkbook.Import(_fullPath);
Worksheet xmlsheet = xmlbook[0];
const int intRegHead = 1;
if (xmlsheet.RowCount > 0 && chboxDisableProducts.Checked)
{
ProductService.DisableAllProducts();
}
ImportStatistic.TotalRow = xmlsheet.RowCount - 1;
// Step by rows
for (int i = intRegHead; i <= xmlsheet.RowCount - 1; i++)
{
if (!ImportStatistic.IsRun)
{
return;
}
var artNo = Convert.ToString(xmlsheet[0, i].Value);
var product = ProductService.GetProduct(artNo);
var name = Convert.ToString(xmlsheet[1, i].Value);
if (!string.IsNullOrEmpty(name))
{
product.Name = name;
}
else
{
LogInvalidData(name, 1, i);
continue;
}
var enabled = Convert.ToString(xmlsheet[4, i].Value);
product.Enabled = !string.IsNullOrEmpty(enabled) && enabled.Trim().Equals("+");
var discount = Convert.ToString(xmlsheet[9, i].Value);
try
{
product.Discount = (string.IsNullOrEmpty(discount)) ? 0 : (Convert.ToDecimal(discount));
}
catch (Exception)
{
LogInvalidData(discount, 9, i);
continue;
}
var weight = Convert.ToString(xmlsheet[11, i].Value);
try
{
product.Weight = (string.IsNullOrEmpty(weight)) ? 0 : Convert.ToDecimal(weight);
}
catch (Exception)
{
LogInvalidData(weight, 11, i);
continue;
}
product.Size = GetSizeForBdFormat(Convert.ToString(xmlsheet[12, i].Value));
product.BriefDescription = Convert.ToString(xmlsheet[13, i].Value);
product.Description = Convert.ToString(xmlsheet[14, i].Value);
var offr = new Offer {
OfferListId = CatalogService.DefaultOfferListId
};
var price = Convert.ToString(xmlsheet[5, i].Value);
try
{
offr.Price = (string.IsNullOrEmpty(price)) ? 0 : Convert.ToDecimal(price);
}
catch (Exception)
{
LogInvalidData(price, 5, i);
continue;
}
var supplyPrice = Convert.ToString(xmlsheet[6, i].Value);
try
{
offr.SupplyPrice = (string.IsNullOrEmpty(supplyPrice)) ? 0 : Convert.ToDecimal(supplyPrice);
}
catch (Exception)
{
LogInvalidData(supplyPrice, 6, i);
continue;
}
var shippingPrice = Convert.ToString(xmlsheet[10, i].Value);
try
{
offr.ShippingPrice = (string.IsNullOrEmpty(shippingPrice)) ? 0 : (Convert.ToDecimal(shippingPrice));
}
catch (Exception)
{
LogInvalidData(shippingPrice, 10, i);
continue;
}
var amount = Convert.ToString(xmlsheet[7, i].Value);
try
{
offr.Amount = (string.IsNullOrEmpty(amount)) ? 0 : Convert.ToInt32(amount);
}
catch (Exception)
{
LogInvalidData(amount, 7, i);
continue;
}
offr.Unit = Convert.ToString(xmlsheet[8, i].Value);
product.Offers = new List <Offer> {
offr
};
string rewurl = Convert.ToString(xmlsheet[2, i].Value);
//string synonym = (string.IsNullOrEmpty(rewurl)) ? product.ArtNo : rewurl;
//product.Synonym = synonym;
// Todo
// --- New Fix Code - XML
if (string.IsNullOrEmpty(rewurl)) // пустая строка
{
rewurl = product.ArtNo;
}
else
{
// Не пустая строка, Используется или нет этот синомим вообще ?
int productId = ProductService.GetProductId(product.ArtNo);
if (!UrlService.IsAvalibleUrl(productId, ParamType.Product, rewurl)) // RouteService.GetParamBySynonym(rewurl) != null
{
//TODO не компилится
// Ок, Используется, не у нашего ли товара?
//UrlSynonym synonym = RouteService.GetUrlSynonymByParamValue(productId, ParamType.Product);
//if (synonym != null && synonym.Synonym != rewurl)
//{
// // Нет не унашего. Значит он занят, чтобы небыло дубликата,
// // ставим Артикул как синоним
// rewurl = product.ArtNo;
//}
}
}
product.UrlPath = rewurl;
UpdateInsertProduct(product);
//RouteService.UpdateParamSynonym(ParamType.Product, product.ProductId, synonym);
//if (string.IsNullOrEmpty(rewurl) || RouteService.GetParamBySynonym(rewurl) != null) rewurl = product.ArtNo;
//RouteService.InsertUpdateParam(rewurl, ParamType.Product, product.ProductId.ToString());
var parentCategory = Convert.ToString(xmlsheet[3, i].Value);
if (string.IsNullOrEmpty(parentCategory))
{
continue;
}
try
{
CategoryService.SubParseAndCreateCategory(parentCategory, product.ProductId);
}
catch
{
LogInvalidData(parentCategory, 3, i);
//TODO: vk: тут надо бы вывести сообщение о том, что продукт уже есть в категории. или изменить try/catch блок на проверку существования продукта в категории
//наличие exception`ов здесь - нормально
}
}
}
CategoryService.RecalculateProductsCountManual();
//TODO find where is this function!
//ProductService.SumImportLog(Resource.Admin_ImportXLS_UpdoadingSuccessfullyCompleted,Resource.Admin_ImportXLS_UpdoadingCompletedWithErrors);
}
catch (Exception ex)
{
MsgErr(ex.Message + " at xls");
Debug.LogError(ex);
}
ImportStatistic.IsRun = false;
//ImportStatistic.ThreadImport.Abort();
}
private static ExcelXmlWorkbook OrdersArrayToWorkbook(IEnumerable <Order> orders)
{
var book = new ExcelXmlWorkbook {
Properties = { Author = "AdvantShop.Net", LastAuthor = "AdvantShop.Net" }, DefaultStyle = new XmlStyle {
Font = { Name = "Calibri", Size = 11 }
}
};
Worksheet sheet = book[0];
sheet.Name = "Orders";
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
//XLS columns definition
sheet.Columns(0).Width = 50;
sheet.Columns(1).Width = 80;
sheet.Columns(2).Width = 100;
sheet.Columns(3).Width = 130;
sheet.Columns(4).Width = 100;
sheet.Columns(5).Width = 100;
sheet.Columns(6).Width = 150;
sheet.Columns(7).Width = 80;
sheet.Columns(8).Width = 80;
sheet.Columns(9).Width = 80;
sheet.Columns(10).Width = 80;
sheet.Columns(11).Width = 300;
sheet.Columns(12).Width = 300;
sheet.Columns(13).Width = 300;
sheet.Columns(14).Width = 300;
sheet.Columns(15).Width = 300;
sheet.Columns(16).Width = 50;
sheet[0, 0].Value = "OrderID";
sheet[1, 0].Value = "Status";
sheet[2, 0].Value = "OrderDate";
sheet[3, 0].Value = "FIO";
sheet[4, 0].Value = "Customer Email";
sheet[5, 0].Value = "Customer Phone";
sheet[6, 0].Value = "OrderedItems";
sheet[7, 0].Value = "Total";
sheet[8, 0].Value = "Tax";
sheet[9, 0].Value = "Cost";
sheet[10, 0].Value = "Profit";
sheet[11, 0].Value = "Payment";
sheet[12, 0].Value = "Shipping";
sheet[13, 0].Value = "Shipping Address";
sheet[14, 0].Value = "Customer Comment";
sheet[15, 0].Value = "Admin Comment";
sheet[16, 0].Value = "Payed";
var i = 1;
foreach (Order order in orders)
{
if (!CommonStatistic.IsRun)
{
return(book);
}
CommonStatistic.RowPosition++;
//Order to XLS row
sheet[0, i].Value = order.OrderID;
sheet[1, i].Value = order.OrderStatus != null ? order.OrderStatus.StatusName : "Неизвестный";
sheet[2, i].Value = order.OrderDate;
if (order.OrderCustomer != null)
{
sheet[3, i].Value = order.OrderCustomer.LastName + " " + order.OrderCustomer.FirstName;
sheet[4, i].Value = order.OrderCustomer.Email ?? string.Empty;
sheet[5, i].Value = order.OrderCustomer.MobilePhone ?? string.Empty;
}
else
{
sheet[3, i].Value = "Неизвестный";
sheet[4, i].Value = string.Empty;
sheet[5, i].Value = string.Empty;
}
if (order.OrderCurrency != null)
{
sheet[6, i].Value = RenderOrderedItems(order.OrderItems) ?? string.Empty;
sheet[7, i].Value = CatalogService.GetStringPrice(order.Sum, order.OrderCurrency);
sheet[8, i].Value = CatalogService.GetStringPrice(order.TaxCost, order.OrderCurrency);
float totalCost = order.OrderItems.Sum(oi => oi.SupplyPrice * oi.Amount);
sheet[9, i].Value = CatalogService.GetStringPrice(totalCost, order.OrderCurrency);
sheet[10, i].Value =
CatalogService.GetStringPrice(order.Sum - order.ShippingCost - order.TaxCost - totalCost,
order.OrderCurrency);
sheet[11, i].Value = order.PaymentMethodName;
sheet[12, i].Value = order.ArchivedShippingName + " - " +
CatalogService.GetStringPrice(order.ShippingCost, order.OrderCurrency);
sheet[13, i].Value = order.ShippingContact != null
? new List <string>
{
order.ShippingContact.Zip,
order.ShippingContact.Country,
order.ShippingContact.City,
order.ShippingContact.Address,
order.ShippingContact.CustomField1,
order.ShippingContact.CustomField2,
order.ShippingContact.CustomField3
}.Where(s => s.IsNotEmpty()).AggregateString(", ")
: string.Empty;
sheet[14, i].Value = order.CustomerComment ?? string.Empty;
sheet[15, i].Value = order.AdminOrderComment ?? string.Empty;
sheet[16, i].Value = order.Payed ? " Да" : "Нет";
}
i++;
}
return(book);
}
public void Exportar(DadosRelatorio dados, Stream stream)
{
_dados = dados;
ExcelXmlWorkbook pasta = new ExcelXmlWorkbook();
pasta.Properties.Author = "Sistema Integrado de Monitoramento e Licenciamento Ambiental";
pasta.Properties.Company = "Instituto de Defesa Agropecuária e Florestal";
pasta.Properties.Title = dados.Nome;
Worksheet planilha = pasta[0];
IniciarRelatorio(dados, planilha);
int linha = 2;
if (dados.ComAgrupamento)
{
if (dados.Grupos.Count == 0)
{
linha = EscreverColunas(dados.Colunas.Select(x => x.Value).ToList(), planilha, linha);
linha = EscreverSemDados(planilha, linha);
}
else
{
foreach (var grupo in dados.Grupos)
{
linha = EscreverTituloGrupo(grupo.Campo, grupo.Valor, planilha, linha);
linha = EscreverColunas(dados.Colunas.Select(x => x.Value).ToList(), planilha, linha);
linha = EscreverDados(grupo.Dados, planilha, linha);
if (grupo.Sumarizacoes.Linhas.Count > 0)
{
linha = EscreverTituloSumario(planilha, linha, "Grupo");
linha = EscreverSumario(grupo.Sumarizacoes, planilha, linha);
}
if (dados.Totalizar)
{
linha = EscreverTotalGrupo(grupo.Total, planilha, linha);
}
linha++;
}
if (dados.Sumarizacoes.Linhas.Count > 0)
{
linha = EscreverTituloSumario(planilha, linha);
linha = EscreverColunas(dados.Colunas.Select(x => x.Value).ToList(), planilha, linha);
linha = EscreverSumario(dados.Sumarizacoes, planilha, linha);
}
if (dados.Totalizar)
{
linha = EscreverTotal(dados.Total, planilha, linha);
}
}
}
else
{
if (dados.Dados.Linhas.Count == 0)
{
linha = EscreverColunas(dados.Colunas.Select(x => x.Value).ToList(), planilha, linha);
linha = EscreverSemDados(planilha, linha);
}
else
{
linha = EscreverColunas(dados.Colunas.Select(x => x.Value).ToList(), planilha, linha);
linha = EscreverDados(dados.Dados, planilha, linha);
if (dados.Sumarizacoes.Linhas.Count > 0)
{
linha = EscreverTituloSumario(planilha, linha);
linha = EscreverSumario(dados.Sumarizacoes, planilha, linha);
}
if (dados.Totalizar)
{
linha = EscreverTotal(dados.Total, planilha, linha);
}
}
}
pasta.Export(stream);
}
public void GenerateOutput_Backup(string outputRootDir)
{
#region Input validation
if (string.IsNullOrWhiteSpace(outputRootDir))
{
throw new ArgumentNullException(nameof(outputRootDir));
}
if (!Directory.Exists(outputRootDir))
{
throw new ArgumentException($"Unable to find directory {outputRootDir}.");
}
#endregion
#region Create output DIR and file
OutputDir = Path.Combine(outputRootDir, Ids.EXCEL_DIR);
if (!Directory.Exists(Directory.CreateDirectory(OutputDir).FullName))
{
throw new OutputComponentException($"Unable to create output directory {OutputDir}.");
}
OutputFile = Path.Combine(outputRootDir, Ids.EXCEL_DIR, Ids.EXCEL_FILE);
// File.Create(OutputFile);
// if (!File.Exists(OutputFile))
// throw new OutputComponentException($"Unable to create output file {OutputFile}.");
#endregion
#region Define row 'n' columns values...
int columns = 13;
int firstHeaderRow = 0;
int secondHeaderRow = firstHeaderRow + 1;
int timestampRow = secondHeaderRow + 1;
int tableHeaderRow = 0;
int row = tableHeaderRow + 1;
int freezeRow = tableHeaderRow + 1;
#endregion
#region Create the Excel xml workbook...
// Create a instance...
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
// Many such properties exist. Details can be found in the documentation
// The author of the document
book.Properties.Author = "Code Analyzer";
// This returns the first worksheet.
// Note that we have not declared a instance of a new worksheet
// All the dirty work is done by the library.
Worksheet dashboardSheet = book[0];
Worksheet matchesSheet = book[1];
// Name is the name of the sheet. If not set, the default name
// style is "sheet" + sheet number, like sheet1, sheet2
dashboardSheet.Name = "Dashboard";
matchesSheet.Name = "Matches";
// More on this in documentation
matchesSheet.FreezeTopRows = freezeRow;
// and this too...
dashboardSheet.PrintOptions.Orientation = PageOrientation.Landscape;
dashboardSheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
#endregion
#region Creating the header...
// Text for the header...
dashboardSheet[0, firstHeaderRow].Value = "Octopus | Code Analyzer";
dashboardSheet[0, secondHeaderRow].Value = "The Octopus Code Analyzer - the simple way to keep track of your code base";
// Setting the background color for the header...
new Range(dashboardSheet[0, firstHeaderRow], dashboardSheet[30, firstHeaderRow]).Interior.Color = Color.FromArgb(22, 54, 92);
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Interior.Color = Color.FromArgb(22, 54, 92);
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.Sides = BorderSides.Bottom;
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.LineStyle = Borderline.Double;
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.Color = Color.FromArgb(255, 255, 255);
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.Weight = 1;
// Setting the foreground color for the header text...
new Range(dashboardSheet[0, firstHeaderRow], dashboardSheet[8, firstHeaderRow]).Font.Color = Color.FromArgb(255, 255, 255);
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[8, secondHeaderRow]).Font.Color = Color.FromArgb(255, 255, 255);
// Setting the font for the header text...
new Range(dashboardSheet[0, firstHeaderRow], dashboardSheet[8, firstHeaderRow]).Font.Size = 18;
new Range(dashboardSheet[0, firstHeaderRow], dashboardSheet[8, firstHeaderRow]).Font.Bold = true;
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[8, secondHeaderRow]).Font.Size = 8;
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[8, secondHeaderRow]).Font.Bold = true;
dashboardSheet[0, timestampRow].Value = "Exported on: " + DateTime.Now.ToShortDateString();
dashboardSheet[0, timestampRow].Font.Bold = true;
dashboardSheet[0, timestampRow].Font.Size = 8;
#endregion
#region Create the table header for all the data...
// Setting the width of the columns...
matchesSheet.Columns(0).Width = 100;
matchesSheet.Columns(1).Width = 100;
matchesSheet.Columns(2).Width = 100;
matchesSheet.Columns(3).Width = 100;
matchesSheet.Columns(4).Width = 100;
matchesSheet.Columns(5).Width = 100;
matchesSheet.Columns(6).Width = 100;
matchesSheet.Columns(7).Width = 100;
matchesSheet.Columns(8).Width = 100;
matchesSheet.Columns(9).Width = 100;
matchesSheet.Columns(10).Width = 100;
matchesSheet.Columns(11).Width = 100;
matchesSheet.Columns(12).Width = 100;
matchesSheet.Columns(13).Width = 900;
// Inserting headers and setting them to bold...
matchesSheet[0, tableHeaderRow].Value = "BatchId Id";
matchesSheet[1, tableHeaderRow].Value = "Timestamp";
matchesSheet[2, tableHeaderRow].Value = "Match Id";
matchesSheet[3, tableHeaderRow].Value = "Severity";
matchesSheet[4, tableHeaderRow].Value = "Project";
matchesSheet[5, tableHeaderRow].Value = "Language";
matchesSheet[6, tableHeaderRow].Value = "Category";
matchesSheet[7, tableHeaderRow].Value = "Category description";
matchesSheet[8, tableHeaderRow].Value = "Rule";
matchesSheet[9, tableHeaderRow].Value = "Rule description";
matchesSheet[10, tableHeaderRow].Value = "Rule Expression";
matchesSheet[11, tableHeaderRow].Value = "File name";
matchesSheet[12, tableHeaderRow].Value = "Match on line";
matchesSheet[13, tableHeaderRow].Value = "Code extract";
new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Font.Bold = true;
new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).AutoFilter();
new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Border.Sides = BorderSides.Bottom;
new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Border.LineStyle = Borderline.Continuous;
new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Border.Weight = 2;
#endregion
#region Insert data...
// Insert data...
IMatchProxy matchProxy = ProxyHome.Instance.RetrieveMatchProxy(OutputKeyKeeper.Instance.AccessKey);
foreach (IMatch match in matchProxy.Matches())
{
if ((row % 2) == 0)
{
new Range(matchesSheet[0, row], matchesSheet[columns, row]).Interior.Color = Color.FromArgb(220, 230, 241);
}
else
{
new Range(matchesSheet[0, row], matchesSheet[columns, row]).Interior.Color = Color.FromArgb(197, 217, 241);
}
matchesSheet[0, row].Value = match.Batch.Id;
matchesSheet[1, row].Value = $"{match.Batch.TimeStamp.ToShortDateString()} - {match.Batch.TimeStamp.ToShortTimeString()}";
matchesSheet[2, row].Value = match.Id;
matchesSheet[3, row].Value = $"{RuleSeverityMapper.Int2RuleSeverity((int)match.Severity)}";
matchesSheet[4, row].Value = match.ProjectDefinitionRef.Name;
matchesSheet[5, row].Value = match.LanguageDeclarationRef.Name;
matchesSheet[6, row].Value = match.CategoryDeclarationRef.Name;
matchesSheet[7, row].Value = match.CategoryDeclarationRef.Description;
matchesSheet[8, row].Value = match.RuleDeclarationRef.Name;
matchesSheet[9, row].Value = match.RuleDeclarationRef.Description;
matchesSheet[10, row].Value = match.RuleDeclarationRef.Expression;
matchesSheet[11, row].Value = match.Filename;
matchesSheet[12, row].Value = match.LineNumber;
matchesSheet[13, row].Value = match.CodeExtract;
row++;
}
#endregion
#region Export the Excel xml workbook...
book.Export(OutputFile);
#endregion
}
public static Task <RegistrySDSP> Parse(string fileName)
{
RegistrySDSP sdsp = new RegistrySDSP();
TaskCompletionSource <RegistrySDSP> tcs = new TaskCompletionSource <RegistrySDSP>();
ThreadStart listenThread = delegate()
{
try
{
ExcelXmlWorkbook book = ExcelXmlWorkbook.Import("data.xml");
var r = book.SheetCount;
Worksheet sheet = book[0];
int columnCount = sheet.ColumnCount;
if (columnCount <= 0)
{
throw new InvalidDataException("В рабочем листе недопустимое количество колонок.");
}
int rowCount = sheet.RowCount;
if (rowCount <= 3)
{
throw new InvalidDataException("В рабочем листе недопустимое количество строк.");
}
String departament = null;
RegistryCollector collector = null;
// количество счётчиков очередной системы
int collectorMetersCount = 0;
// количество счётчиков для обработки
int countMetersToParse = 0;
#region парсер
// пропускаем название таблицы и шапку таблицы
for (int rowIndex = 2; rowIndex < sheet.RowCount; rowIndex++)
{
Row row = sheet[rowIndex];
int cellCount = row.CellCount;
// имеются ли данные в строке
bool hasData = false;
for (int colIndex = 0; colIndex < row.CellCount; colIndex++)
{
if (!row[colIndex].IsEmpty())
{
hasData = true;
break;
}
}
if (hasData == false)
{
continue;
}
// первая ячейка в строке, это не первая ячейка в строке листа EXCEL !
Cell firstCell = row[0];
ContentType meterNameCellContentType = row[4].ContentType;
// если это какая-то категория
if (firstCell.ContentType == ContentType.None & meterNameCellContentType == ContentType.None)
{
// если это новая - добавляем предыдущую в список
if (departament != null)
{
sdsp.Departaments.Add(departament);
}
departament = String.Empty;
StringBuilder data = new StringBuilder();
for (int i = 0; i < row.CellCount; i++)
{
if (row[i].ContentType == ContentType.String)
{
data.Append(row[i].Value);
}
}
departament = data.ToString();
}
// тогда если это данные
else
{
if (meterNameCellContentType == ContentType.UnresolvedValue)
{
U.L(LogLevel.Error, "EDITOR PARSER",
String.Format("Не обнаружены данные:\nНомер строки таблицы {0}", rowIndex));
throw new InvalidDataException("Не обнаружены данные.");
}
if (meterNameCellContentType == ContentType.String)
{
RegistryCounter counter = new RegistryCounter();
// имеется более одного счётчика?
collectorMetersCount = firstCell.RowSpan;
if (collectorMetersCount > 1)
{
countMetersToParse = collectorMetersCount;
}
// если значение в первом столбце имеется и является числовым значением
if (firstCell.ContentType == ContentType.Number)
{
collector = new RegistryCollector();
try
{
// номер по-порядку
collector.NumberOfOrder = row[0].GetValue <uint>();
// фидер
collector.House = row[1].Value == null ? "<???>" : row[1].GetValue <string>();
// тип модема
collector.ModemType = row[2].Value == null ? "<???>" : row[2].GetValue <string>();
// номер gsm
collector.GsmNumber = row[3].Value == null ? "<???>" : row[3].Value.ToString();
// место установки
collector.Street = row[10].Value == null ? "<???>" : row[10].GetValue <string>();
// примечание и номер договора
if (row[11].ContentType == ContentType.String)
{
collector.Description += "Договор №" + Environment.NewLine + row[11].GetValue <string>();
}
collector.Description += row[12].GetValue <string>();
collector.Departament = departament;
//collector.CreationDate = row[13].Value == null ? "<?>" : row[13].GetValue<string>();
}
catch (Exception ex)
{
var s = ex.Message;
}
}
try
{
// присоединение
counter.Name = row[4].Value == null ? "<???>" : row[4].GetValue <string>();
// состояние
// сетевой адрес
counter.NetworkAddress = row[6].Value == null ? "<???>" : row[6].Value.ToString();
// тип счётчика
counter.CounterType = row[7].Value == null ? "<???>" : row[7].GetValue <string>();
// номер счётчика
counter.Number = row[8].Value == null ? "<???>" : row[8].Value.ToString();
// количество тарифов
counter.TarifsCount = row[9].Value == null ? (byte)0 : row[9].GetValue <byte>();
}
catch (Exception ex)
{
var s = ex.Message;
}
//
countMetersToParse--;
if (collector == null)
{
System.Diagnostics.Debugger.Break();
}
if (collector.Counters == null)
{
System.Diagnostics.Debugger.Break();
}
if (counter == null)
{
System.Diagnostics.Debugger.Break();
}
collector.Counters.Add(counter);
if (countMetersToParse == 0)
{
sdsp.Collectors.Add(collector);
collector = null;
}
}
}
}
#endregion
tcs.TrySetResult(sdsp);
}
catch (Exception e)
{
U.L(LogLevel.Error, "EDITOR", "Ошибка при импорте данных.");
U.L(LogLevel.Error, "EDITOR", e.Message);
}
};
Thread l_thread = new Thread(listenThread);
l_thread.Name = "Import data thread";
l_thread.Priority = ThreadPriority.Highest;
l_thread.Start();
return(tcs.Task);
}
