How to do a mapping of an excel file using c#

Question

I wanted to do a mapping of an excel file but with the columns in a certain way. As for example I have an exel file where the second column (of the excel file) has to be referenced there is 1 column of the sql database.

This is the Form I'm using:

I already have the sql search done I just wanted to know how I can put in the combobox the name of the columns in the excel file.

This is the code I am using to search the excel file:

        using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook|*.xls", ValidateNames = true })
        {
            DataSet result;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
                IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(fs);
                reader.IsFirstRowAsColumnNames = true;
                result = reader.AsDataSet();
                comboBox1.Items.Clear();
                foreach (DataTable dt in result.Tables) comboBox1.Items.Add(dt.TableName);
                reader.Close();

                string ConecçãoDB = ConfigurationManager.ConnectionStrings["ConecçaoDB"].ConnectionString;
                string Table = ConfigurationManager.AppSettings["table"];

                string ssqltable = Table;

                string ssqlconnectionstring = ConecçãoDB;

                filename = ofd.FileName;
                MessageBox.Show(Convert.ToString(filename));
                var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
                var conexao = new System.Data.OleDb.OleDbConnection(connectionString);
                var sql = "SELECT * FROM [" + comboBox1.SelectedText + "$]";
                string sclearsql = "delete from " + ssqltable;
            }
        }

What specific problem are you having - the actual databinding into the combobox itself? What steps have you taken so far to try this, or are you stuck? — LightCC, Oct 10 '17 at 14:27

S Nash · Accepted Answer · 2017-10-10T15:26:57.830

2

Updating my answer based on your latest comment as I misunderstood what you need.

You need to access Excel object model and after that you will have full and easy control over Excel data.

For a solid and simple answer refer to :

Microsoft Interop: Excel Column Names

When using the suggested code make sure you add the following reference to your code:

using Microsoft.Office.Interop.Excel;

Remember the provided answer there puts the column names in a generic list of strings.

After that you have to bind it to combo box:

BindingSource bs = new BindingSource();
bs.DataSource=columnNames ;
comboBox1.DataSource = bs;

edited Oct 10 '17 at 15:26

answered Oct 10 '17 at 14:45

S Nash

2,363
3
34
64

what you mean with "Here add your code to populate Dataset ds from the Database" – Pedro Azevedo Oct 10 '17 at 14:56
So let say your Excel file has 3 columns A,B,C, in Combox you want to see A,B,C(On C# Webform)? please confirm. – S Nash Oct 10 '17 at 15:10
Yes , thats what I need – Pedro Azevedo Oct 10 '17 at 15:13
OK, I misunderstood your question, now I know what you want. Updating my answer. – S Nash Oct 10 '17 at 15:14
Is this the best way to do what I need? – Pedro Azevedo Oct 10 '17 at 15:23
In my view, this is the most efficient way to do it. – S Nash Oct 10 '17 at 15:27
First, you need to add that reference to the project references. Go to project menu and then add reference. Then on right-hand side click on COM and then find Microsoft Excel xx.x Object Library. – S Nash Oct 10 '17 at 15:55

How to do a mapping of an excel file using c#

1 Answers1