/// <summary> /// Generates a list of municipalities in JSON format to applications file generation folder. /// </summary> /// <exception cref="System.Exception">The workbook doesn't contain the expected sheet name.</exception> internal static void GenerateJsonFile() { ExcelXmlReader reader = new ExcelXmlReader(); reader.ReadSheets(MunicipalitiesStartDataFile, worksheets => { if (worksheets.Count == 0) { throw new Exception("The workbook doesn't contain any sheet."); } ArrayList municipalities = new ArrayList(); int rowIndex = 2; var worksheet = worksheets[1]; bool keepReading = true; Dictionary <string, Province> provinces = new Dictionary <string, Province>(); do { Console.WriteLine($"Row {rowIndex}:"); var number = worksheet.Cells[rowIndex, 1].Value; if (number != null) { var dataItem = new { municipalityCode = number.ToString().PadLeft(3, '0').Trim(), names = new[] { new LanguageName(reader.GetString(worksheet, 2, rowIndex), "fi"), new LanguageName(reader.GetString(worksheet, 3, rowIndex), "sv") } }; municipalities.Add(dataItem); string provinceCode = reader.GetString(worksheet, 16, rowIndex); if (provinces.TryGetValue(provinceCode, out Province province)) { province.Municipalities.Add(dataItem.municipalityCode); } else { province = new Province { Code = provinceCode, Names = new List <LanguageName> { new LanguageName { Language = "fi", Name = reader.GetString(worksheet, 17, rowIndex) }, new LanguageName { Language = "sv", Name = reader.GetString(worksheet, 18, rowIndex) } }, Municipalities = new List <string> { dataItem.municipalityCode } }; provinces.Add(provinceCode, province); } } else { keepReading = false; } rowIndex++; Console.WriteLine(); } while (keepReading); // convert data to json var json = JsonConvert.SerializeObject(municipalities, Formatting.Indented); // overwrite the file always File.WriteAllText(MunicipalitiesGeneratedFile, json); // convert data to json var jsonProvince = JsonConvert.SerializeObject(provinces.Values, Formatting.Indented); // overwrite the file always File.WriteAllText(ProvincesGeneratedFile, jsonProvince); }); }
/// <summary> /// Generates a list of business regions in JSON format to applications file generation folder. /// </summary> /// <exception cref="System.Exception">The workbook doesn't contain the expected sheet name.</exception> internal static void GenerateJsonFile() { ExcelXmlReader reader = new ExcelXmlReader(); reader.ReadSheets(BusinessRegionStartDataFile, worksheets => { if (worksheets.Count == 0) { throw new Exception("The workbook doesn't contain any sheet."); } ArrayList municipalities = new ArrayList(); int rowIndex = 2; var worksheet = worksheets[1]; bool keepReading = true; Dictionary <string, Area> businessAreas = new Dictionary <string, Area>(); do { Console.WriteLine($"Row {rowIndex}:"); var municipalityNumber = worksheet.Cells[rowIndex, 2].Value; if (municipalityNumber != null) { var municipalityCode = municipalityNumber.ToString().PadLeft(3, '0').Trim(); string businessCode = reader.GetString(worksheet, 1, rowIndex); if (businessAreas.TryGetValue(businessCode, out Area businessArea)) { businessArea.Municipalities.Add(municipalityCode); } else { businessArea = new Area { Code = businessCode, Names = new List <LanguageName> { new LanguageName { Language = "fi", Name = reader.GetString(worksheet, 3, rowIndex) }, new LanguageName { Language = "sv", Name = reader.GetString(worksheet, 5, rowIndex) }, new LanguageName { Language = "en", Name = reader.GetString(worksheet, 7, rowIndex) } }, Municipalities = new List <string> { municipalityCode } }; businessAreas.Add(businessCode, businessArea); } } else { keepReading = false; } rowIndex++; Console.WriteLine(); } while (keepReading); // convert data to json var jsonResult = JsonConvert.SerializeObject(businessAreas.Values, Formatting.Indented); // overwrite the file always File.WriteAllText(BusinessRegionGeneratedFile, jsonResult); }); }
/// <summary> /// Generates a list of hospital district area in JSON format to applications file generation folder. /// </summary> /// <exception cref="System.Exception">The workbook doesn't contain the expected sheet name.</exception> internal static void GenerateJsonFile() { ExcelXmlReader reader = new ExcelXmlReader(); reader.ReadSheets(HospitalAreaStartDataFile, worksheets => { if (worksheets.Count == 0) { throw new Exception("The workbook doesn't contain any sheet."); } var sheetNames = worksheets.Select(x => x.Name).ToList(); if (!sheetNames.Contains(DataSheetName)) { throw new Exception($"The hosital district area doesn't contain a sheet with name: {DataSheetName}."); } var worksheet = worksheets.FirstOrDefault(x => x.Name.ToLower() == DataSheetName.ToLower()); if (worksheet == null) { throw new Exception($"The hospital district area worksheet is null: {DataSheetName}."); } ArrayList municipalities = new ArrayList(); int rowIndex = 6; bool keepReading = true; Dictionary <string, Area> hospitalAreas = new Dictionary <string, Area>(); do { Console.WriteLine($"Row {rowIndex}:"); var municipalityNumber = worksheet.Cells[rowIndex, 1].Value; if (municipalityNumber != null) { var municipalityCode = municipalityNumber.ToString().PadLeft(3, '0').Trim(); string hospitalCode = reader.GetString(worksheet, 3, rowIndex); if (hospitalAreas.TryGetValue(hospitalCode, out Area hospitalArea)) { hospitalArea.Municipalities.Add(municipalityCode); } else { hospitalArea = new Area { Code = hospitalCode, Names = new List <LanguageName> { new LanguageName { Language = "fi", Name = reader.GetString(worksheet, 5, rowIndex) }, new LanguageName { Language = "sv", Name = reader.GetString(worksheet, 6, rowIndex) } }, Municipalities = new List <string> { municipalityCode } }; hospitalAreas.Add(hospitalCode, hospitalArea); } } else { keepReading = false; } rowIndex++; Console.WriteLine(); } while (keepReading); //convert data to json var jsonResult = JsonConvert.SerializeObject(hospitalAreas.Values, Formatting.Indented); // overwrite the file always File.WriteAllText(HsopitalAreaGeneratedFile, jsonResult); }); }