EPPlus - Read Excel Table

Question

Using EPPlus  I want to read an excel table  then store all the contents from each column into its corresponding List  I want it to recognize the table s heading and categorize the contents based on that   For example  if my excel table is as below   Id    Name     Gender  1    John     Male  2    Maria    Female  3    Daniel   Unknown   I want the data to store in List lt ExcelData gt  where  public class ExcelData       public string Id   get  set        public string Name   get  set        public string Gender   get  set        So that I can call out the contents using the heading name  For example  when I do this   foreach  var data in ThatList         Console WriteLine data Id   data Name   data Gender       It will give me this output   1JohnMale 2MariaFemale 3DanielUnknown     This is really all I got   var package   new ExcelPackage new FileInfo   C  ExcelFile xlsx     ExcelWorksheet sheet   package Workbook Worksheets 1    var table   sheet Tables First     table Columns Something   I guess I can use this to do what I want   Please help    I have spent long hours searching for sample code regarding this so that I can learn from it but to no avail  I also understand ExcelToLinQ is managed to do that but it can t recognize table

User · Answer

Not sure why but none of the above solution work for me  So sharing what worked   public void readXLS string FilePath        FileInfo existingFile   new FileInfo FilePath       using  ExcelPackage package   new ExcelPackage existingFile                   get the first worksheet in the workbook         ExcelWorksheet worksheet   package Workbook Worksheets 1           int colCount   worksheet Dimension End Column     get Column Count         int rowCount   worksheet Dimension End Row        get row count         for  int row   1  row  lt   rowCount  row                          for  int col   1  col  lt   colCount  col                                  Console WriteLine   Row     row     column     col     Value     worksheet Cells row  col  Value  ToString   Trim

User · Answer

This is my working version  Note that the resolvers code is not shown but are a spin on my implementation which allows columns to be resolved even though they are named slightly differently in each worksheet    public static IEnumerable lt T gt  ToArray lt T gt  this ExcelWorksheet worksheet  List lt PropertyNameResolver gt  resolvers  where T   new           List of all the column names   var header   worksheet Cells GroupBy cell   gt  cell Start Row  First          Get the properties from the type your are populating   var properties   typeof T  GetProperties   ToList        var start   worksheet Dimension Start    var end   worksheet Dimension End        Resulting list   var list   new List lt T gt           Iterate the rows starting at row 2  ie start Row   1    for  int row   start Row   1  row  lt   end Row  row            var instance   new T        for  int col   start Column  col  lt   end Column  col                object value   worksheet Cells row  col  Text            Get the column name zero based  ie col -1        var column    string header Skip col - 1  First   Value            Gets the corresponding property to set       var property   properties Property resolvers  column          try                 var propertyName   property PropertyType IsGenericType             property PropertyType GetGenericArguments   First   FullName             property PropertyType FullName               Implement setter code as needed           switch  propertyName                      case  System String               property SetValue instance  Convert ToString value                break            case  System Int32               property SetValue instance  Convert ToInt32 value                break            case  System DateTime               if  DateTime TryParse  string  value  out var date                               property SetValue instance  date                             property SetValue instance  FromExcelSerialDate Convert ToInt32 value                 break            case  System Boolean               property SetValue instance   int value    1               break                          catch  Exception e                     instance property is empty because there was a problem                      list Add instance         return list        Utility function taken from the above post s inline function  public static DateTime FromExcelSerialDate int excelDate      if  excelDate  lt  1      throw new ArgumentException  Excel dates cannot be smaller than 0        var dateOfReference   new DateTime 1900  1  1      if  excelDate  gt  60d      excelDate   excelDate - 2    else     excelDate   excelDate - 1    return dateOfReference AddDays excelDate

User · Answer

