9 Replies - 40459 Views - Last Post: 04 October 2010 - 01:32 PM Rate Topic: -----

#1 Guest_Sean*


Reputation:

Writing to Excel with Interop.Excel

Posted 01 October 2010 - 10:32 AM

Hi All,

I want to write a specific string to an excel file, I've looked around on the internet but there seem to be alot of different approaches to this problem. I am using Visual Studio 2010, and referencing the Microsoft Excel 12.0 Object Library.

using System;
using System.Drawing;
using System.Collections.Generic;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Reflection; 
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

   public class MyExcel
    {
        public void ExcelApp()
        {

        Excel.Application myExcelApp = new Excel.Application();
        Excel.Application myWorkbook;
        Excel.Application myWorksheet;
        myExcelApp.Visible = true;

        object misValue = System.Reflection.Missing.Value;
        myWorkbook = myExcelApp.Workbooks;


        string myPath = @"C:\Documents and Settings\sean\Desktop";
        myExcelApp.Workbooks.Open(myPath);
        int rowIndex = 1; int colIndex = 1;

        myExcelApp.Cells[rowIndex, colIndex] = "Testing";

        }

    }



Need some assistance with completing this code and how the overall class works.

Thanks.

Is This A Good Question/Topic? 0

Replies To: Writing to Excel with Interop.Excel

#2 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6535
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: Writing to Excel with Interop.Excel

Posted 01 October 2010 - 10:38 AM

Quote

Need some assistance with completing this code
Rather vague.

Are you getting errors, exceptions... ? Can you be a little more specific than something that equates to "someone write it for me" ?
Was This Post Helpful? 0
  • +
  • -

#3 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Writing to Excel with Interop.Excel

Posted 01 October 2010 - 10:52 AM

If you're using .NET 4, I'd suggest taking a look at this MSDN article:

http://msdn.microsof...y/dd264733.aspx
Was This Post Helpful? 0
  • +
  • -

#4 Guest_Sean*


Reputation:

Re: Writing to Excel with Interop.Excel

Posted 01 October 2010 - 10:55 AM

        Excel.Application myExcelApp = new Excel.Application();
        string myPath = @"C:\Documents and Settings\sean\Desktop";
        myExcelApp.Workbooks.Open(myPath);
        int rowIndex = 1; int colIndex = 1;

        myExcelApp.Cells(rowIndex, colIndex) = "Testing";
        myExcelApp.Visible = true;



Error 1 Non-invocable member 'Microsoft.Office.Interop.Excel._Application.Cells' cannot be used like a method. C:\Documents and Settings\sean\Local Settings\Application Data\Temporary Projects\Project1\CodeFile1.cs 24 20 Project1
Was This Post Helpful? 0

#5 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6258
  • View blog
  • Posts: 24,026
  • Joined: 23-August 08

Re: Writing to Excel with Interop.Excel

Posted 01 October 2010 - 10:57 AM

Why did you change it from array notation?
Was This Post Helpful? 0
  • +
  • -

#6 Guest_Sean*


Reputation:

Re: Writing to Excel with Interop.Excel

Posted 01 October 2010 - 10:57 AM

View PostinsertAlias, on 01 October 2010 - 09:52 AM, said:

If you're using .NET 4, I'd suggest taking a look at this MSDN article:

http://msdn.microsof...y/dd264733.aspx


Ah thanks, that is much clearer.
Was This Post Helpful? 0

#7 Guest_Sean*


Reputation:

Re: Writing to Excel with Interop.Excel

Posted 01 October 2010 - 12:15 PM

Ok, so I can compile and run without any errors, but the program only seems to open up the excel file and doesnt actually write any characters to the cells.

Here is all the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication1
{
    public class MyExcel
    {
        public void ExcelApp(string one, string two)
        {

        Excel.Application myExcelApp = new Excel.Application();
        string myPath = @"C:\Write";
        myExcelApp.Workbooks.Open(myPath);
        int rowIndex = 1; int colIndex = 1;
        int rowIndex2 = 2; int colIndex2 = 2;

        myExcelApp.Cells[rowIndex, colIndex] = one;
        myExcelApp.Cells[rowIndex2, colIndex2] = two;
        myExcelApp.Visible = true;

        }

    }

    public class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }


    }
}



