Breaking News: Grepper is joining You.com. Read the official announcement!

excel to datatable in c#

Add Answer

Rasel Ahmed answered on February 3, 2022 Popularity 9/10 Helpfulness 2/10

answer excel to datatable in c#

related c# create datatable from excel file

related Convert DataTable to excel file c# using epplus

related create excel from datatable c# using interop

related c# read excel file into datatable

excel to datatable in c#

Comment

// Using OleDb:
private static DataTable  ExcelToDataTable(string fileFullPath)
        {
            try
            {
                DataTable dt = new DataTable();
                string StartingColumn = "A";
                string EndingColumn = "D";
                string StartReadingFromRow = "1";
                string HDR = "YES";
                string ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                            + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";

                OleDbConnection cnn = new OleDbConnection(ConStr);
                cnn.Open();

                DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                string sheetname = "";
                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                    {

                        sheetname = drSheet["TABLE_NAME"].ToString();
                        OleDbCommand oconn = new OleDbCommand("select * from ["
                                + sheetname + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", cnn);
                        OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                       
                        adp.Fill(dt);
                        cnn.Close();
                    }
                }

                return dt;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

// ------------------------------------------------------
// using openxmlSDK:
using System.Data;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Core_Excel.Utilities
{
    static class ExcelUtility
    {
        public static DataTable Read(string path)
        {
            var dt = new DataTable();

            using (var ssDoc = SpreadsheetDocument.Open(path, false))
            {
                var sheets = ssDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                var relationshipId = sheets.First().Id.Value;
                var worksheetPart = (WorksheetPart) ssDoc.WorkbookPart.GetPartById(relationshipId);
                var workSheet = worksheetPart.Worksheet;
                var sheetData = workSheet.GetFirstChild<SheetData>();
                var rows = sheetData.Descendants<Row>().ToList();

                foreach (var row in rows) //this will also include your header row...
                {
                    var tempRow = dt.NewRow();

                    var colCount = row.Descendants<Cell>().Count();
                    foreach (var cell in row.Descendants<Cell>())
                    {
                        var index = GetIndex(cell.CellReference);

                        // Add Columns
                        for (var i = dt.Columns.Count; i <= index; i++)
                            dt.Columns.Add();

                        tempRow[index] = GetCellValue(ssDoc, cell);
                    }

                    dt.Rows.Add(tempRow);
                }
            }

            return dt;
        }

        private static string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            var stringTablePart = document.WorkbookPart.SharedStringTablePart;
            var value = cell.CellValue.InnerXml;

            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
                return stringTablePart.SharedStringTable.ChildElements[int.Parse(value)].InnerText;

            return value;
        }

        public static int GetIndex(string name)
        {
            if (string.IsNullOrWhiteSpace(name))
                return -1;

            int index = 0;
            foreach (var ch in name)
            {
                if (char.IsLetter(ch))
                {
                    int value = ch - 'A' + 1;
                    index = value + index * 26;
                }
                else
                    break;
            }

            return index - 1;
        }
    }
}

Popularity 9/10 Helpfulness 2/10 Language csharp

Source: Grepper

Tags: c# datatable excel

Link to this answer
Share Copy Link

Contributed on Oct 20 2022

Rasel Ahmed

0 Answers Avg Quality 2/10

Closely Related Answers

c# create datatable from excel file

Comment

To create a DataTable from an Excel file in C#, you can use the ExcelDataReader and System.Data libraries.

using System.Data;
using ExcelDataReader;
using System.IO;

// ...

// Define the path to the Excel file
string filePath = @"C:\path\to\file.xlsx";

// Open the file using a stream
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    // Create the reader object
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        // Create the dataset to hold the data
        var result = reader.AsDataSet();

        // Get the first table (assuming there is only one)
        DataTable table = result.Tables[0];
        
        // Do something with the table...
    }
}

Popularity 10/10 Helpfulness 4/10 Language csharp

Source: Grepper

Tags: c# c

Link to this answer
Share Copy Link

Contributed on Mar 05 2023

SubZero

0 Answers Avg Quality 2/10

Convert DataTable to excel file c# using epplus

