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

Read Embedded Excel sheet

14 REPLIES 14
SOLVED
Reply
Message 1 of 15
Ertqwa
2821 Views, 14 Replies

Read Embedded Excel sheet

Hello,

 

I have a drawing with an embedded Excel sheet. I want to read the contents of the excel sheet. How can this be done? Maybe in combination with Microsoft.Office.Interop.Excel?

 

Ty.

14 REPLIES 14
Message 2 of 15
fenton.webb
in reply to: Ertqwa

Does this help

 

http://adndevblog.typepad.com/autocad/2012/04/update-linked-ole-object-from-net.html




Fenton Webb
AutoCAD Engineering
Autodesk

Message 3 of 15
Ertqwa
in reply to: fenton.webb

Hi,

 

I already saw some articles about linked OLE objects, but not about embedded OLE objects. But maybe it will help me, I'll look it at.

 

Thank you.

Message 4 of 15
DouceDeux
in reply to: Ertqwa

How do you embed an Excel spreadsheet into a DWG?

Linking means the .xls(x) is somewhere else but we hold a reference to the spreadsheet.

Embedding would mean the Excel object, the spreadsheet and its information is actually inside the DWG. I Haven't tinkered with that possibility yet, how did you manage to achieve it?

Besides from that.

Whether you have the Excel file inside the DWG or just a link to it, just open the Excel object like you would any Excel file, either early or latebound.

Some say "Write in early bound, compile, debug and then translate into late bound", for performance and compability reasons of course.

If you're looking into writing something quick, just do early bound and don't waste time translating.

If you have the time, early bound and then late bound. Creating a custom class can do wonders.

Message 5 of 15
Ertqwa
in reply to: DouceDeux

Hi,

 

I open Excel, add data, copy the cells, and paste it in Autocad. I close Excel without saving it.

I right-click the Excel table in AutoCAD and pick OLE->OPEN to open it again in Exel to edit it.

Since I dont save the Excel file, I assume it is saved in the dwg.

 

Ty for your reply.

Message 6 of 15
DouceDeux
in reply to: Ertqwa

For the life of meeeee!
God damned OLE objects!

I'm stuck here:

[CommandMethod("test1")]
public void test1()
{
  Database newD = Application.DocumentManager.MdiActiveDocument.Database;
  Editor newE = Application.DocumentManager.MdiActiveDocument.Editor;
  PromptEntityResult per = newE.GetEntity("Pick a OLE object: ");
  Object id = per.ObjectId;

  using (Transaction newT = newD.TransactionManager.StartTransaction())
  {
    Ole2Frame myOle = newT.GetObject(per.ObjectId, OpenMode.ForRead) as Ole2Frame;
    object excel = myOle.OleObject;
  }
}

I don't know what to do with that object or how to find how what to do with it.
I've tried everything I could think of (which wasn't much). Online search would not help.

It's an interesting topic, I guess I would need to read about OLE objects.

These guys were on to something, or solved it, I dont even know now:
http://forums.autodesk.com/t5/Autodesk-Inventor-Customization/How-do-I-programmatically-access-an-em...
but it's for Inventor, I dont know if the AutoCAD API has a ReferencedOLEFileDescriptor type.
Keep me updated.

Message 7 of 15
fenton.webb
in reply to: DouceDeux

OleObject returns an unmanaged COleClientItem object. I don't believe that is exposed by via our .NET API so you are totally on your own there. Your best (perhaps only) option is to use C++




Fenton Webb
AutoCAD Engineering
Autodesk

Message 8 of 15

Looks like it is possible in .NET but with P/Invoke:

Update linked OLE object from .NET

Відповідь корисна? Клікніть на "ВПОДОБАЙКУ" цім повідомленням! | Do you find the posts helpful? "LIKE" these posts!
Находите сообщения полезными? Поставьте "НРАВИТСЯ" этим сообщениям!
На ваше запитання відповіли? Натисніть кнопку "ПРИЙНЯТИ РІШЕННЯ" | Have your question been answered successfully? Click "ACCEPT SOLUTION" button.
На ваш вопрос успешно ответили? Нажмите кнопку "УТВЕРДИТЬ РЕШЕНИЕ"


Alexander Rivilis / Александр Ривилис / Олександр Рівіліс
Programmer & Teacher & Helper / Программист - Учитель - Помощник / Програміст - вчитель - помічник
Facebook | Twitter | LinkedIn
Expert Elite Member

Message 9 of 15

I believe that allows you to update the link, not gain access to the OleClientItem itself. But maybe that's all that's needed here?




Fenton Webb
AutoCAD Engineering
Autodesk

Message 10 of 15

Hi, Fenton!

I do not think this is all that is necessary to the author. But I point out a direction that may lead him to the solution of its problems.

Description of COleClientItem class: http://msdn.microsoft.com/en-us/library/et2y5cd7%28v=vs.100%29.aspx

IMHO it is possible call P/Invoke any methods of COleClientItem for any purpose. For example, to run associated application (Word/Excel/etc.) hi can P/Invoke method COleClientItem::Run

Відповідь корисна? Клікніть на "ВПОДОБАЙКУ" цім повідомленням! | Do you find the posts helpful? "LIKE" these posts!
Находите сообщения полезными? Поставьте "НРАВИТСЯ" этим сообщениям!
На ваше запитання відповіли? Натисніть кнопку "ПРИЙНЯТИ РІШЕННЯ" | Have your question been answered successfully? Click "ACCEPT SOLUTION" button.
На ваш вопрос успешно ответили? Нажмите кнопку "УТВЕРДИТЬ РЕШЕНИЕ"


