CODEWITHSUNDEEP
c#excel
Allen Simpson
Allen Simpson

Reputation: 45

C# , Visual Studio , Opening excel & then putting data into combo box

I have had an issue for the last few hours trying to pull the sheet names from an excel workbook and display for selection in a combobox. I managed to get it to work but i'm a little concerned its crude and not very efficient.

     private void btnChoose2_Click(object sender, RoutedEventArgs e)
    {
        OpenFileDialog openfileDialog1 = new OpenFileDialog();
        if (openfileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {

            openLabel.Text = openfileDialog1.SafeFileName;
            String filename = DialogResult.ToString();

            var excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.Visible = false;
            excelApp.Workbooks.Open(openfileDialog1.FileName);

            int rcountTag = excelApp.Sheets.Count - 1;

             for (int i = 1; i <= rcountTag + 1; i++)
            {
                Microsoft.Office.Interop.Excel.Sheets excelSheets = excelApp.Worksheets;
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)

           excelSheets.get_Item(i);
                comboBoxMapping.Items.Insert(i - 1, worksheet.Name);

         }
        }
    }

Any advice would be greatly appreciated.

Upvotes: 1

Views: 94

Answers (2)

Abbas
Abbas

Reputation: 4069

I would recommend EPPLUS if xlsx Excel format is to be dealt with.

Install epplus library from nuget package manager: enter image description here

enter image description here

Then using OfficeOpenXml;

Get the list of sheet names:

ExcelPackage DocInv = new ExcelPackage(new FileInfo(ExcelDocument));
DocList = DocInv.Workbook.Worksheets.AsEnumerable().Select(x => x.Name).ToList();

Upvotes: 0

sparrow
sparrow

Reputation: 980

You should look at using a 3rd party library for this that will make your life much easier than messing with interop.

Excel Data Reader

This will let you get all the sheet names and read data into a DataTable which then you can access however you want to get whatever data you need out of it. The GitHub Read Me page has simple examples that should solve your issue for you.

Upvotes: 1

Related Questions