Comment

 /// <summary>
        /// Convert DataTable to Excel File
        /// </summary>
        /// <param name="filePath">Excel FilePath</param>
        /// <param name="table">DataTable</param>
        /// <param name="fileName">Excel Filename</param>
        /// <returns></returns>
        public bool Write(string filePath, DataTable table, string fileName)
        {
            try
            {
                bool success = false;
                var today = Convert.ToDateTime(DateTime.Now).ToString("yyyyMMddHHmmss");
                //delete existing files
                // Util_Directory.DeleteFiles(filePath);
                var newDir = filePath + "\\" + today;
                //if dir not exist create
                Util_Directory.CreateDirectory(newDir);
                FileInfo file = new FileInfo(Path.Combine(newDir, fileName));
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
          
                using (var package = new ExcelPackage(file))
                {
                    var workSheet = package.Workbook.Worksheets.Add("Sheet1");
                    //add column headers
                    var columns = table.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList();
                    int i = 1;
                    foreach (var column in columns)
                    {
                        workSheet.Cells[1, i].Value = column;
                        i++;
                    }

                    // add row values
                    int startRow = 2;
                    foreach (DataRow row in table.Rows)
                    {
                        int startColumn = 1;
                        foreach (var item in row.ItemArray)
                        {
                            workSheet.Cells[startRow, startColumn].Value = item;
                            startColumn++;
                        }
                        startRow++;
                    }

                    workSheet.Cells.AutoFitColumns();
                    package.Save();
                    success = true;
                }

                return success;
            }
            catch (Exception)
            {
                throw;
            }
        }

Popularity 9/10 Helpfulness 3/10 Language csharp

Source: Grepper

Tags: c# datatable

Link to this answer
Share Copy Link

Contributed on May 10 2022

BlackSwan

0 Answers Avg Quality 2/10

create excel from datatable c# using interop

Comment

using System;
using System.Data;
using Microsoft.Office.Interop.Excel;

namespace ExcelFromDataTableExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new DataTable
            DataTable dataTable = new DataTable("TestTable");

            // Add some columns to the DataTable
            dataTable.Columns.Add("Name", typeof(string));
            dataTable.Columns.Add("Age", typeof(int));

            // Add some rows to the DataTable
            dataTable.Rows.Add("John Smith", 35);
            dataTable.Rows.Add("Jane Doe", 25);
            dataTable.Rows.Add("Bob Johnson", 42);

            // Create a new Excel application
            Application excel = new Application();

            // Add a new workbook
            Workbook workbook = excel.Workbooks.Add(Type.Missing);

            // Get the first worksheet
            Worksheet worksheet = (Worksheet)workbook.ActiveSheet;

            // Set the worksheet name
            worksheet.Name = "TestSheet";

            // Write the DataTable to the worksheet
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    worksheet.Cells[i + 1, j + 1] = dataTable.Rows[i][j].ToString();
                }
            }

            // Save the workbook
            workbook.SaveAs(@"C:\Temp\TestWorkbook.xlsx");

            // Close the workbook and Excel application
            workbook.Close();
            excel.Quit();
        }
    }
}

Popularity 6/10 Helpfulness 3/10 Language csharp

Source: Grepper

Tags: c# datatable excel interop intero

Link to this answer
Share Copy Link

Contributed on Mar 05 2023

SubZero

0 Answers Avg Quality 2/10

c# read excel file into datatable

Comment

string sSheetName = null;
string sConnection = null;
DataTable dtTablesList = default(DataTable);
OleDbCommand oleExcelCommand = default(OleDbCommand);
OleDbDataReader oleExcelReader = default(OleDbDataReader);
OleDbConnection oleExcelConnection = default(OleDbConnection);

sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test.xls;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";

oleExcelConnection = new OleDbConnection(sConnection);
oleExcelConnection.Open();

dtTablesList = oleExcelConnection.GetSchema("Tables");

if (dtTablesList.Rows.Count > 0) 
{
    sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
}

dtTablesList.Clear();
dtTablesList.Dispose();


if (!string.IsNullOrEmpty(sSheetName)) {
    oleExcelCommand = oleExcelConnection.CreateCommand();
    oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
    oleExcelCommand.CommandType = CommandType.Text;
    oleExcelReader = oleExcelCommand.ExecuteReader();
    nOutputRow = 0;

    while (oleExcelReader.Read())
    {
    }
    oleExcelReader.Close();
}
oleExcelConnection.Close();

Popularity 9/10 Helpfulness 2/10 Language csharp

Source: stackoverflow.com

Tags: c# c

Link to this answer
Share Copy Link

Contributed on Feb 03 2022

CRNmd

0 Answers Avg Quality 2/10

excel to datatable in c#

Contents

More Related Answers

excel to datatable in c#

Closely Related Answers

c# create datatable from excel file

Convert DataTable to excel file c# using epplus

create excel from datatable c# using interop

c# read excel file into datatable

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.