Alexander Rivilis / Александр Ривилис / Олександр Рівіліс
Programmer & Teacher & Helper / Программист - Учитель - Помощник / Програміст - вчитель - помічник
Facebook | Twitter | LinkedIn
Expert Elite Member

Message 11 of 15

Hey Alexander

 

I agree with you... Although, I think the work involed to PInvoke this is more than the work to simply create a C++ application to deal with it.




Fenton Webb
AutoCAD Engineering
Autodesk

Message 12 of 15


@fenton.webb wrote:

Hey Alexander

 

I agree with you... Although, I think the work involed to PInvoke this is more than the work to simply create a C++ application to deal with it.


I totally agree with you. But I do not know whether the author knows how to create program in C++.

[offtopic]

I often meet people who know C#, but can not even understand the code in C++. It's very sad when you consider that these languages are similar in syntax.

[/offtopic]

Відповідь корисна? Клікніть на "ВПОДОБАЙКУ" цім повідомленням! | Do you find the posts helpful? "LIKE" these posts!
Находите сообщения полезными? Поставьте "НРАВИТСЯ" этим сообщениям!
На ваше запитання відповіли? Натисніть кнопку "ПРИЙНЯТИ РІШЕННЯ" | Have your question been answered successfully? Click "ACCEPT SOLUTION" button.
На ваш вопрос успешно ответили? Нажмите кнопку "УТВЕРДИТЬ РЕШЕНИЕ"


Alexander Rivilis / Александр Ривилис / Олександр Рівіліс
Programmer & Teacher & Helper / Программист - Учитель - Помощник / Програміст - вчитель - помічник
Facebook | Twitter | LinkedIn
Expert Elite Member

Message 13 of 15

If someone is going to PInvoke COleClientItem then he sure is going to need to know C++!!! 🙂




Fenton Webb
AutoCAD Engineering
Autodesk

Message 14 of 15


@fenton.webb wrote:

If someone is going to PInvoke COleClientItem then he sure is going to need to know C++!!! 🙂


Oh! I think that's where you're wrong. There are volunteers to help write the function signature for P/Invoke (and I am one of them).

Відповідь корисна? Клікніть на "ВПОДОБАЙКУ" цім повідомленням! | Do you find the posts helpful? "LIKE" these posts!
Находите сообщения полезными? Поставьте "НРАВИТСЯ" этим сообщениям!
На ваше запитання відповіли? Натисніть кнопку "ПРИЙНЯТИ РІШЕННЯ" | Have your question been answered successfully? Click "ACCEPT SOLUTION" button.
На ваш вопрос успешно ответили? Нажмите кнопку "УТВЕРДИТЬ РЕШЕНИЕ"


Alexander Rivilis / Александр Ривилис / Олександр Рівіліс
Programmer & Teacher & Helper / Программист - Учитель - Помощник / Програміст - вчитель - помічник
Facebook | Twitter | LinkedIn
Expert Elite Member

Message 15 of 15
Ertqwa
in reply to: Ertqwa

Hello,

 

I've got it working. Thought I post it here, if anyone is looking for something similar:

 

[CommandMethod("TestExcel")]
public void TestExcel()
{
    // using Excel = Microsoft.Office.Interop.Excel;
    Excel.Workbook objWorkBook = null;
    Excel.Worksheet objWorksheet = null;
    Excel.Range objRange = null;
    Document objDocument = null;
    Database objDatabase = null;
    Editor objEditor = null;
    Transaction objTransaction = null;
    PromptEntityResult objPromptEntityResult = null;
    Object oidExcel;
    Ole2Frame objOle2Frame;
    string strCell;
    int intRow = 0;
    int intColumn = 0;

    try
    {
        // Get active document, database and editor.
        objDocument = CADS.Application.DocumentManager.MdiActiveDocument;
        objDatabase = objDocument.Database;
        objEditor = objDocument.Editor;
        // Ask user to select the excel tabel.
        objPromptEntityResult = objEditor.GetEntity("\nSelect the excel table: ");
        if (objPromptEntityResult.Status != PromptStatus.OK)
        { return; }
        // Get id of selected object.
        oidExcel = objPromptEntityResult.ObjectId;
        // Start transaction.
        using (objTransaction = objDatabase.TransactionManager.StartTransaction())
        {
            // Get Ole2Frame object.
            objOle2Frame = objTransaction.GetObject(objPromptEntityResult.ObjectId, OpenMode.ForRead) as Ole2Frame;
            // The Ole2Frame will be an object of type Microsoft.Office.Interop.Excel.Workbook.
            objWorkBook = objOle2Frame.OleObject as Excel.Workbook;
            // Get the first sheet (it is assumed the table has one).
            objWorksheet = (Excel.Worksheet)objWorkBook.Worksheets.get_Item(1);
            // Get the range of cells in the sheet.
            objRange = objWorksheet.UsedRange;
            // Loop the range and write contents to the editor.
            for (intRow = 1; intRow <= objRange.Rows.Count; intRow++)
            {
                for (intColumn = 1; intColumn <= objRange.Columns.Count; intColumn++)
                {
                    try
                    {
                        strCell = Convert.ToString((objRange.Cells[intRow, intColumn] as Excel.Range).Value2);
                        objDocument.Editor.WriteMessage("\n{0}.", strCell);
                    }
                    catch
                    {
                        objDocument.Editor.WriteMessage("\nCould not convert cell {0},{1} to a string.", intRow.ToString(), intColumn.ToString());
                    }
                }
            }
        }
    }
    catch (System.Exception Ex)
    {
        CADS.Application.DocumentManager.MdiActiveDocument.Editor.WriteMessage("Error retreiving data from embedded excel file: " + Ex.ToString());
    }
}

 

Thank you VERY much, all who responded to my question.

 

 

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