There is no native but what if you use what I put in this post   How to parse excel rows back to types using EPPlus  If you want to point it at a table only it will need to be modified   Something like this should do it   public static IEnumerable lt T gt  ConvertTableToObjects lt T gt  this ExcelTable table  where T   new           DateTime Conversion     var convertDateTime   new Func lt double  DateTime gt  excelDate   gt                if  excelDate  lt  1              throw new ArgumentException  Excel dates cannot be smaller than 0              var dateOfReference   new DateTime 1900  1  1            if  excelDate  gt  60d              excelDate   excelDate - 2          else             excelDate   excelDate - 1          return dateOfReference AddDays excelDate                  Get the properties of T     var tprops    new T             GetType            GetProperties            ToList           Get the cells based on the table address     var start   table Address Start      var end   table Address End      var cells   new List lt ExcelRangeBase gt            Have to use for loops insteadof worksheet Cells to protect against empties     for  var r   start Row  r  lt   end Row  r            for  var c   start Column  c  lt   end Column  c                cells Add table WorkSheet Cells r  c         var groups   cells          GroupBy cell   gt  cell Start Row           ToList           Assume the second row represents column data types  big assumption       var types   groups          Skip 1           First            Select rcell   gt  rcell Value GetType             ToList           Assume first row has the column names     var colnames   groups          First            Select  hcell  idx    gt  new   Name   hcell Value ToString    index   idx             Where o   gt  tprops Select p   gt  p Name  Contains o Name            ToList           Everything after the header is data     var rowvalues   groups          Skip 1    Exclude header          Select cg   gt  cg Select c   gt  c Value  ToList            Create the collection container     var collection   rowvalues          Select row   gt                        var tnew   new T                colnames ForEach colname   gt                                  This is the real wrinkle to using reflection - Excel stores all numbers as double including int                 var val   row colname index                   var type   types colname index                   var prop   tprops First p   gt  p Name    colname Name                      If it is numeric it is a double since that is how excel stores all numbers                 if  type    typeof double                                         if   string IsNullOrWhiteSpace val  ToString                                                     Unbox it                         var unboxedVal    double val                             FAR FROM A COMPLETE LIST                            if  prop PropertyType    typeof Int32                               prop SetValue tnew   int unboxedVal                           else if  prop PropertyType    typeof double                               prop SetValue tnew  unboxedVal                           else if  prop PropertyType    typeof DateTime                               prop SetValue tnew  convertDateTime unboxedVal                            else                             throw new NotImplementedException String Format  Type   0   not implemented yet    prop PropertyType Name                                                            else                                         Its a string                     prop SetValue tnew  val                                                  return tnew                      Send it back     return collection      Here is a test method    TestMethod  public void Table To Object Test           Create a test file     var fi   new FileInfo   c  temp Table To Object xlsx         using  var package   new ExcelPackage fi                 var workbook   package Workbook          var worksheet   workbook Worksheets First            var ThatList   worksheet Tables First   ConvertTableToObjects lt ExcelData gt             foreach  var data in ThatList                        Console WriteLine data Id   data Name   data Gender                      package Save              Gave this in the console   1JohnMale 2MariaFemale 3DanielUnknown   Just be careful if you Id field is an number or string in excel since the class is expecting a string

User · Answer

Below code will read excel data into a datatable  which is converted to list of datarows   if  FileUpload1 HasFile        if  Path GetExtension FileUpload1 FileName       xlsx                 Stream fs   FileUpload1 FileContent          ExcelPackage package   new ExcelPackage fs           DataTable dt   new DataTable            dt  package ToDataTable            List lt DataRow gt  listOfRows   new List lt DataRow gt             listOfRows   dt AsEnumerable   ToList               using OfficeOpenXml  using System Data  using System Linq    public static class ExcelPackageExtensions               public static DataTable ToDataTable this ExcelPackage package                        ExcelWorksheet workSheet   package Workbook Worksheets First                DataTable table   new DataTable                foreach  var firstRowCell in workSheet Cells 1  1  1  workSheet Dimension End Column                                 table Columns Add firstRowCell Text                              for  var rowNumber   2  rowNumber  lt   workSheet Dimension End Row  rowNumber                                  var row   workSheet Cells rowNumber  1  rowNumber  workSheet Dimension End Column                   var newRow   table NewRow                    foreach  var cell in row                                        newRow cell Start Column - 1    cell Text                                    table Rows Add newRow                             return table

User · Answer

Working solution with validate email mobile number  public class ExcelProcessing                       public List lt ExcelUserData gt  ReadExcel                                 string path   Config folderPath     quot  MemberUploadFormat xlsx quot                        using  var excelPack   new ExcelPackage                                            Load excel stream                     using  var stream   File OpenRead path                                                 excelPack Load stream                                                    Lets Deal with first worksheet  You may iterate here if dealing with multiple sheets                      var ws   excelPack Workbook Worksheets 0                            List lt ExcelUserData gt  userList   new List lt ExcelUserData gt                              int colCount   ws Dimension End Column     get Column Count                     int rowCount   ws Dimension End Row                                              for  int row   2  row  lt   rowCount  row       start from to 2 omit header                                                                       bool IsValid   true                          ExcelUserData  user   new ExcelUserData                                 for  int col   1  col  lt   colCount  col                                                          if  col    1                                                                 user FirstName   ws Cells row  col  Value  ToString   Trim                                    if  string IsNullOrEmpty  user FirstName                                                                          user ErrorMessage     quot Enter FirstName  lt br  gt  quot                                       IsValid   false                                                                                              else if  col    2                                                                 user Email   ws Cells row  col  Value  ToString   Trim                                         if  string IsNullOrEmpty  user Email                                                                          user ErrorMessage     quot Enter Email  lt br  gt  quot                                       IsValid   false                                                                    else if   IsValidEmail  user Email                                                                          user ErrorMessage     quot Invalid Email Address  lt br  gt  quot                                       IsValid   false                                                                                              else if  col   3                                                                 user MobileNo   ws Cells row  col  Value  ToString   Trim                                         if  string IsNullOrEmpty  user MobileNo                                                                          user ErrorMessage     quot Enter Mobile No  lt br  gt  quot                                       IsValid   false                                                                    else if   user MobileNo Length    10                                                                         user ErrorMessage     quot Invalid Mobile No  lt br  gt  quot                                       IsValid   false                                                                                                   else if  col    4                                                                 user IsAdmin   ws Cells row  col  Value  ToString   Trim                                         if  string IsNullOrEmpty  user IsAdmin                                                                          user IsAdmin    quot 0 quot                                                                                                     user IsValid   IsValid                                                    userList Add  user                                             return userList                                              public static bool IsValidEmail string email                                Regex regex   new Regex   quot    a-zA-Z0-9  -           0-9  1 3    0-9  1 3    0-9  1 3        a-zA-Z0-9 -          a-zA-Z  2 4   0-9  1 3         quot                    RegexOptions CultureInvariant   RegexOptions Singleline                        return regex IsMatch email

