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

poojasureshkumarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nishant joshiTechnology Development ConsultantCommented:
Hi,
means you want to store a excel into one object..??


0
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
What do you do with the objects? I would like to see if code in Powershell would work for your solution!
0
Miguel OzSoftware EngineerCommented:
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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

poojasureshkumarAuthor Commented:
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
0
poojasureshkumarAuthor Commented:
Hi Mas ox2003 , Nishantcomp2512 and Marahman3001,
The input file is csv file.


Kind regards,
Pooja
0
poojasureshkumarAuthor Commented:
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
0
nishant joshiTechnology Development ConsultantCommented:
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

0
poojasureshkumarAuthor Commented:
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
0
Miguel OzSoftware EngineerCommented:
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
0
poojasureshkumarAuthor Commented:
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
0
Miguel OzSoftware EngineerCommented:
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.
}
0
poojasureshkumarAuthor Commented:
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
0
Miguel OzSoftware EngineerCommented:
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)


0
poojasureshkumarAuthor Commented:
Hi Mas oz2003,
I don't want to use third party LumenWorks.Framework.IO.Csv. Also i tried textfieldparser and it is not working good since my csv file is not well formatted. Thanks for the response.

Kind regards,
Pooja
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
poojasureshkumarAuthor Commented:
Hi Mas 0z2003,
After some testing i prefer to use this csvreader since it solves my issue completely. Thanks.

Kind regards,
Pooja
0
poojasureshkumarAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.