I want to import .xlsx file into my Application to save it in the DataBase then. The .xlsx file can be different so I don't have static ColumnNames.
First I want to Load the content of the .xlsx file in the DataGrid.
So I made a Grid with AutoGenerateColumns = true
.
<DataGrid x:Name="DataGrid" AutoGenerateColumns="True" IsReadOnly="True" Grid.Row="1" />
Then I created a ImportExcel class
for the code-behind.
private void ChooseExcelFile_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.DefaultExt = ".xlsx";
openFileDialog.Filter = "(.xlsx)|*.xlsx";
if (openFileDialog.ShowDialog() == true)
{
FilePathTextBox.Text = System.IO.Path.GetFullPath(openFileDialog.FileName);
string constr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = '" + FilePathTextBox.Text + "'" + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";
string query = "SELECT * FROM [Sheet1$]";
OleDbConnection con = new OleDbConnection(constr);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, con);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
DataGrid.DataContext = dataTable;
}
}
my problem is, that the DataGrid will not have any content after clicking "OK". What is wrong there ?
I also tried DataGrid.DataContext = dataTable.DefaultView
without success.
Also, is string query = "SELECT * FROM [Sheet1$]";
always working? Because what is if the name of the sheet is not Sheet1
?