The solution to read excel by using C#

Question

Situation:

I'm using C#, and I need to read the content from an excel file. The content in the excel file will be looks alike as the image.

The first column is the criteria, and the second column is the amount of the criteria. These content will have more than 1000 rows and will be placed at the last sheet of the excel file.

I'm trying to use File Helper to read the content from excel. But from the example provided, I only know that I can read text file but not excel file.

Questions:

1. Is there any better example to show me how to read the content from excel by using the FileHelpers?
2. Is there any other better solution with example that can help me to read the content from excel. Please direct me.

Answer 1

You could ExcelToEnumerable for this. Here's an example:

First define a class that represents a row of spreadsheet data. In your case, it might be something like:

public class SpreadsheetRow
{
    public string Category { get; set; }
    public int Frequency { get; set; }
}

Then use ExcelToEnumerable to convert the spreadsheet data into an IEnumerable of type SpreadsheetRow

var filePath = "../Path/To/Spreadsheet.xlsx";
IEnumerable<SpreadsheetRow> result = filePath.ExcelToEnumerable<SpreadsheetRow>(x => x.UsingHeaderNames(false)
    .Property(y => y.Category).UsesColumnNumber(0)
    .Property(y => y.Frequency).UsesColumnNumber(1));

Disclaimer: I'm the author of ExcelToEnumerable

Answer 2

I think you should use OpenXML which will give you excel data in Datatable format and later you can use as per your wish.

static void Main(string[] args)
{
    DataTable dt = new DataTable();

    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"..\..\example.xlsx", false))
    {

        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
        IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
        Worksheet workSheet = worksheetPart.Worksheet;
        SheetData sheetData = workSheet.GetFirstChild<SheetData>();
        IEnumerable<Row> rows = sheetData.Descendants<Row>();

        foreach (Cell cell in rows.ElementAt(0))
        {
            dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
        }

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

            for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
            {
                tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));
            }

            dt.Rows.Add(tempRow);
        }

    }
    dt.Rows.RemoveAt(0); //...so i'm taking it out here.

}


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

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

Note: Reference From Excel to DataTable in C# with Open XML

Answer 3

You can read an Excel by using Microsoft.Office.Interop.Excel Library . You can find the full process in attached link

How to read from Excel in C#

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            Excel.Range range ;

            string str;
            int rCnt = 0;
            int cCnt = 0;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;

            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2 ;
                    MessageBox.Show(str);
                }
            }

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 

    }
}