/// <summary> /// Заполняет словари данными из выбранного файла Excel /// </summary> /// <returns></returns> public static async Task GetDictionariesAsync() { Titles = await ExcelInteraction.GetDictionaryAsync("Titles"); Anons = await ExcelInteraction.GetDictionaryAsync("Anons"); Presenters = await ExcelInteraction.GetDictionaryAsync("Presenters"); Directors = await ExcelInteraction.GetDictionaryAsync("Directors"); DirectorsSchedule = await ExcelInteraction.GetDirectorScheduleDictionaryAsync("DirectorsSchedule"); }
/// <summary> /// Возвращает список передач, записанных в выбранном файле Excel /// </summary> /// <param name="openFileDialog"></param> /// <param name="selectedExcelFileName"></param> /// <returns></returns> public static async Task <List <Broadcast> > LoadListFromExcelAsync(OpenFileDialog openFileDialog, Label selectedExcelFileName) { List <Broadcast> broadcasts = new List <Broadcast>(); string connectionString; string mp3Directory = @".\mp3\"; if (openFileDialog.ShowDialog() == DialogResult.OK) { _excelFileName = openFileDialog.FileName; } else { return(null); } switch (Path.GetExtension(_excelFileName)) { case ".xlsx": case ".xls": connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={_excelFileName}; Extended Properties=\"Excel 12.0 Xml; HDR=YES; IMEX=1;\""; //connectionString = $"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={_excelFileName}; Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1\""; break; default: MessageBox.Show("Выберите файл Excel!", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(null); } string excelQuery = "SELECT * FROM [Broadcasts$]"; using (OleDbConnection excelConnection = new OleDbConnection(connectionString)) { await excelConnection.OpenAsync(); DataTable dtShema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); List <string> sheetNames = ExcelInteraction.GetSheetsName(dtShema); if (!sheetNames.Contains("Broadcasts$")) { MessageBox.Show("Выбранный файл не содержит лист \"Broadcasts\"\nc данными о передаваемых передачах!", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(null); } OleDbCommand excelCommand = new OleDbCommand(excelQuery, excelConnection); OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter(excelCommand); DataTable sheetBroadcasts = new DataTable(); excelDataAdapter.Fill(sheetBroadcasts); var broadcastsDataReader = sheetBroadcasts.CreateDataReader(); if (broadcastsDataReader.HasRows) { while (await broadcastsDataReader.ReadAsync()) { object[] items = new object[broadcastsDataReader.FieldCount]; broadcastsDataReader.GetValues(items); Broadcast broadcast = new Broadcast { Id = Convert.ToInt16(items[0]), Title = Convert.ToString(items[1]), DateAired = Convert.ToString(items[2]), DateAiredEnd = Convert.ToString(items[3]), Vendor = Convert.ToString(items[4]), Author = Convert.ToString(items[5]), Composer = Convert.ToString(items[6]), Director = Convert.ToString(items[7]), Fragments = Convert.ToString(items[8]), Presenters = Convert.ToString(items[9]), Guests = Convert.ToString(items[10]), BroadcastCountryId = Convert.ToString(items[11]), Languages = Convert.ToString(items[12]), Anons = Convert.ToString(items[13]), FileName = Convert.ToString(items[14]), Transmitted = Convert.ToString(items[15]), }; broadcast.FileExists = true; if (!File.Exists(mp3Directory + broadcast.FileName)) { MessageBox.Show($"Файл \"{broadcast.FileName}\" не найден!", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Warning); broadcast.FileExists = false; } broadcasts.Add(broadcast); } } } selectedExcelFileName.Text = Path.GetFileName(_excelFileName); return(broadcasts); }