User · Answer

I have got an error on the first answer so I have changed some code line   Please try my new code  it s working for me   using OfficeOpenXml  using OfficeOpenXml Table  using System  using System Collections Generic  using System Linq  using System Reflection   public static class ImportExcelReader       public static List lt T gt  ImportExcelToList lt T gt  this ExcelWorksheet worksheet  where T   new                   DateTime Conversion         Func lt double  DateTime gt  convertDateTime   new Func lt double  DateTime gt  excelDate   gt                        if  excelDate  lt  1                                throw new ArgumentException  Excel dates cannot be smaller than 0                                DateTime dateOfReference   new DateTime 1900  1  1                if  excelDate  gt  60d                                excelDate   excelDate - 2                            else                               excelDate   excelDate - 1                             return dateOfReference AddDays excelDate                        ExcelTable table   null           if  worksheet Tables Any                          table   worksheet Tables FirstOrDefault                      else                       table   worksheet Tables Add worksheet Dimension   tbl    ShortGuid NewGuid   ToString                  ExcelAddressBase newaddy   new ExcelAddressBase table Address Start Row  table Address Start Column  table Address End Row   1  table Address End Column                  Edit the raw XML by searching for all references to the old address             table TableXml InnerXml   table TableXml InnerXml Replace table Address ToString    newaddy ToString                          Get the cells based on the table address         List lt IGrouping lt int  ExcelRangeBase gt  gt  groups   table WorkSheet Cells table Address Start Row  table Address Start Column  table Address End Row  table Address End Column               GroupBy cell   gt  cell Start Row               ToList               Assume the second row represents column data types  big assumption           List lt Type gt  types   groups Skip 1  FirstOrDefault   Select rcell   gt  rcell Value GetType    ToList               Get the properties of T         List lt PropertyInfo gt  modelProperties   new T   GetType   GetProperties   ToList               Assume first row has the column names         var colnames   groups FirstOrDefault                Select  hcell  idx    gt  new                               Name   hcell Value ToString                    index   idx                             Where o   gt  modelProperties Select p   gt  p Name  Contains o Name                ToList               Everything after the header is data         List lt List lt object gt  gt  rowvalues   groups              Skip 1    Exclude header              Select cg   gt  cg Select c   gt  c Value  ToList    ToList               Create the collection container         List lt T gt  collection   new List lt T gt             foreach  List lt object gt  row in rowvalues                        T tnew   new T                foreach  var colname in colnames                                  This is the real wrinkle to using reflection - Excel stores all numbers as double including int                 object val   row colname index                   Type type   types colname index                   PropertyInfo prop   modelProperties FirstOrDefault p   gt  p Name    colname Name                      If it is numeric it is a double since that is how excel stores all numbers                 if  type    typeof double                                           Unbox it                     double unboxedVal    double val                         FAR FROM A COMPLETE LIST                        if  prop PropertyType    typeof int                                                 prop SetValue tnew   int unboxedVal                                             else if  prop PropertyType    typeof double                                                 prop SetValue tnew  unboxedVal                                             else if  prop PropertyType    typeof DateTime                                                 prop SetValue tnew  convertDateTime unboxedVal                                              else if  prop PropertyType    typeof string                                                 prop SetValue tnew  val ToString                                               else                                               throw new NotImplementedException string Format  Type   0   not implemented yet    prop PropertyType Name                                                            else                                         Its a string                     prop SetValue tnew  val                                               collection Add tnew                      return collection            How to call this function  please view below code   private List lt FundraiserStudentListModel gt  GetStudentsFromExcel HttpPostedFileBase file                List lt FundraiserStudentListModel gt  list   new List lt FundraiserStudentListModel gt             if  file    null                        try                               using  ExcelPackage package   new ExcelPackage file InputStream                                         ExcelWorkbook workbook   package Workbook                      if  workbook    null                                                ExcelWorksheet worksheet   workbook Worksheets FirstOrDefault                            if  worksheet    null                                                        list   worksheet ImportExcelToList lt FundraiserStudentListModel gt                                                                                                 catch  Exception err                                  save error log                                 return list          FundraiserStudentListModel here    public class FundraiserStudentListModel       public string Name   get  set        public string Email   get  set        public string Phone   get  set

[c#] EPPlus - Read Excel Table

Examples related to c#

Examples related to excel

Examples related to c#-4.0

Examples related to epplus

Examples related to epplus-4