Excel import and associate columns to class value

Question

4.00/5 (1 vote)

See more:

Okay,

i'll try to explain myself clear with the problem i'm having right now, i'm sure it has a very simple solution i'm just not seeing it right now, (could be the time of day or lack of coffee, anyways)

I am writing a little winforms program for our product managers in our company so that they can import any given excel file with data about products in each column (like name, description, material etc..) and i want my program to read the colum names, which i already have see snipet:

C#

           private Excel.Application appExl;
           private Excel.Workbook workbook;
           private Excel.Worksheet NwSheet;
           private Excel.Range ShtRange;
           private Webproduct Articulo;
           private List<Webproduct> Articulos;
           private Excel.ApplicationClass appXl;

if (ofdExcel.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
        string filename = ofdExcel.FileName;
        workbook = appXl.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
        int Cnum = 0;
        int Rnum = 0;
        ShtRange = NwSheet.UsedRange;
        Hashtable htColumns = new Hashtable();
        for (int i = 1; i <= ShtRange.Columns.Count; i++)
        {
           htColumns.Add(i, ((Range)ShtRange.Cells[1, i]).Value2);
        }
 }

Okay so i thought i have a webproduct class which has all the field requiered by our product database, some of them like (id, language, Reference,etc..) are mandatory and some others optional(dimensions, material, whatsoever)
what i want to do is that once the column names are in the Hashtable (don't know if that is the best way to do it) how can i associate or what would be the best way to associate the excel column names with the webproduct columns,

Example i know my webproduct has the variable _ProductName but the excel file colum could be "ProductName" or "Nombre" or "Whatever"

What kind of solution would you do? i'm not asking for the complete code just a hint to the right direction or maybe a snippet to make this as dynamic as possible knowing that the are about 86 columns/variables

any ideas?

Posted 25-Oct-12 4:17am

Mendor81

Add a Solution

Comments

[no name] 25-Oct-12 14:06pm

you can use reflection to get all the properties of webproduct and then map it with the column names in hashtable.

Sergey Alexandrovich Kryukov 25-Oct-12 16:28pm

That would be a huge performance cost, unless you use System.Reflection.Emit to emit the code reusing reflection results on the IL level, which is considerably more difficult to implement and especially debug, needs good qualification.
--SA

[no name] 26-Oct-12 14:13pm

totally agree with it.

John d. Bartels 25-Oct-12 21:07pm

XML file to house the Column names, that is loaded at runtime. This allows you to easily alter the XML file to include future columns. You could also store this information in the database. You could also store it statically in the code. The first and second options are preferred because they do not require code change.

public class webProductInfo
{
public string ProductName {get; set;}
public int ProductID {get; set;}
public List<string> Columns {get; set;}
}

Then you can create a List<webproductinfo> prodList = new List<webproductinfo>(); and fill it up with the information from the xml file or the database, etc.

Mendor81 26-Oct-12 5:04am

Thanks i think i'll try the xml option, mapping the names in the excel saving them in xml and in the winform the user can map these columns later via combobox. since there is a number of minimum mandatory variables i'll just let the user choose which he wants to import later.

thx

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Excel import and associate columns to class value

Add your solution here

Preview 0

Excel import and associate columns to class value

Add your solution here

Preview 0

Existing Members

...or Join us