New to Excel Programming in C#
-
This is a general question which I am looking to the experts for advice. My need is to be able to read/write, primarily read from Excel files. I am comfortable using SQL, ODBC, DataSets, etc. Looking at the C# articles it seems you have two choices: 1) For direct access, use the Excel Object Library - (which incidentally, which DLL is this anyway?) 2) For SQL like access you can use Ole DB I have started playing with the Ole DB solution. Decent way of getting it done, reasonably fast, but I wonder about the long term viability of using this approach? I would like to be able to simply define a custom DataSet that matches the rows/columns in the spreadsheet. Then using an Adapter.Fill() command to load. I am still experimenting. So I am thinking about going the direct access via the Excel object library but I cannot determine what exe or dll to load and then refernce. I have scanned the MS site to no avail. Any advice here is definitely appreciated. Thanks a bunch! Mike Luster CTI/IVR/Telephony SME
-
This is a general question which I am looking to the experts for advice. My need is to be able to read/write, primarily read from Excel files. I am comfortable using SQL, ODBC, DataSets, etc. Looking at the C# articles it seems you have two choices: 1) For direct access, use the Excel Object Library - (which incidentally, which DLL is this anyway?) 2) For SQL like access you can use Ole DB I have started playing with the Ole DB solution. Decent way of getting it done, reasonably fast, but I wonder about the long term viability of using this approach? I would like to be able to simply define a custom DataSet that matches the rows/columns in the spreadsheet. Then using an Adapter.Fill() command to load. I am still experimenting. So I am thinking about going the direct access via the Excel object library but I cannot determine what exe or dll to load and then refernce. I have scanned the MS site to no avail. Any advice here is definitely appreciated. Thanks a bunch! Mike Luster CTI/IVR/Telephony SME
You really don't need to know what the dll name is to reference the Excel object. Just right mouse on references in the Solution Explorer and click on Add Reference. Click on the COM tab and choose the Microsoft Excell 11.0 Object Library and click on select. Add the following to your form's constructor:
ExcelObj = new Excel.Application(); // See if the Excel Application Object was successfully constructed if (ExcelObj == null) { MessageBox.Show("ERROR: EXCEL couldn't be started!"); System.Windows.Forms.Application.Exit(); } // Make the Application Visible ExcelObj.Visible = false;Add, ExcelObj.Quit();, to the form's dispose method so Excel will shutdown when you application shut's down. If you don't do this then you flood your system Excel's in your task manager. Here is the code you need to open an Excel workbook and display it's worksheet.private void DispSheets(string FileName) { // *********** Here is the call to Open a Workbook in Excel **************** // It uses most of the default values (except for the read-only which we set to true) theWorkbook = ExcelObj.Workbooks.Open( FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 0, 0); foreach (Excel.Worksheet WrkSht in theWorkbook.Worksheets) { this.lstWrkSheets.Items.Add(WrkSht.Name); } }You can use the next two methods to read data from an excel spreadsheet iinto a list box.private void btnShowData_Click(object sender, System.EventArgs e) { int iFirst = 0; int iLines = 0; this.lstXLSData.Items.Clear(); //this.lstSelectedColumns.Items.Clear(); // get the collection of sheets in the workbook Excel.Sheets sheets = theWorkbook.Worksheets; // get the first and only worksheet from the collection of worksheets Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(this.lstWrkSheets.SelectedIndex+1); // find starting row iFirst = RowStart(); // find row total //iLines = RowTotal(); iLines = 25; // loop through user specified rows of the spreadsheet and place each row in the list view for (int i = iFirst; i <= iLines; i++) { //this.sbpInfo.Text = "Importing row " + i + " of " + iLines; Excel.Range range = worksheet.get_Range(this.cmbStartCol.Text + i.ToString(), this.cmbEndCol.Text + i.ToString()); System.Array myvalues = (System.Array)range.Cells.Value2; -
You really don't need to know what the dll name is to reference the Excel object. Just right mouse on references in the Solution Explorer and click on Add Reference. Click on the COM tab and choose the Microsoft Excell 11.0 Object Library and click on select. Add the following to your form's constructor:
ExcelObj = new Excel.Application(); // See if the Excel Application Object was successfully constructed if (ExcelObj == null) { MessageBox.Show("ERROR: EXCEL couldn't be started!"); System.Windows.Forms.Application.Exit(); } // Make the Application Visible ExcelObj.Visible = false;Add, ExcelObj.Quit();, to the form's dispose method so Excel will shutdown when you application shut's down. If you don't do this then you flood your system Excel's in your task manager. Here is the code you need to open an Excel workbook and display it's worksheet.private void DispSheets(string FileName) { // *********** Here is the call to Open a Workbook in Excel **************** // It uses most of the default values (except for the read-only which we set to true) theWorkbook = ExcelObj.Workbooks.Open( FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 0, 0); foreach (Excel.Worksheet WrkSht in theWorkbook.Worksheets) { this.lstWrkSheets.Items.Add(WrkSht.Name); } }You can use the next two methods to read data from an excel spreadsheet iinto a list box.private void btnShowData_Click(object sender, System.EventArgs e) { int iFirst = 0; int iLines = 0; this.lstXLSData.Items.Clear(); //this.lstSelectedColumns.Items.Clear(); // get the collection of sheets in the workbook Excel.Sheets sheets = theWorkbook.Worksheets; // get the first and only worksheet from the collection of worksheets Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(this.lstWrkSheets.SelectedIndex+1); // find starting row iFirst = RowStart(); // find row total //iLines = RowTotal(); iLines = 25; // loop through user specified rows of the spreadsheet and place each row in the list view for (int i = iFirst; i <= iLines; i++) { //this.sbpInfo.Text = "Importing row " + i + " of " + iLines; Excel.Range range = worksheet.get_Range(this.cmbStartCol.Text + i.ToString(), this.cmbEndCol.Text + i.ToString()); System.Array myvalues = (System.Array)range.Cells.Value2;Thanks for the example code. Using the Ole DB interface is alot more abstract so I like the idea of using the Excel object model. However when I try to add the reference I do not have the Microsoft Excel 11.0 Object Lbrary. That is why I was asking about the DLL. Do I need to download a SDK to the object library? Mike
-
Thanks for the example code. Using the Ole DB interface is alot more abstract so I like the idea of using the Excel object model. However when I try to add the reference I do not have the Microsoft Excel 11.0 Object Lbrary. That is why I was asking about the DLL. Do I need to download a SDK to the object library? Mike
What version of MS Office do you have? This code was built using MS Office 2003. The file being reference is the actual Excel.exe of your installed Office product. I reject your reality and substitute my own! - Adam Savage, Mythbuster - George W Bush life is like a roll of toilet paper. The closer it gets to the end, the faster it goes. My definition of an expert in any field is a person who knows enough about what's really going on to be scared. - PJ Plauger
-
What version of MS Office do you have? This code was built using MS Office 2003. The file being reference is the actual Excel.exe of your installed Office product. I reject your reality and substitute my own! - Adam Savage, Mythbuster - George W Bush life is like a roll of toilet paper. The closer it gets to the end, the faster it goes. My definition of an expert in any field is a person who knows enough about what's really going on to be scared. - PJ Plauger
Slight progress, I have Office Xp aka Office 2002 (10.6713.6735) SP3 for Excel. SO.. I found the Excel 10.0 Object Library under COM components. And when I try to "new" the excel object, i.e. a Excel.Application, it only has Excel.ApplicationClass. Not exactly what I need, so I am wondering if I have to upgrade to the later version of Office (2003)? Mike
-
Slight progress, I have Office Xp aka Office 2002 (10.6713.6735) SP3 for Excel. SO.. I found the Excel 10.0 Object Library under COM components. And when I try to "new" the excel object, i.e. a Excel.Application, it only has Excel.ApplicationClass. Not exactly what I need, so I am wondering if I have to upgrade to the later version of Office (2003)? Mike
You want to look in the object browser. The application is actually an inerface for the Excel object. Unfortunately I don't have a system with OfficeXP on it. With was little, and I mean little, I read on this subject it should work the same. I have users that OfficeXP installed and are able to use my application that I wrote referencing Office 2003. I reject your reality and substitute my own! - Adam Savage, Mythbuster -George W Bush life is like a roll of toilet paper. The closer it gets to the end, the faster it goes. My definition of an expert in any field is a person who knows enough about what's really going on to be scared. - PJ Plauger
-
You want to look in the object browser. The application is actually an inerface for the Excel object. Unfortunately I don't have a system with OfficeXP on it. With was little, and I mean little, I read on this subject it should work the same. I have users that OfficeXP installed and are able to use my application that I wrote referencing Office 2003. I reject your reality and substitute my own! - Adam Savage, Mythbuster -George W Bush life is like a roll of toilet paper. The closer it gets to the end, the faster it goes. My definition of an expert in any field is a person who knows enough about what's really going on to be scared. - PJ Plauger
To use the latest version of all this stuff, you need to have .Net 2005 and Office 2003. Anything less can cause issues.