topic Re: Excel to datagrid in Revit in Revit API Forum

Excel to datagrid in Revit

vanlion — Wed, 31 Oct 2018 22:10:32 GMT

Hi,

I have a code (see below)that can load an excel file xls and xlsx in visual studio. This code i'd like to use in Revit. It is working with xls files but when i want to load xlsx i get the following error message. Is this because it isn't possible to read xlsx? i that case i will stay with xls. But maybe it's a simple solution that i couldn't find

private void button3_Click(object sender, EventArgs e)
   {            
    OpenFileDialog openFileDialog1 = new OpenFileDialog();  //create openfileDialog Object
    openFileDialog1.Filter = "XML Files (*.xml; *.xls; *.xlsx; *.xlsm; *.xlsb) |*.xml; *.xls; *.xlsx; *.     xlsm; *.xlsb";//open file format define Excel Files(.xls)|*.xls| Excel Files(.xlsx)|*.xlsx| 
     openFileDialog1.FilterIndex = 3;

     openFileDialog1.Multiselect = false;   //not allow multiline selection at the file selection level
     openFileDialog1.Title = "Open Excel File";   //define the name of openfileDialog
     openFileDialog1.InitialDirectory = @"Desktop"; //define the initial directory                

          if (openFileDialog1.ShowDialog() == DialogResult.OK)        //executing when file open
             {
             string path = openFileDialog1.FileName;
             textBox1.Text = openFileDialog1.FileName;

             OleDbConnection connection = new OleDbConnection(String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + path + ";Extended Properties=\"Excel 12.0;HDR=YES;\""));

              connection.Open();
              OleDbCommand sqlCommand = new OleDbCommand("SELECT * FROM [Blad1$]", connection);
              OleDbDataAdapter adaObj = new OleDbDataAdapter();
              adaObj.SelectCommand = sqlCommand;
              DataSet setObj = new DataSet();
              adaObj.Fill(setObj);
              connection.Close();
              dataGridView1.DataSource = setObj.Tables[0];
            }

Re: Excel to datagrid in Revit

MexicanCustard — Thu, 01 Nov 2018 11:26:00 GMT

Without getting into checking that OleDb is setup up or that you have excel on the target machine. Look at using libraries like ClosedXml and SpreadSheetLight instead of OleDb. There are lots of open source libraries out there that don't depend on Excel being installed or a connection being setup in the OS like OleDb.

Re: Excel to datagrid in Revit

vanlion — Thu, 01 Nov 2018 15:49:33 GMT

Hi MexicanCustard,

Thanks for the reply! So it has something to do if Excel is installed or not.

I will google the solutions you said. Also i saw a package ExcelDataReader you can download for free.

I only don't know how it goes with rights when you sell the code

Re: Excel to datagrid in Revit

vanlion — Thu, 01 Nov 2018 20:06:11 GMT

Hi,

For working with .xlsx i used OpenXml and that is working but can't open older files. But the company the addin is build for doesn't use older versions 🙂 it only has some trouble with Google spreadsheets saved as xlxs