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