3

I am using Asp.net with C#. I need to import data from an Excel sheet to a DataTable. The sheet has 100,000 records with four columns: Firstname, LastName, Email,Phone no.

How can I do this?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Thinksright
  • 31
  • 1
  • 1
  • 4

3 Answers3

1

Use the following code:

public static DataTable exceldata(string filePath)
    {     
        DataTable dtexcel = new DataTable();
           bool hasHeaders = false;
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;
            if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            else
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            //Looping Total Sheet of Xl File
            /*foreach (DataRow schemaRow in schemaTable.Rows)
            {
            }*/
            //Looping a first Sheet of Xl File
            DataRow schemaRow = schemaTable.Rows[0];
            string sheet = schemaRow["TABLE_NAME"].ToString();
            if (!sheet.EndsWith("_"))
            {
                string query = "SELECT  * FROM [" + sheet3 + "]";
                OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                dtexcel.Locale = CultureInfo.CurrentCulture;
                daexcel.Fill(dtexcel);
            }

        conn.Close();
        return dtexcel;

    }

Source: http://www.codeproject.com/Questions/445400/Read-Excel-Sheet-Data-into-DataTable

You may also refer the following question: Importing Excel into a DataTable Quickly if you wish to import faster.

Community
  • 1
  • 1
milan m
  • 2,164
  • 3
  • 26
  • 40
0

I'm not sure if this will work in ASP.NET but it works in WPF so maybe there's something you can take from it?

Anyway, at the global scope:

Microsoft.Office.Interop.Excel.Application xls;

Then to select and read a spreadsheet:

     private void readSheet()
    {
        // Initialise and open file picker
        OpenFileDialog openfile = new OpenFileDialog();
        openfile.DefaultExt = ".xlsx";
        openfile.Filter = "Office Files | *xls;.xlsx";
        var browsefile = openfile.ShowDialog();

        if (browsefile == true)
        {
            string path = openfile.FileName;

            xls = new Microsoft.Office.Interop.Excel.Application();

            // Dynamic File Using Uploader... Note the readOnly flag is true
            Workbook excelBook = xls.Workbooks.Open(path, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Worksheet excelSheet = (Worksheet)excelBook.Worksheets.get_Item(1); ;
            Range excelRange = excelSheet.UsedRange;

            // Make default cell contents
            string strCellData = String.Empty;
            double douCellData;

            // Initialise row and column
            int rowCnt, colCnt = 0;

            // Initialise DataTable
            System.Data.DataTable dt = new System.Data.DataTable();

            // Loop through first row of columns to make header
            for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
            {
                string strColumn = "";
                strColumn = Convert.ToString((excelRange.Cells[1, colCnt] as Range).Value2);
                var Column = dt.Columns.Add();
                Column.DataType = Type.GetType("System.String");

                // Check & rename for duplicate entries
                if (dt.Columns.Contains(strColumn))
                    Column.ColumnName = (strColumn + ", " + colCnt);
                else
                    Column.ColumnName = strColumn;
            }
            dt.AcceptChanges();

            // Fill in the rest of the cells
            for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
            {
                string strData = "";
                for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
                {
                    try
                    {
                        strCellData = Convert.ToString((excelRange.Cells[rowCnt, colCnt] as Range).Value2);
                        strData += strCellData + "|";
                    }
                    catch (Exception ex)
                    {
                        douCellData = (excelRange.Cells[rowCnt, colCnt] as Range).Value2;
                        strData += douCellData.ToString() + "|";
                        Console.Write(ex.ToString());
                    }
                }
                strData = strData.Remove(strData.Length - 1, 1);
                dt.Rows.Add(strData.Split('|'));

            }

            dtGrid.ItemsSource = dt.DefaultView;
            try
            {
                excelBook.Close(true, null, null);
            }
            catch (System.Runtime.InteropServices.COMException comEX)
            {
                Console.Write("COM Exception: " + comEX.ToString());
            }
            xls.Quit();
        }
    }
Jalapeno
  • 202
  • 1
  • 2
  • 9
  • This can work, but it requires MS Office to be installed on the web server and will literal open an instance of excel behind the scenes for every http request where this is used. – Joel Coehoorn Aug 02 '21 at 20:16
0

Speed Problems?

I'll note several ways to do this:

  • ODBC (answered here)
  • Interop (answered here)

These have drawbacks, they might not be fast; Interop requires excel, runs it, and can cause lots of problems with re-running it or the web server trying to run it.

please try @milan_m solution first. If it has problems come back here.

So some faster, potentially better solutions are as such.

  • NPOI
  • Save-As CSV

NPOI is available as a NuGet for C# and will read excel files very well. Yes this is a product recommendation, but you didn't ask for one. It is a well-maintained project and will be relevant for readers into the 2030s.

https://github.com/nissl-lab/npoi

You'll want to use NPOI if ODBC is too slow, and your users are uploading a different XLSX file as part of the use case. Unless there are only 2 or 3 internal power users you are in contact with, then you can require them to upload it as CSV.

What if the use case is: You just use one .XLSX file that's the same for all users, you deploy it with the app?

You didn't mention if this is the case or not and it makes a HUGE difference. you definitely will be miles ahead if you save as csv and consume that from the startup of the program. Or, if you need it in a datatable, import it to the data table at dev time and save it to XML file using a method on the dataset object (you have to put the tbl into a set to save as XML I believe ... many examples abound).

If you need to do super flexible lookups, a datatable (or object collection and linq-to-objects) is good.

However if you have to look up items at extreme speed, and not via ranges but just by exact match, load it to a dictionary or dictionaries at startup time, from a CSV or similar.

I did this for a power user who was searching a spreadsheet of about 2-3lakh / records with interop+excel ... operation went from 90 minutes to 30 seconds. Literally. Dictionary is about the fastest way to look stuff up in .Net, if you can fit that stuff in memory, that is, and don't have to reload different data all the time (so keep your RDBMS).

Oops.

Just now saw this question is 7 years old. @#$^#@!!!

FastAl
  • 6,194
  • 2
  • 36
  • 60