/// <summary> /// /// </summary> /// <param name="args"></param> /// <remarks>args[0] = File path and name</remarks> /// <remarks>args[1] = url for Tax Form Catalog</remarks> /// <remarks>args[2] = user id for Tax Form Catalog</remarks> /// <remarks>args[3] = password for Tax Form Catalog</remarks> static void Main(string[] args) { List <InputData> filingSystems = new List <InputData>(); // = null; //Populate object with source file using (FileStream fs = new FileStream(args[0], FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fs, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); int rowNumber = 1; foreach (Row row in sheetData.Elements <Row>()) { InputData filingSystem = new InputData(); int rowcolumn = 1; if (rowNumber > 1) { foreach (Cell c in row.Elements <Cell>()) { switch (rowcolumn) { case 1: filingSystem.CountryCode = ReadExcelCell(c, workbookPart); break; case 2: filingSystem.State = ReadExcelCell(c, workbookPart); break; case 3: filingSystem.HolidayDate = DateTime.FromOADate(Convert.ToInt32(ReadExcelCell(c, workbookPart))); break; case 4: filingSystem.HolidayName = ReadExcelCell(c, workbookPart); break; } rowcolumn++; } filingSystems.Add(filingSystem); } rowNumber++; } } } int totalRecords = filingSystems.Count; int recordsInserted = 0; int recordsErrored = 0; int recordsSkipped = 0; List <Holiday> holidays = GetHolidays(args[1], args[2], args[3]); foreach (InputData item in filingSystems) { List <Holiday> foundHolidays = holidays.Where(x => x.Country == item.CountryCode.Substring(0, 2) && x.Region == item.State && x.Date == item.HolidayDate).ToList(); if (!foundHolidays.Any()) { Holiday holidayToAdd = new Holiday(); holidayToAdd.Country = item.CountryCode.Substring(0, 2); holidayToAdd.Region = item.State; holidayToAdd.BankId = 1; holidayToAdd.Description = item.HolidayName; holidayToAdd.Date = item.HolidayDate; Int64 insertedHolidayId = -1; insertedHolidayId = InsertHoliday(holidayToAdd, args[1], args[2], args[3]); if (insertedHolidayId > 0) { recordsInserted++; } else { Console.WriteLine(string.Format("2: Insert to Holiday did not return a valid HolidayId for Country [{0}], Region [{1}], Holiday Name [{2}] with date of [{3}].", item.CountryCode, item.State, item.HolidayName, item.HolidayDate)); recordsErrored++; } } else { Console.WriteLine(string.Format("3: Insert to Holiday was skipped because a record for the same country,region, and date already exists. Country [{0}], Region [{1}], Holiday Name [{2}] with date of [{3}].", item.CountryCode, item.State, item.HolidayName, item.HolidayDate)); recordsSkipped++; } } Console.WriteLine(string.Format("4: Process Completed with [{0}] records inserted, [{1}] records skipped, [{2}] records Errored while processing [{3}] total records.", recordsInserted, recordsSkipped, recordsErrored, totalRecords)); Console.ReadKey(); }