Click here to Skip to main content
16,016,489 members
Please Sign up or sign in to vote.
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
Updated 19-Apr-11 0:09am
v3

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;
        }
    }
 
Share this answer
 
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
Use
VB
DocumentFormat.OpenXml.Spreadsheet
 
Share this answer
 
Comments
Sai Goutham 20-Apr-11 6:47am    
Can u send me the Source code for this ?
Or you could use something like this:
BULK INSERT DataBaseName.dbo.[tableName]
   FROM 'c:\Upload.csv'
   WITH
      (
         FIELDTERMINATOR = ',',
         ROWTERMINATOR = '\n'             
      )


Good luck!
 
Share this answer
 
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;\"";

        }
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900