Hi experts,
I'm supposed to write a program to get a list of data from Excel to Visual Studio 2006. So far, I've only managed to make Visual Studio open the excel file i want, but I'm still thinking of ways to capture the data and use it in my C# program. So far, it's still fruitless.
Wonder if anyone can provide me with precious guidance? Your help will be greatly appreciated.
Hi,
If the data in the excel file is in the form of a table (similar to a database table), then you can directly connect to the excel file using OLEDB and fetch the data by writing sql query. But if the data in the excel is in some other form, then you have to read them using excel APIs. Please provide further details about the excel file and what you want to achieve.
Regards,
Asim.
Hi,
An easy way is to use Excel to save the file as a text file & then simply read the text file with c#.
regards,
joe
Hi,
Thanks for the reply. My Excel file should be in the form of a table, with columns of datas I have to read using Visual Studio C#. Do you consider this as a table?
Secondly, what is OLEDB and sql query? I'm not very sure about all these, therefore I do not understand what is it all about.
Would it be possible if you elaborate on it?
With Regards,
Guan Zhao
Hi Guanzhao,
Basically what you can do is treat an Excel sheet like a database table, which is quite handy as you can run a database query against it to extract out the information. The sheet need to look like a database table basically, with column names and rows of data. Like this as a very very basic example.....
ID Name
1 Jim
2 Bob
What you can do is connect to the sheet, run an SQL query (the language used to query a database) and extract the information out of Excel into .NET, without needing to open Excel. I have a code example but unfortunately it is in VB, but I'm sure with a bit of study you will see what is going on....
This opens an Excel spreadsheet called C:\test.xls and imports all the information in Sheet1$, it places the results into a DataSet object (one table) which is a bit overkill as a single DataTable would have done. I understand this is all new to you but have a read on the code and see if you can get it to work, perhaps converting it to C# might be one way to understand it.
Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
Dim ds As New System.Data.DataSet()
cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=C:\test.xls;Extended Properties=Excel 8.0;")
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)
cn.Open()
cmd.Fill(ds)
cn.Close()
For Each row As DataRow In ds.Tables(0).Rows
For Each col As DataColumn In ds.Tables(0).Columns
Console.Write("{0} ", row(col.Ordinal))
Next
Console.WriteLine()
Next
Console.ReadLine()
Hi guanzhao,
You can use either office automation or ADO.NET to get the data.
For ADO.NET part, create a OLE connection:
OleDbConnection con = new OleDbConnection(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\book1.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
For more information, see my another post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1656260&SiteID=1
For office automation part, check: How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET
http://support.microsoft.com/kb/302902
Thanks
Hi Derek,
Thanks for the generous help. But I still do not get the part about SQL Query as I'm really an elementary programmer.
However I have some questions as well. After data extraction, how do you use the data? If the data is extracted, would it be in 'string'? or is it in array form like how a column is?
Next, is OleDb the only way to solve this problem? I've been having quite a lot of trouble understanding things about SQL Query and OleDb as I totally do not understand anything about it.
Warmest Regards
Guan Zhao
Hi Figo
Thanks for your generous help too. However, what I need to do is to read the Excel file and use the columns of data INSIDE Visual 2005. And up till now, I still can't figure out and clue on how can i accomplish the task.
I only managed to open the Excel file, and so far I'm heading the direction of using something such as get_Range, get_Item. However, what I'm doing is not working, and even through many trial and error instances, I still can't figure it out how to capture the data.
Can you provide me comments if what I'm doing is right? Or am i just walking around in circles again going nowhere?
Warmest Regards
Guan Zhao
Hi Guan Zhao,
Here is the sample code(ADO.NET):
bool ReadFiles() { try { DataSet ds = new DataSet(); OleDbConnection con = new OleDbConnection(); OleDbDataAdapter da; string FilePath = … con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; /*con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file.FullName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; *"HDR=Yes;" indicates that the first row contains columnnames, not data. * "HDR=No;" indicates the opposite. * "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. * Note that this option might affect excel sheet write access negative. */ /*Excel 2007 connection string as follows */ da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con); //read Sheet1 da.Fill(ds, FilePath); } catch { return false; } return true; } Code Snippet
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ FilePath + ";Extended Properties='Excel 12.0;HDR=YES';";
Here is complete code of excel automation:
Microsoft.Office.Interop.Excel.Application app; Microsoft.Office.Interop.Excel.Workbook wkBk; Microsoft.Office.Interop.Excel.Worksheet wkSht; object oMissing = Missing.Value; app = new Microsoft.Office.Interop.Excel.ApplicationClass(); app.DisplayAlerts = false; wkBk = app.Workbooks.Open(FilePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); wkSht = (Microsoft.Office.Interop.Excel.Worksheet)wkBk.Sheets.get_Item(1); //wkSht.Cells[1, 1]= ... We can use this to assign value but not read value Microsoft.Office.Interop.Excel.Range result = (Microsoft.Office.Interop.Excel.Range)wkSht.Cells[1, 1];//Read value of cell row 1 col 1 app.Visible = true; //House keeping work for releasing COM references. if (wkSht != null) Marshal.ReleaseComObject(wkSht); if (wkBk != null) Marshal.ReleaseComObject(wkBk); if (app != null) Marshal.ReleaseComObject(app); wkSht = null; wkBk = null; app = null; GC.Collect(); Label1.Text = result.Text.ToString(); Code Snippet
About excel automation's other operation, see: How to automate Microsoft Excel from Microsoft Visual C# .NET
and Microsoft.Office.Tools.Excel Namespace
Thanks
Hi Guan,
You know I think your best approach is to continue using the OLE Automation way. Using ADO.NET to query Excel could be considered an advanced approach. It's not difficult really to do, but it involves a number of technologies and database concepts, and it becomes very difficult to do without this knowledge. The OLE automation approach would be adequate and I believe you have this working to a degree so rather than start again continue on with what your doing.
Hi Derek,
Thanks for your wonderful help. I really appreciated it, even though I am still stuck with little progress. Nice knowing you.
Warmest Regards
Guan Zhao
Hi Figo,
Your code snippets was a great help to me, but I don't think I can understand ADO.NET very well as it's a much much higher level of programming for me. The Excel automation code however, is easier to understand. I've tried the code, but there seems to be some problems. Here's what I encountered:
1) I assumed that 'string result'' captures the value of cell row 1 col1 right? so assuming I have a float value inside the cell, is this string going to capture the float result?
2) When I used a label to display the string ( label1.Text = result; ), the result showed inside the label is 'System._ComObject' instead of the float value that is inside the cell.
Can you give me your comments on why this happens?
Warmest Regards
Guan Zhao
Hi Guan zhao,
I have modified my previous answer with the highlight part you can see how to get the result of a specific cell.
When you want to read other type out of the cell, simply do some type casting from string to the type.
For example, use double.TryParse to convert a string into a double-precision floating-point number:
double d; if (double.TryParse(result.Text.ToString(), out d)) { Label1.Text = d.ToString(); } else { Label1.Text = "error casting"; } Code Snippet
Thanks
Hi Figo,
All thanks to you, I am finally able capture the data from the cell! I'm truly grateful for your help. However this time, I need your advice again as the problem doesn't end there.
Assuming the Excel file data is all inside a column, and there are 20,000 over rows of data to be collected, how am I supposed to collect it?
I've tried ways like:
label1.Text = "";
for (int index = 1; index <= totalRow; index++) //Assuming totalRow is in 20,000 and above range.
{
Microsoft.Office.Interop.Excel.Range result = (Microsoft.Office.Interop.Excel.Range)wkSht.Cells[i, 1];
//Row keeps changing with index, but column stays the same.
label1.Text = label1.Text + result.Text.ToString(); //To continously update label1 with 'result'
}
I am able to build the program. But the form itself won't load after Excel application starts when normally, the form loads automatically after Excel application starts. And everytime I click on the cells, an error message comes out and I have to end the program.
Can you advice me on how I can overcome this problem? How am I supposed to capture all 20,000 over data and use the data one by one to plot (sorry to say which I will also post a problem I face here after I solved the capturing part)?
Hope you can advise me on this.
Warmest Regards
Guan Zhao
