show an Excel spreadsheet as a table on windows form in Revit

[Anonymous]

I am trying to show an Excel spreadsheet as a table on windows form in Revit,
First I wrote the code in VisualStudio, I created a windows form and by running that, it opens the windows form and shows excel data in Table format `

        namespace ExcelVisualStudio
         {
           public partial class Form1 : Form
         {
             public Form1()
           {
              InitializeComponent();
              }

                 private void button1_Click(object sender, EventArgs e)
                {
                     string PathConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"C:\Users\farshidmoosavi\Desktop\Schedule3.xls" + ";Extended                             Properties=Excel 12.0;";
                     OleDbConnection conn = new OleDbConnection(PathConn);

                     OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("SELECT * FROM [" + "Sheet1" + "$]", conn);
                    DataTable dt = new DataTable();
                    myDataAdapter.Fill(dt);

                   dataGridView1.DataSource = dt;
          }
      }
}`

Then I created an External command, added a windows form for that and did exactly the same process, and this is the code for external command :

                 [Autodesk.Revit.Attributes.Transaction(Autodesk.Revit.Attributes.TransactionMode.ReadOnly)]
                 public class iShowDataWindowsForm : IExternalCommand
                       {
                       static AddInId appId = new AddInId(new Guid("DB98294F-89D9-4EAE-A990-63A0694F91CC"));
                           public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elementSet)
                        {

                            using (Form1 thisform = new Form1())
                           {
                             thisform.ShowDialog();

                               }

                              return Result.Succeeded;
                                }

  My problem is when I open Revit and run the plugin, it opens a windows form, but after clicking on show button, Revit Does crash and closes.
I appreciate if anyone can help me.

I debugged the code and I found that this is the error that causes the program to crash:

       Step into: Stepping over non-user code 'System.Data.Common.DbDataAdapter.Fill'
       'Revit.exe' (Managed (v4.0.30319)): Loaded                'C:\windows\Microsoft.Net\assembly\GAC_64\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Exception thrown: 'System.AccessViolationException' in System.Data.dll
The program '[168] Revit.exe: Managed (v4.0.30319)' has exited with code -1073741819 (0xc0000005) 'Access violation'.

STILL, don't know how to fix that.

The picture below shows how it works when I have not yet made an external command.

[Anonymous · Accepted solution]

I just got the problem solved.
At first, I tried it Revit 2018, and the advantage was that it showed an error and didn't crash. By Googling the error, I changed the version of office to 2013, Access engine 64b, and then the file format that I saved as my excel spreadsheet to .xls. Then it worked.

[Anonymous]

Hi farshid90,

    Do you mind telling me that how to make these two codes work? What references should I add?

    Data Source=" + @"C:\Users\farshidmoosavi\Desktop\Schedule3.xls" + ";

    Extended   Properties=Excel 12.0;";

Thanks a lot!

[Anonymous]

Hello,

I'm trying to do the same thing. I mean to insert an excel table into a revit sheet (not just data but the table with it forms and if possible linked with its formulas).

I put the code you made in a codeblock on DYNAMO but it's not working. Can you tell me how did you do it?

Thanks a lot

[Anonymous]

Hi,

The way I have seen this done is by repeatedly splitting the header cells of a Revit schedule and putting the data from Excel in those cells.

http://puntorevit.blogspot.com/2014/11/insert-excel-worksheets-as-revti.html

http://www.hksinc.com/hksline/2015/10/19/importing-excel-into-revit/