C# read excel file

Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets.Versions of Excel up to 7.0 had a limitation in the size of their data sets of 16K (214 = 16384) rows. Versions 8.0 through 11.0 could handle 64K (216 = 65536) rows and 256 columns (28 as label 'IV'). Version 12.0 can handle 1M (220 = 1048576) rows, and 16384 (214 as label 'XFD') columns.

There are multiple extensions of Excel:

• .xls
• .xlsX
• .xlsm
• .xlsb - Excel 2007

Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs.Today we will see c# read excel file. We use EPplus library for reading data from excel. There are different versions are available in Epplus library.There are different versions are available in Epplus library.

C# read excel file

Step 1: First of all create new project in visual studio, we can create a project also in visual studio code editor. Both editors are different that is in visual studio we install the packages by “NuGet Package Manager” and in visual studio code we install the packages by command prompt or any different ways. So you can use any one of the editor for read excel data but in this post we must use visual studio C# language for read excel file.

Step 2: If the project is created successfully then first of all we must need to install package “EPPlus 4.1.0” library in our project because using this library we read excel file in project. You can install the package by “NuGet Package Manager”. It’s not compulsory to use this package for read excel file, you can use another package library for read excel file.

Step 3: For installing “EPPlus 4.1.0” library in our project, right click on “References” and then click on “Manage Nuget Packages” and one package manager modal is open then Search “EPplus” word in search box, you can see Epplus library (Version 4.1.0) available in Manage Nuget Packages. Click on install.

Step 4: Now, We add new folder in our project because in this folder we put the excel file that we will read by c# code, so create new folder (Excel) in project and then put the excel file with filled data that you can see below image. You can see in excel file we have managed student’s basic information.

Step 5: You know in excel file we have managed student information with some data that’s are Roll No, Student Name , Description and Gender field and you can see we have managed single data in single cell or multiple data in single cell and another is selection of Gender.

Step 6: We have installed Epplus library and excel file available in project Excel folder. If you use Epplus library you must add “using OfficeOpenXml” NameSpace in .aspx.cs file and add using (var package = new ExcelPackage()) in code.

Step 7: Now, we will read excel file from project folder and do the operation for read excel cell data. we have added all the required folders and then installed required packages and added namespace in (.aspx.cs) file. You can see code below that C# read excel file.

Code :

//...first fetch excel file from project folder or any others location…

var fi = new FileInfo(@"D:BlogProjectsReadExcelDataReadExcelDataExcelSI.xlsx");

using (var package = new ExcelPackage(fi))
{
      var workbook = package.Workbook;

//..In Excel file you can add more than one ExcelSheet but here we get first excelsheet…

      var worksheet = workbook.Worksheets.First();

       //...here we have decrement of 1 count because do not calculate header row...
       int noOfRow = worksheet.Dimension.End.Row - 1;

       //... we know our data start from second row so set row 2..
       int row = 2;

       for(int a = 0; a < noOfRow; a++)
        {

             // studentRollno
             string rollNo = worksheet.GetValue(row, 1).ToString();

            // studentName
            string studentName = worksheet.GetValue(row, 2).ToString();
                       
           //classroomDescription
           string classroomDescription = Regex.Match(worksheet.GetValue(row, 3).ToString(),
            @"Classroom: .+").Groups[0].Value;

           classroomDescription = string.IsNullOrWhiteSpace(classroomDescription) ?
           string.Empty : classroomDescription.Remove(0, 11);

           //sportsDescription
          string sportsDescription = Regex.Match(worksheet.GetValue(row, 3).ToString(),
          @"Sports: .+").Groups[0].Value;
        
          sportsDescription = string.IsNullOrWhiteSpace(sportsDescription) ? string.Empty :
          sportsDescription.Remove(0, 8);

          // Gender
          bool Male = Regex.Match(worksheet.GetValue(row, 4).ToString(), @"[(][X][)]
           bMale?b").Groups[0].Success;         
         bool Female = Regex.Match(worksheet.GetValue(row, 4).ToString(), @"[(][X][)]
        bFemale?b").Groups[0].Success;

        string gender = Male ? "Male" : string.Empty;
        gender = string.IsNullOrEmpty(gender) && Female ? "Female" : string.Empty;

      row++;

  }
}

Step 8: Using above code you can read excel file, first we have read RollNo, Student Name very easy because that is single field data. But in description field we have store two field that’s “Classroom” and “sports” field in one cell so it is very hard to get both different values that’s solution available in above code. We will see explanation of above codes, first we have read excel file from folder (Excel). You can assume excel is one of workbook. Second is we got the first worksheet from excel file. In third one we count the no of rows filled in current worksheet but we do not count first row because in first row we have set the title of fields. In forth one we start the loop based on count, in this loop we have read the all the data from first worksheet with cell number, you can see in above code we have used Regular expression for getting selection of Gender. We have used checkbox for select gender that is very difficult to get selected value from checkbox but we have written that code above Here finally end of c# read excel file. You can do the same in your project and see the result.In Next post, We will see how to read image from Excel (.xlsx) file.