public void ClearText()
{
_filePath = @"C:\Users\vemal\Documents\DevHandover_corrected.xlsm";
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(_filePath, true))
{
_sheetName = "Sheet1"; //Sheet name should be changed to dynamic
_mainSheetData = GetSingleSheetByName(spreadsheetDocument, _sheetName);
_mainSheetData = GetRowsAndStringTableFromSheet(_mainSheetData);
_mainSheetObjectToCompare = GetAllNamesFromSheet(_mainSheetData.sharedStringTable, _mainSheetData.Rows);
_sheetName = "EBAttributes";
_sheetData = GetSingleSheetByName(spreadsheetDocument, _sheetName);
_sheetData = GetRowsAndStringTableFromSheet(_sheetData);
_newSheetObject = GetAllSimilerData(_mainSheetObjectToCompare, _sheetData.sharedStringTable, _sheetData.Rows);
_sheetName = "API";
_sheetData = GetSingleSheetByName(spreadsheetDocument, _sheetName);
_sheetData = GetRowsAndStringTableFromSheet(_sheetData);
_newSheetObject = GetAllSimilerData(_mainSheetObjectToCompare, _sheetData.sharedStringTable, _sheetData.Rows);
_sheetName = "ISA";
_sheetData = GetSingleSheetByName(spreadsheetDocument, _sheetName);
_sheetData = GetRowsAndStringTableFromSheet(_sheetData);
_newSheetObject = GetAllSimilerData(_mainSheetObjectToCompare, _sheetData.sharedStringTable, _sheetData.Rows);
//DeleteSheet(_mainSheetData);
var newSheet = CreateNewWorkSheet(spreadsheetDocument, "Sheet2");
spreadsheetDocument.Save();
AddrowsToNewWorkSheet(_newSheetObject, spreadsheetDocument);
}
}
private SpreadSheetHelper GetRowsAndStringTableFromSheet(SpreadSheetHelper spreadSheet)
{
spreadSheet.SharedStringTablePart = spreadSheet.workbookPart.GetPartsOfType <SharedStringTablePart>().First();
spreadSheet.sharedStringTable = spreadSheet.SharedStringTablePart.SharedStringTable;
//List<Cell> cells = sheetData.workSheet.Descendants<Cell>().ToList();
spreadSheet.Rows = spreadSheet.workSheet.Descendants <Row>().ToList();
return(spreadSheet);
}
public static SpreadSheetHelper GetAllWorksheets(SpreadsheetDocument Document)
{
SpreadSheetHelper theSheets = new SpreadSheetHelper();
WorkbookPart wbPart = Document.WorkbookPart;
theSheets.MultipleSheet = wbPart.Workbook.Sheets;
return(theSheets);
}
private string CellValueFromCell(Cell cell, SpreadSheetHelper spreadSheetHelper)
{
string cellValue = "";
if (cell != null && (cell.DataType != null) && (cell.DataType == CellValues.SharedString))
{
int ssid = int.Parse(cell.CellValue.Text);
cellValue = spreadSheetHelper.sharedStringTable.ChildElements[ssid].InnerText;
}
return(cellValue);
}
public List <InputCompany> ReadDataFromFile()
{
Application app = null;
Workbook theWorkbook = null;
try
{
app = new Application();
theWorkbook = app.Workbooks.Open(
_filePath, true, false,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Sheets sheets = theWorkbook.Worksheets;
int worksheetNumber = sheets.Count;
//Do not Assume worksheet is the first one
int i = 1;
Worksheet worksheet = (Worksheet)sheets.get_Item(i);
while (SpreadSheetHelper.SheetNameContainsNamesToExclude(worksheet.Name.ToLower(), _sheetNamesToExclude))
{
i++;
if (i > sheets.Count)
{
throw new SpreadSheetReaderException("Błąd formatu arkusza. Nazwy arkuszy zawierają niedozwolone słowa. Sprawdź plik konfiguracjny aplikacji.");
}
worksheet = (Worksheet)sheets.get_Item(i);
}
AnalyzeWorksheet(worksheet);
theWorkbook.Close(false, Type.Missing, Type.Missing);
app.Quit();
return(CompaniesReadFromFile);
}
catch (Exception e)
{
if (theWorkbook != null)
{
theWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
}
if (app != null)
{
app.Quit();
}
if (e as SpreadSheetReaderException == null)
{
throw new Exception("Nieznany błąd podczas importu!" + e.Message, e);
}
throw;
}
}
private SpreadSheetHelper GetSingleSheetByName(SpreadsheetDocument document, string sheetName)
{
SpreadSheetHelper helper = new SpreadSheetHelper();
IEnumerable <Sheet> Sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().Where(s => s.Name == sheetName);
helper.SheetId = Sheets.First().Id.Value;
helper.workbookPart = document.WorkbookPart;
helper.WorkSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(helper.SheetId);
helper.workSheet = helper.WorkSheetPart.Worksheet;
return(helper);
}
private SpreadSheetHelper GetOneSheet(SpreadSheetHelper helper, SpreadsheetDocument document, DocumentFormat.OpenXml.Spreadsheet.Sheet obj)
{
var sheetname = obj.Name;
IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Sheet> Sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Where(s => s.Name == sheetname);
helper.SheetId = Sheets.First().Id.Value;
helper.workbookPart = document.WorkbookPart;
helper.WorkSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(helper.SheetId);
helper.workSheet = helper.WorkSheetPart.Worksheet;
helper.SheetName = sheetname.ToString();
return(helper);
}
private void ReadExcel()
{
var _selectedFilePath = openSelectionDialog();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(_selectedFilePath, true))
{
SpreadSheetHelper SpreadSheetHelper = new SpreadSheetHelper();
SpreadSheetHelper = GetAllWorksheets(spreadsheetDocument);
int validationCount = 0;
if (SpreadSheetHelper != null && SpreadSheetHelper.MultipleSheet != null)
{
foreach (var obj in SpreadSheetHelper.MultipleSheet)
{
SpreadSheetHelper = GetOneSheet(SpreadSheetHelper, spreadsheetDocument, obj as DocumentFormat.OpenXml.Spreadsheet.Sheet);
string TypeName = SpreadSheetHelper.SheetName.Split('>').Last();
if (!String.IsNullOrEmpty(TypeName))
{
SpreadSheetHelper = GetRowsAndStringTableFromSheet(SpreadSheetHelper);
if (SpreadSheetHelper.Rows != null)
{
var tabs = MapSheetAsObjTypeDefination(SpreadSheetHelper);
var attWithFunctions = GetAttToAddAndRemove(tabs);
if (attWithFunctions.Count > 0)
{
AttAndRemoveAttributeForExcel(attWithFunctions, TypeName);
validationCount++;
}
}
}
}
}
if (validationCount == 0)
{
System.Windows.MessageBox.Show("Nothing to update or Wrong file or format." +
" /n Check your Sheet name and make sure to include ID in the right TAB." +
" /n Also Dont forget to include the ADD OR REMOVE word in column C");
}
else
{
System.Windows.MessageBox.Show("Successfully updated data");
}
}
}
private SpreadSheetHelper GetRowsAndStringTableFromSheet(SpreadSheetHelper spreadSheet)
{
try
{
spreadSheet.SharedStringTablePart = spreadSheet.workbookPart.GetPartsOfType <SharedStringTablePart>().First();
spreadSheet.sharedStringTable = spreadSheet.SharedStringTablePart.SharedStringTable;
//List<Cell> cells = sheetData.workSheet.Descendants<Cell>().ToList();
spreadSheet.Rows = spreadSheet.workSheet.Descendants <Row>().ToList();
}
catch
{
System.Windows.MessageBox.Show("No Changes Or Invalid File");
}
return(spreadSheet);
}
private void ClearEarlierResultsIfPresent(Worksheet worksheet, int headerRow, int lastColumn, List <InputCompany> verifiedCompanies, List <InputCompany> erroredWhileReadingInputFileCompanies)
{
foreach (var company in verifiedCompanies)
{
Range rangeToClean = worksheet.Range[SpreadSheetHelper.ConvertCellAddresFromNumsToLetterNum(company.RowNumber, lastColumn + 1), SpreadSheetHelper.ConvertCellAddresFromNumsToLetterNum(company.RowNumber, lastColumn + _numberOfColumnsToClean)];
ClearRange(rangeToClean);
}
foreach (var errcompany in erroredWhileReadingInputFileCompanies)
{
Range rangeToClean = worksheet.Range[SpreadSheetHelper.ConvertCellAddresFromNumsToLetterNum(errcompany.RowNumber, lastColumn + 1), SpreadSheetHelper.ConvertCellAddresFromNumsToLetterNum(errcompany.RowNumber, lastColumn + _numberOfColumnsToClean)];
ClearRange(rangeToClean);
}
Range headerRange = worksheet.Range[SpreadSheetHelper.ConvertCellAddresFromNumsToLetterNum(headerRow, lastColumn + 1), SpreadSheetHelper.ConvertCellAddresFromNumsToLetterNum(headerRow, lastColumn + _numberOfColumnsToClean)];
headerRange.Font.Bold = true;
headerRange.Interior.Color = XlRgbColor.rgbLightGray;
//headerRange.Formula = string.Empty;
headerRange.WrapText = true;
}
public void DeleteSheet(SpreadSheetHelper spreadSheet)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(_filePath, false))
{
var workbookPart = spreadsheetDocument.WorkbookPart;
// Get the SheetToDelete from workbook.xml
var theSheet = workbookPart.Workbook.Descendants <Sheet>()
.FirstOrDefault(s => s.Id == spreadSheet.SheetId);
if (theSheet == null)
{
return;
}
// Remove the sheet reference from the workbook.
var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(spreadSheet.SheetId));
theSheet.Remove();
// Delete the worksheet part.
workbookPart.DeletePart(worksheetPart);
}
}
private List <MaskDescriptionModel> MapSheetAsObjTypeDefination(SpreadSheetHelper spreadSheetHelper)
{
int rowIndex = 1;
List <MaskDescriptionModel> TotalTabAndAtt = new List <MaskDescriptionModel>();
MaskDescriptionModel TabsAndAtt = new MaskDescriptionModel();
List <ExcelAttributeMapping> totalAtt = new List <ExcelAttributeMapping>();
foreach (Row row in spreadSheetHelper.Rows)
{
ExcelAttributeMapping AttNameID = new ExcelAttributeMapping();
var RowA = row.Descendants <Cell>().Where(x => x.CellReference == "A" + rowIndex).FirstOrDefault();
var RowB = row.Descendants <Cell>().Where(x => x.CellReference == "B" + rowIndex).FirstOrDefault();
var RowC = row.Descendants <Cell>().Where(x => x.CellReference == "C" + rowIndex).FirstOrDefault();
var ValueA = CellValueFromCell(RowA, spreadSheetHelper);
var ValueB = CellValueFromCell(RowB, spreadSheetHelper);
var ValueC = CellValueFromCell(RowC, spreadSheetHelper);
if (String.IsNullOrEmpty(ValueB) && RowB != null)
{
ValueB = RowB.CellValue.InnerText;
}
if (String.IsNullOrEmpty(ValueC) && RowC != null)
{
ValueC = RowC.CellValue.InnerText;
}
if (ValueB != null && ValueB.Equals("Tab Name"))
{
if (rowIndex > 2)
{
TabsAndAtt.AttibuteNameAndID = totalAtt;
TotalTabAndAtt.Add(TabsAndAtt);
TabsAndAtt = new MaskDescriptionModel();
totalAtt = new List <ExcelAttributeMapping>();
}
if (ValueB != null)
{
TabsAndAtt.TabNameString = ValueA;
}
}
else if (ValueB != null && !ValueB.Equals("Tab Name") && !ValueB.Equals("AttributeID"))
{
AttNameID.AttributesNameString = ValueA;
AttNameID.AttributesIdString = ValueB;
if (!String.IsNullOrEmpty(ValueC))
{
AttNameID.Function = ValueC;
}
}
if (AttNameID.AttributesIdString != null)
{
totalAtt.Add(AttNameID);
}
rowIndex++;
}
if (TabsAndAtt != null && TotalTabAndAtt != null && totalAtt != null)
{
TabsAndAtt.AttibuteNameAndID = totalAtt;
}
TotalTabAndAtt.Add(TabsAndAtt);
return(TotalTabAndAtt);
}
public void WriteResultsToFile(List <InputCompany> companiesReadFromFile, List <InputCompany> erroredWhileReadingInputFileCompanies, Dictionary <string, VerifyNIPResult> verifiedNips, Dictionary <string, BiRVerifyResult> areCompaniesActive, Dictionary <string, WhiteListVerResult> verifiedCompanies, Dictionary <string, WhiteListVerResult> verifiedCompaniesForInvoiceDate, bool addAccountsToSeparateColumns)
{
Application app = null;
Workbook theWorkbook = null;
try
{
app = new Application();
theWorkbook = app.Workbooks.Open(
_exportFilePath, true, false,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Sheets sheets = theWorkbook.Worksheets;
int worksheetNumber = sheets.Count;
//Do not Assume worksheet is the first one
int i = 1;
Worksheet worksheet = (Worksheet)sheets.get_Item(i);
while (SpreadSheetHelper.SheetNameContainsNamesToExclude(worksheet.Name.ToLower(), _sheetNamesToExclude))
{
i++;
if (i > sheets.Count)
{
throw new SpreadSheetReaderException("Błąd formatu arkusza. Nazwy arkuszy zawierają niedozwolone słowa. Sprawdź plik konfiguracjny aplikacji.");
}
worksheet = (Worksheet)sheets.get_Item(i);
}
int headerRow = SpreadSheetHelper.FindHeaderRow(worksheet, _importColumnsConfig);
int lastColumn = GetLastColumnWithOriginalData(worksheet, headerRow);
int nipColumn = GetNipColumn(worksheet, headerRow);
int lpColumn = GetLpColumn(worksheet, headerRow);
foreach (var company in companiesReadFromFile)
{
_overallVerificationResult.Add(company.ID, OverallResult.OK);
}
ClearEarlierResultsIfPresent(worksheet, headerRow, lastColumn, companiesReadFromFile, erroredWhileReadingInputFileCompanies);
AddCompanyDataAndVerConfirmation(worksheet, headerRow, lastColumn, nipColumn, lpColumn, companiesReadFromFile, verifiedCompanies, addAccountsToSeparateColumns);
AddNIPVerification(worksheet, headerRow, lastColumn, nipColumn, lpColumn, companiesReadFromFile, verifiedNips);
AddREGONVerification(worksheet, headerRow, lastColumn, nipColumn, lpColumn, companiesReadFromFile, areCompaniesActive);
AddWhiteListVerification(worksheet, headerRow, lastColumn, nipColumn, lpColumn, companiesReadFromFile, verifiedCompanies);
AddWhiteListVerificationForInvoiceDate(worksheet, headerRow, lastColumn, nipColumn, lpColumn, companiesReadFromFile, verifiedCompaniesForInvoiceDate);
AddErroredWhileReadingInputCompanies(worksheet, headerRow, lastColumn, nipColumn, lpColumn, erroredWhileReadingInputFileCompanies);
AddOverallResultsToFile(worksheet, headerRow, lastColumn, nipColumn, lpColumn, companiesReadFromFile, erroredWhileReadingInputFileCompanies);
theWorkbook.Save();
theWorkbook.Close(true, _exportFilePath, Type.Missing); //true = save changes
app.Quit();
}
catch (Exception e)
{
if (theWorkbook != null)
{
theWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
}
if (app != null)
{
app.Quit();
}
if (e as SpreadSheetReaderException != null)
{
throw new System.Exception($"Błąd podczas odczytywania danych - zly format danych! {e.Message}", e);
}
else
{
throw new System.Exception($"Nieznany blad podczas importu! {e.Message}", e);
}
}
}
private bool ExportOOo()
{
try
{
SpreadSheetHelper helper = new SpreadSheetHelper(true);
//Samples for service sheet.SheetCell
unoidl.com.sun.star.sheet.XSpreadsheet xSheet = helper.getSpreadsheet(0);
unoidl.com.sun.star.table.XCell xCell = null;
//xCell = xSheet.getCellByPosition(1, 1);
//// --- Insert two text paragraphs into the cell. ---
//unoidl.com.sun.star.text.XText tText = (unoidl.com.sun.star.text.XText)xCell;
//unoidl.com.sun.star.text.XTextCursor tTextCursor =tText.createTextCursor();
//tText.insertString(tTextCursor, _title + "\r\n", false);
if (_gridType == 0)
{
for (int j = 0; j < _exportGrid.Columns.Count; j++)
{
xCell = xSheet.getCellByPosition(j + 1, 2);
// --- Insert two text paragraphs into the cell. ---
unoidl.com.sun.star.text.XText xText = (unoidl.com.sun.star.text.XText)xCell;
unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
xText.insertString(xTextCursor, _exportGrid.Columns[j].HeaderText, false);
}
// --- Get cell B3 by position - (column, row) ---
for (int i = 0; i < _exportGrid.Rows.Count; i++)
{
if (_exportGrid.Rows[i].Visible)
{
for (int j = 0; j < _exportGrid.Columns.Count; j++)
{
xCell = xSheet.getCellByPosition(j + 1, i + 3);
unoidl.com.sun.star.text.XText xText = (unoidl.com.sun.star.text.XText)xCell;
unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
xText.insertString(xTextCursor, _exportGrid.Rows[i].Cells[j].Value.ToString(), false);
}
}
}
}
else if (_gridType == 1)
{
for (int i = 0; i < _flexGrid.Rows; i++)
{
for (int j = 0; j < _flexGrid.Cols; j++)
{
xCell = xSheet.getCellByPosition(j + 1, i + 2);
unoidl.com.sun.star.text.XText xText = (unoidl.com.sun.star.text.XText)xCell;
unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
xText.insertString(xTextCursor, _flexGrid.get_TextMatrix(i, j), false);
}
}
}
else
{
for (int i = 0; i < _listview.Items.Count; i++)
{
xCell = xSheet.getCellByPosition(1, i + 2);
unoidl.com.sun.star.text.XText xText = (unoidl.com.sun.star.text.XText)xCell;
unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
xText.insertString(xTextCursor, _listview.Items[i].Text, false);
for (int j = 1; j < _flexGrid.Cols; j++)
{
xCell = xSheet.getCellByPosition(j + 1, i + 2);
xText = (unoidl.com.sun.star.text.XText)xCell;
xTextCursor = xText.createTextCursor();
xText.insertString(xTextCursor, _listview.Items[i].SubItems[j].Text, false);
}
}
}
helper.storeDocComponent(_filename);
helper.closeDocCompant();
return(true);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
finally
{
//_exportGrid.Dispose();
}
}
private void AnalyzeWorksheet(Worksheet worksheet)
{
string lastProcessedItem = "Jeszcze nie zaczelismy przetwarzania";
int lastReadColumn = -1;
string tempStr = string.Empty;
CompaniesReadFromFile = new List <InputCompany>();
try
{
lastProcessedItem = "Przed wczytywaniem nagłówka";
HeaderRow = -1;
HeaderRow = SpreadSheetHelper.FindHeaderRow(worksheet, _columnsConfig);
if (HeaderRow == -1)
{
throw new SpreadSheetReaderHeaderException(string.Format("Błąd formatu aruksza. W arkuszu: {0} nie znaleziono nagłówka danych. Sprawdź czy któryś nagłówek zawiera słowo 'nip'", worksheet.Name));
}
DetermineColumns(worksheet);
ValidateColumns();
lastProcessedItem = "Wczytano nagłówek";
InputCompany tempCompany;
for (int i = HeaderRow + 1; !IsEndOfTable(i, 1, worksheet); i++)
{
tempCompany = new InputCompany();
tempCompany.RowNumber = i;
lastProcessedItem = $"Przed wczytaniem NIPu. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NIP];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NIP]]).Formula.ToString();
tempCompany.NIP = GetNip(tempStr);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano NIP. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem konta bankowego. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.AccountNumber];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.AccountNumber]]).Formula.ToString();
tempCompany.BankAccountNumber = GetBankAccountNumber(tempStr);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano Numer Konta. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem LP. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.LP];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.LP]]).Formula.ToString();
tempCompany.LP = GetLP(tempStr);
tempStr = string.Empty;
if (CompaniesReadFromFile.Any(c => c.ID == tempCompany.ID))
{
throw new SpreadSheetReaderException($"Dane zawierają już wpis o takiej samej pozycji i nipie. Aktualna wiersz: {i}, Nip: {tempCompany.NIP}, LP: {tempCompany.LP}");
}
lastProcessedItem = $"Wczytano LP.Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem daty zapłaty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.PaymentDate];
var dateRange = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.PaymentDate]]);
tempStr = dateRange.Formula.ToString();
tempCompany.PaymentDate = GetDate(dateRange);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano datę zapłaty. Aktualna pozycja: wiersz {i}";
if (ColumnMapping.ContainsKey(ImportColumnName.InvoiceDate))
{
lastProcessedItem = $"Przed wczytaniem daty faktury. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.InvoiceDate];
var invoiceDateRange = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.InvoiceDate]]);
tempStr = invoiceDateRange.Formula.ToString();
double tempDateInDouble;
if (double.TryParse(tempStr, out tempDateInDouble))
{
try
{
tempCompany.InvoiceDate = DateTime.FromOADate(tempDateInDouble);
}
catch (ArgumentException)
{ tempCompany.FormatErrors.Add(InputCompanyFormatError.InvoiceDateError); }
}
else
{
tempCompany.FormatErrors.Add(InputCompanyFormatError.InvoiceDateError);
}
tempStr = string.Empty;
lastProcessedItem = $"Wczytano datę faktury. Aktualna pozycja: wiersz {i}";
}
if ((_generateNotes && AreColumnsToGenerateNotesPresent()))
{
lastProcessedItem = $"Przed wczytaniem ID noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteID];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteID]]).Formula.ToString().Trim();
tempCompany.NoteID = tempStr;
tempStr = string.Empty;
lastProcessedItem = $"Wczytano ID Noty. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem tytułu Noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteTitle];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteTitle]]).Formula.ToString().Trim();
tempCompany.NoteTitle = tempStr;
tempStr = string.Empty;
lastProcessedItem = $"Wczytano tytuł Noty. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem netto noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteAmount];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteAmount]]).Formula.ToString().Trim();
tempCompany.NoteNettoAmount = tempStr;
tempStr = string.Empty;
lastProcessedItem = $"Wczytano netto noty. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem daty noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteDate];
var dataRange = (Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteDate]];
tempStr = dataRange.Formula.ToString();
tempCompany.NoteDate = GetDate(dataRange);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano datę noty. Aktualna pozycja: wiersz {i}";
}
tempCompany.FormatErrors.AddRange(ValidateCompany(tempCompany));
CompaniesReadFromFile.Add(tempCompany);
}
}
catch (Exception e)
{
if (e is SpreadSheetReaderHeaderException || e is SpreadSheetReaderMissingColumnsException)
{
throw;
}
string errorMsg = string.Format("\nZłapano błąd w rzędzie gdy procedura była na kroku: {0}, w kolumnie: {2}, odczytała wartość: {1}.\n\n", lastProcessedItem, tempStr, lastReadColumn);
throw new SpreadSheetReaderException(errorMsg, e);
}
}
