Print Page - .net Excel w/ CAD Advice

Code Red => .NET => Topic started by: nobody on June 20, 2018, 12:38:13 AM

Title: .net Excel w/ CAD Advice
Post by: nobody on June 20, 2018, 12:38:13 AM

I need to write some code to draw some stuff in autocad using data on an excel form. Essentially it either needs to be a button in excel that will fire a command in CAD and start drawing, or a command in CAD that will grab the excel data.

I'm looking for advice from people who have done this...not code, but basically anything you are willing to share from your experience that might help me avoid pitfalls or problems as I dive in to start writing code.

Thanks a bunch!

Title: Re: .net Excel w/ CAD Advice
Post by: huiz on June 20, 2018, 01:09:42 AM

Recently I found EPPlus, it's just one DLL that is able to read and write .xlsx files. There are more solutions but this one is quite powerfull.

You can use it to start grabbing from AutoCAD. If you want do to it from inside Excel, you need to write macro's. I have n experience with that.

Title: Re: .net Excel w/ CAD Advice
Post by: MickD on June 20, 2018, 02:16:40 AM

If your data in excel is in a 'flat' format it would be pretty easy to export the excel data as csv then read in the csv into AutoCAD to draw. Even if you did this as 'stage 1' of the development process as a proof of concept and then work out whether to automate this from Excel or AutoCAD, either way you will need to work with both API's but at least you'll have your drawing engine done and a workable solution :)

Title: Re: .net Excel w/ CAD Advice
Post by: nobody on June 20, 2018, 02:25:44 AM

Thanks for both the ideas MickD / Huiz. Very much appreciated!

Title: Re: .net Excel w/ CAD Advice
Post by: huiz on June 20, 2018, 03:13:51 AM

Quote from: MickD on June 20, 2018, 02:16:40 AM

If your data in excel is in a 'flat' format it would be pretty easy to export the excel data as csv then read in the csv into AutoCAD to draw. Even if you did this as 'stage 1' of the development process as a proof of concept and then work out whether to automate this from Excel or AutoCAD, either way you will need to work with both API's but at least you'll have your drawing engine done and a workable solution :)

CSV is very easy to write of course but in some countries you have to deal with commas inside Excel as decimal seperator or dots as thousands seperator. When a comma is used, the column seperator must be a semicolon. So reading a CSV is more difficult. Also I had problems with accented characters, so you must write the file in the correct UTF modus.

You can also look around for a CSV class, which will encounter these bottlenecks.

Title: Re: .net Excel w/ CAD Advice
Post by: MickD on June 20, 2018, 05:02:40 AM

fair point Huiz!

There is a workaround though. Set you csv delimiter to some other symbol such as ';' or '|'. Here's an example on how after a very quick search.
https://www.itsupportguides.com/knowledge-base/office-2013/excel-20132016-how-to-change-csv-delimiter-character/

It is also pretty trivial to write a '?'sv parser using something like my_string.Split(';') for example as long as the data is well-formed. Coming from Excel it should be ok.

Title: Re: .net Excel w/ CAD Advice
Post by: Keith Brown on June 20, 2018, 09:10:51 AM

How much data do you have in your excel sheet? If you can limit it to 150 rows and 5 sheets, you can use Gembox spreadsheet. The free version is fully functional with the previously mentioned restrections. From their web page:

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

With GemBox.Spreadsheet you get a fast and reliable component that’s easy to use. It requires only the .NET framework, so you can deploy your application easily without having to think about other licenses. And it’s up to 250 times faster than Microsoft Excel automation!

https://www.gemboxsoftware.com/spreadsheet (https://www.gemboxsoftware.com/spreadsheet)

Title: Re: .net Excel w/ CAD Advice
Post by: n.yuan on June 20, 2018, 10:07:16 AM

While other replies all provide very helpful opinions, I'd like add a bit extra.

Using external data in AutoCAD to generate entities (or do anything in AutoCAD, for that matter) is very common process. When programming such a task, it is very important to decouple the process inside AutoCAD and the data storage format. That is, in AutoCAD, you define your data classes/models, which is used to hold runtime data for AutoCAD to do its work. And then you define am INTERFACE for accessing to the data from whatever data storage. Then you can have different data access implementation for different data store. For AutoCAD, it is completely blind to the external data storage, be the data in *.xls, or in *.csv, or Excel app installed or not, or the data in some sort of database or not.

Title: Re: .net Excel w/ CAD Advice
Post by: dgorsman on June 20, 2018, 10:09:42 AM

My first preference is to always run AutoCAD and consume external data files, rather than driving AutoCAD via an external program.

I also tend to stay away from Excel and other Office applications for data storage, as upgrades/patches can "cut the cord" very easily. Not so much a problem with your own computer but throw in a few dozen people and IT pushing updates and it's a chore. Another downside to using Excel is users can delete/hide/merge cells rows and columns; when your program is expecting something to be available, or there is something there that was hidden and forgotten about, it throws a wrench into the process.

