static List <PESA2010Entry> ParsePESADBs2010Chap10()
{
string ExcelFilePath = @"Assets/pesa_2010_database_tables_chapter10.xlsx";
string SheetName = @"CRA 2010 Chapter 10 DB final ";
List <PESA2010Entry> PESA2010Entries = new List <PESA2010Entry>();
DataTable CURRENTTABLE;
using (var stream = File.Open(ExcelFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
DataTableCollection Worksheets = result.Tables;
CURRENTTABLE = Worksheets[Worksheets.IndexOf(SheetName)];
}
}
List <string> FinancialYears = new List <string>()
{
"2004-05",
"2005-06",
"2006-07",
"2007-08",
"2008-09",
"2009-10"
};
FinancialYears.Remove("2009-10"); // already covered in a later dataset
for (int i = 1; i < CURRENTTABLE.Rows.Count; i++)
{
foreach (string FinancialYear in FinancialYears)
{
PESA2010Entry pesaEntry = new PESA2010Entry();
pesaEntry.FinancialYear = FinancialYear;
pesaEntry.YearEnd = int.Parse(FinancialYear.Substring(0, 4)) + 1;
pesaEntry.Value = SafeConvertObjectToDouble(CURRENTTABLE.Rows[i].ItemArray[CURRENTTABLE.Rows[0].ItemArray.OfType <string>().ToList().IndexOf(FinancialYear)]);
pesaEntry.DepartmentCode = SafeConvertObjectToString(CURRENTTABLE.Rows[i].ItemArray[0]);
pesaEntry.DepartmentName = (string)CURRENTTABLE.Rows[i].ItemArray[1];
pesaEntry.COFOGLevel1 = (string)CURRENTTABLE.Rows[i].ItemArray[2];
pesaEntry.HMTFunction = (string)CURRENTTABLE.Rows[i].ItemArray[3];
pesaEntry.COFOGLevel2 = (string)CURRENTTABLE.Rows[i].ItemArray[4];
pesaEntry.HMTSubfunction = (string)CURRENTTABLE.Rows[i].ItemArray[5];
pesaEntry.ProgrammeObjectGroup = SafeConvertObjectToString(CURRENTTABLE.Rows[i].ItemArray[6]);
pesaEntry.ProgrammeObjectGroupAlias = SafeConvertObjectToString(CURRENTTABLE.Rows[i].ItemArray[7]);
pesaEntry.IDNonID = (string)CURRENTTABLE.Rows[i].ItemArray[8];
pesaEntry.CAPorCUR = (string)CURRENTTABLE.Rows[i].ItemArray[9];
pesaEntry.NUTSRegion = (string)CURRENTTABLE.Rows[i].ItemArray[11];
pesaEntry.CGorLGorPCorBOE = (string)CURRENTTABLE.Rows[i].ItemArray[10];
PESA2010Entries.Add(pesaEntry);
}
}
return(PESA2010Entries);
}
static List <PESA2018Entry> ParsePESADB2018()
{
string ExcelFilePath = @"Assets/CRA_2018_Database_for_Publication_rvsd.xlsx";
string SheetName = @"CRA 2018 database";
DataTable CURRENTTABLE;
using (var stream = File.Open(ExcelFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
DataTableCollection Worksheets = result.Tables;
CURRENTTABLE = Worksheets[Worksheets.IndexOf(SheetName)];
}
}
List <PESA2018Entry> PESA2018Entries = new List <PESA2018Entry>();
List <string> FinancialYears = new List <string>()
{
"2013-14",
"2014-15",
"2015-16",
"2016-17",
"2017-18"
};
for (int i = 1; i < CURRENTTABLE.Rows.Count; i++)
{
foreach (string FinancialYear in FinancialYears)
{
PESA2018Entry pesaEntry = new PESA2018Entry();
pesaEntry.FinancialYear = FinancialYear;
pesaEntry.YearEnd = int.Parse(FinancialYear.Substring(0, 4)) + 1;
pesaEntry.Value = SafeConvertObjectToDouble(CURRENTTABLE.Rows[i].ItemArray[CURRENTTABLE.Rows[0].ItemArray.OfType <string>().ToList().IndexOf(FinancialYear)]);
pesaEntry.DepartmentName = (string)CURRENTTABLE.Rows[i].ItemArray[0];
pesaEntry.OrganisationName = (string)CURRENTTABLE.Rows[i].ItemArray[1];
pesaEntry.CRASegmentCode = (string)CURRENTTABLE.Rows[i].ItemArray[2];
pesaEntry.CRASegmentName = (string)CURRENTTABLE.Rows[i].ItemArray[3];
pesaEntry.COFOGLevel0 = (string)CURRENTTABLE.Rows[i].ItemArray[4];
pesaEntry.HMTFunction = (string)CURRENTTABLE.Rows[i].ItemArray[5];
pesaEntry.COFOGLevel1 = (string)CURRENTTABLE.Rows[i].ItemArray[6];
pesaEntry.HMTSubfunction = (string)CURRENTTABLE.Rows[i].ItemArray[7];
pesaEntry.IDNonID = (string)CURRENTTABLE.Rows[i].ItemArray[8];
pesaEntry.CAPorCUR = (string)CURRENTTABLE.Rows[i].ItemArray[9];
pesaEntry.CGorLGorPCorBOE = (string)CURRENTTABLE.Rows[i].ItemArray[10];
pesaEntry.HMTorDept = (string)CURRENTTABLE.Rows[i].ItemArray[11];
pesaEntry.NUTSRegion = (string)CURRENTTABLE.Rows[i].ItemArray[12];
pesaEntry.Country = (string)CURRENTTABLE.Rows[i].ItemArray[13];
PESA2018Entries.Add(pesaEntry);
}
}
return(PESA2018Entries);
}
static List <PESA2014Entry> ParsePESADB2014()
{
string ExcelFilePath = @"Assets/CRA_2014_Combined_Database_for_Publication.xlsx";
string SheetName = @"CRA14 combined database";
DataTable CURRENTTABLE;
using (var stream = File.Open(ExcelFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
DataTableCollection Worksheets = result.Tables;
CURRENTTABLE = Worksheets[Worksheets.IndexOf(SheetName)];
}
}
List <PESA2014Entry> PESA2014Entries = new List <PESA2014Entry>();
List <string> FinancialYears = new List <string>()
{
"2009-10",
"2010-11",
"2011-12",
"2012-13",
"2013-14"
};
FinancialYears.Remove("2013-14"); // already covered in a later dataset
for (int i = 1; i < CURRENTTABLE.Rows.Count; i++)
{
foreach (string FinancialYear in FinancialYears)
{
PESA2014Entry pesaEntry = new PESA2014Entry();
pesaEntry.FinancialYear = FinancialYear;
pesaEntry.YearEnd = int.Parse(FinancialYear.Substring(0, 4)) + 1;
pesaEntry.Value = SafeConvertObjectToDouble(CURRENTTABLE.Rows[i].ItemArray[CURRENTTABLE.Rows[0].ItemArray.OfType <string>().ToList().IndexOf(FinancialYear)]);
// Department COFOG Level 0 HMT Function COFOG Level 1 HMT Subfunction CRA Segment CRA Segment Description ID/ non - ID CAP or CUR CG, LG or PC NUTS Region Country
pesaEntry.DepartmentName = (string)CURRENTTABLE.Rows[i].ItemArray[0];
//pesaEntry.OrganisationName = (string)CURRENTTABLE.Rows[i].ItemArray[1];
pesaEntry.CRASegmentCode = (string)CURRENTTABLE.Rows[i].ItemArray[5];
pesaEntry.CRASegmentName = (string)CURRENTTABLE.Rows[i].ItemArray[6];
pesaEntry.COFOGLevel0 = (string)CURRENTTABLE.Rows[i].ItemArray[1];
pesaEntry.HMTFunction = (string)CURRENTTABLE.Rows[i].ItemArray[2];
pesaEntry.COFOGLevel1 = (string)CURRENTTABLE.Rows[i].ItemArray[3];
pesaEntry.HMTSubfunction = (string)CURRENTTABLE.Rows[i].ItemArray[4];
pesaEntry.IDNonID = (string)CURRENTTABLE.Rows[i].ItemArray[7];
pesaEntry.CAPorCUR = (string)CURRENTTABLE.Rows[i].ItemArray[8];
pesaEntry.CGorLGorPCorBOE = (string)CURRENTTABLE.Rows[i].ItemArray[9];
//pesaEntry.HMTorDept = (string)CURRENTTABLE.Rows[i].ItemArray[11];
pesaEntry.NUTSRegion = (string)CURRENTTABLE.Rows[i].ItemArray[10];
pesaEntry.Country = (string)CURRENTTABLE.Rows[i].ItemArray[11];
PESA2014Entries.Add(pesaEntry);
}
}
return(PESA2014Entries);
}