How to Export Excel sheet to DataTable

Question

0.00/5 (No votes)

See more:

I used following code to export

C#

public DataTable Import(String path)
        {
            System.Data.DataTable dt = null;

                try
                  {

                      int index = 0;
                      object rowIndex = 2;

                      dt = new System.Data.DataTable();
                      dt.Columns.Add("Client Name");
                      dt.Columns.Add("Course Name");
                      dt.Columns.Add("File Name");
                      dt.Columns.Add("File Type");
                      dt.Columns.Add("Developer");
                      dt.Columns.Add("Status");



                      DataRow row;

                      Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
                      Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                      Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;



                      while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null)
                      {
                          rowIndex = 2 + index;
                          row = dt.NewRow();
                          row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                          row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
                          row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
                          row[3] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 4]).Value2);
                          row[4] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 5]).Value2);
                          row[5] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2);
                          index++;
                          dt.Rows.Add(row);


                      }
                      app.Workbooks.Close();
                    }
                    catch(Exception ex)
                    {
                        lblError.Text = ex.Message;
                    }
                    return dt;
        }

But its leading to following error :

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).<br />

So how to upload excel without using Microsoft.Office.Interop.Excel ?

Posted 19-Apr-11 0:02am

Sai Goutham

Updated 19-Apr-11 0:09am

Tarun.K.S

v3

Add a Solution

4 solutions

Solution 4

There is another way using Oledb connection

MSIL

private DataTable BindExcelData(string strFileName)
        {
            // Create a new Adapter
            OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();

            // Create a DataSet
            DataSet objDataSet = new DataSet();

            try
            {
                // retrieve the Select command for the Spreadsheet
                objDataAdapter.SelectCommand = ExcelConnection(strFileName);

                if (objDataAdapter.SelectCommand != null)
                {
                    // Populate the DataSet with the spreadsheet worksheet data
                    objDataAdapter.Fill(objDataSet);

                }
            }
            catch (Exception objException)
            {
                // this.LogException(objException, "ExcelConnection");
                throw objException;
            }
            finally
            {
                if (objXConn.State == ConnectionState.Open)
                {
                    objXConn.Close();
                    objXConn.Dispose();
                }
            }

            return objDataSet.Tables[0];
        }

private OleDbCommand ExcelConnection(string strFileName)
        {
            OleDbCommand objCommand = null;
            string strFileName = string.Empty;

            try
            {


                string xConnStr = GetConnectionString(strFileName);
                DataTable dtSheetNames = new DataTable();

                // create your excel connection object using the connection string
                objXConn = new OleDbConnection(xConnStr);
                objXConn.Open();
                dtSheetNames = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);


                if (ExcelExceptions(dtSheetNames, ClsCommon.SheetName))
                {
                    objCommand = new OleDbCommand("SELECT * FROM [" + strExcelSheet + "]", objXConn);
                }
                else
                {
                    return null;
                }

            }
            catch (Exception objException)
            {
                //this.LogException(objException, "ExcelConnection");
                throw objException;

            }
            return objCommand;
        }

        /// <summary>
        /// Set the connection string as per the different versions of excel sheet
        /// </summary>
        /// <param name="strFileName"></param>
        /// <returns></returns>
        private string GetConnectionString(string strFileName)
        {
            FileInfo fileInfo = new FileInfo(strFileName);
            if (fileInfo.Extension == ".xlsx")
                return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties=\"Excel 12.0;\"";
            else
                return "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties=\"Excel 8.0;\"";

        }

Posted 20-Apr-11 13:45pm

DipaliKolhe

Solution 3

MIDL

