Link to home
Start Free TrialLog in
Avatar of suresh pondicherry
suresh pondicherryFlag for United States of America

asked on

Excel sheet to C# objects

Hi,
Advance thanks. I need great help in building business objects from spread sheet. All the fields in spread sheets should be the object properties and values are stored in objects. Any code or links will be helpful since i am very new to WPF.


Kind regards,
Pooja

Avatar of nishant joshi
nishant joshi
Flag of India image

Hi,
means you want to store a excel into one object..??


What do you do with the objects? I would like to see if code in Powershell would work for your solution!
Notice that Excel interop code is independent of UI technology (WPF, win forms), you just need to be careful to always run this code on the main (UI) thread, check:
http://support.microsoft.com/kb/302096/en-us
The Excel 2007/2010 object model is still similar to 2003.

Note: Check this article if you want to build an add-in: (it is still vS2008, but 2010 is basically the same without the Nothing optional parameter)
http://www.eggheadcafe.com/tutorials/excel/ff2d1d4b-aedf-4d14-9e60-39a86ccab5d6/using-vsto-addin-to-automate-frequent-excel-2007-tasks.aspx
Avatar of suresh pondicherry

ASKER

Hi Nishantcomp2512 and Marahman3001,
Yes, i need to store excel sheet into one C# object , columns should go as properties. I am using WPF and VS2010.

Kind regards,
Pooja
Hi Mas ox2003 , Nishantcomp2512 and Marahman3001,
The input file is csv file.


Kind regards,
Pooja
Hi,
Please find the sample csv file.  
 
Field Name,Field Type,Policy,Field Remote Group,Cusr Name,Description,Element,Cusr Time,Cusr State,Cusr Value                                      => are  C# fields.

Kind regards,
Pooja

test.csv
Hi,
pooja

you can use microsoft excel in .net

check code..
oXL = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook wp = new Microsoft.Office.Interop.Excel.Workbook();
                oXL.Workbooks.Open("");

Open in new window

Hi Nishantcomp2512,
The input is .csv file. Also i could see commas inside each double quoted strings.
I am using WPf and C#. Please let me know if you need any more information. Did you see the sample csv file.

Kind regards,
Pooja
If your file is a csv file, there is no need to use Excel.
My algorithm will be as follows:
1.- read csv file to a string array (http://www.dotnetperls.com/file-readalllines)
2.- For every line(string)
     2.1 Create customer = new CustomerObj();
     2.2 Parse line to fill customer object.
     2.3 Store customer in a list/dictionary for future usage.

sample code:
               string[] lines = File.ReadAllLines("C:\\test.csv");
               List<CustomerObj> custs = new List<CustomerObj>();
      foreach (string line in lines)
      {
          customer = new CustomerObj();
          customer.Parse(line);
          custs.Add(customer);
      }

where CustomerObj is your business object and parse is a method that split the line into an array and assign this array to your object properties
Hi Mas oz2003,
You are right. I did this similar way . What i am facing is with regular expressions. I am using regex for parsing the each line from file. This is my regular expression.
 Regex csvSplit = new Regex("(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)", RegexOptions.Compiled);

I have attached the csv file earlier.

Kind regards,
Pooja
At parse method: I was thinking of using line.Split(',') just like the text file section of http://www.dotnetperls.com/split

e.g. Let FieldName, FieldType are CustomerObj properties
then parse method in CustomerObj will look llike:
void Parse(string line)
{
 string[] tokens = line.Split(',');
 //do a check if token is ok , e.g check length
 this.Field Name = token[0];
 this.FieldType = token[1];
//and so on.
}
Hi Mas oz2003,
But some double quoted string has got comma inside it. So i feel regex will do better here. If i use split using comma delimiter, i could not get the right property value in place.

Kind regards,
Pooja
Sorry, I forgot you have  commas inside the field.
You can use regex, but I prefer to use libraries like:
http://www.codeproject.com/KB/database/CsvReader.aspx

note: Some people suggest using TextFieldParser class (http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser.aspx)


ASKER CERTIFIED SOLUTION
Avatar of suresh pondicherry
suresh pondicherry
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Mas 0z2003,
After some testing i prefer to use this csvreader since it solves my issue completely. Thanks.

Kind regards,
Pooja
Thanks