topic Re: Read Embedded Excel sheet in .NET Forum

Read Embedded Excel sheet

Ertqwa — Fri, 08 Feb 2013 19:11:05 GMT

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.

Re: Read Embedded Excel sheet

fenton_webb — Tue, 12 Feb 2013 01:22:36 GMT

Does this help

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

Re: Read Embedded Excel sheet

Ertqwa — Tue, 12 Feb 2013 04:50:39 GMT

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.

Re: Read Embedded Excel sheet

Anonymous — Tue, 12 Feb 2013 16:29:52 GMT

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.

Re: Read Embedded Excel sheet

Ertqwa — Wed, 13 Feb 2013 19:55:06 GMT

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.

Re: Read Embedded Excel sheet

Anonymous — Thu, 14 Feb 2013 00:52:55 GMT

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-embedded-Excel-document-from/td-p/3379409
but it's for Inventor, I dont know if the AutoCAD API has a ReferencedOLEFileDescriptor type.
Keep me updated.

Re: Read Embedded Excel sheet

fenton_webb — Thu, 14 Feb 2013 23:13:26 GMT

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

Re: Read Embedded Excel sheet

Alexander.Rivilis — Fri, 15 Feb 2013 09:26:04 GMT

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

Update linked OLE object from .NET

Re: Read Embedded Excel sheet

fenton_webb — Fri, 15 Feb 2013 15:24:48 GMT

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?

Re: Read Embedded Excel sheet

Alexander.Rivilis — Fri, 15 Feb 2013 15:43:38 GMT

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

Re: Read Embedded Excel sheet

fenton_webb — Fri, 15 Feb 2013 16:25:40 GMT

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.

Re: Read Embedded Excel sheet

Alexander.Rivilis — Fri, 15 Feb 2013 17:02:51 GMT

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

Re: Read Embedded Excel sheet

fenton_webb — Fri, 15 Feb 2013 17:08:08 GMT

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

Re: Read Embedded Excel sheet

Alexander.Rivilis — Fri, 15 Feb 2013 17:53:23 GMT

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

Re: Read Embedded Excel sheet

Ertqwa — Sun, 17 Feb 2013 18:34:33 GMT

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.

Re: Read Embedded Excel sheet

c_rampe — Fri, 01 Nov 2024 12:42:40 GMT

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