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);
        }
Ejemplo n.º 2
0
        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);
            }
        }
Ejemplo n.º 3
0
        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();
        }
Ejemplo n.º 4
0
 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;
        }
Ejemplo n.º 7
0
        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();
        }
Ejemplo n.º 11
0
        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);
        }
Ejemplo n.º 13
0
        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();
        }
Ejemplo n.º 14
0
        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();
        }
Ejemplo n.º 15
0
 // Закрываем и сохраняем изменения в 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);
        }
Ejemplo n.º 18
0
        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();
        }
Ejemplo n.º 19
0
        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();
        }
Ejemplo n.º 20
0
        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);
        }
Ejemplo n.º 24
0
        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);
        }
Ejemplo n.º 25
0
        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);
        }
Ejemplo n.º 26
0
        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);
        }
Ejemplo n.º 27
0
        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);
        }