private static bool FillDocument(string fileName, List <LotsExtended> lotsExtended, string lotCode = "", decimal minimalPrice = 0, decimal startPrice = 0, bool withPDF = false)
{
IExcelService excel = new ExcelService(fileName);
if (minimalPrice > 0)
{
decimal difference = 100 - ((startPrice - minimalPrice) / (startPrice / 100));
foreach (var subItem in lotsExtended)
{
subItem.endprice = subItem.price / 100 * difference;
subItem.endsum = subItem.endprice * subItem.quantity;
}
}
int iRow = 4;
excel.SetCells(1, "A", "Техническая спецификация по лоту №" + lotCode);
try {
foreach (var item in lotsExtended)
{
excel.SetCells(iRow, "A", item.serialnumber);
excel.SetCells(iRow, "B", item.name);
excel.SetCells(iRow, "C", item.unit);
excel.SetCells(iRow, "D", item.quantity);
excel.SetCells(iRow, "E", item.price);
excel.SetCells(iRow, "F", item.sum);
excel.SetCells(iRow, "G", item.country);
excel.SetCells(iRow, "H", item.techspec);
excel.SetCells(iRow, "I", item.terms);
excel.SetCells(iRow, "J", item.paymentterm);
excel.SetCells(iRow, "K", item.dks);
excel.SetCells(iRow, "L", item.contractnumber);
excel.SetCells(iRow, "M", item.endprice);
excel.SetCells(iRow, "N", item.endsum);
for (var i = 1; i < 15; i++)
{
excel.SetCellBorder(iRow, i);
}
iRow++;
}
} catch (Exception) { }
excel.SetCells(iRow, "F", lotsExtended.Sum(l => l.sum));
excel.SetCells(iRow, "N", lotsExtended.Sum(l => l.endsum));
excel.SetPagesFit();
if (withPDF)
{
excel.SaveAsPDF(fileName.Replace(".xlsx", ".pdf"), false);
}
excel.CloseWorkbook(true);
excel.CloseExcel();
return(true);
}
private void submit_Click(object sender, EventArgs e)
{
ExcelService calcSheet = new ExcelService();
Status[] outStatus;
RangeCoordinates rangeCoordinates = new RangeCoordinates();
string sheetName = "Sheet1";
string targetWorkbookPath = "http://vsmoss/sites/intranet/Docs/Expense%20Calculators/Mileage%20Calculator.xlsx";
calcSheet.Credentials = System.Net.CredentialCache.DefaultCredentials;
try
{
string id = calcSheet.OpenWorkbook(
targetWorkbookPath, "en-US", "en-US", out outStatus);
object rateCell = calcSheet.GetCell(
id, sheetName, 1, 1, true, out outStatus);
calcSheet.SetCell(id, sheetName, 1, 0, mileage.Value);
calcSheet.Calculate(id, sheetName, rangeCoordinates);
object reimbursementCell = calcSheet.GetCell(
id, sheetName, 1, 2, true, out outStatus);
rate.Text = rateCell.ToString();
reimbursement.Text = reimbursementCell.ToString();
calcSheet.CloseWorkbook(id);
}
catch (Exception x)
{
MessageBox.Show(x.Message);
}
}
private static void FillDocument()
{
// Open document
excel = new ExcelService(fileName);
// Fill document
excel.SetCells(5, "A", ""); // Registral number
excel.SetCells(5, "B", company.name);
excel.SetCells(5, "C", company.bin);
excel.SetCells(5, "D", ""); // RNN
excel.SetCells(5, "E", company.govregnumber);
excel.SetCells(5, "F", company.govregdate == null ? "Не указано" : ((DateTime)company.govregdate).ToShortDateString());
excel.SetCells(5, "G", contract.number);
excel.SetCells(5, "H", contract.agreementdate == null ? "" : ((DateTime)contract.agreementdate).ToShortDateString());
excel.SetCells(5, "I", ""); // City
excel.SetCells(5, "J", company.addressActual);
excel.SetCells(5, "K", company.addressLegal);
excel.SetCells(5, "L", company.directorPowers);
excel.SetCells(5, "M", company.director);
excel.SetCells(5, "N", ""); // City
excel.SetCells(5, "O", company.bank);
excel.SetCells(5, "P", company.bik);
excel.SetCells(5, "Q", company.iik.Replace(" ", ""));
// Save & close document
excel.CloseWorkbook(true);
excel.CloseExcel();
}
public override void Close()
{
excel.CloseWorkbook(false);
excel.CloseExcel();
word.CloseDocument(true);
word.CloseWord(true);
}
public static void FormateDocument(string fileName, List <DealNumberInfo> reportList)
{
ExcelService excel = new ExcelService(fileName);
int iRow = 2;
foreach (var item in reportList)
{
excel.SetCells(iRow, "A", item.dealNumber);
excel.SetCells(iRow, "B", item.auctionDate);
excel.SetCells(iRow, "C", item.auctionNumber);
excel.SetCells(iRow, "D", item.customerName);
excel.SetCells(iRow, "E", item.lotCode);
excel.SetCells(iRow, "F", item.supplierName);
excel.SetCells(iRow, "G", item.finalPriceOffer);
excel.SetCells(iRow, "H", item.debt);
excel.SetCells(iRow, "I", item.traderName);
excel.SetCells(iRow, "J", item.brokerName);
iRow++;
}
excel.CloseWorkbook(true);
excel.CloseExcel();
}
private static void FillDocument(string fileName, List <FinalReportPlmtl> finalReportPlmtl)
{
IExcelService excel = new ExcelService(fileName);
int iRow = 2, startRow = 0;
string curLotCode = "", curAuctionNum = "";
bool IsNew = true;
decimal oldFinalPrice = 0;
foreach (var item in finalReportPlmtl)
{
if (curLotCode == item.lotCode && curAuctionNum == item.auctionNumber)
{
IsNew = false;
}
else
{
excel.SetCells(iRow, "A", item.dealNumber);
excel.SetCells(iRow, "B", item.auctionDate);
excel.SetCells(iRow, "J", item.name);
excel.SetCells(iRow, "K", item.bin);
excel.SetCells(iRow, "M", item.address);
excel.SetCells(iRow, "N", item.email);
excel.SetCells(iRow, "P", item.telephone);
if (iRow != 2)
{
CountLastStroke(excel, startRow, iRow, oldFinalPrice);
excel.InsertRow(iRow + 1);
iRow++;
}
startRow = iRow;
curLotCode = item.lotCode;
curAuctionNum = item.auctionNumber;
IsNew = true;
}
try
{
excel.SetCells(iRow, "C", item.productName);
excel.SetCells(iRow, "E", item.unit);
excel.SetCells(iRow, "F", item.quantity);
excel.SetCells(iRow, "G", item.productSum);
}
catch { }
excel.InsertRow(iRow + 1);
iRow++;
}
excel.CloseWorkbook(true);
excel.CloseExcel();
excel = null;
}
static void Main(string[] args)
{
//Instantiate the Web service and create a status array object and range coordinate object
ExcelService es = new ExcelService();
Status[] outStatus;
string sheetName = "Sheet1";
string targetWorkbookPath = "http://chiron/codedemo/Excel/Provision.xlsx";
//Set credentials for requests
es.Credentials = System.Net.CredentialCache.DefaultCredentials;
try
{
//Call open workbook, and point to the trusted
//location of the workbook to open.
string sessionId = es.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", out outStatus);
// Set the input parameter
es.SetCellA1(sessionId, sheetName, "Provisionssatz", "10");
es.SetCellA1(sessionId, sheetName, "Umsatz", "1000");
// Recalculate
es.CalculateWorkbook(sessionId, CalculateType.Recalculate);
// Get the return value
object result = es.GetCellA1(sessionId, sheetName, "Provision", true, out outStatus);
// Access a range of cells
object[] rangeResult = es.GetRangeA1(sessionId, sheetName, "F1:F3", true, out outStatus);
foreach (object row in rangeResult)
{
foreach (object cell in (object[])row)
{
Console.Write(cell + " ");
}
Console.WriteLine();
}
//Close workbook. This also closes session.
es.CloseWorkbook(sessionId);
}
catch (SoapException e)
{
Console.WriteLine("SOAP Exception Message: {0}", e.Message);
}
}
public static bool FillKaspiTechSpec(string templateFileName, List <LotsExtended> lotsExtended)
{
if (string.IsNullOrEmpty(templateFileName))
{
return(false);
}
var excel = new ExcelService(templateFileName);
if (excel == null)
{
return(false);
}
int rowCount = 15;
int iCount = 1;
try {
foreach (var item in lotsExtended)
{
if (iCount > 1)
{
excel.InsertRow(rowCount + 1);
}
excel.SetCells(rowCount, "C", iCount);
excel.SetCells(rowCount, "D", item.name != null ? item.name : "");
excel.SetCells(rowCount, "E", item.unit != null ? item.unit : "");
excel.SetCells(rowCount, "F", item.quantity != null ? item.quantity : 0);
excel.SetCells(rowCount, "H", item.price != null ? item.price : 0);
excel.SetCells(rowCount, "I", (item.quantity != null && item.price != null) ? (item.quantity * item.price) : 0);
excel.SetCells(rowCount, "K", item.terms != null ? item.terms : "");
iCount += 1;
rowCount += 1;
}
excel.SetCells(rowCount, "F", lotsExtended.Sum(l => l.quantity));
excel.SetCells(rowCount, "I", lotsExtended.Sum(l => l.sum));
} catch {
return(false);
} finally {
if (excel.IsWorkbookOpened())
{
excel.CloseWorkbook(true);
}
if (excel.IsExcelOpened())
{
excel.CloseExcel();
}
}
return(true);
}
public static bool ParseDocument(string fileNameInfo, List <LotsExtended> lotExtendedInfo, out List <LotsExtended> lotExtendedResult)
{
// Open document
IExcelService excel = new ExcelService(fileNameInfo);
// Parse document
int rCount = excel.GetRowsCount();
for (var iCount = 2; iCount < rCount + 1; iCount++)
{
if (lotExtendedInfo[iCount - 2].serialnumber == Convert.ToInt32(excel.GetCell(iCount, "A")) && lotExtendedInfo[iCount - 2].name == excel.GetCell(iCount, "B"))
{
/*lotExtendedInfo[iCount - 2].serialnumber = Convert.ToInt32(excel.GetCell(iCount, "A"));
* lotExtendedInfo[iCount - 2].name = excel.GetCell(iCount, "B");
* lotExtendedInfo[iCount - 2].unit = excel.GetCell(iCount, "C");
* lotExtendedInfo[iCount - 2].quantity = Convert.ToDecimal(excel.GetCell(iCount, "D"));
* lotExtendedInfo[iCount - 2].price = Convert.ToDecimal(excel.GetCell(iCount, "E"));
* lotExtendedInfo[iCount - 2].sum = Convert.ToDecimal(excel.GetCell(iCount, "F"));
* lotExtendedInfo[iCount - 2].country = excel.GetCell(iCount, "G");
* lotExtendedInfo[iCount - 2].techspec = excel.GetCell(iCount, "H");
* lotExtendedInfo[iCount - 2].terms = excel.GetCell(iCount, "I");
* lotExtendedInfo[iCount - 2].paymentterm = excel.GetCell(iCount, "J");
* lotExtendedInfo[iCount - 2].dks = Convert.ToInt32(excel.GetCell(iCount, "K"));
* lotExtendedInfo[iCount - 2].contractnumber = excel.GetCell(iCount, "L");*/
lotExtendedInfo[iCount - 2].endprice = Convert.ToDecimal(excel.GetCell(iCount, "M"));
lotExtendedInfo[iCount - 2].endsum = Convert.ToDecimal(excel.GetCell(iCount, "N"));
}
else
{
lotExtendedResult = new List <LotsExtended>();
return(false);
}
}
// Close document
excel.CloseWorkbook(true);
excel.CloseExcel();
// Return
lotExtendedResult = new List <LotsExtended>(lotExtendedInfo);
return(true);
}
private void ParseOrder()
{
excel = new ExcelService(fPath);
failedAct.sourceNumber = "Введите пожалуйста исходящий номер";
failedAct.fromDate = DateTime.Now.ToShortDateString();
failedAct.auctionDate = excel.GetCell("9", "A");
failedAct.orderNumber = excel.GetCell("7", "A");
failedAct.orderNumber = failedAct.orderNumber.Substring(failedAct.orderNumber.IndexOf("№"), failedAct.orderNumber.Length - failedAct.orderNumber.IndexOf("№"));
failedAct.orderNumber = failedAct.orderNumber.Substring(1, failedAct.orderNumber.LastIndexOf(" "));
fName = failedAct.orderNumber.Substring(0, failedAct.orderNumber.IndexOf("от") - 1);
if (fName.Length > 4)
{
fName = fName.Substring(fName.Length - 4, 4);
}
excel.CloseWorkbook(false);
excel.CloseExcel();
}
private static void FillCoverLetter()
{
// Fill cover letter
excel = new ExcelService(orderFiles[1]);
excel.SetCells(3, "A", "ЗАЯВКА №" + order.Auction.Number); // Order number
excel.SetCells(7, "E", order.Auction.Customer); // Customer name
excel.SetCells(11, "B", order.Auction.Lots[0].Name); // Lot name
excel.SetCells(11, "D", order.Auction.Lots[0].Unit); // Unit of size
excel.SetCells(11, "E", order.Auction.Lots[0].UnitId != 11 ? order.Auction.Lots[0].Quantity.ToString() : order.Auction.Lots[0].Unit); // Quantity
excel.SetCells(11, "F", (order.Auction.Lots[0].UnitId == 11 ? "По приложению" : order.Auction.Lots[0].Price.ToString())); // Price
excel.SetCells(11, "H", order.Auction.Lots[0].Sum); // Sum
excel.SetCells(12, "H", order.Auction.Lots[0].Sum); // Amount sum
excel.SetCells(18, "A", "\"" + order.Date.Day + "\" " + GetMonthName(order.Date.Month) + " " + order.Date.Year + " г."); // Order date
// Convert cover letter to pdf
excel.SaveAsPDF(orderFiles[1], false);
// Close cover letter
excel.CloseWorkbook(true);
excel.CloseExcel();
}
public static List <JournalC01Company> GetCompanies(string brokerCode)
{
var journalC01Companies = new List <JournalC01Company>();
var excel = new ExcelService(FileArchiveTransport.GetJournalC01FileName());
excel.SetActiveSheetByName(brokerCode);
for (var iRow = excel.GetRowsCount(); iRow > 0; iRow--)
{
var name = excel.GetCell(iRow, "C");
if (!string.IsNullOrEmpty(name))
{
journalC01Companies.Add(new JournalC01Company {
name = name, bin = excel.GetCell(iRow, "D"), code = excel.GetCell(iRow, "E")
});
}
}
excel.CloseWorkbook(false);
excel.CloseExcel();
return(journalC01Companies);
}
public void GetInfoForCommissionDocument()
{
// Show path to client order and open them
excel = new ExcelService(GetPathWithOrder());
// Get order number and date
if (excel.GetCell(7, "A").Contains("ВЦМ"))
{
auctionNumber = excel.GetCell(7, "A").Substring(excel.GetCell(7, "A").IndexOf("ВЦМ") - 1);
}
else
{
auctionNumber = "";
}
auctionDate = excel.GetCell(9, "A");
// Close order
excel.CloseWorkbook(false);
excel.CloseExcel();
// Paste to commission doc
PasteInfoToCommissionDoc();
}
private static void FillDocument(string fileName, List <Document> documentsList)
{
ExcelService excel = new ExcelService(fileName, true);
// Make title
string docType = documentsList[0].name.ToLower();
string titleEnd = docType.Contains("догов") ? "договоров" : docType.Contains("счет") ? "счет-фактур" : "документов";
excel.MergeCells(2, 1, 2, 5);
excel.SetCells(2, 1, "РЕЕСТР " + titleEnd.ToUpper());
excel.SetCellFontName(2, 1, "Times New Roman");
excel.SetCellBoldStyle(2, 1, true);
excel.SetCellFontHAlignment(2, 1, 2);
// Make Headers
excel.SetCells(4, 1, "Архивный номер");
excel.SetCellWrapText(4, 1, true);
excel.SetCells(4, 2, "Компания (контрагент)");
excel.SetCells(4, 3, "Номер документа");
excel.SetCells(4, 4, "Дата создания");
excel.SetCells(4, 5, "Тип документа");
for (var i = 1; i < 6; i++)
{
excel.SetCellBoldStyle(4, i, true);
excel.SetCellBorder(4, i);
excel.SetCellFontHAlignment(4, i, 2);
excel.SetCellFontVAlignment(4, i, 2);
excel.SetCellBackgroundColor(4, i, System.Drawing.Color.FromArgb(248, 203, 173));
}
// Fill content
int iCount = 5;
foreach (var item in documentsList)
{
excel.SetCells(iCount, 1, item.serialNumber);
excel.SetCellFontHAlignment(iCount, 1, 2);
excel.SetCells(iCount, 2, item.company);
excel.SetCellWrapText(iCount, 2, true);
excel.SetCells(iCount, 3, item.number);
excel.SetCellFontHAlignment(iCount, 3, 2);
excel.SetCells(iCount, 4, item.createdDate.ToShortDateString());
excel.SetCells(iCount, 5, item.name);
excel.SetCellWrapText(iCount, 5, true);
for (var i = 1; i < 6; i++)
{
excel.SetCellBorder(iCount, i);
excel.SetCellFontVAlignment(iCount, i, 2);
}
excel.SetRowAutoFit(iCount, 1);
iCount++;
}
// Last configurations
excel.SetCellFontName(0, 0, "Times New Roman", true);
excel.SetCellFontSize(0, 0, 9, true);
excel.SetCellFontSize(2, 1, 12);
excel.SetColumnWidth(1, 1, 8);
excel.SetColumnWidth(1, 2, 34);
excel.SetColumnWidth(1, 3, 16);
excel.SetColumnWidth(1, 4, 13);
excel.SetColumnWidth(1, 5, 21);
// Close excel
excel.CloseWorkbook(true);
excel.CloseExcel();
}
// Закрываем и сохраняем изменения в Excel
public void Close()
{
service.CloseWorkbook(true);
service.CloseExcel();
}
private static void Filldocument(int mode)
{
// Open excel
excel = new ExcelService(fileName);
switch (mode)
{
case 1: // Envelop
iRow = 1;
iCount = 1;
foreach (var item in postRegister)
{
// Broker info
excel.SetCells(iRow, "B", broker.Name);
excel.SetCells(iRow + 1, "B", broker.Index);
excel.SetCells(iRow + 2, "B", broker.Address);
excel.SetCells(iRow + 3, "B", broker.Phones);
// Serial number
excel.SetCells(iRow, "D", iCount);
// Client info
excel.SetCells(iRow + 12, "D", item.name);
excel.SetCells(iRow + 13, "D", item.index);
excel.SetCells(iRow + 14, "D", item.address);
excel.SetCells(iRow + 15, "D", item.phones.Length > 44 ? item.phones.Substring(0, 44) : item.phones);
if (iCount != postRegister.Count)
{
excel.CopyRange(1, "A", 16, "D", iRow + 17);
}
iRow += 18;
iCount++;
}
break;
case 2: // Notification
iRow = 4;
iCount = 1;
foreach (var item in postRegister)
{
// Broker info
excel.SetCells(iRow, "F", broker.Name);
excel.SetCells(iRow + 2, "F", broker.Address);
// Broker index
excel.SetCells(iRow + 4, "D", broker.Index.Substring(0, 1));
excel.SetCells(iRow + 4, "E", broker.Index.Substring(1, 1));
excel.SetCells(iRow + 4, "F", broker.Index.Substring(2, 1));
excel.SetCells(iRow + 4, "G", broker.Index.Substring(3, 1));
excel.SetCells(iRow + 4, "H", broker.Index.Substring(4, 1));
excel.SetCells(iRow + 4, "I", broker.Index.Substring(5, 1));
// Client info
excel.SetCells(iRow + 4, "AK", item.name);
excel.SetCells(iRow + 8, "AK", item.address);
// Client index
try {
excel.SetCells(iRow + 11, "AK", item.index.Substring(0, 1));
excel.SetCells(iRow + 11, "AL", item.index.Substring(1, 1));
excel.SetCells(iRow + 11, "AM", item.index.Substring(2, 1));
excel.SetCells(iRow + 11, "AN", item.index.Substring(3, 1));
excel.SetCells(iRow + 11, "AO", item.index.Substring(4, 1));
excel.SetCells(iRow + 11, "AP", item.index.Substring(5, 1));
} catch { }
if (iCount != postRegister.Count)
{
excel.CopyRange(1, "A", 34, "BE", iRow + 34);
}
iRow += 38;
iCount++;
}
break;
case 3: // Register
excel.SetCells(4, "B", broker.Name);
if (broker.Name.ToLower().Contains("корунд"))
{
excel.SetCells(2, "E", "№0599/11.1-20-1550 от 27.07.2012г.");
}
else if (broker.Name.ToLower().Contains("альта"))
{
excel.SetCells(2, "E", "№0599/11.1-14-698 от 01.04.2015г.");
}
else if (broker.Name.ToLower().Contains("алтын"))
{
excel.SetCells(2, "E", "№0599/11.1-08-749 от 15.03.2016г.");
}
iRow = 9;
foreach (var item in postRegister)
{
excel.SetCells(iRow, "A", (iRow - 8).ToString());
excel.SetCells(iRow, "B", item.name);
excel.SetCells(iRow, "C", item.index);
excel.SetCells(iRow, "D", item.address);
excel.SetCells(iRow, "E", item.phones);
excel.SetCells(iRow, "F", item.code);
excel.InsertRow(iRow + 1);
iRow++;
}
break;
}
// Close excel file
excel.CloseWorkbook(true);
excel.CloseExcel();
}
public static bool FillKaspiOrder(string templateFileName, Order order, string direction)
{
if (string.IsNullOrEmpty(templateFileName) || order == null || order.Auction == null || order.Auction.Lots == null || order.Auction.Lots.Count < 1 ||
order.Auction.Lots[0].LotsExtended == null || order.Auction.Lots[0].LotsExtended.Count < 1)
{
return(false);
}
var excel = new ExcelService(templateFileName);
if (excel == null)
{
return(false);
}
try {
excel.SetCells("4", "D", direction != null ? direction : "Продажа/покупка");
excel.SetCells("5", "D", order.Organizer != null ? order.Organizer.Replace("Товарищество с ограниченной ответственностью", "ТОО") : "-");
excel.SetCells("6", "D", order.Initiator != null ? order.Initiator.Replace("Товарищество с ограниченной ответственностью", "ТОО") : "-");
excel.SetCells("7", "D", order.Auction.Lots[0].Name != null ? order.Auction.Lots[0].Name : "-");
excel.SetCells("8", "D", order.Auction.Type != null ? order.Auction.Type : "-");
excel.SetCells("9", "D", order.Auction.Lots[0].LotsExtended[0].marka != null ? order.Auction.Lots[0].LotsExtended[0].marka : "-");
excel.SetCells("10", "D", order.Auction.Lots[0].LotsExtended[0].gost != null ? order.Auction.Lots[0].LotsExtended[0].gost : "-");
excel.SetCells("11", "D", order.Auction.Lots[0].CodeTRFEA != null ? order.Auction.Lots[0].CodeTRFEA : "-");
excel.SetCells("12", "D", order.Auction.Lots[0].LotsExtended[0].country != null ? order.Auction.Lots[0].LotsExtended[0].country : "-");
excel.SetCells("13", "D", order.Auction.Lots[0].LotsExtended[0].factory != null ? order.Auction.Lots[0].LotsExtended[0].factory : "-");
excel.SetCells("14", "D", order.Auction.Lots[0].LotsExtended.Count > 1 ? "Согласно спецификации" : (order.Auction.Lots[0].Quantity != null ? order.Auction.Lots[0].Quantity : 0).ToString());
excel.SetCells("15", "D", order.Auction.Lots[0].LotsExtended.Count > 1 ? "Согласно спецификации" : (order.Auction.Lots[0].Unit != null ? order.Auction.Lots[0].Unit : "-"));
excel.SetCells("16", "D", order.Auction.Lots[0].LotsExtended.Count > 1 ? "Согласно спецификации" : (order.Auction.Lots[0].Price != null ? order.Auction.Lots[0].Price : 0).ToString());
excel.SetCells("17", "D", order.Auction.Comments != null ? order.Auction.Comments : ""); // currency
excel.SetCells("18", "D", (order.Auction.Lots[0].Step != null ? order.Auction.Lots[0].Step : 0) + " " + excel.GetCell("17", "D"));
excel.SetCells("19", "D", order.Auction.Lots[0].DeliveryPlace != null ? order.Auction.Lots[0].DeliveryPlace : "");
excel.SetCells("20", "D", order.Auction.Lots[0].LotsExtended[0].terms != null ? order.Auction.Lots[0].LotsExtended[0].terms : "-");
excel.SetCells("21", "D", order.Auction.Lots[0].PaymentTerm != null ? order.Auction.Lots[0].PaymentTerm : "-");
excel.SetCells("22", "D", order.Auction.Lots[0].DeliveryTime != null ? order.Auction.Lots[0].DeliveryTime : "-");
excel.SetCells("23", "D", order.Auction.Date != null ? order.Auction.Date.ToShortDateString() : "-");
excel.SetCells("25", "D", order.Auction.Lots[0].LotsExtended[0].sum != null ? order.Auction.Lots[0].LotsExtended.Sum(l => l.sum) : 0);
excel.SetCells("26", "D", (order.Auction.InvoicePercent != null ? order.Auction.InvoicePercent : 0) + "%");
int rowCount = 31;
int iCount = 1;
foreach (var item in order.Auction.Lots[0].LotsExtended)
{
if (iCount > 1)
{
excel.InsertRow(rowCount);
}
excel.SetCells(rowCount, "B", iCount);
excel.SetCells(rowCount, "C", item.name != null ? item.name : "-");
excel.SetCells(rowCount, "D", item.unit != null ? item.unit : "-");
excel.SetCells(rowCount, "E", item.quantity != null ? item.quantity : 0);
excel.SetCells(rowCount, "F", item.price != null ? item.price : 0);
excel.SetCells(rowCount, "G", (item.quantity != null && item.price != null) ? (item.quantity * item.price) : 0);
iCount += 1;
rowCount += 1;
}
excel.SetCells(rowCount, "G", order.Auction.Lots[0].LotsExtended.Sum(l => l.sum));
} catch {
return(false);
} finally {
if (excel.IsWorkbookOpened())
{
excel.CloseWorkbook(true);
}
if (excel.IsExcelOpened())
{
excel.CloseExcel();
}
}
return(true);
}
private static void FillOrder()
{
// Fill order
excel = new ExcelService(orderFiles[0]);
excel.SetCells(4, "B", "Заявка №" + order.Auction.Number); // Auction number
if (order.customerid == 1)
{
excel.SetCells(7, "C", "ТОО 'Востокцветмет', Восточно - Казахстанская область, г Усть - Каменогорск, улица имени Александра Протозанова, 121, БИН: 140740012829, ИИК: KZ666010151000205076, АО 'Народный Банк Казахстан', БИК: HSBKKZKX"); // Customer data for VCM
excel.SetCells(30, "B", "Начальник отдела закупок "); // Head trader
excel.DeleteAllPictures();
}
else if (order.customerid == 7)
{
excel.SetCells(7, "C", "ТОО 'Полиметалл' Адрес: РК, ВКО, Жарминский район, поселок Ауэзов, Промышленная зона, БИН 930340000251, ИИК KZ23914102203KZ000A3 – KZT, Банковские реквизиты:, БИК SABRKZKA"); // Customer data for Polymetall
excel.SetCells(30, "B", "Директор службы снабжения ТОО 'Полиметалл'"); // Head trader
excel.DeleteAllPictures();
}
else if (order.customerid == 11)
{
excel.SetCells(7, "C", "ТОО 'Vertex Holding' Адрес: РК, г.Алматы, ул. Чайковского, 170, БИН 041240005077, ИИК KZ57722S000000127438"); // Customer data for V
excel.SetCells(30, "B", "Директор службы снабжения ТОО 'Vertex Holding'"); // Head trader
excel.DeleteAllPictures();
}
else if (order.customerid == 12)
{
excel.SetCells(7, "C", "ТОО 'Kerem Equipment LTD (Керем Иквипмент ЛТД)', Адрес: РК, г. Семей, ул. Интернациональная, 52, БИН 090640017369, ИИК KZ428560000003939474 - KZT"); // Customer data for Kerem
excel.SetCells(30, "B", "Директор службы снабжения ТОО 'Kerem Equipment LTD (Керем Иквипмент ЛТД)'"); // Head trader
excel.DeleteAllPictures();
}
else if (order.customerid == 14)
{
excel.SetCells(7, "C", "ТОО 'MADOT OIL', Адрес: РК, г. Алматы, ул. Кастеева 106 'В', БИН 161240022071, ИИК KZ619261802192594000 - KZT"); // Customer data for Kerem
excel.SetCells(30, "B", "Директор службы снабжения ТОО 'MADOT OIL'"); // Head trader
excel.DeleteAllPictures();
}
else if (order.customerid == 8)
{
excel.SetCells(7, "C", "ТОО 'M-Ali Petrol', Адрес: РК, г. Алматы, ул. Кастеева 106 В, БИН 161040025306, ИИК KZ559261802191065000 - KZT"); // Customer data for Kerem
excel.SetCells(30, "B", "Директор службы снабжения ТОО 'M-Ali Petrol'"); // Head trader
excel.DeleteAllPictures();
}
else if (order.customerid == 22)
{
excel.SetCells(7, "C", "ТОО 'Фэлкон Ойл энд Гэс ЛТД', Адрес: г. Алматы, Алмалинский район, ул. Шевченко 90, Бизнес-центр «Каратал», 9 этаж., БИН 000940000676, ИИК KZ268560000000523422 - KZT"); // Customer data for Kerem
excel.SetCells(30, "B", "Директор службы снабжения ТОО 'Фэлкон Ойл энд Гэс ЛТД'"); // Head trader
excel.DeleteAllPictures();
}
excel.SetCells(8, "C", order.Auction.Lots[0].Name); // Lot name
excel.SetCells(9, "C", order.Auction.Lots[0].CodeTRFEA); // TR FEA
excel.SetCells(10, "C", order.Auction.Lots[0].Name); // Lot description
excel.SetCells(11, "C", order.Auction.Lots[0].UnitId != 11 ? order.Auction.Lots[0].Price.ToString() : order.Auction.Lots[0].Unit); // Start price
excel.SetCells(12, "C", order.Auction.Lots[0].UnitId != 11 ? order.Auction.Lots[0].Quantity.ToString() : order.Auction.Lots[0].Unit); // Quantity
excel.SetCells(13, "C", order.Auction.Lots[0].Unit); // Unit of size
excel.SetCells(14, "C", order.Auction.Lots[0].Sum); // Amount sum
excel.SetCells(15, "C", order.Auction.Lots[0].Step); // Step
excel.SetCells(16, "C", order.Auction.Lots[0].PaymentTerm); // Payment
excel.SetCells(17, "C", order.Auction.Lots[0].DeliveryPlace); // Delivery term
excel.SetCells(18, "C", order.Auction.Lots[0].DeliveryTime); // Delivery time
excel.SetCells(20, "C", order.Auction.Lots[0].LocalContent); // Local
excel.SetCells(21, "C", order.Auction.Date.ToShortDateString()); // Auction date
excel.SetCells(23, "C", order.Auction.Lots[0].Warranty); // Warranty
excel.SetCells(25, "C", CountWorkDays(order.Auction.Date, 10)); // Order ellapse date
excel.SetCells(28, "B", "Дата " + order.Date.ToShortDateString()); // Order date
// Set cursor on first position
excel.SetRange(1, "A");
// Convert order to pdf
excel.SaveAsPDF(orderFiles[0]);
// Close order
excel.CloseWorkbook(true);
excel.CloseExcel();
}
public static void FormateProcuratory(string templateFileName, Order orderItem, bool autoCounting = false)
{
//IExcelService excel = new ExcelService(templateFileName);
IExcelService excel = new ExcelService(templateFileName);
// First page
excel.SetSheetByIndex(1);
// Main data
excel.SetCells(5, "D", DateTime.Now.ToShortDateString());
excel.SetCells(7, "D", orderItem.Auction.Date.ToShortDateString());
excel.SetCells(8, "D", orderItem.Auction.Number + " от " + orderItem.Date.ToShortDateString());
excel.SetCells(9, "D", orderItem.Auction.SupplierOrders[0].BrokerName);
excel.SetCells(10, "D", orderItem.Auction.SupplierOrders[0].BrokerCode);
excel.SetCells(11, "D", orderItem.Auction.SupplierOrders[0].Name);
excel.SetCells(12, "D", orderItem.Auction.SupplierOrders[0].Code);
excel.SetCells(37, "A", orderItem.Auction.Date.ToShortDateString());
excel.SetCells(37, "D", "11:00");
excel.SetCells(37, "H", orderItem.Auction.SupplierOrders[0].Trader);
// Lots data
int rowCount = 17;
int lotCount = 1;
decimal endSum = 0;
foreach (var item in orderItem.Auction.SupplierOrders[0].lots)
{
excel.SetCells(rowCount, "D", string.Format("Цена лота (стартовая - {0} с НДС)", item.Sum));
excel.SetCells(rowCount + 1, "A", item.Number);
excel.SetCells(rowCount + 1, "C", string.Format("Лот №{0}", lotCount));
endSum = 0;
if (orderItem.Auction.Procuratories != null && orderItem.Auction.Procuratories.Count > 0)
{
var procSum = orderItem.Auction.Procuratories.First(p => p.lotId == item.Id);
if (procSum != null)
{
endSum = procSum.MinimalPrice;
}
}
excel.SetCells(rowCount + 1, "D", endSum == 0 ? "Пожалуйста введите сумму понижения (с НДС)" : endSum.ToString());
excel.SetCellItalicStyle(rowCount + 1, "D", true);
excel.SetCellForegroundColor(rowCount + 1, "D", System.Drawing.Color.FromArgb(128, 128, 128));
rowCount += 2;
lotCount++;
}
// Tech spec pages
if (orderItem.Auction.SupplierOrders[0].lots[0].LotsExtended != null && orderItem.Auction.SupplierOrders[0].lots[0].LotsExtended.Count > 0)
{
FillTechSpecSheet(excel, orderItem, autoCounting);
}
// Close excel
excel.CloseWorkbook(true);
excel.CloseExcel();
}
public FormC01 InsertCompany(FormC01 form)
{
var excel = new ExcelService(path);
excel.SetActiveSheetByName(form.code.ToUpper());
var rowsCount = excel.GetRowsCount();
string number = null;
for (var iRow = rowsCount; iRow > 0; iRow--)
{
if (excel.GetCell(iRow, "B") != "")
{
number = excel.GetCell(iRow, "B");
}
}
if (string.IsNullOrEmpty(number))
{
return(null);
}
var iNumber = 0;
iNumber = int.Parse(number);
number = (iNumber + 1).ToString();
if (number.Length == 1)
{
number = number.Insert(0, "00");
}
else if (number.Length == 2)
{
number = number.Insert(0, "0");
}
else if (number.Length > 3)
{
number = "1";
}
form.number = number;
form.codeG = form.broker.code.ToLower() + "g" + form.number;
form.codeP = form.broker.code.ToLower() + "p" + form.number;
form.codeS = form.broker.code.ToLower() + "s" + form.number;
for (var iRow = rowsCount; iRow > 0; iRow--)
{
if (!string.IsNullOrEmpty(excel.GetCell(iRow, "B")))
{
excel.SetCells(iRow + 1, "A", form.date);
excel.SetCells(iRow + 1, "B", form.number);
excel.SetCells(iRow + 1, "C", form.name);
excel.SetCells(iRow + 1, "D", form.bin);
excel.SetCells(iRow + 1, "E", form.code);
excel.SetCells(iRow + 1, "F", form.codeG + " " + form.codeS);
excel.SetCells(iRow + 1, "G", form.codeP);
break;
}
}
excel.CloseWorkbook(true);
excel.CloseExcel();
return(form);
}
private static void FillDocument(string fileName, List <TechSpecReportBO> techSpecReport)
{
IExcelService excel = new ExcelService(fileName);
int iRow = 2, iCount = 1, startRow = 0;
string curLotCode = "", curAuctionNum = "";
bool IsNew = true;
decimal oldStartPrice = 0, oldFinalPrice = 0;
foreach (var item in techSpecReport)
{
try
{
if (curLotCode == item.lotCode && curAuctionNum == item.auctionNumber)
{
IsNew = false;
}
else
{
if (iRow != 2)
{
CountLastStroke(excel, startRow, iRow, oldFinalPrice);
excel.InsertRow(iRow + 1);
iRow++;
}
startRow = iRow;
curLotCode = item.lotCode;
curAuctionNum = item.auctionNumber;
IsNew = true;
}
if (iCount == techSpecReport.Count)
{
CountLastStroke(excel, startRow, iRow, oldFinalPrice); // Last line
}
if (IsNew)
{
excel.SetCells(iRow, "A", item.auctionNumber + " от " + item.orderDate.ToShortDateString());
excel.SetCells(iRow, "B", item.lotCode);
excel.SetCells(iRow, "I", item.dealNumber == null ? "" : item.dealNumber);
excel.SetCells(iRow, "J", item.name == null ? "" : item.name);
excel.SetCells(iRow, "K", item.auctionDate.ToShortDateString());
// TODO: Почему здесь изменяется дата?
excel.SetCells(iRow, "L", item.auctionDate.AddDays(1).ToShortDateString());
}
excel.SetCells(iRow, "C", item.productName);
excel.SetCells(iRow, "D", item.unit);
excel.SetCells(iRow, "E", item.quantity);
excel.SetCells(iRow, "F", item.price);
excel.SetCells(iRow, "G", string.Format("=F{0}*E{1}", iRow, iRow));
excel.SetCells(iRow, "H", "-");
excel.SetCells(iRow, "M", item.productFinalPrice == null ? 0 : item.productFinalPrice);
excel.SetCells(iRow, "N", string.Format("=M{0}*E{1}", iRow, iRow));
excel.SetCells(iRow, "O", "-");
excel.SetCells(iRow, "P", string.Format("=G{0}-N{1}", iRow, iRow));
excel.SetCells(iRow, "Q", string.Format("=P{0}/G{1}", iRow, iRow));
excel.SetCells(iRow, "R", item.contractNumber);
}
catch { }
excel.InsertRow(iRow + 1);
iRow++;
try
{
oldStartPrice = (decimal)item.startPriceOffer;
oldFinalPrice = (decimal)item.finalPriceOffer;
}
catch
{
oldStartPrice = 0;
oldFinalPrice = 0;
}
iCount++;
}
excel.CloseWorkbook(true);
excel.CloseExcel();
excel = null;
}
public static Order ParseKaspiOrder(string fileName)
{
if (string.IsNullOrEmpty(fileName))
{
return(null);
}
var excel = new ExcelService(fileName);
if (excel == null)
{
return(null);
}
Order order = new Order();
order.Auction = new Auction();
order.Auction.Lots = new System.Collections.ObjectModel.ObservableCollection <Lot>();
order.Auction.Lots.Add(new Lot()
{
LotsExtended = new System.Collections.ObjectModel.ObservableCollection <LotsExtended>()
});
bool result;
DateTime dateTime;
decimal decimalNumber;
double doubleNumber;
int intNumber;
try {
order.Organizer = excel.GetCell("5", "D");
order.Initiator = excel.GetCell("6", "D");
order.Auction.Lots[0].Name = excel.GetCell("7", "D");
order.Auction.Type = excel.GetCell("8", "D");
result = decimal.TryParse(excel.GetCell("14", "D"), out decimalNumber);
order.Auction.Lots[0].Quantity = result ? decimalNumber : 0;
order.Auction.Lots[0].Unit = excel.GetCell("15", "D");
result = decimal.TryParse(excel.GetCell("16", "D"), out decimalNumber);
order.Auction.Lots[0].Price = result ? decimalNumber : 0;
order.Auction.Comments = excel.GetCell("17", "D"); // currency
result = decimal.TryParse(excel.GetCell("18", "D"), out decimalNumber);
order.Auction.Lots[0].Step = result ? decimalNumber : 0;
order.Auction.Lots[0].DeliveryPlace = excel.GetCell("19", "D");
order.Auction.Lots[0].PaymentTerm = excel.GetCell("21", "D");
order.Auction.Lots[0].DeliveryTime = excel.GetCell("22", "D");
result = DateTime.TryParse(excel.GetCell("23", "D"), out dateTime);
order.Auction.Date = result ? dateTime : DateTime.Now;
result = decimal.TryParse(excel.GetCell("25", "D"), out decimalNumber);
order.Auction.Lots[0].Sum = result ? decimalNumber : 0;
result = double.TryParse(excel.GetCell("26", "D"), out doubleNumber);
order.Auction.InvoicePercent = result ? doubleNumber : 0;
int rowCount = 31;
while (!string.IsNullOrEmpty(excel.GetCell(rowCount, "B")) && !string.IsNullOrEmpty(excel.GetCell(rowCount, "D")))
{
LotsExtended lotsExtended = new LotsExtended();
result = Int32.TryParse(excel.GetCell(rowCount, "B"), out intNumber);
lotsExtended.serialnumber = result ? intNumber : 0;
lotsExtended.name = excel.GetCell(rowCount, "C");
lotsExtended.unit = excel.GetCell(rowCount, "D");
result = decimal.TryParse(excel.GetCell(rowCount, "E"), out decimalNumber);
lotsExtended.quantity = result ? decimalNumber : 0;
result = decimal.TryParse(excel.GetCell(rowCount, "F"), out decimalNumber);
lotsExtended.price = result ? decimalNumber : 0;
lotsExtended.sum = lotsExtended.quantity * lotsExtended.price;
lotsExtended.marka = excel.GetCell("9", "D");
lotsExtended.gost = excel.GetCell("10", "D");
lotsExtended.codeTNVD = excel.GetCell("11", "D");
lotsExtended.country = excel.GetCell("12", "D");
lotsExtended.factory = excel.GetCell("13", "D");
lotsExtended.terms = excel.GetCell("20", "D");
order.Auction.Lots[0].LotsExtended.Add(lotsExtended);
}
} catch {
return(null);
} finally {
if (excel.IsWorkbookOpened())
{
excel.CloseWorkbook();
}
if (excel.IsExcelOpened())
{
excel.CloseExcel();
}
}
return(order);
}
public static ObservableCollection <LotsExtended> ParseKaspiTechSpec(string fileName)
{
if (string.IsNullOrEmpty(fileName))
{
return(null);
}
var excel = new ExcelService(fileName);
if (excel == null)
{
return(null);
}
ObservableCollection <LotsExtended> lotsExtendedList = new ObservableCollection <LotsExtended>();
bool result;
decimal decimalNumber;
int intNumber;
try {
var rowCount = excel.FindRow("Наименование товара");
if (rowCount == null || rowCount == 0)
{
throw new Exception();
}
rowCount += 2;
while (!string.IsNullOrEmpty(excel.GetCell(rowCount, "C")) && !string.IsNullOrEmpty(excel.GetCell(rowCount, "E")))
{
LotsExtended lotsExtended = new LotsExtended();
result = Int32.TryParse(excel.GetCell(rowCount, "C"), out intNumber);
lotsExtended.serialnumber = result ? intNumber : 0;
lotsExtended.name = excel.GetCell(rowCount, "D");
lotsExtended.unit = excel.GetCell(rowCount, "E");
result = decimal.TryParse(excel.GetCell(rowCount, "F"), out decimalNumber);
lotsExtended.quantity = result ? decimalNumber : 0;
result = decimal.TryParse(excel.GetCell(rowCount, "H"), out decimalNumber);
lotsExtended.price = result ? decimalNumber : 0;
lotsExtended.sum = lotsExtended.quantity * lotsExtended.price;
lotsExtended.terms = excel.GetCell(rowCount, "K");
lotsExtendedList.Add(lotsExtended);
rowCount += 1;
}
} catch {
return(null);
} finally {
if (excel.IsWorkbookOpened())
{
excel.CloseWorkbook();
}
if (excel.IsExcelOpened())
{
excel.CloseExcel();
}
}
return(lotsExtendedList);
}
public static Lot GetLotData(string orderFileName)
{
Lot lot = new Lot();
// Open file
excel = new ExcelService(orderFileName);
// Check for sheets count
if (excel.GetSheetsCount(false) < 3)
{
excel.AddSheet("");
}
// Parse file and get data
excel.SetSheetByIndex(1);
// Check for space between hat and broker
if (string.IsNullOrEmpty(excel.GetCell(4, "B")))
{
excel.InsertRow(4);
}
lot.Number = "1";
lot.Name = excel.GetCell(9, "C");
var quantityCell = excel.GetCell(13, "C");
try {
if (quantityCell != null)
{
lot.Quantity = string.IsNullOrEmpty(quantityCell) ? 0 : quantityCell.ToLower().Contains("прилож") ? 0 : Convert.ToDecimal(quantityCell);
}
else
{
lot.Quantity = 0;
}
} catch { lot.Quantity = 0; }
Regex regex = new Regex(@"[\d]*[,]*[\d]*");
lot.Price = lot.Quantity == 0 ? 0 : Convert.ToDecimal(regex.Match(excel.GetCell(12, "C").Replace(" ", "")).Value);
lot.Sum = lot.Quantity == 0 ? Convert.ToDecimal(regex.Match(excel.GetCell(15, "C").Replace(" ", "")).Value) : lot.Quantity * lot.Price;
try {
lot.Step = Convert.ToDecimal(regex.Match(excel.GetCell(16, "C")).Value);
} catch { lot.Step = 0; }
lot.PaymentTerm = excel.GetCell(17, "C");
lot.DeliveryPlace = excel.GetCell(18, "C");
lot.DeliveryTime = excel.GetCell(19, "C");
lot.LocalContent = Convert.ToDecimal(regex.Match(excel.GetCell(21, "C")).Value);
lot.Warranty = Convert.ToDecimal(regex.Match(excel.GetCell(24, "C")).Value);
// Parse tech spec part for lot ex
excel.SetSheetByIndex(2);
lot.LotsExtended = new System.Collections.ObjectModel.ObservableCollection <LotsExtended>();
int startRow = excel.FindRow("Наименование");
if (startRow != 0)
{
int iRow = startRow + 1;
int iCount = 1;
while (!string.IsNullOrEmpty(excel.GetCell(iRow, "A")) || !string.IsNullOrEmpty(excel.GetCell(iRow, "B")))
{
lot.LotsExtended.Add(new LotsExtended()
{
serialnumber = iCount,
name = excel.GetCell(iRow, "B"),
unit = excel.GetCell(iRow, "C"),
quantity = Convert.ToDecimal(excel.GetCell(iRow, "D")),
price = Convert.ToDecimal(excel.GetCell(iRow, "E")),
sum = Convert.ToDecimal(excel.GetCell(iRow, "F")),
country = string.IsNullOrEmpty(excel.GetCell(iRow, "G")) ? "" : excel.GetCell(iRow, "G"),
techspec = excel.GetCell(iRow, "H"),
terms = excel.GetCell(iRow, "I"),
paymentterm = excel.GetCell(iRow, "J"),
dks = Convert.ToInt32(Math.Round(Convert.ToDecimal(regex.Match(excel.GetCell(iRow, "K")).Value), 0)),
contractnumber = string.IsNullOrEmpty(excel.GetCell(iRow, "L")) ? "" : excel.GetCell(iRow, "L")
});
iCount++;
iRow++;
}
lot.Dks = lot.LotsExtended[0].dks;
}
// close excel
excel.CloseWorkbook(false);
excel.CloseExcel();
// Return collected data
return(lot);
}
public static List <LotsExtended> ParseTechSpec(string sourceFileName, string lotCode)
{
// Open file in excel
IExcelService excel = new ExcelService(sourceFileName);
// Check for right data structure
excel.SetSheetByIndex(1);
var procTest = excel.FindRow("Поручение");
if (procTest == 0)
{
return(null);
}
// Get sheets count
int sheetsCount = excel.GetSheetsCount();
List <LotsExtended> lotExList = new List <LotsExtended>();
// Parse tables for getting data
for (var curSheet = 2; curSheet == sheetsCount; curSheet++)
{
excel.SetSheetByIndex(curSheet);
var lotTest = excel.FindRow(lotCode);
if (lotTest != 0)
{
int rowCount = 4;
while (!string.IsNullOrEmpty(excel.GetCell(rowCount, "A")))
{
LotsExtended lotExItem = new LotsExtended()
{
serialnumber = Convert.ToInt32(excel.GetCell(rowCount, "A")),
name = excel.GetCell(rowCount, "B"),
unit = excel.GetCell(rowCount, "C"),
quantity = Convert.ToDecimal(excel.GetCell(rowCount, "D")),
price = Convert.ToDecimal(excel.GetCell(rowCount, "E")),
sum = Convert.ToDecimal(excel.GetCell(rowCount, "F")),
country = excel.GetCell(rowCount, "G"),
techspec = excel.GetCell(rowCount, "H"),
terms = excel.GetCell(rowCount, "I"),
paymentterm = excel.GetCell(rowCount, "J"),
dks = Convert.ToInt32(excel.GetCell(rowCount, "K")),
contractnumber = excel.GetCell(rowCount, "L"),
endprice = Convert.ToDecimal(excel.GetCell(rowCount, "M")),
endsum = Convert.ToDecimal(excel.GetCell(rowCount, "N"))
};
lotExList.Add(lotExItem);
rowCount++;
}
}
}
// Close excel
excel.CloseWorkbook();
excel.CloseExcel();
return(lotExList);
}
public static bool FillAttach(string templateFileName, Order order, string dealNumber = "", string customerBin = "", string brokerCustomerBin = "")
{
if (string.IsNullOrEmpty(templateFileName) || order == null || order.Auction == null || order.Auction.Lots == null || order.Auction.Lots.Count < 1 ||
order.Auction.Lots[0].LotsExtended == null || order.Auction.Lots[0].LotsExtended.Count < 1)
{
return(false);
}
var excel = new ExcelService(templateFileName);
if (excel == null)
{
return(false);
}
try {
excel.SetCells(3, "C", order.Auction.Date != null ? order.Auction.Date.ToShortDateString() : "");
excel.SetCells(4, "C", order.Number != null ? order.Number : "");
excel.SetCells(5, "C", dealNumber != null ? dealNumber : "");
excel.SetCells(7, "B", order.Initiator != null ? order.Initiator : "");
excel.SetCells(8, "B", order.Organizer != null ? order.Organizer : "");
excel.SetCells(7, "E", "БИН/ИИН " + customerBin != null ? customerBin : "");
excel.SetCells(8, "E", "БИН/ИИН " + brokerCustomerBin != null ? brokerCustomerBin : "");
var supplierOrder = order.Auction.SupplierOrders != null?order.Auction.SupplierOrders.FirstOrDefault(s => s.status != null && s.status.Id != null && s.status.Id == 23) : new SupplierOrder();
excel.SetCells(7, "H", supplierOrder != null ? supplierOrder.Name != null ? supplierOrder.Name : "" : "");
excel.SetCells(8, "H", supplierOrder != null ? supplierOrder.BrokerName != null ? supplierOrder.BrokerName : "" : "");
excel.SetCells(7, "L", "БИН/ИИН " + supplierOrder != null ? supplierOrder.BIN != null ? supplierOrder.BIN : "" : "");
excel.SetCells(8, "L", "БИН/ИИН " + supplierOrder != null ? supplierOrder.BrokerBIN != null ? supplierOrder.BrokerBIN : "" : "");
int rowCount = 10;
int iCount = 1;
foreach (var item in order.Auction.Lots[0].LotsExtended)
{
if (iCount > 1)
{
excel.InsertRow(rowCount + 1);
}
excel.SetCells(rowCount, "A", iCount);
excel.SetCells(rowCount, "B", item.name != null ? item.name : "");
excel.SetCells(rowCount, "C", item.unit != null ? item.unit : "");
excel.SetCells(rowCount, "D", item.quantity != null ? item.quantity : 0);
excel.SetCells(rowCount, "E", ""); // currency
excel.SetCells(rowCount, "F", item.price != null ? item.price : 0);
excel.SetCells(rowCount, "G", (item.price != null && item.quantity != null) ? item.price * item.quantity : 0);
excel.SetCells(rowCount, "H", item.endprice != null ? item.endprice : 0);
excel.SetCells(rowCount, "I", (item.endprice != null && item.quantity != null) ? item.endprice * item.quantity : 0);
excel.SetCells(rowCount, "J", (item.endsum != null && item.sum != null) ? item.endsum - item.sum : 0);
excel.SetCells(rowCount, "K", order.Auction.Lots[0].DeliveryTime != null ? order.Auction.Lots[0].DeliveryTime : "");
excel.SetCells(rowCount, "L", item.terms != null ? item.terms : "");
excel.SetCells(rowCount, "M", order.Auction.Lots[0].PaymentTerm != null ? order.Auction.Lots[0].PaymentTerm : "");
rowCount += 1;
iCount += 1;
}
excel.SetCells(rowCount, "G", order.Auction.Lots[0].LotsExtended.Sum(l => l.sum));
excel.SetCells(rowCount, "I", order.Auction.Lots[0].LotsExtended.Sum(l => l.endsum));
excel.SetCells(rowCount + 2, "C", order.Auction.Lots[0].LotsExtended.Sum(l => l.endsum));
excel.CloseWorkbook(true);
excel.CloseExcel();
} catch {
if (excel.IsWorkbookOpened())
{
excel.CloseWorkbook();
}
if (excel.IsExcelOpened())
{
excel.CloseExcel();
}
return(false);
}
return(true);
}
private static void FillTemplateForKazETS(Order order, string fileName, string coverLetterFileName, string techSpecFile, bool withPDF = false)
{
// Fill order
//excel = new ExcelService(fileName);
IExcelService excel = new ExcelService(fileName);
string supplierData = order.Auction.SupplierOrders[0].Name + "\n" + order.Auction.SupplierOrders[0].Address + "\nБИН " + order.Auction.SupplierOrders[0].BIN + "\nИИК " + order.Auction.SupplierOrders[0].IIK + "\n" + order.Auction.SupplierOrders[0].BankName + "\nБИК " + order.Auction.SupplierOrders[0].BIK;
excel.SetCells(4, "B", "Заявка №" + order.Auction.Number); // Auction number
excel.SetCells(7, "C", supplierData); // Supplier data
excel.SetCells(8, "C", order.Auction.Lots[0].Name); // Lot name
excel.SetCells(9, "C", order.Auction.Lots[0].CodeTRFEA); // TR FEA
excel.SetCells(10, "C", order.Auction.Lots[0].Name); // Lot description
excel.SetCells(11, "C", order.Auction.Lots[0].UnitId != 11 ? Math.Round(order.Auction.Procuratories[0].MinimalPrice / order.Auction.Lots[0].Quantity, 2).ToString() : order.Auction.Lots[0].Unit); // Start price
excel.SetCells(12, "C", order.Auction.Lots[0].UnitId != 11 ? order.Auction.Lots[0].Quantity.ToString() : order.Auction.Lots[0].Unit); // Quantity
excel.SetCells(13, "C", order.Auction.Lots[0].Unit); // Unit of size
excel.SetCells(14, "C", order.Auction.Procuratories[0].MinimalPrice); // Amount sum
excel.SetCells(15, "C", order.Auction.Lots[0].Step); // Step
excel.SetCells(16, "C", order.Auction.Lots[0].PaymentTerm); // Payment term
excel.SetCells(17, "C", order.Auction.Lots[0].DeliveryPlace); // Delivery terms
excel.SetCells(18, "C", order.Auction.Lots[0].DeliveryTime); // Delivery time
excel.SetCells(20, "C", order.Auction.Lots[0].LocalContent); // Local content
excel.SetCells(21, "C", order.Auction.Date.ToShortDateString()); // Auction date
excel.SetCells(23, "C", order.Auction.Lots[0].Warranty); // Warranty
excel.SetCells(25, "C", CountWorkDays(order.Auction.Date, 10)); // Order ellapse date
// If supplier is alternative then need make one day later than private supplier
DateTime tmpDate = order.Date;
if (order.Auction.SupplierOrders[0].Name == order.Auction.SupplierOrders[0].BrokerName)
{
tmpDate = order.Deadline;
}
else
{
tmpDate = order.Deadline.AddDays(-1);
}
if (tmpDate.DayOfWeek == DayOfWeek.Sunday)
{
tmpDate = tmpDate.AddDays(-2);
}
else if (tmpDate.DayOfWeek == DayOfWeek.Saturday)
{
tmpDate = tmpDate.AddDays(-1);
}
//excel.SetCells(28, "B", "Дата \"" + tmpDate.Day + "\" " + GetMonthName(tmpDate.Month) + " " + tmpDate.Year + " г."); // Supplier order date
excel.CloseWorkbook(true);
excel.CloseExcel();
// Fill cover letter
//excel = new ExcelService(coverLetterFileName);
excel = new ExcelService(coverLetterFileName);
excel.SetCells(3, "A", "ЗАЯВКА №" + order.Auction.Number); // Order number
excel.SetCells(7, "E", order.Auction.SupplierOrders[0].Name); // Supplier name
excel.SetCells(11, "B", order.Auction.Lots[0].Name); // Lot name
excel.SetCells(11, "D", order.Auction.Lots[0].Unit); // Unit of size
excel.SetCells(11, "E", order.Auction.Lots[0].UnitId != 11 ? order.Auction.Lots[0].Quantity.ToString() : order.Auction.Lots[0].Unit); // Quantity
excel.SetCells(11, "F", (order.Auction.Lots[0].UnitId == 11 ? "По приложению" : (order.Auction.Procuratories[0].MinimalPrice / order.Auction.Lots[0].Quantity).ToString())); // Price
excel.SetCells(11, "H", order.Auction.Procuratories[0].MinimalPrice); // Sum
excel.SetCells(12, "H", order.Auction.Procuratories[0].MinimalPrice); // Amount sum
excel.SetCells(18, "A", "\"" + tmpDate.Day + "\" " + GetMonthName(tmpDate.Month) + " " + tmpDate.Year + " г."); // Order date
// Convert cover letter to pdf
if (withPDF)
{
excel.SaveAsPDF(coverLetterFileName, false);
}
// Close cover letter
excel.CloseWorkbook(true);
excel.CloseExcel();
// Fill tech spec
WordService word = new WordService(techSpecFile, false);
word.FindReplace("[auctionNumber]", order.Auction.Number); // Auction number
word.SetCell(1, 2, 1, "1"); // Number
word.SetCell(1, 2, 2, order.Auction.Lots[0].Name); // Name
word.SetCell(1, 2, 3, order.Auction.Lots[0].Unit); // Unit of size
word.SetCell(1, 2, 4, order.Auction.Lots[0].Quantity.ToString()); // Quantity
word.SetCell(1, 2, 5, (order.Auction.Procuratories[0].MinimalPrice / order.Auction.Lots[0].Quantity).ToString()); // Price
word.SetCell(1, 2, 6, order.Auction.Procuratories[0].MinimalPrice.ToString()); // Sum
word.SetCell(1, 2, 7, order.Auction.Lots[0].Step.ToString()); // Step
word.SetCell(1, 2, 8, order.Auction.Lots[0].DeliveryPlace + " | " + order.Auction.Lots[0].DeliveryTime); // Terms
word.SetCell(1, 2, 9, order.Auction.Lots[0].PaymentTerm); // Payment
word.SetCell(1, 2, 10, order.Auction.Lots[0].Warranty.ToString()); // Warranty
word.SetCell(1, 2, 11, order.Auction.Lots[0].LocalContent.ToString()); // Local
// Close specification
word.CloseDocument(true);
word.CloseWord(true);
}
