private void ExcelColumnsAndRows(string filePath)
{
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//4. DataSet - Create column names from first row
//excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
_tables = result.Tables;
for (var i = 0; i < _tables.Count; i++)
{
var tableComboBox = new TableComboBox
{
TableName = _tables[i].TableName,
TablePosition = i
};
_excelSpreadSheets.Add(tableComboBox);
}
TableSelectComboBox.ItemsSource = _excelSpreadSheets;
TableSelectComboBox.DisplayMemberPath = "TableName";
TableSelectComboBox.SelectedValuePath = "TablePosition";
excelReader.Close();
}
//TableSelectComboBoxHeaderRow
private void TableSelectComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
var items = e.AddedItems;
if (items != null && items.Count != 0)
{
var item = (TableComboBox)items[0];
_table = _tables[item.TablePosition];
var rowCount = _table.Rows.Count;
var rows = _table.Rows;
var columnCount = rows[0].ItemArray.Count();
RowsTxtBox.Text = rowCount.ToString(CultureInfo.InvariantCulture);
ColumnsTxtBox.Text = columnCount.ToString(CultureInfo.InvariantCulture);
int rowCountDropDown = rowCount;
for (var i = 1; i <= rowCount; i++)
{
var tableComboBox = new TableComboBox
{
TableName = i.ToString(),
TablePosition = i
};
_blankRowList.Add(tableComboBox);
_headerRowList.Add(tableComboBox);
}
TableSelectComboBoxHeaderRow.ItemsSource = _headerRowList;
TableSelectComboBoxHeaderRow.DisplayMemberPath = "TableName";
TableSelectComboBoxHeaderRow.SelectedValuePath = "TablePosition";
BlankRowSelectionComboBox.ItemsSource = _blankRowList;
BlankRowSelectionComboBox.DisplayMemberPath = "TableName";
BlankRowSelectionComboBox.SelectedValuePath = "TablePosition";
}
//var tableName = tables[0].TableName;
}
private void LoadTables(string db)
{
if (db == null)
{
return;
}
constr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + db + "; Persist Security Info = False";
con = new OleDbConnection(constr);
using (con)
{
try
{
con.Open();
DataTable dt = con.GetSchema("Tables");
con.Close();
foreach (DataRow table in dt.Rows)
{
TableComboBox.Items.Add(table[2].ToString());
}
}
catch (Exception err)
{
Program.ShowError(err);
}
}
TableComboBox.SelectedIndex = TableComboBox.FindStringExact(dbConfig.Table);
EnableControls();
}