So I've taken to using XML for small-scale data storage. I can build a user interface in CAD which forces users to do the right thing, and contents can be tweaked manually using various text editors. It's not restricted to flat X-by-Y dimensions, and can be transformed into HTML, CSV, or other text files (I've got a technology tester that generates LIN files around here somewhere) by applying a style sheet.

Title: Re: .net Excel w/ CAD Advice
Post by: MickD on June 20, 2018, 06:34:44 PM

both n.yuan and dgorsman make excellent points. Keep the data and AutoCAD decoupled as much as possible. In my book Interfaces trump Classes any day and are an excellent way 'plug together' different components whose underlying structure can be very different.

Keep it as simple as possible, while automating the whole process from one app may _seem_ to be easier for the user, developing the solution may be more pain/cost than it's worth. You are literally talking seconds in each app to export and import the data whereas the dev and maintenance of coupling them together will be an ongoing cost and potential source of bugs as mentioned above.

Before I start a new app I like to refer to the 'Basics of Unix Philosophy' page to keep the process under control and at least the first six points apply here :
https://homepage.cs.uri.edu/~thenry/resources/unix_art/ch01s06.html

Title: Re: .net Excel w/ CAD Advice
Post by: dgorsman on June 21, 2018, 10:03:12 AM

Ah, yes - Interfaces. I keep forgetting about how useful those are. Being able to pass this, that, or the other just because they all implement INeedToDoTHISToYou... priceless.

Title: Re: .net Excel w/ CAD Advice
Post by: nobody on June 22, 2018, 08:19:29 AM

Thank you for the input everyone :) It's a lot of help, and very much appreciated.

Title: Re: .net Excel w/ CAD Advice
Post by: CADbloke on June 24, 2018, 07:42:35 PM

Mick, thanks for that link, it's a beauty. I'll add https://www.joelonsoftware.com/2009/09/23/the-duct-tape-programmer/

Also: Interfaces - yeah, what @dgorsman said, they are the loose coupling real-thing we always hear about so abstractedly.

Title: Re: .net Excel w/ CAD Advice
Post by: MickD on June 24, 2018, 09:37:46 PM

Quote from: CADbloke on June 24, 2018, 07:42:35 PM

Mick, thanks for that link, it's a beauty. I'll add https://www.joelonsoftware.com/2009/09/23/the-duct-tape-programmer/

Also: Interfaces - yeah, what @dgorsman said, they are the loose coupling real-thing we always hear about so abstractedly.

Good read CADBloke :)

While I would not put myself in the same class as the 'duct tape programmer' it does pretty much describe how I get things done. I'd rather battle it out handwriting the code I need than spend days/weeks learning a new API to do something that I _should_ be able to code up myself and more often than not I only need a very small part of what the 'framework' offers.
I learn more that way too :)

I have more than a few 'prototype' applications (with 1000's LOC) in production use that could use a total rewrite but as a single person operation it's hard to find the time and there's a very good chance I'll break something that is working. Mind you, I do clean up sections of my code as I'm making edits or fixing bugs if I have the time so it's a work in progress :)

Here is a quick sample of a very simple csv parser that takes a csv file path to read in, splits each line (row in excel) on the delimiter and returns a list ready for use. It is for spot heights that include an ID field and x,y,z coordinates. Sure, it's not as reusable as an imported library method and the size of the fields is fixed but why complicate things :)

Code - C#: [Select]

        public static List<string[]> CSVFileToSplitList(string filePath)
        {
            List<string[]> list = new List<string[]>();
            if (File.Exists(filePath))
            {
                string[] csvRows = File.ReadAllLines(filePath);
                string[] fields = new string[4];
                foreach (string csvRow in csvRows)
                {
                    fields = csvRow.Split(',');
                    list.Add(fields);
                }
            }
            return list;
        }
 

Title: Re: .net Excel w/ CAD Advice
Post by: MexicanCustard on June 25, 2018, 07:18:21 AM

Mick I'm with you. For something so simple I don't want to bloat my application with a library. You could simplify even more though.

Code - C#: [Select]

return File.ReadAllLines(filename).Select(l => l.Split(',')).ToList();

Title: Re: .net Excel w/ CAD Advice
Post by: It's Alive! on June 25, 2018, 09:14:00 AM

If this project is internal to your company, and have control over what's installed, I would use the Microsoft Excel Object Library.
Excel & late binding works well too if your looking for generic automation.
Lots of examples out there for connecting to excel from .NET. With this method, you can take advantage of Excel's Named Ranges & formulas

cheers :)

Title: Re: .net Excel w/ CAD Advice
Post by: MickD on June 25, 2018, 04:42:35 PM

Quote from: MexicanCustard on June 25, 2018, 07:18:21 AM

Mick I'm with you. For something so simple I don't want to bloat my application with a library. You could simplify even more though.

Code - C#: [Select]
return File.ReadAllLines(filename).Select(l => l.Split(',')).ToList();

Nice one MC. I like LINQ but I haven't got around using it much yet as I still think in a procedural fashion :)

Title: Re: .net Excel w/ CAD Advice
Post by: Jeff H on June 25, 2018, 06:06:02 PM

LINQ is the shiznatch!

TheSwamp

Code Red => .NET => Topic started by: nobody on June 20, 2018, 12:38:13 AM