.NET
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to read data from external excel

6 REPLIES 6
SOLVED
Reply
Message 1 of 7
ilovejingle
2982 Views, 6 Replies

How to read data from external excel

I am trying to find a good approach to this so I can research more details myself

Basically my program is to model different types of houses in AutoCAD, but all the data( height, width, material,supplier ) is stored in an external excel, how am I be able to read the data from excel and use it in my program, I am using VB.Net but some C# code is also appreciated.
Thanks
6 REPLIES 6
Message 2 of 7
norman.yuan
in reply to: ilovejingle


@ilovejingle wrote:
I am trying to find a good approach to this so I can research more details myself

Getting data from Excel sheet for AutoCAD application is one of the most often asked questions. If you search this forum, or AutoCAD VBA (Visual Basic) forum, or the Internet, you would get tons of links. Since you'll do more detailed research, here are my suggestions (since you post in AutoCAD .NET API forum, I assume your are doing .NET API CAD app):

 

1. Basically, you have at least 3 usual options to get data for AutoCAD

  • use COM automation to run Excel app as external app/process. That is, start Excel, if not started; open given *.xlsx file, if not opened; read vales in given cells/range of given sheet. With this approach, obviously, you need Excel app installed.
  • use MS Access DB Engine to access data in *.xlsx. In the case, Excel installation is not required, or not need to run. Data access is fast. However, requirement of installing MS Access DB Engine could be very troublesome, when your AutoCAD is 64-bit (most likely), unless your computer does not have MS Office 32-bit installed.
  • Use OpenXml to access *.xlsx file, which does not need Excel installed/run, data access is fast. There is even free OpenXml wrapper component specific for dealing with Excel, which make things much easier.

     Among these 3 of options, OpenXml would be my first choice, because it eliminates unnecessary dependencies to outside component installation (Excel, Access DB Engine) while OpenXml SDK is free and its DLL goes with your .NET DLL. Then COM automation would be the next, because most likely all your user has Excel installed. 

 

2. If you are somewhat experienced in .NET programming, you would create an Interface to get external data for AutoCAD, so that the AutoCAD side code that uses the data would not care how the data is extracted from Excel sheet (COM automation, Access DB Engine or OpenXml). For example, you could define the interface like:

 

public interface IExcelSheetData

{

    object GetData(string fileName);

}

 

Then you can implement at least 3 data accessing class for each options aforementioned (of course you only need one. But you may change your idea later and choose different option, so simply implement a different one and without need to change of code AutoCAD side at all).

 

Hope this points a few directions you could research towards deeper

 

Message 3 of 7
ilovejingle
in reply to: norman.yuan

Thanks, I will research more on the method you recommended to see whether I can figure it out.

Message 4 of 7
Keith.Brown
in reply to: ilovejingle

I use Gembox.Spreadsheet to read/write excel files.  No excel is required and it has a free version which is fully functional but limited to 150 rows per sheet and 3 sheets.  Depending on your criteria, it might just work for you.  

 

The full version is expensive but if you do alot of work with excel files then it is well worth it.

 

Another free alternative is SpreadsheetLite

 

 

Message 5 of 7
ilovejingle
in reply to: Keith.Brown

Thanks, easy and intuitive !
Message 6 of 7

I think you should try ZetExcel.

Message 7 of 7
romankris
in reply to: ilovejingle

Try OLEDB

                System.Data.OleDb.OleDbConnection MyConnection ;
                System.Data.DataSet DtSet ;
                System.Data.OleDb.OleDbDataAdapter MyCommand ;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
                MyCommand.TableMappings.Add("Table", "TestTable");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);
                dataGridView1.DataSource = DtSet.Tables[0];
                MyConnection.Close();

More on...read excel in C#

 

 

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk DevCon in Munich May 28-29th


Autodesk Design & Make Report

”Boost