none
Column mapping while importing Excel to sql database table RRS feed

  • Question

  • Hi everyone,

    I need a great help from here as I need to select only the mapped columns of a Excel file. Here I found a useful library LinqToExcel. This is what I am implementing in my code. see the sample below.

    I would like to discuss with you about my issue in this discussion. I am clear about this LINQ to excel the great deal from google code. well i need to know how to select only the mapped columns from the excel. Here I will explain you more.

    First_Name L_Name role Age
    A1 A2 R1 12
    B1 B2 R2 13
    D1 C2 R3 14

    The above table is sample User of excel file. The class model is as follows

        public class Users 
        { 
    
            public string FirstName { get; set; } 
            public string LastName { get; set; }
            public string Role { get; set; } 
            public int Age { get; set; } 
        }
    

    In this I have added a mapping technique with a generic model as I am in need to develop this excel import for multiple models. Kindly go through this piece of code.

        public class ExcelDocumentReader { private ExcelQueryFactory Excel { get; set; } 
            
            public string FileName { get; set; } 
            public string SheetName { get; set; } 
            
            [Description("Initializes the ExcelDocumentReader with the FileName.")] 
            public ExcelDocumentReader(string FileName) 
            { 
                try 
                { 
                    if ((FileName != null) && (FileName != "")) 
                    { 
                        this.FileName = System.IO.Path.Combine(System.IO.Directory.GetCurrentDirectory(), @FileName); 
                        this.Excel = new ExcelQueryFactory(this.FileName); 
                    } else 
                        throw new Exception("FileName is Null or Empty"); 
                } catch (Exception ex) 
                { 
                    throw ex; 
                } 
                finally 
                { } 
            } 
            
            [Description("Gets all the Worksheet names.")]
            public IList<string> GetWorkSheets() 
            { 
                List<string> lstWorkSheets = new List<string>(); 
                
                try 
                { 
                    lstWorkSheets = this.Excel.GetWorksheetNames().ToList();
                } catch (Exception ex) 
                { 
                    throw ex; 
                } 
                finally 
                { } 
                
                return lstWorkSheets; 
            } 
            
            [Description("Gets all the column names in the SheetName provided.")] 
            public IList<string> GetColumnNames(string SheetName) 
            { 
                List<string> lstColumns = new List<string>(); 
                try 
                { 
                    lstColumns = this.Excel.GetColumnNames(SheetName).ToList(); 
                } catch (Exception ex) 
                { 
                    throw ex; 
                } 
                finally 
                { } 
                
                return lstColumns;
            } 
            
            [Description("Maps the excel column name with the entity field name for the Entity <T>.")] 
            public IEnumerable<T> MapColumn<T>(string TableColumn, string ExcelColumn) 
            {
                List<T> excelRows = new List<T>(); 
                
                try
                { 
                    this.Excel.AddMapping(TableColumn, ExcelColumn); 
                } catch (Exception ex) 
                { 
                    throw ex; 
                } 
                finally 
                { } 
                
                return excelRows; 
            } 
            
            
            [Description("Read all the records for the entity <T> with the SheetName.")] 
            public IEnumerable<T> ReadAll<T>(string SheetName) 
            { 
                List<T> excelRows = new List<T>(); 
                
                try 
                { 
                    this.SheetName = SheetName;
                    var rows = from sheet in this.Excel.Worksheet<T>(this.SheetName) select sheet; excelRows = rows.ToList(); 
                } catch (Exception ex) 
                { 
                    throw ex;
                } 
                finally
                { } 
                
                return excelRows; 
            }
        }
    

    See the MapColumn<T> function. In this any model can be passed for mapping with the parametres TableColumns(i.e) entity fields and the Excel column headers. The deal here is I wish to select only the mapped columns alone.  for example

    I am calling with the following piece of code.

    MapColumn<T>(new string[] { "FirstName", "Role" }, new string[] { "role", "L_Name" });

    I just mapped the FirstName and of entity class properties with the role and L_Name of excel file. I need to select only these mapped columns passed in to a list. Here I am stuck with this.

    from sheet in this.Excel.Worksheet<T>(this.SheetName).Select( x => new { x.FirstName, x.Role });

    But this is a static approach. I don't know what are the columns passed for mapping into the function and also I am using it as a Generic one NOTE: MapColumns<T>(.....)

    Kindly help me. Any help would be greatly appreciated.


    )-(aree


    Tuesday, July 10, 2012 5:19 AM

All replies

  • Hi Hari,

    Just for kindly reminder, please try to format the code snippet which you provided then it would let people easy to read and understand your issue.

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, July 12, 2012 11:24 AM
    Moderator