-1

I searched the official documentation, but it is focused on creating the Excel file and not on reading. Any code snippet as an example will help me!

How do I read Excel data? 

COMPANY     | DOCUMENT  | 
COMPANYONE  | 123455986 | 
COMPANYTWO  | 123455986 |
COMPANYTHREE| 123455986 |
stringnome
  • 233
  • 1
  • 7
  • 15
  • 1
    What are you trying to do with the data you read in? What methods have you tried already? Before anyone can really answer your question we need a better idea of what you're trying to do. – ScruffMcGruff Oct 07 '15 at 18:24
  • For just a simple example of reading Excel files see this [question](http://stackoverflow.com/questions/11685204/reading-excel-spreasheet-using-epplus) – peteb Oct 07 '15 at 18:25
  • just read the spreadsheet data, which contains a list of clients will have to consult these customer information there is no registration is necessary to create the record in the database. – stringnome Oct 07 '15 at 18:38
  • just want to find a way to read the data – stringnome Oct 07 '15 at 18:43

3 Answers3

2

Here is a method that will read csv and excel data, and return as datatable

Note- Install-Package ExcelDataReader via nuget 

using Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO; 
using System.Text; 

public static DataTable LoadDataTable(string filePath)
        {
            string fileExtension = Path.GetExtension(filePath);
            switch (fileExtension.ToLower())
            {
                case ".xlsx":
                    return ConvertExcelToDataTable(filePath, true);
                case ".xls":
                    return ConvertExcelToDataTable(filePath);
                case ".csv":
                    return ConvertCsvToDataTable(filePath);
                default:
                    return new DataTable();
            }

        }


public static DataTable ConvertExcelToDataTable(string filePath, bool isXlsx = false)
        {
            FileStream stream = null;
            IExcelDataReader excelReader = null;
            DataTable dataTable = null;
            stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
            excelReader = isXlsx ? ExcelReaderFactory.CreateOpenXmlReader(stream) : ExcelReaderFactory.CreateBinaryReader(stream);
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();
            if (result != null && result.Tables.Count > 0)
                dataTable = result.Tables[0];
            return dataTable;
        }

public static DataTable ConvertCsvToDataTable(string filePath)
        {
            DataTable dt = new DataTable();
            using (StreamReader sr = new StreamReader(filePath))
            {
                string[] headers = sr.ReadLine().Split(',');
                foreach (string header in headers)
                {
                    dt.Columns.Add(header);
                }
                while (!sr.EndOfStream)
                {
                    string[] rows = sr.ReadLine().Split(',');
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < headers.Length; i++)
                    {
                        dr[i] = rows[i];
                    }
                    dt.Rows.Add(dr);
                }

            }
            return dt;
        }
Velkumar
  • 446
  • 1
  • 8
  • 20
2

Or use Excel Interop:

using Microsoft.Office.Interop.Excel;

...

        Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workbook = xl.Workbooks.Open(@"C:\test.xlsx");
        Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Sheets[1];


        int numRows = sheet.UsedRange.Rows.Count;
        int numColumns = 2;     // according to your sample

        List<string[]> contents = new List<string[]>();
        string [] record = new string[2];

        for (int rowIndex = 1; rowIndex <= numRows; rowIndex++)  // assuming the data starts at 1,1
        {
            for (int colIndex = 1; colIndex <= numColumns; colIndex++)
            {
                Range cell = (Range)sheet.Cells[rowIndex, colIndex];
                if (cell.Value != null)
                {
                    record[colIndex-1] = Convert.ToString( cell.Value);
                }
            }
            contents.Add(record);
        }

        xl.Quit();
        Marshal.ReleaseComObject(xl);
Ric Gaudet
  • 898
  • 6
  • 16
  • This works when you need other properties or information that can only be had from the Excel object model, but if you need the data only, the object model is significantly slower than the OleDB provider method. – StingyJack May 19 '18 at 19:29
2

I have had the same issue a while ago.

I used this solution to Read Data From an Excel File (.xls) in ASP.NET: http://www.dotnetcurry.com/ShowArticle.aspx?ID=138 .

Simply read the range via SQL and post it into a GridView on the webpage.

It looked to me simpler to understand for a not experienced ASP.NET/C# developer.

George
  • 653
  • 6
  • 18