private void PopulateSheets()
{
var connectionString = $"Provider=Microsoft.Jet.OLEDB.4.0; data source={FilePath}; Extended Properties=Excel 8.0;";
using (var connection = new OleDbConnection(connectionString))
{
connection.Open();
var dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dataTable == null)
{
return;
}
var sheets = new List <string>();
foreach (DataRow row in dataTable.Rows)
{
sheets.Add(row["TABLE_NAME"].ToString());
}
Sheets = sheets;
if (Sheets.Any())
{
SelectedSheet = Sheets.FirstOrDefault();
}
}
}
private void SheetActivated(dynamic sheet)
{
Logger.Debug("SheetActivated: _lockEvents is {0}", _lockEvents);
if (sheet != null) // && _lockEvents <= 0)
{
_lockEvents += 1;
SheetViewModel svm = Sheets.FirstOrDefault(s => s.IsSelected);
if (svm != null)
{
svm.IsSelected = false;
}
int index = IndexOf(sheet);
Logger.Info("SheetActivated: Sheet index is {0}", index);
if (index >= 0 && index < Sheets.Count)
{
Sheets[index].IsSelected = true;
}
else
{
Logger.Warn("SheetActivated: Index {0} is out of bounds!", index);
}
_lockEvents -= 1;
}
Dispatch(() => OnPropertyChanged("ActiveSheet"));
}
public IEnumerable <TObject> ReadExcelDocument <TObject>(string sheetName) where TObject : class
{
List <string[]> propValue = null;
var listReturn = new List <TObject>();
var sheet = Sheets.FirstOrDefault(c => c.Name == sheetName);
var openXmlExcelData = OpenXmlReader.ReadExcel(sheet);
foreach (var row in openXmlExcelData.DataRows)
{
propValue = new List <string[]>();
for (int i = 0; i < row.Count; i++)
{
if (i < openXmlExcelData.Headers.Count)
{
propValue.Add(new[] { openXmlExcelData.Headers[i].Replace(" ", ""), row[i] });
}
}
if (row.Count() > 0)
{
var TObjectInstance = Activator.CreateInstance <TObject>();
ObjectParser.FillInstance(TObjectInstance, propValue);
listReturn.Add(TObjectInstance);
}
}
return(listReturn);
}
public override string ToString()
{
StringBuilder sb = new StringBuilder();
const string line = "----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+";
string title = string.Format("Key Sheet {0}", Year);
int spacesLeft = (line.Length - 14) / 2;
string heading = string.Concat(new string(' ', spacesLeft), title);
string[,] values = new string[12, 31];
for (int m = 1; m <= 12; m++)
{
for (int d = 1; d <= 31; d++)
{
var s = Sheets.FirstOrDefault(e => e.Month == m && e.Day == d);
if (s != null)
{
values[m - 1, d - 1] = s.SheetName;
}
else
{
values[m - 1, d - 1] = " ";
}
}
}
sb.AppendLine();
sb.AppendLine(heading);
sb.AppendLine();
sb.AppendLine();
sb.AppendLine(line);
sb.Append(" |");
for (int m = 1; m <= 12; m++)
{
sb.Append(string.Format(" {0:MMM} ", new DateTime(Year, m, 1)).ToUpper());
sb.Append("|");
}
sb.Append(Environment.NewLine);
sb.AppendLine(line);
for (int d = 0; d < 31; d++)
{
sb.AppendFormat(" {0:00} |", d + 1);
for (int m = 0; m < 12; m++)
{
sb.AppendFormat(" {0} |", values[m, d]);
}
sb.Append(Environment.NewLine);
}
sb.AppendLine(line);
sb.AppendLine();
foreach (var item in SheetDefinitions)
{
sb.AppendLine(item.ToString());
}
return(sb.ToString());
}
/// <summary>
/// Finds the index of a given sheet (worksheet or chart sheet)
/// in the Sheets collection.
/// </summary>
/// <param name="sheet">Worksheet or chart sheet</param>
/// <returns>Index of the sheet in the Sheets collection</returns>
public int IndexOf(dynamic sheet)
{
string name = sheet.Name;
SheetViewModel svm = Sheets.FirstOrDefault(s => s.DisplayString == name);
if (svm == null)
{
// If the sheet was not found, rebuild the sheet list.
// Maybe the sheet was just added, before the watch timer got active.
BuildSheetList();
svm = Sheets.FirstOrDefault(s => s.DisplayString == name);
}
if (svm != null)
{
Logger.Info("IndexOf: Found view model for this sheet");
return(Sheets.IndexOf(svm));
}
else
{
Logger.Warn("IndexOf: Requested sheet not found in collection!");
return(-1);
}
}