public class ExcelToDataTable
    {
        public static DataTable ExtractExcelSheetValuesToDataTable(string xlsxFilePath, string sheetName, out bool blnResult)
        {
            blnResult = false;
            DataTable dt = new DataTable();
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xlsxFilePath, true))
            {
                //Access the main Workbook part, which contains data
                WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                WorksheetPart worksheetPart = null;
                if (!string.IsNullOrEmpty(sheetName))
                {
                    Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();
                    if (ss != null)
                    {
                        worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
                        if (worksheetPart != null)
                        {
                            blnResult = true;
                        }
                    }
                    else
                    {
                        blnResult = false;
                    }
                }
                else
                {
                    blnResult = false;
                }
                if (!blnResult)
                {
                    return null;
                }
                SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
                if (worksheetPart != null)
                {
                    Row firstRow = worksheetPart.Worksheet.Descendants<Row>().FirstOrDefault();
                    //Get Columns
                    if (firstRow != null)
                    {
                        foreach (Cell c in firstRow.ChildElements)
                        {
                            string value = GetValue(c, stringTablePart);
                            //Add Columns to datatable
                            dt.Columns.Add(value);
                        }
                    }
                    IEnumerable<Row> dataRows = from row in worksheetPart.Worksheet.Descendants<Row>() where row.RowIndex > 1 select row;
                    foreach (Row row in dataRows)
                    {
                        //Create New Row
                        DataRow dr = dt.NewRow();
                        int j = 0;
                        int n;
                        if (row != null)
                        {
                            Boolean blnIsEmpty = true;
                            var catalogueValues =
                            from cell in row.Descendants<Cell>()
                            select new ExcelCol
                              {
                                  cellVal = (cell.CellValue != null ? (Int32.TryParse(cell.CellValue.InnerText, out n) ? (cell.DataType != null && cell.DataType == CellValues.SharedString ? stringTablePart.SharedStringTable.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText) : cell.CellValue.Text) : ""),
                                  celRef = GetColumnNumber(Regex.Replace(cell.CellReference.Value, @"\d", ""))
                              };
                            foreach (var item in catalogueValues)
                            {
                                if (!string.IsNullOrEmpty(item.cellVal))
                                {
                                    dr[item.celRef - 1] = item.cellVal;
                                    blnIsEmpty = false;
                                }
                                j++;
                                if (j == dt.Columns.Count)
                                    break;
                            }
                            if (!blnIsEmpty)
                            {
                                dt.Rows.Add(dr);
                            }
                        }
                    }
                }
            }
            return dt;
        }
        private static int GetColumnNumber(string name)
        {
            int number = 0;
            int pow = 1;
            for (int i = name.Length - 1; i >= 0; i--)
            {
                number += (name[i] - 'A' + 1) * pow; pow *= 26;
            }
            return number;
        }
        private static string GetValue(Cell cell, SharedStringTablePart stringTablePart)
        {
            if (cell.ChildElements.Count == 0)
            {
                return null;
            }
            //get cell value
            string value = cell.ElementAt(0).InnerText;//CellValue.InnerText;
            Int32 val;
            if (Int32.TryParse(value, out val))
            {
                //Look up real value from shared string table
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                {
                    value = stringTablePart.SharedStringTable.ChildElements[val].InnerText;
                }
            }
            else
            {
                value = cell.CellValue.Text;
            }
            return value;
        }
    }

Posted 20-Apr-11 13:37pm

DipaliKolhe

Comments

samiji 21-Sep-12 5:36am

Hi, thank you so much for posting your code on this site. I'm new to c# and I used your code to convert excel to DataTable. I have used 'solution 3' of your code but I'm getting an error at this line of your code:

var catalogueValues = from cell in row.Descendants() select new ExcelCol

The error I'm getting is this:

The type or namespace name 'ExcelCol' could not be found (are you missing a using directive or an assembly reference?)

Looking forward for your advise,

Thanks in advance,

Sam

rebounderarun 13-Aug-14 11:37am

nice Solution for empty cells using openXML concept

Solution 2

Or you could use something like this:

BULK INSERT DataBaseName.dbo.[tableName]
   FROM 'c:\Upload.csv'
   WITH
      (
         FIELDTERMINATOR = ',',
         ROWTERMINATOR = '\n'             
      )

Good luck!

Posted 19-Apr-11 0:12am

E.F. Nijboer

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DipaliKolhe · Accepted Answer · 2011-04-19T00:07:00

Solution 1

Use

VB

DocumentFormat.OpenXml.Spreadsheet

Posted 19-Apr-11 0:07am

DipaliKolhe

Comments

Sai Goutham 20-Apr-11 6:47am

Can u send me the Source code for this ?