C# - Ways to copy excel data into an array

Question

I have written some code based on a specification from my boss. A requirement was to import a lot of Excel data into the program. I did this by copying the used range of the Excel sheet to the Clipboard and then used that to write to the array. This was done using Interop. This does work well, and it's code thats been in use for around 4months without any problems.

However, I wrote this code, without thinking too much at the beginning of my internship. I am now re-visiting it, in a quiet moment, and it got me thinking, is there a better (more efficient, elegant) way of doing this? Looking back now, it seems like a bit of a hack.

Thanks.

There's Jet: connectionstrings.com/excel

Blorgbeard
– Blorgbeard

2011-04-08 13:26:17 +00:00
Commented Apr 8, 2011 at 13:26 — Blorgbeard
– Blorgbeard, Commented Apr 8, 2011 at 13:26

Michiel Cornille · Accepted Answer · 2011-04-08 13:30:31Z

2

Using a c# driver you can read the excel file directly without using interop:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Immo.Areas.Administration.Controllers
{
    public class SomethingSometingExcelClass
    {
        public void DoSomethingWithExcel(string filePath)
        {

            List<DataTable> worksheets = ImportExcel(filePath);
            foreach(var item in worksheets){
                foreach (DataRow row in  item.Rows)
                {
                   //add to array
                }

            }
        }
        /// <summary>
        /// Imports Data from Microsoft Excel File.
        /// </summary>
        /// <param name="FileName">Filename from which data need to import</param>
        /// <returns>List of DataTables, based on the number of sheets</returns>
        private List<DataTable> ImportExcel(string FileName)
        {
            List<DataTable> _dataTables = new List<DataTable>();
            string _ConnectionString = string.Empty;
            string _Extension = Path.GetExtension(FileName);
            //Checking for the extentions, if XLS connect using Jet OleDB
            if (_Extension.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
            {
                _ConnectionString =
                    "Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties=Excel 8.0";
            }
            //Use ACE OleDb
            else if (_Extension.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
            {
                _ConnectionString =
                    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0";
            }

            DataTable dataTable = null;

            using (OleDbConnection oleDbConnection =
                new OleDbConnection(string.Format(_ConnectionString, FileName)))
            {
                oleDbConnection.Open();
                //Getting the meta data information.
                //This DataTable will return the details of Sheets in the Excel File.
                DataTable dbSchema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
                foreach (DataRow item in dbSchema.Rows)
                {
                    //reading data from excel to Data Table
                    using (OleDbCommand oleDbCommand = new OleDbCommand())
                    {
                        oleDbCommand.Connection = oleDbConnection;
                        oleDbCommand.CommandText = string.Format("SELECT * FROM [{0}]",
                            item["TABLE_NAME"].ToString());
                        using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter())
                        {
                            oleDbDataAdapter.SelectCommand = oleDbCommand;
                            dataTable = new DataTable(item["TABLE_NAME"].ToString());
                            oleDbDataAdapter.Fill(dataTable);
                            _dataTables.Add(dataTable);
                        }
                    }
                }
            }
            return _dataTables;
        }
    }
}

edited Apr 8, 2011 at 13:30

answered Apr 8, 2011 at 13:29

Michiel Cornille

2,1171 gold badge19 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

R. Martinho Fernandes Over a year ago

You should format code by putting four spaces before each line. You can also select it and click the {} button. More helpful tips at the Markdown Editing Help page.

Darren Young Over a year ago

Thanks for the long answer. I have been playing around with oleDB today actually. And using a xlsx file with this string: string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + "; Extended Properties=Excel 8.0", I get an error on conn.Open(), saying file not in correct format? Any ideas? Thanks

Michiel Cornille Over a year ago

have you tried using my version with the {0} and string.format? If not.. Make sure you have the right driver installed on your IIS

R. Martinho Fernandes · Accepted Answer · 2011-04-08 13:26:25Z

0

Yes, it is definitely a hack. If you use interop to get the used range you can then loop through that range directly and write to the array without any clipboard interaction.

Something along these lines:

for(int i = 1; i <= sheet.UsedRange.Rows.Count; i++)
{
    for(int j = 1; j <= sheet.UsedRange.Columns.Count; j++)
    {
        DoStuffWith(sheet.UsedRange.Cells(i, j).Value)
    }
}

answered Apr 8, 2011 at 13:26

R. Martinho Fernandes

236k73 gold badges443 silver badges518 bronze badges

1 Comment

Darren Young Over a year ago

Yes, I tried this at the time, but because you are reading the cell's individually, the speed is really slow (very noticeable).

Blorgbeard · Accepted Answer · 2011-04-08 13:31:48Z

0

It's a hack alright.

I have successfully used a Jet connection (http://connectionstrings.com/excel) before.

The other option would be to save your Excel spreadsheet in CSV format and read the file yourself. This might not be practical depending on what's in your spreadsheet though.

answered Apr 8, 2011 at 13:31

Blorgbeard

104k50 gold badges237 silver badges276 bronze badges

Collectives™ on Stack Overflow

C# - Ways to copy excel data into an array

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related