Below is the form code:

namespace WindowsFormsApplication1
{
    partial class Form1
    {

        private System.ComponentModel.IContainer components = null;


        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        public void InitializeComponent()
        {
            this.button1 = new System.Windows.Forms.Button();
            this.textBox1 = new System.Windows.Forms.TextBox();
            this.textBox2 = new System.Windows.Forms.TextBox();
            this.SuspendLayout();
            // 
            // button1
            // 
            this.button1.Location = new System.Drawing.Point(12, 97);
            this.button1.Name = "button1";
            this.button1.Size = new System.Drawing.Size(75, 23);
            this.button1.TabIndex = 0;
            this.button1.Text = "Write";
            this.button1.UseVisualStyleBackColor = true;
            this.button1.Click += new System.EventHandler(button1_Click);
         
            // 
            // textBox1
            // 
            this.textBox1.Location = new System.Drawing.Point(12, 61);
            this.textBox1.Name = "textBox1";
            this.textBox1.Size = new System.Drawing.Size(100, 20);
            this.textBox1.TabIndex = 1;
            this.textBox1.Text = "";
            // 
            // textBox2
            // 
            this.textBox2.Location = new System.Drawing.Point(12, 35);
            this.textBox2.Name = "textBox2";
            this.textBox2.Size = new System.Drawing.Size(100, 20);
            this.textBox2.TabIndex = 2;
            this.textBox1.Text = "";
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(292, 266);
            this.Controls.Add(this.textBox2);
            this.Controls.Add(this.textBox1);
            this.Controls.Add(this.button1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.ResumeLayout(false);
            this.PerformLayout();

        }
         
        public void button1_Click(object sender, System.EventArgs e)
        {
            Form1 form = new Form1();
            MyExcel excel = new MyExcel();

            string one = form.textBox1.Text;
            string two = form.textBox2.Text;

            excel.ExcelApp(one, two);
        }

        #endregion

        public System.Windows.Forms.Button button1;
        public System.Windows.Forms.TextBox textBox1;
        public System.Windows.Forms.TextBox textBox2;
    }
}




As I said -- no errors or warnings on running, but the excel file just pops up without anything being written to it.

Any help is appreciated.
Was This Post Helpful? 0

#8 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6535
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: Writing to Excel with Interop.Excel

Posted 01 October 2010 - 12:28 PM

Am I just blind? I see you creating a document... putting in text... but I don't see a save happening afterwards.

LIne 75 of the above: What's the point of the new Form1 that you never do anything with?
Was This Post Helpful? 0
  • +
  • -

#9 Guest_Guest*


Reputation:

Re: Writing to Excel with Interop.Excel

Posted 01 October 2010 - 12:38 PM

I'm new to windows forms -- this is in the Visual Studio 2010 win forms template so theres two files, one for the UI and one for the background code, I think.

Youre right I dont need the Form1 instance, I originally had the method button1_Click in the Program class and needed it there, but after moving it thats unnecessary.

Anyways, I added this:

Excel._Worksheet workSheet = (Excel.Worksheet)myExcelApp.ActiveSheet;

workSheet.Cells[1, "A"] = one;
workSheet.Cells[1, "B"] = two;



And now it works.
Was This Post Helpful? 0

#10 Guest_Sean*


Reputation:

Re: Writing to Excel with Interop.Excel

Posted 04 October 2010 - 01:32 PM

Anyone know how I could have the program open up my excel template, write the appropriate strings to it, and in the process automatically rename it, so when the user then saves the excel file it does not overwrite the template?

Or a link to documentation would work -- I looked on msdn for the interop class but didnt find anything like that.
Was This Post Helpful? 0

Page 1 of 1