Read value from excel

Question

I have a problem that I can't solve. I need to pass a value from one Selenium test to another. And the writing part works just fine. I saves the value to an excel file. The problem appears when I try to read it. I have a class which should be able to read the cell, but it won't.

Excel.cs

using System.IO;
using OfficeOpenXml;
using excel = Microsoft.Office.Interop.Excel;
namespace Core
{
    public sealed class Excel
    {
        #region Private Properties
        private static string InputPath { get; } = ConfigSettingProvider.InputSourcePath;

        private static string OutputPath { get; } = ConfigSettingProvider.OutputSourcePath;
        #endregion Private Properties

        public static string ReadCell (int sheet, int x, int y) // (sheet, row, column)
        {

            FileInfo existingFile = new FileInfo("..\\..\\..\\Property\\Input\\data1.xlsx");
            
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                return package.Workbook.Worksheets[sheet].Cells[x, y].Value.ToString();
            }
        }
     }
}

And then in Test Fixture class I am trying to call ReadCell method:

var cellPolicyNo = Excel.ReadCell(1, 1, 1);

But it simply won't even enter to read it. Immediately the exception is thrown: "The type initializer for 'Core.Excel' threw an exception". at Core.Excel.ReadCell(Int32 sheet, Int32 x, Int32 y)

What am I doing wrong? What should I change?

Thank you

score 0 · Answer 1 · answered Jan 05 '21 at 15:24

You might want to take a look at this thread to see if it helps with answering your question.

How to read single Excel cell value

This is the more common way of reading from an excel file that is more reliable. There are also ways of doing it through OLEDB but that is for reading the entire excel file. Hopefully this helps as it is similar to code I have used in the past to read data from excel files.

score 0 · Answer 2 · answered Jan 05 '21 at 15:29

It appears to be a static constructor problem, see here: https://stackoverflow.com/questions/4398334/the-type-initializer-for-myclass-threw-an-exception#:~:text=The%20type%20initializer%20for%20%27CSMessageUtility.,-CSDetails%27%20threw%20an&text=means%20that%20the%20static%20constructor,static%20members%20of%20that%20class.

Btw, it might perhaps help to try a different toolkit, namely EPPlus (the last free version is 4.5.3.3). I've tried to read cells also with Excel Interop but it didn't work out so well. With EPPlus you can use LINQ on the range objects of Excel.

Read value from excel

2 Answers2