GENERATE SHEETS FROM EXCEL

[Anonymous]

Hello All,

I'm using dynamo to generate sheets from excel (by using this link). I'd like to create an add-in for Revit that simulates the same functionality. I researched and found this solution that generates sheets from the CSV file. However, I'm not able to understand the steps to generate the sheets like the dynamo file does (please see the link). 

I'm wondering if anyone could explain the steps so I could write a better code so, it could work like that dynamo file solution works.  Thank you

[aignatovich]

Hi!

I've send a private message to you

[Anonymous]

Hello imaliasad

You can try something like this:

public void ExcelFile(Document doc,string filepath)
        {

            // Launch or access Excel via COM Interop:

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            //open an existing file
            Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(filepath);

            excel.Visible = true;

            Microsoft.Office.Interop.Excel.Worksheet worksheet;

            // Create and name the worksheet
            bool first = true;
            if (first)
            {
                worksheet = workbook.Sheets.get_Item(1)
                  as Microsoft.Office.Interop.Excel.Worksheet;

                first = false;
            }
            else
            {
                worksheet = excel.Worksheets.Add(
                  Missing.Value, Missing.Value,
                  Missing.Value, Missing.Value)
                  as Microsoft.Office.Interop.Excel.Worksheet;
            }

            worksheet.Name = "TheNameYouWant";

            //it will start to fill what you want in the 2nd row 1st column (I'm assuming there is an header in the excel file)
            int row = 2;
            foreach (string X in stringList)
            {
                worksheet.Cells[row, 1] = X;
                row++;
            }

	    //now fill something on the 2nd column
            row = 2;
            foreach (string X in stringList)
            {
                worksheet.Cells[row, 2] = X;
                row++;
            }

	    //now fill something on the 3rd column
            row = 2;
            foreach (string X in stringList)
            {
                worksheet.Cells[row, 3] = X;
                row++;
            }


            var range = worksheet.get_Range("A1", "Z1");

            range.Font.Bold = true;
            range.EntireColumn.AutoFit();
            
        }

However, before this you'll have to add Excel reference into the project.

Cheers and good luck!

Ruben

[Anonymous]

Thanks but I can't see any sheet being created in Revit??

[Anonymous]

Sorry Imaliasad, I didn't read the question properly. I mistook View Sheets for Excel Sheets! My bad

[Anonymous · Accepted solution]

Anyone who need the solution, Following is the solution: 

 public static void GenerateSheets(Document doc)
        {
            string fileName;

            System.Windows.Forms.OpenFileDialog openDlg = new System.Windows.Forms.OpenFileDialog();
            openDlg.Title = "Select a file";
            openDlg.Filter = "Comma Separated Values (*.csv)|*.csv|Text Files (*.txt)|*.txt|All Files (*.*)|*.*";
            openDlg.RestoreDirectory = true;

            System.Windows.Forms.DialogResult result = openDlg.ShowDialog();
            if (result == System.Windows.Forms.DialogResult.OK)
            {
                fileName = openDlg.FileName;
                System.IO.StreamReader sr = new System.IO.StreamReader(fileName);

                //Create a filter to get all the title block types.
                FilteredElementCollector collector = new FilteredElementCollector(doc);
                collector.OfCategory(BuiltInCategory.OST_TitleBlocks);
                collector.WhereElementIsElementType();

                //Get ElementId of first title block type.
                ElementId titleBlockId = collector.FirstElementId();

                string csvLine = null;

                Transaction t = new Transaction(doc, "Create Sheets");
                t.Start();
                while ((csvLine = sr.ReadLine()) != null)
                {
                    char[] separator = new char[] { ',' };
                    string[] values = csvLine.Split(separator, StringSplitOptions.None);

                    // Make sure both values are valid
                    if (values[0] != null && values[0].Length > 0 && values[1] != null && values[1].Length > 0)
                    {
                        ViewSheet sheet = ViewSheet.Create(doc, titleBlockId);
                        sheet.Name = values[1];
                        sheet.SheetNumber = values[0];
                    }
                }
                t.Commit();
            }
        }