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;
}
}
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);
}
}
}
