0

i need help to create a program that

  1. will create an excel file with just the press of a button and it will also fill some cells
  2. then another button will open an openfile dialogue to choose another excel file from which the program must read some values, calculate
  3. return the results in the program or/and export them in a new excel

any ideas are welcome, please if anyone knows to tell me at least where to start from.
is C# and visual studio 2010 a good choice for a project like this or should i use another language?
if i decide to go on with c# where can i find a list of commands to manipulate excel from my program?

Thanks in advance for your time!!!

naveen
  • 53,448
  • 46
  • 161
  • 251
Christos Karapapas
  • 1,018
  • 3
  • 19
  • 40
  • Why do not use csv file format it's comma or `;` separated and can be opened by excel and it has excel files functionality. it's very simple. – Saeed Amiri Jul 10 '11 at 08:00
  • Have a look into this thread http://stackoverflow.com/questions/444522/import-and-export-excel-what-is-the-best-library – Muhammad Akhtar Jul 10 '11 at 08:09
  • @Saeed csv wouldn't work for me unfortunately because the program is supposed to be used by non-experienced users, and also because once the first excel is created the users also will have to fill some cells @Muhammad thanks for the recommendation but the thread doesn't conclude in some solid solution and i have tried most of the libraries that suggests but most of them doesn't provide even a nice clean and organised table of the commands that one could use with those libraries... there are countless threads on that case, why there isn't a "final" or "generally accepted" solution ? – Christos Karapapas Jul 10 '11 at 08:40
  • @chris deneb: Why not just use Excel-VBA? This would all be quite straightforward. – Jean-François Corbett Jul 10 '11 at 10:05
  • @ Jean Francois Thanks for your answer i know that there are numerous ways much better and more efficient to complete that project but it's for my degree so unfortunately i can't use any other way... there must be a program from which the user would import an excel and the program would calculate ratios from data in excel. also the program should have the ability to create those excels – Christos Karapapas Jul 10 '11 at 11:42

1 Answers1

2

I learnt a clever trick for this a few years back ... i would guess that it still works today ...

using ole connections you can simply "connect" to an excel file as if it were a database (you can do this with csv files too) ...

first thing to do is connect to both your excel files:

using(OleDbCommand com = new OleDbCommand ("select * from sheetName", new OleDbConnection(excelFilePath)))
{
   com.Connection.Open();
   IDataReader reader = com.ExecuteReader();
   // do stuff with the sheet data
   com.Connection.Close();
}

if you wrap that up in a method or something you can have the select from sheet 1 and another method with an insert or update for sheet 2 (in other file).

Can't remember where i found this ... neat though right :)

... so bottom line ...

treat your excel files like a database :)

War
  • 8,539
  • 4
  • 46
  • 98
  • Thank you so much! it would be great if i make this to work seams very nice solution! but so far i have two errors 1. "The type or namespace name 'Olecommand' could not be found (are you missing a using directive or an assembly reference?) 2. the same for Oleconnection i tried by adding Using System.Data.Oledb; didn't work then i tried by adding some references but still nothing... what should i do ? – Christos Karapapas Jul 11 '11 at 17:54
  • I have found it!!! it worked by adding "Using System.Data.Oledb" and no extra reference, the only difference is that i wrote "OleCommand" as "OleDbCommand" and "OleConnection" as "OleDbConnection" Thanks again! – Christos Karapapas Jul 12 '11 at 04:30
  • my bad ... typo (i did do this from head ram) lol ... glad i could help :) ... i've updated the sample to reflect your changes :) – War Jul 12 '11 at 08:36
  • and what would be the code for reading a specific cell ? you know in the "do stuff with the sheet data" area ;) I need to open an .xls with an openfile dialogue then automatically read some specific cells and copy their values in a datagridview in the main form of my application – Christos Karapapas Jul 12 '11 at 17:10
  • what i try to ask is that the OleDbConnextion will import the entire excel to my datagrid... and what i need is to make a few calculations and after that import only the results in a datagrid... is this possible ? – Christos Karapapas Jul 12 '11 at 20:39
  • I would select everything from the sheet and load it straight in to a datatable, from there you can access the cells using something like myTable[row][column] ... when you're done simply re run the above code but instead of a select statement run an update statement (assuming rows have unique ids) if they dont simply flatten the data and load in the copy you have in your datatable. – War Jul 13 '11 at 10:12