Read Embedded Excel sheet

[Ertqwa]

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.

[Ertqwa]

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.

[fenton_webb]

Does this help

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

[fenton_webb]

Does this help

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

[Ertqwa]

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.

[Ertqwa]

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.

[Anonymous]

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.

[Anonymous]

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.

[Ertqwa]

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.

[Ertqwa]

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.

[Anonymous]

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.

[Anonymous]

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.

[fenton_webb]

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]

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++

[Alexander.Rivilis]

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

Update linked OLE object from .NET

[Alexander.Rivilis]

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

Update linked OLE object from .NET

[fenton_webb]

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]

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?

[Alexander.Rivilis]

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

[Alexander.Rivilis]

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

[fenton_webb]

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]

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.

[Alexander.Rivilis]

---

@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]

[Alexander.Rivilis]

---

@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]

[fenton_webb]

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

[fenton_webb]

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

[Alexander.Rivilis]

---

@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).

[Alexander.Rivilis]

---

@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).

[Ertqwa · Accepted solution]

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.

[Ertqwa · Accepted solution]

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.

[c_rampe]

Hi, can you share the complete solution? Including all the necessary using directives and required libraries?

[c_rampe]

Hi, can you share the complete solution? Including all the necessary using directives and required libraries?