private void CreateNote(InputCompany inputCompany, WhiteListVerResult whiteListVerResult, string outputPath, bool createPDF) { string fileName = string.Empty; string templPath = string.Format("{0}\\{1}", Environment.CurrentDirectory, "templates\\NotaTemplate.xltx"); _theWorkbook = _app.Workbooks.Add(templPath); Sheets sheets = _theWorkbook.Worksheets; Worksheet _worksheet = (Worksheet)sheets.get_Item(1); FillInWorksheetWithData(ref _worksheet, inputCompany, whiteListVerResult); fileName = whiteListVerResult.FullName.Replace(_space, _underscore); fileName = fileName.Replace(_dot, string.Empty); fileName = _fileNamePattern.IsMatch(fileName) ? _fileNamePattern.Match(fileName).Value : fileName; fileName = fileName.Length > _lengthOfFullNameInFileNameWithNote?fileName.Substring(0, _lengthOfFullNameInFileNameWithNote) : fileName; string noteID = _noteIDPattern.IsMatch(inputCompany.NoteID) ? _noteIDPattern.Match(inputCompany.NoteID).Value : string.Empty; fileName = string.Format("{0}-{1}-{2}", inputCompany.RowNumber, noteID, fileName); if (createPDF) { string pdfFilePath = string.Format("{0}\\{1}\\{2}.pdf", outputPath, _pdfDirName, fileName); _theWorkbook.ExportAsFixedFormat2(XlFixedFormatType.xlTypePDF, pdfFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false, Type.Missing, Type.Missing); } string xlsFilePath = string.Format("{0}\\{1}.xlsx", outputPath, fileName); _theWorkbook.SaveAs(xlsFilePath, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); _theWorkbook.Close(true, Type.Missing, Type.Missing); }
private void FillInWorksheetWithData(ref Worksheet worksheet, InputCompany inputCompany, WhiteListVerResult whiteListVerResult) { if (inputCompany.NoteID.Length < 4) // means that only number is given { ((Range)worksheet.Cells[3, 1]).Formula = string.Format($"nr {inputCompany.NoteID}/{DateTime.Now.Month}/{DateTime.Now.Year}"); } else //means that the full number was given in the input file { ((Range)worksheet.Cells[3, 1]).Formula = string.Format($"nr {inputCompany.NoteID}"); } if (string.IsNullOrEmpty(inputCompany.NoteDate)) { ((Range)worksheet.Cells[3, 9]).Formula = DateTime.Now.ToString("dd.MM.yyyyr."); } else { ((Range)worksheet.Cells[3, 9]).Formula = string.Format($"{inputCompany.NoteDate}"); } ((Range)worksheet.Cells[4, 9]).Formula = GetPaymentPeriod(inputCompany.NoteTitle); string fullNameWithAbbr = FormatHelper.AbbreviateFullNameOfCompany(whiteListVerResult.FullName); fullNameWithAbbr = fullNameWithAbbr.Replace("\"", string.Empty); int numOfLinesForFullNames = 1 + (fullNameWithAbbr.Length / _maxLengthOfLineInFile); for (int j = 0; j < numOfLinesForFullNames; j++) { string temp = fullNameWithAbbr.Substring(j * _maxLengthOfLineInFile); temp = temp.Substring(0, Math.Min(_maxLengthOfLineInFile, temp.Length)).Trim(); ((Range)worksheet.Cells[8 + j, 8]).Formula = temp; } string addressFull = string.IsNullOrEmpty(whiteListVerResult.FullResidenceAddress) ? whiteListVerResult.FullWorkingAddress : whiteListVerResult.FullResidenceAddress; addressFull = string.Concat(AddressHelper.GetStreetPrefix(addressFull), _space, addressFull).Trim(); addressFull = addressFull.Replace(_colon, string.Empty); int addressDivIndex = _postalCodePattern.Match(addressFull).Index; ((Range)worksheet.Cells[8 + numOfLinesForFullNames, 8]).Formula = addressFull.Substring(0, addressDivIndex).Trim(); ((Range)worksheet.Cells[9 + numOfLinesForFullNames, 8]).Formula = addressFull.Substring(addressDivIndex).Trim(); ((Range)worksheet.Cells[14, 9]).Formula = whiteListVerResult.Nip.Insert(3, _space).Insert(6, _space).Insert(9, _space); ((Range)worksheet.Cells[17, 9]).Formula = FormatHelper.GetAccountNumberInString(whiteListVerResult.GivenAccountNumber); ((Range)worksheet.Cells[21, 9]).Formula = inputCompany.NoteNettoAmount; ((Range)worksheet.Cells[24, 3]).Formula = inputCompany.NoteTitle; string nettoAmountStr = inputCompany.NoteNettoAmount.Replace(_dot, _colon); double nettoAmount = double.Parse(nettoAmountStr); double vatAmout = Math.Round(0.08 * nettoAmount, 2); ((Range)worksheet.Cells[36, 3]).Formula = NumberToWordsConverter.ConvertNumberToAmountPln((nettoAmount + vatAmout).ToString()); }