CODEWITHSUNDEEP
c#-4.0
RobinHood
RobinHood

Reputation: 2397

Convert xls and xlsx to Datatable in C#

I am working in converting ths xls and xlsx to datatable in c#. I have used this code.

public DataTable ReadDataExcel(string filepath)
    {
        FileStream stream = File.Open(filepath, FileMode.Open, FileAccess.Read);
        IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
        excelReader.IsFirstRowAsColumnNames = true;
        DataSet result = excelReader.AsDataSet();
        DataTable dt = new DataTable();
        dt = result.Tables[0];
        return dt;
    }

In case of xls, its working fine. Whenever i used xlsx its not working. Its giving 'Object Reference' error.

Whether there are any other way to convert both the xls and xlsx to datatable. I am not interested in using 'Microsoft.Jet.OLEDB'....

Upvotes: 5

Views: 24979

Answers (3)

Jayachandran V
Jayachandran V

Reputation: 9

Add EPPlus dll from nudget. Add excel file content to memory stream. Then you can use as per below code.

ExcelPackage package = new ExcelPackage(stream);
ExcelWorksheet workSheet = package.Workbook.Worksheets.First();

Upvotes: 0

Vikram Singh
Vikram Singh

Reputation: 124

 public static DataTable MakeDataTablefromxls(string _path, string password = "")
    {
        DataTable dt = new DataTable();
        try
        {
            log.Info("MakeDataTablefromxls : Excel scraping started");
            xlsApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlsApp == null)
            {
                return null;
            }
            wb = xlsApp.Workbooks.Open(_path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
            sheets = wb.Worksheets;
            ws = (Excel.Worksheet)sheets.get_Item(1);
            range = ws.UsedRange;
            if (wb.ProtectStructure)
            {
                wb.Unprotect(password);
            }

            object[,] values = (object[,])range.Cells.Value;

            //To Create columns of DataTable as per column  range in xlxs sheet
            for (int i = 1; i <= values.GetLength(1); i++)
            {
                dt.Columns.Add("F" + (i));
            }

            //To Create rows of DataTable as per column  range in xlxs sheet
            for (int i = 2; i < values.GetLength(1); i++)
            {
                DataRow row = dt.NewRow();
                for (int j = 1; j < values.GetLength(1); j++)
                {
                    row[j - 1] = values[i, j];// assigning Data from object array to data Datarow 
                }
                dt.Rows.Add(row);
            }
            return dt;
        }
        catch (Exception ex)
        {
            log.Error("MakeDataTablefromxls : error : ", ex);
            return null;
        }
        finally
        {
            wb.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            xlsApp.Quit();
            GC.Collect();
            if (range != null) Marshal.FinalReleaseComObject(range);
            if (wb != null) Marshal.FinalReleaseComObject(wb);
            if (sheets != null) Marshal.FinalReleaseComObject(sheets);
            if (xlsApp != null) Marshal.FinalReleaseComObject(xlsApp);
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            dt.Dispose();
            log.Info("MakeDataTablefromxls : Excel scraping completed");
        }
    }

Upvotes: -2

RobinHood
RobinHood

Reputation: 2397

This is the answer.

For importing xlsx,

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

For importing xls,

IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

Upvotes: 5

Related Questions