Uploading Excel Data into DataTable in C#

Uploading Excel Data into DataTable in C#

 Microsoft Excel is a widely used tool for storing and organizing data. Often, there arises a need to extract data from Excel files and work with it programmatically. In this blog post, we'll explore a generic method to upload data from an Excel file into a DataTable using C#.

using System;  
using System.Data;  
using System.IO;  
using ClosedXML.Excel;  
  
public class ExcelUploader  
{  
    /// <summary>  
    /// Converts an Excel sheet into a DataTable based on Stream input.  
    /// </summary>  
    /// <param name="stream">Provides a generic view of a sequence of bytes.</param>  
    /// <returns>DataTable converted from an Excel sheet.</returns>  
    public DataTable GetDataTableFromStream(Stream stream)  
    {  
        // Start reading the Excel file.  
        using (XLWorkbook workbook = new XLWorkbook(stream))  
        {  
            IXLWorksheet worksheet = workbook.Worksheet(1);  
  
            bool isFirstRow = true;  
  
            // Range for reading the cells based on the last cell used.  
            string readRange = "1:1";  
  
            DataTable dataTable = BindDataToDataTable(worksheet, isFirstRow, readRange);  
  
            return dataTable;  
        }  
  
    }  
  
    #region PRIVATE METHODS.  
    private static DataTable BindDataToDataTable(IXLWorksheet worksheet, bool isFirstRow, string readRange)  
    {  
        DataTable dataTable = new DataTable();  
  
        foreach (IXLRow row in worksheet.RowsUsed())  
        {  
            // If reading the first row (used), then add them as column names.  
            if (isFirstRow)  
            {  
                // Checking the last cell used for column generation in the datatable.  
                readRange = string.Format("{0}:{1}", 1, row.LastCellUsed().Address.ColumnNumber);  
                foreach (IXLCell cell in row.Cells(readRange))  
                {  
                    dataTable.Columns.Add(cell.Value.ToString());  
                }  
                isFirstRow = false;  
            }  
            else  
            {  
                // Adding a row in the datatable.  
                dataTable.Rows.Add();  
                int cellIndex = 0;  
                // Updating the values of the datatable.  
                foreach (IXLCell cell in row.Cells(readRange))  
                {  
                    dataTable.Rows[^1][cellIndex] = cell.Value.ToString();  
                    cellIndex++;  
                }  
            }  
        }  
  
        // If no data in the Excel file.  
        if (isFirstRow)  
            dataTable.Rows.Clear();  
  
        return dataTable;  
    }  
    #endregion  
}  
Now, let's see how to use this ExcelUploader class in your application:
class Program  
{  
    static void Main()  
    {  
        // Example usage  
        string excelFilePath = "path/to/your/excel/file.xlsx";  
  
        try  
        {  
            using (FileStream fs = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))  
            {  
                ExcelUploader excelUploader = new ExcelUploader();  
                DataTable dataTable = excelUploader.GetDataTableFromStream(fs);  
  
                // Now you can work with the DataTable as needed.  
                // Print DataTable for example:  
                PrintDataTable(dataTable);  
            }  
        }  
        catch (Exception ex)  
        {  
            Console.WriteLine($"An error occurred: {ex.Message}");  
        }  
    }  
  
    static void PrintDataTable(DataTable dataTable)  
    {  
        foreach (DataRow row in dataTable.Rows)  
        {  
            foreach (var item in row.ItemArray)  
            {  
                Console.Write($"{item}\t");  
            }  
            Console.WriteLine();  
        }  
    }  
}  

if you are using wab api you can use below code to upload excel file
public IActionResult Upload()  
{  
      
        IFormFile formFile = Request.Form.Files[0];  
  
        string fileExtention = Path.GetExtension(fileName);  
        string contentType = formFile.ContentType;  
  
        if (fileExtention == ".xlsx" && contentType == xlsxContentType)  
        {  
            DataTable dataTable = _excelService.ExcelToDatatable(formFile.OpenReadStream());  
  
              
            return Ok(_genericService.GetResponse("SUCCESS", null, new Dictionary<string, string> { { "status", result } }));  
        }  
        else  
        {  
            return BadRequest(_genericService.GetResponse(CustomResponseCodes.BAD_REQUEST.ToString(), fileTypeError, null));  
        }  
      
}  
 







Feel free to integrate this code into your application and customize it as per your requirements. This generic Excel upload utility can be a handy tool for dealing with Excel data programmatically.