Author Topic: .net Excel w/ CAD Advice  (Read 1789 times)

0 Members and 1 Guest are viewing this topic.

hobleglobsquishyworms

  • Swamp Rat
  • Posts: 781
  • .net stuff
.net Excel w/ CAD Advice
« 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!

huiz

  • Swamp Rat
  • Posts: 853
  • Certified Prof C3D
Re: .net Excel w/ CAD Advice
« Reply #1 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.
The conclusion is justified that the initialization of the development of critical subsystem optimizes the probability of success to the development of the technical behavior over a given period.

MickD

  • Gator
  • Posts: 3315
  • (x-in)->[process]->(y-out)
Re: .net Excel w/ CAD Advice
« Reply #2 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 :)
Forth is like the Tao: it is a Way, and is realized when followed.
Its fragility is its strength; its simplicity is its direction - Michael Ham

Lao Tzu: “To attain knowledge, add things
every day; to obtain wisdom, remove things every day.”

hobleglobsquishyworms

  • Swamp Rat
  • Posts: 781
  • .net stuff
Re: .net Excel w/ CAD Advice
« Reply #3 on: June 20, 2018, 02:25:44 AM »
Thanks for both the ideas MickD / Huiz. Very much appreciated!

huiz

  • Swamp Rat
  • Posts: 853
  • Certified Prof C3D
Re: .net Excel w/ CAD Advice
« Reply #4 on: June 20, 2018, 03:13:51 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.
The conclusion is justified that the initialization of the development of critical subsystem optimizes the probability of success to the development of the technical behavior over a given period.

MickD

  • Gator
  • Posts: 3315
  • (x-in)->[process]->(y-out)
Re: .net Excel w/ CAD Advice
« Reply #5 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.
Forth is like the Tao: it is a Way, and is realized when followed.
Its fragility is its strength; its simplicity is its direction - Michael Ham

Lao Tzu: “To attain knowledge, add things
every day; to obtain wisdom, remove things every day.”

Keith Brown

  • Swamp Rat
  • Posts: 597
Re: .net Excel w/ CAD Advice
« Reply #6 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
Keith Brown | AutoCAD MEP Blog | RSS Feed
AutoCAD MEP 2014 / Revit MEP 2014 / EastCoast CAD/CAM addon / Visual Studio 2013

n.yuan

  • Bull Frog
  • Posts: 280
Re: .net Excel w/ CAD Advice
« Reply #7 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.

dgorsman

  • Water Moccasin
  • Posts: 2402
Re: .net Excel w/ CAD Advice
« Reply #8 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.
If you are going to fly by the seat of your pants, expect friction burns.

try {GreatPower;}
   catch (notResponsible)
      {NextTime(PlanAhead);}
   finally
      {MasterBasics;}

MickD

  • Gator
  • Posts: 3315
  • (x-in)->[process]->(y-out)
Re: .net Excel w/ CAD Advice
« Reply #9 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

Forth is like the Tao: it is a Way, and is realized when followed.
Its fragility is its strength; its simplicity is its direction - Michael Ham

Lao Tzu: “To attain knowledge, add things
every day; to obtain wisdom, remove things every day.”

dgorsman

  • Water Moccasin
  • Posts: 2402
Re: .net Excel w/ CAD Advice
« Reply #10 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.
If you are going to fly by the seat of your pants, expect friction burns.

try {GreatPower;}
   catch (notResponsible)
      {NextTime(PlanAhead);}
   finally
      {MasterBasics;}

hobleglobsquishyworms

  • Swamp Rat
  • Posts: 781
  • .net stuff
Re: .net Excel w/ CAD Advice
« Reply #11 on: June 22, 2018, 08:19:29 AM »
Thank you for the input everyone :) It's a lot of help, and very much appreciated.

CADbloke

  • Bull Frog
  • Posts: 308
  • Crash Test Dummy
Re: .net Excel w/ CAD Advice
« Reply #12 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.

MickD

  • Gator
  • Posts: 3315
  • (x-in)->[process]->(y-out)
Re: .net Excel w/ CAD Advice
« Reply #13 on: June 24, 2018, 09:37:46 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]
  1.         public static List<string[]> CSVFileToSplitList(string filePath)
  2.         {
  3.             List<string[]> list = new List<string[]>();
  4.             if (File.Exists(filePath))
  5.             {
  6.                 string[] csvRows = File.ReadAllLines(filePath);
  7.                 string[] fields = new string[4];
  8.                 foreach (string csvRow in csvRows)
  9.                 {
  10.                     fields = csvRow.Split(',');
  11.                     list.Add(fields);
  12.                 }
  13.             }
  14.             return list;
  15.         }
  16.  
Forth is like the Tao: it is a Way, and is realized when followed.
Its fragility is its strength; its simplicity is its direction - Michael Ham

Lao Tzu: “To attain knowledge, add things
every day; to obtain wisdom, remove things every day.”

MexicanCustard

  • Swamp Rat
  • Posts: 705
Re: .net Excel w/ CAD Advice
« Reply #14 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]
  1. return File.ReadAllLines(filename).Select(l => l.Split(',')).ToList();
« Last Edit: June 25, 2018, 07:24:26 AM by MexicanCustard »
Revit 2019, AMEP 2019 64bit Win 10